code. grind. sleep.

On peut parfois trouver cet étrange message d'erreur dans les traces de PostgreSQL (N étant un nombre) ou lors de l'exécution d'une requête :

ERROR:  could not open relation with OID N

Si on recherche ce message dans les mailing-lists du projet, on peut facilement conclure que la base de données est corrompue, qu'il y a des problèmes matériels et que la sécurité des données est en péril. Et bien, ce n'est pas forcément le cas : obtenir ce message peut être tout à fait normal.

Pour démontrer cela, on a besoin d'une table :

$ createdb test
$ psql test
psql (9.0.4)
Type "help" for help.

test=# CREATE TABLE truc AS SELECT generate_series(0, 5) AS i;
SELECT 6
test=#

On lance une session qui bloquerait un DROP de cette table, pour cela on pose un verrou exclusif, le mode « ExclusiveLock » ne laisse passer que les lectures (c'est important pour la suite) :

$ psql test
psql (9.0.4)
Type "help" for help.

test=# BEGIN;
BEGIN
test=# LOCK TABLE truc IN EXCLUSIVE MODE;
LOCK TABLE
test=#

On laisse cette transaction « ouverte », avec le verrou posé et on lance une session pour supprimer la table :

$ psql test
psql (9.0.4)
Type "help" for help.

test=# BEGIN;
BEGIN
test=# DROP TABLE truc;

L'ordre SQL DROP TABLE ne rend pas la main, cette deuxième session attend le verrou « AccessExclusiveLock », qui est le plus restrictif, sur la table pour pouvoir la supprimer. La page http://wiki.postgresql.org/wiki/Loc... fournie une requête montrant les dépendances entre requêtes du point de vue du verrouillage. Dans ce cas, elle donne le résultat suivant :

 waiting_locktype | waiting_table |  waiting_query   |    waiting_mode     | waiting_pid | other_locktype | other_table |      other_query      |  other_mode   | other_pid | other_granted 
------------------+---------------+------------------+---------------------+-------------+----------------+-------------+-----------------------+---------------+-----------+---------------
 relation         | truc          | DROP TABLE truc; | AccessExclusiveLock |       25632 | relation       | truc        | <IDLE> in transaction | ExclusiveLock |     24217 | t

On lance une troisième session, avec un SELECT sur notre table :

$ psql test
psql (9.0.4)
Type "help" for help.

test=# SELECT * FROM truc;

L'ordre SELECT ne rend pas la main, cette troisième session se met à attendre le DROP TABLE et la première session. C'est d'ailleurs le DROP TABLE qui bloque réellement le SELECT, car la première session à verrouillé la table en lecture seule :

 waiting_locktype | waiting_table |    waiting_query    |    waiting_mode     | waiting_pid | other_locktype | other_table |      other_query      |     other_mode      | other_pid | other_granted 
------------------+---------------+---------------------+---------------------+-------------+----------------+-------------+-----------------------+---------------------+-----------+---------------
 relation         | truc          | SELECT * FROM truc; | AccessShareLock     |       28629 | relation       | truc        | DROP TABLE truc;      | AccessExclusiveLock |     25632 | f
 relation         | truc          | DROP TABLE truc;    | AccessExclusiveLock |       25632 | relation       | truc        | <IDLE> in transaction | ExclusiveLock       |     24217 | t
 relation         | truc          | SELECT * FROM truc; | AccessShareLock     |       28629 | relation       | truc        | <IDLE> in transaction | ExclusiveLock       |     24217 | t
 relation         | truc          | DROP TABLE truc;    | AccessExclusiveLock |       25632 | relation       | truc        | SELECT * FROM truc;   | AccessShareLock     |     28629 | f
(4 rows)

On libère la première session :

test=# ROLLBACK;
ROLLBACK
test=#

Le DROP TABLE passe, et le SELECT continue d'attendre :

 waiting_locktype | waiting_table |    waiting_query    |  waiting_mode   | waiting_pid | other_locktype | other_table |      other_query      |     other_mode      | other_pid | other_granted 
------------------+---------------+---------------------+-----------------+-------------+----------------+-------------+-----------------------+---------------------+-----------+---------------
 relation         | truc          | SELECT * FROM truc; | AccessShareLock |       28629 | relation       | truc        | <IDLE> in transaction | AccessExclusiveLock |     25632 | t

On voit que le SELECT attend la transaction qui a lancé le DROP TABLE. Même si le DROP TABLE est terminé, son effet ne sera connu des transactions concurrentes seulement ou moment du commit ou rollback, parce qu'on utilise le niveau d'isolation des transaction « read committed » (par défaut). Il n'y a pas de « UNLOCK » sur les tables dans PostgreSQL, il faut attendre la fin de la transaction pour que les verrous soient libérés, du moins lorsqu'on n'utilise pas de savepoints.

Maintenant, on valide le DROP TABLE, avec l'ordre COMMIT. Le SELECT termine en erreur, on voit alors le message dans les logs :

ERROR:  could not open relation with OID 17366 at character 15
STATEMENT:  SELECT * FROM truc;

Lorsque le SELECT n'est plus bloqué par le verrou, il ne peut accéder pas à la table car elle n'existe plus. Le message n'est pas très explicite parce que la requête est en cours d'exécution : le moteur a déjà terminé le travail de parsing et de planification, il ne travaille qu'avec les OID qu'il a récupéré du catalogue système à ce moment là.

Dans ce cas précis, obtenir ce message n'est un problème de corruption de la base ou du catalogue système.

Gérer une sauvegarde PITR (Point-In-Time Recovery), permettant de revenir à un point précis dans le temps sur un serveur PostgreSQL, n'est pas forcément évident à mettre en place et ensuite à gérer au quotidien : la restauration en cas de problème peut même prendre beaucoup de temps à mettre en place, avec des possibilités d'erreurs non négligeables. C'est d'autant plus stressant que la pression monte quand on en a besoin...

C'est pourquoi, dans le cadre de mon travail, j'ai écrit un ensemble de scripts shell en Bash (ce qui permet d'utiliser des commandes qui sont déjà sur une installation classique de Linux) pour faciliter ce travail. Le code est hébergé sur github : https://github.com/dalibo/pitrery. La version 1.0 vient d'être taguée. C'est encore un peu brut de décoffrage niveau documentation, mais les scripts ont été testés dans tous les sens, donc ce jeu d'outils devrait être utilisable en production sans trop de difficultés.

Il y a un Makefile GNU, à l'ancienne, les fichiers de configuration sont plein de commentaires pour expliquer et il y des « usage » pour chaque script à base de -? ou -h qui devraient parler à ceux qui savent déjà gérer du PITR.

Avec PostgreSQL, on peut utiliser la requête suivante pour obtenir la taille de chacune des bases de données d'un cluster :

SELECT datname AS base,
  pg_size_pretty(pg_database_size(oid)) AS taille
FROM pg_database;

Qui donne par exemple :

         base          | taille  
-----------------------+---------
 template0             | 5273 kB
 postgres              | 5385 kB
 redmine               | 9057 kB
 roundcube             | 9153 kB
 template1             | 5369 kB
 dotclear              | 6833 kB
 dspam                 | 204 MB
 exim                  | 5649 kB
 (8 rows)

Mais comment est-ce possible ? on a appliqué la fonction pg_database_size() sur une colonne appelée oid, alors que la définition de pg_database est la suivante :

postgres=# \d pg_database
    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers 
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] | 
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"

Il n'y a pas de colonne oid dans cette table ! Et pourtant... Cette colonne est tout simplement cachée, mais elle existe bel et bien. Pour vérifier, on recherche donc dans le catalogue système la liste des champs de la table :

SELECT attname, typname, attnum
FROM pg_attribute a
  JOIN pg_class c ON (c.oid = a.attrelid)
  JOIN pg_type t ON (t.oid = a.atttypid)
WHERE relname = 'pg_database';

Qui donne :

    attname    | typname  | attnum 
---------------+----------+--------
 tableoid      | oid      |     -7
 cmax          | cid      |     -6
 xmax          | xid      |     -5
 cmin          | cid      |     -4
 xmin          | xid      |     -3
 oid           | oid      |     -2
 ctid          | tid      |     -1
 datname       | name     |      1
 datdba        | oid      |      2
 encoding      | int4     |      3
 datcollate    | name     |      4
 datctype      | name     |      5
 datistemplate | bool     |      6
 datallowconn  | bool     |      7
 datconnlimit  | int4     |      8
 datlastsysoid | oid      |      9
 datfrozenxid  | xid      |     10
 dattablespace | oid      |     11
 datacl        | _aclitem |     12
(19 rows)

La colonne attnum de pg_attribute indique la position de la colonne, on remarque ainsi qu'il y a sept colonnes avec une position négative. Ces colonnes ne sont pas montrés par la commande \d de psql, ce sont les colonnes système de la table (plus d'information sur ce qu'elles représentent dans la doc)

En plus, on a encore utilisé cette colonne oid pour la retrouver dans la table... Maintenant, on peut être rassuré sur le catalogue système, ce n'est qu'un ensemble de tables, auxquelles on accède en SQL standard...

Pour éditer sa ligne de commande dans psql, on peut soit compiler Postgres avec le support de readline, soit libedit. Pour Debian, les questions de licences sont très importantes, et ils pensent qu'il y a une incompatibilité entre la licence de libreadline (GPLv3) et celle de Postgres (BSD), c'est pourquoi ils ont décidé de compiler Postres avec la libedit. Malheureusement, la version de la libedit est vieille dans Debian et surtout limitée : on ne peut taper que des caractères ASCII, ce qui est une belle regression du point de vu du l'utilisation.

Il y avait deux solutions :

• Ne taper que des caractères ASCII
• Recompiler les packages Postgres avec le support de readline, c'est ce que j'ai fait.

Un workaround a été trouvé récemment, en ayant la libreadline installée, on peut s'arranger pour l'utiliser grâce à un LD_PRELOAD bien placé. C'est ce que fait pg_wrapper (le truc qui permet d'avoir plusieurs versions de Postgres en même temps sous Debian) depuis la version 114 des packages postgresql-common et postgresql-client-common.

De plus, la priorité a été mise plus haute sur cette mise à jour, qui corrige aussi d'autres problèmes, et cette version est déjà dans testing.

Enfin, j'ai refais les backports que je maintiens pour fournir diverses versions de Postgres sur Debian, non disponibles dans les dépôts officiels : c'est sur le dépôt http://debian.dalibo.org.