Re: Simple Atomic Relationship Insert

[Roxanne Reid-Bennett <rox@xxxxxxxxxxx>]

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.difalco@xxxxxxxxx> 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@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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