pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDuplicate key error
22+ messages / 7 participants
[nested] [flat]
* Duplicate key error
@ 2021-03-03 15:23 Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 22+ messages in thread
From: Andrus @ 2021-03-03 15:23 UTC (permalink / raw)
To: pgsql-general
Hi!
Sometimes duplicate key error
duplicate key value violates unique constraint "session_pkey"
Key (workplace)=(WIN-N9BSKUNKBC8 ) already exists.
occurs in script:
delete from session where workplace='WIN-N9BSKUNKBC8' ;
INSERT INTO session (workplace,ipaddress,logintime,loggeduser)
SELECT 'WIN-N9BSKUNKBC8' ,
inet_client_addr()::CHAR(14),current_timestamp::CHAR(28),CURRENT_USER
WHERE NOT EXISTS (SELECT 1 FROM session WHERE workplace='WIN-N9BSKUNKBC8' )
Sript is running form windows task scheduler on every 10 minutes. Error
occurs only sometimes.
How to fix this ?
Table is defined as
CREATE TABLE public.session
(
loggeduser character(10) ,
workplace character(16) NOT NULL,
ipaddress character(20) ,
logintime character(28) ,
activity timestamp with time zone,
CONSTRAINT session_pkey PRIMARY KEY (workplace)
)
Environment:
PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit
Windows server 2019
psqlODBC driver 13.00.0000
I already asked this in this list many years ago. In this case Tom
replies that is looks like index race condition bug which was fixed in
later Postgres 9 version.
However this issue still exists in Postgres 13.1
Andrus.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
@ 2021-03-03 15:42 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
0 siblings, 1 reply; 22+ messages in thread
From: Adrian Klaver @ 2021-03-03 15:42 UTC (permalink / raw)
To: Andrus <kobruleht2@hot.ee>; pgsql-general
On 3/3/21 7:23 AM, Andrus wrote:
> Hi!
>
> Sometimes duplicate key error
>
> duplicate key value violates unique constraint "session_pkey"
> Key (workplace)=(WIN-N9BSKUNKBC8 ) already exists.
>
> occurs in script:
>
> delete from session where workplace='WIN-N9BSKUNKBC8' ;
> INSERT INTO session (workplace,ipaddress,logintime,loggeduser)
> SELECT 'WIN-N9BSKUNKBC8' ,
> inet_client_addr()::CHAR(14),current_timestamp::CHAR(28),CURRENT_USER
> WHERE NOT EXISTS (SELECT 1 FROM session WHERE workplace='WIN-N9BSKUNKBC8' )
>
> Sript is running form windows task scheduler on every 10 minutes. Error
> occurs only sometimes.
There is no other process inserting to this table?
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
@ 2021-03-03 16:08 ` Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 22+ messages in thread
From: Andrus @ 2021-03-03 16:08 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Hi!
> There is no other process inserting to this table?
>
There may be other processes in this server trying to insert same
primary key value (server name).
Last inserted row data should remain.
Andrus.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
@ 2021-03-03 16:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 18:35 ` Re: Duplicate key error Michael Lewis <mlewis@entrata.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
0 siblings, 2 replies; 22+ messages in thread
From: Adrian Klaver @ 2021-03-03 16:16 UTC (permalink / raw)
To: Andrus <kobruleht2@hot.ee>; pgsql-general
On 3/3/21 8:08 AM, Andrus wrote:
> Hi!
>
>> There is no other process inserting to this table?
>>
> There may be other processes in this server trying to insert same
> primary key value (server name).
And if that name already exists there would be a duplicate key error.
>
> Last inserted row data should remain.
I'm not understanding the above.
>
> Andrus.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
@ 2021-03-03 18:35 ` Michael Lewis <mlewis@entrata.com>
2021-03-03 20:02 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
1 sibling, 1 reply; 22+ messages in thread
From: Michael Lewis @ 2021-03-03 18:35 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Andrus <kobruleht2@hot.ee>; pgsql-general
It sounds like this should be re-written as 'insert on conflict do update'
statement.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 18:35 ` Re: Duplicate key error Michael Lewis <mlewis@entrata.com>
@ 2021-03-03 20:02 ` Andrus <kobruleht2@hot.ee>
0 siblings, 0 replies; 22+ messages in thread
From: Andrus @ 2021-03-03 20:02 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general
Hi!
>It sounds like this should be re-written as 'insert on conflict do
update' statement.
It should work in Postgres 9 also. on confilct is not available in
postgres 9.
This code does not insert duplicate key valuse ? Why it throws error ?
You can create testcase to run this code from multiple processes to try
reproduce the error.
Andrus.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
@ 2021-03-03 19:59 ` Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 22+ messages in thread
From: Andrus @ 2021-03-03 19:59 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Hi!
>
> And if that name already exists there would be a duplicate key error.
Name cannot exist: First delete deletes its value. Tables are not
changed by external process during transaction. As double insurance,
insert perfoms additional existence check and adds only if key does not
exist.
>Last inserted row data should remain.
>
> I'm not understanding the above.
This table should contain last login time, user and ip address. It
should be updated by every process on login. For this old entry is
removed if it exists and new entry with same primary key is added.
Andrus.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
@ 2021-03-03 20:19 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 06:14 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
0 siblings, 1 reply; 22+ messages in thread
From: Adrian Klaver @ 2021-03-03 20:19 UTC (permalink / raw)
To: Andrus <kobruleht2@hot.ee>; pgsql-general
On 3/3/21 11:59 AM, Andrus wrote:
> Hi!
>>
>> And if that name already exists there would be a duplicate key error.
>
> Name cannot exist: First delete deletes its value. Tables are not
> changed by external process during transaction. As double insurance,
> insert perfoms additional existence check and adds only if key does not
> exist.
The error message:
"
duplicate key value violates unique constraint "session_pkey"
Key (workplace)=(WIN-N9BSKUNKBC8 ) already exists.
"
says something else is inserting/updating using that key value. So
obviously your script is not catching all the conflicts. At this point
your best bet is to monitor the Postgres log and see what else is
happening at the time of the error. I'm guessing you will find another
process working on that table.
>
> >Last inserted row data should remain.
>>
>> I'm not understanding the above.
>
> This table should contain last login time, user and ip address. It
> should be updated by every process on login. For this old entry is
> removed if it exists and new entry with same primary key is added.
>
> Andrus.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
@ 2021-03-04 06:14 ` Andrus <kobruleht2@hot.ee>
2021-03-04 06:57 ` Re: Duplicate key error Ron <ronljohnsonjr@gmail.com>
2021-03-04 16:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 22+ messages in thread
From: Andrus @ 2021-03-04 06:14 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Hi!
> says something else is inserting/updating using that key value. So
> obviously your script is not catching all the conflicts.
> At this point your best bet is to monitor the Postgres log and see
what else is happening at the time of the error. I'm guessing you will
find another process working on that table.
It looks like other process has added same row during trancaction.
How to change script so that it works starting at Postgres 9.0 and does
not cause error in this case ?
Andrus.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 06:14 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
@ 2021-03-04 06:57 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Ron @ 2021-03-04 06:57 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 3/4/21 12:14 AM, Andrus wrote:
> Hi!
>> says something else is inserting/updating using that key value. So
>> obviously your script is not catching all the conflicts.
>
> > At this point your best bet is to monitor the Postgres log and see what
> else is happening at the time of the error. I'm guessing you will find
> another process working on that table.
>
> It looks like other process has added same row during trancaction.
>
> How to change script so that it works starting at Postgres 9.0 and does
> not cause error in this case ?
>
Having all the processes which try to update that table explicitly set a
serializable transaction would probably eliminate the duplicate key error.
There's no free lunch, though: you'd have to handle the blocking.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 06:14 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
@ 2021-03-04 16:42 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 17:17 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
1 sibling, 1 reply; 22+ messages in thread
From: Adrian Klaver @ 2021-03-04 16:42 UTC (permalink / raw)
To: Andrus <kobruleht2@hot.ee>; pgsql-general
On 3/3/21 10:14 PM, Andrus wrote:
> Hi!
>> says something else is inserting/updating using that key value. So
>> obviously your script is not catching all the conflicts.
>
> > At this point your best bet is to monitor the Postgres log and see
> what else is happening at the time of the error. I'm guessing you will
> find another process working on that table.
>
> It looks like other process has added same row during trancaction.
>
> How to change script so that it works starting at Postgres 9.0 and does
> not cause error in this case ?
Since we have not actually seen the entire script nor have any idea what
the other process is, there is no way to answer this.
>
> Andrus.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 06:14 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-04 16:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
@ 2021-03-04 17:17 ` Andrus <kobruleht2@hot.ee>
2021-03-04 18:13 ` Re: Duplicate key error Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 22+ messages in thread
From: Andrus @ 2021-03-04 17:17 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Hi!
> Since we have not actually seen the entire script nor have any idea
> what the other process is, there is no way to answer this.
This is the same whole script. It will ran by multiple scheduled tasks,
maybe at same time.
It registers logged in user. Different processes may have same user
name. In this case one row should remain.
I can just ignore duplicate key error on commit but maybe there is some
better way not to cause error.
Andrus.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 06:14 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-04 16:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 17:17 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
@ 2021-03-04 18:13 ` Michael Lewis <mlewis@entrata.com>
2021-03-04 18:23 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
0 siblings, 1 reply; 22+ messages in thread
From: Michael Lewis @ 2021-03-04 18:13 UTC (permalink / raw)
To: Andrus <kobruleht2@hot.ee>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Why just do a plain update, relying on row level locking to serialize
requests properly, and then just do an insert where not exists? Is there
value in doing the delete? I don't see it.
Note- On conflict clause is supported from 9.5+ and that is already past
EOL. Upgrading to at least v10 is recommended.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 06:14 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-04 16:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 17:17 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-04 18:13 ` Re: Duplicate key error Michael Lewis <mlewis@entrata.com>
@ 2021-03-04 18:23 ` Andrus <kobruleht2@hot.ee>
2021-03-04 19:23 ` Re: Duplicate key error Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 22+ messages in thread
From: Andrus @ 2021-03-04 18:23 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Hi
>Why just do a plain update, relying on row level locking to serialize
requests properly, and then just do an insert where not exists? Is there
value in doing the delete? I don't see it.
This is an option. How to do update+insert in 9+ in SQL ? Or should
plpgsql procedure created for this ?
After insert if other process inserts same key value, transaction still
ways. Should manual locking used or is there better method.
Andrus.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 06:14 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-04 16:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 17:17 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-04 18:13 ` Re: Duplicate key error Michael Lewis <mlewis@entrata.com>
2021-03-04 18:23 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
@ 2021-03-04 19:23 ` Michael Lewis <mlewis@entrata.com>
2021-03-04 19:46 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
0 siblings, 1 reply; 22+ messages in thread
From: Michael Lewis @ 2021-03-04 19:23 UTC (permalink / raw)
To: Andrus <kobruleht2@hot.ee>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
I just meant a regular update (which might impact 0 rows) and then insert
(not exists) like you are doing already.
--transaction still ways. Should manual locking used or is there better
method.
I don't follow what you mean.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 19:59 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 06:14 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-04 16:42 ` Re: Duplicate key error Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 17:17 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-04 18:13 ` Re: Duplicate key error Michael Lewis <mlewis@entrata.com>
2021-03-04 18:23 ` Re: Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-04 19:23 ` Re: Duplicate key error Michael Lewis <mlewis@entrata.com>
@ 2021-03-04 19:46 ` Andrus <kobruleht2@hot.ee>
0 siblings, 0 replies; 22+ messages in thread
From: Andrus @ 2021-03-04 19:46 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Hi!
>I just meant a regular update (which might impact 0 rows) and then
insert (not exists) like you are doing already.
This causes duplicate key exception if other process adds same row to
table at same time.
>
> >--transaction still ways. Should manual locking used or is there
> better method.
> >I don't follow what you mean.
Allow access to this table for single transaction only. Wait for
exclusive lock , do update/insert, release exclusive lock.
Andrus.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Duplicate key error
@ 2024-11-11 05:40 yudhi s <learnerdatabase99@gmail.com>
2024-11-11 08:27 ` Re: Duplicate key error Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
2024-11-11 20:04 ` Re: Duplicate key error Peter Geoghegan <pg@bowt.ie>
0 siblings, 2 replies; 22+ messages in thread
From: yudhi s @ 2024-11-11 05:40 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
We have a merge query as below for a partition table which is range
partitioned on a truncated date column 'part_date'. And the only unique key
in this table is a composite primary key on (id, part_date). And this merge
queries ON condition is based on one of the columns i.e ID which is the
leading column of the PK.So it means it will ensure no duplication happens
for ID values, but still we are seeing "duplicate key" error. So what is
the possible reason here or are we encountering any buggy behaviour here?
WITH source_data (col1, col2, col3.....col29) AS (VALUES ($1, $2::date,
$3::timestamptz, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16,
$17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27::timestamptz, $28,
$29::timestamptz)) MERGE INTO cpod.TAB1 AS target USING source_data AS
source ON target.ID = source.ID WHEN MATCHED THEN UPDATE SET ....) WHEN NOT
MATCHED THEN INSERT (...) VALUES (....);
Error:
ERROR: duplicate key value violates unique constraint "TAB1_pkey"
Detail: Key (ID, part_date)=(XXXXXXXXX, 2024-11-04) already exists.
Regards
Yudhi
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2024-11-11 05:40 Duplicate key error yudhi s <learnerdatabase99@gmail.com>
@ 2024-11-11 08:27 ` Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
2024-11-11 18:50 ` Re: Duplicate key error yudhi s <learnerdatabase99@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Achilleas Mantzios - cloud @ 2024-11-11 08:27 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 11/11/24 07:40, yudhi s wrote:
> We have a merge query as below for a partition table which is range
> partitioned on a truncated date column 'part_date'. And the only
> unique key in this table is a composite primary key on (id,
> part_date). And this merge queries ON condition is based on one of the
> columns i.e ID which is the leading column of the PK.So it means it
> will ensure no duplication happens for ID values, but still we are
> seeing "duplicate key" error. So what is the possible reason here or
> are we encountering any buggy behaviour here?
>
> WITH source_data (col1, col2, col3.....col29) AS (VALUES ($1,
> $2::date, $3::timestamptz, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,
> $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26,
> $27::timestamptz, $28, $29::timestamptz)) MERGE INTO cpod.TAB1 AS
> target USING source_data AS source ON target.ID = source.ID WHEN
> MATCHED THEN UPDATE SET ....) WHEN NOT MATCHED THEN INSERT (...)
> VALUES (....);
>
> Error:
> ERROR: duplicate key value violates unique constraint "TAB1_pkey"
> Detail: Key (ID, part_date)=(XXXXXXXXX, 2024-11-04) already exists.
You have to use the whole composite unique key (including part_date)
when matching against source_data .
If you had uniqueness on "ID" and then added "part_name" to the key,
you'd still had uniqueness.
BUT, reversely, if you have uniqueness on the pair (ID, part_date) there
is no guaranteed uniqueness on ID alone, hence your ERROR.
>
> Regards
> Yudhi
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2024-11-11 05:40 Duplicate key error yudhi s <learnerdatabase99@gmail.com>
2024-11-11 08:27 ` Re: Duplicate key error Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
@ 2024-11-11 18:50 ` yudhi s <learnerdatabase99@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: yudhi s @ 2024-11-11 18:50 UTC (permalink / raw)
To: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Nov 11, 2024 at 1:57 PM Achilleas Mantzios - cloud <
a.mantzios@cloud.gatewaynet.com> wrote:
>
> > WITH source_data (col1, col2, col3.....col29) AS (VALUES ($1,
> > $2::date, $3::timestamptz, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,
> > $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26,
> > $27::timestamptz, $28, $29::timestamptz)) MERGE INTO cpod.TAB1 AS
> > target USING source_data AS source ON target.ID = source.ID WHEN
> > MATCHED THEN UPDATE SET ....) WHEN NOT MATCHED THEN INSERT (...)
> > VALUES (....);
> >
> > Error:
> > ERROR: duplicate key value violates unique constraint "TAB1_pkey"
> > Detail: Key (ID, part_date)=(XXXXXXXXX, 2024-11-04) already exists.
>
> You have to use the whole composite unique key (including part_date)
> when matching against source_data .
>
> If you had uniqueness on "ID" and then added "part_name" to the key,
> you'd still had uniqueness.
>
> BUT, reversely, if you have uniqueness on the pair (ID, part_date) there
> is no guaranteed uniqueness on ID alone, hence your ERROR.
>
>
>
Actually the table is partitioned on column part_date which is why the
unique key is composite i.e. on ID and part_date. So in that case even we
merge on one of the column i.e. ID which is unique in itself, if any
duplicate value ID tries to be merged it should satisfy the MATCH criteria
and thus it should do a UPDATE on table TAB1 rather than going for NOMATCH
or INSERT. So in that case it should not error out with "duplicate key
violation". Is my understanding correct here?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2024-11-11 05:40 Duplicate key error yudhi s <learnerdatabase99@gmail.com>
@ 2024-11-11 20:04 ` Peter Geoghegan <pg@bowt.ie>
2024-11-12 20:38 ` Re: Duplicate key error yudhi s <learnerdatabase99@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Peter Geoghegan @ 2024-11-11 20:04 UTC (permalink / raw)
To: yudhi s <learnerdatabase99@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On Mon, Nov 11, 2024 at 12:41 AM yudhi s <learnerdatabase99@gmail.com> wrote:
> So it means it will ensure no duplication happens for ID values, but still we are seeing "duplicate key" error. So what is the possible reason here or are we encountering any buggy behaviour here?
MERGE doesn't actually make any promises about not getting unique
violations. Only ON CONFLICT DO UPDATE (and ON CONFLICT DO NOTHING)
make such a promise. That's the main reason why Postgres supports
both.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2024-11-11 05:40 Duplicate key error yudhi s <learnerdatabase99@gmail.com>
2024-11-11 20:04 ` Re: Duplicate key error Peter Geoghegan <pg@bowt.ie>
@ 2024-11-12 20:38 ` yudhi s <learnerdatabase99@gmail.com>
2024-11-12 20:43 ` Re: Duplicate key error Peter Geoghegan <pg@bowt.ie>
0 siblings, 1 reply; 22+ messages in thread
From: yudhi s @ 2024-11-12 20:38 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On Tue, Nov 12, 2024 at 1:35 AM Peter Geoghegan <pg@bowt.ie> wrote:
> On Mon, Nov 11, 2024 at 12:41 AM yudhi s <learnerdatabase99@gmail.com>
> wrote:
> > So it means it will ensure no duplication happens for ID values, but
> still we are seeing "duplicate key" error. So what is the possible reason
> here or are we encountering any buggy behaviour here?
>
> MERGE doesn't actually make any promises about not getting unique
> violations. Only ON CONFLICT DO UPDATE (and ON CONFLICT DO NOTHING)
> make such a promise. That's the main reason why Postgres supports
> both.
>
>
Okay. But here in this Merge statement it should first compare the ON
clause which is the value of ID column and if its exists in the
target table then its a MATCH which means it will do the UPDATE and if
its not available in the target table then its a NOT MATCH and it will do
the INSERT, so i am wondering at what exact situation it will throw
duplicate key error. Also the WITH clause will only pick one record at a
time and run the MERGE, so it will only merge one record at a time and then
commit. Can you share your thoughts on how exactly this merge query can
possibly cause the duplicate key error?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Duplicate key error
2024-11-11 05:40 Duplicate key error yudhi s <learnerdatabase99@gmail.com>
2024-11-11 20:04 ` Re: Duplicate key error Peter Geoghegan <pg@bowt.ie>
2024-11-12 20:38 ` Re: Duplicate key error yudhi s <learnerdatabase99@gmail.com>
@ 2024-11-12 20:43 ` Peter Geoghegan <pg@bowt.ie>
0 siblings, 0 replies; 22+ messages in thread
From: Peter Geoghegan @ 2024-11-12 20:43 UTC (permalink / raw)
To: yudhi s <learnerdatabase99@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On Tue, Nov 12, 2024 at 3:38 PM yudhi s <learnerdatabase99@gmail.com> wrote:
> Can you share your thoughts on how exactly this merge query can possibly cause the duplicate key error?
MERGE doesn't take any special precautions to avoid such unique
violations. ON CONFLICT does. It really is that simple.
Your app will be prone to the same sorts of errors (at READ COMMITTED
isolation level) if you do inserts conditioned on the absence of an
existing/conflict row. You'd have to retry the statement to plug the
race condition, which is how Postgres users did upserts prior to the
introduction of ON CONFLICT DO UPDATE.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2024-11-12 20:43 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-03 15:23 Duplicate key error Andrus <kobruleht2@hot.ee>
2021-03-03 15:42 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 16:08 ` Andrus <kobruleht2@hot.ee>
2021-03-03 16:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-03 18:35 ` Michael Lewis <mlewis@entrata.com>
2021-03-03 20:02 ` Andrus <kobruleht2@hot.ee>
2021-03-03 19:59 ` Andrus <kobruleht2@hot.ee>
2021-03-03 20:19 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 06:14 ` Andrus <kobruleht2@hot.ee>
2021-03-04 06:57 ` Ron <ronljohnsonjr@gmail.com>
2021-03-04 16:42 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-03-04 17:17 ` Andrus <kobruleht2@hot.ee>
2021-03-04 18:13 ` Michael Lewis <mlewis@entrata.com>
2021-03-04 18:23 ` Andrus <kobruleht2@hot.ee>
2021-03-04 19:23 ` Michael Lewis <mlewis@entrata.com>
2021-03-04 19:46 ` Andrus <kobruleht2@hot.ee>
2024-11-11 05:40 Duplicate key error yudhi s <learnerdatabase99@gmail.com>
2024-11-11 08:27 ` Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
2024-11-11 18:50 ` yudhi s <learnerdatabase99@gmail.com>
2024-11-11 20:04 ` Peter Geoghegan <pg@bowt.ie>
2024-11-12 20:38 ` yudhi s <learnerdatabase99@gmail.com>
2024-11-12 20:43 ` Peter Geoghegan <pg@bowt.ie>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox