Foreign key and locking problem

[Edoardo Serra <edoardo@xxxxxxxxxxx>]

Hi guys,

I have a problem with the implicit "FOR SHARE" lock which postgres seems to get on the referenced record when there is a foreign 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 records in 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
-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general