Re: [Proposal] Global temporary tables

> 2020年4月8日 下午6:55，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com> 写道：
>
>
>
> On Wed, Apr 8, 2020 at 1:48 PM 曾文旌 <wenjing(dot)zwj(at)alibaba-inc(dot)com <mailto:wenjing(dot)zwj(at)alibaba-inc(dot)com>> wrote:
>
>
>> 2020年4月7日 下午6:22，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com <mailto:prabhat(dot)sahu(at)enterprisedb(dot)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(at)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/>