Thread: BUG #6683: dropping a table with a serial column from an extension needs to explicitely drop the associated seq
BUG #6683: dropping a table with a serial column from an extension needs to explicitely drop the associated seq
From
phb.emaj@free.fr
Date:
The following bug has been logged on the website: Bug reference: 6683 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 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.=20 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=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: 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 <<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',''); EOF echo "--> 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*
Re: BUG #6683: dropping a table with a serial column from an extension needs to explicitely drop the associated seq
From
Tom Lane
Date:
phb.emaj@free.fr writes: > 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. This doesn't really seem like a bug to me. The sequence is a somewhat independent object. regards, tom lane
Re: BUG #6683: dropping a table with a serial column from an extension needs to explicitely drop the associated seq
From
Craig Ringer
Date:
On 06/10/2012 06:14 AM, Tom Lane wrote: > phb.emaj@free.fr writes: >> 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. > This doesn't really seem like a bug to me. The sequence is a somewhat > independent object. I disagree; it is inconsistent with the expectation established in normal operations that the sequence created to serve a SERIAL column is owned by that table/column and is dropped when it is. regress=# create table test (x serial primary key); NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for serial column "test.x" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE regress=# \d List of relations Schema | Name | Type | Owner --------+------------+----------+------- public | test | table | craig public | test_x_seq | sequence | craig (2 rows) regress=# drop table test; DROP TABLE regress=# \d No relations found. regress=# create table test (x serial primary key); NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for serial column "test.x" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE regress=# alter table test drop column x; ALTER TABLE regress=# \d List of relations Schema | Name | Type | Owner --------+------+-------+------- public | test | table | craig (1 row) -- Craig Ringer
Re: BUG #6683: dropping a table with a serial column from an extension needs to explicitely drop the associated seq
From
Tom Lane
Date:
Craig Ringer <ringerc@ringerc.id.au> writes: > On 06/10/2012 06:14 AM, Tom Lane wrote: >> phb.emaj@free.fr writes: >>> 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. >> This doesn't really seem like a bug to me. The sequence is a somewhat >> independent object. > I disagree; it is inconsistent with the expectation established in > normal operations that the sequence created to serve a SERIAL column is > owned by that table/column and is dropped when it is. But we're not talking about a DROP; we're talking about disassociating it from an extension. That's a sufficiently unusual thing to do that I think it dangerous to make any assumptions about why the extension author is doing that, or whether he wants the sequence to come too. In the reverse direction, ALTER EXTENSION ADD TABLE doesn't auto-add associated sequences either. If we did do something like this, pg_dump would need some complicated and fragile logic additions to understand the implications; for instance, if it saw an owned sequence that wasn't a member of the extension, it would have to know to issue an ALTER EXTENSION DROP SEQUENCE after adding the table. (Now admittedly, I'm on record as wanting to get rid of pg_dump having to do anything with extension members, but still it's a great example of why "helpful" automatic actions aren't necessarily a good thing.) regards, tom lane
Re: BUG #6683: dropping a table with a serial column from an extension needs to explicitely drop the associated seq
From
Philippe BEAUDOIN
Date:
Craig Ringer a =E9crit : > On 06/10/2012 06:14 AM, Tom Lane wrote: >> phb.emaj@free.fr writes: >>> When a table having a seial column has been created by a CREATE=20 >>> EXTENSION, >>> and when this table is later dropped from the extension, the associated >>> sequence must be also explicitely dropped from the extension. >> This doesn't really seem like a bug to me. The sequence is a somewhat >> independent object. > I disagree; it is inconsistent with the expectation established in=20 > normal operations that the sequence created to serve a SERIAL column=20 > is owned by that table/column and is dropped when it is. > > regress=3D# create table test (x serial primary key); > NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for=20 > serial column "test.x" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index=20 > "test_pkey" for table "test" > CREATE TABLE > regress=3D# \d > List of relations > Schema | Name | Type | Owner > --------+------------+----------+------- > public | test | table | craig > public | test_x_seq | sequence | craig > (2 rows) > > regress=3D# drop table test; > DROP TABLE > regress=3D# \d > No relations found. > > > > > regress=3D# create table test (x serial primary key); > NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for=20 > serial column "test.x" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index=20 > "test_pkey" for table "test" > CREATE TABLE > regress=3D# alter table test drop column x; > ALTER TABLE > regress=3D# \d > List of relations > Schema | Name | Type | Owner > --------+------+-------+------- > public | test | table | craig > (1 row) > > > > > > --=20 > Craig Ringer > > I aggree with Craig. The current rule is not natural (at least for me, as I did the mistake=20 in one of my extension scripts ;-)) If this behaviour remains as is, it should be at least documented. Finding the best desirable behaviour is probably also linked with the=20 discussion just started for BUG #6682. Best regards. Philippe BEAUDOIN.