Re: Read-only access to temp tables for 2PC transactions

On Tue, 14 May 2019 at 10:53, Stas Kelvich <s(dot)kelvich(at)postgrespro(dot)ru> wrote:

> One of the problems regarding the use of temporary tables in prepared
> transactions
> is that such transaction will hold locks for a temporary table after being
> prepared.
> That locks will prevent the backend from exiting since it will fail to
> acquire lock
> needed to delete temp table during exit. Also, re-acquiring such lock
> after server
> restart seems like an ill-defined operation.
>
...

> Any thoughts?
>

It occurs to me that there is no problem to solve here.

When we PREPARE, it is because we expect to COMMIT or ABORT soon afterwards.

If we are using an external transaction manager, the session is idle while
we wait for the manager to decide whether to commit or abort. Or the
session is disconnected or server is crashes. Either way, nothing happens
between PREPARE and resolution. So there is no need at all for locking of
temporary tables after the prepare.

The ONLY case where this matters is if someone does a PREPARE and then
starts doing other work on the session. Which makes no sense in the normal
workflow of a session. I'm sure there are tests that do that, but those
tests are unrepresentative of sensible usage.

If you were using session temporary tables while using a transaction mode
pool then you're already going to have problems, so that aspect is a
non-issue.

So I think we should ban this by definition. Say that we expect that you
won't do any work on the session until COMMIT/ABORT. That means we can then
drop locks on sesion temporary tables and drop on-commit temp tables when
we hit the prepare, not try and hold them for later.

A patch is needed to implement the above, but I think we can forget the
current patch as not needed.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services