😠 ☝🏿 🔵 Quelle est la nouveauté de la Devil's Dozen? 🌯 💗 👨🏽‍💼

Nous ne parlons que de PostgreSQL 13. Le 8 avril, un «gel» a eu lieu - le gel des fonctionnalités de PostgreSQL , désormais seules les fonctionnalités acceptées avant cette date seront incluses dans cette version.

Il est difficile de nommer une version révolutionnaire de cela. Il n'y a aucun changement conceptuel cardinal. De plus, des correctifs aussi importants que Table et Fonctions pour la norme JSON / SQL, que je voulais voir dans PG12 à côté du correctif JSONPath, n'ont pas eu le temps de le saisir; le stockage intégré prêt à l'emploi n'apparaît pas - seule l'interface est en cours de finalisation. Mais la liste des améliorations est toujours impressionnante. Nous avons préparé un résumé assez complet des correctifs inclus dans la Devil's Dozen.

Modifications des commandes SQL

CREATE DATABASE ... LOCALE

Utilitiesinitdb,createdbet l'équipeCREATE COLLATIONont un paramètreLOCALEqui vous permet de spécifier des valeurs pour le droitLC_CTYPEetLC_COLLATE. Maintenant, la même opportunité est apparue dans l'équipeCREATE DATABASE:

CREATE DATABASE db_koi8r TEMPLATE template0 
    ENCODING 'KOI8R' LOCALE 'ru_RU.KOI8R';

ALTER VIEW ... RENAME COLUMN

Le nom de la colonne dans la vue peut maintenant être modifié avec la commandeALTER VIEW. Auparavant, cela nécessitait de recréer la vue.

Supposons que vous ayez oublié de donner un nom à la colonne:

CREATE VIEW uptime AS SELECT current_timestamp, current_timestamp - pg_postmaster_start_time();
SELECT * FROM uptime;

       current_timestamp       |    ?column?     
-------------------------------+-----------------
 2020-03-23 15:37:00.088824+03 | 04:18:24.897856

Il peut être corrigé:

ALTER VIEW uptime RENAME COLUMN "?column?" TO uptime;
SELECT * FROM uptime;

       current_timestamp       |     uptime      
-------------------------------+-----------------
 2020-03-23 15:37:40.726516+03 | 04:19:05.535548

ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION

La colonne générée de la table peut maintenant être rendue normale, c'est-à-dire supprimer l'expression pour l'évaluer:

CREATE TABLE payments (
    id integer PRIMARY KEY,
    amount numeric(18,2),
    income_tax numeric(18,2) GENERATED ALWAYS AS (amount*0.13) STORED
);
INSERT INTO payments(id, amount) VALUES(1, 42);
\d payments

                                     Table "public.payments"
   Column   |     Type      | Collation | Nullable |                   Default            
        
------------+---------------+-----------+----------+--------------------------------------
 id         | integer       |           | not null |
 amount     | numeric(18,2) |           |          |
 income_tax | numeric(18,2) |           |          | generated always as ((amount * 0.13))
 stored
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)

Par la suite, ils ont décidé que la taxe sur le revenu devrait être explicitement définie. Supprimez l'expression:

ALTER TABLE payments ALTER COLUMN income_tax DROP EXPRESSION;
\d payments

                   Table "public.payments"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 id         | integer       |           | not null |
 amount     | numeric(18,2) |           |          |
 income_tax | numeric(18,2) |           |          |
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)

Bien sûr, les données existantes de la colonne n'ont pas disparu:

SELECT * FROM payments;

 id | amount | income_tax
----+--------+------------
  1 |  42.00 |       5.46

DROP DATABASE ... FORCE
Si vous souhaitez supprimer une base de données sans attendre la déconnexion de tous les utilisateurs, vous pouvez utiliser la nouvelle option deFORCEcommandeDROP DATABASE.

CREATE DATABASE db;

Connectez-vous à la nouvelle base de données:

CREATE EXTENSION IF NOT EXISTS dblink;
SELECT dblink_connect('dbname=db');

 dblink_connect
----------------
 OK

Et maintenant, nous allons supprimer, interrompant de force également pg_terminate_backend, les connexions ouvertes:

DROP DATABASE db WITH (FORCE);

ALTER TYPE ... SET STORAGE
La commandeALTER TYPEpermet aux types de données de base de modifier diverses propriétés, en particulier la stratégie de stockage. Auparavant, vous ne pouviez le définir qu'en équipeCREATE TYPE.

Pour la démonstration, nous ne créerons pas de nouveau type de base, mais utiliserons le -tsquery. Mais d'abord, créez une base de données distincte et connectez-vous à celle-ci:

CREATE DATABASE db;
\c db

Une stratégie de stockage est utilisée pour le type de données tsquery plain, donc les colonnes de tables de ce type ont la même stratégie:

SELECT typname,typstorage FROM pg_type WHERE typname = 'tsquery';

 typname | typstorage
---------+------------
 tsquery | p

CREATE TABLE queries (query tsquery);
SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'queries'::regclass AND attname = 'query';

 attname | attstorage
---------+------------
 query   | p

Si vous devez utiliser une stratégie différente pour les nouvelles tables, vous pouvez modifier le type de base:

ALTER TYPE tsquery SET (storage=external);
SELECT typname,typstorage FROM pg_type WHERE typname = 'tsquery';

 typname | typstorage
---------+------------
 tsquery | e

Le type de stockage dans les nouvelles tables changera également:

CREATE TABLE new_queries (query tsquery);
SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'new_queries'::regclass AND attname = 'query';

 attname | attstorage
---------+------------
 query   | e

Il convient de garder à l'esprit que le changement d'une stratégie impliquant l'utilisation de TOAST plainne peut pas être changé en :

ALTER TYPE tsquery SET (storage=plain);

ERROR:  cannot change type's storage to PLAIN

Par conséquent, les expériences ont été réalisées dans une base de données distincte, ce qui n'est pas dommage à supprimer.

ALTER STATISTICS ... SET STATISTICS La

commande CREATE STATISTICSvous permet de collecter des listes des valeurs les plus courantes pour les combinaisons sélectionnées de colonnes de tableau. Le nombre de valeurs les plus courantes collectées est déterminé par le paramètre default_statistics_target. La valeur de statistiques spécifiques peut maintenant être modifiée avec la commande:

ALTER STATISTICS  SET STATISTICS _;

FETCH FIRST avec l'option WITH TIES
Comme vous le savez,SELECTau lieu de spécifier unecommande,LIMITvous pouvez utiliser la syntaxe définie dans le standard SQL:

SELECT *
FROM (VALUES (1,'1.1'), (2,'2.1'), (2,'2.2'), (3,'3.1')) t(a,b)
ORDER BY a
FETCH FIRST 2 ROWS ONLY;

 a |  b  
---+-----
 1 | 1.1
 2 | 2.1
(2 rows)

Maintenant, il FETCHprend en charge la phrase WITH TIES, qui ajoute à la sortie toutes les lignes "liées" (lignes égales à celles déjà sélectionnées, si seule la condition de tri est prise en compte):

SELECT *
FROM (VALUES (1,'1.1'), (2,'2.1'), (2,'2.2'), (3,'3.1')) t(a,b)
ORDER BY a
FETCH FIRST 2 ROWS WITH TIES;

 a |  b  
---+-----
 1 | 1.1
 2 | 2.1
 2 | 2.2
(3 rows)

Fonctions et types de données intégrés

get_random_uuid La
nouvelle fonctionget_random_uuidrenvoie la valeur UUID de la version 4 (valeur aléatoire):

SELECT gen_random_uuid();

           gen_random_uuid            
--------------------------------------
 25e02793-80c0-438c-be07-c94b966c43ab

La fonction est utile pour générer des valeurs UUID uniques dans des systèmes distribués.
Auparavant, vous deviez utiliser les bibliothèques uuid-ossp ou pgcrypto.

min_scale et trim_scale pour les valeurs de type numérique

La fonction min_scaledétermine le nombre de chiffres significatifs dans la partie fractionnaire du nombre et la fonction trim_scalesupprime les zéros non significatifs :

SELECT n, min_scale(n), trim_scale(n) FROM (VALUES(42.42000)) as t(n);

    n     | min_scale | trim_scale
----------+-----------+------------
 42.42000 |         2 |      42.42

gcd et lcm

Reconstitution dans la section des fonctions mathématiques. Vous pouvez maintenant trouver rapidement le plus grand diviseur commun (gcm) et le plus petit multiple commun (lcm):

SELECT gcd(54,24), lcm(54,24);

 gcd | lcm
-----+-----
   6 | 216

Fonctions d'agrégation min et max pour le type pg_lsn Des fonctions d'agrégation ont été ajoutées pour le

type de donnéesetcela vous permet d'effectuer des requêtes du formulaire:pg_lsnminmax

SELECT min(restart_lsn) FROM pg_replication_slots;
SELECT min(sent_lsn) FROM pg_stat_replication;

Vérification du modificateur de type de la valeur de retour d'une fonction

Dans les versions précédentes, le modificateur de type n'était pas vérifié pour la valeur de retour de la fonction.

Supposons qu'il existe un type de stockage des unités monétaires et une fonction qui renvoie le montant de l'impôt sur le revenu:

CREATE TYPE currency AS (
    amount numeric(18,2),
    code   text
);
CREATE FUNCTION income_tax(quantity currency) RETURNS currency
    AS 'SELECT quantity.amount * 0.13, quantity.code' LANGUAGE SQL;

En appelant la fonction, nous nous attendons à obtenir deux décimales, cependant, nous en obtenons quatre. Même la conversion explicite après un appel de fonction n'aide pas (troisième colonne):

SELECT amount, code, amount::numeric(18,2)
FROM income_tax(ROW(42.42, ''))\gx

-[ RECORD 1 ]--
amount | 5.5146
code   | 
amount | 5.5146

Dans la version 13, le résultat est correct:

SELECT amount, code, amount::numeric(18,2)
FROM income_tax(ROW(42.42, ''))\gx

-[ RECORD 1 ]
amount | 5.51
code   | 
amount | 5.51

Les noms localisés dans to_date () et to_timestamp ()

Fonctionsto_dateaussito_timestampappris à comprendre les noms localisés des mois etjours de la semaine. Auparavant, seuls les noms anglais pouvaient être utilisés:

SELECT to_date(', 24  2020', 'TMDay, DD TMMonth YYYY');

  to_date   
------------
 2020-03-24

normaliser et EST NORMALISÉ

Pour se conformer à la norme SQL, la fonction normalize () a été ajoutée pour normaliser une chaîne Unicode, et le prédicat IS NORMALIZED pour vérifier si une chaîne est normalisée.

SELECT str, normalize(str, NFC) AS nfc,
       str IS NFC NORMALIZED AS is_nfc_normalized,
       normalize(str, NFKC) AS nfkc,
       str IS NFKC NORMALIZED AS is_nfkc_normalized
FROM (VALUES(U&'\0032\2075')) AS vals(str)\gx

-[ RECORD 1 ]------+---
str                | 2⁵
nfc                | 2⁵
is_nfc_normalized  | t
nfkc               | 25
is_nfkc_normalized | f

En savoir plus sur les formulaires de normalisation UNICODE.

Type Xid8 et fonction xid8_current () pour les numéros de transaction 64 bits

Ajout d'un nouveau type de données xid8 pour le numéro de transaction 64 bits. Mais non, cela ne signifie pas que PostgreSQL est passé aux transactions 64 bits: tout fonctionne exactement comme avant. Mais certaines fonctions renvoient un nouveau type, par exemple, est maintenant recommandé pour une utilisation à la place des anciennes fonctions pg_current_xact_id txid_current, qui sont retournés int8, et ainsi de suite. N. De

nouveaux types de données polymorphes famille anycompatible

types ajouté anycompatible, anycompatiblearray, anycompatiblenonarray, anycompatiblerange. Contrairement aux types de famille anyelement, les nouveaux types vous permettent d'utiliser non pas exactement les mêmes, mais des types réellement compatibles.

Dans l'exemple suivant, la fonctionmaximumcomme arguments définis comme anycompatiblesont passés integeret numeric. La valeur de retour est convertie en une valeur commune pour ces deux types:

CREATE FUNCTION maximum(a anycompatible, b anycompatible) RETURNS anycompatible
	AS 'SELECT CASE WHEN a > b THEN a ELSE b END' LANGUAGE SQL;

SELECT f, pg_typeof(f) FROM maximum(42, 42.42) f;

   f   | pg_typeof
-------+-----------
 42.42 | numeric

De plus, les types anycompatible- et any- sont deux ensembles de types indépendants:

CREATE FUNCTION first_elems(a anyarray, b anycompatiblearray)
    RETURNS TABLE(a anyelement, b anycompatible) AS $$
        SELECT ROW(a[array_lower(a,1)], b[array_lower(b,1)]);
    $$ LANGUAGE sql;
SELECT first_elems(ARRAY[1,2,3], ARRAY['a','b','c']) AS str, 
       first_elems(ARRAY[1.1,2.2,3.3],ARRAY[true,false]) bool;

  str  |  bool   
-------+---------
 (1,a) | (1.1,t)

Langues procédurales

Transformation pour le type bool en PL / Perl
Plus récemment, TRANSFORM d'Ivan Panchenko (Postgres Professional) - bool_plperl a été commis . Postgres transmet des valeurs booléennes àtouen PL / Perl commef, mais pour Perl cefn'est pas un faux booléen, mais juste la lettre f, c'est-à-dire dans un contexte logique, la vérité . Ce problème peut être résolu de différentes manières (voir la correspondance ), mais la création de TRANSFORM pour bool, selon Tom Lane, est la plus pratique.

Exécution rapide d'expressions simples en PL / pgSQL

Les expressions simples (au moins ne contenant pas d'appels de table et ne nécessitant pas de verrous) seront plus rapides. Auparavant, dans ces cas, le temps consacré à contacter le programmateur à chaque cycle était improductif.

CREATE OR REPLACE FUNCTION slow_pi() RETURNS double precision AS $$
    DECLARE
        a double precision := 1;
        s double precision := 1;
        r double precision := 0;
    BEGIN
        FOR i IN 1 .. 10000000 LOOP
            r := r + s/a; a := a + 2; s := -s;
        END LOOP;
        RETURN 4*r;
    END;
    $$ LANGUAGE plpgsql;

Appelez slow_pi () dans PG12:

SELECT slow_pi();

          slow_pi      
    --------------------
     3.1415925535898497
    (1 row)
    Time: 13060,650 ms (00:13,061)

Maintenant en PG13:

SELECT slow_pi();

          slow_pi      
    --------------------
     3.1415925535897915
    (1 row)
    Time: 2108,464 ms (00:02,108)

Extensions approuvées au lieu de pg_pltemplate

Le répertoire système a diminué d'une table. Supprimépg_pltemplate. Il stockait les propriétés des langages procéduraux nécessaires lors de l'exécutionCREATE LANGUAGE. Nous avons maintenant décidé d'enregistrer les propriétés dans les scripts des extensions des langages correspondantspg_pltemplateet de nous débarrasser de la table elle-même. Mais pour mettre en œuvre le plan, il est nécessaire de prévoir la possibilité pour le propriétaire de la base de données (sans droits de superutilisateur) de créer une langue de confiance à partir du script d'extension. En effet, maintenant pour créer, par exemple, plpgsql, le propriétaire de la base de données n'a pas besoin d'être superutilisateur.

Reçu comme suit. Un nouveau paramètre logique est apparu dans le fichier de contrôle des extensionstrusted. Si les deux paramètrestrustedetsuperuserinclus, l'extension peut être créée non seulement par le superutilisateur, mais aussi par l'utilisateur ayant le droit CREATEsur la base de données actuelle (et donc son propriétaire). Lors de l'exécution d'un script d'une telle extension, les droits de superutilisateur qui ont initialisé le cluster seront utilisés. Les objets créés par l'extension lui appartiendront, bien que le propriétaire de l'extension lui-même soit l'utilisateur créateur.

Conséquences importantes de ces changements:

Les extensions de confiance ouvrent la voie aux développeurs tiers pour créer d'autres langues de confiance. Maintenant, nous sommes limités uniquement à plpgsql, plperl et pltcl.
Il pg_pltemplateétait bien écrit que plpython se réfère à la deuxième version du langage. Ne pas le pg_pltemplatefaire est une étape (nécessaire, mais pas suffisante) pour la transition vers python 3.

Indices

Compression de l'arbre B
Un patch important et attendu depuis longtemps (les travaux ont déjà commencé en 2015) écrit par Anastasia Lubennikova (Postgres Professional) et Peter Geigan (Peter Geoghegan) est finalement communiqué par Peter. Nastya a réussi à en parler à PGconf India . Postgres a appris à réduire considérablement la taille des index B-tree grâce à la déduplication, c'est-à-dire des économies sur les clés d'index en double. Ces index ont été sérieusement repensés afin que la compression soit possible sans perte de compatibilité avec les versions précédentes des index. L'idée de déduplication est issue d'une architecture plus flexible d'index comme GIN (reverse indexes - Generalized Inverted Index).

Dans ces indices plus souvent que dans l' arbre B, il existe une situation où une clé correspond à un grand nombre d'enregistrements. Dans le cas du traitement de texte, par exemple, le même jeton se trouve généralement dans plusieurs documents. Et il n'est stocké dans l'index qu'une seule fois. Jusqu'à récemment, les index B-tree ne savaient pas comment procéder.

Les index B-tree diffèrent des index GIN principalement dans les pages feuilles. Selon le nombre d'enregistrements associés à la même valeur de clé, des options sont possibles: la page ne contient qu'une liste de publication - une liste de TID (identificateurs d'enregistrements indexés), si la liste est petite et s'il y a beaucoup de TID, alors au lieu d'une liste de valeurs sont stockées nouvelles «branches d'arbre» - liens vers d'autres pages comme la liste de publication ou d'autres branches d'arbre (elles sont appelées arbre de publication).

Une telle arborescence est similaire à une arborescence B, mais diffère dans les détails essentiels: par exemple, les listes permettant de parcourir des pages du même niveau d'arborescence dans le GIN sont unidirectionnelles et non bidirectionnelles. Par conséquent (y compris) une bonne compatibilité des nouveaux index dédupliqués avec les anciennes versions n'est pas facile à réaliser. Et les améliorations ont vraiment pris plus de 3 ans. Il a également été nécessaire de travailler sur le mécanisme de nettoyage (micro-vide) et d'autres nuances.

Dans les tests de performances, tous les index auxquels la déduplication est applicable ont diminué d'environ 3 fois. La compression des doublons aide également les index uniques, éliminant le problème de gonflement d'index à un taux élevé de changements de table. Un nouveau comportement peut être connecté et déconnecté au niveau des paramètres d'index.

L'analyse complète par index GIN n'est pas effectuée là où elle n'est pas nécessaire
Ce patch permet dans certains cas d'éviter un passage complet à travers l'index GIN complet. Certaines opérations, bien que prises en charge par l'index GIN, sont effectuées par une analyse complète de l'index. Prenons, par exemple, l'index pour la recherche de colonnes en texte intégral tsvector. Si la requête de recherche a la forme «tout sauf un mot donné», alors l'index entier devra être lu dans son intégralité. Si, cependant, une autre condition est présente dans la demande qui ne nécessite pas une analyse complète de l'index, alors l'index sera toujours analysé complètement.

Avec la nouvelle optimisation, une condition plus précise sera utilisée en premier, permettant d'obtenir un gain de l'indice, puis les résultats seront revérifiés pour prendre en compte une autre limitation. Comparez le nombre de pages lues dans la version 12 (tampons):

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT * FROM mail_messages WHERE tsv @@ to_tsquery('!vacuum') AND tsv @@ to_tsquery('analyze');

                                             QUERY PLAN                                             
------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual rows=5864 loops=1)
   Recheck Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
   Heap Blocks: exact=5167
   Buffers: shared hit=24 read=27405
   ->  Bitmap Index Scan on mail_messages_tsv_idx (actual rows=5864 loops=1)
         Index Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
         Buffers: shared hit=24 read=22238
 Planning Time: 0.283 ms
 Execution Time: 3258.234 ms

avec le nombre de tampons dans la nouvelle version:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT * FROM mail_messages WHERE tsv @@ to_tsquery('!vacuum') AND tsv @@ to_tsquery('analyze');

                                             QUERY PLAN                                             
---------------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual rows=5864 loops=1)
   Recheck Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
   Heap Blocks: exact=5156
   Buffers: shared hit=5179
   ->  Bitmap Index Scan on mail_messages_tsv_idx (actual rows=5864 loops=1)
         Index Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
         Buffers: shared hit=23
 Planning Time: 0.250 ms
 Execution Time: 8.779 ms

Une situation similaire peut être rencontrée lors de l'utilisation de trigrammes et lors de la vérification de l'occurrence de tableaux.

Paramètres des classes d'opérateurs
Dans PostgreSQL, de nombreuses méthodes d'accès aux index sont un «framework» qui prend en charge une implémentation de haut niveau de l'algorithme de recherche, travaillant avec des pages et des verrous, et le journal WAL. Et la liaison à des types de données et des opérateurs spécifiques est effectuée à l'aide de classes d'opérateurs.

Jusqu'à présent, les classes d'opérateurs ne pouvaient pas avoir de paramètres. Par exemple, pour une recherche en texte intégral, un index GiST avec une classe d'opérateurs peut être utilisé tsvector_ops(à propos des classes d'opérateurs GiST ici) Cette classe d'opérateurs utilise un arbre de signature, et la longueur de signature a été fixée (124 octets). Vous pouvez maintenant spécifier la longueur explicitement, ce qui vous permet de contrôler l'équilibre entre la taille de l'index et l'efficacité (le nombre de collisions de hachage):

CREATE INDEX ON mail_messages USING gist(tsv tsvector_ops(siglen=1024));

Des modifications similaires ont été apportées aux démarreurs pour d'autres classes d'opérateurs GiST qui utilisent un arbre de signature, qui s'applique aux extensions hstore, intarray, ltree et pg_trgm.
Mais l'idée principale pour laquelle cette modification a été conçue est la possibilité de passer l'expression JSONPath à l'index GIN afin que tout le document JSON ne soit pas indexé, mais seulement la partie nécessaire de celui-ci. Dans de nombreux cas, cela réduira radicalement la taille des index. Mais ce travail reste à faire.

L'idée d'Oleg Bartunov, la mise en œuvre de Nikita Glukhov et Alexander Korotkov (tous les trois Postgres Professional).

L'opérateur <-> (case, point) a été
ajouté. L'opération manquante a été ajoutée pour être utilisée dans kNN pour GiST et SP-GiST . Dans PG12 lorsque vous travaillez avec des types géométriques pointetboxvous pouvez utiliser l'opérateur de distance <->(point, box), et cela accélérera la recherche avec les index GiST et SP-GiST. Mais l'opérateur symétrique pour lui <->(box, point)n'a pas été implémenté, bien qu'il boxcomprenne déjà les distances à des types plus complexes - polygones et cercles.

CREATE TABLE points(point point);
CREATE TABLE boxes(box box);
INSERT INTO points VALUES('1,2','3,4');
INSERT INTO boxes VALUES(box(point(3,4),point(5,6)), box(point(13,14),point(15,16)));

Dans PG12:

SELECT p.point, b.box, b.box  <-> p.point distance FROM points AS p, boxes AS b ORDER BY distance;

:    : box <-> point

Si vice versa, alors tout va bien:

SELECT p.point, b.box, p.point  <-> b.box distance FROM points AS p, boxes AS b ORDER BY distance;

 point |       box       |      distance
-------+-----------------+--------------------
 (1,2) | (5,6),(3,4)     | 2.8284271247461903
 (2,1) | (5,6),(3,4)     | 3.1622776601683795
 (1,2) | (15,16),(13,14) | 16.970562748477143
 (2,1) | (15,16),(13,14) | 17.029386365926403

Et en PG13:

SELECT p.point, b.box, b.box  <-> p.point distance FROM points AS p, boxes AS b ORDER BY distance;

 point |       box       |      distance
-------+-----------------+--------------------
 (1,2) | (5,6),(3,4)     | 2.8284271247461903
 (2,1) | (5,6),(3,4)     | 3.1622776601683795
 (1,2) | (15,16),(13,14) | 16.970562748477143
 (2,1) | (15,16),(13,14) | 17.029386365926403

Les indices GiST et SP-GiST seront accélérés dans cette opération.

Notez que dans PG13, si vous demandez:

SELECT count(*) FROM pg_operator WHERE oprname = '<->';

 count 
-------
    28

et si nous faisons de même en PG12, nous obtenons 20 entrées: dans la 13e version, la liste a été remplie avec jusqu'à 8 opérateurs.

Json

Prise en charge de la méthode .datetime () pour jsonpath

Il s'agit de l'un des correctifs infructueux d'une grande série de correctifs JSONPath que PG12 n'a pas eu le temps de terminer. Fait partie de la norme JSON / SQL. Le problème était que toutes les fonctions de la série de correctifs JSONPath sont immuables, mais la comparaison de date prend en compte le fuseau horaire actuel, qui peut changer pendant la session.

Dans de tels cas, nous permettons aux fonctions immuables existantes de générer une erreur sur les comparaisons non immuables. Dans le même temps, ce patch a des fonctions avec le suffixe _tz qui fonctionnent de manière stable dans les opérations avec le fuseau horaire.

Nouvelle fonction - fonction jsonb_set_lax

En général, lax est un mode de fonctionnement non strict (contrairement à strict) des fonctions avec jsonb. Dans ce cas, cette fonction sera opérationnelle dans une situation où l'un des arguments qu'elle prend est NULL. Contrairement à la version stricte - jsonb_set () - elle a un argument supplémentaire qui indique des actions dans le cas de NULL. Options: use_json_null / raise_exception / return_target / delete_key. Options suggérées par les utilisateurs intéressés.

Optimisé certaines fonctions jsonb. Beaucoup

optimisé ., principalement grâce aux efforts de Nikita Glukhov (Postgres Professional). Mais analyser chaque point dans ce cas est inutile: premièrement, leur abondance fera gonfler un article déjà court; et deuxièmement, les modifications concernent le périphérique interne et tous les utilisateurs ne sont pas intéressés. Par conséquent, nous n'en énumérerons que la plupart:

Fonction optimisée JsonbExtractScalar ();
Opérateur optimisé # >>, fonctions jsonb_each_text (), jsonb_array_elements_text ();
La reconnaissance du type JsonbContainer dans get_jsonb_path_all () est optimisée;
La récupération du premier jeton de l'itérateur JsonbIterator est remplacée par la macro légère JsonbContainerIsXxx ();
Extraction de clés plus pratique - findJsonbKeyInObject ();
Stockage optimisé des résultats findJsonbValueFromContainer () et getIthJsonbValueFromContainer ();
get_jsonb_path_all(), ;
JsonbValueAsText.

, SQL/JSON: JSON_TABLE SQL/JSON: functions. . , . . PG14. JSONPath .

pgbench

L'utilitaire permettant d'exécuter des tests de performances a reçu une série d'améliorations. Il y avait des statistiques sur l'exécution des tâches lors de la phase d'initialisation, une conclusion plus visuelle , la possibilité de voir le code des scripts intégrés , des tests sur une table de comptes partitionnée .

De plus, nous avons ajouté une commande \asetsimilaire à \gset, mais permettant de définir des valeurs pour les variables de plusieurs requêtes envoyées à la fois. La ligne suivante, envoyée au serveur pour exécution, définit les deux variables oneet two:

SELECT 1 AS one \; SELECT 2 AS two \aset

pg_dump

pg_dump a appris à décharger les données des tables tierces . Grâce au paramètre, --include-foreign-datavous pouvez spécifier une liste de serveurs tiers, dont les données des tables seront déchargées.

Utilisez ce déchargement avec précaution. Loin du fait que les données doivent être téléchargées sur un serveur tiers. De plus, il est tout à fait possible qu'un serveur tiers ne soit pas disponible pendant la récupération. Ou un serveur tiers ne peut autoriser que la lecture, mais pas l'écriture de données.

psql

Une série de petits correctifs rend le psql plus confortable:

Amélioration de la terminaison des onglets pour plusieurs équipes.
En plus d' \echoenvoyer une chaîne à STDOUT, une nouvelle commande \warnenvoie une chaîne à la sortie d'erreur standard (STDERR).
\d toast- . \d+ .
\dt+ «Persistence» (unlogged) (permanent).
\e , ; ( \g*). , psql .
. PROMPT1 PROMPT2 %x.
De nouvelles commandes pour plus d' informations sur les méthodes d'accès: \dAc, \dAf, \dAo,\dAp
Vous pouvez\g maintenant spécifier entre parenthèses toutes les options prises en charge \pset. Ils n'agiront que sur l'équipe actuelle.

libpq

Modifications mineures par rapport à PostgreSQL:

L'inexactitude dans la description des paramètres host et hostadr et l'incohérence qui en résulte dans la sortie de la \conninfocommande utilitaire psql ont été corrigées.
Si la clé de certificat client est stockée sous forme cryptée, vous ne pouvez entrer le mot de passe qu'en mode interactif. Le nouveau paramètre sslpassword vous permet de décrypter la clé de manière non interactive.
Deux nouveaux paramètres, sslminprotocolversion et sslmaxprotocolversion, vous permettent de spécifier une restriction sur la version du protocole SSL / TCL avec laquelle la connexion est autorisée.

reindexdb

Le nouveau paramètre --jobs de l' utilitaire reindexdb définit le nombre de connexions à la base de données dans lesquelles les index seront reconstruits en même temps.

pg_rewind

Les limites de l'utilitaire disparaissent progressivement et les possibilités augmentent.
Premièrement, pg_rewind peut maintenant enregistrer des informations pour la récupération (comme pg_basebackup peut le faire), ainsi que démarrer la récupération et l'arrêt ultérieur d'une instance si elle n'a pas été arrêtée via un point de contrôle (cela devait être fait manuellement auparavant).

Et deuxièmement, pg_rewind a appris à travailler avec l'archive WAL .
Une fois que l'utilitaire a trouvé le point de divergence WAL entre les deux serveurs, il doit créer une liste de toutes les pages qui doivent être copiées dans le cluster cible afin d'éliminer les différences. Pour cela, l'utilitaire requiert tous les fichiers WAL, à partir du point trouvé. Si les fichiers WAL nécessaires ne sont pas disponibles sur le cluster cible, l'utilitaire n'a pas pu effectuer son travail auparavant.

Avec ce patch d'Alexey Kondratov (Postgres Professional), pg_rewind pourra lire les segments WAL manquants de l'archive des fichiers journaux en utilisant le paramètre restore_command si un nouveau commutateur -c ou --restore-target-wal est spécifié.

pg_waldump

pg_waldump décryptera l' enregistrement de transaction préparé.

amcheck

L'extension amcheck a appris à mieux reconnaître les dommages dans les index B-tree.
Soit dit en passant, les messages du journal du serveur concernant les pages endommagées diffèrent désormais pour les index et les tables .

pageinspect

La heap_tuple_infomask_flagsfonction d'extension pageinspect déchiffre les valeurs des champs infomask et infomask2renvoyées par la fonction heap_page_items. Utile pour enquêter sur des situations de corruption de données.

postgres_fdw

Le superutilisateur au niveau du mappage de nom d'utilisateur peut permettre aux utilisateurs réguliers d' utiliser une connexion sans mot de passe:

ALTER USER MAPPING FOR  SERVER 
    OPTIONS (ADD password_required 'false');

Cela se fait, entre autres, afin que sslkey et sslcert puissent être utilisés comme paramètres de connexion .

adminpack

L'extension adminpack a une nouvelle fonctionnalité - pg_file_sync. En l'utilisant, vous pouvez faire fsync pour les fichiers écrits par le serveur sur le disque, par exemple via pg_file_writeou COPY TO.

surveillance

pg_stat_slru

Dans la mémoire partagée du serveur, il y a non seulement un grand cache tampon, mais également un certain nombre d'autres caches plus simples (par exemple, pour le statut des transactions). Ils utilisent un algorithme simple pour évincer les pages les moins fréquemment utilisées (simple moins récemment utilisé ou SLRU). Jusqu'à présent, de tels caches «fonctionnaient juste», mais il fallait les surveiller, tout d'abord pour que les développeurs du noyau PostgreSQL sachent si quelque chose devait y être changé. Pour cela et dans un but, une nouvelle vue de pg_stat_slru est apparue .

pg_stat_activity

Dans la vue, la pg_stat_activity nouvelle colonne est leader_id. Pour les processus participant à des demandes parallèles, il est renseigné avec le numéro du processus principal. Et le processus principal leader_idest un numéro de processus pid.
La requête suivante montre quelles requêtes et quels processus s'exécutent actuellement en parallèle:

SELECT query, leader_pid, 
    array_agg(pid) filter(WHERE leader_pid != pid) AS members
  FROM pg_stat_activity
 WHERE leader_pid IS NOT NULL
 GROUP BY query, leader_pid;

Des modifications ont été apportées à la liste des événements en attente. Ajout de deux nouveaux événements : BackupWaitWalArchiveet RecoveryPause. Et les deux autres ont reçu des noms plus précis: RecoveryWalStream -> RecoveryRetrieveRetryInterval, RecoveryWalAll -> RecoveryWalStream.

Et deux autres nouveaux événements d'attente se produisant sur la réplique : RecoveryConflictSnapshot(conflit avec VACUUM, qui a supprimé la version nécessaire des lignes) et RecoveryConflictTablespace(conflit lié à la suppression de l'espace table).

pg_stat_statements

Jusqu'à présent, l'extension a pg_stat_statementstraité les demandes avec FOR UPDATEet sans expression comme la même demande. Les demandes avec FOR UPDATE sont désormais enregistrées séparément .

La quantité d'informations collectées a augmenté. Désormais, non seulement les informations sur les ressources pour l'exécution des commandes sont enregistrées, mais aussi les statistiques sur les écritures de journal générées . Nouvelles colonnes de présentation: wal_bytes- volume d'enregistrements générés, wal_records- nombre d'enregistrements générés, wal_num_fpw- nombre d'images pleine page (écritures pleine page).

Cela a été rendu possible grâce à l' infrastructure préparée pour suivre l'utilisation du WAL. Par conséquent, maintenant EXPLAINavec une nouvelle option, il WALaffichera le volume des enregistrements générés:

CREATE TABLE t (id int);
EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF, SUMMARY OFF)
    INSERT INTO t VALUES(1);

              QUERY PLAN              
--------------------------------------
 Insert on t (actual rows=0 loops=1)
   WAL:  records=1  bytes=59
   ->  Result (actual rows=1 loops=1)

Extension auto_explain, VACUUMs VERBOSE, et autovacuumégalement utiliser l'infrastructure créée et produira des volumes de WAL.

Revenons à pg_stat_statements. Si le nouveau paramètre pg_stat_statements.track_planning est activé , des statistiques supplémentaires liées au planificateur seront enregistrées pour chaque opérateur: nombre de builds de plan; temps total de planification; temps minimum et maximum d'une planification, ainsi que la moyenne et l'écart type.

La prise en compte des ressources allouées au planificateur se reflète dans un autre correctif qui n'est pas lié à pg_stat_statements. EXPLAINavec l'option BUFFERSsignalera le nombre de tampons utilisés au stade de la planification :

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) 
    SELECT * FROM pg_class;

                   QUERY PLAN                   
------------------------------------------------
 Seq Scan on pg_class (actual rows=386 loops=1)
   Buffers: shared hit=9 read=4
 Planning Time: 0.782 ms
   Buffers: shared hit=103 read=11
 Execution Time: 0.219 ms

Magazine

log_statement_sample_rate SQL, , log_min_duration_sample( ).
, log_min_duration_statement , .. log_min_duration_statement , log_min_duration_sample, , log_statement_sample_rate.
, log_transaction_sample_rate , , .
, ( log_min_error_statement), . log_parameter_max_length_on_error. 0, .
log_parameter_max_length_on_error SQL, , .
( log_statements log_duration) , : log_parameter_max_length, , .
Vous pouvez maintenant écrire le type de processus ( pg_stat_activity.backend_type) dans le journal du serveur . Pour cela log_line_prefix, un symbole spécial est fourni dans le paramètre %b. Et si le journal est écrit au format csv ( log_destination=csvlog), la colonne y est backend_typedéjà incluse.

Le progrès

De nouvelles vues vous permettent pg_stat_progress_analyzeégalement pg_stat_progress_basebackupde suivre la progression de la collecte de statistiques par l'équipe ANALYZEet de créer une copie de sauvegarde de l' utilitaire, pg_basebackuprespectivement.

Optimisation

Calcul des fonctions immuables dans la clause FROM au stade de la planification
Le patch d'Aleksandr Kuzmenkov et Aleksandr Parfyonov (tous deux de Postgres Professional) aide dans les cas où l'FROMappel contient un appel de fonction qui est en fait une constante. Dans ce cas, au lieu d'établir la connexion, la valeur constante est substituée aux endroits nécessaires de la demande.

Voici comment cela se produit avec un exemple de requête liée à la recherche en texte intégral:

EXPLAIN (COSTS OFF)
SELECT subject, ts_rank_cd(tsv, q) AS rank
FROM mail_messages, to_tsquery('english', 'tuple') q
WHERE tsv @@ q
ORDER BY rank DESC;

                              QUERY PLAN                              
------------------------------------------------------------------
 Sort
   Sort Key: (ts_rank_cd(mail_messages.tsv, '''tuple'''::tsquery)) DESC
   ->  Bitmap Heap Scan on mail_messages
         Recheck Cond: (tsv @@ '''tuple'''::tsquery)
         ->  Bitmap Index Scan on mail_messages_tsv_idx
               Index Cond: (tsv @@ '''tuple'''::tsquery)

Il n'y a pas de connexion et la valeur 'tuple' :: tsquery est substituée dans la requête déjà au stade de la planification. La version 12 avait une image complètement différente:

EXPLAIN (COSTS OFF)
SELECT subject, ts_rank_cd(tsv, q) AS rank                            
FROM mail_messages, to_tsquery('english', 'tuple') q
WHERE tsv @@ q                            
ORDER BY rank DESC;

                          QUERY PLAN                         
-----------------------------------------------------
 Sort
   Sort Key: (ts_rank_cd(mail_messages.tsv, q.q)) DESC
   ->  Nested Loop
         ->  Function Scan on q
         ->  Bitmap Heap Scan on mail_messages
               Recheck Cond: (tsv @@ q.q)
               ->  Bitmap Index Scan on mail_messages_tsv_idx
                     Index Cond: (tsv @@ q.q)

Tri incrémentiel

Lorsqu'un tri par plusieurs clés est nécessaire (k1, k2, k3 ...), l'ordonnanceur peut désormais profiter du fait que les données sont déjà triées par plusieurs des premières clés (par exemple, k1 et k2). Dans ce cas, vous ne pouvez pas trier à nouveau toutes les données, mais les diviser en groupes consécutifs avec les mêmes valeurs k1 et k2 et «trier» par la clé k3.

Ainsi, le tri entier se divise en plusieurs sortes consécutives de plus petite taille. Cela réduit la quantité de mémoire nécessaire et vous permet également de fournir les premières données avant la fin du tri.

Par exemple, dans la base de données de la table tickets, il y a un index dans la colonne ticket_id. Les données reçues de l'index seront triées par ticket_id, donc la requête suivante utilisera le tri incrémentiel:

EXPLAIN (analyze, costs off, timing off)
SELECT * FROM tickets ORDER BY ticket_no, passenger_id;

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Incremental Sort (actual rows=2949857 loops=1)
   Sort Key: ticket_no, passenger_id
   Presorted Key: ticket_no
   Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB
   ->  Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
 Planning Time: 2.137 ms
 Execution Time: 2230.019 ms

La fonctionnalité de tri incrémentiel peut être désactivée avec le paramètre enable_incrementalsort. Dans ce cas, le tri prendra sensiblement plus de temps:

SET enable_incrementalsort = off;
EXPLAIN (analyze, costs off, timing off)
SELECT * FROM tickets ORDER BY ticket_no, passenger_id;

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Gather Merge (actual rows=2949857 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort (actual rows=983286 loops=3)
         Sort Key: ticket_no, passenger_id
         Sort Method: external merge  Disk: 107024kB
         Worker 0:  Sort Method: external merge  Disk: 116744kB
         Worker 1:  Sort Method: external merge  Disk: 107200kB
         ->  Parallel Seq Scan on tickets (actual rows=983286 loops=3)
 Planning Time: 3.994 ms
 Execution Time: 12291.844 ms

L'idée du tri incrémental a été proposée en 2013 par Alexander Korotkov (Postgres Professional), et maintenant, sept ans plus tard, le patch a été porté par James Coleman dans un état accepté par la communauté.

Accélération TRUNCATE
Lorsque l' TRUNCATEanalyse se produit shared_bufferspour supprimer les tampons de table de la mémoire partagée. Auparavant, l'analyse était effectuée trois fois, pour chaque couche de table: MAIN (couche de données principale), FSM (carte d'espace libre), VM (carte de visibilité). Maintenant, la logique a changé, au lieu d'une triple opération, les tampons ne sont analysés qu'une seule fois. Avec de grandes valeurs, shared_bufferscela donne un gain tangible.

Décompression partielle TOAST
Lorsqu'il n'est pas nécessaire de lire complètement TOAST, en le limitant à une tranche au début ou près du début, il n'est pas logique de le desserrer complètement. TOAST compressé est lu par itérations: lisez un morceau, s'il n'y a pas de données nécessaires, puis développez-le et lisez la suite. Suggéré par un étudiant de Google Summer of Code, Binguo Bao, qui donne un exemple:

CREATE TABLE slicingtest (
     id serial PRIMARY KEY,
     a text
);
INSERT INTO slicingtest (a) SELECT
    repeat('1234567890-=abcdefghijklmnopqrstuvwxyz', 1000000) AS a FROM
    generate_series(1,100);
\timing
SELECT sum(length(substr(a, 0, 20))) FROM slicingtest;

Time: 28.123 ms

Avec le patch, un ordre de grandeur plus rapide:

Time: 2.306 ms

Parallel VACUUM
Dans son article sur ce sujet, Yegor Rogov explique en détail cette étape importante de la parallélisation. En bref: «Patch Masahiko Sawada, qui vous permet d'effectuer un nettoyage en parallèle. La table elle-même est toujours effacée par un processus (principal), mais pour nettoyer les index, elle peut désormais démarrer des workflows en arrière-plan, un pour chaque index. En mode manuel, cela vous permet d'accélérer le nettoyage de grandes tables avec plusieurs index; le nettoyage automatique n'utilise pas encore cette fonction. »

Nettoyage automatique une fois collé dans une table
Pour ce correctif (également connu sous le nom d'aspirateur automatique Berserk), nous devons remercier Dorofei Proleskovsky, qui a proposé une solution au problème suivant: le nettoyage automatique ne vient pas à la table en annexe uniquement, car ils n'ont pas de versions «mortes» des rangées. Pour cette raison, la carte de visibilité n'est pas mise à jour, rendant les analyses uniquement indexées inefficaces, et lorsque le nettoyage empêche les débordements du compteur de transactions, il doit faire beaucoup de travail à la fois. Maintenant, cette situation a été corrigée: l'autonettoyage fonctionnera également sur l'ajout de lignes. Deux nouveaux paramètres de serveur ( autovacuum_vacuum_insert_thresholdet autovacuum_vacuum_insert_scale_factor) sont apparus, similaires à ceux des modifications ( autovacuum_vacuum_thresholdet autovacuum_vacuum_scale_factor).

Hash Aggregate Memory Management
L'agrégation de hachage peut nécessiter plus de RAM que le planificateur ne le pensait et que ce qui est indiqué dans work_mem. Auparavant, une telle erreur de planificateur entraînait le fait que la taille était work_memignorée et que la mémoire était allouée autant que nécessaire pour le fonctionnement ou l'arrivée de OOM Killer. Maintenant, l'algorithme ne va pas plus loin work_memet, si nécessaire, utilise des fichiers temporaires sur le disque. Pour contrôler le comportement du planificateur, les paramètres suivants sont apparus: enable_groupingsets_hash_disket enable_hashagg_disk.

Optimisation de UPDATE pour les tables avec les colonnes générées
Dans la version 12, les colonnes générées étaient recalculées lors de toute mise à jour de ligne, même si cette modification ne les affectait en aucune façon. Maintenant, ils ne seront recalculés que lorsque cela est vraiment nécessaire (si leurs colonnes de base ont changé).

Cette optimisation, par exemple, peut accélérer considérablement la mise à jour des tables avec une colonne de type générée tsvector, car la fonction est to_tsvector()assez coûteuse.

Accès depuis le déclencheur à la liste des colonnes modifiées
Un petit patch qui ajoute un TriggerDatabitmap des colonnes modifiées à la structure . Ces informations peuvent être utilisées par des fonctions de déclenchement générales, telles que tsvector_update_trigger()ou lo_manage(), afin de ne pas faire de travail inutile.

Utilisation de plusieurs statistiques avancées lors de l'évaluation
Dans PG12, le planificateur n'était pas en mesure d'utiliser plusieurs statistiques avancées pour la même table en même temps. Par exemple, imaginez une situation dans laquelle deux statistiques avancées sont construites pour différents ensembles de colonnes et les colonnes d'un ensemble et d'un autre participent à la requête. Le planificateur a désormais accès à toutes les informations disponibles.

Infrastructure de parallélisation et COPY (voir aussi ce patch. )
La concurrence PostgreSQL fonctionne toujours pour les requêtes en lecture seule. Il y a des difficultés avec les écrivains, et l'un d'eux bloque les processus qui effectuent simultanément une tâche (inclus dans un groupe parallèle commun). On pense que les verrous de ces processus n'entrent pas en conflit - par exemple, plusieurs processus peuvent contenir un verrou exclusif sur la même table. Cela nécessite une attention particulière de la part des développeurs du noyau, mais sinon ils auraient constamment des blocages.
Mais il y a deux exceptions:

verrou d'extension de relation, qui est capturé lorsque de nouvelles pages sont ajoutées à la fin du fichier de données, et
verrou de page, qui est utilisé lors du déplacement des éléments d'index GIN de la liste d'attente vers l'arborescence principale.

(Vous pouvez en savoir plus dans cet article. )
De tels verrous devraient entrer en conflit même entre les processus du même groupe parallèle - qui implémente ce correctif. Mais ces verrous ne peuvent jamais conduire à des blocages, ils sont donc exclus de l'analyse.

Pour l'utilisateur, en général, rien ne change, mais ce correctif est important car, d'une part, il ouvre la voie à INSERT et COPY parallèles, et d'autre part, il élimine l'un des goulots d'étranglement de PostgreSQL dans des conditions de charge élevée (ce qui peut être entendu dans le rapport HL ++ ).

sécurité

Primes SKH PRH remplacées Primes
EDH remplacées (clés éphémères Diffie-Hellman) en utilisant le protocole SKIP, aujourd'hui disparu.

initdb: les paramètres par défaut pour l'authentification
ont changé Les paramètres d'accès par défaut pour les connexions locales et réseau ont changé au démarrage d'initdb. Maintenant,pg_hba.confpour les connexions locales au lieu de la méthode d'authentificationtrustserapeer(ou md5 si l'homologue n'est pas pris en charge), etmd5pour les connexions réseau. Dans un premier temps, des mesures plus libérales ont été discutées: avertissement dans la documentation. Puis plus difficile:scram-sha-256. En conséquence, nous avons décidé de nous limiter àpeeretmd5.

Utilisation de explicit_bzero
Patch important. Les fonctions du système d'exploitation bzero () et explicit_bzero () écrivent des octets contenant dans les zones de mémoire indiquées '\0'(voir, par exemple , Linux). Ces correctifs ne sont qu'un début: il existe de nombreuses sections de mémoire dans lesquelles les mots de passe et autres informations sensibles peuvent rester. Nous avons décidé de partir d'endroits comme libpq, dans lesquels l'intégralité du fichier contenant les mots de passe peut rester en mémoire après la lecture de .pgpass, et du nettoyage après la fermeture de la connexion. Dans be-secure-common.c, il y a maintenant un écrasement de la phrase secrète entrée dans SSL, qui apparaît dans la ligne (chemin) de l'erreur.

Ajout du paramètre "password_protocol" à libpq
Ce patch permet à libpq de contrôler le protocole de transfert de mot de passe utilisé lors de la connexion. Après avoir reçu ce paramètre, libpq refusera l'authentification si le protocole est plus faible que celui spécifié. Par défaut, ce paramètre plaintext, c'est-à-dire que tous les protocoles conviennent.

Accès obligatoire pour TRUNCATE
Ce correctif permet aux extensions d'incorporer le contrôle d'accès obligatoire (MAC) pour une opération TRUNCATE. Les droits seront désormais vérifiés par l'extension sepgsql . La politique de référence SELinux et les distributions Linux basées sur Redhat ne prennent pas en charge la vérification SELinux sur db_table {truncate}. Dans ce cas, sepgsql sera utilisé avec 'deny_unknown' égal à 1 et TRUNCATE échouera.

Disponibilité de GUC ssl_passphrase_command
Un patch simple mais utile. Désormais, la valeur du paramètre ssl_passphrase_command ne sera vue que par le superutilisateur. Le paramètre spécifie une commande externe qui est appelée lorsqu'un mot de passe est requis pour déchiffrer un fichier SSL, par exemple, une clé privée.

Localisation

Versionnement des règles de classement libc
Pour les règles de classement ICU, les numéros de version sont stockés dans la base de données. Chaque fois que la règle est utilisée (tri, comparaison de caractères), le numéro de version enregistré est vérifié avec la version actuelle dans la bibliothèque ICU du système d'exploitation, et en cas de divergences, un avertissement est émis. Cela vous permet de constater que certains index construits selon les règles de tri modifiées peuvent être incorrects et doivent être reconstruits. En reconstruisant les index avec la commandeALTER COLLATION ... REFRESH VERSION, la version de la règle de tri dans la base de données est mise à jour et les avertissements ne sont plus émis.

Mais c'était uniquement pour l'ICU. Maintenant, le numéro de version est également stocké pour les règles de tri libc:

SELECT collname, collversion FROM pg_collation WHERE collname = 'ru_RU';

 collname | collversion
----------+-------------
 ru_RU    | 2.27

Ce qui permet d'émettre des avertissements lorsqu'une bibliothèque change dans l'OS. Très pertinent à la lumière de la transition vers la glibc 2.28, où de nombreuses règles de tri ont changé et les index correspondants doivent être reconstruits.

Mais jusqu'à ce qu'ils passent à la version 2.28, tout est calme:

ALTER COLLATION "ru_RU" REFRESH VERSION;

NOTICE:  version has not changed
ALTER COLLATION

Recherche en texte intégral

Recherche plein texte pour la langue grecque
Pas de commentaires.

dict_int a appris à gérer les valeurs absolues.
Le dictionnaire de modèles dict_int (aka extension) a ajouté la possibilité de supprimer le signe d'un nombre.


CREATE EXTENSION dict_int;
SELECT ts_lexize('intdict', '-123');

ts_lexize
-----------
 {-123}
(1 row)

ALTER TEXT SEARCH DICTIONARY intdict (ABSVAL = true);
SELECT ts_lexize('intdict', '-123');

ts_lexize
-----------
 {123}
(1 row)

Autrement dit, cette fois, la valeur absolue a été reconnue.

Partitionnement

AVANT les
déclencheurs de ligneBEFOREsur une table partitionnéeDans la version 12, vous ne pouvez pas créer de déclencheurs de lignesur une table partitionnée. Sur des sections séparées - s'il vous plaît, mais pas sur toute la table à la fois. Désormais, unBEFORE FOR EACH ROWdéclencheur créé sur une table partitionnée sera automatiquement hérité et fonctionnera pour toutes les sections. Mais à la condition que si un déclencheur est activéUPDATE, la clé de partition ne peut être modifiée que dans la section actuelle.

Prise en charge des tables partitionnées dans la réplication logique
Auparavant, l'inclusion d'une table partitionnée dans une publication provoquait une erreur:

CREATE PUBLICATION pub FOR TABLE p;

ERROR:  "p" is a partitioned table
DETAIL:  Adding partitioned tables to publications is not supported.
HINT:  You can add the table partitions individually.

Maintenant ça marche.

Amélioration de l'algorithme JOIN sectionnel
À partir de la 11e version, le planificateur peut joindre les tables partitionnées section par section, mais uniquement si les limites des sections correspondent exactement. Maintenant, l'algorithme a été amélioré: il fonctionnera si la section d'une table est complètement incluse dans la section d'une autre, même si leurs tailles ne correspondent pas (par exemple, si une table est partitionnée par jour et l'autre par mois). Le nouvel algorithme fonctionne pour le partitionnement par plages et par listes.

Sectional FULL OUTER JOIN La jointure sectionnée
fonctionne maintenant pour les jointures externes complètes enregistrées avec une phrase USING.

tableam

Dans ce domaine attrayant et prometteur, mais difficile, il n'y a pas d'avancées radicales concernant PostgreSQL 12. Il n'y a pas de stockages de plug-ins prêts à l'emploi tels zheapque des tas), mais le travail continue sur l'API.

Un niveau d'abstraction plus élevé pour déterminer la taille de la table
Robert Haas a réécrit le code, changeant son architecture en faveur des couches abstraites, afin de ne pas dupliquer le code à l'avenir. Cette pièce fait référence à estimate_rel_size- la taille des couches (fourches) de la table.

Vous pouvez utiliser les méthodes d'accès aux tables avec relcache.
Ce correctif apporte les capacités de gestion de la mémoire des méthodes d'accès aux tables aux capacités des méthodes d'indexation.

tableam et TOAST
TOAST sont largement conçus pour le stockageheapPar conséquent, lors de la création de nouvelles méthodes d'accès aux tables, vous pouvez procéder de deux manières: aider les développeurs de nouvelles méthodes à y insérer, mettre à jour et supprimer des enregistrements TOAST ou déléguer le travail avec TOAST au code à l'aide du stockage traditionnel PostgreSQL - heap. Une série de 5 correctifs utilise des emplacements de tuple pour implémenter les opérations d'insertion / mise à jour / suppression et peut aider ceux qui vont dans les deux sens.

fsync

Gestion des erreurs fsync dans pg_receivewal et pg_recvlogical
La lutte contre fsync () continue. PostgreSQL estime qu'un appel fsync () réussi signifie que toutes les données du fichier ont été vidées sur le disque, mais cela ne se produit pas toujours (en fonction du système d'exploitation) et peut entraîner une perte de données. PG13 a décidé qu'il était nécessaire de traiter avec les services publicspg_receivewaletpg_recvlogical. Actuellement, le comportement par défaut est le suivant: ces utilitaires écriront des erreurs fsync dans les journaux, restaureront la connexion et continueront comme si de rien n'était. Par conséquent, le WAL contient des informations sur les fichiers qui ont été copiés avec succès et qui, en fait, n'ont pas été correctement vidés sur le disque. Il est donc préférable d'interrompre l'utilitaire. Le sort de pg_dump, pg_basebackup, pg_rewind et pg_checksums a également été discuté, mais jusqu'à présent, ils se sont limités à ces deux.

Protection contre la définition d'indicateurs incorrects pour fsync ()
Ce correctif vérifie si les indicateurs sont définis correctement lors de la réception du descripteur de fichier pour fsync () - les répertoires sont ouverts uniquement en lecture et les fichiers en écriture ou les deux.

Sauvegarde et réplication

Pause pendant la récupération avant d'atteindre le point de récupération
Si, pendant la récupération, les WAL sont terminésrecovery_target_timeet n'ont pas atteint celuispécifié, le serveur termine la récupération et passe en mode de fonctionnement normal. Maintenant, il n'en sera plus ainsi. Le processus de récupération s'arrêtera, comme indiqué dans le journal, et l'administrateur aura la possibilité d'insérer les segments WAL manquants et de poursuivre la récupération.

Le paramètre ignore_invalid_pages
Lorsque le processus de récupération sur une réplique trouve un lien vers une page non valide dans l'enregistrement WAL,panic-ase produit. L'inclusion du paramètre aidera à le surmonter.ignore_invalid_pages. La récupération se poursuivra avec une perte possible d'intégrité, de données et d'autres conséquences les plus graves. Le paramètre est destiné aux développeurs de serveurs et doit être utilisé dans les cas où vous devez encore essayer de terminer la récupération et de démarrer la réplique.

Changer primary_conninfo sans redémarrer
le patch de Sergey Kornilov, qui vous permet de changer les paramètres primary_conninfo, primary_slot_nameet wal_receiver_create_temp_slotsans redémarrer le serveur. En fait, pour cela, ils ont abandonné le fichier recovery.confdans la 12e version. Les manifestes de

sauvegarde
Pg_basebackup créent maintenant un «manifeste» - un fichier JSON contenant des informations sur la sauvegarde effectuée (noms et tailles des fichiers, fichiers WAL nécessaires, ainsi que des sommes de contrôle pour tout et tout).
Le nouvel utilitaire pg_validatebackup vérifie la conformité des sauvegardes avec le manifeste et vérifie également la disponibilité et l'exactitude des fichiers WAL nécessaires à la récupération à l'aide de l'utilitaire pg_waldump (cela s'applique uniquement aux fichiers WAL à l'intérieur de la sauvegarde elle-même, et non dans l'archive).
Cela vous permettra de détecter les situations où les fichiers de sauvegarde ont été endommagés ou ont disparu, ou lorsque la récupération est devenue impossible en raison du manque de fichiers journaux nécessaires.

Limitation de l'emplacement de réplication des données non lues L'emplacement de réplication
est un mécanisme pratique mais dangereux: si le client ne lit pas les données de l'emplacement à temps, les enregistrements WAL non lus peuvent occuper tout l'espace du serveur. Maintenant en utilisant le paramètremax_slot_wal_keep_sizeVous pouvez définir une limite sur la quantité maximale d'espace disque pouvant être occupée par des données non lues. Si au prochain point de contrôle, il s'avère que la taille est dépassée, la fente est désactivée et la place est libérée.

les fenêtres

Prise en charge des sockets Unix sur Windows Les
sockets de domaine Unix sont pris en charge sur Windows 10, bien qu'ils soient désactivés par défaut.

Documentation

Il y a deux nouvelles applications dans la documentation.
Après une longue discussion , l' annexe M. Glossaire est apparue . Le glossaire compte actuellement 101 termes.

La possibilité de mettre en évidence la couleur des messages de diagnostic des utilitaires de console à l'aide d'une variable PG_COLORétait antérieure. Ceci est maintenant documenté dans l' annexe N. Prise en charge des couleurs . L'intention originale de Peter Eisentrout dans ce patch était de rendre la sortie colorisée activée par défaut. Et pour ceux qui ne le voulaient pas, il a été proposé de définir explicitement la variableNO_COLOR. Mais il y avait plus d'opposants à la différenciation des couleurs des messages parmi ceux qui discutaient du patch. Par conséquent, ils ont décidé de documenter uniquement les opportunités disponibles. Et nous avons obtenu une nouvelle section du premier niveau dans la documentation.

PG13, , PG14 . , . .

Quelle est la nouveauté de la Devil's Dozen?