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 1k3yiO-0004I1-CI for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Aug 2020 09:31:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k3yiN-0003b4-09 for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Aug 2020 09:31: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 1k3yiM-0003aw-LN for pgsql-hackers@lists.postgresql.org; Fri, 07 Aug 2020 09:31:02 +0000 Received: from sender4-op-o17.zoho.com ([136.143.188.17]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1k3yiJ-00044w-Ep for pgsql-hackers@postgresql.org; Fri, 07 Aug 2020 09:31:02 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1596792637; cv=none; d=zohomail.com; s=zohoarc; b=aC5mfsQUP9GmhucDkS2DJrmHjVXjy2vviV79joF74lEZplhmy6mHTT8WL7ndCWzrbSeoWRLLu9x5US1NCu9CIkuWmnD5r8pAvB11wZ4wZ7xnQsqT2E1CPTBB6kLRvfKxaliOaz089TUm7EHNokIlzOavfzXR4AwKOXZwu5m7kj8= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1596792637; h=Content-Type:Cc:Date:From:MIME-Version:Message-ID:References:Subject:To; bh=yyBWRpxRjw8dDb7gE5zPe6FXRPJvZ22ZOESGOfOq0Vo=; b=Rj/NWLJ8fu9ChH4xrhth9Zt/tWkwDTY9nQcCJvbUw4O6OA05jZnwnjR76J347iP+Q9PqVM0e+ovGomU5mcMWSl11L6MkGHLTn/apdMsCTQY5XV2GEC9/smN//atPd8c3Tp8Hlh91Jc84/U+CuLnMql1mFWSz3QPW4otiJITorrc= ARC-Authentication-Results: i=1; mx.zohomail.com; dkim=pass header.i=highgo.ca; spf=pass smtp.mailfrom=movead.li@highgo.ca; dmarc=pass header.from= header.from= DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1596792637; s=zoho; d=highgo.ca; i=movead.li@highgo.ca; h=Date:From:To:Cc:Subject:References:Mime-Version:Message-ID:Content-Type; bh=yyBWRpxRjw8dDb7gE5zPe6FXRPJvZ22ZOESGOfOq0Vo=; b=RBBXIzhVIGlJOCmMKl82qZDkM3WeWRCg4L/mem8hG2QlIea3wpAizHHTjLSZ/Uxw kl0vyTRbpbqxmsCX1TAnT1ErQOEmv53FnGKzQVlUpw1M+MwS2LUxIFr7NL7CJM1gr2W HPFkxOvEcOBXnhXLFpNpWQ1Kts8YCs/KhdpsOUN4= Received: from lchch (124.133.18.218 [124.133.18.218]) by mx.zohomail.com with SMTPS id 1596792632313645.5532295959205; Fri, 7 Aug 2020 02:30:32 -0700 (PDT) Date: Fri, 7 Aug 2020 17:30:24 +0800 From: "movead.li@highgo.ca" To: =?UTF-8?B?5pu+5paH5peM?= Cc: tushar , "Prabhat Sahu" , "Pavel Stehule" , robertmhaas , "Tomas Vondra" , "Konstantin Knizhnik" , pgsql-hackers , =?UTF-8?B?IuiUoeadvumcsijlrZDlmIkpIg==?= , =?UTF-8?B?Q2FpLCBMZQ==?= , wenjing , ahsan.hadi Subject: Re: [Proposal] Global temporary tables References: <24099f4e-742c-e92b-729c-77bac1ca80d4@postgrespro.ru>, <8517A6EA-17A6-4160-8987-B01073A0ECAF@alibaba-inc.com>, , <8BC7841E-3B32-4518-9586-D9ED571F4922@alibaba-inc.com>, , , , , , , <80DC3BA7-E5C0-43CA-A66C-2CCB45C10EA7@alibaba-inc.com>, <953705a7-52df-4b7e-a7b5-675a6303a262@enterprisedb.com>, , , , <88124F96-7519-4CD4-9011-3CDAADEB7FFA@alibaba-inc.com>, , , <2020080315092094832025@highgo.ca>, <350F7BE8-369F-45C3-AC4D-0C9313E35FA2@alibaba-inc.com> X-Priority: 3 X-GUID: 7FF9431C-D134-4175-8449-32467CADA96E X-Has-Attach: no X-Mailer: Foxmail 7.2.16.188[cn] Mime-Version: 1.0 Message-ID: <2020080717302110039054@highgo.ca> Content-Type: multipart/alternative; boundary="----=_001_NextPart881775707161_=----" X-ZohoMailClient: External List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. ------=_001_NextPart881775707161_=---- Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: base64 DQo+SSBmaW5kIHRoaXMgaXMgdGhlIG1vc3QgbGF0ZXN0IG1haWwgd2l0aCBhbiBhdHRhY2htZW50 LCBzbyBJIHRlc3QgYW5kIHJlcGx5IG9uDQo+dGhpcyB0aHJlYWQsIHNldmVyYWwgcG9pbnRzIGFz IGJlbG93Og0KDQo+MS4gSSBub3RpY2UgaXQgcHJvZHVjZXMgbmV3IHJlbGZpbGVub2RlIHdoZW4g bmV3IHNlc3Npb24gbG9naW4gYW5kIHNvbWUNCj5kYXRhIGluc2VydC4gQnV0IHRoZSByZWxmaWxl bm9kZSBjb2x1bW4gaW4gcGdfY2xhc3Mgc3RpbGwgdGhlIG9uZSB3aGVuIGNyZWF0ZQ0KPnRoZSBn bG9iYWwgdGVtcCB0YWJsZS4gSSB0aGluayB5b3UgY2FuIHRyeSB0byBzaG93IDAgaW4gdGhpcyBh cmVhIGFzIHdoYXQgbmFpbA0KPnJlbGF0aW9uIGRvZXMuIA0KPkkgdGhpbmsgZ2V0dGluZyB0aGUg R1RUIHRvIGhhdmUgYSBkZWZhdWx0IHJlbGZpbGVub2RlIGxvb2tzIGNsb3NlciB0byB0aGUgZXhp c3RpbmcgaW1wbGVtZW50YXRpb24sIGFuZCBzZXR0aW5nIGl0IHRvIDAgcmVxdWlyZXMgZXh0cmEg d29yayBhbmQgaGFzIG5vIGNsZWFyIGJlbmVmaXQuDQo+V2hhdCBkbyB5b3UgdGhpbmvvvJ8NCj5J J2QgbGlrZSB0byBrbm93IHRoZSByZWFzb25zIGZvciB5b3VyIHN1Z2dlc3Rpb24uDQpUaGUgJ3Jl bGZpbGVub2RlJyBtZWFuIHRoZSBmaWxlIG5vIG9uIGRpc2sgd2hpY2ggZGlmZmVyZW50IGZyb20g b2lkIG9mIGEgcmVsYXRpb24sDQogdGhlIGRlZmF1bHQgb25lIGlzIGEgd3JvbmcgZm9yIGd0dCwg c28gSSB0aGluayBpdCdzIG5vdCBzbyBnb29kIHRvIHNob3cgaXQgaW4gDQpwZ19jbGFzcy4NCg0K PjIuIFRoZSBuYWlsIHJlbGF0aW9ucyBoYW5kbGUgdGhlaXIgcmVsZmlsZW5vZGVzIGJ5IFJlbE1h cEZpbGUgc3RydWN0LCBhbmQgdGhpcw0KPnBhdGNoIHVzZSBoYXNoIGVudHJ5IGFuZCByZWxmaWxl bm9kZV9saXN0LCBtYXliZSBSZWxNYXBGaWxlIGFwcHJvYWNoIG1vcmUNCj51bmRlcnN0YW5kYWJs ZSBpbiBteSBvcGluaW9uLiBTb3JyeSBpZiBJIG1pc3MgdGhlIHJlYWwgZGVzaWduIGZvciB0aGF0 Lg0KPldlIGNhbiBzZWUgdGhlIFNUT1JBR0UgYW5kIHN0YXRpc3RpY3MgaW5mbyBmb3IgdGhlIEdU VCwgaW5jbHVkaW5nIHJlbGZpbGVub2RlLCB0aHJvdWdoIHZpZXcgcGdfZ3R0X3JlbHN0YXRzDQoN CnBvc3RncmVzPSMgXGQgZ3R0DQogICAgICAgICAgICAgICAgVGFibGUgInB1YmxpYy5ndHQiDQog Q29sdW1uIHwgIFR5cGUgICB8IENvbGxhdGlvbiB8IE51bGxhYmxlIHwgRGVmYXVsdCANCi0tLS0t LS0tKy0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0tLS0tLS0tKy0tLS0tLS0tLQ0KIGEgICAgICB8 IGludGVnZXIgfCAgICAgICAgICAgfCAgICAgICAgICB8IA0KIGIgICAgICB8IGludGVnZXIgfCAg ICAgICAgICAgfCAgICAgICAgICB8IA0KDQpwb3N0Z3Jlcz0jIGluc2VydCBpbnRvIGd0dCB2YWx1 ZXMoMSwxKTsNCklOU0VSVCAwIDENCnBvc3RncmVzPSMgc2VsZWN0ICogZnJvbSBwZ19ndHRfcmVs c3RhdHMgOw0KIHNjaGVtYW5hbWUgfCB0YWJsZW5hbWUgfCByZWxmaWxlbm9kZSB8IHJlbHBhZ2Vz IHwgcmVsdHVwbGVzIHwgcmVsYWxsdmlzaWJsZSB8IHJlbGZyb3plbnhpZCB8IHJlbG1pbm14aWQg DQotLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tKy0tLS0t LS0tLS0tKy0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0NCiBwdWJs aWMgICAgIHwgZ3R0ICAgICAgIHwgICAgICAgMTYzODQgfCAgICAgICAgMCB8ICAgICAgICAgMCB8 ICAgICAgICAgICAgIDAgfCAgICAgICAgICA1MzIgfCAgICAgICAgICAxDQooMSByb3cpDQoNCnBv c3RncmVzPSMgdHJ1bmNhdGUgZ3R0Ow0KVFJVTkNBVEUgVEFCTEUNCnBvc3RncmVzPSMgc2VsZWN0 ICogZnJvbSBwZ19ndHRfcmVsc3RhdHMgOw0KIHNjaGVtYW5hbWUgfCB0YWJsZW5hbWUgfCByZWxm aWxlbm9kZSB8IHJlbHBhZ2VzIHwgcmVsdHVwbGVzIHwgcmVsYWxsdmlzaWJsZSB8IHJlbGZyb3pl bnhpZCB8IHJlbG1pbm14aWQgDQotLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0t LSstLS0tLS0tLS0tKy0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLSst LS0tLS0tLS0tLS0NCiBwdWJsaWMgICAgIHwgZ3R0ICAgICAgIHwgICAgICAgMTYzODcgfCAgICAg ICAgMCB8ICAgICAgICAgMCB8ICAgICAgICAgICAgIDAgfCAgICAgICAgICA1MzMgfCAgICAgICAg ICAxDQooMSByb3cpDQoNCg0KSSBqdXN0IHN1Z2dlc3QgYSB3YXkgd2hpY2ggbWF5YmUgbW9zdCBu YXR1cmVseSB0byB0aGUgZXhpc3QgY29kZSBzdHJ1Y3QsIGFuZCBpdCdzDQp1byB0byB5b3UuDQoN Cg0KPjMuIEkgZ2V0IGEgd3JvbmcgcmVzdWx0IG9mIHBnX3JlbGF0aW9uX2ZpbGVwYXRoKCkgZnVu Y3Rpb24gZm9yIGdsb2JhbCB0ZW1wIHRhYmxlLA0KPkkgdGhpbmsgaXQncyBuZWNlc3Nhcnl0byBr ZWVwIHRoaXMgYW4gY29ycmVjdCBvdXRwdXQuDQoNCnBvc3RncmVzPSMgc2VsZWN0IHBnX3JlbGF0 aW9uX2ZpbGVwYXRoKG9pZCkgZnJvbSBwZ19jbGFzcyB3aGVyZSByZWxuYW1lID0gJ2d0dCc7DQog cGdfcmVsYXRpb25fZmlsZXBhdGggDQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQogYmFzZS8xMzgz NS90M18xNjM4NA0KKDEgcm93KQ0KDQpJIGRpZG4ndCBmaW5kIGFueXRoaW5nIHdyb25nLiBDb3Vs ZCB5b3UgcGxlYXNlIGdpdmUgbWUgYSBkZW1vLg0KDQpJbiBteSBvcGlub2luIGl0IHNob3VsZCBz aG93ICdiYXNlLzEzODM1L3QzXzE2Mzg3Jywgb3RoZXIgdGhhbiAnYmFzZS8xMzgzNS90M18xNjM4 NCcsDQpiZWNhdXNlIHRoZSByZWxmaWxlbm9kZSBjaGFuZ2UgdG8gMTYzODcgd2hlbiB5b3UgdHJ1 bmNhdGUgaXQgaW4gc3RlcCAyLg0KDQo+NC4gSW4gZ3R0X3NlYXJjaF9ieV9yZWxpZCgpIGZ1bmN0 aW9uLCBpdCBoYXMgbm90IGhhbmRsZSB0aGUgbWlzc2luZ19vayBhcmd1bWVudA0KPmlmIGd0dF9z dG9yYWdlX2xvY2FsX2hhc2ggaXMgbnVsbC4gVGhlcmUgc2hvdWxkIGJlIHNvbWUgY29tbWVudHMg aWYgaXQncyB0aGUgcmlnaHQNCj5jb2RlLg0KPlRoaXMgaXMgYSBwcm9ibGVtIHRoYXQgaGFzIGJl ZW4gZml4ZWQgaW4gZ2xvYmFsX3RlbXBvcmFyeV90YWJsZV92MzQtcGcxMy5wYXRjaC4NClNvcnJ5 IGFib3V0IGl0LCBJIGNhbiBub3QgZmluZCBpdCBpbiBtYWlsIHRocmVhZCBhbmQgbWF5YmUgSSBt aXNzIHNvbWV0aGluZy4gVGhlIG1haWwgdGhyZWFkDQppcyBzbyBsb25nLCBpdCdzIGJldHRlciB0 byBjcmVhdGUgYSBuZXcgbWFpbCB0aHJlYWQgSSB0aGluay4NCg0KDQoNClJlZ2FyZHMsDQpIaWdo Z28gU29mdHdhcmUgKENhbmFkYS9DaGluYS9QYWtpc3RhbikgDQpVUkwgOiB3d3cuaGlnaGdvLmNh IA0KRU1BSUw6IG1haWx0bzptb3ZlYWQoZG90KWxpKGF0KWhpZ2hnbyhkb3QpY2ENCg== ------=_001_NextPart881775707161_=---- Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable = =0A

>I find this is the mo= st latest mail with an attachment, so I test and reply on
>this thread, several points as below:

>1. I notice it produces new relfilenod= e when new session login and some
>data insert. Bu= t the relfilenode column in pg_class still the one when create
>the global temp table. I think you can try to show 0 in this= area as what nail
>relation does. 
>I think getting the GTT to have a default relfile= node looks closer to the existing implementation, and setting it to 0 requ= ires extra work and has no clear benefit.
>What do you think= =EF=BC=9F
>I'd like to know the reasons for your suggestion.<= /div>
The 'relfilenode' mean the file no on disk whic= h different from oid of a relation,
 the defaul= t one is a wrong for gtt, so I think it's not so good to show it in <= /span>
pg_class.

>2. The nail relations handle their relfilenodes= by RelMapFile struct, and this
>patch use hash en= try and relfilenode_list, maybe RelMapFile approach more
>understandable in my opinion. Sorry if I miss the real design fo= r that.
>We can see the STORAGE and = statistics info for the GTT, including relfilenode, through view pg_gtt_re= lstats

postgres=3D# \d gtt
                Table= "public.gtt"
 = ;Column |  Type   | Collation | Nullable | Defaul= t 
--------+--= -------+-----------+----------+---------
 a      | integer | &n= bsp;         |         &= nbsp;| 
 = b      | integer |         &n= bsp; |          | 

postgres=3D# insert= into gtt values(1,1);
INSERT 0 1
post= gres=3D# select * from pg_gtt_relstats ;
 schemaname | tablename | relfilenode | relpage= s | reltuples | relallvisible | relfrozenxid | relminmxid 
------------+-----------+----= ---------+----------+-----------+---------------+--------------+----------= --
 public&nbs= p;    | gtt       |   &n= bsp;   16384 |        0 |   &= nbsp;     0 |            = ; 0 |          532 |     = ;     1
(1 row)

postgres=3D# truncate gtt;
TRUNCATE TABLE
postgres=3D# select * from pg_gtt_relstats ;
 schemaname | tablename | relfi= lenode | relpages | reltuples | relallvisible | relfrozenxid | relminmxid&= nbsp;
------------+= -----------+-------------+----------+-----------+---------------+---------= -----+------------
=  public     | gtt       = |       16387 |        0= |         0 |       &nb= sp;     0 |          533 |&nb= sp;         1
(1 row)


I j= ust suggest a way which maybe most naturely to the exist code struct, and = it's
uo to you.


>3. I get a wrong result of p= g_relation_filepath() function for global temp table,
>I think it's necessaryto keep this an correct output.
<= /div>

postgres=3D# select pg_relation_filepath(oid) from= pg_class where relname =3D 'gtt';
 pg_relation_filepath 
----------------------
 base/13835/t3_16384
(1 row)

I didn't find any= thing wrong. Could you please give me a demo.

In my opinoin it should = show 'base/13835/t3_16387', other than 'base/13835/t3_16384',
= because the relfileno= de change to 16387 when you truncate it in step 2.

>4. In gtt_sea= rch_by_relid() function, it has not handle the missing_ok argument
>if gtt_storage_local_hash is null. There should be= some comments if it's the right
= >code.
>This is a problem that has been fixed in glo= bal_temporary_table_v34-pg13.patch.
Sorry about it, I can not fi= nd it in mail thread and maybe I miss something. The mail thread
is so long, it's better to create a new mail thread I think.

=0A
=
<= span style=3D"color: rgb(13, 10, 11); font-family: monospace, monospace; f= ont-size: 10.7333px; font-variant-ligatures: normal; orphans: 2; white-spa= ce: pre-wrap; widows: 2;">Regards,
Highgo = Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mail= to:movead(dot)li(at)highgo(dot)ca
=0A ------=_001_NextPart881775707161_=------