Re: [GENERAL] invisible dependencies on a table?
Adrian Klaver 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unexpected update behaviour
Victor Yegorov writes: > Could you kindly explain me why the query as it is updates no records? It's a bug, that's why. See http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=324577f39bc8738ed0ec24c36c5cb2c2f81ec660 or for 9.2, http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5d545b7ed53a8b2058c1152bd1c9ae227b3280e3 regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] invisible dependencies on a table?
> > > 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.
Re: [GENERAL] invisible dependencies on a table?
On 12/14/2013 10:50 AM, Tom Lane wrote: Adrian Klaver 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] invisible dependencies on a table?
Adrian Klaver 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] invisible dependencies on a table?
On 12/14/2013 09:00 AM, Tom Lane wrote: Tim Uckun 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] invisible dependencies on a table?
Tim Uckun 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'::regclass and 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'::regclass and 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] invisible dependencies on a table?
> > > 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.
