Re: [Proposal] Global temporary tables

On Fri, Oct 25, 2019 at 11:14 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> > Access to GTT at replica:
>> > 1. Access is prohibited (as for original temp tables). No changes at all.
>> > 2. Tuples of temp tables are marked with forzen XID. Minimal changes, rollbacks are not possible.
>> > 3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
>> > and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).
>>
>> I again vote for #1. A GTT is defined to allow data to be visible only
>> within one session -- so what does it even mean for the data to be
>> accessible on a replica?
>
> why not? there are lot of sessions on replica servers. One usage of temp tables is fixing estimation errors. You can create temp table with partial query result, run ANALYZE and evaluate other steps. Now this case is not possible on replica servers.
>
> One motivation for GTT is decreasing port costs from Oracle. But other motivations, like do more complex calculations on replica are valid and valuable.

Hmm, I think I was slightly confused when I wrote my previous
response. I now see that what was under discussion was not making data
from the master visible on the standbys, which really wouldn't make
any sense, but rather allowing standby sessions to also use the GTT,
each with its own local copy of the data. I don't think that's a bad
feature, but look how invasive the required changes are. Not allowing
rollbacks seems dead on arrival; an abort would be able to leave the
table and index mutually inconsistent. A separate XID space would be
a real solution, perhaps, but it would be *extremely* complicated and
invasive to implement.

One thing that I've learned over and over again as a developer is that
you get a lot more done if you tackle one problem at a time. GTTs are
a sufficiently-large problem all by themselves; a major reworking of
the way XIDs work might be a good project to undertake at some point,
but it doesn't make any sense to incorporate that into the GTT
project, which is otherwise about a mostly-separate set of issues.
Let's not try to solve more problems at once than strictly necessary.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company