tldr: Seems to be broken by http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4da99ea4231e3d8bbf28b666748c1028e7b7d665 : commit 4da99ea4231e3d8bbf28b666748c1028e7b7d665 Author: Robert Haas <rh...@postgresql.org> Date: Mon Jun 27 10:27:17 2011 -0400
Avoid having two copies of the HOT-chain search logic. Full story: While playing with the non inheritable constraints patch (https://commitfest.postgresql.org/action/patch_view?id=611) I noticed the following sequence was broken: create table colors (c text check (not null)); create table colors_i () inherits (colors); alter table only colors drop constraint colors_check; ERROR: relation 16412 has non-inherited constraint "colors_check" Naturally I assumed it was the patches fault, but after further digging turns out it was not. I bisected it down to the above commit (for those that have not tried git bisect and git bisect run its great for this kind of thing). The basic problem seems to be after we update pg_constraint to decrement inhcount for a child table we call CommandCounterIncrement(); then we fetch the next row from pg_constraint... which happens to be the row we just updated. So we try to decrement it again, only now its at 0 which shouldn't happen so we error out. The simple fix seemed to be to move the CommandCounterIncrement() outside of the while(... systable_getnext()) loop. Im not sure if that's the right thing to-do or if there is some other bug here...
*** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 6734,6739 **** ATExecDropConstraint(Relation rel, const char *constrName, --- 6734,6740 ---- { Oid childrelid = lfirst_oid(child); Relation childrel; + bool updated = false; /* find_inheritance_children already got lock */ childrel = heap_open(childrelid, NoLock); *************** *** 6790,6798 **** ATExecDropConstraint(Relation rel, const char *constrName, con->coninhcount--; simple_heap_update(conrel, ©_tuple->t_self, copy_tuple); CatalogUpdateIndexes(conrel, copy_tuple); ! ! /* Make update visible */ ! CommandCounterIncrement(); } } else --- 6791,6798 ---- con->coninhcount--; simple_heap_update(conrel, ©_tuple->t_self, copy_tuple); CatalogUpdateIndexes(conrel, copy_tuple); ! /* need to call CommandCounterIncrement() */ ! updated = true; } } else *************** *** 6807,6815 **** ATExecDropConstraint(Relation rel, const char *constrName, simple_heap_update(conrel, ©_tuple->t_self, copy_tuple); CatalogUpdateIndexes(conrel, copy_tuple); ! ! /* Make update visible */ ! CommandCounterIncrement(); } heap_freetuple(copy_tuple); --- 6807,6814 ---- simple_heap_update(conrel, ©_tuple->t_self, copy_tuple); CatalogUpdateIndexes(conrel, copy_tuple); ! /* need to call CommandCounterIncrement() */ ! updated = true; } heap_freetuple(copy_tuple); *************** *** 6824,6829 **** ATExecDropConstraint(Relation rel, const char *constrName, --- 6823,6832 ---- constrName, RelationGetRelationName(childrel)))); + /* Make update visible */ + if (updated) + CommandCounterIncrement(); + heap_close(childrel, NoLock); } *** a/src/test/regress/expected/alter_table.out --- b/src/test/regress/expected/alter_table.out *************** *** 2103,2105 **** ALTER TABLE tt7 NOT OF; --- 2103,2112 ---- x | integer | y | numeric(8,2) | + -- make sure we can drop a constraint on the parent but it remains on the child + create table test_drop_constr_parent (c text check (c is not null)); + create table test_drop_constr_child () inherits (test_drop_constr_parent); + alter table only test_drop_constr_parent drop constraint "test_drop_constr_parent_c_check"; + -- should fail + insert into test_drop_constr_child (c) values (NULL); + ERROR: new row for relation "test_drop_constr_child" violates check constraint "test_drop_constr_parent_c_check" *** a/src/test/regress/sql/alter_table.sql --- b/src/test/regress/sql/alter_table.sql *************** *** 1456,1458 **** CREATE TYPE tt_t1 AS (x int, y numeric(8,2)); --- 1456,1465 ---- ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table ALTER TABLE tt7 NOT OF; \d tt7 + + -- make sure we can drop a constraint on the parent but it remains on the child + create table test_drop_constr_parent (c text check (c is not null)); + create table test_drop_constr_child () inherits (test_drop_constr_parent); + alter table only test_drop_constr_parent drop constraint "test_drop_constr_parent_c_check"; + -- should fail + insert into test_drop_constr_child (c) values (NULL);
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers