Re: [Proposal] Global temporary tables

> 2020年2月5日 上午12:47，Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> 写道：
>
>
>
> On 04.02.2020 18:01, 曾文旌(义从) wrote:
>>
>>
>>>
>>> Yes, exactly.
>>> But it is still not clear to me why do we need some special handling for GTT?
>>> Shared memory is reinitialized and storage of temporary tables is removed.
>>> It is true for both local and global temp tables.
>> Of course not. The local temp table cleans up the entire table (including catalog buffer and datafile). GTT is not.
>>
>
> What do you mean by "catalog buffer"?
> Yes, cleanup of local temp table requires deletion of correspondent entry from catalog and GTT should not do it.
> But I am speaking only about cleanup of data files of temp relations. It is done in the same way for local and global temp tables.
For native pg, the data file of temp table will not be cleaned up direct after oom happen.
Because the orphan local temp table(include catalog, local buffer, datafile) will be cleaned up by deleting the orphan temp schame in autovacuum.
So for GTT ,we cannot do the same with just deleting data files. This is why I dealt with it specifically.

>
>
>>> In my patch autovacuum is prohibited for GTT.
>> But vacuum GTT is not prohibited.
>>
> Yes, but the simplest solution is to prohibit also explicit vacuum of GTT, isn't it?
>
>>>
>>>> IMHO forced terminated of client sessions is not acceptable solution.
>>>>> And it is not an absolutely necessary requirement.
>>>>> So from my point of view we should not add such limitations to GTT design.
>>>> This limitation makes it possible for the GTT to do all the DDL.
>>>> IMHO even oracle's GTT has similar limitations.
>>>
>>> I have checked that Oracle is not preventing creation of index for GTT if there are some active sessions working with this table. And this index becomes visible for all this sessions.
>> 1 Yes The creation of inde gtt has been improved in global_temporary_table_v10-pg13.patch
>> 2 But alter GTT ; drop GTT ; drop index on GTT is blocked by other sessions
>>
> Yes, you are right.
> Orale documetation says:
> > 1) DDL operation on global temporary tables
> > It is not possible to perform a DDL operation (except TRUNCATE <https://www.oracletutorial.com/oracle-basics/oracle-truncate-table/>) on an existing global temporary table if one or more sessions are currently bound to that table.
>
> But looks like create index is not considered as DDL operation on GTT and is also supported by Oracle.

>
> Your approach with prohibiting such accessed using shared cache is certainly better then my attempt to prohibit such DDLs for GTT at all.
> I just what to eliminate maintenance of such shared cache to simplify the patch.
>
> But I still think that we should allow truncation of GTT and creating/dropping indexes on it without any limitations.
I think the goal of this work is this.
But, the first step is let GTT get as many features as possible on regular tables, even with some limitations.

>>>
>>> May be the easies solution is to prohibit explicit vacuum of GTT?
>> I think vacuum is an important part of GTT.
>>
>> Looking back at previous emails, robert once said that vacuum GTT is pretty important.
>> https://www.postgresql.org/message-id/CA%2BTgmob%3DL1k0cpXRcipdsaE07ok%2BOn%3DtTjRiw7FtD_D2T%3DJwhg%40mail.gmail.com <https://www.postgresql.org/message-id/CA+Tgmob=L1k0cpXRcipdsaE07ok+On=tTjRiw7FtD_D2T=Jwhg@mail.gmail.com>
>>
>
> Well, may be I am not right.
> I never saw use cases where temp table are used not like append-only storage (when temp table tuples are updated multiple times).
> But I think that if such problem actually exists then solution is to support autovacuum for temp tables, rather than allow manual vacuum.
> Certainly it can not be done by another worker because it has no access to private backend's data. But it can done incrementally by backend itself.
>
>