[BUGS] BUG #6712: PostgreSQL 9.2 beta2: alter table drop constraint does not work on inherited master table
The following bug has been logged on the website: Bug reference: 6712 Logged by: Miroslav Šulc Email address: miroslav.s...@fordfrog.com PostgreSQL version: Unsupported/Unknown Operating system: Gentoo Linux Description: here is the test case: test=# create table test_constraints (id int, val1 varchar, val2 int, unique (val1, val2)); NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_constraints_val1_val2_key" for table "test_constraints" CREATE TABLE test=# create table test_constraints_inh () inherits (test_constraints); CREATE TABLE test=# alter table only test_constraints drop constraint test_constraints_val1_val2_key; ERROR: constraint "test_constraints_val1_val2_key" of relation "test_constraints_inh" does not exist postgresql tries to drop the constraint even from descendant table though "only" is specified. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6706: pg_upgrade fails when plpgsql dropped/re-created
Bruce Momjian writes: > On Fri, Jun 29, 2012 at 11:35:15PM -0400, Tom Lane wrote: >> I think you're misjudging the core of the issue. The same thing >> would happen if somebody dropped and recreated the public schema. >> Or anything else that we create at initdb time but allow to be >> dropped. > I just tested dropping and recreating the 'public' schema and pg_upgrade > worked fine. Did it restore the nonstandard ownership of the schema? Your proposed fix for plpgsql won't preserve the previous state of the extension. (Maybe we don't care, but it needs consideration.) regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6706: pg_upgrade fails when plpgsql dropped/re-created
On Sat, Jun 30, 2012 at 11:12:56AM -0400, Tom Lane wrote: > Bruce Momjian writes: > > On Fri, Jun 29, 2012 at 11:35:15PM -0400, Tom Lane wrote: > >> I think you're misjudging the core of the issue. The same thing > >> would happen if somebody dropped and recreated the public schema. > >> Or anything else that we create at initdb time but allow to be > >> dropped. > > > I just tested dropping and recreating the 'public' schema and pg_upgrade > > worked fine. > > Did it restore the nonstandard ownership of the schema? Your proposed > fix for plpgsql won't preserve the previous state of the extension. > (Maybe we don't care, but it needs consideration.) My point was that drop/create of the public schema does not generate a pg_upgrade error like plpgsql does. Let me address the schema question here and the plpgsql issue in the next email. > Did it restore the nonstandard ownership of the schema? No --- drop/create of the public schema produces: test=> \dn+ List of schemas Name | Owner | Access privileges | Description +--+---+- public | postgres | | (1 row) while the original shipped public and the post-upgrade of a drop/created schema are: test=> \dn+ List of schemas Name | Owner | Access privileges | Description +--+--+ public | postgres | postgres=UC/postgres+| standard public schema | | =UC/postgres | (1 row) However, surprisingly, a simple pg_dump/restore also does not preserve the public schema permissions either. :-( -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6706: pg_upgrade fails when plpgsql dropped/re-created
Bruce Momjian writes: > On Sat, Jun 30, 2012 at 11:12:56AM -0400, Tom Lane wrote: >> Did it restore the nonstandard ownership of the schema? > No --- drop/create of the public schema produces: > ... > However, surprisingly, a simple pg_dump/restore also does not preserve > the public schema permissions either. :-( Right. My point is that there is a whole lot of stuff that initdb creates but does not mark "pinned" in pg_depend, with the intention that users could drop it, and perhaps recreate similarly-named objects with different properties. We have never had a very sane story for what would happen to such modified objects during dump/reload, and pg_upgrade is no better (or worse). I don't think there's too much point in thinking about plpgsql alone without also worrying about * system views (including the information schema) * collations * conversions * text search dictionaries Now for a lot of this stuff, it's perhaps reasonable that a major version upgrade would restore the objects to standard state. I'm thinking though that it's rather bad that we treat either the public schema or the plpgsql language that way. In particular, an admin might have wished to remove or restrict those two objects for security reasons, in which case he'd not be very happy if pg_upgrade resurrected them or restored their default permissions. BTW, I think your proposed fix doesn't work even without considering this angle --- it would prevent creation of the duplicate pg_extension row, but the binary-upgrade dump script is still going to try to create the extension's member objects. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
