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

On Fri, 24 May 2019 at 01:39, Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> On Thu, May 23, 2019 at 08:54:59AM -0700, Andres Freund wrote:
> > On 2019-05-23 12:36:09 +0100, Simon Riggs wrote:
> >> 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.
> >
> > That's extremely common.
> >
> > There's no way we can forbid using session after 2PC unconditionally,
> > it'd break most users of 2PC.
>
> This does not break Postgres-XC or XL as their inner parts with a
> COMMIT involving multiple write nodes issue a set of PREPARE
> TRANSACTION followed by an immediate COMMIT PREPARED which are
> transparent for the user, so the point of Simon looks sensible from
> this angle.

Maybe, but I am not discussing other products since they can be changed
without discussion here.

> Howewer, I much agree with Andres that it is very common
> to have PREPARE and COMMIT PREPARED issued with different sessions. I
> am not much into the details of XA-compliant drivers, but I think that
> having us lose this property would be the source of many complaints.
>

Yes, it is *very* common to have PREPARE and COMMIT PREPARED issued from
different sessions. That is the main usage in a session pool and not the
point I made.

There are two usage patterns, with a correlation between the way 2PC and
temp tables work:

Transaction-mode session-pool: (Most common usage mode)
* No usage of session-level temp tables (because that wouldn't work)
* 2PC with PREPARE and COMMIT PREPARED on different sessions
* No reason at all to hold locks on temp table after PREPARE

Session-mode (Less frequent usage mode)
* Usage of session-level temp tables
* 2PC on same session only, i.e. no usage of session between PREPARE and
COMMIT PREPARED (Simon's observation)
* No reason at all to hold locks on temp table after PREPARE (Simon's
conclusion)

I'd like to hear from anyone that thinks my observation is incorrect and to
explain their usage pattern so we can understand why they think they would
execute further SQL between PREPARE and COMMIT PREPARED when using a single
session, while at the same time using temp tables.

If there really is a usage pattern there we should take note of, then I
suggest we introduce a parameter that allows temp table locks to be dropped
at PREPARE, so that we can use 2PC and Temp Tables with ease, for those
that want it.

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