Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions - Mailing list pgsql-hackers

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> I propose here that we stop the release of lock before end of transaction.
> I have been suffering from the early release of lock.

Now that read and write locks don't interfere with each other, this may
not be as big a performance loss as it sounds.

But, do you intend to apply this principle to system tables as well as
user tables?  I am concerned that we will have deadlock problems if we
try to do it for system tables, because practically all transactions
will start out with system-table accesses, which implies grabbing a read
lock on those system tables if you want to take a hard line about it.
If you later need to upgrade to a higher-grade lock on any of those
system tables, you've got trouble.

There is another issue I've been thinking about that seems to require
some amount of lock-releasing within a transaction, too.  Specifically,
I'd like to see the parser grab a minimal lock (AccessShareLock) on each
table referenced in a query as soon as it recognizes the table name.
The rewriter would also have to lock each table that it adds into the
query due to rules.  This would prevent problems that we have now with
ALTER TABLE running in parallel with parsing/planning of a query.

But, many queries require more than AccessShareLock on their tables.
If we simply try to grab the higher-grade locks without releasing
AccessShareLock, we will certainly suffer deadlock.

If anyone's having a hard time seeing why lock upgrade is dangerous,
consider two backends trying at about the same time to doBEGIN; LOCK TABLE foo; etc etc
since this can happen:Backend A's parser recognizes 'foo', grabs AccessShareLock on fooBackend B's parser recognizes
'foo',grabs AccessShareLock on fooBackend A's executor tries to get AccessExclusiveLock on foo,    must wait for
BBackendB's executor tries to get AccessExclusiveLock on foo,    must wait for A
 

So I think the real solution must go something like this:

* Parser and rewriter grab AccessShareLock on each table as it is added
to the query.
* At start of planner, all tables and required access rights are known.
Release AccessShareLocks, then grab required lock levels on each table.
We probably want to error out if any DDL alteration has actually occurred
to any of the tables by the time we re-acquire its lock.

An easy improvement on this is to avoid the drop/grab if AccessShareLock
is the only thing needed on each table (as in a SELECT).  We could
further try to extend the parser so that it grabs a sufficient lock
on each table initially --- that's probably easy enough for INSERT
target tables and so forth, but we cannot guarantee that it will be
possible in every case.  (Consider rule rewrites that add actions
not present in the initial query.)

Can you see a way to solve this problem without dropping/grabbing locks?

> If we don't allow DDL command inside transaction block,we won't need
> the release before end of transaction.
> If we allow DDL command inside transaction block,it may be a problem.
> But are there any other principles which could guarantee consistency ?

I certainly do not wish to give up the goal of supporting DDL statements
inside transactions.  What problems do you foresee?
        regards, tom lane