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

Hi Team,

Can anyone help with this?

Regards,

Inzamam Shafiq
Sr. DBA
________________________________
From: Inzamam Shafiq <inzamam(dot)shafiq(at)hotmail(dot)com>
Sent: Friday, September 16, 2022 12:51 PM
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Steve Midgley <science(at)misuse(dot)org>; pgsql-sql(at)lists(dot)postgresql(dot)org <pgsql-sql(at)lists(dot)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Friday, September 16, 2022 11:41 AM
To: Inzamam Shafiq <inzamam(dot)shafiq(at)hotmail(dot)com>
Cc: Steve Midgley <science(at)misuse(dot)org>; pgsql-sql(at)lists(dot)postgresql(dot)org <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12

Inzamam Shafiq <inzamam(dot)shafiq(at)hotmail(dot)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