Horton

Re: [Proposal] Global temporary tables

曾文旌 (义从) <wenjing.zwj@alibaba-inc.com>

View thread
From: 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
To: Prabhat Sahu <prabhat.sahu@enterprisedb.com>, Robert Haas <robertmhaas@gmail.com>, tushar <tushar.ahuja@enterprisedb.com>, Pavel Stehule <pavel.stehule@gmail.com>
Cc: "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-17T03:42:24Z
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.


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