public inbox for [email protected]help / color / mirror / Atom feed
ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 8+ messages / 5 participants [nested] [flat]
* ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 @ 2022-09-15 17:31 Inzamam Shafiq <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Inzamam Shafiq @ 2022-09-15 17:31 UTC (permalink / raw) To: [email protected] <[email protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 @ 2022-09-15 23:09 Steve Midgley <[email protected]> parent: Inzamam Shafiq <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Steve Midgley @ 2022-09-15 23:09 UTC (permalink / raw) To: Inzamam Shafiq <[email protected]>; +Cc: [email protected] <[email protected]> On Thu, Sep 15, 2022 at 6:32 PM Inzamam Shafiq <[email protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 @ 2022-09-16 05:49 Inzamam Shafiq <[email protected]> parent: Steve Midgley <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Inzamam Shafiq @ 2022-09-16 05:49 UTC (permalink / raw) To: Steve Midgley <[email protected]>; +Cc: [email protected] <[email protected]> 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 protected]> Sent: Friday, September 16, 2022 4:09 AM To: Inzamam Shafiq <[email protected]> Cc: [email protected] <[email protected]> Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 On Thu, Sep 15, 2022 at 6:32 PM Inzamam Shafiq <[email protected]<mailto:[email protected]>> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 @ 2022-09-16 06:41 Tom Lane <[email protected]> parent: Inzamam Shafiq <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Tom Lane @ 2022-09-16 06:41 UTC (permalink / raw) To: Inzamam Shafiq <[email protected]>; +Cc: Steve Midgley <[email protected]>; [email protected] <[email protected]> Inzamam Shafiq <[email protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 @ 2022-09-16 07:51 Inzamam Shafiq <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Inzamam Shafiq @ 2022-09-16 07:51 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Steve Midgley <[email protected]>; [email protected] <[email protected]> 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 protected]> Sent: Friday, September 16, 2022 11:41 AM To: Inzamam Shafiq <[email protected]> Cc: Steve Midgley <[email protected]>; [email protected] <[email protected]> Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 Inzamam Shafiq <[email protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 @ 2022-09-20 09:29 Inzamam Shafiq <[email protected]> parent: Inzamam Shafiq <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: Inzamam Shafiq @ 2022-09-20 09:29 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Steve Midgley <[email protected]>; [email protected] <[email protected]> Hi Team, Can anyone help with this? Regards, Inzamam Shafiq Sr. DBA ________________________________ From: Inzamam Shafiq <[email protected]> Sent: Friday, September 16, 2022 12:51 PM To: Tom Lane <[email protected]> Cc: Steve Midgley <[email protected]>; [email protected] <[email protected]> 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 <[email protected]> Sent: Friday, September 16, 2022 11:41 AM To: Inzamam Shafiq <[email protected]> Cc: Steve Midgley <[email protected]>; [email protected] <[email protected]> Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 Inzamam Shafiq <[email protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 @ 2022-09-20 09:46 David Rowley <[email protected]> parent: Inzamam Shafiq <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: David Rowley @ 2022-09-20 09:46 UTC (permalink / raw) To: Inzamam Shafiq <[email protected]>; +Cc: Tom Lane <[email protected]>; Steve Midgley <[email protected]>; [email protected] <[email protected]> On Fri, 16 Sept 2022 at 19:52, Inzamam Shafiq <[email protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 @ 2022-09-22 10:39 Geri Wright <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Geri Wright @ 2022-09-22 10:39 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Inzamam Shafiq <[email protected]>; Tom Lane <[email protected]>; Steve Midgley <[email protected]>; [email protected] 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 On Tue, Sep 20, 2022, 5:46 AM David Rowley <[email protected]> wrote: > On Fri, 16 Sept 2022 at 19:52, Inzamam Shafiq > <[email protected]> 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 > > > ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2022-09-22 10:39 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-09-15 17:31 ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 Inzamam Shafiq <[email protected]> 2022-09-15 23:09 ` Steve Midgley <[email protected]> 2022-09-16 05:49 ` Inzamam Shafiq <[email protected]> 2022-09-16 06:41 ` Tom Lane <[email protected]> 2022-09-16 07:51 ` Inzamam Shafiq <[email protected]> 2022-09-20 09:29 ` Inzamam Shafiq <[email protected]> 2022-09-20 09:46 ` David Rowley <[email protected]> 2022-09-22 10:39 ` Geri Wright <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox