Confusing behavior of create table like

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Confusing behavior of create table like
Date: 2020-07-31 22:06:08
Message-ID: 0f7574c7-40cb-6b9c-5f79-c7b57f98ea15@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Postgres provides serial and bigserial column types for which it
implicitly creates sequence.
As far as this mechanism is somehow hidden from user, it may be
confusing that table
created with CREATE TABLE LIKE has no associated sequence.

But what is worse, even if experienced user knows that serial types are
implemented in Postgres by specifying
nextval(seq) default value for this column and default values are copied
by CREATE TABLE LIKE only if is it explicitly requested (including all),
then two tables will share the same sequence:

create table t1(x serial primary key, val int);
create table t2(like t1 including all);

postgres=# \d+ t1;
                                               Table "public.t1"
 Column |  Type   | Collation | Nullable | Default            | Storage
| Stats target | Description
--------+---------+-----------+----------+-------------------------------+---------+--------------+-------------
 x      | integer |           | not null |
nextval('t1_x_seq'::regclass) | plain   |              |
 val    | integer |           | |                               |
plain   |              |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (x)
Access method: heap

postgres=# \d+ t2;
                                               Table "public.t2"
 Column |  Type   | Collation | Nullable | Default            | Storage
| Stats target | Description
--------+---------+-----------+----------+-------------------------------+---------+--------------+-------------
 x      | integer |           | not null |
nextval('t1_x_seq'::regclass) | plain   |              |
 val    | integer |           | |                               |
plain   |              |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (x)
Access method: heap

Please notice that index is correctly replaced, but sequence - not.
I consider such behavior more like bug than a feature.
And it can be fixed using relatively small patch.

Thoughts?

Attachment Content-Type Size
create_table_like.patch text/x-patch 3.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2020-08-01 00:16:51 Re: Comment simplehash/dynahash trade-offs
Previous Message Thomas Munro 2020-07-31 21:56:14 Re: Cache relation sizes?