Danger of idiomatic plpgsql loop for merging data

["J. Greg Davidson" <jgd@xxxxxxxx>]

Hi fellow PostgreSQL hackers,

I just got burned by the idiomatic loop
documented in the PostgreSQL manual as

Example 39-2. Exceptions with UPDATE/INSERT

I have now replaced this "standard" idiom
with a safer one described below.

What went wrong:

It seems that the table I was either
inserting into or selecting from had
a trigger inserting some associated
data which was sometimes raising a
unique_violation exception, turning the
"standard" idiom into an infinite loop!

My (simplified) old code looked like this:

CREATE TABLE foos (
  foo_ foo PRIMARY KEY DEFAULT next_foo();
  name_ text UNIQUE NOT NULL;
);

CREATE OR REPLACE
FUNCTION get_foo(text) RETURNS foo AS $$
DECLARE
  _foo foo;
BEGIN
  LOOP
    SELECT foo_ INTO _foo
      FROM foos WHERE name_ = $1;
    IF FOUND THEN RETURN _foo; END IF;
    BEGIN
      INSERT INTO foos(name_) VALUES($1);
    EXCEPTION
      WHEN unique_violation THEN
      -- maybe another thread?
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;

My (simplified) new code is longer but
more flexible, safer and adds logging:

CREATE OR REPLACE
FUNCTION old_foo(text) RETURNS foo AS $$
  SELECT foo_ FROM foos WHERE name_ = $1
$$ LANGUAGE SQL STRICT;

CREATE OR REPLACE
FUNCTION new_foo(text) RETURNS foo AS $$
DECLARE
  this regprocedure := 'new_foo(text)';
  _foo foo;
BEGIN
  INSERT INTO foos(name_) VALUES ($1)
    RETURNING foo_ INTO _foo;
  RETURN _ref;
EXCEPTION
  WHEN unique_violation THEN
    -- maybe another thread?
    RAISE NOTICE '% "%" unique_violation', this, $1;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql STRICT;

CREATE OR REPLACE
FUNCTION get_foo(text) RETURNS foo AS $$
  SELECT COALESCE(
    old_foo($1), new_foo($1), old_foo($1)
  )
$$ LANGUAGE sql STRICT;

_Greg

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general