sql: related sequence from a SERIAL column is not deleted when DROP TABLE

[ZhouXing19]

Describe the problem

The SERIAL syntax under CREATE TABLE creates an underlying sequence. When we DROP TABLE, in PostgreSQL this underlying sequence will be deleted at the same time. But in CRDB, this sequence stays.

To Reproduce

I'm using PostgreSQL 13.3 and CockroachDB 21.1.7 on MacOS.

In PostgreSQL:

postgres=# \d
Did not find any relations.

postgres=# CREATE TABLE t (x SERIAL);
CREATE TABLE

postgres=# \d
           List of relations
 Schema |  Name   |   Type   |  Owner
--------+---------+----------+----------
 public | t       | table    | postgres
 public | t_x_seq | sequence | postgres
(2 rows)

postgres=# DROP TABLE  t;
DROP TABLE

postgres=# \d
Did not find any relations

In CockroachDB (with unrelated lines deleted):

[email protected]:26257/movr> set serial_normalization=sql_sequence;
SET

[email protected]:26257/movr> CREATE TABLE t (x SERIAL);
CREATE TABLE

[email protected]:26257/movr> \d
  schema_name |         table_name         |   type   | owner | estimated_row_count | locality
--------------+----------------------------+----------+-------+---------------------+-----------
  public      | t                          | table    | demo  |                   0 | NULL
  public      | t_x_seq                    | sequence | demo  |                   0 | NULL



[email protected]:26257/movr> DROP TABLE t;
DROP TABLE


[email protected]:26257/movr> \d
  schema_name |         table_name         |   type   | owner | estimated_row_count | locality
--------------+----------------------------+----------+-------+---------------------+-----------
  public      | t_x_seq                    | sequence | demo  |                   0 | NULL

If possible, provide steps to reproduce the behavior:
I ran cockroachDB with:

cd ~/your/path/to/cockroachdb/cockroach
gmake buildshort
./cockroach demo

Expected behavior
In this DROP TABLE case, the SERIAL column in CRDB is expected to behave the same as in PostgreSQL.

[ajwerner]

I think this might be that we aren't setting the owner of sequence to the column.