[gull] pgsql...aaaarh

[Marc SCHAEFER]

On Wed, Jun 15, 2005 at 05:43:37PM +0200, Cedric BRINER wrote:
> comment faire pour recuperer une sequence d'une table sur une autre.

Il y a deux problèmes:

   1. il faut que la colonne concernée de la table soit configurée
      comme type SERIAL (ce qui, pour PostgreSQL, signifie que la
      valeur par défaut de cette colonne lorsque NON spécifiée est
      la fonction next_val('nom_colonne_seq').

   2. il faut que la valeur de la séquence soit préconfigurée à la
      valeur désirée si pas 0.

> create table test (id serial, nom varchar(32));
> INSERT INTO test ("nom") values ('cedric');
> INSERT INTO test ("nom") values ('felix');
> INSERT INTO test ("nom") values ('julien');
> 
> create table new_test (id serial, nom varchar(32));
> insert into new_test SELECT * from test;

Problème: cela ne copie pas la valeur de la séquence. Symptôme:
erreur à la première insertion suivante dans new_test, dans la
mesure où id a bien été configuré comme UNIQUE (ou PRIMARY KEY)
ce qui n'est pas le cas ici: d'où duplicats d'id.

On pourrait le faire avec setval().

> ALTER TABLE test RENAME to old_test;
> ALTER TABLE new_test RENAME to test;

regardons:

   \d test d     | integer               | not null default nextval('public.new_test_id_seq'::text)

évidemment incorrect pour les 2 raisons évoquées.

Correction (manuelle):

   ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('public.test_id_seq');

Dans ce dernier cas comme on reprend la séquence initialement crée,
la valeur courante de la séquence est correcte.

> commentaire: ce qui est bizarre c'est que pgsql gere tres bien les inter-dependances (foreign key) mais ne renomme pas les sequences ?

Le problème est que PostgreSQL traduit immédiatement le qualificateur SERIAL
et ne le stocke pas. Et qu'il y a des raisons parfaitement valides pour
utiliser une AUTRE séquence au nom quelconque!
Voire une séquence partagée. PostgreSQL garantit que les numéros de séquence
sont backend-private (ils peuvent avoir des trous en cas d'abort
de transactions).

Ce qui suit est valide (PL/SQL) même en transactionnel.

   SELECT next_val('test_id_seq') INTO indice;
   INSERT INTO test(id, nom) VALUES(indice, 'test');
   INSERT INTO linked_table(truc, test_id) VALUES('truc', indice);

c'est d'ailleurs la façon recommandée pour faire ce que MySQL fait
avec last_insert_id().  On peut en faire une procédure stockée, p.ex.
dans une VIEW inscriptible qui répartit les données sur plusieurs
tables avec les liaisons nécessaires.

> question: comment faire pour que la table ``test'' prenne la sequence ``test_id_seq'' ?

On peut aussi créer la nouvelle table ainsi:

   SELECT * INTO new_test FROM test;

(les contraintes ne sont pas copiées)

puis ensuite ajouter les contraintes.

On peut aussi faire un dump/restore texte.