[YSQL] Unable to use ON CONFLICT on partitioned table

[jannehietamaki]

Jira Link: DB-2865

Description

This query is returning an error ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification:

create table part ( a int, b int, list varchar(5) ) partition by list (list);
create table part_1 partition of part for values in ('beer');
create table part_2 partition of part for values in ('wine');
create unique index iu1 on part_1 (a);
create unique index iu2 on part_2 (a);
insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set b = part.b + 1;

It should work on Postgres 11: https://www.dbi-services.com/blog/insert-on-conflict-with-partitions-finally-works-in-postgresql-11/

[ddorian]

Just tried it on PostgreSQL 11.16 and it doesn't work:

guru=# select version();
                                                              version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.16 (Ubuntu 11.16-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)

guru=# create table part ( a int, b int, list varchar(5) ) partition by list (list);
CREATE TABLE
guru=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
guru=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
guru=# create unique index iu1 on part_1 (a);
CREATE INDEX
guru=# create unique index iu2 on part_2 (a);
CREATE INDEX
guru=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set b = part.b + 1;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

[ddorian]

This works though:

guru=# create unique index iu0 on part(a,list);
CREATE INDEX
guru=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set b = part.b + 1;
INSERT 0 1

[jannehietamaki]

@ddorian yes that works, but when doing geopartitioning it's not good idea to create the index on parent table.

[ddorian]

As a workaround you can upsert directly to the child table I believe. Can that work for you as a workaround?

[jannehietamaki]

Can't do that in my use case because can't resolve the table name easily.

[ddorian]

Can you describe your exact use case?

[jannehietamaki]

I'm doing upsert from trigger, and partition table name is not easily available.

But I'll use update+insert as a workaround for now and waiting if YB will be updated to new Postgres. 2.15 was supposed to be based on v13, but I guess that has not happened?

[ddorian]

to new Postgres. 2.15 was supposed to be based on v13, but I guess that has not happened?

It's still in progress. You can track this issue: #9797

[jannehietamaki]

It's still in progress. You can track this issue: #9797

Already tracking, but there is no progress since it was created last year.

[ddorian]

A recent update from the dev:

This is active work in progress, code porting is done and stabilization is in progress. Since this involves changes to the core module, it needs to go though rigorous testing, we expect this feature to be released in later this year (around fall releases 2.16. or 2.17)

[FranckPachot]

@jannehietamaki the behaviour above (constraint created on child, query on parent) will be the same in PostgreSQL 14:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2106039b5855b6f0b8c4602abbd2645a

The dbi-services blog creates the constraint on the parent. That works in YugabyteDB as well.

What I do with geo-partitioning is mapping the owner / schema / tablespaces to get it transparent. Example:
https://dev.to/aws-heroes/postgresql-index-partition-in-same-tablespace-as-table-2hak

[karthik-ramanathan-3006]

Postgres 15 (as of version 15.7) does not have support for "partitioned table, query on parent" either.

From the (latest version) docs:

INSERT statements with ON CONFLICT clauses are unlikely to work as expected, as the ON CONFLICT action is only taken in case of unique violations on the specified target relation, not its child relations.