Обсуждение: Unexpected behavior with inherited constraints
Hello all,
Thanks,
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):
begin;
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);
-- 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.
-- By all appearances, my_table_child now has a single inherited constraint.
-- For example, attempting to drop the constraint on my_table_child will fail because it's inherited.
-- Now we want to update the constraint.
alter table my_table drop constraint my_col_constraint;
alter table my_table add constraint my_col_constraint check (my_col >= 1337);
-- Error! After we dropped the constraint on the parent table, the non-inherited constraint
-- "re-appeared" on the child table and we cannot add this new constraint.
I would expect one of the following behaviors: A) it should be obvious that there is more than one constraint on the child table (show both inherited and non-inherited constraint), B) postgres should fail rather than "merging" the constraints, or C) "merging" the constraints should delete the non-inherited constraint on the child table. Right now, "merging" doesn't seem to actually do anything. Perhaps this is unintended behavior?
It's also entirely possible I'm going about this completely wrong. I appreciate any input.
Thanks,
Will
William Yager <wyager@janestreet.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