Jack Douglas (imported from SE)
My function `new_customer` is called several times per second (but only once per session) by a web application. The very first thing it does is lock the `customer` table (to do an 'insert if not exists'—a simple variant of an `upsert`).

My understanding of [the docs](http://www.postgresql.org/docs/9.4/static/sql-lock.html) is that other calls to `new_customer` should simply queue until all previous calls have finished:

> LOCK TABLE obtains a table-level lock, waiting if necessary for any conflicting locks to be released.

Why is it sometimes deadlocking instead?

definition:

    create function new_customer(secret bytea) returns integer language sql 
                    security definer set search_path = postgres,pg_temp as $$
      lock customer in exclusive mode;
      --
      with w as ( insert into customer(customer_secret,customer_read_secret)
                  select secret,decode(md5(encode(secret, 'hex')),'hex') 
                  where not exists(select * from customer where customer_secret=secret)
                  returning customer_id )
      insert into collection(customer_id) select customer_id from w;
      --
      select customer_id from customer where customer_secret=secret;
    $$;

error from log:

```none
2015-07-28 08:02:58 BST DETAIL:  Process 12380 waits for ExclusiveLock on relation 16438 of database 12141; blocked by process 12379.
        Process 12379 waits for ExclusiveLock on relation 16438 of database 12141; blocked by process 12380.
        Process 12380: select new_customer(decode($1::text, 'hex'))
        Process 12379: select new_customer(decode($1::text, 'hex'))
2015-07-28 08:02:58 BST HINT:  See server log for query details.
2015-07-28 08:02:58 BST CONTEXT:  SQL function "new_customer" statement 1
2015-07-28 08:02:58 BST STATEMENT:  select new_customer(decode($1::text, 'hex'))
```

relation:

    postgres=# select relname from pg_class where oid=16438;
    ┌──────────┐
    │ relname  │
    ├──────────┤
    │ customer │
    └──────────┘


---
### edit:

I've managed to get a simple-ish reproducible test case. To me this looks like a bug due to some sort of race condition.

schema:

    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;
    $$;

bash script run simultaneously in two bash sessions:

    for i in {1..1000}; do psql postgres postgres -c "select f_test('blah')"; done

error log (usually a handful of deadlocks over the 1000 calls):

    2015-07-28 16:46:19 BST ERROR:  deadlock detected
    2015-07-28 16:46:19 BST DETAIL:  Process 9394 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9393.
            Process 9393 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9394.
            Process 9394: select f_test('blah')
            Process 9393: select f_test('blah')
    2015-07-28 16:46:19 BST HINT:  See server log for query details.
    2015-07-28 16:46:19 BST CONTEXT:  SQL function "f_test" statement 1
    2015-07-28 16:46:19 BST STATEMENT:  select f_test('blah')

---

### edit 2:

@ypercube [suggested a variant](http://chat.stackexchange.com/transcript/message/23065138#23065138) with the `lock table` outside the function:

    for i in {1..1000}; do psql postgres postgres -c "begin; lock test in exclusive mode; select f_test('blah'); end"; done

interestingly this eliminates the deadlocks.
Top Answer
Jack Douglas (imported from SE)
I posted this [to pgsql-bugs](http://www.postgresql.org/message-id/20150728162823.25043.27625@wrigleys.postgresql.org) and [the reply there](http://www.postgresql.org/message-id/17665.1438105627@sss.pgh.pa.us) from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, **the lock generated by the `insert` is obtained *before* the exclusive lock on the table**:

> 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

This also explains why locking the table outside the function in a wrapping plpgsql block (as [suggested by](http://chat.stackexchange.com/transcript/message/23065138#23065138) @ypercube) prevents the deadlocks.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.