Subject: Re: [Proposal] Global temporary tables
To: =?UTF-8?B?5pu+5paH5peM?= <wenjing.zwj@alibaba-inc.com>,
 Pavel Stehule <pavel.stehule@gmail.com>, Robert Haas
 <robertmhaas@gmail.com>, Prabhat Sahu <prabhat.sahu@enterprisedb.com>
Cc: 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>
 <0498c45c-53cb-d9b0-c1e2-7d2d1a618511@postgrespro.ru>
 <20200109164829.mdl3z5xalmaqxs67@development>
 <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>
From: tushar <tushar.ahuja@enterprisedb.com>
Message-ID: <3aabfdf7-aa21-7658-d535-72a65948277f@enterprisedb.com>
Date: Wed, 8 Apr 2020 16:04:20 +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: <A1BED593-4D6F-429D-8971-55E6EF62314C@alibaba-inc.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

On 4/7/20 2:27 PM, 曾文旌 wrote:
> Vacuum full GTT, cluster GTT is already 
> supported in global_temporary_table_v24-pg13.patch.
Please refer this below scenario , where pg_upgrade is failing
1)Server is up and running (./pg_ctl -D data status)
2)Stop the server ( ./pg_ctl -D data stop)
3)Connect to server using single user mode ( ./postgres --single -D data 
postgres) and create a global temp table
[tushar@localhost bin]$ ./postgres --single -D data1233 postgres

PostgreSQL stand-alone backend 13devel
backend> create global temp table t(n int);

--Press Ctl+D to exit

4)Perform initdb ( ./initdb -D data123)
5.Run pg_upgrade
[tushar@localhost bin]$ ./pg_upgrade -d data -D data123 -b . -B .
--
--
--
Restoring database schemas in the new cluster
   postgres
*failure*
Consult the last few lines of "pg_upgrade_dump_13592.log" for
the probable cause of the failure.
Failure, exiting

log file content  -

[tushar@localhost bin]$ tail -20   pg_upgrade_dump_13592.log
pg_restore: error: could not execute query: ERROR:  pg_type array OID 
value not set when in binary upgrade mode
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT 
pg_catalog.binary_upgrade_set_next_pg_type_oid('13594'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_class oids
SELECT 
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('13593'::pg_catalog.oid);

CREATE GLOBAL TEMPORARY TABLE "public"."t" (
     "n" integer
)
WITH ("on_commit_delete_rows"='false');

-- For binary upgrade, set heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '0', relminmxid = '0'
WHERE oid = '"public"."t"'::pg_catalog.regclass;

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