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