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 1iuvUo-0001XD-Jf for pgsql-hackers@arkaria.postgresql.org; Fri, 24 Jan 2020 09:43:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iuvUl-0003Ug-Tx for pgsql-hackers@arkaria.postgresql.org; Fri, 24 Jan 2020 09:43:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iuvUl-0003UZ-Gn for pgsql-hackers@lists.postgresql.org; Fri, 24 Jan 2020 09:43:19 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1iuvUi-0003hF-9r for pgsql-hackers@postgresql.org; Fri, 24 Jan 2020 09:43:18 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id B568B21C719C; Fri, 24 Jan 2020 12:43:14 +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 [192.168.27.200] (gw.postgrespro.ru [93.174.131.141]) (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 6EE1421C7199; Fri, 24 Jan 2020 12:43:14 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1579858994; bh=uQyyI4crogkna0ZkDuifkt/TsQEc9h4b05fkY7qtCvY=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=C8K0gfDRQxJnh50sESxka9p9n4xYZbTYgiwV+3sKZVP5h2GsklHHASewCcdG3Hs5R /CP6pAlWCb8uYtPV2iRez1Cp4ZRjn3/Rlt04Rprta9NxUC46zBd8K5Ht2Lxd7bdQ2z 0F9s405sH8rdaYdxAph+Y1cB/LG1BYaa+K/7tMik= Subject: Re: [Proposal] Global temporary tables To: Pavel Stehule Cc: Robert Haas , Tomas Vondra , =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= , PostgreSQL Hackers , =?UTF-8?B?6JSh5p2+6Zyy?= =?UTF-8?B?KOWtkOWYiSk=?= , "Cai, Le" , =?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?= References: <24099f4e-742c-e92b-729c-77bac1ca80d4@postgrespro.ru> <80cf30c2-ebd5-a568-9f62-a970e4d91326@postgrespro.ru> <5767433E-1A77-4682-B3FD-4869911021C8@alibaba-inc.com> <6b7570aa-14ea-2f28-c3b5-74dd785bbafe@postgrespro.ru> <20200105200603.wrb4beacwb57hhpk@development> <33969545-BEF7-4518-A741-302CBF7FF269@alibaba-inc.com> <0498c45c-53cb-d9b0-c1e2-7d2d1a618511@postgrespro.ru> <20200109164829.mdl3z5xalmaqxs67@development> <3fef844f-0e73-8fcb-e4b6-595d6317b191@postgrespro.ru> <20200112015109.caab6qdr2ob6d5jv@development> From: Konstantin Knizhnik Message-ID: <7dd64333-3b6f-5aae-20ac-5fde142902e1@postgrespro.ru> Date: Fri, 24 Jan 2020 12:43:10 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------43569C84DEA62CD3817BDC35" 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. --------------43569C84DEA62CD3817BDC35 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 24.01.2020 12:09, Pavel Stehule wrote: > > > pá 24. 1. 2020 v 9:39 odesílatel Konstantin Knizhnik > > napsal: > > > > On 23.01.2020 23:47, Robert Haas wrote: > > On Sat, Jan 11, 2020 at 8:51 PM Tomas Vondra > > > wrote: > >> I proposed just ignoring those new indexes because it seems > much simpler > >> than alternative solutions that I can think of, and it's not > like those > >> other solutions don't have other issues. > > +1. > > > >> For example, I've looked at the "on demand" building as > implemented in > >> global_private_temp-8.patch, I kinda doubt adding a bunch of > index build > >> calls into various places in index code seems somewht suspicious. > > +1. I can't imagine that's a safe or sane thing to do. > > > > As far as you know there are two versions of GTT implementations now. > And we are going to merge them into single patch. > But there are some principle question concerning provided > functionality > which has to be be discussed: > should we prohibit DDL on GTT if there are more than one sessions > using > it. It includes creation/dropping indexes, dropping table, > altering table... > > If the answer is "yes", then the question whether to populate new > indexes with data is no relevant at all, because such situation > will not > be possible. > But in this case we will get incompatible behavior with normal > (permanent) tables and it seems to be very inconvenient from DBA > point > of view: > it will be necessary to enforce all clients to close their > sessions to > perform some DDL manipulations with GTT. > Some DDLs will be very difficult to implement if GTT is used by more > than one backend, for example altering table schema. > > My current solution is to allow creation/droping index on GTT and > dropping table itself, while prohibit alter schema at all for GTT. > Wenjing's approach is to prohibit any DDL if GTT is used by more than > one backend. > > > When I create index on GTT in one session, then I don't expect > creating same index in all other sessions that uses same GTT. > > But I can imagine to creating index on GTT enforces index in current > session, and for other sessions this index will be invalid to end of > session. So there are three possible alternatives: 1. Prohibit index creation of GTT when it used by more than once session. 2. Create index and populate them with data in all sessions using this GTT. 3. Create index only in current session and do not allow to use it in all other sessions already using this GTT (but definitely allow to use it in new sessions). 1 is Wenjing's approach, 2 - is my approach, 3 - is your suggestion :) I can construct the following table with pro/cons of each approach: Approach Compatibility with normal table User (DBA) friendly Complexity of implementation Consistency 1 - 1: requires restart of all sessions to perform operation 2: requires global cache of GTT 3/: /no man, no problem 2 + 3: if index is created then it is actually needed, isn't it? 1: use existed functionality to create index 2: if alter schema is prohibited 3 - 2: requires restart of all sessions to use created index 3: requires some mechanism for prohibiting index created after first session access to GTT 1: can perform DDL but do no see effect of it -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------43569C84DEA62CD3817BDC35 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 24.01.2020 12:09, Pavel Stehule wrote:


pá 24. 1. 2020 v 9:39 odesílatel Konstantin Knizhnik <k.knizhnik@postgrespro.ru> napsal:


On 23.01.2020 23:47, Robert Haas wrote:
> On Sat, Jan 11, 2020 at 8:51 PM Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
>> I proposed just ignoring those new indexes because it seems much simpler
>> than alternative solutions that I can think of, and it's not like those
>> other solutions don't have other issues.
> +1.
>
>> For example, I've looked at the "on demand" building as implemented in
>> global_private_temp-8.patch, I kinda doubt adding a bunch of index build
>> calls into various places in index code seems somewht suspicious.
> +1. I can't imagine that's a safe or sane thing to do.
>

As far as you know there are two versions of GTT implementations now.
And we are going to merge them into single patch.
But there are some principle question concerning provided functionality
which has to be be discussed:
should we prohibit DDL on GTT if there are more than one sessions using
it. It includes creation/dropping indexes, dropping table, altering table...

If the answer is "yes", then the question whether to populate new
indexes with data is no relevant at all, because such situation will not
be possible.
But in this case we will get incompatible behavior with normal
(permanent) tables and it seems to be very inconvenient from DBA point
of view:
it will be necessary to enforce all clients to close their sessions to
perform some DDL manipulations with GTT.
Some DDLs will be very difficult to implement if GTT is used by more
than one backend, for example altering table schema.

My current solution is to allow creation/droping index on GTT and
dropping table itself, while prohibit alter schema at all for GTT.
Wenjing's approach is to prohibit any DDL if GTT is used by more than
one backend.

When I create index on GTT in one session, then I don't expect creating same index in all other sessions that uses same GTT.

But I can imagine to creating index on GTT enforces index in current session, and for other sessions this index will be invalid to end of session.

So there are three possible alternatives:

1. Prohibit index creation of GTT when it used by more than once session.
2. Create index and populate them with data in all sessions using this GTT.
3. Create index only in current session and do not allow to use it in all other sessions already using this GTT (but definitely allow to use it in new sessions).

1 is Wenjing's approach, 2 - is my approach, 3 - is your suggestion :)

I can construct the following table with pro/cons of each approach:

Approach
 Compatibility with normal table
 User (DBA) friendly
 Complexity of implementation
 Consistency
1
 -
 1: requires restart of all sessions to perform operation
 2: requires global cache of GTT
 3: no man, no problem
2
 +
 3: if index is created then it is actually needed, isn't it? 1: use existed functionality to create index
 2: if alter schema is prohibited
3
 -
 2: requires restart of all sessions to use created index
 3: requires some mechanism for prohibiting index created after first session access to GTT
1: can perform DDL but do no see effect of it
 



-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--------------43569C84DEA62CD3817BDC35--