Re: [Proposal] Global temporary tables

> 2020年3月12日 下午8:22，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com> 写道：
>
> 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)

Truncated GTT has been supported.
Now it clears the data in the table by switching relfilenode and can support rollback.
Note that the latest relfilenode in GTT is not stored in pg_class, you can view them in the view pg_gtt_stats.

postgres=# create global temp table gtt1(a int primary key);
CREATE TABLE
postgres=# insert into gtt1 select generate_series(1,10000);
INSERT 0 10000
postgres=# select tablename,relfilenode from pg_gtt_relstats;
tablename | relfilenode
-----------+-------------
gtt1 | 16406
gtt1_pkey | 16409
(2 rows)
postgres=# truncate gtt1;
TRUNCATE TABLE
postgres=#
postgres=# select tablename,relfilenode from pg_gtt_relstats;
tablename | relfilenode
-----------+-------------
gtt1 | 16411
gtt1_pkey | 16412
(2 rows)

Wenjing

>
>
> On Thu, Mar 12, 2020 at 3:36 PM 曾文旌(义从) <wenjing(dot)zwj(at)alibaba-inc(dot)com <mailto:wenjing(dot)zwj(at)alibaba-inc(dot)com>> wrote:
>
>
> > 2020年3月12日 上午4:12，Robert Haas <robertmhaas(at)gmail(dot)com <mailto:robertmhaas(at)gmail(dot)com>> 写道：
> >
> > On Wed, Mar 11, 2020 at 9:07 AM 曾文旌(义从) <wenjing(dot)zwj(at)alibaba-inc(dot)com <mailto:wenjing(dot)zwj(at)alibaba-inc(dot)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 <http://www.enterprisedb.com/>
> > The Enterprise PostgreSQL Company
>
>
>
> --
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>