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.