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

On Fri, 24 May 2019 at 01:39, Michael Paquier <michael@paquier.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/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

From my point of view releasing all temporary table locks after preparing of 2PC transaction is not technically possible:
assume that this transaction has  updated some tuples of temporary table - them are not visible to other transactions until 2PC is committed,
but since lock is removed, other transactions can update the same tuple.

Prohibiting transaction to do anything else  except COMMIT/ROLLBACK PREPARED after preparing transaction seems to be too voluntaristic decision.
I do not think that "That's extremely common", but I almost sure that there are such cases.

The safe scenario is when temporary table is created and dropped inside transaction (table created with ON COMMIT DROP). But there is still one issue with this scenario: first creation of temporary table cause creation of
pg_temp namespace and it can not be undone. Another possible scenario is temporary table created outside transaction with ON COMMIT DELETE. In this case truncation of table on prepare will also release all locks.

Pure read-only access to temporary tables seems to be not so useful,  because before reading something from temporary table, we have to write something to it. And if reading of temporary table is wrapped in 2PC,
then most likely writing to temporary table also has to be wrapped in 2PC, which is not possible with the proposed solution.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company