Re: Simple Atomic Relationship Insert

This seems to get rid of the INSERT race condition.

CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR)
RETURNS INTEGER AS $
DECLARE hometown_id INTEGER;
BEGIN
LOOP
BEGIN
WITH sel AS (
SELECT id FROM hometowns WHERE name = hometown_name
), ins AS (
INSERT INTO hometowns (name)
SELECT hometown_name
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;

EXCEPTION WHEN unique_violation
THEN
END;
END LOOP;
END;
$ LANGUAGE plpgsql;

On Tue, Jan 13, 2015 at 1:53 PM, Brian Dunavant <brian(at)omniti(dot)com> wrote:

> With the single CTE I don't believe you can do a full upsert loop. If
> you're doing this inside of a postgres function, your changes are
> already atomic, so I don't believe by switching you are buying
> yourself much (if anything) by using a CTE query instead of something
> more traditional here.
>
> The advantages of switching to a CTE would be if this code was all
> being done inside of the app code with multiple queries.
>
> On Tue, Jan 13, 2015 at 4:39 PM, Robert DiFalco
> <robert(dot)difalco(at)gmail(dot)com> wrote:
> > Well, traditionally I would create a LOOP where I tried the SELECT, if
> there
> > was nothing I did the INSERT, if that raised an exception I would repeat
> the
> > LOOP.
> >
> > What's the best way to do it with the CTE? Currently I have the following
> > which gives me Duplicate Key Exceptions when two sessions try to insert
> the
> > same record at the same time.
> >
> > CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR)
> RETURNS
> > INTEGER AS $
> > DECLARE hometown_id INTEGER;
> > BEGIN
> > WITH sel AS (
> > SELECT id FROM hometowns WHERE name = hometown_name
> > ), ins AS (
> > INSERT INTO hometowns (name)
> > SELECT hometown_name
> > 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;
> >
> > And that is no bueno. Should I just put the whole thing in a LOOP?
>