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.na...@bluetreble.com>
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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
