Обсуждение: invisible dependencies on a table?
I have a table foo. It has a serial column called "id". I execute the following statement
ALTER TABLE table_name RENAME TO archived_table_name;
CREATE TABLE table_name (LIKE archived_table_name INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
..... Archieve the table here...
DROP TABLE arhived_table_name
This doesn't work because the archived table name has a dependency on the sequence created by the serial field. So I try to remove that dependency by doing this.
alter table "archived_table_name" alter column id drop default;
ALTER TABLE"archived_table_name" DROP CONSTRAINT systemevents_pkey;
So by now there should not be a dependency on the sequence but I still can't drop the table and and pgadmin tells me it's still depending on the sequence.
When I look at the table definition it doesn't seem to have any reference to the sequence at all.
How can I drop this table and leave the sequence alone? Obviously the newly created table needs it.
Thanks.
Tim Uckun wrote > How can I drop this table and leave the sequence alone? Obviously the > newly > created table needs it. <not tested> You cannot. You need to put the sequence up for adoption and have the "new" table become its parent/owner. http://www.postgresql.org/docs/9.2/interactive/sql-altersequence.html ALTER SEQUENCE ... OWNED BY ... ; I cannot readily speak to why you are not seeing sequence ownership as a dependent when looking at the now-archive table definition. Dropping the "systemevents_pkey" solved nothing (the PK constraint is part of the table definition and furthermore has nothing to do with the sequence) and really you wouldn't have to "drop default" either since all you are doing is removing a dependency that the sequence has on the table: i.e., you cannot drop the sequence until you drop the default - not the other way around. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/invisible-dependencies-on-a-table-tp5783252p5783254.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/12/2013 08:24 PM, Tim Uckun wrote:
> I have a table foo. It has a serial column called "id". I execute the
> following statement
>
> ALTER TABLE table_name RENAME TO archived_table_name;
> CREATE TABLE table_name (LIKE archived_table_name INCLUDING
> DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
>
> ..... Archieve the table here...
>
> DROP TABLE arhived_table_name
>
> This doesn't work because the archived table name has a dependency on
> the sequence created by the serial field. So I try to remove that
> dependency by doing this.
>
> alter table "archived_table_name" alter column id drop default;
> ALTER TABLE"archived_table_name" DROP CONSTRAINT systemevents_pkey;
>
> So by now there should not be a dependency on the sequence but I still
> can't drop the table and and pgadmin tells me it's still depending on
> the sequence.
>
> When I look at the table definition it doesn't seem to have any
> reference to the sequence at all.
>
> How can I drop this table and leave the sequence alone? Obviously the
> newly created table needs it.
In addition to what David said here is another option, create the
original table with a non-dependent sequence:
test=> CREATE SEQUENCE shared_seq;
CREATE SEQUENCE
test=> create table seq_test(id integer default nextval('shared_seq'),
fld varchar);
CREATE TABLE
test=> ALTER TABLE seq_test RENAME to archived_seq_test;
ALTER TABLE
test=> CREATE TABLE seq_test (LIKE archived_seq_test INCLUDING DEFAULTS
INCLUDING CONSTRAINTS INCLUDING INDEXES);
CREATE TABLE
test=> DROP TABLE archived_seq_test;
DROP TABLE
When you use the serial type it creates a dependency on the serial and
as David pointed out you can do the same thing with ALTER SEQUENCE.
However as shown above there is no requirement that a sequence be
dependent. It is at its core a 'table' that is a number generator.
>
> Thanks.
--
Adrian Klaver
adrian.klaver@gmail.com
I cannot readily speak to why you are not seeing sequence ownership as a
dependent when looking at the now-archive table definition.
pgadmin knows it's a dependency because when you try to drop it you get a message saying so but I can't see it in the defintion of the table.
BTW is there a way to get a list of dependencies for a object? I was some scripts when I was googling but none of them seem to work with later versions of postgres.
Tim Uckun <timuckun@gmail.com> writes:
> BTW is there a way to get a list of dependencies for a object? I was some
> scripts when I was googling but none of them seem to work with later
> versions of postgres.
Don't know why that would be; the pg_depend data structure hasn't really
changed since it was invented (in 7.3, if memory serves). If anything,
it's gotten easier to work with, as a result of invention of helper
functions such as pg_describe_object().
regression=# create table foo (f1 serial);
CREATE TABLE
regression=# -- things foo depends on:
regression=# select pg_describe_object(refclassid,refobjid,refobjsubid), deptype from pg_depend where
classid='pg_class'::regclassand objid = 'foo'::regclass;
pg_describe_object | deptype
--------------------+---------
schema public | n
(1 row)
regression=# -- things that depend on foo:
regression=# select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where
refclassid='pg_class'::regclassand refobjid = 'foo'::regclass;
pg_describe_object | deptype
---------------------------------+---------
type foo | i
sequence foo_f1_seq | a
default for table foo column f1 | a
(3 rows)
It's that automatic dependency of the sequence on the table (or, if you
drill down a little further by looking at refobjsubid, you'll find out
it's really depending specifically on the f1 column) that represents
the owned-by relationship.
This is a nice way to look at the contents of pg_depend:
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 10;
obj | refobj | deptype
---------------------------------+---------------------+---------
default for table foo column f1 | sequence foo_f1_seq | n
default for table foo column f1 | table foo column f1 | a
type foo | table foo | i
type foo[] | type foo | i
table foo | schema public | n
type foo_f1_seq | sequence foo_f1_seq | i
sequence foo_f1_seq | schema public | n
sequence foo_f1_seq | table foo column f1 | a
function wait_for_stats() | language plpgsql | n
function wait_for_stats() | schema public | n
(10 rows)
See
http://www.postgresql.org/docs/9.3/static/catalog-pg-depend.html
for some documentation about what the deptype means.
regards, tom lane
On 12/14/2013 09:00 AM, Tom Lane wrote: > Tim Uckun <timuckun@gmail.com> writes: >> BTW is there a way to get a list of dependencies for a object? I was some >> scripts when I was googling but none of them seem to work with later >> versions of postgres. > > Don't know why that would be; the pg_depend data structure hasn't really > changed since it was invented (in 7.3, if memory serves). If anything, > it's gotten easier to work with, as a result of invention of helper > functions such as pg_describe_object(). > > > > See > http://www.postgresql.org/docs/9.3/static/catalog-pg-depend.html > for some documentation about what the deptype means. 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. 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. > > regards, tom lane > > -- Adrian Klaver adrian.klaver@gmail.com
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
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
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.
I did an alter sequence after the table renaming and it works now. Thanks for all your help.
Adrian Klaver <adrian.klaver@gmail.com> writes:
> 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?
Argh, sorry about that! I decided old/new would be more useful names
in the middle of composing the example, and forgot to go back and fix
the creation commands in my text.
regards, tom lane