Re: Simple Atomic Relationship Insert

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(dot)Nasby(at)bluetreble(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>