Horton

Re: [Proposal] Global temporary tables

Prabhat Sahu <prabhat.sahu@enterprisedb.com>

View thread
From: Prabhat Sahu <prabhat.sahu@enterprisedb.com>
To: 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
Cc: Robert Haas <robertmhaas@gmail.com>, tushar <tushar.ahuja@enterprisedb.com>, Pavel Stehule <pavel.stehule@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, 蔡松露(子嘉) <zijia@taobao.com>, "Cai, Le" <le.cai@alibaba-inc.com>, 萧少聪(铁庵) <shaocong.xsc@alibaba-inc.com>
Date: 2020-03-12T12:22:20Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. PageAddItemExtended(): Add LP_UNUSED assertion.

  2. Remove temporary files after backend crash

  3. Fix comment in indexing.c

  4. Fix failure to ignore leftover temp tables after a server crash.

Hi Wenjing,

Please check the below findings:
After running "TRUNCATE" command, the "relfilenode" field is not changing
for GTT
whereas, for Simple table/Temp table "relfilenode" field is changing after
TRUNCATE.

*Case 1: Getting same "relfilenode" for GTT after and before "TRUNCATE"*
postgres=# create global temporary table gtt1(c1 int) on commit delete rows;
CREATE TABLE
postgres=# select relfilenode from pg_class  where relname ='gtt1';
 relfilenode
-------------
       16384
(1 row)
postgres=# truncate gtt1;
TRUNCATE TABLE
postgres=# select relfilenode from pg_class  where relname ='gtt1';
 relfilenode
-------------
       16384
(1 row)

postgres=# create global temporary table gtt2(c1 int) on commit preserve
rows;
CREATE TABLE
postgres=# select relfilenode from pg_class  where relname ='gtt2';
 relfilenode
-------------
       16387
(1 row)
postgres=# truncate gtt2;
TRUNCATE TABLE
postgres=# select relfilenode from pg_class  where relname ='gtt2';
 relfilenode
-------------
       16387
(1 row)


*Case 2: "relfilenode" changes after "TRUNCATE" for Simple table/Temp table*
postgres=# create temporary table temp3(c1 int) on commit preserve rows;
CREATE TABLE
postgres=# select relfilenode from pg_class  where relname ='temp3';
 relfilenode
-------------
       16392
(1 row)
postgres=# truncate temp3;
TRUNCATE TABLE
postgres=# select relfilenode from pg_class  where relname ='temp3';
 relfilenode
-------------
       16395
(1 row)


postgres=# create table tabl4(c1 int);
CREATE TABLE
postgres=# select relfilenode from pg_class  where relname ='tabl4';
 relfilenode
-------------
       16396
(1 row)
postgres=# truncate tabl4;
TRUNCATE TABLE
postgres=# select relfilenode from pg_class  where relname ='tabl4';
 relfilenode
-------------
       16399
(1 row)


On Thu, Mar 12, 2020 at 3:36 PM 曾文旌(义从) <wenjing.zwj@alibaba-inc.com> wrote:

>
>
> > 2020年3月12日 上午4:12，Robert Haas <robertmhaas@gmail.com> 写道：
> >
> > On Wed, Mar 11, 2020 at 9:07 AM 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
> wrote:
> >> reindex need change relfilenode, but GTT is not currently supported.
> >
> > In my view that'd have to be fixed somehow.
> Ok , I am working on it.
>
>
>
> >
> > --
> > Robert Haas
> > EnterpriseDB: http://www.enterprisedb.com
> > The Enterprise PostgreSQL Company
>
>

-- 

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