Re: Simple Atomic Relationship Insert

On 1/16/2015 2:41 AM, Jim Nasby 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

And if the advisory lock is used? That presumably creates an exclusive
lock on the asset "hometown_name". [in most examples given "Portland,
OR".] Would not any other process that runs (this function) on the same
asset have to wait for this specific transaction to commit or roll back
- blocking the race condition?

Roxanne
(sorry, I was out of town)