I am working on eliminating the "relation NNN modified while in use"
misfeature by instead grabbing a lock on each relation at first use
in a statement, and holding that lock till end of transaction. The
main trick here is to make sure that the first lock grabbed is adequate
--- for example, it won't do to grab AccessShareLock and then have to
raise that to AccessExclusiveLock, because there will be a deadlock if
two backends do this concurrently.
To help debug this, I'm planning to add a little bit of code to the
lock manager that detects a request for a lock on an object on which
we already hold a lock of a lower level. What I'm wondering about is
whether to make the report be elog(DEBUG) --- ie, send to postmaster
log only --- or elog(NOTICE), so that users would see it by default.
A NOTICE might be useful to users since it would complain about
deadlock-prone user-level coding practices too, such as
begin;
select * from foo; -- grabs read lock
lock table foo; -- grabs exclusive lock
However, it might not be *very* useful, because the lock manager isn't
in a position to issue a message that's much more intelligible than
this:
NOTICE: Deadlock risk: raising lock level from 1 to 4 on object 85372/5732
(The lock level could be printed symbolically, but I doubt that very
much can be done with the object identifier --- it's not safe for the
lock manager to try to resolve relation OIDs to names, for example.)
Right now I'm thinking that this sort of notice would just create more
confusion than enlightenment for most users, so I'm inclined to make it
a DEBUG message. But that's a judgment call, so I thought I'd throw
the issue out for discussion. Any contrary opinions?
regards, tom lane
