st 25. 3. 2020 v 13:53 odesílatel Prabhat Sahu <
[email protected]> napsal:
> 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?
>
It is interesting side effect - theoretically it is not important, because
sequence ensure just unique values - so values are not important.
You created classic shared sequence so the behave is correct and expected.
Pavel
> --
>
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com
>
