From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | Roxanne Reid-Bennett <rox(at)tara-lu(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Simple Atomic Relationship Insert |
Date: | 2015-01-19 21:56:25 |
Message-ID: | CAAXGW-zHYnZVbzw6rn=LZqjsbLnAQdy4yTa8c684aqXB4uFXyQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Robert DiFalco | 2015-01-19 21:58:26 | Re: Simple Atomic Relationship Insert |
Previous Message | Jonathan Vanasco | 2015-01-19 21:47:41 | splitting up tables based on read/write frequency of columns |