Voici quelques questions pour commencer:
- Les plages peuvent-elles contenir des lacunes dans les valeurs?
- Pourquoi avons-nous besoin d'une notation d'index pour le type json?
- Un index peut-il augmenter moins qu'une table avec des mises à jour fréquentes? Et ne pas grandir du tout?
- Combien de temps les sessions sont-elles restées inactives dans idle_in_transaction?
- Comment créer un diagramme ER pour les tables de catalogue système?
Applications client
psql: \ dX - afficher les statistiques étendues
commit: ad600bba La
nouvelle commande \ dX affiche les objets statistiques étendus sous forme de liste.
CREATE STATISTICS flights_from_to (dependencies, MCV)
ON departure_airport, arrival_airport
FROM flights;
\x
\dX
List of extended statistics -[ RECORD 1 ]+------------------------------------------------ Schema | bookings Name | flights_from_to Definition | departure_airport, arrival_airport FROM flights Ndistinct | Dependencies | defined MCV | defined
Pour chaque type de statistiques (Dépendances, Ndistinct, MCV), seul le fait de collecte est affiché. Les valeurs elles-mêmes doivent être examinées dans pg_statistic_ext_data, qui, par défaut, n'est accessible qu'aux super-utilisateurs.
psql: \ dtS montre les tables TOAST
commit: 7d80441d
Une table TOAST distincte pouvait être affichée avec la commande \ d auparavant. Cependant, il n'a pas été possible d'obtenir une liste de ces tables avec la commande \ dt ou \ dtS. L'omission a été corrigée, \ dtS affiche maintenant les tables TOAST car ce sont des tables de service.
Mais il y a une mise en garde. Toutes les tables TOAST se trouvent dans le schéma pg_toast, qui est peu susceptible d'être inclus dans search_path . Par conséquent, pour obtenir la liste, vous devez spécifier le modèle approprié:
\dtS pg_toast.*165*
List of relations Schema | Name | Type | Owner ----------+----------------+-------------+---------- pg_toast | pg_toast_16529 | TOAST table | postgres pg_toast | pg_toast_16539 | TOAST table | postgres pg_toast | pg_toast_16580 | TOAST table | postgres
Amélioration de la complétion de l'onglet Psql pour les commandes CLOSE, FETCH, MOVE et DECLARE
commit: 3f238b88 Aucune
description supplémentaire requise.
Documentation
Relecture et édition de la documentation
commit: 2a5862f0
Beaucoup de gens notent que PostgreSQL a une excellente documentation. Mais il est écrit par les développeurs eux-mêmes, qui ne sont généralement pas considérés comme des maîtres du stylo. Comment parvenez-vous à maintenir une qualité élevée? C'est simple. Comme pour toute écriture, vous avez besoin d'éditeurs et de correcteurs. Et donc Justin Prizzby fait un travail énorme et important depuis deux ans: relire la documentation. Le résultat est une grande liste de 18 patchs. Et Mikael Paquier, en tant que committer, l'a aidé.
Et ce n'est qu'un gros engagement. Et le nombre de petits correctifs qui améliorent la documentation ne peut tout simplement pas être compté.
miscellanea
Paramètre Idle_session_timeout - force la fin des sessions inactives
commit: 9877374b Le
nouveau paramètre idle_session_timeout spécifie le délai d'expiration de la session inactive. Si le délai défini est dépassé, la session sera interrompue. Le paramètre est très similaire à idle_in_transaction_session_timeout ,qui est apparu dans 9.6 , mais n'affecte que les sessions dans lesquelles il n'y a pas de transaction démarrée. Par conséquent, si vous souhaitez interrompre les sessions inactives, qu’une transaction y soit démarrée ou non, les deux paramètres doivent être définis.
Il est recommandé d'utiliser ce paramètre avec une extrême prudence sur les systèmes qui utilisent des extracteurs de connexion ou des connexions postgres_fdw.
Le paramètre peut être défini par n'importe quel utilisateur pour sa session. Dans l'exemple suivant, après avoir défini le paramètre et attendu une seconde, nous voyons qu'une entrée de fin de session apparaît dans le journal du serveur. Après cela, la tentative d'exécution de la demande échoue, mais psql établit automatiquement une nouvelle connexion:
SET idle_session_timeout = '1000ms';
--
\! tail -n 1 logfile
2021-02-01 12:25:06.716 MSK [5262] FATAL: terminating connection due to idle-session timeout
SHOW idle_session_timeout;
FATAL: terminating connection due to idle-session timeout server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.
SHOW idle_session_timeout;
idle_session_timeout ---------------------- 0
Description faite par depesz.
Informations GSS dans le message du journal du serveur
commit: dc11f31a Le
nouveau message de connexion de session dans le journal du serveur a été mis à jour avec les informations GSS si cette méthode d'authentification a été utilisée.
pageinspect: fonctions pour les index GiST
commit: 756ab291
Pour tous ceux qui souhaitent explorer l'organisation et le stockage des index GiST, l'extension pageinspect offre de nouvelles fonctionnalités .
Corriger le comportement EXPLAIN dans les commandes avec le
commit IF NOT EXISTS : e665769e
Une tentative de création d'une table existante avec l'option IF NOT EXISTS entraîne un avertissement:
CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping
Cependant, obtenir un plan pour une telle commande conduit à des résultats inattendus. C'est juste qu'EXPLAIN produit un plan pour le SELECT, que la commande parvient à construire avant de vérifier l'existence de la table des tickets. Et pas d'avertissement!
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
QUERY PLAN ----------------------------------------------------------------- Seq Scan on bookings (cost=0.00..4301.88 rows=262788 width=21)
EXPLAIN ANALYZE échoue au lieu d'avertir:
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
ERROR: relation "tickets" already exists
En version 14, le comportement est devenu prévisible:
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
Mêmes modifications pour la commande EXPLAIN [ANALYZE] CREATE MATERIALIZED VIEW IF NOT EXISTS.
Ajout de clés primaires et uniques aux tables de catalogue système.
Commit: dfb75e47 , 62f34097
Ajout de contraintes d'intégrité aux tables de catalogue système: clés primaires et uniques. Il y avait des indices uniques avant, maintenant les restrictions sont faites sur leur base.
Voici à quoi ça ressemble:
\d pg_class
Table "pg_catalog.pg_class" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- oid | oid | | not null | relname | name | | not null | relnamespace | oid | | not null | ... ... Indexes: "pg_class_oid_index" PRIMARY KEY, btree (oid) "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace) "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
Mais il y a des exceptions: pg_depend, pg_shdepend. Ces deux tables ont deux index et les deux ne sont pas uniques. Il n'a pas été possible de proposer une combinaison unique de colonnes. Je dois admettre que comprendre le fonctionnement de la table de dépendances n'est pas une tâche triviale. Et l'impossibilité de créer une clé primaire en est une confirmation claire.
Mais les clés étrangères n'ont pas été ajoutées. Il y a de bonnes raisons pour ça:
- Pour un certain nombre de tables, les colonnes oid peuvent être 0 lorsqu'il n'y a pas de véritable OID auquel se référer. Pour créer une clé étrangère, vous devrez remplacer 0 par NULL partout, et c'est un énorme travail de réécriture que vous n'êtes pas prêt à entreprendre.
- Un certain nombre de tables ont une colonne avec une clé étrangère potentielle non seulement de type oid, mais oid []. Il est impossible de créer une clé étrangère à partir d'un tableau.
L'idée originale du correctif était de créer un diagramme des relations entre les tables du catalogue système à partir des informations de la base de données. Cela pourrait être fait automatiquement par des outils externes. Après tout, la construction d'un diagramme sans informations sur les clés étrangères ne peut être effectuée que manuellement et avec des modifications régulières après chaque version, ce qui est extrêmement gênant.
Se rendant compte que la tâche du correctif n'était pas terminée, immédiatement après la validation, Tom Lane a lancé une nouvelle discussion dans laquelle il a proposé une solution de compromis sous la forme d'une fonction qui renvoie une liste de clés étrangères pour toutes les tables du catalogue système. Le patch a été adopté après la clôture du commitfest de janvier, mais il est plus logique de le décrire maintenant.
Ainsi, les tables du catalogue système n'ont pas de clés étrangères. Mais nous pouvons obtenir des informations à leur sujet en appelant la fonction pg_get_catalog_foreign_keys. La requête suivante montre qui fait référence à pg_attribute:
SELECT fktable, fkcols, is_array, is_opt
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_attribute'::regclass
AND pkcols = ARRAY['attrelid','attnum'];
fktable | fkcols | is_array | is_opt ----------------------+-----------------------+----------+-------- pg_attrdef | {adrelid,adnum} | f | f pg_constraint | {conrelid,conkey} | t | t pg_constraint | {confrelid,confkey} | t | f pg_index | {indrelid,indkey} | t | t pg_statistic_ext | {stxrelid,stxkeys} | t | f pg_statistic | {starelid,staattnum} | f | f pg_trigger | {tgrelid,tgattr} | t | f pg_partitioned_table | {partrelid,partattrs} | t | t (8 rows)
surveillance
Paramètre Log_recovery_conflict_waits - consignation des longues attentes pour résoudre les conflits de récupération
commit: 0650ff23
Lorsque le nouveau paramètre log_recovery_conflict_waits est activé , l'attente de résolution des conflits de récupération par le processus de démarrage sera consignée dans le journal du serveur si le délai dépasse deadlock_timeout.
Simulons la situation. Sur la réplique, activez le paramètre, puis démarrez la transaction et attendez:
ALTER SYSTEM SET log_recovery_conflict_waits = on;
SELECT pg_reload_conf();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;
Et maintenant sur le maître:
DELETE FROM t;
VACUUM t;
Après une seconde ( deadlock_timeout ), un message détaillé décrivant le conflit apparaîtra dans le journal du réplica. Voici le numéro du processus en conflit, et la position LSN où ils sont restés bloqués, et le nom du fichier (lire les tables) avec le numéro de bloc:
LOG: recovery still waiting after 1023.267 ms: recovery conflict on snapshot DETAIL: Conflicting process: 29119. CONTEXT: WAL redo at 0/1001BEB0 for Heap2/CLEAN: latestRemovedXid 717; blkref #0: rel 1663/16384/17198, blk 0
Après 30 secondes supplémentaires d'attente ( max_standby_streaming_delay ), la session sur la réplique sera terminée, comme il se doit dans de tels cas.
Ceci est une continuation du travail commencé et décrit dans le commitfest précédent.
Vue Pg_stat_database - statistiques ajoutées sur les sessions utilisateur
commit: 960869da Les
développeurs de systèmes de surveillance ont plus de travail à faire. Des changements utiles et sympas attendent les utilisateurs des systèmes de surveillance!
De nombreuses colonnes sont apparues dans pg_stat_database avec des informations supplémentaires sur les sessions utilisateur dans chaque base de données du cluster:
- session_time - temps total de toutes les sessions passées dans cette base de données;
- active_time ― , ;
- idle_in_transaction_time ― ;
- sessions ― ;
- sessions_abandoned ― - ;
- sessions_fatal ― - FATAL;
- sessions_killed ― .
Description faite par depesz.
ps: mise à jour de l'état des processus lorsqu'un point de contrôle est exécuté
commit: df9274ad Vous pouvez
surveiller les processus de démarrage et de point de contrôle en interrogeant pg_stat_activity. Mais il existe trois situations où la vue pg_stat_activity n'est pas disponible et le pointeur de contrôle fonctionne. Il s'agit du point de contrôle à la fin du processus de récupération après incident, du point de contrôle lors de l'arrêt du serveur et du point de redémarrage lors de l'arrêt de la réplique.
Dans ces trois situations, vous pouvez surveiller l'état des processus de démarrage et de point de contrôle dans le système d'exploitation, par exemple à l'aide de l'utilitaire ps.
Un exemple typique est la reprise après sinistre. En fin de compte, après avoir reporté les modifications de WAL, le processus de démarrage effectue un point de contrôle et cela peut prendre un certain temps. Cependant, l'état du processus de démarrage ne change pas et affiche "récupération NNN". Bien qu'il serait utile de savoir que la reprise des modifications est terminée et qu'il reste à attendre l'achèvement du point de contrôle. L'état est maintenant mis à jour pour abaisser le niveau d'alerte du DBA en cas d'urgence.
pg_stat_statements: Lorsque les statistiques de
validation ont été vidées : 2e0fedf0
Il n'y a aucun doute que les statistiques de pg_stat_statements devraient être vidées régulièrement. Sinon, à quoi bon accumuler des informations sur les requêtes exécutées hier, il y a une semaine, un mois, un an ...
Mais comment savoir quand les statistiques ont été réinitialisées pour la dernière fois? Très simple. Nous examinons pg_stat_statements_info:
SELECT now(), pg_stat_statements_reset();
now | pg_stat_statements_reset -------------------------------+-------------------------- 2021-02-03 13:25:44.738188+03 |
SELECT * FROM pg_stat_statements_info;
dealloc | stats_reset ---------+------------------------------- 0 | 2021-02-03 13:25:44.738468+03
La vue pg_stat_statements_info a été introduite dans la version 14. Vous pouvez en savoir plus sur la colonne dealloc dans l'article précédent.
Description faite par depesz.
Encore plus utile est l'idée non seulement de vider les statistiques régulièrement, mais aussi de sauvegarder le contenu de pg_stat_statements avant chaque vidage. Ensuite, en présence de nombreuses tranches régulièrement réalisées, il est possible d'obtenir des informations sur des intervalles de temps dans le passé. Cette approche est utilisée par l' extension de surveillance pgpro_pwr . Progression du commit
COPY
: 8a4f618e
La famille de vues pg_stat_progress_ * a été mise à jour! Vous pouvez maintenant surveiller la progression de la commande COPY.
Faisons une copie logique de la base de données de démonstration:
\! pg_dump -d demo -Fc -f demo.dump
Maintenant, développons la copie dans la base de données postgres en deux threads et, pendant que le processus se poursuit, jetons un œil à la vue pg_stat_progress_copy:
\! pg_restore tickets.dump -d postgres -j 2 &
SELECT pid, datname, relid::regclass, bytes_processed, bytes_total, lines_processed
FROM pg_stat_progress_copy\gx
-[ RECORD 1 ]---+------------------------- pid | 18771 datname | postgres relid | bookings.tickets bytes_processed | 19088527 bytes_total | 0 lines_processed | 189820 -[ RECORD 2 ]---+------------------------- pid | 18772 datname | postgres relid | bookings.boarding_passes bytes_processed | 14833287 bytes_total | 0 lines_processed | 567652
La colonne bytes_total aurait été remplie avec la taille du fichier lorsque la commande COPY… FROM 'file' a été exécutée. Mais dans l'exemple ci-dessus, le téléchargement provient d'une copie de pg_dump, donc la taille n'est pas connue.
L'état peut être surveillé non seulement pour le téléchargement (COPY… FROM), mais aussi pour le téléchargement (COPY… TO) des données.
Description faite par depesz.
Performance
Optimisation du vidage du
commit du cache tampon : d6ad34f3 , bea449c6
Un certain nombre d'opérations nécessitent que tous les tampons associés à une table particulière soient supprimés du cache tampon. Ces opérations incluent les commandes TRUNCATE et DROP table, une commande CREATE TABLE AS SELECT interrompue et VACUUM lorsque des blocs vides doivent être supprimés de la fin d'une table.
Pour le supprimer, l'intégralité du cache tampon est analysé, ce qui peut être coûteux avec de grandes tailles de cache. Désormais, pour les petites tables, une structure spéciale sera maintenue en mémoire avec des informations sur les tampons occupés, ce qui évitera d'analyser tout le cache de tampons.
Les tests ont montré qu'avec des shared_buffers de 100 Go ou plus, la troncature de milliers de tables est plus de 100 fois plus rapide.
Il s'agit de la suite du travail entamé dans la version 13.
postgres_fdw: mode batch pour l'insertion des enregistrements de
commit: b663a413 Récupération des
données à partir de tables externes postgres_fdw utilise le mode batch. Les enregistrements sont transférés depuis le serveur externe par lots de 100 (la valeur par défaut du paramètre fetch_size ). C'est beaucoup plus rapide que de les envoyer un à la fois. Mais insérez, modifiez, supprimez le travail ligne par ligne. Et donc très lentement.
L'API FDW a été améliorée pour l'optimisation. Certes, il n'a été amélioré que dans la partie du mode batch pour les opérations d'insertion. Les mises à jour et les suppressions sont pour référence future. Bien sûr, postgres_fdw a été le premier wrapper à tirer parti de la nouvelle API.
Voyons ce qui se passe. Configurez postgres_fdw pour qu'il fonctionne avec des tables externes dans la base de données de démonstration:
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'postgres');
CREATE USER MAPPING FOR postgres
SERVER remote_server
OPTIONS (user 'postgres');
La table externe sera située dans la base de données postgres voisine:
postgres=# CREATE TABLE bookings(
book_ref char(6),book_date timestamptz, total_amount numeric(10,2)
);
Prenons la vitesse d'insertion dans la table locale comme vitesse de référence. Et activez la synchronisation des mesures:
CREATE TABLE bookings_local (LIKE bookings);
\timing
Insérer dans une table locale:
INSERT INTO bookings_local SELECT * FROM bookings;
INSERT 0 262788 Time: 165,653 ms
Et maintenant insérer dans une table externe, le mode batch est désactivé. (En fait, il est activé, par défaut, la taille du lot est de 1 ligne).
CREATE FOREIGN TABLE bookings_remote_no_batch (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings'
);
INSERT INTO bookings_remote_no_batch SELECT * FROM bookings;
INSERT 0 262788 Time: 6729,867 ms (00:06,730)
Presque 40 fois plus lent! Et c'est entre les bases du même cluster, où il n'y a pas de retards réseau.
Répétons l'expérience, mais définissons la taille du lot (batch_size) sur 100.
CREATE FOREIGN TABLE bookings_remote_batch_100 (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings',
batch_size '100'
);
INSERT INTO bookings_remote_batch_100 SELECT * FROM bookings;
INSERT 0 262788 Time: 679,632 ms
Une autre affaire. Bien sûr, la perte d'insertion locale est toujours perceptible, ~ 4 fois, mais toujours pas 40!
Et enfin. La taille du lot à insérer (batch_size) pourrait être définie dans les paramètres du serveur externe, alors elle serait valable pour toutes les tables dans lesquelles elle n'est pas explicitement définie.
Suppression des lignes d'index de bas en haut
commit: 9dc718bd , d168b666
Cette optimisation tente d'éviter de diviser la page d'index par deux dans les opérations UPDATE jusqu'à la dernière, dans les situations où les colonnes d'index n'ont pas changé. Avant d'ajouter une nouvelle version d'une ligne à l'index, vous devez voir si vous pouvez supprimer les lignes inutiles sur cette page. Par exemple, si vous trouvez une chaîne de lignes d'index en double inutiles qui font référence à la même ligne de table, vous pouvez supprimer ces lignes. Peter Geigan, l'auteur du patch, a appelé cette «suppression ascendante».
Un problème similaire (pour éviter la croissance de l'indice) est résolu par l'optimisation de la mise à jour HOT. Si UPDATE ne modifie aucune des colonnes indexées, les nouvelles versions des lignes des index ne peuvent pas être créées. Et s'il y a plusieurs index sur la table et que la colonne d'un seul d'entre eux change? Dans ce cas, la mise à jour HOT n'est pas un assistant.
Voyons ce que peut faire "supprimer de bas en haut". Pour l'expérience, prenons un tableau avec deux colonnes indexées séparément et le nettoyage automatique désactivé.
CREATE TABLE t(col1 int, col2 int) WITH (autovacuum_enabled=off);
CREATE INDEX t_col1 ON t(col1);
CREATE INDEX t_col2 ON t(col2);
INSERT INTO t VALUES (1, 1);
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size --------+-------------+------------- 8192 | 16384 | 16384
Il y a une ligne dans le tableau avant la mise à jour groupée. La taille de la table est d'une page et les deux index occupent deux pages (page de service + page de données).
Maintenant, nous changeons une seule colonne col2 100 000 fois et examinons la taille de la table et des index.
SELECT 'UPDATE t SET col2 = col2+1' FROM generate_series(1,100000)\gexec
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 2121728 | 2260992
Ces résultats ont été obtenus dans PostgreSQL 12. Comme vous pouvez le voir, la mise à jour HOT n'a pas fonctionné et les deux index ont augmenté de taille presque égale.
Maintenant, cette même expérience dans PostgreSQL 13:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 663552 | 2260992
L'index t_col1, dans lequel il n'y a eu aucun changement, a augmenté beaucoup moins, d'environ 3,5 fois. C'est le résultat d'une célèbre optimisation de la version 13: la déduplication d'index . Mais néanmoins il a grandi.
Et enfin, voyons ce qu'il y a dans PostgreSQL 14:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 16384 | 2260992
Blimey! Dans l'index t_col1, il ne reste qu'une seule page avec des données. C'est super!
Bien sûr, si l'autovacuum était activé, il pourrait avoir le temps d'effacer certaines des lignes mortes pendant l'expérience. Mais c'est à cela que sert l'expérience. De plus, en conditions réelles, avec des mises à jour fréquentes (un bon exemple est les tables de files d'attente), l'autovacuum n'aura certainement pas le temps de tout nettoyer à temps.
Description de Viktor Egorov.
Exécution parallèle REINDEX CONCURRENTLY
commit: f9900df5
Dans l'article de novembre commitfest, j'ai déjà écrit sur l'exécution parallèle non bloquante de CREATE INDEX CONCURRENTLY. Une optimisation similaire est désormais disponible pour REINDEX CONCURRENTLY.
Langages procéduraux
Les procédures sont plus rapides à exécuter la
validation: les procédures ee895a65
ont été conçues pour pouvoir effectuer des transactions. Si une telle procédure qui exécute COMMIT est elle-même appelée plusieurs fois, par exemple, dans une boucle, alors à chaque itération de la boucle, toutes les instructions à l'intérieur de la procédure seront ré-analysées.
Il n'y avait aucune raison sérieuse de ré-analyser les commandes, ce qui a été éliminé dans le patch. Désormais, l'appel de procédures en boucle nécessite moins de travail et de ressources. Et, par conséquent, il fonctionne plus vite.
PL / pgSQL: opérateur d'assignation entièrement repensé
commit: 844fe9f1 , c9d52984 , 1788828d , 1c1cbe27
Sans plus tarder:
DO $$
<<local>>
DECLARE
a bookings[];
x bookings;
BEGIN
/* */
local.a[1].book_ref := 'ABCDEF';
local.a[1].book_date := current_date;
local.a[1].total_amount := 0;
/* */
local.a[2:3] := (SELECT array_agg(t.*)
FROM (SELECT b.* FROM bookings b LIMIT 2) AS t
);
FOREACH x IN ARRAY a LOOP
RAISE NOTICE '%', x;
END LOOP;
END;
$$;
NOTICE: (ABCDEF,"2021-02-04 00:00:00+03",0.00) NOTICE: (00000F,"2017-07-05 03:12:00+03",265700.00) NOTICE: (000012,"2017-07-14 09:02:00+03",37900.00) DO
Désormais, dans un bloc PL / pgSQL, vous pouvez attribuer des valeurs aux éléments de tableau d'un type composite, ainsi qu'aux tranches de tableau.
Pour cela, l'opérateur d'affectation PL / pgSQL a été entièrement repensé. Et l'analyseur de serveur a appris à analyser les expressions PL / pgSQL.
Pour évaluer une expression, vous n'avez plus besoin d'émettre une commande comme
«
SELECT expr
»
. Vous pouvez facilement le vérifier en regardant le message d'erreur dans l'exemple suivant:
DO $$ BEGIN RAISE NOTICE '%', 2 + 'a'; END; $$;
ERROR: invalid input syntax for type integer: "a" LINE 1: 2 + 'a' ^ QUERY: 2 + 'a' CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE
Le mot SELECT n'est plus dans la ligne QUERY.
Réplication
Gestion des répliques des modifications des paramètres de configuration dans l'assistant
commit: 15251c0a
Les modifications apportées par l'assistant aux paramètres qui affectent la taille de la mémoire partagée du serveur ne passent pas inaperçues sur les répliques physiques. Lorsqu'un enregistrement WAL arrive sur le réplica indiquant que ces paramètres ont changé, le réplica s'arrête automatiquement, mettant fin à toutes les sessions en cours. La liste des paramètres se trouve dans la documentation .
Ce n'est pas très bon. Par conséquent, nous avons fait ceci: en recevant un enregistrement des modifications des paramètres, la réplique continue de fonctionner, mais interrompt la réplication et envoie un message au journal. L'administrateur peut attendre la fin des sessions importantes, mettre à jour les paramètres dans les fichiers de configuration pour qu'ils correspondent aux valeurs sur le maître et réactiver la réplication. Le réplica s'arrêtera immédiatement après cela, mais comme les modifications de configuration ont déjà été apportées, il peut être démarré immédiatement, ce qui minimise les temps d'arrêt.
Changez restore_command sans redémarrer le serveur
commit: 942305a3
Poursuite du travail de Sergei Kornilov, adopté dans la version 13. Ensuite, il est devenu possible de modifier les paramètres sans redémarrer le serveur primary_conninfo , primary_slot_name et wal_receiver_create_temp_slot .
Maintenant, restore_command leur a été ajouté .
Serveur
Utilisation améliorée du
commit de statistiques étendues : 25a9e54d
Les statistiques étendues sont désormais utilisées dans plus de cas pour évaluer la cardinalité des conditions dans les requêtes. En particulier, les statistiques étendues seront désormais utilisées lorsque différentes conditions pour lesquelles des statistiques étendues peuvent être utilisées individuellement sont combinées via OR.
Dans l'exemple, nous collecterons des statistiques étendues pour les aéroports de départ et d'arrivée. Et puis nous compterons le nombre de vols entre Sheremetyevo et Pulkovo ou dans la direction opposée.
CREATE STATISTICS s ON departure_airport, arrival_airport FROM flights;
ANALYZE flights;
Le nombre exact de vols est de 610. Comparez avec les estimations du planificateur dans les versions 13 et 14.
EXPLAIN SELECT *
FROM flights
WHERE (departure_airport = 'SVO' AND arrival_airport = 'LED')
OR (departure_airport = 'LED' AND arrival_airport = 'SVO');
PostgreSQL 13:
Seq Scan on flights (cost=0.00..1054.42 rows=885 width=63)
PostgreSQL 14:
Seq Scan on flights (cost=0.00..1054.42 rows=607 width=63)
Comme vous pouvez le voir, l'estimation de la version 14 est presque exacte.
Infrastructure générale pour la prise en charge de la notation d'index pour n'importe quel type de données
commit: c7aba7c1 , 0ec5f7e7 , 676887a3 La
notation d'index est utilisée pour travailler avec des tableaux. Par exemple, trouvons l'élément avec l'index 3:
SELECT (ARRAY[10,20,30,40,50])[3];
array ------- 30
Mais il existe d'autres types de données où cette syntaxe serait pratique à utiliser. Tout d'abord, nous parlons de json. C'est avec l'idée de soutenir la notation d'index pour json que le long voyage de travail de Dmitry Dolgov sur ce patch a commencé.
Et maintenant, quelques années plus tard, un tel soutien est apparu. Le premier correctif crée l'infrastructure de notation d'index nécessaire pour les types de données arbitraires. Le deuxième patch ajoute la notation d'index au type hstore et le troisième au type json b .
Désormais, au lieu de fonctions et d'opérateurs spéciaux, vous pouvez extraire les parties nécessaires de la valeur json. Trouvons le numéro de téléphone dans les coordonnées de l'un des billets:
SELECT contact_data, contact_data['phone'] AS phone
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]+----------------------------------------------------------------------------------------------------------------------------------------------------------- contact_data | {"email": "antonova.irina04121972@postgrespro.ru", "phone": "+70844502960"} phone | "+70844502960"
La notation d'index peut également être utilisée pour écrire dans jsonb. Ajoutons l'adresse au contact précédemment trouvé d'Irina Antonova:
UPDATE tickets
SET contact_data['address'] =
'{"city": "",
"street": " ",
"building": "7"
}'::jsonb
WHERE ticket_no = '0005432000994';
Notez que l'adresse elle-même est composée et vous pouvez également utiliser la notation d'index pour faire référence à des parties de celle-ci:
SELECT contact_data['address'] AS address,
contact_data['address']['city'] AS city,
contact_data['address']['street'] AS street,
contact_data['address']['building'] AS building,
contact_data['phone'] AS phone,
contact_data['email'] AS email
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]---------------------------------------------------------------- address | {"city": "", "street": " ", "building": "7"} city | "" street | " " building | "7" phone | "+70844502960" email | "antonova.irina04121972@postgrespro.ru"
C'est très pratique!
(Clarification. Tous les contacts de la base de données de démonstration sont fictifs et il n'y en a pas dans Postgres Pro.)
Description de hstore de depesz.
Commandes SQL
Validation des types de données multi- plages
: 6df7a969
Chaque type de données plage a désormais son propre type de données multi-plages . Ce type est essentiellement un tableau de plages individuelles. Les plages d'un type multibande ne doivent pas se chevaucher, mais il peut y avoir des espaces entre les plages.
Les plages régulières sont des plages contiguës de valeurs du sous-type correspondant: plage in4range pour le sous-type int, plage timestamptz pour le sous-type d'horodatage, etc. Mais que faire si vous avez besoin de stocker des gammes avec des espaces à certains endroits? C'est là que les multi-bandes viennent à la rescousse.
Disons que nous voulons stocker les heures de commitfest dans la table pour chaque version de PostgreSQL. Un seul commitfest peut être considéré comme une durée d'un mois. Mais comment représenter les cinq commitfests d'une seule version?
La plage du sous-type timestamptz est appelée tstzrange et la plage multiple est tstzmultirange. Les types disponibles sont décrits dans la documentation . Créez un tableau:
CREATE TABLE pg_commitfest (
version text,
working_period tstzmultirange
);
Pour former des valeurs, nous utilisons le constructeur:
INSERT INTO pg_commitfest VALUES
('13', tstzmultirange(
tstzrange('2019-07-01', '2019-08-01', '[)'),
tstzrange('2019-09-01', '2019-10-01', '[)'),
tstzrange('2019-11-01', '2019-12-01', '[)'),
tstzrange('2020-01-01', '2020-02-01', '[)'),
tstzrange('2020-03-01', '2020-04-07', '[]')
)
),
('14', tstzmultirange(
tstzrange('2020-07-01', '2020-08-01', '[)'),
tstzrange('2020-09-01', '2020-10-01', '[)'),
tstzrange('2020-11-01', '2020-12-01', '[)'),
tstzrange('2021-01-01', '2021-02-01', '[)'),
tstzrange('2021-03-01', '2021-04-01', '[)')
)
);
La liste des fonctions et des opérateurs pour travailler avec les types multi-bandes comprend les mêmes que pour les bandes normales, plus celles destinées uniquement aux multi-bandes.
Par exemple, nous pouvons savoir sur quelle version de PostgreSQL la communauté des développeurs a travaillé au cours de la dernière année:
SELECT version
FROM pg_commitfest
WHERE working_period @> '2021-01-01'::timestamptz;
version --------- 14
Ou dates de début et de fin des travaux sur la version 13:
SELECT lower(working_period), upper(working_period)
FROM pg_commitfest
WHERE version = '13';
lower | upper ------------------------+------------------------ 2019-07-01 00:00:00+03 | 2020-04-07 00:00:00+03
De nouveaux types multi-plages personnalisés peuvent être créés. Ceci est utile dans les cas où il n'y a pas de bande intégrée et de type multibande correspondant. La même commande CREATE TYPE… AS RANGE est utilisée, dans laquelle vous pouvez spécifier un nom pour le type multi-plages généré automatiquement.
Par exemple, nous nous intéressons aux plages et multi-plages de temps, sous-type de temps. Pour créer une plage, vous avez besoin d'une fonction qui calcule la différence entre deux valeurs de type time:
CREATE FUNCTION time_diff(a time, b time) RETURNS double precision
AS $$
SELECT extract(epoch FROM (a - b));
$$ LANGUAGE sql STRICT IMMUTABLE;
Nous créons un type pour la plage de temps, et en même temps pour la multi-plage:
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_diff,
multirange_type_name = timemultirange
);
Maintenant, le temps de travail peut être formé par l'expression suivante:
SELECT timemultirange(
timerange('09:00', '13:00', '[)'),
timerange('14:00', '18:00', '[)')
) AS working_hours;
working_hours ------------------------------------------- {[09:00:00,13:00:00),[14:00:00,18:00:00)}
Description faite par depesz.
Fonctions Ltrim et rtrim pour les chaînes binaires
commit: a6cf3df4 Vous pouvez
également couper les octets au début et à la fin d'une chaîne bytea en même temps en utilisant la fonction btrim. Vous pouvez maintenant couper chaque bord séparément avec les nouvelles fonctions ltrim et rtrim pour les chaînes binaires.
Phrase GRANTED BY dans les commandes GRANT et REVOKE
la validation: 6aaaa76b
Pour la compatibilité avec le standard SQL dans les commandes GRANT et REVOKE, une phrase optionnelle GRANTED BY a été ajoutée. Par exemple:
GRANT ALL ON TABLE table_name TO role_specification
[GRANTED BY role_specification];
REVOKE ALL ON TABLE table_name FROM role_specification
[GRANTED BY role_specification];
Le nom du rôle dans GRANTED BY doit correspondre au rôle actuel. Ainsi, il ne fonctionnera pas d'émettre / retirer des droits au nom d'un autre rôle. La phrase a été ajoutée pour se conformer à la norme.
Ceci est une continuation du travail décrit dans l'article de septembre commitfest.
L'administration du système
initdb --no-instructions
commit: e09155bd
L'utilitaire initdb est utilisé pour initialiser le cluster. Et à la fin de son travail, il affiche une instruction pour démarrer un cluster:
Success. You can now start the database server using: pg_ctl -D /usr/local/pgsql/data -l logfile start
Mais ce n'est pas toujours vrai. Par exemple, dans les distributions de packages Debian, l'utilitaire pg_ctlcluster est utilisé pour démarrer le cluster, pas pg_ctl. Et ses paramètres sont différents.
Avec la nouvelle option --no-instructions, initdb ne fournira plus de conseils de démarrage, dont les distributions de packages peuvent tirer parti.
pg_dump: restaurer une seule partition en tant que table autonome
commit: 9a4c0e36 , 9eabfe30
Si une table partitionnée est incluse dans la copie logique de pg_dump, il ne sera pas possible de restaurer une partition séparée à partir d'une telle copie en tant que table indépendante. Immédiatement après la commande CREATE TABLE vient la commande ALTER TABLE… ATTACH PARTITION, qui n'est pas seulement inutile dans une telle situation, mais se termine également par une erreur. nous n'avons pas restauré la table parent.
CREATE TABLE t (id int) PARTITION BY RANGE(id);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES FROM (1) TO (1001);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES FROM (1001) TO (2001);
\! pg_dump -Fc -f db.dump
\! pg_restore db.dump -t t_p1 -f -
... CREATE TABLE public.t_p1 ( id integer ); ALTER TABLE ONLY public.t ATTACH PARTITION public.t_p1 FOR VALUES FROM (1) TO (1001); ...
Désormais, les commandes ALTER TABLE… ATTACH PARTITION pour toutes les partitions sont déchargées séparément et après toutes les commandes de création de partitions CREATE TABLE. Par conséquent, lors de la restauration d'une seule partition spécifiée avec l'option -t, seule la commande CREATE TABLE sera exécutée, ce qui permet de restaurer la partition en tant que table indépendante.
C'est tout pour le moment. Nous attendons le dernier commitfest du 14 mars .