Oddity in error handling of constraint violation in ExecConstraints for partitioned tables

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Oddity in error handling of constraint violation in ExecConstraints for partitioned tables
Date: 2017-07-06 07:06:04
Message-ID: ee12f648-8907-77b5-afc0-2980bcb0aa37@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Here is an example:

postgres=# create table col_desc (a int, b int) partition by list (a);
postgres=# create table col_desc_1 partition of col_desc for values in (1);
postgres=# alter table col_desc_1 add check (b > 0);
postgres=# create role col_desc_user;
postgres=# grant insert on col_desc to col_desc_user;
postgres=# revoke select on col_desc from col_desc_user;
postgres=# set role col_desc_user;
postgres=> insert into col_desc values (1, -1) returning 1;
ERROR: new row for relation "col_desc_1" violates check constraint
"col_desc_1_b_check"
DETAIL: Failing row contains (a, b) = (1, -1).

Looks good, but

postgres=> reset role;
postgres=# create table result (f1 text default 'foo', f2 text default
'bar', f3 int);
postgres=# grant insert on result to col_desc_user;
postgres=# set role col_desc_user;
postgres=> with t as (insert into col_desc values (1, -1) returning 1)
insert into result (f3) select * from t;
ERROR: new row for relation "col_desc_1" violates check constraint
"col_desc_1_b_check"

Looks odd to me because the error message doesn't show any DETAIL info;
since the CTE query, which produces the message, is the same as the
above query, the message should also be the same as the one for the
above query. I think the reason for that is: ExecConstraints looks at
an incorrect resultRelInfo to build the message for a violating tuple
that has been routed *in the case where the partitioned table isn't the
primary ModifyTable node*, which leads to deriving an incorrect
modifiedCols and then invoking ExecBuildSlotValueDescription with the
wrong bitmap. I think this should be fixed. Attached is a patch for that.

Best regards,
Etsuro Fujita

Attachment Content-Type Size
fix-error-handling-in-execconstrains.patch text/plain 10.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2017-07-06 07:08:38 Re: pgsql 10: hash indexes testing
Previous Message Etsuro Fujita 2017-07-06 06:58:40 Re: Update comment in ExecPartitionCheck