Re: invisible dependencies on a table? - Mailing list pgsql-general

Adrian Klaver <adrian.klaver@gmail.com> 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.

> 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 <======
 ...

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.

            regards, tom lane