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

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
The following bug has been logged on the website:

Bug reference: 6712
Logged by: Miroslav Šulc
Email address: miroslav.sulc@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.

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 for noinherit passed is false.
I think this is the reason of failure for the same.

With Regards,
Amit Kapila.

On Mon, Jul 2, 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
The following bug has been logged on the website:

Bug reference: 6712
Logged by: Miroslav Šulc
Email address: miroslav.sulc@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.

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 for noinherit passed is false.
I think this is the reason of failure for the same.

I believe this if fixed in all current versions of Postgres. I tested
9.1, 9.2rc1, and 9.3.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +