Обсуждение: BUG #6682: pg_dump and sequence values of serial columns for extension configuration tables
BUG #6682: pg_dump and sequence values of serial columns for extension configuration tables
От
phb.emaj@free.fr
Дата:
The following bug has been logged on the website: Bug reference: 6682 Logged by: Philippe BEAUDOIN Email address: phb.emaj@free.fr PostgreSQL version: 9.1.4 Operating system: Linux Description:=20=20=20=20=20=20=20=20 When a table is registered as an extension configuration table and contains a serial column, the current value of the sequence associated to this serial column is not dumped by pg_dump. Here is a test case to easily reproduce this issue. Just copy/paste and adjust initial parameters. #!/bin/sh export PGINST=3D/usr/local/pg913 export PGBIN=3D$PGINST/bin export PGEXT=3D$PGINST/share/postgresql/extension export PGHOST=3Dlocalhost export PGPORT=3D5913 export PGUSER=3Dpostgres export PGDATABASE=3Dphb echo "-----------------------------------------------------------------------" echo " Issue: pg_dump doesn't record sequence values of serial columns for " echo " tables registered by pg_extension_config_dump()" echo "-----------------------------------------------------------------------" echo Connection parameters: $PGHOST - $PGPORT - $PGUSER - $PGDATABASE # Build the extension control file cat >$PGINST/share/postgresql/extension/phb.control <<EOF=20 default_version =3D '1.0.0' schema =3D phb EOF # Create a test database $PGBIN/psql template1 -ac "drop database phb;" $PGBIN/psql template1 -ac "create database phb;" # create the create extension script cat >$PGEXT/phb--1.0.0.sql <<EOF=20 create table tbl1 (col1 serial not null primary key, col2 int); select pg_catalog.pg_extension_config_dump('tbl1',''); create sequence seq1; EOF echo "--> Create the extension" $PGBIN/psql -ac "create extension phb;" $PGBIN/psql -ac "\dx+ phb" echo "--> Use the extension and look at the impact" $PGBIN/psql -ac "insert into phb.tbl1 (col2) values (1),(2),(3);" $PGBIN/psql -ac "select setval('phb.seq1',10);" $PGBIN/psql -ac "select * from phb.tbl1;" $PGBIN/psql -ac "select * from phb.tbl1_col1_seq;" $PGBIN/psql -ac "select * from phb.seq1;" echo "--> OK, now dump the phb database" $PGBIN/pg_dump -Fp -f phb.dump echo "--> Cleanup and recreate the database" $PGBIN/psql template1 -ac "drop database phb;" $PGBIN/psql template1 -ac "create database phb;" echo "--> Restore the phb database" $PGBIN/psql -f phb.dump echo "--> Look at the result: the sequences start values equal 1 !!! " $PGBIN/psql -ac "\dx+ phb" $PGBIN/psql -ac "select * from phb.tbl1;" $PGBIN/psql -ac "select * from phb.tbl1_col1_seq;" $PGBIN/psql -ac "select * from phb.seq1;" echo "--> Of course inserting a new row results in duplicate key errors" $PGBIN/psql -ac "insert into phb.tbl1 (col2) values (4);" echo "--> And sequences cannot be registered as 'content to be dumped'" $PGBIN/psql -ac "select pg_catalog.pg_extension_config_dump('tbl1_col1_seq','');" $PGBIN/psql -ac "select pg_catalog.pg_extension_config_dump('seq1','');" # cleanup the environment $PGBIN/psql template1 -ac "drop database phb;" rm $PGINST/share/postgresql/extension/phb* rm phb.dump
phb.emaj@free.fr writes: > When a table is registered as an extension configuration table and contains > a serial column, the current value of the sequence associated to this serial > column is not dumped by pg_dump. I think the way to make that happen is to also register the sequence as a configuration table of the extension. It's not clear to me that that should happen by default; it would depend a lot on how the extension designer intends to manage modifications of the table. > echo "--> And sequences cannot be registered as 'content to be dumped'" > $PGBIN/psql -ac "select > pg_catalog.pg_extension_config_dump('tbl1_col1_seq','');" This test proves nothing of the kind. Please read the error message: ERROR: pg_extension_config_dump() can only be called from an SQL script executed by CREATE EXTENSION You have to do it in the extension's script. regards, tom lane
Re: BUG #6682: pg_dump and sequence values of serial columns for extension configuration tables
От
Philippe BEAUDOIN
Дата:
Hi Tom, Here is a final answer to this very old thread, for the record. You were right (as usual ;-)). It's indeed possible to register a sequence as a "configuration table" using the pg_extension_config_dump() function. >> When a table is registered as an extension configuration table and contains >> a serial column, the current value of the sequence associated to this serial >> column is not dumped by pg_dump. > I think the way to make that happen is to also register the sequence as > a configuration table of the extension. It's not clear to me that that > should happen by default; it would depend a lot on how the extension > designer intends to manage modifications of the table. > >> echo "--> And sequences cannot be registered as 'content to be dumped'" >> $PGBIN/psql -ac "select >> pg_catalog.pg_extension_config_dump('tbl1_col1_seq','');" > This test proves nothing of the kind. Please read the error message: > ERROR: pg_extension_config_dump() can only be called from an SQL script executed by CREATE EXTENSION > You have to do it in the extension's script. > > regards, tom lane > > May be a sentence about this in the documentation would be useful. I will try to propose something. Best regards. Philippe Beaudoin.