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