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.