Re: [GENERAL] Problem with inherited table, can you help?...
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 ([EMAIL PROTECTED]) 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Problem with inherited table, can you help?...
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
Re: [GENERAL] Problem with inherited table, can you help?...
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
Re: [GENERAL] Problem with inherited table, can you help?...
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
