On 2/27/20 9:43 AM, 曾文旌(义从) wrote:
_-- Scenario 2:_
Here I am getting the same error message in both the below cases.
We may add a "global" keyword with GTT related error message.
postgres=# create global temporary table gtt1 (c1 int unique);
CREATE TABLE
postgres=# create temporary table tmp1 (c1 int unique);
CREATE TABLE
postgres=# create temporary table tmp2 (c1 int references gtt1(c1) );
ERROR: constraints on temporary tables may reference only temporary
tables
postgres=# create global temporary table gtt2 (c1 int references
tmp1(c1) );
ERROR: constraints on temporary tables may reference only temporary
tables
Fixed in global_temporary_table_v15-pg13.patch
Thanks Wenjing.
This below scenario is not working i.e even 'on_commit_delete_rows' is
true then after commit - rows are NOT removing
postgres=# create global temp table foo1(n int) with
(on_commit_delete_rows='true');
CREATE TABLE
postgres=#
postgres=# begin;
BEGIN
postgres=*# insert into foo1 values (9);
INSERT 0 1
postgres=*# insert into foo1 values (9);
INSERT 0 1
postgres=*# select * from foo1;
n
---
9
9
(2 rows)
postgres=*# commit;
COMMIT
postgres=# select * from foo1; -- after commit -there should be 0 row
as on_commit_delete_rows is 'true'
n
---
9
9
(2 rows)
postgres=# \d+ foo1
Table "public.foo1"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
n | integer | | | | plain
| |
Access method: heap
Options: on_commit_delete_rows=true
postgres=#
but if user - create table this way then it is working as expected
postgres=# create global temp table foo2(n int) *on commit delete rows;*
CREATE TABLE
postgres=# begin; insert into foo2 values (9); insert into foo2 values
(9); commit; select * from foo2;
BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
n
---
(0 rows)
postgres=#
i guess , problem is something with this syntax - create global temp
table foo1(n int) *with (on_commit_delete_rows='true'); *
--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company
