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

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:

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