From: | Edwin Grubbs <edwin(dot)grubbs(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | referential integrity preventing simultaneous insert |
Date: | 2004-09-09 17:53:57 |
Message-ID: | d6b3d4ab04090910533700a02a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have experienced problems with postgres hanging when two inserts
reference the same foreign key. It appears that the second insert is
waiting for the first insert to release a lock.
Here is an example of how to recreate the problem. Please ignore the
lack of sequences, since that is irrelevent to the problem. I have
tested this under pg 7.4.3.
----------------------------------------------------------
-- Set up test database:
CREATE TABLE model (
id INTEGER PRIMARY KEY,
name varchar(20)
);
CREATE TABLE car (
id INTEGER PRIMARY KEY,
model_id INTEGER REFERENCES model
);
INSERT INTO model VALUES (1, 'one');
----------------------------------------------------------
-- First connection opens a transaction and adds a reference to model id=1.
BEGIN; INSERT INTO car VALUES (5, 1);
-- Do not commit this transaction.
----------------------------------------------------------
-- Open a separate connection.
-- Second connection does the same and will hang until the
-- first transaction ends.
BEGIN; INSERT INTO car VALUES (6, 1);
----------------------------------------------------------
Is this fixed in postgres 8? If it still requires work, I may be able
to help fund it to get it completed sooner.
Thanks,
Edwin Grubbs
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Bartunov | 2004-09-09 17:56:59 | Re: Text Search vs MYSQL vs Lucene |
Previous Message | the inquirer | 2004-09-09 17:49:26 | PL/pgSQL Function Problem |