Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iz1Mo-0008Hm-Vz for pgsql-hackers@arkaria.postgresql.org; Tue, 04 Feb 2020 16:48:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iz1Mm-0003Lj-I8 for pgsql-hackers@arkaria.postgresql.org; Tue, 04 Feb 2020 16:48:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iz1Ml-0003Lb-SO for pgsql-hackers@lists.postgresql.org; Tue, 04 Feb 2020 16:48:00 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1iz1Md-0004Sn-Pn for pgsql-hackers@postgresql.org; Tue, 04 Feb 2020 16:47:58 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 4CD0821C5816; Tue, 4 Feb 2020 19:47:49 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [10.84.6.243] (unknown [212.248.24.235]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 55F0C21C580A; Tue, 4 Feb 2020 19:47:48 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1580834869; bh=L6hlhr3TqSb/zXtOXV+iOeWY4POb7lVUSllCqaGHgig=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=IzqaM/ifMPNtdL1qoEXFs17NfzIQqP22OKH5PpLHAtKciYk0jQJDRbIYkhTn9vQv1 sCIpaJA5T2jG7FED8a2jymsHYQ5gHVlAptXBvJcerwFh/ArB334t6b7YpttPTvB8ku IWeW17Uxq3b3gThGrLkp6IYu2+Xm6F7WIRtdVo/o= Subject: Re: [Proposal] Global temporary tables To: =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= Cc: Pavel Stehule , Erik Rijkers , Tomas Vondra , Robert Haas , Julien Rouhaud , Dean Rasheed , PostgreSQL Hackers , =?UTF-8?B?6JSh5p2+6Zyy?= =?UTF-8?B?KOWtkOWYiSk=?= , "Cai, Le" , =?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?= References: <1A1A6EDC-D0EC-47B0-BD21-C2ACBAEA65E4@alibaba-inc.com> <3A07073C-AF60-4C5E-859F-81A82911B671@alibaba-inc.com> <55cfa22b53c89a75cebcb40eb245c999@xs4all.nl> <9DB9B56D-EF23-45CB-8AF4-0129D736446A@alibaba-inc.com> <3D295871-7918-4019-A6F8-22C792C390E7@alibaba-inc.com> <1C648A29-C88F-45B1-BE66-84B51D319CAD@alibaba-inc.com> From: Konstantin Knizhnik Message-ID: Date: Tue, 4 Feb 2020 19:47:47 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.4.1 MIME-Version: 1.0 In-Reply-To: <1C648A29-C88F-45B1-BE66-84B51D319CAD@alibaba-inc.com> Content-Type: multipart/alternative; boundary="------------9F9FCACA2CE2DB3E0FCA3496" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------9F9FCACA2CE2DB3E0FCA3496 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 |) 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. --------------9F9FCACA2CE2DB3E0FCA3496 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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


--------------9F9FCACA2CE2DB3E0FCA3496--