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-03-24T13:34:46Z
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.

Hi Wenjing,
Please check my findings(on gtt_v20.patch) as below:

*TestCase1:* (cache lookup failed on GTT)

-- Session1:
postgres=# create global temporary table gtt1(c1 int) on commit delete rows;
CREATE TABLE

-- Session2:
postgres=# drop table gtt1 ;
DROP TABLE

-- Session1:
postgres=# create global temporary table gtt1(c1 int) on commit delete rows;
ERROR:  cache lookup failed for relation 16384


*TestCase2:*

-- Session1:
postgres=# create global temporary table gtt (c1 integer) on commit
preserve rows;
CREATE TABLE
postgres=# insert into gtt values(10);
INSERT 0 1

-- Session2:
postgres=# drop table gtt;
DROP TABLE


I hope "session2" should not allow to perform the "DROP" operation on GTT
having data.

*Behavior of GTT in Oracle Database in such scenario:* For a completed
transaction on GTT with(on_commit_delete_rows='FALSE') with data in a
session, we will not be able to DROP from any session, we need to TRUNCATE
the data first to DROP the table.

SQL> drop table gtt;
drop table gtt
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table
already
in use



On Tue, Mar 17, 2020 at 9:16 AM 曾文旌(义从) <wenjing.zwj@alibaba-inc.com> wrote:

>
>
> 2020年3月11日 下午3:52，Prabhat Sahu <prabhat.sahu@enterprisedb.com> 写道：
>
> On Mon, Mar 9, 2020 at 10:02 PM 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
> wrote:
>
>>
>>
>> Fixed in global_temporary_table_v18-pg13.patch.
>>
> Hi Wenjing,
> Thanks for the patch. I have verified the previous issues with
> "gtt_v18_pg13.patch" and those are resolved.
> Please find below case:
>
> postgres=# create sequence seq;
> CREATE SEQUENCE
>
> postgres=# CREATE GLOBAL TEMPORARY TABLE gtt1(c1 int PRIMARY KEY) ON
> COMMIT DELETE ROWS;
> CREATE TABLE
>
> postgres=# CREATE GLOBAL TEMPORARY TABLE gtt2(c1 int PRIMARY KEY) ON
> COMMIT PRESERVE ROWS;
> CREATE TABLE
>
> postgres=# alter table gtt1 add c2 int default nextval('seq');
> ERROR:  cannot reindex global temporary tables
>
> postgres=# alter table gtt2 add c2 int default nextval('seq');
> ERROR:  cannot reindex global temporary tables
>
> reindex GTT is already supported
>
> Please check global_temporary_table_v20-pg13.patch
>
>
> Wenjing
>
>
>
>
> *Note*: We are getting this error if we have a key column(PK/UNIQUE) in a
> GTT, and trying to add a column with a default sequence into it.
>
> --
>
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com
>
>
>

-- 

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