Re: [Proposal] Global temporary tables
| От | Prabhat Sahu |
|---|---|
| Тема | Re: [Proposal] Global temporary tables |
| Дата | |
| Msg-id | CANEvxPrVan7f_JgVr5chaOHGtMHFfL-A5hVcYbQj_-5aYWvDRQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [Proposal] Global temporary tables ("曾文旌(义从)" <wenjing.zwj@alibaba-inc.com>) |
| Ответы |
Re: [Proposal] Global temporary tables
(Prabhat Sahu <prabhat.sahu@enterprisedb.com>)
Re: [Proposal] Global temporary tables (wjzeng <wenjing.zwj@alibaba-inc.com>) |
| Список | pgsql-hackers |
Hi Wenjing,
Please check my findings(on gtt_v20.patch) as below:
TestCase1: (cache lookup failed on GTT)
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 TABLEpostgres=# 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 alreadyin 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 tablesreindex GTT is already supportedPlease check global_temporary_table_v20-pg13.patchWenjing--
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
В списке pgsql-hackers по дате отправления: