Subject: Re: [Proposal] Global temporary tables
To: =?UTF-8?B?5pu+5paH5peM?= <wenjing.zwj@alibaba-inc.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
 Prabhat Sahu <prabhat.sahu@enterprisedb.com>,
 Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 Konstantin Knizhnik <k.knizhnik@postgrespro.ru>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>, =?UTF-8?B?6JSh5p2+6Zyy?=
 =?UTF-8?B?KOWtkOWYiSk=?= <zijia@taobao.com>, "Cai, Le"
 <le.cai@alibaba-inc.com>, =?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?=
 <shaocong.xsc@alibaba-inc.com>
References: <24099f4e-742c-e92b-729c-77bac1ca80d4@postgrespro.ru>
 <3fef844f-0e73-8fcb-e4b6-595d6317b191@postgrespro.ru>
 <20200112015109.caab6qdr2ob6d5jv@development>
 <CA+TgmobRhPA1gVbMsj9YLA-sv0Ub8NpMrkWCKwRiD=bnjVND3Q@mail.gmail.com>
 <9262B679-EA73-495E-82A6-508F5625023F@alibaba-inc.com>
 <CAFj8pRCnHvditiNKtGAByfzLjayOg0AZT-KXFvELvtfGJLRL8w@mail.gmail.com>
 <8517A6EA-17A6-4160-8987-B01073A0ECAF@alibaba-inc.com>
 <CA+TgmobtnmTguphZ_7536yhzENzCSDPEkJnha-29a4Q7hAwEWg@mail.gmail.com>
 <8BC7841E-3B32-4518-9586-D9ED571F4922@alibaba-inc.com>
 <CAFj8pRBSFwhj+iccFF50WdrruAXbMsvqTMTfCVu9x2LPcvnd2w@mail.gmail.com>
 <CAFj8pRBNssuJoYH6d51-yqOfV2_nB+aK-Mj7maLPQH+O8cm-TA@mail.gmail.com>
 <EFC62064-4477-4BA9-9E6D-71551FA3BE9D@alibaba-inc.com>
 <CAFj8pRCz52CbxnNmO=NK7cBA=9o=rdWVEBJRKOeVJ1FwzqzLRw@mail.gmail.com>
 <A1BED593-4D6F-429D-8971-55E6EF62314C@alibaba-inc.com>
 <e1e97c2c-bdae-d855-672d-3841fa15e8eb@enterprisedb.com>
 <80DC3BA7-E5C0-43CA-A66C-2CCB45C10EA7@alibaba-inc.com>
From: tushar <tushar.ahuja@enterprisedb.com>
Message-ID: <953705a7-52df-4b7e-a7b5-675a6303a262@enterprisedb.com>
Date: Mon, 13 Apr 2020 16:02:30 +0530
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.9.0
MIME-Version: 1.0
In-Reply-To: <80DC3BA7-E5C0-43CA-A66C-2CCB45C10EA7@alibaba-inc.com>
Content-Type: multipart/alternative;
 boundary="------------2800E004EA9E71E5D9035A3C"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------2800E004EA9E71E5D9035A3C
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 4/9/20 6:26 PM, 曾文旌 wrote:
>> On 4/7/20 2:27 PM, 曾文旌 wrote:
>>> Vacuum full GTT, cluster GTT is already supported in global_temporary_table_v24-pg13.patch.
>> Here , it is skipping GTT
>>
>> postgres=# \c foo
>> You are now connected to database "foo" as user "tushar".
>> foo=# create global temporary table  g123( c1 int) ;
>> CREATE TABLE
>> foo=# \q
>> [tushar@localhost bin]$ ./vacuumdb --full  foo
>> vacuumdb: vacuuming database "foo"
>> WARNING:  skipping vacuum global temp table "g123" because storage is not initialized for current session
> The message was inappropriate at some point, so I removed it.
>
Thanks Wenjing. Please see -if this below behavior is correct

X terminal -

postgres=# create global temp table foo1(n int);
CREATE TABLE
postgres=# insert into foo1 values (generate_series(1,10));
INSERT 0 10
postgres=# vacuum full ;
VACUUM

Y Terminal -

[tushar@localhost bin]$ ./vacuumdb -f  postgres
vacuumdb: vacuuming database "postgres"
WARNING:  global temp table oldest relfrozenxid 3276 is the oldest in 
the entire db
DETAIL:  The oldest relfrozenxid in pg_class is 3277
HINT:  If they differ greatly, please consider cleaning up the data in 
global temp table.
WARNING:  global temp table oldest relfrozenxid 3276 is the oldest in 
the entire db
DETAIL:  The oldest relfrozenxid in pg_class is 3277
HINT:  If they differ greatly, please consider cleaning up the data in 
global temp table.


-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company


--------------2800E004EA9E71E5D9035A3C
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 4/9/20 6:26 PM, 曾文旌 wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:80DC3BA7-E5C0-43CA-A66C-2CCB45C10EA7@alibaba-inc.com">
      <blockquote type="cite" style="color: #000000;">
        <pre class="moz-quote-pre" wrap="">On 4/7/20 2:27 PM, 曾文旌 wrote:
</pre>
        <blockquote type="cite" style="color: #000000;">
          <pre class="moz-quote-pre" wrap="">Vacuum full GTT, cluster GTT is already supported in global_temporary_table_v24-pg13.patch.
</pre>
        </blockquote>
        <pre class="moz-quote-pre" wrap="">Here , it is skipping GTT

postgres=# \c foo
You are now connected to database "foo" as user "tushar".
foo=# create global temporary table  g123( c1 int) ;
CREATE TABLE
foo=# \q
[tushar@localhost bin]$ ./vacuumdb --full  foo
vacuumdb: vacuuming database "foo"
WARNING:  skipping vacuum global temp table "g123" because storage is not initialized for current session
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">The message was inappropriate at some point, so I removed it.

</pre>
    </blockquote>
    <p>Thanks Wenjing. Please see -if this below behavior is correct <br>
    </p>
    <p>X terminal -</p>
    <p>postgres=# create global temp table foo1(n int);<br>
      CREATE TABLE<br>
      postgres=# insert into foo1 values (generate_series(1,10));<br>
      INSERT 0 10<br>
      postgres=# vacuum full ;<br>
      VACUUM<br>
    </p>
    <p>Y Terminal - <br>
    </p>
    <p>[tushar@localhost bin]$ ./vacuumdb -f  postgres<br>
      vacuumdb: vacuuming database "postgres"<br>
      WARNING:  global temp table oldest relfrozenxid 3276 is the oldest
      in the entire db<br>
      DETAIL:  The oldest relfrozenxid in pg_class is 3277<br>
      HINT:  If they differ greatly, please consider cleaning up the
      data in global temp table.<br>
      WARNING:  global temp table oldest relfrozenxid 3276 is the oldest
      in the entire db<br>
      DETAIL:  The oldest relfrozenxid in pg_class is 3277<br>
      HINT:  If they differ greatly, please consider cleaning up the
      data in global temp table.<br>
    </p>
    <p><br>
    </p>
    <pre class="moz-signature" cols="72">-- 
regards,tushar
EnterpriseDB  <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com/">https://www.enterprisedb.com/</a>
The Enterprise PostgreSQL Company
</pre>
  </body>
</html>

--------------2800E004EA9E71E5D9035A3C--