Re: Simple Atomic Relationship Insert

Hometowns get selected and possibly inserted in unpredictable ways even
from multiple concurrent sessions. The only way I could figure out how to
solve it was to force each INSERT hometowns to be in its own transaction.

On Mon, Jan 19, 2015 at 1:56 PM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
wrote:

> I don't think an advisory lock would remove the deadlock.
>
> On Sun, Jan 18, 2015 at 10:33 PM, Roxanne Reid-Bennett <rox(at)tara-lu(dot)com>
> wrote:
>
>> 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)
>>
>>
>>
>> --
>> 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
>>
>
>