Amir Zicherman <amir.zicherman@gmail.com> writes:
> I tried using the lock inside the function body and that didn't lock.
> ...
> LOCK TABLE "mytable" IN ROW EXCLUSIVE MODE;
Sure it did. But ROW EXCLUSIVE is a very weak lock (the same as what
INSERT would take for itself) and it's not going to lock out other
INSERTs.
If you want to be the only writer of the table then you need EXCLUSIVE
lock, or possibly ACCESS EXCLUSIVE if you'd like to block out readers
too. See
http://www.postgresql.org/docs/7.4/static/explicit-locking.html#LOCKING-TABLES
> when i do a regular insert statement without a function or a
> transaction block, i get no deadlocks because the INSERT statement
> automatically gets an ACCESS EXCLUSIVE LOCK from what i understand.
Don't know where you got that idea. If it did that, we'd have no
concurrency at all.
regards, tom lane