Thread: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition

The following bug has been logged on the website:

Bug reference:      16714
Logged by:          Andy S
Email address:      gatekeeper.mail@gmail.com
PostgreSQL version: 11.2
Operating system:   Gentoo Linux
Description:

create table tbl (
    part_key1 int,
    part_key2 int,
    part_key3 int,
    part_key4 int,
    values_columns varchar
) partition by list(part_key1);

create table tbl_p1 partition of tbl for values in (1) partition by
list(part_key2);
create table tbl_p1_p2 partition of tbl_p1 for values in (1) partition by
range (part_key3);
create table tbl_p1_p2_p3 partition of tbl_p1_p2  for values from (0) to
(100) partition by HASH(part_key4);

create table tbl_p1_p2_p3_p4_0 partition of tbl_p1_p2_p3 (constraint
tbl_p1_p2_p3_0_pkey primary key (part_key4)) for values with (modulus 4,
remainder 0);
create table tbl_p1_p2_p3_p4_1 partition of tbl_p1_p2_p3 (constraint
tbl_p1_p2_p3_1_pkey primary key (part_key4)) for values with (modulus 4,
remainder 1);
create table tbl_p1_p2_p3_p4_2 partition of tbl_p1_p2_p3 (constraint
tbl_p1_p2_p3_2_pkey primary key (part_key4)) for values with (modulus 4,
remainder 2);
create table tbl_p1_p2_p3_p4_3 partition of tbl_p1_p2_p3 (constraint
tbl_p1_p2_p3_3_pkey primary key (part_key4)) for values with (modulus 4,
remainder 3);

insert into tbl values (1, 1, 1, 1, 'a');

insert into tbl values (1, 1, 1, 1, 'b');
-- ERROR:  duplicate key value violates unique constraint
"tbl_p1_p2_p3_0_pkey"
-- DETAIL:  Key (part_key4)=(1) already exists.

insert into tbl values (1, 1, 1, 1, 'b') on conflict (part_key4) do update
set values_columns = excluded.values_columns;
-- ERROR:  there is no unique or exclusion constraint matching the ON
CONFLICT specification

PG Bug reporting form <noreply@postgresql.org> writes:
> insert into tbl values (1, 1, 1, 1, 'b') on conflict (part_key4) do update
> set values_columns = excluded.values_columns;
> -- ERROR:  there is no unique or exclusion constraint matching the ON
> CONFLICT specification

I see no bug here.  The partitioned table indeed does not have any
such index.  Moreover, if you had tried to make one, you would have
gotten

ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "tbl" lacks column "part_key1" which is part of the partition key.

The short answer here is that uniqueness constraints on the individual
partitions are not a substitute for a constraint on the whole partitioned
table.

            regards, tom lane

Andy S <gatekeeper.mail@gmail.com> writes:
> That's then either a docs bug. The documentation states:
>> For each individual row proposed for insertion, either the insertion
>> proceeds, or, if an *arbiter* constraint or index specified by
>> *conflict_target* is violated, the alternative *conflict_action* is taken

Yeah, but you did not specify a valid arbiter constraint.  ON CONFLICT is
not a get-out-of-jail-free card; it will not trap any error whatsoever,
only a detected unique-constraint violation.

What you probably want here is to declare (part_key1, part_key4)
as the primary key of the partitioned table, and use that in the
ON CONFLICT spec.  That will save you from having to make constraints
on the individual partitions, too.

            regards, tom lane

Hi,

On 2020-11-13 21:30:39 +0300, Andy S wrote:
> No, it's a bug

At best it is a potentially desirable feature. One that isn't trivial to
implement. We'd have to scan the entire partition tree to ensure there's
a matching constraint on all partitions. And add some more complicated
way to do plan invalidation, because somebody could add a new partition
without a matching constraint - which'd not be detected in a trivial
implementation, because the arbiter determination happens at parse
analysis time.


> Also:
> the certain partition to which data is to be inserted is computed at query
> planning stage the very stage where the planner could also find out if an
> index matching the given specification could be inferred since it's the
> only index that matters.

That is not generally the case. You can have parametrized values that
are inserted, and you can have multi-row inserts. In both cases you
cannot make this decision at plan time.

- Andres

Hi,

On 2020-11-19 02:49:20 +0300, Andy S wrote:
> Oh! What a gem I mined in sources:
> 
> branch: REL_11_STABLE
> src/backend/executor/nodeModifyTable.c:
>          * BEFORE ROW INSERT Triggers.
>          *
>          * Note: We fire BEFORE ROW TRIGGERS for every attempted insertion
> in an
>          * INSERT ... ON CONFLICT statement.  We cannot check for constraint
>          * violations before firing these triggers, because they can change
> the
>          * values to insert.  Also, they can run arbitrary user-defined
> code with
>          * side-effects that we can't cancel by just not inserting the
> tuple.
>          */
> 
> Still not the place where ON CONFLICT could be validated on per-row
> basis?

On conflict arbiter determination happens much earlier - and that can't,
as I have explained earlier, really be changed. We need to know which
index etc this applies to during parse analysis, not during execution
time. This happens as part of transformOnConflictClause() called from
transformInsertStmt().

Greetings,

Andres Freund