Re: [Proposal] Global temporary tables

> 2020年3月25日 下午8:52，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com> 写道：
>
> Hi All,
>
> Please check the behavior of GTT having column with "SERIAL" datatype and column with default value as "SEQUENCE" as below:
>
> Session1:
> postgres=# create sequence gtt_c3_seq;
> CREATE SEQUENCE
> postgres=# create global temporary table gtt(c1 int, c2 serial, c3 int default nextval('gtt_c3_seq') not null) on commit preserve rows;
> CREATE TABLE
>
> -- Structure of column c2 and c3 are similar:
> postgres=# \d+ gtt
> Table "public.gtt"
> Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
> --------+---------+-----------+----------+---------------------------------+---------+--------------+-------------
> c1 | integer | | | | plain | |
> c2 | integer | | not null | nextval('gtt_c2_seq'::regclass) | plain | |
> c3 | integer | | not null | nextval('gtt_c3_seq'::regclass) | plain | |
> Access method: heap
> Options: on_commit_delete_rows=false
>
> postgres=# insert into gtt select generate_series(1,3);
> INSERT 0 3
> postgres=# select * from gtt;
> c1 | c2 | c3
> ----+----+----
> 1 | 1 | 1
> 2 | 2 | 2
> 3 | 3 | 3
> (3 rows)
>
> Session2:
> postgres=# insert into gtt select generate_series(1,3);
> INSERT 0 3
> postgres=# select * from gtt;
> c1 | c2 | c3
> ----+----+----
> 1 | 1 | 4
> 2 | 2 | 5
> 3 | 3 | 6
> (3 rows)
>
> Kindly let me know, Is this behavior expected?
>
> --

postgres=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------------+----------+-------------+-------------+------------+-------------
public | gtt | table | wenjing.zwj | session | 8192 bytes |
public | gtt_c2_seq | sequence | wenjing.zwj | session | 8192 bytes |
public | gtt_c3_seq | sequence | wenjing.zwj | permanent | 8192 bytes |
(3 rows)

This is expected.
GTT'sequence is the same as GTT, so gtt_c2_seq is independent of each sessions.
gtt_c3_seq is a classic sequence.

Wenjing

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