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 1ix5SW-0005nV-85 for pgsql-hackers@arkaria.postgresql.org; Thu, 30 Jan 2020 08:45:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ix5SU-0003c4-S9 for pgsql-hackers@arkaria.postgresql.org; Thu, 30 Jan 2020 08:45:54 +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 1ix5SU-0003XR-Be for pgsql-hackers@lists.postgresql.org; Thu, 30 Jan 2020 08:45:54 +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 1ix5SD-0004MN-VT for pgsql-hackers@postgresql.org; Thu, 30 Jan 2020 08:45:53 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id B4AA621C57E4; Thu, 30 Jan 2020 11:45:35 +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 5D92B21C57E1; Thu, 30 Jan 2020 11:45:35 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1580373935; bh=J7TxEly2sRDaCrH0mskM2S0NMFWIGgXAzGPofOSSzlo=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=bKaGrpVZF2FcWh50131A16trIK/ng/pJdcgXJa8wHNs39UlVfaErtwhT7gxX1BS3R RPv0kkL7udrSk3QLqR33xjGz1LYDJdJuGM1ClLBsBRoXHLBwc/1vN9Yj8bn9LfVJ4k oYNxaGD+mp7Qr2+3BVYwUwT2QasXMjVaK8zkBJZ8= 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> <7dd64333-3b6f-5aae-20ac-5fde142902e1@postgrespro.ru> <2fd087e8-b03a-5996-e077-df010104177c@postgrespro.ru> <0bddbc6d-fed1-d56b-d39f-67352461b2d1@postgrespro.ru> <407d6499-950d-e3d9-acbc-a2e86d8b82a3@postgrespro.ru> <6cbc12a0-bf0e-0205-959e-d871eb7e7c3e@postgrespro.ru> <2e7fc7af-34f7-f707-5100-2c3d24128aee@postgrespro.ru> From: Konstantin Knizhnik Message-ID: <60ad936e-23d1-a46d-2783-5652040d7dbe@postgrespro.ru> Date: Thu, 30 Jan 2020 11:45:35 +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="------------FD69A7A7C20CBAF5DCD2D4FF" 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. --------------FD69A7A7C20CBAF5DCD2D4FF Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 29.01.2020 20:37, Pavel Stehule wrote: > > > st 29. 1. 2020 v 18:21 odesílatel Konstantin Knizhnik > > napsal: > > > > On 29.01.2020 20:08, Pavel Stehule wrote: >> >> >> >> 2. Actually I do not propose some completely new approach. I >> try to >> provide behavior with is compatible with regular tables. >> If you create index for regular table, then it can be used in >> all >> sessions, right? >> >> >> I don't understand to this point. Regular tables shares data, >> shares files. You cannot to separate it. More - you have to uses >> relatively aggressive locks to be this operation safe. >> >> Nothing from these points are valid for GTT. > > GTT shares metadata. > As far as them are not sharing data, then GTT are safer than > regular table, aren't them? > "Safer" means that we need less "aggressive" locks for them: we > need to protect only metadata, not data itself. > > My point is that if we allow other sessions to access created > indexes for regular tables, then it will be not more complex to > support it for GTT. > Actually "not more complex" in this case means "no extra efforts > are needed". > > > It is hard to say. I see a significant difference. When I do index on > regular table, then I don't change a context of other processes. I > have to wait for lock, and after I got a lock then other processes > waiting. > > With GTT, I don't want to wait for others - and other processes should > build indexes inside - without expected sequence of operations. Maybe > it can have positive effect, but it can have negative effect too. In > this case I prefer (in this moment) zero effect on other sessions. So > I would to build index in my session and I don't would to wait for > other sessions, and if it is possible other sessions doesn't need to > interact or react on my action too. It should be independent what is > possible. The most simple solution is request on unique usage. I > understand so it can be not too practical. Better is allow to usage > GTT by other tables, but the changes are invisible in other sessions > to session reset. It is minimalistic strategy. It has not benefits for > other sessions, but it has not negative impacts too. > Building regular index requires two kinds of lock: 1. You have to lock pg_class to make changes in system catalog. 2. You need to lock heap relation  to pervent concurrent updates while building index. GTT requires 1)  but not 2). Once backend inserts information about new index in system catalog, all other sessions may use it. pg_class lock prevents any race condition here. And building index itself doesn't affect any other backends. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------FD69A7A7C20CBAF5DCD2D4FF Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 29.01.2020 20:37, Pavel Stehule wrote:


st 29. 1. 2020 v 18:21 odesílatel Konstantin Knizhnik <k.knizhnik@postgrespro.ru> napsal:


On 29.01.2020 20:08, Pavel Stehule wrote:



2. Actually I do not propose some completely new approach. I try to
provide behavior with is compatible with regular tables.
If you create index for regular table, then it can be used in all
sessions, right?

I don't understand to this point. Regular tables shares data, shares files. You cannot to separate it. More - you have to uses relatively aggressive locks to be this operation safe.

Nothing from these points are valid for GTT.

GTT shares metadata.
As far as them are not sharing data, then GTT are safer than regular table, aren't them?
"Safer" means that we need less "aggressive" locks for them: we need to protect only metadata, not data itself.

My point is that if we allow other sessions to access created indexes for regular tables, then it will be not more complex to support it for GTT.
Actually "not more complex" in this case means "no extra efforts are needed".

It is hard to say. I see a significant difference. When I do index on regular table, then I don't change a context of other processes. I have to wait for lock, and after I got a lock then other processes waiting.

With GTT, I don't want to wait for others - and other processes should build indexes inside - without expected sequence of operations. Maybe it can have positive effect, but it can have negative effect too. In this case I prefer (in this moment) zero effect on other sessions. So I would to build index in my session and I don't would to wait for other sessions, and if it is possible other sessions doesn't need to interact or react on my action too. It should be independent what is possible. The most simple solution is request on unique usage. I understand so it can be not too practical. Better is allow to usage GTT by other tables, but the changes are invisible in other sessions to session reset. It is minimalistic strategy. It has not benefits for other sessions, but it has not negative impacts too.


Building regular index requires two kinds of lock:
1. You have to lock pg_class to make changes in system catalog.
2. You need to lock heap relation  to pervent concurrent updates while building index.

GTT requires 1)  but not 2).
Once backend inserts information about new index in system catalog, all other sessions may use it. pg_class lock prevents any race condition here.
And building index itself doesn't affect any other backends.


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