On 12/14/2013 10:50 AM, Tom Lane wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxx> writes:
So if I am following, in the OPs case when he did the ALTER TABLE RENAME
he transferred ownership of the sequence to the renamed table.
Well, I prefer to think of it as being the same table (same OID). The
ownership didn't move anywhere, because pg_depend tracks objects by
OID not name.
Yea, I still get caught by the fact names are for humans and that OIDs
are what count.
Then when
he did CREATE TABLE LIKE (renamed table) he set up a dependency from
the newly created table to the renamed table because the sequence is
actually owned by the renamed table.
More precisely, he created a dependency of the new table's column default
expression on the existing sequence, which itself has a dependency on
the old table.
regression=# create table foo1 (f1 serial);
CREATE TABLE
regression=# create table foo2 (like foo1 including defaults);
CREATE TABLE
regression=# select pg_describe_object(classid,objid,objsubid) as obj, pg_describe_object(refclassid,refobjid,refobjsubid) as refobj, deptype from pg_depend order by objid desc limit 20;
obj | refobj | deptype
----------------------------------------+------------------------+---------
default for table foonew column f1 | table foonew column f1 | a
default for table foonew column f1 | sequence fooold_f1_seq | n <======
type foonew | table foonew | i
type foonew[] | type foonew | i
table foonew | schema public | n
default for table fooold column f1 | table fooold column f1 | a
default for table fooold column f1 | sequence fooold_f1_seq | n <======
type fooold | table fooold | i
type fooold[] | type fooold | i
table fooold | schema public | n
type fooold_f1_seq | sequence fooold_f1_seq | i
sequence fooold_f1_seq | schema public | n
sequence fooold_f1_seq | table fooold column f1 | a <======
Alright, just do my head does not explode, I am going to say the
pg_describe_object() query is from a different run where you used table
names foonew and fooold instead of foo1 and foo2?
...
As I've marked here, both default expressions are depending on the
sequence, but there's only one "ownership" dependency of the sequence
on a column. To complete the switchover you'd need to use ALTER SEQUENCE
... OWNED BY ... to move that ownership dependency to the new table.
Then the old table (and its default) could be dropped without affecting
the new table.
Understood.
regards, tom lane
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
