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 1jJTy4-0003jU-Ll for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Apr 2020 03:23:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jJTy3-00063y-Ie for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Apr 2020 03:23:03 +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 1jJTy2-00060m-FL for pgsql-hackers@lists.postgresql.org; Wed, 01 Apr 2020 03:23:03 +0000 Received: from out0-129.mail.aliyun.com ([140.205.0.129]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJTxx-0002hB-Su for pgsql-hackers@postgresql.org; Wed, 01 Apr 2020 03:23:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=alibaba-inc.com; s=default; t=1585711372; h=From:Message-Id:Content-Type:Mime-Version:Subject:Date:To; bh=52zhuUFDFGkuiVw2Fvc3cP/8GfrE+jxczT2jqkuRC3c=; b=p1x2zeST+wU9sDCjpVqK4KwVY3Zr/FvPOZbEAQqkOwmGmovvz/lHDhVDbEzIndhfX/kHqZ7YFS2DA4kbjepQWDSWHIVErIG48o9awFPqUE9Z8Eyzx2ueiVze7nRC58wMwvPMPH2OzX34F2/zYrGpM4NGV2UIwoUhX/zWRG+nMKQ= X-Alimail-AntiSpam: AC=PASS;BC=-1|-1;BR=01201311R851e4;CH=green;DM=||false|;DS=||;FP=0|-1|-1|-1|0|-1|-1|-1;HT=e01a16367;MF=wenjing.zwj@alibaba-inc.com;NM=1;PH=DS;RN=10;SR=0;TI=SMTPD_---.H8YrQxZ_1585711369; Received: from 30.5.97.196(mailfrom:wenjing.zwj@alibaba-inc.com fp:SMTPD_---.H8YrQxZ_1585711369) by smtp.aliyun-inc.com(127.0.0.1); Wed, 01 Apr 2020 11:22:50 +0800 From: =?utf-8?B?5pu+5paH5peM?= Message-Id: Content-Type: multipart/signed; boundary="Apple-Mail=_A45DCBDB-9E6A-4B91-AC26-39702449A3EF"; protocol="application/pkcs7-signature"; micalg=sha-256 Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\)) Subject: Re: [Proposal] Global temporary tables Date: Wed, 1 Apr 2020 11:22:49 +0800 In-Reply-To: Cc: tushar , Pavel Stehule , Robert Haas , Tomas Vondra , Konstantin Knizhnik , PostgreSQL Hackers , =?utf-8?B?IuiUoeadvumcsijlrZDlmIkpIg==?= , "Cai, Le" , =?utf-8?B?IuiQp+WwkeiBqijpk4HlurUpIg==?= To: Prabhat Sahu 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> 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=_A45DCBDB-9E6A-4B91-AC26-39702449A3EF Content-Type: multipart/alternative; boundary="Apple-Mail=_86BE610C-C706-4AB6-AF94-23752F9936C9" --Apple-Mail=_86BE610C-C706-4AB6-AF94-23752F9936C9 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > 2020=E5=B9=B43=E6=9C=8831=E6=97=A5 =E4=B8=8B=E5=8D=889:59=EF=BC=8CPrabha= t Sahu =E5=86=99=E9=81=93=EF=BC=9A >=20 > Hi Wenjing, > Thanks for the new patch. > I saw with the patch(gtt_v23.patch), we are supporting the new concept = "global temporary sequence"(i.e. session-specific sequence), is this = intentional? It was supported in earlier versions, This causes the sequence built into the GTT to automatically become a = "global temp sequence", Such as create global temp table (a serial); Like GTT, the global temp sequnce is used individually for each session. Recently, I added the global temp sequence syntax so that it can be = created independently. The purpose of this is to enable such sequence built into the GTT to = support pg_dump and pg_restore. Wenjing >=20 > postgres=3D# create global temporary sequence gt_seq; > CREATE SEQUENCE > postgres=3D# create sequence seq; > CREATE SEQUENCE > postgres=3D# \d+ > List of relations > Schema | Name | Type | Owner | Persistence | Size | = Description=20 > = --------+--------+----------+-------+-------------+------------+----------= --- > public | gt_seq | sequence | edb | session | 8192 bytes |=20 > public | seq | sequence | edb | permanent | 8192 bytes |=20 > (2 rows) >=20 > postgres=3D# select nextval('gt_seq'), nextval('seq'); > nextval | nextval=20 > ---------+--------- > 1 | 1 > (1 row) >=20 > postgres=3D# select nextval('gt_seq'), nextval('seq'); > nextval | nextval=20 > ---------+--------- > 2 | 2 > (1 row) >=20 > -- Exit and re-connect to psql prompt: > postgres=3D# \q > [edb@localhost bin]$ ./psql postgres=20 > psql (13devel) > Type "help" for help. >=20 > postgres=3D# select nextval('gt_seq'), nextval('seq'); > nextval | nextval=20 > ---------+--------- > 1 | 3 > (1 row) >=20 > postgres=3D# select nextval('gt_seq'), nextval('seq'); > nextval | nextval=20 > ---------+--------- > 2 | 4 > (1 row) >=20 > On Tue, Mar 31, 2020 at 9:46 AM =E6=9B=BE=E6=96=87=E6=97=8C = > = wrote: >=20 >=20 >> 2020=E5=B9=B43=E6=9C=8827=E6=97=A5 =E4=B8=8B=E5=8D=885:21=EF=BC=8Ctusha= r > = =E5=86=99=E9=81=93=EF=BC=9A >>=20 >> On 3/27/20 10:55 AM, =E6=9B=BE=E6=96=87=E6=97=8C wrote: >>>> Hi Wenjing, >>>> This patch(gtt_v21_pg13.patch) is not applicable on PG HEAD, I hope = you have prepared the patch on top of some previous commit.=20 >>>> Could you please rebase the patch which we can apply on HEAD ? >>> Yes, It looks like the built-in functions are in conflict with new = code. >>>=20 >>>=20 >> This error message looks wrong to me- >>=20 >> postgres=3D# reindex table concurrently t ; >> ERROR: cannot create indexes on global temporary tables using = concurrent mode >> postgres=3D#=20 >>=20 >> Better message would be- >>=20 >> ERROR: cannot reindex global temporary tables concurrently >>=20 > I found that the local temp table automatically disables concurrency = mode. > so, I made some improvements, The reindex GTT behaves the same as the = local temp table. >=20 >=20 > Wenjing >=20 >=20 >=20 >>=20 >> --=20 >> regards,tushar >> EnterpriseDB https://www.enterprisedb.com/ = >> The Enterprise PostgreSQL Company >=20 >=20 >=20 > --=20 > With Regards, > Prabhat Kumar Sahu > EnterpriseDB: http://www.enterprisedb.com = --Apple-Mail=_86BE610C-C706-4AB6-AF94-23752F9936C9 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

2020=E5=B9=B43=E6=9C=8831=E6=97=A5 =E4=B8=8B=E5=8D=889:59=EF=BC= =8CPrabhat Sahu <prabhat.sahu@enterprisedb.com> =E5=86=99=E9=81=93=EF=BC=9A=

Hi = Wenjing,
Thanks for the new patch.
I saw with the = patch(gtt_v23.patch), we are supporting the new concept "global = temporary sequence"(i.e. session-specific sequence), is this = intentional?
It was supported in = earlier versions,
This causes the sequence built into the = GTT to automatically become a "global temp = sequence",
Such as create global temp table = (a serial);
Like GTT, = the global temp sequnce is used individually for each = session.

Recently, I added the = global temp sequence syntax so that it can be created = independently.
The purpose of this is to enable = such sequence built into the GTT to support = pg_dump and pg_restore.


Wenjing



postgres=3D# create global temporary = sequence gt_seq;
CREATE SEQUENCE
postgres=3D# create sequence seq;
CREATE = SEQUENCE
postgres=3D# \d+
    =                     =       List of relations
 Schema | =  Name  |   Type   | Owner | Persistence |   =  Size    | Description
--------+--------+----------+-------+-------------+------------= +-------------
 public | gt_seq | = sequence | edb   | session     | 8192 bytes |
 public | seq    | sequence | edb   | = permanent   | 8192 bytes |
(2 rows)

postgres=3D# select nextval('gt_seq'), = nextval('seq');
 nextval | nextval
---------+---------
       1 |       1
(1 row)

postgres=3D# select nextval('gt_seq'), = nextval('seq');
 nextval | nextval
---------+---------
      =  2 |       2
(1 = row)

-- Exit and = re-connect to psql prompt:
postgres=3D# \q
[edb@localhost= bin]$ ./psql postgres
psql (13devel)
Type = "help" for help.

postgres=3D# select = nextval('gt_seq'), nextval('seq');
 nextval | nextval =
---------+---------
       1 |       3
(1 row)

postgres=3D# select nextval('gt_seq'), = nextval('seq');
 nextval | nextval
---------+---------
      =  2 |       4
(1 = row)

On Tue, Mar = 31, 2020 at 9:46 AM =E6=9B=BE=E6=96=87=E6=97=8C <wenjing.zwj@alibaba-inc.com> wrote:


2020=E5=B9=B43=E6=9C=8827=E6=97=A5 =E4=B8=8B=E5=8D=885:21=EF=BC= =8Ctushar <tushar.ahuja@enterprisedb.com> = =E5=86=99=E9=81=93=EF=BC=9A

=20 =20 =20
On 3/27/20 10:55 AM, =E6=9B=BE=E6=96=87=E6=97=8C = wrote:
Hi Wenjing,
This patch(gtt_v21_pg13.patch) is not applicable on PG HEAD, I hope you have prepared the patch on top of some previous commit.
Could you please rebase the patch which we can apply on HEAD ?
Yes, It looks like the built-in functions are = in conflict with new code.


This error message looks wrong  to = me-

postgres=3D# reindex table concurrently t ;
ERROR:  cannot create indexes on global temporary tables = using concurrent mode
postgres=3D# 

Better message would be-

ERROR:  cannot reindex global temporary tables = concurrently

I = found that the local temp table automatically disables concurrency = mode.
so, I made some improvements, The reindex GTT = behaves the same as the local temp table.


Wenjing




--=20
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company



--

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


= --Apple-Mail=_86BE610C-C706-4AB6-AF94-23752F9936C9-- --Apple-Mail=_A45DCBDB-9E6A-4B91-AC26-39702449A3EF Content-Disposition: attachment; filename=smime.p7s Content-Type: application/pkcs7-signature; name=smime.p7s Content-Transfer-Encoding: base64 MIAGCSqGSIb3DQEHAqCAMIACAQExDzANBglghkgBZQMEAgEFADCABgkqhkiG9w0BBwEAAKCCBusw ggM6MIICo6ADAgECAgEDMA0GCSqGSIb3DQEBBQUAMIGQMSYwJAYJKoZIhvcNAQkBFhdhbGlsYW5n QGFsaWJhYmEtaW5jLmNvbTETMBEGA1UEAwwKQWxpbGFuZyBDQTEMMAoGA1UECwwDSW5jMRAwDgYD VQQKDAdBbGliYWJhMREwDwYDVQQHDAhIYW5nWmhvdTERMA8GA1UECAwIWmhlSmlhbmcxCzAJBgNV BAYTAkNOMB4XDTE0MDMyMTE4MjY0OFoXDTM0MDMxNjE4MjY0OFowPzEdMBsGA1UEAwwUQWxpbGFu ZyBDbGFzcyAzIFJvb3QxDDAKBgNVBAsMA0luYzEQMA4GA1UECgwHQWxpYmFiYTCBnzANBgkqhkiG 9w0BAQEFAAOBjQAwgYkCgYEA5EmVMYbK8q7pcO0oTDWAbhetfpZGE9f8nZgF8jMjF4UhmiiaOqJ6 nbovFt1/E2eOBOWTBDDN6029KN/rejdhvarLW6X5v8J8ZTbHRSgWQEKbE6e2Cg2lRqNYjIq+jROV V7ZWXGZX3Vy53ABsRiAyE3+vu571llNnkAgHXhh+PosCAwEAAaOB8zCB8DAdBgNVHQ4EFgQUvBg5 59PY0tRSklvSA+Es4n8PWzUwgb0GA1UdIwSBtTCBsoAUbEKnBvQw5utZdaHNpiloheRi1K2hgZak gZMwgZAxJjAkBgkqhkiG9w0BCQEWF2FsaWxhbmdAYWxpYmFiYS1pbmMuY29tMRMwEQYDVQQDDApB bGlsYW5nIENBMQwwCgYDVQQLDANJbmMxEDAOBgNVBAoMB0FsaWJhYmExETAPBgNVBAcMCEhhbmda aG91MREwDwYDVQQIDAhaaGVKaWFuZzELMAkGA1UEBhMCQ06CAQEwDwYDVR0TAQH/BAUwAwEB/zAN BgkqhkiG9w0BAQUFAAOBgQAH5MIMHpvYGsFG+8FOKIhsNmgsOkGyoH0gb/g4+kYgOAic7VtCBeoO nETPOVL+vgKBsgteMfO9VCQRzbe+WQYEsr2aV7tp9wS0Jk3t4EDV7IqTrwkpTNlffFPylSkYFIM2 +nTQ3YzM7/chCBrmq/c25ORkQw0kEfqkzPmvItrphTCCA6kwggMSoAMCAQICBgFw5ynKKDANBgkq hkiG9w0BAQUFADA/MR0wGwYDVQQDDBRBbGlsYW5nIENsYXNzIDMgUm9vdDEMMAoGA1UECwwDSW5j MRAwDgYDVQQKDAdBbGliYWJhMB4XDTIwMDMxNjA2MjQwMFoXDTI1MDMxNTA2MjQwMFowgdoxKjAo BgkqhkiG9w0BCQEWG3dlbmppbmcuendqQGFsaWJhYmEtaW5jLmNvbTEOMAwGA1UEAwwFNjc4NjIx STBHBgNVBAwMQDY3ODYyfE1ETVNFUlZFUkY5N0U5RkQwLTlCQzMtNTBCMy1BMjhCLTM4MUZDNjlE QzQ0OXxmOGZmYzI1OWNmYTYxDDAKBgNVBAsMA0luYzEQMA4GA1UECgwHQWxpYmFiYTERMA8GA1UE BwwISGFuZ1pob3UxETAPBgNVBAgMCFpoZUppYW5nMQswCQYDVQQGEwJDTjCBnzANBgkqhkiG9w0B AQEFAAOBjQAwgYkCgYEAotJNaw65iOpWLohS/RrJfYTgodgDGOfiHHVQAk/NBc8ZFV1x1bPglLGv 64RbK0IxGUgLbf94UrNFEoXAZn0rbgMnqK285bNw5lb1lCI16Hd8seTEx+32ePFVjupY5jap70X4 L6z6bz/dwPUA6bcU0BHmkALremGnUN8Ep2hJXaUCAwEAAaOCARIwggEOMA8GBikBAQIEHgQFBANv c3gwEQYGKQEBAgseBAcEBTY3ODYyMDkGBikBAQIMHgQvBC1NRE1TRVJWRVJGOTdFOUZEMC05QkMz LTUwQjMtQTI4Qi0zODFGQzY5REM0NDkwGAYGKQEBAg0eBA4EDGY4ZmZjMjU5Y2ZhNjBFBgNVHSUE PjA8BggrBgEFBQcDAQYIKwYBBQUHAwIGCCsGAQUFBwMEBggrBgEFBQcDDQYIKwYBBQUHAw4GCCsG AQUFBwMRMAwGA1UdDwQFAwMH/4AwHQYDVR0OBBYEFFsfN1W/DAI3pTtvpex37Ekokt2NMB8GA1Ud IwQYMBaAFLwYOefT2NLUUpJb0gPhLOJ/D1s1MA0GCSqGSIb3DQEBBQUAA4GBABOVGBHKOgUsQoby QhZ0wN1Flb8v+pG843bMnPufPL51ugsw/MbQBLXyZc9ji2ZPQwtjszPyywjNXKnCryZ1Fvg6QcOH NYZv2UJf3+Ahae0EVMgLuGOjL4OA5Kvuf2IbBm4VqZE1b8yUmgeSsO/2Vo7Fe+eiXM/vy5Ip74HR Kp7CMYICFjCCAhICAQEwSTA/MR0wGwYDVQQDDBRBbGlsYW5nIENsYXNzIDMgUm9vdDEMMAoGA1UE CwwDSW5jMRAwDgYDVQQKDAdBbGliYWJhAgYBcOcpyigwDQYJYIZIAWUDBAIBBQCgggEfMBgGCSqG SIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTIwMDQwMTAzMjI0OVowLwYJKoZI hvcNAQkEMSIEIH2ZN7ZEgeofpMWaw+wdVUwABEFqn8O2sFxG+may8hP6MFgGCSsGAQQBgjcQBDFL MEkwPzEdMBsGA1UEAwwUQWxpbGFuZyBDbGFzcyAzIFJvb3QxDDAKBgNVBAsMA0luYzEQMA4GA1UE CgwHQWxpYmFiYQIGAXDnKcooMFoGCyqGSIb3DQEJEAILMUugSTA/MR0wGwYDVQQDDBRBbGlsYW5n IENsYXNzIDMgUm9vdDEMMAoGA1UECwwDSW5jMRAwDgYDVQQKDAdBbGliYWJhAgYBcOcpyigwDQYJ KoZIhvcNAQEBBQAEgYADZGBuMXkqoBZwEHnLh/56UXFgByn7/lewg+RwV35649pMLZJ+h5vs55eH UKBOEObYNmI2gFZ5iiOIo7giokJrDU+vLl4iCSUhnMRxw5dLsKxx7Xgb8WpbJ6cWWiXwbXyx0S5C KiwL1SoWzWzLfx81H2UBDGt/NqOwFIVAA8NyAgAAAAAAAA== --Apple-Mail=_A45DCBDB-9E6A-4B91-AC26-39702449A3EF--