SERIAL4 is showing up as INT8 after creating table.

[jkthorne]

Describe the problem

I am trying to create a 32bit integer serial and I am getting a 64 bit.

To Reproduce

1. Set up CockroachDB cluster
2. create table

root@:26257/yolo> CREATE TABLE  test ( id SERIAL4 PRIMARY KEY);

3. describe table

root@:26257/yolo> SHOW COLUMNS FROM test;
  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
  id          | INT8      |    false    | unique_rowid() |                       | {primary} |   false
(1 row)

Expected behavior
the column type should be an INT4

Environment:
Build Tag: v20.1.8
Build Time: 2020/10/21 15:46:38
Distribution: CCL
Platform: linux amd64 (x86_64-unknown-linux-gnu)
Go Version: go1.13.9
C Compiler: gcc 6.3.0
Build SHA-1: ffd029f
Build Type: release

MacOS
Docker

Additional context
Trying to port an application from postgres to cockrochdb

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @mattcrdb (member of the technical support engineering team)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

thanks for the issue @wontruefree.

I believe the issue here is that when the setting serial_normalization is set to rowid or virtual_sequence (rowid is the default), then any SERIAL column will be backed by unique_rowid(), which always returns INT8. The motivation here is that unique_rowid() is far quicker than using a real sequence, though that behavior can be forced by setting serial_normalization=sql_sequence.

I don't know if it's do-able for us to make different unique_rowid() functions for different SERIAL sizes -- I'm not sure if we could still guarantee uniqueness with the smaller number of bits. But if we can, then that would be a fix.

Otherwise, we may address this issue by returning a warning (or possibly an error) if a SERIAL4 or SERIAL2 column is created and serial_normalization=sql_sequence is not being used.

[jkthorne]

So no matter which serial you use (SERIAL4, SERIAL8) you will only get an INT8? I am reading the documentation and I dont see the difference in data_types.
Let me know if you can do SERIAL4 with sql_sequence. I would like to integrate with a project that is postgres only right now.

[rafiss]

@wontruefree Yes, if you use SERIAL4 and have serial_normalization=sql_sequence, then the column type will be an INT4. Just be aware that sql_sequence has an additional performance cost: https://www.cockroachlabs.com/docs/stable/serial.html#generated-values-for-mode-sql_sequence

If possible, I would recommend that you try using SERIAL8 with serial_normalization=rowid.