Postgres Pro
Downloads
Home   >   mailing lists

Thread: delete commands fails silently to delete primary key

delete commands fails silently to delete primary key

From
"Andrus"
Date:
I have table in 8.1.4 which tracks users logged into db

CREATE TABLE "session"
(
  workplace character(16) NOT NULL,
  ipaddress character(20),
  logintime character(28),
  loggeduser character(10),
  CONSTRAINT session_pkey PRIMARY KEY (workplace)
);

Commands executed at logon in same transaction are:

delete from session where workplace=E'LIIVA' ;
insert into session (workplace,ipaddress,logintime,loggeduser) values (
E'LIIVA' , inet_client_addr()::CHAR(14),
current_timestamp::CHAR(28),CURRENT_USER)

Sometimes (during locking contention or during heavy load) those commands
cause error:

2008-11-22 11:24:26 EET INSERT 1 47433335ERROR:  duplicate key violates
unique constraint "session_pkey"
2008-11-22 11:24:26 EET INSERT 2 47433335STATEMENT:  delete from session
where workplace=E'LIIVA' ;insert into session
(workplace,ipaddress,logintime,loggeduser) values (  E'LIIVA' ,
inet_client_addr()::CHAR(14), current_timestamp::CHAR(28),CURRENT_USER)

No other client can add 'LIIVA' primary key.
Any idea why this error occurs and how to fix ?

Andrus.

Re: delete commands fails silently to delete primary key

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
> I have table in 8.1.4 which tracks users logged into db

There have been a number of index-corruption bugs fixed since 8.1.4 ...

In particular, if it's possible that any of these clients abort before
committing these insertions, the vacuum race condition bug fixed in
8.1.10 is a pretty likely candidate for your problem.

            regards, tom lane

Re: delete commands fails silently to delete primary key

From
"Andrus"
Date:
> There have been a number of index-corruption bugs fixed since 8.1.4 ...
>
> In particular, if it's possible that any of these clients abort before
> committing these insertions, the vacuum race condition bug fixed in
> 8.1.10 is a pretty likely candidate for your problem.

I changed second statement to

INSERT INTO session ('MYCOMPNAME',ipaddress,logintime,loggeduser)
   SELECT 'MYCOMPNAME',
inet_client_addr()::CHAR(14),current_timestamp::CHAR(28),CURRENT_USER
         WHERE NOT EXISTS (SELECT 1 FROM session WHERE
workplace='MYCOMPNAME')

where MYCOMPNAME is logging-in computer name.

Will this fix the isse or is it better to wait 100 ms  and re-try insert?

Andrus.

Re: delete commands fails silently to delete primary key

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
>> There have been a number of index-corruption bugs fixed since 8.1.4 ...
>>
>> In particular, if it's possible that any of these clients abort before
>> committing these insertions, the vacuum race condition bug fixed in
>> 8.1.10 is a pretty likely candidate for your problem.

> I changed second statement to ...
> Will this fix the isse

No.  Look, as you've been told several times already you are running a
very old version with a lot of known bugs.  Just update to the latest
in that branch.  It's not hard.

            regards, tom lane