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: 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-08T10:55:12Z
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.

On Wed, Apr 8, 2020 at 1:48 PM 曾文旌 <wenjing.zwj@alibaba-inc.com> wrote:

>
>
> 2020年4月7日 下午6:22，Prabhat Sahu <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

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

-- 

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