Subject: Re: [Proposal] Global temporary tables
To: Robert Haas <robertmhaas@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
 Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= <wenjing.zwj@alibaba-inc.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>, =?UTF-8?B?6JSh5p2+6Zyy?=
 =?UTF-8?B?KOWtkOWYiSk=?= <zijia@taobao.com>, "Cai, Le"
 <le.cai@alibaba-inc.com>, =?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?=
 <shaocong.xsc@alibaba-inc.com>
References: <24099f4e-742c-e92b-729c-77bac1ca80d4@postgrespro.ru>
 <CAFj8pRAvegZoQCLw3vjHZ=6_Kr7-1UAA5QgGYOshhXU6j_RuCg@mail.gmail.com>
 <0bddbc6d-fed1-d56b-d39f-67352461b2d1@postgrespro.ru>
 <CAFj8pRDVaNQhg1juJKuj3A2fN-TW5LsMVCqyOkEhKKjbjHEk5w@mail.gmail.com>
 <407d6499-950d-e3d9-acbc-a2e86d8b82a3@postgrespro.ru>
 <CA+TgmobN=o7d4zTJywJzsvkCeL4-_3ttWK_dhzpt-P3yCJ=77w@mail.gmail.com>
 <6cbc12a0-bf0e-0205-959e-d871eb7e7c3e@postgrespro.ru>
 <CA+TgmoZQmLhPdMGM1YA-if2=iYQdSEr9r5b=WWwmJS9FbJNx+Q@mail.gmail.com>
 <3bfa5b5d-6b3b-743d-573c-b85f933ba540@postgrespro.ru>
 <CA+TgmoaNYmuW7oUSeOviXM_8pFfgroDJbY5TXx7j=P7eXhpHqw@mail.gmail.com>
 <3c68995f-0b2e-1c24-d4f5-d2f11c9dd34f@postgrespro.ru>
 <CA+TgmoY=aP4p_AR=z4O_kfMFHoSEg19Ed+f=U+6_0W1HKfJ2-Q@mail.gmail.com>
 <d6eedce3-6b54-8167-7caa-b6b085156219@postgrespro.ru>
 <CA+TgmoamrbSB=tu2QBsUbMum52_xBrtAL8=OkFC_hqsZbEDhag@mail.gmail.com>
 <f0c8abe7-2407-513a-f901-d2de8535be85@postgrespro.ru>
 <CA+Tgmoafh-1OA+7i5ikk4ZmzQfRXx+w5dzMg5erypNop79NrHg@mail.gmail.com>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <674a6a05-5aa6-be32-7531-e1776546d277@postgrespro.ru>
Date: Fri, 7 Feb 2020 20:28:41 +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: 
 <CA+Tgmoafh-1OA+7i5ikk4ZmzQfRXx+w5dzMg5erypNop79NrHg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk


On 07.02.2020 18:15, Robert Haas wrote:
> On Wed, Feb 5, 2020 at 10:48 AM Konstantin Knizhnik
> <k.knizhnik@postgrespro.ru> wrote:
> My answer is - yes.
>> Just because:
>> - Such behavior is compatible with regular tables. So it will not
>> confuse users and doesn't require some complex explanations.
>> - It is compatible with Oracle.
>> - It is what DBA usually want when creating index.
>> -
>> There are several arguments against such behavior:
>> - Concurrent building of index in multiple sessions can consume a lot of
>> memory
>> - Building index can increase query execution time (which can be not
>> expected by clients)
> I think those are good arguments, especially the second one. There's
> no limit on how long building a new index might take, and it could be
> several minutes. A user who was running a query that could have
> completed in a few seconds or even milliseconds will be unhappy to
> suddenly wait a long time for a new index to be built. And that is an
> entirely realistic scenario, because the new index might be better,
> but only marginally.
Yes, I agree that this arguments are important.
But IMHO less important than incompatible behavior (Pavel doesn't agree 
with word "incompatible" in this context
since semantic of temp tables is in any case different with semantic of 
regular tables).

Just want to notice that if we have huge GTT (so that creation of index 
takes significant amount of time)
sequential scan of this table also will not be fast.

But in any case, if we agree that we can control thus behavior using GUC 
or index property,
then it is ok for me.


>
> Also, an important point to which I've already alluded a few times is
> that creating an index can fail. Now, one way it can fail is that
> there could be some problem writing to disk, or you could run out of
> memory, or whatever. However, it can also fail because the new index
> is UNIQUE and the data this backend has in the table doesn't conform
> to the associated constraint. It will be confusing if all access to a
> table suddenly starts complaining about uniqueness violations.

Yes, building index can fail (as any other operation with database).
What's wring with it?
If it is fatal error, then backend is terminated and content of its temp 
table is disappeared.
If it is non-fatal error, then current transaction is aborted:


Session1:
postgres=# create global temp table gtt(x integer);
CREATE TABLE
postgres=# insert into gtt values (generate_series(1,100000));
INSERT 0 100000

Session2:
postgres=# insert into gtt values (generate_series(1,100000));
INSERT 0 100000
postgres=# insert into gtt values (1);
INSERT 0 1

Session1:
postgres=# create unique index on gtt(x);
CREATE INDEX

Sessin2:
postgres=# explain select * from gtt where x=1;
ERROR:  could not create unique index "gtt_x_idx"
DETAIL:  Key (x)=(1) is duplicated.

> I don't believe that the feature you are proposing can be correctly
> implemented in 10 lines of code. I would be pleasantly surprised if it
> can be done in 1000.
>
Right now I do not see any sources of extra complexity.
Will be pleased if you can point them to me.

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