On Thu, Mar 10, 2005 at 01:31:21AM -0800, Net Virtual Mailing Lists wrote:
> 1. set search_path=jl_site2,public;
> 2. BEGIN;
> 3. INSERT INTO locations (user_id, city, state_id, zip, country_id,
> loc_type, deleted, entered_dt) VALUES (37613, 'Glendale', 3, '85301', 1,
> 'secondary', 'f', now());
>
> .. at this point connection #2 is blocked until I either commit or
> rollback the in-process transaction in connection
As you guessed, connection #2 is blocked because of a concurrent
transaction inserting another record with the same country_id foreign
key. PostgreSQL acquires a row-level lock on the referenced key
to ensure that it doesn't change while the referencing transaction
remains open. Unfortunately it's an exclusive lock, which causes
other transactions to block when they try to lock the same row.
> More importantly what I can do about this. The countries/states table are
> basically static and won't change, but I want the constraint check in
> place because it just seems like a good practice.
Constraints are indeed good practice. This has come up before, and
one possibility is to make the foreign key constraint deferrable
and defer its integrity checks so they aren't made until commit
time. This has problems of its own, however: you won't detect
referential integrity violations until the transaction commits, so
you won't get an error for the specific statement that caused the
violation.
Some have suggested that PostgreSQL should use a weaker lock on the
referenced key, but that hasn't been implemented yet.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/