[BUGS] BUG #6683: dropping a table with a serial column from an extension needs to explicitely drop the associated seq

[phb . emaj]

The following bug has been logged on the website:

Bug reference:  6683
Logged by:  Philippe BEAUDOIN
Email address:  phb.e...@free.fr
PostgreSQL version: 9.1.4
Operating system:   Linux
Description:

When a table having a seial column has been created by a CREATE EXTENSION,
and when this table is later dropped from the extension, the associated
sequence must be also explicitely dropped from the extension. 

Otherwise, after the table is detached from the extension, neither the table
can be dropped, nor the extension (except using CASCADE clauses).

This unattended behaviour could be considered as a design choice. In this
case, this should be clearly documentated.

Here is a test case to easily reproduce this issue.
Just copy/paste and adjust initial parameters.

#!/bin/sh

export PGINST=/usr/local/pg913
export PGBIN=$PGINST/bin
export PGEXT=$PGINST/share/postgresql/extension

export PGHOST=localhost
export PGPORT=5913
export PGUSER=postgres
export PGDATABASE=phb

echo
"---"
echo " Issue: dropping a table with a serial column from an extension needs
"
echo "to explicitely drop the associated sequence from the
extension"
echo
"---"
echo Connection parameters: $PGHOST - $PGPORT - $PGUSER - $PGDATABASE

# Build the extension control file
cat >$PGINST/share/postgresql/extension/phb.control <$PGEXT/phb--1.0.0.sql < Create the extension"
$PGBIN/psql -ac "create extension phb;"
$PGBIN/psql -ac "\dx+ phb"

echo "--> Now remove tbl1 from the extension"
$PGBIN/psql -ac "alter extension phb drop table phb.tbl1;"

echo "--> The table pushed out of the extension cannot be dropped, neither
the extension"
$PGBIN/psql -ac "drop table phb.tbl1;"
$PGBIN/psql -ac "drop extension phb;"

echo "--> And what if we drop the generated sequence from the extension ?"
$PGBIN/psql -ac "\dx+ phb"
$PGBIN/psql -ac "alter extension phb drop sequence phb.tbl1_col1_seq;"
$PGBIN/psql -ac "\dx+ phb"
$PGBIN/psql -ac "drop extension phb;"
$PGBIN/psql -ac "\d phb.tbl1;"

# cleanup the environment
$PGBIN/psql template1 -ac "drop database phb;"
rm $PGINST/share/postgresql/extension/phb*



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #6682: pg_dump and sequence values of serial columns for extension configuration tables

[phb . emaj]

The following bug has been logged on the website:

Bug reference:  6682
Logged by:  Philippe BEAUDOIN
Email address:  phb.e...@free.fr
PostgreSQL version: 9.1.4
Operating system:   Linux
Description:

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=/usr/local/pg913
export PGBIN=$PGINST/bin
export PGEXT=$PGINST/share/postgresql/extension

export PGHOST=localhost
export PGPORT=5913
export PGUSER=postgres
export PGDATABASE=phb

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 <$PGEXT/phb--1.0.0.sql < 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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #6681: pg_extension.extconfig after alter sequence drop table

[phb . emaj]

The following bug has been logged on the website:

Bug reference:  6681
Logged by:  Philippe BEAUDOIN
Email address:  phb.e...@free.fr
PostgreSQL version: 9.1.4
Operating system:   Linux
Description:

When a table has been registered as an extension configuration table, it's
oid is reported in the extconfig column of the pg_extension table. But once
the table is dropped from the extension, it is still referenced in
pg_extension.extconfig.
Here is a test case to easily reproduce this issue.
Just copy/paste and adjust initial parameters.

#!/bin/sh

export PGINST=/usr/local/pg913
export PGBIN=$PGINST/bin
export PGEXT=$PGINST/share/postgresql/extension

export PGHOST=localhost
export PGPORT=5913
export PGUSER=postgres
export PGDATABASE=phb

echo
"---"
echo " Issue: extconfig from pg_extension after alter sequence drop table"
echo
"---"
echo Connection parameters: $PGHOST - $PGPORT - $PGUSER - $PGDATABASE

# Build the extension control file
cat >$PGINST/share/postgresql/extension/phb.control <$PGEXT/phb--1.0.0.sql < Create the extension"
$PGBIN/psql -ac "create extension phb;"
$PGBIN/psql -ac "\dx+ phb"
$PGBIN/psql -ac "select oid from pg_class where relname = 'tbl1';"
$PGBIN/psql -ac "select * from pg_extension;"

echo "--> OK, tbl1 is correctly referenced as a configuration tables"
echo "--> Now remove tbl1 from the extension and look at the result"
$PGBIN/psql -ac "alter extension phb drop table phb.tbl1;"
$PGBIN/psql -ac "select * from pg_extension;"

echo "--> Surprisingly, extconfig references tbl1 !!!"
echo "--> However, tbl1 is really out of the extension."
$PGBIN/psql -ac "\dx+ phb"
echo "--> This is confirmed by a pg_dump output"
$PGBIN/pg_dump -Fp

echo "--> And the extension and the table can be dropped separately"
$PGBIN/psql -ac "drop extension phb;"
$PGBIN/psql -ac "\d phb.tbl1"
$PGBIN/psql -ac "drop table phb.tbl1;"

# cleanup the environment
$PGBIN/psql template1 -ac "drop database phb;"
rm $PGINST/share/postgresql/extension/phb*



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #6694: 9.2 beta 2 : psql commands \db and \db+ fail

[phb . emaj]

The following bug has been logged on the website:

Bug reference:  6694
Logged by:  Philippe Beaudoin
Email address:  phb.e...@free.fr
PostgreSQL version: Unsupported/Unknown
Operating system:   linux
Description:

Using the latest 9.2 beta 2 version, I got an error when issuing \db or \db+
psql command.

paf=# select version();

 version
 

--

 PostgreSQL 9.2beta2 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.4.0
20090506 (Red Hat 4.4.0-4), 32-bit

(1 row)



paf=# \db

ERROR:  column "spclocation" does not exist

LINE 3:   spclocation AS "Location"

  ^

paf=# \db+

ERROR:  column "spclocation" does not exist

LINE 3:   spclocation AS "Location",

  ^
I presume this is linked to the "Remove the spclocation field from
pg_tablespace" item of the release notes
and that something like the new pg_tablespace_location() function should be
used by psql.

Regards. Philippe.


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs