Problem with inherited table, can you help?...

From: "Net Virtual Mailing Lists" <mailinglists(at)net-virtual(dot)com>
To: "Pgsql General" <pgsql-general(at)postgresql(dot)org>
Subject: Problem with inherited table, can you help?...
Date: 2005-03-10 09:31:21
Message-ID: 20050310093121.18947@mail.net-virtual.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Travers 2005-03-10 09:38:20 Re: pl sql to check if table of table_name exists
Previous Message Joe Conway 2005-03-10 09:13:00 Re: partitionning