Re: Simple Atomic Relationship Insert

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(at)omniti(dot)com
> <mailto:brian(at)omniti(dot)com>> 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(at)gmx(dot)net <mailto:spam_eater(at)gmx(dot)net>> 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(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org>)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>
> --
> 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