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
