Re: BUG #6712: PostgreSQL 9.2 beta2: alter table drop constraint does not work on inherited master table

On Mon, Jul 02, 2012 at 04:16:31PM +0530, Amit Kapila wrote:
> > From: pgsql-bugs-owner@postgresql.org [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of
miroslav.sulc@fordfrog.com
> > Sent: Saturday, June 30, 2012 4:28 PM

> > 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.

ONLY shouldn't be necessary, either.

> In function ATExecDropConstraint(), for the constarint "test_constraints_val1_val2_key" con->connoinherit is false,
> due to which it tries to drop the constrint from child table as well.
> I have checked that from function index_constraint_create() when it calls function CreateConstraintEntry(), the flag
fornoinherit passed is false.  
> I think this is the reason of failure for the same.

Agreed.  The other non-CHECK callers also get this wrong:

[local] regression=# select contype,connoinherit,count(*) from pg_constraint group by 1,2;
 contype | connoinherit | count
---------+--------------+-------
 p       | f            |     7
 x       | f            |     2
 c       | f            |    17
 f       | f            |     5

One can construct similar bugs around dropping foreign key and exclusion
constraints.  Though it may be irrelevant for command semantics, additionally
using connoinherit = 't' for contype = 't' (CONSTRAINT_TRIGGER) would
more-accurately represent the nature of those constraints.

Care to prepare a patch with a test case addition?

Thanks,
nm