Message-ID: <42FBBF1E.6040403@arbash-meinel.com>
Date: Thu, 11 Aug 2005 16:11:58 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Macintosh/20050716)
MIME-Version: 1.0
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Odd Locking Problem
References: <42FBB6CF.6000404@arbash-meinel.com>
	<20050811210842.GA28253@alvh.no-ip.org>
In-Reply-To: <20050811210842.GA28253@alvh.no-ip.org>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig85C8C97A4CA324ACA27CB4AC"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig85C8C97A4CA324ACA27CB4AC
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Alvaro Herrera wrote:
> On Thu, Aug 11, 2005 at 03:36:31PM -0500, John A Meinel wrote:
>
>>I'm having an odd case where my system is locking such that if I insert
>>into a table during a transaction, if I start a new connection and
>>transaction, it blocks while trying to do a similar insert until the
>>first transaction is committed or rolled back.
>
>
> Are there foreign keys here?  I can duplicate the problem easily with
> them:
>
> -- session 1
> create table a (a serial primary key);
> create table b (a int references a);
> insert into a values (1);
>
> begin;
> insert into b values (1);
>
>
> -- session 2
> insert into b values (1);
> -- hangs
>

Actually, there are but the insert is occurring into table 'a' not table
'b'.
'a' refers to other tables, but these should not be modified.

>
> If I commit on session 1, session 2 is unlocked.
>
> This is a known problem, solved in 8.1.  A workaround for previous
> releases is to defer FK checks until commit:
>
> create table b (a int references a initially deferred);

I'll try one of the CVS entries and see if it happens there. Good to
hear there has been work done.

John
=:->

>


--------------enig85C8C97A4CA324ACA27CB4AC
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC+78eJdeBCYSNAAMRAi8AAJ9N+E4aJXZvG+SadflT5Mml4DpX1ACcC4j1
FLEvQdCoRCup3WTHkgg+dNk=
=dHX/
-----END PGP SIGNATURE-----

--------------enig85C8C97A4CA324ACA27CB4AC--