Re: invisible dependencies on a table?

On 12/14/2013 10:50 AM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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.

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(dot)klaver(at)gmail(dot)com