Re: [Proposal] Global temporary tables

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(dot)zwj(at)alibaba-inc(dot)com> wrote:

>
>
> > 2020年3月12日 上午4:12，Robert Haas <robertmhaas(at)gmail(dot)com> 写道：
> >
> > On Wed, Mar 11, 2020 at 9:07 AM 曾文旌(义从) <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
> > The Enterprise PostgreSQL Company
>
>

--

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