[Bug]: Add_dimension errors that only integer, timestamp and date data fields are supported, but smallint is also working

[igor2x]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Partitioning

What happened?

I. TEST

1. Create PostgreSQL table.

CREATE TABLE public.tab (
    mytime  TIMESTAMP WITH TIME ZONE,
    myspace VARCHAR(10),
    measure INTEGER
);

2. Convert table to hypertable.

SELECT CREATE_HYPERTABLE('public.tab', BY_RANGE('mytime'));

3. Add space dimension by range.

SELECT ADD_DIMENSION('public.tab', BY_RANGE('myspace', 1));

Above command returns error:

SELECT ADD_DIMENSION('public.tab', BY_RANGE('myspace', 1));
ERROR: invalid type for dimension "myspace"
HINT: Use an integer, timestamp, or date type.

This error is little bit unexpected, because there is no documentation that data type restriction exists.

II. TEST

1. Drop and recreate table with myspace as SMALLINT data type.

DROP TABLE public.tab IF EXISTS;

CREATE TABLE public.tab (
    mytime  TIMESTAMP WITH TIME ZONE,
    myspace SMALLINT,
    measure INTEGER
);

2. Create hypertable and add space dimension by range.

SELECT CREATE_HYPERTABLE('public.tab', BY_RANGE('mytime'));
SELECT ADD_DIMENSION('public.tab', BY_RANGE('myspace', 1));

Also a surprise, command works fine, but ISSUE FOR THIS BUG REPORT: "According to error message from previous ADD_DIMENSION command I expected "smallint" also fails, but it does not fail, it works fine.

SUGGESTIONS:

1. Fix the error message with adding "smallint" data type to error message as allowed data type. Maybe other data types are also supported. I haven't tested.
2. To documentation ADD_DIMENSION at https://docs.timescale.com/api/latest/hypertable/add_dimension/ add some info like "Warning" that there is restriction about data types that can be candidate for space partitioning, this will spare some time users that are reading documentation.

TimescaleDB version affected

2.14.2

PostgreSQL version used

15.6

What operating system did you use?

Red Hat 9.3

What installation method did you use?

RPM

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

I have explained above.

[nikkhils]

@igor2x integer data type subsumes smallint ranges. So, for the sake of brevity, we don't enumerate all sub-types in that case.

[nikkhils]

also, in your first example, myspace was a varchar, which can be used in by_hash in add_dimension.

[igor2x]

I have opened separate issue about improving documentation (for SUGGESTION 2 from my first post).

If you think, error message is good enough as it is currently, then you can close this issue (and I hope documentation will get updated with more info).

[jnidzwetzki]

Hi @igor2x,

Thanks for opening an issue in the docs repository. We will use this new issue to track the documentation update. As you suggested, I will close this one instead.