The code shown in the Doc (I think) will still give you deadlock in the case where you have two sessions concurrently trying to insert the same 'hometown'. For example:
INSERT INTO users VALUES('Tom', select_hometown_id('Portland, OR'));
INSERT INTO users VALUES(''Waits', select_hometown_id('Portland, OR'));
The LOOP removes the race condition BUT with my limited knowledge I can only think of two ways to prevent the deadlock. Some kind of lock (not sure which) or force a separate transaction for the hometowns INSERT (also not sure how to do this in a FUNCTION). Is there an accepted approach here?
On Thu, Jan 15, 2015 at 11:41 PM, Jim Nasby <Jim.Nasby@xxxxxxxxxxxxxx> wrote:
On 1/15/15 10:57 PM, Roxanne Reid-Bennett wrote:
try this: (if you still get deadlocks, uncomment the advisory lock [thanks Daniel] and try again)
Logically I suppose it might run faster to do the select, then insert "if". I almost always write these as insert first - because it's the more restrictive lock.
CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR) RETURNS
INTEGER AS
$BODY$
DECLARE
v_id integer;
BEGIN
-- perform pg_advisory_xact_lock(hashtext(hometown_name));
BEGIN
insert into hometowns (name)
select hometown_name where not exists (select id from hometowns where name = hometown_name)
returning id into v_id;
That has a race condition. The only safe way to do this (outside of SSI) is using the example code at http://www.postgresql.org/docs/devel/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
