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

On 12/14/2013 10:50 AM, Tom Lane wrote:
> 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.

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@gmail.com