I have the following three tables and my inserts are blocking each other
in a way I just can't understand.... Can someone point me in the
direction as to what is causing this?


jobs=> \d master.locations
Table "master.locations"
Column | Type |
Modifiers
-------------+-----------------------------
+--------------------------------------------------------------------
location_id | integer | not null default
nextval('master.locations_location_id_seq'::text)
user_id | integer |
addr1 | character varying(50) |
addr2 | character varying(50) |
city | character varying(50) | not null
state_id | integer |
state_other | character varying(50) |
country_id | integer |
zip | character varying(35) | not null
loc_type | character varying(9) |
deleted | boolean | not null
entered_dt | timestamp without time zone | not null
updated_dt | timestamp without time zone |
Check constraints:
"locations_loc_type" CHECK (loc_type::text = 'primary'::text OR
loc_type::text = 'secondary'::text)



jobs=> \d jl_site1.locations
Table "jl_site1.locations"
Column | Type |
Modifiers
-------------+-----------------------------
+--------------------------------------------------------------------
location_id | integer | not null default
nextval('master.locations_location_id_seq'::text)
user_id | integer |
addr1 | character varying(50) |
addr2 | character varying(50) |
city | character varying(50) | not null
state_id | integer |
state_other | character varying(50) |
country_id | integer |
zip | character varying(35) | not null
loc_type | character varying(9) |
deleted | boolean | not null
entered_dt | timestamp without time zone | not null
updated_dt | timestamp without time zone |
Indexes:
"locations_pkey" primary key, btree (location_id)
"locations_location_id_key" unique, btree (location_id)
"locations_country_id_idx" btree (country_id)
"locations_state_id_idx" btree (state_id)
"locations_user_id_idx" btree (user_id)
"locations_zip_idx" btree (zip)
Check constraints:
"locations_loc_type" CHECK (loc_type::text = 'primary'::text OR
loc_type::text = 'secondary'::text)
Foreign-key constraints:
"$3" FOREIGN KEY (user_id) REFERENCES jl_site1.customer(id) ON UPDATE
CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (country_id) REFERENCES countries(country_id) ON
DELETE RESTRICT
"$1" FOREIGN KEY (state_id) REFERENCES states(state_id) ON DELETE RESTRICT
Inherits: locations


jobs=> \d jl_site2.locations
Table "jl_site2.locations"
Column | Type |
Modifiers
-------------+-----------------------------
+--------------------------------------------------------------------
location_id | integer | not null default
nextval('master.locations_location_id_seq'::text)
user_id | integer |
addr1 | character varying(50) |
addr2 | character varying(50) |
city | character varying(50) | not null
state_id | integer |
state_other | character varying(50) |
country_id | integer |
zip | character varying(35) | not null
loc_type | character varying(9) |
deleted | boolean | not null
entered_dt | timestamp without time zone | not null
updated_dt | timestamp without time zone |
Indexes:
"locations_pkey" primary key, btree (location_id)
"locations_location_id_key" unique, btree (location_id)
"locations_country_id_idx" btree (country_id)
"locations_state_id_idx" btree (state_id)
"locations_user_id_idx" btree (user_id)
"locations_zip_idx" btree (zip)
Check constraints:
"locations_loc_type" CHECK (loc_type::text = 'primary'::text OR
loc_type::text = 'secondary'::text)
Foreign-key constraints:
"$3" FOREIGN KEY (user_id) REFERENCES jl_site2.customer(id) ON UPDATE
CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (country_id) REFERENCES countries(country_id) ON
DELETE RESTRICT
"$1" FOREIGN KEY (state_id) REFERENCES states(state_id) ON DELETE RESTRICT
Inherits: locations


(NOTE: at this point, hopefull it is clear that both jl_site1 and
jl_site2 inherit the master.locations table)


In connection #1, I do:

1. set search_path=jl_site1,public;
2. BEGIN;
3. INSERT INTO locations (user_id, city, state_id, zip, country_id,
loc_type, deleted, entered_dt) VALUES (17181, 'Lansing', 23, '48901', 1,
'secondary', 'f', now());

I can continue to insert records without any issue. Now without
committing or rolling back this transaction, I open another connection and do:

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

I am *fairly* certain that it is due to the "country_id" column, because
if in the second connection if I remove it or change it to a value other
than 1 it seems to work without a hitch, which would seem to indicate it
is attempting to get a row level lock on the countries table where id=1,
but I just can't figure out why it would need to do that.. 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. But the thought of
this long running process which runs for potentially hours basically
locking out other inserts because of a lock on this table just doesn't
seem worth it to me.. ;-(

I could understand it if I was trying to do an insert into site1, but I
cannot make any sense as to why this is (or should) be blocking on me...



Thanks as always!

- Greg


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

On Fri, 11 Mar 2005 03:39 am, Michael Fuhr wrote:
> On Thu, Mar 10, 2005 at 01:31:21AM -0800, Net Virtual Mailing Lists wrote:

[snip]

>
> Some have suggested that PostgreSQL should use a weaker lock on the
> referenced key, but that hasn't been implemented yet.
>

Are there actually any problems with only getting a AccessShareLock?

Regards

Russell Smith

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Fri, Mar 11, 2005 at 06:16:28PM +1100, Russell Smith wrote:
> On Fri, 11 Mar 2005 03:39 am, Michael Fuhr wrote:
> > On Thu, Mar 10, 2005 at 01:31:21AM -0800, Net Virtual Mailing Lists wrote:
>
> > Some have suggested that PostgreSQL should use a weaker lock on the
> > referenced key, but that hasn't been implemented yet.
>
> Are there actually any problems with only getting a AccessShareLock?

Yes, because there is no infrastructure to get any type of lock save
an exclusive lock (not sure to which lmgr lock type is equivalent) on a
per-row basis.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Ni aun el genio muy grande llegaría muy lejos
si tuviera que sacarlo todo de su propio interior" (Goethe)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly