Thread: transaction blocking inserts in postgresql 7.3
I'm trying to find a work-around for blocked inserts in transactions in
postgres 7.3.
It appears that inserts into tables which reference other tables block
inserts until a transaction is committed.
Is there any solution other than abandoning referential integrity?
Any suggestions gratefully received. An example of the problem is listed
below.
Regards,
Chris
I've tested the following schema:
----------------
create table Organisations (
OrgID SERIAL NOT NULL PRIMARY KEY,
Name TEXT NOT NULL
);
create table Trials (
TrialID SERIAL NOT NULL PRIMARY KEY,
OrgID INTEGER NOT NULL REFERENCES Organisations,
Title TEXT NOT NULL
);
insert into organisations (name) values ('org1');
insert into organisations (name) values ('org2');
---------------
in one psql instance running:
-------
begin;
insert into trials(orgid,title) values(1,'test1');
-------
in a second psql instance running;
-------
insert into trials(orgid,title) values(1,'test2');
-------
The second insert blocks until a commit in the first instance, even though
the inserts only require row-level and share locks. This blocking occurs
regardless of whether the second instance runs in a transaction or not.
Here's output from pg_locks. PID 3605 is running the transaction, 3603 is
the blocked insert:
----------
tester# select pgc.relname,pg_locks.* from pg_class pgc,pg_locks where
pgc.relfilenode=pg_locks.relation order by pid,relname;
organisations | 20810985 | 20810982 | | 3603 |
AccessShareLock | t
organisations | 20810985 | 20810982 | | 3603 |
RowShareLock | t
trials | 20810996 | 20810982 | | 3603 |
AccessShareLock | t
trials | 20810996 | 20810982 | | 3603 |
RowExclusiveLock | t
trials_trialid_seq | 20810994 | 20810982 | | 3603 |
AccessShareLock | t
organisations | 20810985 | 20810982 | | 3605 |
AccessShareLock | t
organisations | 20810985 | 20810982 | | 3605 |
RowShareLock | t
organisations_pkey | 20810991 | 20810982 | | 3605 |
AccessShareLock | t
trials | 20810996 | 20810982 | | 3605 |
AccessShareLock | t
trials | 20810996 | 20810982 | | 3605 |
RowExclusiveLock | t
trials_trialid_seq | 20810994 | 20810982 | | 3605 |
AccessShareLock | t
pg_class | 1259 | 20810982 | | 3607 |
AccessShareLock | t
pg_locks | 16757 | 20810982 | | 3607 |
AccessShareLock | t
----------
I'm really curious how you got these two transactions to occur
simultaneously, i.e. how does one DO the test that you DID?
Chris Hutchinson wrote:
>
> I'm trying to find a work-around for blocked inserts in transactions in
> postgres 7.3.
> It appears that inserts into tables which reference other tables block
> inserts until a transaction is committed.
> Is there any solution other than abandoning referential integrity?
>
> Any suggestions gratefully received. An example of the problem is listed
> below.
>
> Regards,
> Chris
>
> I've tested the following schema:
> ----------------
> create table Organisations (
> OrgID SERIAL NOT NULL PRIMARY KEY,
> Name TEXT NOT NULL
> );
>
> create table Trials (
> TrialID SERIAL NOT NULL PRIMARY KEY,
> OrgID INTEGER NOT NULL REFERENCES Organisations,
> Title TEXT NOT NULL
> );
>
> insert into organisations (name) values ('org1');
> insert into organisations (name) values ('org2');
>
> ---------------
>
> in one psql instance running:
> -------
> begin;
> insert into trials(orgid,title) values(1,'test1');
> -------
>
> in a second psql instance running;
> -------
> insert into trials(orgid,title) values(1,'test2');
> -------
>
> The second insert blocks until a commit in the first instance, even though
> the inserts only require row-level and share locks. This blocking occurs
> regardless of whether the second instance runs in a transaction or not.
>
> Here's output from pg_locks. PID 3605 is running the transaction, 3603 is
> the blocked insert:
> ----------
> tester# select pgc.relname,pg_locks.* from pg_class pgc,pg_locks where
> pgc.relfilenode=pg_locks.relation order by pid,relname;
>
> organisations | 20810985 | 20810982 | | 3603 |
> AccessShareLock | t
> organisations | 20810985 | 20810982 | | 3603 |
> RowShareLock | t
> trials | 20810996 | 20810982 | | 3603 |
> AccessShareLock | t
> trials | 20810996 | 20810982 | | 3603 |
> RowExclusiveLock | t
> trials_trialid_seq | 20810994 | 20810982 | | 3603 |
> AccessShareLock | t
> organisations | 20810985 | 20810982 | | 3605 |
> AccessShareLock | t
> organisations | 20810985 | 20810982 | | 3605 |
> RowShareLock | t
> organisations_pkey | 20810991 | 20810982 | | 3605 |
> AccessShareLock | t
> trials | 20810996 | 20810982 | | 3605 |
> AccessShareLock | t
> trials | 20810996 | 20810982 | | 3605 |
> RowExclusiveLock | t
> trials_trialid_seq | 20810994 | 20810982 | | 3605 |
> AccessShareLock | t
> pg_class | 1259 | 20810982 | | 3607 |
> AccessShareLock | t
> pg_locks | 16757 | 20810982 | | 3607 |
> AccessShareLock | t
> ----------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Dennis, > I'm really curious how you got these two transactions to occur > simultaneously, i.e. how does one DO the test that you DID? Open two terminal sessions, run a copy of psql in both. In one run the 'begin; insert...', in the other run 'insert'. Thesecond is blocked until a commit (or rollback) is entered in the first. Regards, Chris
Hi Chris,
I have the same problem with foreign keys. Short answer: AFAIK, without
changing Postgres code, there's no valid workaround but dropping foreign
keys or fixing your code to avoid/handle the resulting
contention/deadlocks.
Our application was originally developed using Oracle, where this
problem is not existing (foreign key checks place less restrictive
locks).
The only quick solution I found is a kludge: I've changed the source
code of Postgres so it doesn't place locks when checking foreign keys...
this (mostly) works with our code, but it doesn't fully warrant you
anymore the referential integrity of the data (there are a few
situations when parallel transactions can leave child records without
parent record, e.g. when one transaction updates a child record while
the other transaction deletes the referenced row). In our code these
situations are very unlikely, so it is acceptable risk.
In the long run, there is a development effort to make Postgres handle
the foreign key checks with better locking, but I don't know how far it
got, and I don't have the time to participate myself.
HTH,
Csaba
On Wed, 2003-03-26 at 06:32, Chris Hutchinson wrote:
> I'm trying to find a work-around for blocked inserts in transactions in
> postgres 7.3.
> It appears that inserts into tables which reference other tables block
> inserts until a transaction is committed.
> Is there any solution other than abandoning referential integrity?
>
> Any suggestions gratefully received. An example of the problem is listed
> below.
>
> Regards,
> Chris
>
>
> I've tested the following schema:
> ----------------
> create table Organisations (
> OrgID SERIAL NOT NULL PRIMARY KEY,
> Name TEXT NOT NULL
> );
>
> create table Trials (
> TrialID SERIAL NOT NULL PRIMARY KEY,
> OrgID INTEGER NOT NULL REFERENCES Organisations,
> Title TEXT NOT NULL
> );
>
> insert into organisations (name) values ('org1');
> insert into organisations (name) values ('org2');
>
> ---------------
>
> in one psql instance running:
> -------
> begin;
> insert into trials(orgid,title) values(1,'test1');
> -------
>
> in a second psql instance running;
> -------
> insert into trials(orgid,title) values(1,'test2');
> -------
>
> The second insert blocks until a commit in the first instance, even though
> the inserts only require row-level and share locks. This blocking occurs
> regardless of whether the second instance runs in a transaction or not.
>
> Here's output from pg_locks. PID 3605 is running the transaction, 3603 is
> the blocked insert:
> ----------
> tester# select pgc.relname,pg_locks.* from pg_class pgc,pg_locks where
> pgc.relfilenode=pg_locks.relation order by pid,relname;
>
> organisations | 20810985 | 20810982 | | 3603 |
> AccessShareLock | t
> organisations | 20810985 | 20810982 | | 3603 |
> RowShareLock | t
> trials | 20810996 | 20810982 | | 3603 |
> AccessShareLock | t
> trials | 20810996 | 20810982 | | 3603 |
> RowExclusiveLock | t
> trials_trialid_seq | 20810994 | 20810982 | | 3603 |
> AccessShareLock | t
> organisations | 20810985 | 20810982 | | 3605 |
> AccessShareLock | t
> organisations | 20810985 | 20810982 | | 3605 |
> RowShareLock | t
> organisations_pkey | 20810991 | 20810982 | | 3605 |
> AccessShareLock | t
> trials | 20810996 | 20810982 | | 3605 |
> AccessShareLock | t
> trials | 20810996 | 20810982 | | 3605 |
> RowExclusiveLock | t
> trials_trialid_seq | 20810994 | 20810982 | | 3605 |
> AccessShareLock | t
> pg_class | 1259 | 20810982 | | 3607 |
> AccessShareLock | t
> pg_locks | 16757 | 20810982 | | 3607 |
> AccessShareLock | t
> ----------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Is it blocking because orgid is a primary key?
Does it still block if you use a different orgid in each of the two
transactions?
Regards,
Link.
At 04:32 PM 3/26/03 +1100, Chris Hutchinson wrote:
>I'm trying to find a work-around for blocked inserts in transactions in
>postgres 7.3.
>It appears that inserts into tables which reference other tables block
>inserts until a transaction is committed.
>Is there any solution other than abandoning referential integrity?
>
>Any suggestions gratefully received. An example of the problem is listed
>below.
>
>Regards,
>Chris
>
>
>I've tested the following schema:
>----------------
>create table Organisations (
> OrgID SERIAL NOT NULL PRIMARY KEY,
> Name TEXT NOT NULL
>);
>
>create table Trials (
> TrialID SERIAL NOT NULL PRIMARY KEY,
> OrgID INTEGER NOT NULL REFERENCES Organisations,
> Title TEXT NOT NULL
>);
>
>insert into organisations (name) values ('org1');
>insert into organisations (name) values ('org2');
>
>---------------
>
>in one psql instance running:
>-------
>begin;
>insert into trials(orgid,title) values(1,'test1');
>-------
>
>in a second psql instance running;
>-------
>insert into trials(orgid,title) values(1,'test2');
>-------
>
>The second insert blocks until a commit in the first instance, even though
>the inserts only require row-level and share locks. This blocking occurs
>regardless of whether the second instance runs in a transaction or not.
>
>Here's output from pg_locks. PID 3605 is running the transaction, 3603 is
>the blocked insert:
>----------
>tester# select pgc.relname,pg_locks.* from pg_class pgc,pg_locks where
>pgc.relfilenode=pg_locks.relation order by pid,relname;
> Is it blocking because orgid is a primary key? > Does it still block if you use a different orgid in each of the two > transactions? It doesn't block if the orgid is different. Unfortunately that doesn't solve the problem for the actual multiuser application. Users are being blocked from adding records or making changes in one table, while another process has a (long) transaction importing records into another table. The two tables are related only by a common foreign key against a third table. I'm curious why postgres should be blocking like this, as the foreign key constraint is presumably read-based. Regards, Chris
On Wednesday 26 Mar 2003 9:59 am, Chris Hutchinson wrote: > I'm curious why postgres should be blocking like this, as the foreign key > constraint is presumably read-based. Actually, I believe it's a write lock (which is the cause of the problem). What it actually needs to be is a "prevent writes" lock but there is no such thing at the moment. -- Richard Huxton
> > I'm curious why postgres should be blocking like this, as the foreign key > constraint is presumably read-based. No, the foreign key check places an exclusive lock on the parent table's referenced row. There's no row level shared lock implemented. This is the problem. > > Regards, > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
The light now glows above my head. OTOH, i thought that with a MVCC transaction model, the scenario you describe should not be possible. Chris Hutchinson wrote: > Dennis, > > >>I'm really curious how you got these two transactions to occur >>simultaneously, i.e. how does one DO the test that you DID? > > > Open two terminal sessions, run a copy of psql in both. In one run the 'begin; insert...', in the other run 'insert'. Thesecond is blocked until a commit (or rollback) is entered in the first. > > Regards, > Chris > > >
Actually,
I need a clarification of this since it might affect my design. When you mean
blocked, is an error thrown, or does it just wait its turn?
Chris Hutchinson wrote:
> Dennis,
>
>
>>I'm really curious how you got these two transactions to occur
>>simultaneously, i.e. how does one DO the test that you DID?
>
>
> Open two terminal sessions, run a copy of psql in both. In one run the 'begin; insert...', in the other run 'insert'.
Thesecond is blocked until a commit (or rollback) is entered in the first.
>
> Regards,
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Dennis,
I develop web-based genetic data management systems for agricultural
research. One facility of the system is importing large CSV-format data
files into the database through a web interface.
On a postgresql backend, while an import is running (which can take several
minutes due to the quantity of data imported) users cannot make changes to
other tables which share a common foreign key with the species table. This
locks users out of numerous administrative operations, and is something of a
pain.
One example of the problem occurs when users open a form to add a new trait
definition for a experimental study, and the form save never returns. Their
browser times out because the form won't return until the background import
job is complete. Essentially users see the system freeze, with no clue as to
why.
Possible workarounds I'm investigating are to split imports into smaller
transactions to give other tasks a chance to run, but I forsee issues when
multiple simultaneous imports are running.
So in brief answer to your question, database operations wait their turn,
but with a long transaction in a web environment this can cause browser
timeouts and a problematic user experience.
Regards,
Chris
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dennis Gearon
Sent: Thursday, 27 March 2003 6:51 AM
To: Chris Hutchinson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] transaction blocking inserts in postgresql 7.3
Actually,
I need a clarification of this since it might affect my design. When you
mean
blocked, is an error thrown, or does it just wait its turn?
Chris Hutchinson wrote:
> Dennis,
>
>
>>I'm really curious how you got these two transactions to occur
>>simultaneously, i.e. how does one DO the test that you DID?
>
>
> Open two terminal sessions, run a copy of psql in both. In one run the
'begin; insert...', in the other run 'insert'. The second is blocked until a
commit (or rollback) is entered in the first.
>
> Regards,
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Thanks for the input. I can see that you are going to have to have your web script split up the data. How about a buffer table that a chron job, every minute or so, pumps a number of records out of equal to 5% of what's in the buffer or some maximum value determined by experiment? It loads the CSV stuff lickety quick, doesn't lock up your other tasks, (for more than 5 seconds if it were my design). Chris Hutchinson wrote: > Dennis, > > I develop web-based genetic data management systems for agricultural > research. One facility of the system is importing large CSV-format data > files into the database through a web interface. > > On a postgresql backend, while an import is running (which can take several > minutes due to the quantity of data imported) users cannot make changes to > other tables which share a common foreign key with the species table. This > locks users out of numerous administrative operations, and is something of a > pain. > > One example of the problem occurs when users open a form to add a new trait > definition for a experimental study, and the form save never returns. Their > browser times out because the form won't return until the background import > job is complete. Essentially users see the system freeze, with no clue as to > why. > > Possible workarounds I'm investigating are to split imports into smaller > transactions to give other tasks a chance to run, but I forsee issues when > multiple simultaneous imports are running. > > So in brief answer to your question, database operations wait their turn, > but with a long transaction in a web environment this can cause browser > timeouts and a problematic user experience. > > Regards, > Chris > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dennis Gearon > Sent: Thursday, 27 March 2003 6:51 AM > To: Chris Hutchinson > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] transaction blocking inserts in postgresql 7.3 > > > Actually, > I need a clarification of this since it might affect my design. When you > mean > blocked, is an error thrown, or does it just wait its turn? > > Chris Hutchinson wrote: > >>Dennis, >> >> >> >>>I'm really curious how you got these two transactions to occur >>>simultaneously, i.e. how does one DO the test that you DID? >> >> >>Open two terminal sessions, run a copy of psql in both. In one run the > > 'begin; insert...', in the other run 'insert'. The second is blocked until a > commit (or rollback) is entered in the first. > >>Regards, >>Chris >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
Chris, Perhaps it'd be faster for the users to upload their data to a dynamically created temporary table (keeping the main tableavailable). The web application should choose/create one. Once that upload is done, copy to the main table. I think this would be faster since the copy is within the database. Any ideas? -------------------------- David Olbersen iGuard Engineer 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -----Original Message----- > From: Chris Hutchinson [mailto:chris@hutchinsonsoftware.com] > Sent: Wednesday, March 26, 2003 2:35 PM > To: gearond@cvc.net > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] transaction blocking inserts in postgresql 7.3 > > > Dennis, > > I develop web-based genetic data management systems for agricultural > research. One facility of the system is importing large > CSV-format data > files into the database through a web interface. > > On a postgresql backend, while an import is running (which > can take several > minutes due to the quantity of data imported) users cannot > make changes to > other tables which share a common foreign key with the > species table. This > locks users out of numerous administrative operations, and is > something of a > pain. > > One example of the problem occurs when users open a form to > add a new trait > definition for a experimental study, and the form save never > returns. Their > browser times out because the form won't return until the > background import > job is complete. Essentially users see the system freeze, > with no clue as to > why. > > Possible workarounds I'm investigating are to split imports > into smaller > transactions to give other tasks a chance to run, but I > forsee issues when > multiple simultaneous imports are running. > > So in brief answer to your question, database operations wait > their turn, > but with a long transaction in a web environment this can > cause browser > timeouts and a problematic user experience. > > Regards, > Chris > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dennis Gearon > Sent: Thursday, 27 March 2003 6:51 AM > To: Chris Hutchinson > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] transaction blocking inserts in postgresql 7.3 > > > Actually, > I need a clarification of this since it might affect my > design. When you > mean > blocked, is an error thrown, or does it just wait its turn? > > Chris Hutchinson wrote: > > Dennis, > > > > > >>I'm really curious how you got these two transactions to occur > >>simultaneously, i.e. how does one DO the test that you DID? > > > > > > Open two terminal sessions, run a copy of psql in both. In > one run the > 'begin; insert...', in the other run 'insert'. The second is > blocked until a > commit (or rollback) is entered in the first. > > > > Regards, > > Chris > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org >
Please note that if you have 2 parallel imports which insert records locking referenced rows in reverse order, deadlocks can occur. So you have to make sure that the imported records are sorted by referenced foreign keys. Cheers, Csaba. On Wed, 2003-03-26 at 23:46, Dennis Gearon wrote: > Thanks for the input. I can see that you are going to have to have your web > script split up the data. > > How about a buffer table that a chron job, every minute or so, pumps a number of > records out of equal to 5% of what's in the buffer or some maximum value > determined by experiment? > > It loads the CSV stuff lickety quick, doesn't lock up your other tasks, (for > more than 5 seconds if it were my design). > > Chris Hutchinson wrote: > > Dennis, > > > > I develop web-based genetic data management systems for agricultural > > research. One facility of the system is importing large CSV-format data > > files into the database through a web interface. > > > > On a postgresql backend, while an import is running (which can take several > > minutes due to the quantity of data imported) users cannot make changes to > > other tables which share a common foreign key with the species table. This > > locks users out of numerous administrative operations, and is something of a > > pain. > > > > One example of the problem occurs when users open a form to add a new trait > > definition for a experimental study, and the form save never returns. Their > > browser times out because the form won't return until the background import > > job is complete. Essentially users see the system freeze, with no clue as to > > why. > > > > Possible workarounds I'm investigating are to split imports into smaller > > transactions to give other tasks a chance to run, but I forsee issues when > > multiple simultaneous imports are running. > > > > So in brief answer to your question, database operations wait their turn, > > but with a long transaction in a web environment this can cause browser > > timeouts and a problematic user experience. > > > > Regards, > > Chris > > > > > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org > > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dennis Gearon > > Sent: Thursday, 27 March 2003 6:51 AM > > To: Chris Hutchinson > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] transaction blocking inserts in postgresql 7.3 > > > > > > Actually, > > I need a clarification of this since it might affect my design. When you > > mean > > blocked, is an error thrown, or does it just wait its turn? > > > > Chris Hutchinson wrote: > > > >>Dennis, > >> > >> > >> > >>>I'm really curious how you got these two transactions to occur > >>>simultaneously, i.e. how does one DO the test that you DID? > >> > >> > >>Open two terminal sessions, run a copy of psql in both. In one run the > > > > 'begin; insert...', in the other run 'insert'. The second is blocked until a > > commit (or rollback) is entered in the first. > > > >>Regards, > >>Chris > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >> > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
You could try converting your data into SQL insert statements (shouldn't be too hard to write a script that does that), and load that. Your import will take quite a bit longer because of the transaction overhead required for each new record, but would solve your blocking problem. --- Chris Hutchinson <chris@hutchinsonsoftware.com> wrote: > Dennis, > > I develop web-based genetic data management systems > for agricultural > research. One facility of the system is importing > large CSV-format data > files into the database through a web interface. > > On a postgresql backend, while an import is running > (which can take several > minutes due to the quantity of data imported) users > cannot make changes to > other tables which share a common foreign key with > the species table. This > locks users out of numerous administrative > operations, and is something of a > pain. > > One example of the problem occurs when users open a > form to add a new trait > definition for a experimental study, and the form > save never returns. Their > browser times out because the form won't return > until the background import > job is complete. Essentially users see the system > freeze, with no clue as to > why. > > Possible workarounds I'm investigating are to split > imports into smaller > transactions to give other tasks a chance to run, > but I forsee issues when > multiple simultaneous imports are running. > > So in brief answer to your question, database > operations wait their turn, > but with a long transaction in a web environment > this can cause browser > timeouts and a problematic user experience. > > Regards, > Chris > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf > Of Dennis Gearon > Sent: Thursday, 27 March 2003 6:51 AM > To: Chris Hutchinson > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] transaction blocking inserts > in postgresql 7.3 > > > Actually, > I need a clarification of this since it might > affect my design. When you > mean > blocked, is an error thrown, or does it just wait > its turn? > > Chris Hutchinson wrote: > > Dennis, > > > > > >>I'm really curious how you got these two > transactions to occur > >>simultaneously, i.e. how does one DO the test that > you DID? > > > > > > Open two terminal sessions, run a copy of psql in > both. In one run the > 'begin; insert...', in the other run 'insert'. The > second is blocked until a > commit (or rollback) is entered in the first. > > > > Regards, > > Chris > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org __________________________________________________ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com
Thanks to everyone for their suggestions on dealing with inserts blocked by index locking. My eventual solution was to remove the REFERENCES constraints on the affected tables and move referential integrity into the application - not ideal, but faster to implement than the alternatives. Suggested solutions were: * Load file into temporary table, and insert into primary table from temporary (Dennis Gearon, David Olberson) * Convert data to insert statements and (I think) run each in a separate transaction (Jeff Eckerman)
Let us know in about a month what your database references look like :-) without table locks, which cause the same problem that you're trying to fix, I don't think that it's possible to maintain referential integrity with multiple processes going .... in application code. I've been proved wrong on many things, I hope for your sake that this is one of them :-) Chris Hutchinson wrote: > Thanks to everyone for their suggestions on dealing with inserts blocked by > index locking. > > My eventual solution was to remove the REFERENCES constraints on the > affected tables and move referential integrity into the application - not > ideal, but faster to implement than the alternatives. > > Suggested solutions were: > * Load file into temporary table, and insert into primary table from > temporary (Dennis Gearon, David Olberson) > * Convert data to insert statements and (I think) run each in a separate > transaction (Jeff Eckerman) > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >