jack@douglastechnology.co.uk writes:
> I'm getting intermittent (but easily reproducible) deadlocks showing in my
> error log, for example:
> The above is from a minimal test case I've attempted to create rather than
> my production code. The test case is as follows:
> create table test( id serial primary key, val text );
> create function f_test(v text) returns integer language sql security definer
> set search_path = postgres,pg_temp as $$
> lock test in exclusive mode;
> insert into test(val) select v where not exists(select * from test where
> val=v);
> select id from test where val=v;
> $$;
I believe the issue with this is that a SQL function will do parsing (and
maybe planning too; don't feel like checking the code right now) for the
entire function body at once. This means that due to the INSERT command
you acquire RowExclusiveLock on the "test" table during function body
parsing, before the LOCK command actually executes. So the LOCK
represents a lock escalation attempt, and deadlocks are to be expected.
This coding technique would be safe in plpgsql, but not in a SQL-language
function.
There have been discussions of reimplementing SQL-language functions so
that parsing occurs one statement at a time, but don't hold your breath
about something happening in that direction; it doesn't seem to be a
high priority concern for anybody.
regards, tom lane