Foreign key and locking problem

Поиск
Список
Период
Сортировка
От Edoardo Serra
Тема Foreign key and locking problem
Дата
Msg-id EEAAE7DE-FFC0-41B6-A1D3-7E74D89E8967@serra.to.it
обсуждение исходный текст
Ответы Re: Foreign key and locking problem  (Craig Ringer <craig@postnewspapers.com.au>)
Re: Foreign key and locking problem  (Vick Khera <vivek@khera.org>)
Список pgsql-general
Hi guys,

I have a problem with the implicit "FOR SHARE" lock which postgres seems to get on the referenced record when there is
aforeign key. 
I'm using postgres 8.3.3 from debian packages.

Here is a sample database structure and commands to reproduce.

-- Test database structure

CREATE TABLE people (
    id serial NOT NULL,
    nickname character varying(255) NOT NULL,
    status integer NOT NULL
);

ALTER TABLE people ADD PRIMARY KEY (id);

CREATE TABLE friendships (
    id serial NOT NULL,
    person1_id integer NOT NULL,
    person2_id integer NOT NULL
);

ALTER TABLE friendships ADD PRIMARY KEY (id);

ALTER TABLE friendships ADD FOREIGN KEY (person1_id) REFERENCES people (id)
  ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE friendships ADD FOREIGN KEY (person2_id) REFERENCES people (id)
  ON UPDATE CASCADE ON DELETE CASCADE;

INSERT INTO people (id, nickname, status) VALUES (1, 'john.doe', 5);
INSERT INTO people (id, nickname, status) VALUES (2, 'jane.doe', 5);

-- now, in 2 different sessions I type:

client1> BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
client2> BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
client2> UPDATE people SET status = 6 WHERE id = 1;
client1> INSERT INTO friendships (id, person1_id, person2_id) VALUES (default, 1, 2);

client1 hangs trying to acquire the implicit FOR SHARE lock.

client2> COMMIT;

At this point, client1 gives the following error:
ERROR:  could not serialize access due to concurrent update
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."people" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

Is there a way to work around that?

In my architecture I have a background task which is computing friendships and a web frontend which is updating the
recordsin the people table. 
So updates to the people table can occurr while the background task is doing his job.

Any idea?

Tnx in advance

Regards

Edoardo

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

Предыдущее
От: John R Pierce
Дата:
Сообщение: Re: ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..
Следующее
От: Ray Stell
Дата:
Сообщение: schema access privs