Re: Simple Atomic Relationship Insert

[Robert DiFalco <robert.difalco@xxxxxxxxx>]

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@xxxxxxxxxx> 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.difalco@xxxxxxxxx> 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?