Re: [Proposal] Global temporary tables - Mailing list pgsql-hackers

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

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.