Re: Simple Atomic Relationship Insert

[Roxanne Reid-Bennett <rox@xxxxxxxxxxx>]

I don't like loops to catch "failure" condition... can you possibly fail to stop?

In a stored procedure (or with auto-commit turned off in any transaction)... You can avoid any race condition by using a semaphore (e.g. you lock "something" for the duration of the critical part of your processing so that anything that "would" update that data just waits). 
[ http://www.postgresql.org/docs/9.4/static/applevel-consistency.html#NON-SERIALIZABLE-CONSISTENCY ]

When you have a sequence of steps that need to be serialized across processes, choose or even create a table to use for locking.  SELECT FOR UPDATE "a" row in that table.  Every process trying to access that row now waits until the first transaction to get the exclusive lock commits/rollbacks (no actual update need be executed).   How atomic the lock is depends on what you use for your lock. (poor levels of atomicity will lead to performance problems that are hard to diagnose)

For the most recent version of this I've done... we used the unique business key in an project based audit table.  (the table was totally unrelated to the work being done other than it had the same business key values and locking wouldn't interfere with other processing.)  So if you had to "create" something to lock...


    -- create something to lock... uniqueness is not required but allowed for if you need it
         INSERT INTO LOCKIT (table, biz_key) VALUES ('hometowns',hometown_name); 
         insert into users(name, hometown_id)  VALUES ('Robert', SELECT select_hometown_id(hometown_name));

CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR) RETURNS INTEGER AS $
DECLARE hometown_id INTEGER;
BEGIN

         SELECT FOR UPDATE from LOCKIT where table = 'hometowns' and biz_key = hometown_name; -- "wait"

WITH sel AS (
    SELECT id FROM hometowns WHERE name = 'Portland'
), ins AS (
  INSERT INTO hometowns(name)
    SELECT 'Portland'
    WHERE NOT EXISTS (SELECT 1 FROM sel)
  RETURNING id
)

SELECT id INTO hometown_id FROM ins UNION ALL SELECT id FROM sel;

      RETURN hometown_id;
    END;
$ LANGUAGE plpgsql;

Only one process will be able to execute the CTE at a time - always - and no looping required.

Roxanne

On 1/13/2015 6:52 PM, Robert DiFalco wrote:

Good points. I guess my feeling is that if there can be a race condition on INSERT then the CTE version is not truly atomic, hence the LOOP.

On Tue, Jan 13, 2015 at 3:11 PM, Brian Dunavant <brian@xxxxxxxxxx> wrote:

A very good point, but it does not apply as here (and in my article)
we are not using updates, only insert and select.

On Tue, Jan 13, 2015 at 6:03 PM, Thomas Kellerer <spam_eater@xxxxxxx> wrote:
> Brian Dunavant wrote on 13.01.2015 22:33:
>>
>> What issue are you having?  I'd imagine you have a race condition on
>> the insert into hometowns, but you'd have that same race condition in
>> your app code using a more traditional 3 query version as well.
>>
>> I often use CTEs like this to make things atomic.  It allows me to
>> remove transactional code out of the app and also to increase
>> performance by reducing the back-and-forth to the db.
>> http://omniti.com/seeds/writable-ctes-improve-performance
>>
>
> Craig Ringer explained some of the pitfalls of this approach here:
>
> http://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates
>
> which is a follow up question based on this:
> http://stackoverflow.com/a/8702291/330315
>
> Thomas
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
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