Postgres Pro
Downloads
Home   >   mailing lists

Re: Simple Atomic Relationship Insert - Mailing list pgsql-general

From Robert DiFalco
Subject Re: Simple Atomic Relationship Insert
Date
Msg-id CAAXGW-zXwxEM7LcGBaSMRP4zsBReXnbduSSSHeg=tKWoEz2nfg@mail.gmail.com
Whole thread Raw
In response to Re: Simple Atomic Relationship Insert  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
List pgsql-general
Tree view
The code shown in the Doc (I think) will still give you deadlock in the case where you have two sessions concurrently trying to insert the same 'hometown'. For example:
    
  INSERT INTO users VALUES('Tom', select_hometown_id('Portland, OR'));
  INSERT INTO users VALUES(''Waits', select_hometown_id('Portland, OR'));

The LOOP removes the race condition BUT with my limited knowledge I can only think of two ways to prevent the deadlock. Some kind of lock (not sure which) or force a separate transaction for the hometowns INSERT (also not sure how to do this in a FUNCTION). Is there an accepted approach here?



On Thu, Jan 15, 2015 at 11:41 PM, Jim Nasby <Jim.Nasby@bluetreble.com> 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

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Chris Mair
Date:
Subject: Re: Out of Memory
Next
From: Maciek Sakrejda
Date:
Subject: sslcompression / PGSSLCOMPRESSION not behaving as documented?