> 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.
That makes perfect sense, many thanks for the explanation.
> This coding technique would be safe in plpgsql, but not in a SQL-language
function.
That's useful to know - I already worked around the issue with a retry-loop
(as I'm basically doing an upsert the 'bad' way with `lock table` - roll on
9.5!)
Kind regards
Jack