Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jux7P-0006Xh-FY for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Jul 2020 11:59:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jux7O-0005Uc-DM for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Jul 2020 11:59:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jux7O-0005Rz-1i for pgsql-hackers@lists.postgresql.org; Mon, 13 Jul 2020 11:59:34 +0000 Received: from mail-pg1-x543.google.com ([2607:f8b0:4864:20::543]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jux7L-0002AW-B2 for pgsql-hackers@postgresql.org; Mon, 13 Jul 2020 11:59:33 +0000 Received: by mail-pg1-x543.google.com with SMTP id e18so5929255pgn.7 for ; Mon, 13 Jul 2020 04:59:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=7XzNd+1surEU1iuOprX/4yjMI5sp/02dyrjIqykfiXs=; b=F1+lJY3alaxYclxss6KNbct3AMVOHeQ/me3Cg51YLOF2D1hk4kSZECBBDDjQ62SyMG sQss6G4g36AQQea3NnH6BrgmenokahAn5lyxwKWvDg6G51R4BYaU9k9myeVy6Pn+WM0w w/d66gGcR3PPo0M6LTaD9yWoVaGy6jHZv9FIq7xcy1J3/PvWXl4JnmM4zyIA1INeIi3M oOlQUigf4AwpnhqFFEuZe/KqRCZz2ahqI7ol326rdTiMfpFDMLBv0tnJS2pBNrtcurbN LUQcp+j9GE1x6vy+066adPtYCfL4CbpCqKzwjWCQS0uxGFDfc38ySDFTyOngG9dPxxV1 cuZA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=7XzNd+1surEU1iuOprX/4yjMI5sp/02dyrjIqykfiXs=; b=TYOx5nEpDDtyC3Uixbvs2/5SJSoh2Mppna9XspuP2m8EJbPG5E+UES8rPYAlIK/GsN OywHktGn0MWen3q4jeebVMZfVC/B3AhaJa1zj3rSXu98MrhTqa+1sma8hPQv/VhVRYKw HWCFGjTJfNXr5rU4xQlcbSDJnPIgCM2IsjTurZvu3BYlUaLe/BDRbUqXgX/ckYq4x2Mw GSiRc7P+gzK2yvjzQLfH3ZfRQA7OGjDNfP16zH0V3XgNHfYGFHZeZiFqkVA54CgCOExU ZSjwDUnqQv5JUflCsLlqrYXNvVHoAXEEVF8JhQaJAhsqrd5ddqphyNLa6xjPdO76Mp3P ETqw== X-Gm-Message-State: AOAM530YdHMz6yb5MwW5d7esP8KatKcpJoMVGEsXXvSIin6SCYz7PZzL JpfoVPHTYpllI9fiZXH/q0k= X-Google-Smtp-Source: ABdhPJy81ZAEkrZftiTfV5QNJHy0FSQpcF1h4aPk3AkPRPHXm6wgXr8GIW2AiT9QTvgDUrkitk4cfA== X-Received: by 2002:a63:3ec4:: with SMTP id l187mr35619675pga.371.1594641569291; Mon, 13 Jul 2020 04:59:29 -0700 (PDT) Received: from [127.0.0.1] ([47.251.3.230]) by smtp.gmail.com with ESMTPSA id c207sm14445660pfb.159.2020.07.13.04.59.26 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 13 Jul 2020 04:59:28 -0700 (PDT) From: wenjing zeng Message-Id: <352F067E-D340-4A43-A2EE-3799E79A9642@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_76C4BE22-9CDC-491A-83A2-A53A03927D98" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\)) Subject: Re: [Proposal] Global temporary tables Date: Mon, 13 Jul 2020 19:59:24 +0800 In-Reply-To: <196359A8-9363-4D87-A6B1-F52AC6FEA9D3@gmail.com> Cc: Prabhat Sahu , tushar , Robert Haas , Tomas Vondra , Konstantin Knizhnik , PostgreSQL Hackers , =?utf-8?B?IuiUoeadvumcsijlrZDlmIkpIg==?= , "Cai, Le" To: Pavel Stehule References: <24099f4e-742c-e92b-729c-77bac1ca80d4@postgrespro.ru> <77320A29-DC39-4EFE-A5E2-0A00F6A316E8@alibaba-inc.com> <7eb93026-0f35-325a-132f-ca183a5da3ac@enterprisedb.com> <7CCE7DE5-5E85-4155-9010-5EDEAE762194@alibaba-inc.com> <087bd809-e1d7-ee44-da1f-25996abad442@enterprisedb.com> <40391A22-CB8B-4143-B936-7707667F7905@alibaba-inc.com> <68FC0EB3-8E39-4300-B9ED-036F4163C791@alibaba-inc.com> <25D7E36F-B2D6-4A63-BFC7-E1FD24EA6BE0@alibaba-inc.com> <507b246f-4350-f4af-3950-a334be7c2cf2@enterprisedb.com> <06844F6A-0BD2-438E-95E5-E76E4CB5CA57@alibaba-inc.com> <750A0445-7FE0-4B4D-A319-AC1A8C7249EB@alibaba-inc.com> <9EBDB104-42CD-409E-BB27-D14AB0858AE4@alibaba-inc.com> <6FBF19E7-1C3E-4F83-AA37-778F2A285AD9@alibaba-inc.com> <318BAF11-FE5A-4CE0-9286-3695F9AD0A8A@alibaba-inc.com> <53F385F1-E0D6-4FE1-9B33-7EFDD77A1FF2@alibaba-inc.com> <196359A8-9363-4D87-A6B1-F52AC6FEA9D3@gmail.com> X-Mailer: Apple Mail (2.3608.80.23.2.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_76C4BE22-9CDC-491A-83A2-A53A03927D98 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > 2020=E5=B9=B47=E6=9C=8810=E6=97=A5 =E4=B8=8B=E5=8D=885:03=EF=BC=8Cwenjin= g zeng =E5=86=99=E9=81=93=EF=BC=9A >=20 > HI all >=20 > I started using my personal email to respond to community issue. >=20 >=20 >=20 >> 2020=E5=B9=B47=E6=9C=887=E6=97=A5 =E4=B8=8B=E5=8D=886:05=EF=BC=8CPavel = Stehule > = =E5=86=99=E9=81=93=EF=BC=9A >>=20 >> Hi >> =20 >> GTT Merge the latest PGMaster and resolves conflicts. >>=20 >>=20 >>=20 >> I tested it and it looks fine. I think it is very usable in current = form, but still there are some issues: >>=20 >> postgres=3D# create global temp table foo(a int); >> CREATE TABLE >> postgres=3D# insert into foo values(10); >> INSERT 0 1 >> postgres=3D# alter table foo add column x int; >> ALTER TABLE >> postgres=3D# analyze foo; >> WARNING: reloid 16400 not support update attstat after add colunm >> WARNING: reloid 16400 not support update attstat after add colunm >> ANALYZE > This is a limitation that we can completely eliminate. >=20 >>=20 >> Please, can you summarize what is done, what limits are there, what = can be implemented hard, what can be implemented easily? > Sure. >=20 > The current version of the GTT implementation supports all regular = table operations. > 1 what is done > 1.1 insert/update/delete on GTT. > 1.2 The GTT supports all types of indexes, and the query statement = supports the use of GTT indexes to speed up the reading of data in the = GTT. > 1.3 GTT statistics keep a copy of THE GTT local statistics, which are = provided to the optimizer to choose the best query plan. > 1.4 analyze vacuum GTT. > 1.5 truncate cluster GTT. > 1.6 all DDL on GTT. > 1.7 GTT table can use GTT sequence or Regular sequence. > 1.8 Support for creating views on GTT. > 1.9 Support for creating views on foreign key. > 1.10 support global temp partition. >=20 > I feel like I cover all the necessary GTT requirements. >=20 > For cluster GTT,I think it's complicated. > I'm not sure the current implementation is quite reasonable. Maybe you = can help review it. >=20 >=20 >>=20 >>=20 >>=20 >> I found one open question - how can be implemented table locks - = because data is physically separated, then we don't need table locks as = protection against race conditions.=20 > Yes, but GTT=E2=80=99s DML DDL still requires table locking. > 1 The DML requires table locks (RowExclusiveLock) to ensure that=20 > definitions do not change during run time (the DDL may modify or = delete them). > This part of the implementation does not actually change the code, > because the DML on GTT does not block each other between sessions. As a side note, since the same row of GTT data can not modified by = different sessions, So, I don't see the need to care the GTT's PG_class.relminmxID. What do you think? Wenjing >=20 > 2 For truncate/analyze/vacuum reinidex cluster GTT is now like DML,=20 > they only modify local data and do not modify the GTT definition. > So I lowered the table lock level held by the GTT, only need = RowExclusiveLock. >=20 > 3 For DDLs that need to be modified the GTT table definition(Drop GTT = Alter GTT),=20 > an exclusive level of table locking is required(AccessExclusiveLock),=20= > as is the case for regular table. > This part of the implementation also does not actually change the = code. >=20 > Summary: What I have done is to adjust the GTT lock levels in = different types of statements based on the above thinking. > For example, truncate GTT, I'm reducing the GTT holding table lock = level to RowExclusiveLock, > So We can truncate data in the same GTT between different sessions at = the same time. >=20 > What do you think about table locks on GTT? >=20 >=20 > Wenjing >=20 >=20 >>=20 >> Now, table locks are implemented on a global level. So exclusive lock = on GTT in one session block insertion on the second session. Is it = expected behaviour? It is safe, but maybe it is too strict.=20 >>=20 >> We should define what table lock is meaning on GTT. >>=20 >> Regards >>=20 >> Pavel >> =20 >> Pavel >>=20 >>=20 >>> With Regards, >>> Prabhat Kumar Sahu >>> EnterpriseDB: http://www.enterprisedb.com = >>=20 >>=20 >=20 --Apple-Mail=_76C4BE22-9CDC-491A-83A2-A53A03927D98 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

2020=E5=B9=B47=E6=9C=8810=E6=97=A5 =E4=B8=8B=E5=8D=885:03=EF=BC= =8Cwenjing zeng <wjzeng2012@gmail.com> =E5=86=99=E9=81=93=EF=BC=9A
<= br class=3D"Apple-interchange-newline">
HI = all

I started using my = personal email to respond to community issue.



2020=E5=B9=B47=E6=9C=887=E6=97=A5 =E4=B8=8B=E5=8D=886:05=EF=BC=8C= Pavel Stehule <pavel.stehule@gmail.com> =E5=86=99=E9=81=93=EF=BC=9A
Hi
 
GTT = Merge the latest PGMaster and resolves conflicts.


I tested it and it looks = fine. I think it is very usable in current form, but still there are = some issues:

postgres=3D# create global temp table foo(a int);
CREATE TABLE
postgres=3D# insert into foo = values(10);
INSERT 0 1
postgres=3D# alter = table foo add column x int;
ALTER TABLE
postgres=3D# analyze foo;
WARNING:  reloid = 16400 not support update attstat after add colunm
WARNING: =  reloid 16400 not support update attstat after add colunm
ANALYZE
This is a limitation that we can completely = eliminate.


Please, can you = summarize what is done, what limits are there, what can be implemented = hard, what can be implemented = easily?
Sure.

The current version of the GTT implementation = supports all regular table operations.
1 what is = done
1.1 insert/update/delete on GTT.
1.2 The GTT supports all types of indexes, and the query = statement supports the use of GTT indexes to speed up the reading of = data in the GTT.
1.3 GTT statistics keep a copy of = THE GTT local statistics, which are provided to the optimizer to choose = the best query plan.
1.4 analyze vacuum GTT.
1.5 truncate = cluster GTT.
1.6 all DDL on GTT.
1.7 GTT table can use  GTT sequence =  or Regular sequence.
1.8 Support for = creating views on GTT.
1.9 Support for creating = views on foreign key.
1.10 support global temp = partition.

I = feel like I cover all the necessary GTT requirements.

For cluster GTT,I = think it's complicated.
I'm not sure the current = implementation is quite reasonable. Maybe you can help review = it.





I found one open question - how can be implemented table = locks - because data is physically separated, then we don't need table = locks as protection against race conditions.
Yes, but = GTT=E2=80=99s DML DDL still requires table locking.
1 The DML requires table locks (RowExclusiveLock) to = ensure that 
definitions do not change during = run time (the DDL may modify or delete them).
This = part of the implementation does not actually change the code,
because the DML on GTT does not block each other between = sessions.
As a side note, = since the same row of GTT data can not modified by different = sessions,
So, I don't see the need to care the GTT's = PG_class.relminmxID.
What do you think?


Wenjing



2 = For truncate/analyze/vacuum reinidex cluster GTT is = now like DML, 
they = only modify local data and do not modify the GTT = definition.
So I lowered the table = lock level held by the GTT, only need RowExclusiveLock.

3 For DDLs = that need to be modified the GTT table definition(Drop GTT Alter = GTT), 
an exclusive = level of table locking is required(AccessExclusiveLock), 
as is the case for regular table.
This part of the implementation also does not = actually change the code.

Summary: What I have done is to = adjust the GTT lock levels in different types of statements based on the = above thinking.
For example, truncate GTT, = I'm reducing the GTT holding table lock level to = RowExclusiveLock,
So We can truncate data in the = same GTT between different sessions at the same time.

What do you think about = table locks on GTT?


Wenjing



Now, table locks are implemented on a = global level. So exclusive lock on GTT in one session block insertion on = the second session. Is it expected behaviour? It is safe, but maybe it = is too strict.

We should define what table lock is = meaning on GTT.

Regards

Pavel
 
Pavel


With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com




= --Apple-Mail=_76C4BE22-9CDC-491A-83A2-A53A03927D98--