Open jannehietamaki opened 2 years ago
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
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
@ddorian yes that works, but when doing geopartitioning it's not good idea to create the index on parent table.
As a workaround you can upsert directly to the child table I believe. Can that work for you as a workaround?
Can't do that in my use case because can't resolve the table name easily.
Can you describe your exact use case?
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?
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: https://github.com/yugabyte/yugabyte-db/issues/9797
It's still in progress. You can track this issue: #9797
Already tracking, but there is no progress since it was created last year.
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)
@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
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.
Jira Link: DB-2865
Description
This query is returning an error
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
:It should work on Postgres 11: https://www.dbi-services.com/blog/insert-on-conflict-with-partitions-finally-works-in-postgresql-11/