Обсуждение: BUG #6683: dropping a table with a serial column from an extension needs to explicitely drop the associated seq
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*
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
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
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
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.