Обсуждение: BUG #6682: pg_dump and sequence values of serial columns for extension configuration tables

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

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.