Re: [Proposal] Global temporary tables

pá 25. 10. 2019 v 17:01 odesílatel Robert Haas <robertmhaas(at)gmail(dot)com>
napsal:

> On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
> > Just to clarify.
> > I have now proposed several different solutions for GTT:
> >
> > Shared vs. private buffers for GTT:
> > 1. Private buffers. This is least invasive patch, requiring no changes
> in relfilenodes.
> > 2. Shared buffers. Requires changing relfilenode but supports parallel
> query execution for GTT.
>
> I vote for #1. I think parallel query for temp objects may be a
> desirable feature, but I don't think it should be the job of a patch
> implementing GTTs to make it happen. In fact, I think it would be an
> actively bad idea, because I suspect that if we do eventually support
> temp relations for parallel query, we're going to want a solution that
> is shared between regular temp tables and global temp tables, not
> separate solutions for each.
>
> > 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.

> > So except the limitation mentioned above (which I do not consider as
> critical) there is only one problem which was not addressed: maintaining
> statistics for GTT.
> > If all of the following conditions are true:
> >
> > 1) GTT are used in joins
> > 2) There are indexes defined for GTT
> > 3) Size and histogram of GTT in different backends can significantly
> vary.
> > 4) ANALYZE was explicitly called for GTT
> >
> > then query execution plan built in one backend will be also used for
> other backends where it can be inefficient.
> > I also do not consider this problem as "show stopper" for adding GTT to
> Postgres.
>
> I think that's *definitely* a show stopper.
>
> > I still do not understand the opinion of community which functionality
> of GTT is considered to be most important.
> > But the patch with local buffers and no replica support is small enough
> to become good starting point.
>
> Well, it seems we now have two patches for this feature. I guess we
> need to figure out which one is better, and whether it's possible for
> the two efforts to be merged, rather than having two different teams
> hacking on separate code bases.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
>