Re: Simple Atomic Relationship Insert

On 1/19/2015 4:58 PM, Robert DiFalco wrote:
> 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.

So you fixed it - good. In our situation we were never inserting more
than one hometown** record in the same transaction - so I see now that
my scenario was not the same as yours - but yours evolved to that.
(**using "hometown" for our record type)

> On Mon, Jan 19, 2015 at 1:56 PM, Robert DiFalco
> <robert(dot)difalco(at)gmail(dot)com <mailto:robert(dot)difalco(at)gmail(dot)com>> wrote:
>
> I don't think an advisory lock would remove the deadlock.
>

For 2 or more hometown inserts per transaction - I agree - it won't fix it.

Glad to hear you have it fixed.

Roxanne
>
>
> On Sun, Jan 18, 2015 at 10:33 PM, Roxanne Reid-Bennett
> <rox(at)tara-lu(dot)com <mailto: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
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth