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/ ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
