Subject: Re: [Proposal] Global temporary tables
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= <wenjing.zwj@alibaba-inc.com>,
 Pavel Stehule <pavel.stehule@gmail.com>, Robert Haas
 <robertmhaas@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 =?UTF-8?B?6JSh5p2+6ZyyKOWtkOWYiSk=?= <zijia@taobao.com>,
 "Cai, Le" <le.cai@alibaba-inc.com>, =?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?=
 <shaocong.xsc@alibaba-inc.com>
References: 
 <CA+Tgmob_Fg9PfNjjOReJtAE4B5gc8+UqHh+tztJ_C+iRN4KHSw@mail.gmail.com>
 <24099f4e-742c-e92b-729c-77bac1ca80d4@postgrespro.ru>
 <CAFj8pRBo8YZ=bJESqf2fkxGL8Bq2Fs7=SjxisS7UhXHh6yPSfA@mail.gmail.com>
 <80cf30c2-ebd5-a568-9f62-a970e4d91326@postgrespro.ru>
 <5767433E-1A77-4682-B3FD-4869911021C8@alibaba-inc.com>
 <6b7570aa-14ea-2f28-c3b5-74dd785bbafe@postgrespro.ru>
 <20200105200603.wrb4beacwb57hhpk@development>
 <33969545-BEF7-4518-A741-302CBF7FF269@alibaba-inc.com>
 <20200106110119.zfrg2m5e2q5mvi5v@development>
 <3f360d7b-a46a-79f6-27b6-1b1df8c3ee82@postgrespro.ru>
 <20200109163037.u76k3kdr3pcmrytb@development>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <f462a08d-288b-3c6b-d55b-136f6b442a61@postgrespro.ru>
Date: Fri, 10 Jan 2020 15:24:34 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
In-Reply-To: <20200109163037.u76k3kdr3pcmrytb@development>
Content-Type: multipart/alternative;
 boundary="------------D66F616015E3019B1CDB98DB"
Content-Language: en-US
Precedence: bulk

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


On 09.01.2020 19:30, Tomas Vondra wrote:


>
>>
>>>
>>>> 3 Still no one commented on GTT's transaction information 
>>>> processing, they include
>>>> 3.1 Should gtt's frozenxid need to be care?
>>>> 3.2 gtt’s clog clean
>>>> 3.3 How to deal with "too old" gtt data
>>>>
>>>
>>> No idea what to do about this.
>>>
>>
>> I wonder what is the specific of GTT here?
>> The same problem takes place for normal (local) temp tables, doesn't it?
>>
>
> Not sure. TBH I'm not sure I understand what the issue actually is. 

Just open session, create temporary table and insert some data in it.
Then in other session run 2^31 transactions (at my desktop it takes 
about 2 hours).
As far as temp tables are not proceeded by vacuum, database is stalled:

  ERROR:  database is not accepting commands to avoid wraparound data 
loss in database "postgres"

It seems to be quite dubious behavior and it is strange to me that 
nobody complains about it.
We discuss  many issues related with temp tables (statistic, parallel 
queries,...) which seems to be less critical.

But this problem is not specific to GTT - it can be reproduced with 
normal (local) temp tables.
This is why I wonder why do we need to solve it in GTT patch.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--------------D66F616015E3019B1CDB98DB
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">
    <br>
    <br>
    <div class="moz-cite-prefix">On 09.01.2020 19:30, Tomas Vondra
      wrote:<br>
      <br>
      <br>
      <blockquote type="cite" style="color: #000000;">
        <br>
        <blockquote type="cite">
          <br>
          <blockquote type="cite">
            <br>
            <blockquote type="cite">3 Still no one commented on GTT's
              transaction information processing, they include
              <br>
              3.1 Should gtt's frozenxid need to be care?
              <br>
              3.2 gtt’s clog clean
              <br>
              3.3 How to deal with "too old" gtt data
              <br>
              <br>
            </blockquote>
            <br>
            No idea what to do about this.
            <br>
            <br>
          </blockquote>
          <br>
          I wonder what is the specific of GTT here?
          <br>
          The same problem takes place for normal (local) temp tables,
          doesn't it?
          <br>
          <br>
        </blockquote>
        <br>
        Not sure. TBH I'm not sure I understand what the issue actually
        is.
      </blockquote>
      <br>
      Just open session, create temporary table and insert some data in
      it.<br>
    </div>
    Then in other session run 2^31 transactions (at my desktop it takes
    about 2 hours).<br>
    As far as temp tables are not proceeded by vacuum, database is
    stalled:<br>
    <br>
     ERROR:  database is not accepting commands to avoid wraparound data
    loss in database "postgres"<br>
    <br>
    It seems to be quite dubious behavior and it is strange to me that
    nobody complains about it.<br>
    We discuss  many issues related with temp tables (statistic,
    parallel queries,...) which seems to be less critical.<br>
    <br>
    But this problem is not specific to GTT - it can be reproduced with
    normal (local) temp tables.<br>
    This is why I wonder why do we need to solve it in GTT patch.<br>
    <br>
    <pre class="moz-signature" cols="72">-- 
Konstantin Knizhnik
Postgres Professional: <a class="moz-txt-link-freetext" href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company </pre>
  </body>
</html>

--------------D66F616015E3019B1CDB98DB--