Обсуждение: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
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
On Thu, Sep 15, 2022 at 6:32 PM Inzamam Shafiq <inzamam.shafiq@hotmail.com> 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.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?
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
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.
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 <science@misuse.org>
Sent: Friday, September 16, 2022 4:09 AM
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Sent: Friday, September 16, 2022 4:09 AM
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
On Thu, Sep 15, 2022 at 6:32 PM Inzamam Shafiq <inzamam.shafiq@hotmail.com> 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.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?
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
Inzamam Shafiq <inzamam.shafiq@hotmail.com> writes:
> 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
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
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 <tgl@sss.pgh.pa.us>
Sent: Friday, September 16, 2022 11:41 AM
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Cc: Steve Midgley <science@misuse.org>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Sent: Friday, September 16, 2022 11:41 AM
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Cc: Steve Midgley <science@misuse.org>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Inzamam Shafiq <inzamam.shafiq@hotmail.com> writes:
> 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
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
> 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
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
Hi Team,
Can anyone help with this?
Regards,
Inzamam Shafiq
Sr. DBA
From: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Sent: Friday, September 16, 2022 12:51 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Steve Midgley <science@misuse.org>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Sent: Friday, September 16, 2022 12:51 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Steve Midgley <science@misuse.org>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
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 <tgl@sss.pgh.pa.us>
Sent: Friday, September 16, 2022 11:41 AM
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Cc: Steve Midgley <science@misuse.org>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Sent: Friday, September 16, 2022 11:41 AM
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Cc: Steve Midgley <science@misuse.org>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Inzamam Shafiq <inzamam.shafiq@hotmail.com> writes:
> 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
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
> 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
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
On Fri, 16 Sept 2022 at 19:52, Inzamam Shafiq <inzamam.shafiq@hotmail.com> wrote: > 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
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.
On Tue, Sep 20, 2022, 5:46 AM David Rowley <dgrowleyml@gmail.com> wrote:
On Fri, 16 Sept 2022 at 19:52, Inzamam Shafiq
<inzamam.shafiq@hotmail.com> wrote:
> 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