Re: [Proposal] Global temporary tables

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.

>> 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.
>>
>> 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.