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 1j3LeO-00008K-Qo for pgsql-hackers@arkaria.postgresql.org; Sun, 16 Feb 2020 15:16:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j3LeN-0007gI-Es for pgsql-hackers@arkaria.postgresql.org; Sun, 16 Feb 2020 15:16:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j3LeN-0007gB-4k for pgsql-hackers@lists.postgresql.org; Sun, 16 Feb 2020 15:16:03 +0000 Received: from out0-133.mail.aliyun.com ([140.205.0.133]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j3LeF-00085z-86 for pgsql-hackers@postgresql.org; Sun, 16 Feb 2020 15:16:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=alibaba-inc.com; s=default; t=1581866149; h=From:Message-Id:Content-Type:Mime-Version:Subject:Date:To; bh=2scXcXIUDgcxpVjFV7THzPA1pQVoiVe58WEoHApjmko=; b=bYIKuBo8nDObXc3b/QelKQGnKif3c1DUs2OwKVtmPCKjJ1Y+B1hkFkzfFTjw88kNOrUuwwcnCykiaUJUJ1dpQMxJDyzluqEQGRtrwkrfM3CD52CQYvvbEycJdM57JLRCxBPfayf92i9iCm8F3wS626N/quw0SJSpjCCTeCIPSo0= X-Alimail-AntiSpam: AC=PASS;BC=-1|-1;BR=01201311R101e4;CH=green;DM=||false|;DS=||;FP=0|-1|-1|-1|0|-1|-1|-1;HT=e02c03312;MF=wenjing.zwj@alibaba-inc.com;NM=1;PH=DS;RN=8;SR=0;TI=SMTPD_---.GogoHVx_1581866145; Received: from 30.15.209.233(mailfrom:wenjing.zwj@alibaba-inc.com fp:SMTPD_---.GogoHVx_1581866145) by smtp.aliyun-inc.com(127.0.0.1); Sun, 16 Feb 2020 23:15:46 +0800 From: "=?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?=" Message-Id: <35A2DF75-05A8-4F8D-9D43-A930E2ABDFDD@alibaba-inc.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_0B5BAAEC-F1C5-4825-AEA7-E6DD8D491457" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\)) Subject: Re: [Proposal] Global temporary tables Date: Sun, 16 Feb 2020 23:16:19 +0800 In-Reply-To: Cc: "Robert Haas" , "Tomas Vondra" , "Konstantin Knizhnik" , "PostgreSQL Hackers" , "=?UTF-8?B?6JSh5p2+6ZyyKOWtkOWYiSk=?=" , "=?UTF-8?B?Q2FpLCBMZQ==?=" , "=?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?=" To: Pavel Stehule References: <24099f4e-742c-e92b-729c-77bac1ca80d4@postgrespro.ru> <80cf30c2-ebd5-a568-9f62-a970e4d91326@postgrespro.ru> <5767433E-1A77-4682-B3FD-4869911021C8@alibaba-inc.com> <6b7570aa-14ea-2f28-c3b5-74dd785bbafe@postgrespro.ru> <20200105200603.wrb4beacwb57hhpk@development> <33969545-BEF7-4518-A741-302CBF7FF269@alibaba-inc.com> <0498c45c-53cb-d9b0-c1e2-7d2d1a618511@postgrespro.ru> <20200109164829.mdl3z5xalmaqxs67@development> <3fef844f-0e73-8fcb-e4b6-595d6317b191@postgrespro.ru> <20200112015109.caab6qdr2ob6d5jv@development> <9262B679-EA73-495E-82A6-508F5625023F@alibaba-inc.com> <8517A6EA-17A6-4160-8987-B01073A0ECAF@alibaba-inc.com> <8BC7841E-3B32-4518-9586-D9ED571F4922@alibaba-inc.com> X-Mailer: Apple Mail (2.3608.60.0.2.5) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_0B5BAAEC-F1C5-4825-AEA7-E6DD8D491457 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > 2020=E5=B9=B42=E6=9C=8815=E6=97=A5 =E4=B8=8B=E5=8D=886:06=EF=BC=8CPavel = Stehule =E5=86=99=E9=81=93=EF=BC=9A >=20 >=20 >> postgres=3D# insert into foo select generate_series(1,10000); >> INSERT 0 10000 >> postgres=3D# \dt+ foo >> List of relations >> = =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=90 >> =E2=94=82 Schema =E2=94=82 Name =E2=94=82 Type =E2=94=82 Owner =E2=94=82= Persistence =E2=94=82 Size =E2=94=82 Description =E2=94=82 >> = =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=A1 >> =E2=94=82 public =E2=94=82 foo =E2=94=82 table =E2=94=82 pavel =E2=94=82= session =E2=94=82 384 kB =E2=94=82 =E2=94=82 >> = =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=98 >> (1 row) >>=20 >> postgres=3D# truncate foo; >> TRUNCATE TABLE >> postgres=3D# \dt+ foo >> List of relations >> = =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=90 >> =E2=94=82 Schema =E2=94=82 Name =E2=94=82 Type =E2=94=82 Owner =E2=94=82= Persistence =E2=94=82 Size =E2=94=82 Description =E2=94=82 >> = =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=A1 >> =E2=94=82 public =E2=94=82 foo =E2=94=82 table =E2=94=82 pavel =E2=94=82= session =E2=94=82 16 kB =E2=94=82 =E2=94=82 >> = =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=98 >> (1 row) >>=20 >> I expect zero size after truncate. > Thanks for review. >=20 > I can explain, I don't think it's a bug. > The current implementation of the truncated GTT retains two blocks of = FSM pages. > The same is true for truncating regular tables in subtransactions. > This is an implementation that truncates the table without changing = the relfilenode of the table. >=20 >=20 > This is not extra important feature - now this is little bit a = surprise, because I was not under transaction. >=20 > Changing relfilenode, I think, is necessary, minimally for future = VACUUM FULL support. Not allowing relfilenode changes is the current limit. I think can improve on it. But ,This is a bit complicated. so I'd like to know the necessity of this improvement. Could you give me more details? >=20 > Regards >=20 > Pavel Stehule > =20 >=20 > Wenjing >=20 >>=20 >> Regards >>=20 >> Pavel >>=20 >>=20 >>=20 >> Wenjing >>=20 >>=20 >>=20 >>=20 >> >=20 >> > --=20 >> > Robert Haas >> > EnterpriseDB: http://www.enterprisedb.com = >> > The Enterprise PostgreSQL Company >>=20 >=20 --Apple-Mail=_0B5BAAEC-F1C5-4825-AEA7-E6DD8D491457 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

2020=E5=B9=B42=E6=9C=8815=E6=97=A5 =E4=B8=8B=E5=8D=886:06=EF=BC= =8CPavel Stehule <pavel.stehule@gmail.com> =E5=86=99=E9=81=93=EF=BC=9A

postgres=3D# insert into foo = select generate_series(1,10000);
INSERT 0 10000
postgres=3D# \dt+ foo
      =                     = List of relations
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
=E2=94=82 = Schema =E2=94=82 Name =E2=94=82 Type  =E2=94=82 Owner =E2=94=82 = Persistence =E2=94=82  Size  =E2=94=82 Description =E2=94=82
=E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1
=E2=94=82 = public =E2=94=82 foo  =E2=94=82 table =E2=94=82 pavel =E2=94=82 = session     =E2=94=82 384 kB =E2=94=82       =       =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98
(1 row)

postgres=3D# truncate foo;
TRUNCATE TABLE
postgres=3D# \dt+ foo
                =           List of relations
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=90
=E2=94=82 Schema =E2=94=82= Name =E2=94=82 Type  =E2=94=82 Owner =E2=94=82 Persistence =E2=94=82= Size  =E2=94=82 Description =E2=94=82
=E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=A1
=E2=94=82 public =E2=94=82= foo  =E2=94=82 table =E2=94=82 pavel =E2=94=82 session   =   =E2=94=82 16 kB =E2=94=82           =   =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=98
(1 row)

I expect zero size after = truncate.
Thanks for = review.

I can explain, I = don't think it's a bug.
The current implementation = of the truncated GTT retains two blocks of FSM pages.
The same is true for truncating regular tables in = subtransactions.
This is an implementation that = truncates the table without changing the relfilenode of the = table.


This is not extra important feature - = now this is little bit a surprise, because I was not under = transaction.

Changing relfilenode, I think, is necessary, minimally for = future VACUUM FULL = support.
Not allowing = relfilenode changes is the current limit.
I think can improve = on it. But ,This is a bit complicated.
so I'd like to know the = necessity of this improvement.
Could you give me more = details?


Regards

Pavel Stehule
 

Wenjing


Regards

Pavel



Wenjing




>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



= --Apple-Mail=_0B5BAAEC-F1C5-4825-AEA7-E6DD8D491457--