Hans-Juergen Schoenig: PostgreSQL: Sophistication des tables temporaires de Planet PostgreSQL

Les tables temporaires existent depuis toujours et sont largement utilisées par les développeurs d’applications. Cependant, les tables temporaires ne se limitent pas à ce que l’on pense. PostgreSQL vous permet de configurer la durée de vie d’une table temporaire de manière agréable et permet d’éviter certains pièges courants.

CRÉER UNE TABLE TEMPORAIRE…

Par défaut, une table temporaire vivra aussi longtemps que votre connexion à la base de données. Il sera supprimé dès que vous vous déconnecterez. Dans de nombreux cas, c’est le comportement que les gens veulent:

tmp=# CREATE TEMPORARY TABLE x (id int);
CREATE TABLE
tmp=# d
        List of relations
  Schema   | Name | Type  | Owner 
-----------+------+-------+-------
 pg_temp_3 | x    | table | hs
(1 row)

tmp=# q
iMac:~ hs$ psql tmp
psql (12.3)
Type "help" for help.

tmp=# d
Did not find any relations.

Une fois que nous nous sommes reconnectés, la table est partie pour de bon. Gardez également à l’esprit que la table temporaire n’est visible que dans votre session. Les autres connexions ne verront pas le tableau (ce qui est bien sûr le comportement souhaité). Cela implique également que de nombreuses sessions peuvent créer une table temporaire portant le même nom.

Cependant, une table temporaire peut faire plus. Le plus important est la possibilité de contrôler ce qui se passe lors de la validation:

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

Comme vous pouvez le voir, il existe trois options. «PRESERVE ROWS» est le comportement dont vous venez d’être témoin. Parfois, vous ne le voulez pas. Il est donc également possible de vider une table temporaire lors de la validation:

tmp=# BEGIN;
BEGIN
tmp=# CREATE TEMP TABLE x ON COMMIT DELETE ROWS AS 
	SELECT * FROM generate_series(1, 5) AS y;
SELECT 5
tmp=# SELECT * FROM x;
 y 
---
 1
 2
 3
 4
 5
(5 rows)

tmp=# COMMIT;
COMMIT
tmp=# SELECT * FROM x;
 y 
---
(0 rows)

Dans ce cas, PostgreSQL nous laisse simplement une table vide dès que la transaction se termine. La table elle-même est toujours là et peut être utilisée.

Abandonnons la table pour l’instant:

tmp=# DROP TABLE x;
DROP TABLE

Parfois, vous voulez que la table entière disparaisse à la fin de la transaction: «ON COMMIT DROP» peut être utilisé pour obtenir exactement cela:

tmp=# BEGIN;
BEGIN
tmp=# CREATE TEMP TABLE x ON COMMIT DROP AS 
	SELECT * FROM generate_series(1, 5) AS y;
SELECT 5
tmp=# COMMIT;
COMMIT
tmp=# SELECT * FROM x;
ERROR:  relation "x" does not exist
LINE 1: SELECT * FROM x;

PostgreSQL lancera une erreur car la table est déjà partie. Ce qui est remarquable ici, c’est que vous pouvez toujours utiliser les curseurs WITH HOLD comme indiqué dans l’exemple suivant:

tmp=# BEGIN;
BEGIN
tmp=# CREATE TEMP TABLE x ON COMMIT DROP AS SELECT * FROM generate_series(1, 5) AS y;
SELECT 5
tmp=# DECLARE mycur CURSOR WITH HOLD FOR SELECT * FROM x;
DECLARE CURSOR
tmp=# COMMIT;
COMMIT
tmp=# FETCH ALL FROM mycur;
 y 
---
 1
 2
 3
 4
 5
(5 rows)

La table elle-même a toujours disparu, mais les curseurs WITH HOLD garantiront que le «contenu» du curseur survivra à la fin de la transaction. Beaucoup de gens ne s’attendent pas à ce genre de comportement, mais cela a du sens et peut être très utile.

Contrôle de l’utilisation de la mémoire…

Si vous utilisez des tables temporaires, il est judicieux de les garder relativement petites. Dans certains cas, cependant, une table temporaire peut être assez grande pour une raison quelconque. Pour vous assurer que les performances restent bonnes, vous pouvez demander à PostgreSQL de conserver davantage une table temporaire dans la RAM. temp_buffers est le paramètre dans postgresql.conf que vous devriez regarder dans ce cas:

tmp=# SHOW temp_buffers;
 temp_buffers 
--------------
 8MB
(1 row)

La valeur par défaut est de 8 Mo. Si vos tables temporaires sont volumineuses, augmenter cette valeur est certainement logique.

Finalement …

Si vous souhaitez en savoir plus sur les performances de la base de données PostgreSQL en général, pensez à consulter mon article sur trois façons de détecter et de corriger les requêtes lentes.

La poste PostgreSQL: perfectionner les tables temporaires est apparu en premier le Cybertec.