Problem with inherited table, can you help?... - Mailing list pgsql-general

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