Re: Global temporary tables

st 18. 9. 2019 v 12:04 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

>
>
> On 21.08.2019 11:54, Konstantin Knizhnik wrote:
>
>
>
> On 20.08.2019 20:01, Pavel Stehule wrote:
>
> Another solution is wait on ZHeap storage and replica can to have own UNDO
> log.
>
>>
>> I thought about implementation of special table access method for
>> temporary tables.
>>
>
> +1
>
>
> Unfortunately implementing special table access method for temporary
> tables doesn't solve all problems.
> XID generation is not part of table access methods.
> So we still need to assign some XID to write transaction at replica which
> will not conflict with XIDs received from master.
> Actually only global temp tables can be updated at replica and so assigned
> XIDs can be stored only in tuples of such relations.
> But still I am not sure that we can use arbitrary XID for such
> transactions at replica.
>
> Also I upset by amount of functionality which has to be reimplemented for
> global temp tables if we really want to provide access method for them:
>
> 1. CLOG
> 2. vacuum
> 3. MVCC visibility
>
> And still it is not possible to encapsulate all changes need to support
> writes to temp tables at replica inside table access method.
> XID assignment, transaction commit and abort, subtransactions - all this
> places need to be patched.
>
>
> I was able to fully support work with global temp tables at replica
> (including subtransactions).
> The patch is attached. Also you can find this version in
> https://github.com/postgrespro/postgresql.builtin_pool/tree/global_temp_hot
>
> Right now transactions at replica updating global temp table are assigned
> special kind of GIDs which are not related with XIDs received from master.
> So special visibility rules are used for such tables at replica. Also I
> have to patch TransactionIdIsInProgress, TransactionIdDidCommit,
> TransactionIdGetCurrent
> functions to correctly handle such XIDs. In principle it is possible to
> implement global temp tables as special heap access method. But it will
> require copying a lot of code (heapam.c)
> so I prefer to add few checks to existed functions.
>
> There are still some limitations:
> - Number of transactions at replica which update temp tables is limited by
> 2^32 (wraparound problem is not addressed).
> - I have to maintain in-memory analog of CLOG for such transactions which
> is also not cropped. It means that for 2^32 transaction size of bitmap can
> grow up to 0.5Gb.
>
> I try to understand what are the following steps in global temp tables
> support.
> This is why I want to perform short survey - what people are expecting
> from global temp tables:
>
> 1. I do not need them at all.
> 2. Eliminate catalog bloating.
> 3. Mostly needed for compatibility with Oracle (simplify porting,...).
> 4. Parallel query execution.
> 5. Can be used at replica.
> 6. More efficient use of resources (first of all memory).
>

There can be other point important for cloud. Inside some cloud usually
there are two types of discs - persistent (slow) and ephemeral (fast). We
effectively used temp tables there because we moved temp tablespace to
ephemeral discs.

I missing one point in your list - developer's comfort - using temp tables
is just much more comfortable - you don't need create it again, again, ..
Due this behave is possible to reduce @2 and @3 can be nice side effect. If
you reduce @2 to zero, then @5 should be possible without any other.

Pavel

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