Re: [Proposal] Global temporary tables

> 2020年1月12日 上午9:14，Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> 写道：
>
> On Fri, Jan 10, 2020 at 03:24:34PM +0300, Konstantin Knizhnik wrote:
>>
>>
>> On 09.01.2020 19:30, Tomas Vondra wrote:
>>
>>
>>>
>>>>
>>>>>
>>>>>> 3 Still no one commented on GTT's transaction information processing, they include
>>>>>> 3.1 Should gtt's frozenxid need to be care?
>>>>>> 3.2 gtt’s clog clean
>>>>>> 3.3 How to deal with "too old" gtt data
>>>>>>
>>>>>
>>>>> No idea what to do about this.
>>>>>
>>>>
>>>> I wonder what is the specific of GTT here?
>>>> The same problem takes place for normal (local) temp tables, doesn't it?
>>>>
>>>
>>> Not sure. TBH I'm not sure I understand what the issue actually is.
>>
>> Just open session, create temporary table and insert some data in it.
>> Then in other session run 2^31 transactions (at my desktop it takes about 2 hours).
>> As far as temp tables are not proceeded by vacuum, database is stalled:
>>
>> ERROR: database is not accepting commands to avoid wraparound data loss in database "postgres"
>>
>> It seems to be quite dubious behavior and it is strange to me that nobody complains about it.
>> We discuss many issues related with temp tables (statistic, parallel queries,...) which seems to be less critical.
>>
>> But this problem is not specific to GTT - it can be reproduced with normal (local) temp tables.
>> This is why I wonder why do we need to solve it in GTT patch.
>>
>
> Yeah, I think that's out of scope for GTT patch. Once we solve it for
> plain temporary tables, we'll solve it for GTT too.
1. The core problem is that the data contains transaction information (xid), which needs to be vacuum(freeze) regularly to avoid running out of xid.
The autovacuum supports vacuum regular table but local temp does not. autovacuum also does not support GTT.

2. However, the difference between the local temp table and the global temp table(GTT) is that
a) For local temp table: one table hava one piece of data. the frozenxid of one local temp table is store in the catalog(pg_class).
b) For global temp table: each session has a separate copy of data, one GTT may contain maxbackend frozenxid.
and I don't think it's a good idea to keep frozenxid of GTT in the catalog(pg_class).
It becomes a question: how to handle GTT transaction information?

I agree that problem 1 should be completely solved by a some feature, such as local transactions. It is definitely not included in the GTT patch.

But, I think we need to ensure the durability of GTT data. For example, data in GTT cannot be lost due to the clog being cleaned up. It belongs to problem 2.

Wenjing

>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services