Subject: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12

From: Inzamam Shafiq <[email-masked]>
Date: Thu, 15 Sep 2022 17:31:47 +0000

Hi Team,

I have a partitioned table in postgresql12 and the unique constraint is applied on the child tables, when I use ON CONFLICT clause it returned an error "duplicate key value violates unique constraint "..."", I tried to replicate the scenario on a test table and created a unique index on partition column and the unique column but when I try to insert data it again return the same error.

This works successfully when I directly insert data in the child table.

What could be the way forward to get this work on parent table?

Thanks.

Cheers,

Inzamam Shafiq
Sr. DBA

---

From: Steve Midgley <[email-masked]>
Date: Fri, 16 Sep 2022 00:09:01 +0100

On Thu, Sep 15, 2022 at 6:32 PM Inzamam Shafiq <[email-masked]>
wrote:

>
> Hi Team,
>
> I have a partitioned table in postgresql12 and the unique constraint is
> applied on the child tables, when I use ON CONFLICT clause it returned an
> error "duplicate key value violates unique constraint "..."", I tried to
> replicate the scenario on a test table and created a unique index on
> partition column and the unique column but when I try to insert data it
> again return the same error.
>

Please send DDL, sample data (ideally as inserts), and the sql statement
that causes the error. Given your description of the problem, I think if
you do that, you'll find people on this list solve the issue very quickly
for you. Without that, it's hard to diagnose, at least for me.

Best,
Steve

---

From: Inzamam Shafiq <[email-masked]>
Date: Fri, 16 Sep 2022 05:49:43 +0000

Following is the DDL,

CREATE TABLE testpart (
      id bigserial NOT NULL,
      uniqueid varchar(60) NULL,
      username varchar(60) NULL,
      starttime timestamp NULL,
      stoptime timestamp NULL
)
PARTITION BY RANGE (starttime)
;

ALTER TABLE testpart OWNER TO postgres;

CREATE TABLE part1 PARTITION OF testpart (
      CONSTRAINT part1_uniqueid_key UNIQUE (uniqueid),
      CONSTRAINT part1_pkey PRIMARY KEY (id)
)FOR VALUES FROM ('2022-09-15 00:00:00') TO ('2022-09-21 00:00:00');

ALTER TABLE part1 OWNER TO postgres;

ALTER TABLE testpart ADD CONSTRAINT uniqueid_const UNIQUE (uniqueid, starttime);

INSERT INTO testpart
VALUES(2, 'Microsoft','hotline', now(), now() + interval '1' hour)
ON CONFLICT (uniqueid,starttime)
DO NOTHING;  --- This gives Error

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "part1_uniqueid_key"
  Detail: Key (uniqueid)=(Microsoft) already exists.

INSERT INTO testpart
VALUES(2, 'Microsoft','hotline', now(), now() + interval '1' hour)
ON CONFLICT ON CONSTRAINT uniqueid_const
DO NOTHING; --This is equivalent to above statement

INSERT INTO part1
VALUES(3, 'Microsoft','hotline', now(), now() + interval '1' hour)
ON CONFLICT (uniqueid)
DO NOTHING; -- This works perfectly

Regards,

Inzamam Shafiq
Sr. DBA
________________________________
From: Steve Midgley <[email-masked]>
Sent: Friday, September 16, 2022 4:09 AM
To: Inzamam Shafiq <[email-masked]>
Cc: [email-masked] <[email-masked]>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12

On Thu, Sep 15, 2022 at 6:32 PM Inzamam Shafiq <[email-masked]<mailto:[email-masked]>> wrote:

Hi Team,

Please send DDL, sample data (ideally as inserts), and the sql statement that causes the error. Given your description of the problem, I think if you do that, you'll find people on this list solve the issue very quickly for you. Without that, it's hard to diagnose, at least for me.

Best,
Steve

---

From: Tom Lane <[email-masked]>
Date: Fri, 16 Sep 2022 02:41:29 -0400

> DO NOTHING;  --- This gives Error

The precise sequence you give here doesn't fail for me.  However,
this table has three different uniqueness constraints: there's
part1_uniqueid_key on uniqueid alone, part1_pkey on id alone,
and then uniqueid_const on uniqueid plus starttime.  Your ON
CONFLICT clause will only trap conflicts on the last one.
It's an implementation detail whether that gets checked before
or after the constraint on uniqueid alone.  I don't really
feel a need to make that better-defined, because what in the
world is the use for a constraint on uniqueid plus starttime
alongside a constraint on uniqueid alone?

			regards, tom lane

---

From: Inzamam Shafiq <[email-masked]>
Date: Fri, 16 Sep 2022 07:51:54 +0000

Thanks Tom.

From your point what I understand is to remove unique constraint "uniqueid_const" from child table so that this will work, I have done this on a test table and yes, it is working fine.

Now the problem is the actual table is quite big and in production, so dropping the constraint will have any affect/issues on data integrity and performance?

The actual problem is, the application have multiple servers and they work in a round robin method, so once the record is inserted from one server if the same record is inserted from another server, then we should be assured that there is no duplicate entry (as I suspect that creating unique constraint on uniqueid and starttime column will create duplicate entry because I checked the error messages, the timestamp is different for the same record/uniqiueid which means constraint on 2 columns will not work as expected), currently we are receiving around 500 errors like this on daily basis.

Is there anyway we can create constraint on column only which are not part of partition key?

Regards,

Inzamam Shafiq
Sr. DBA
________________________________
From: Tom Lane <[email-masked]>
Sent: Friday, September 16, 2022 11:41 AM
To: Inzamam Shafiq <[email-masked]>
Cc: Steve Midgley <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12

                        regards, tom lane

---

From: Inzamam Shafiq <[email-masked]>
Date: Tue, 20 Sep 2022 09:29:53 +0000

Hi Team,

Can anyone help with this?

Regards,

Inzamam Shafiq
Sr. DBA
________________________________
From: Inzamam Shafiq <[email-masked]>
Sent: Friday, September 16, 2022 12:51 PM
To: Tom Lane <[email-masked]>
Cc: Steve Midgley <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12

Thanks Tom.

Regards,

Inzamam Shafiq
Sr. DBA

                        regards, tom lane

---

From: David Rowley <[email-masked]>
Date: Tue, 20 Sep 2022 21:46:07 +1200

> Is there anyway we can create constraint on column only which are not part of partition key?

Unfortunately, there is no way to create a unique or primary key
constraint unless the constraint contains all columns from the
partition key.   This is explained in the limitations section in [1].

If you need such a constraint, then you might want to consider
changing your partition key.

David

[1] https://www.postgresql.org/docs/12/ddl-partitioning.html

---

From: Geri Wright <[email-masked]>
Date: Thu, 22 Sep 2022 06:39:27 -0400

You can create a Constraint trigger to check the new values of the desired
columns when updating or insering them. This trigger will fire for each
partition but checks the data in the entire table.  The trigger should
obtain an advisory lock for the unique columns . Here is a blog for more
information.
https://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html