delete commands fails silently to delete primary key

Поиск
Список
Период
Сортировка
От Andrus
Тема delete commands fails silently to delete primary key
Дата
Msg-id gg9pg9$1edv$3@news.hub.org
обсуждение исходный текст
Ответы Re: delete commands fails silently to delete primary key  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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.


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: SQL query
Следующее
От: "Ciprian Dorin Craciun"
Дата:
Сообщение: Re: Using Postgres to store high volume streams of sensor readings