Re: Unexpected behavior with inherited constraints

William Yager <wyager(at)janestreet(dot)com> writes:
> I was recently debugging some database infrastructure and I ran across an
> issue with postgres "merging" inherited constraints with pre-existing
> constraints. Please see the following minimal example (run on postgres 9.5):

AFAICS this is all expected behavior. The concept you're missing is that
a single constraint can have multiple origins, either "local" to a table
or inherited from parent table(s). It doesn't go away as long as any of
those origins is in effect.

> create table my_table (my_col int);
> create table my_table_child () inherits (my_table);
> -- Experiment with constraint on child table.
> alter table my_table_child add constraint my_col_constraint check (my_col
> >= 0);

At this point you have

select conrelid::regclass, conislocal, coninhcount from pg_constraint where conname = 'my_col_constraint';
conrelid | conislocal | coninhcount
----------------+------------+-------------
my_table_child | t | 0
(1 row)

> -- Some time later, add it to the parent table.
> alter table my_table add constraint my_col_constraint check (my_col >= 0);
> -- Postgres gives a warning, not an error, and says it will merge the
> constraints.

You get

NOTICE: merging constraint "my_col_constraint" with inherited definition

and now the situation is

select conrelid::regclass, conislocal, coninhcount from pg_constraint where conname = 'my_col_constraint';
conrelid | conislocal | coninhcount
----------------+------------+-------------
my_table | t | 0
my_table_child | t | 1
(2 rows)

> -- Now we want to update the constraint.
> alter table my_table drop constraint my_col_constraint;

This leaves us back at

select conrelid::regclass, conislocal, coninhcount from pg_constraint where conname = 'my_col_constraint';
conrelid | conislocal | coninhcount
----------------+------------+-------------
my_table_child | t | 0
(1 row)

You'd need to drop the child's constraint too, ie reverse both of your
ADD CONSTRAINT actions not just one of them, before the constraint would
disappear from the child.

regards, tom lane