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>
Cc: tushar <tushar.ahuja@enterprisedb.com>, Pavel Stehule <pavel.stehule@gmail.com>, Robert Haas <robertmhaas@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-04-09T12:55:04Z
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年4月8日 下午6:55，Prabhat Sahu <prabhat.sahu@enterprisedb.com> 写道：
> 
> 
> 
> On Wed, Apr 8, 2020 at 1:48 PM 曾文旌 <wenjing.zwj@alibaba-inc.com <mailto:wenjing.zwj@alibaba-inc.com>> wrote:
> 
> 
>> 2020年4月7日 下午6:22，Prabhat Sahu <prabhat.sahu@enterprisedb.com <mailto:prabhat.sahu@enterprisedb.com>> 写道：
>> 
>> Thanks for review.
>> This parameter should support all types of writing of the bool type like parameter autovacuum_enabled.
>> So I fixed in global_temporary_table_v24-pg13.patch.
>> 
>> Thank you Wenjing for the new patch with the fix and the "VACUUM FULL GTT" support.
>> I have verified the above issue now its resolved.
>> 
>> Please check the below findings on VACUUM FULL.
>> 
>> postgres=# create global temporary table  gtt(c1 int) on commit preserve rows;
>> CREATE TABLE
>> postgres=# vacuum FULL ;
>> WARNING:  global temp table oldest FrozenXid is far in the past
>> HINT:  please truncate them or kill those sessions that use them.
>> VACUUM
> 
> This is expected,
> This represents that the GTT FrozenXid is the oldest in the entire db, and dba should vacuum the GTT if he want to push the db datfrozenxid.
> Also he can use function pg_list_gtt_relfrozenxids() to check which session has "too old” data and truncate them or kill the sessions.
>  
> Again as per the HINT given, as  "HINT:  please truncate them or kill those sessions that use them."
> There is only a single session.
> If we try "TRUNCATE" and "VACUUM FULL" still the behavior is same as below.
> 
> postgres=# truncate gtt ;
> TRUNCATE TABLE
> postgres=# vacuum full;
> WARNING: global temp table oldest FrozenXid is far in the past
> HINT: please truncate them or kill those sessions that use them.
> VACUUM
> 
If the GTT is vacuumed in the all table first, it will still receive a warning message,
So I improved the error message to make it more reasonable.


> I have one more finding related to "CLUSTER table USING index", Please check the below issue.
> postgres=# create global temporary table gtt(c1 int) on commit preserve rows;
> CREATE TABLE
> postgres=# create index idx1 ON gtt (c1);
> CREATE INDEX
> 
> -- exit and re-connect the psql prompt
> postgres=# \q
> [edb@localhost bin]$ ./psql postgres
> psql (13devel)
> Type "help" for help.
> 
> postgres=# cluster gtt using idx1;
> WARNING:  relcache reference leak: relation "gtt" not closed
> CLUSTER
It is a bug, I fixed ,please check global_temporary_table_v25-pg13.patch.


Wenjing




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