[Bug]: alter table for compression has inconsistent/misleading error messages

[felipenogueirajack]

What type of bug is this?

Configuration

What subsystems and features are affected?

Compression, Configuration

What happened?

We witnessed inconsistent behavior regarding the default value of timescaledb.compress_orderby, when the same alter table statement to turn on compression and set compression options are executed more than once.

To reproduce the error:

1. Create hypertable as below:

CREATE TABLE events.abc_evt(   
 timestamp BIGINT NOT NULL,    
 live_timescale_event_id BIGSERIAL NOT NULL,    
 event jsonb NOT NULL,
segment jsonb NOT NULL);

SELECT create_hypertable ('events.abc_evt', 'timestamp', chunk_time_interval => 604800000);

2. Set the following compression settings:

ALTER TABLE events."abc_evt" set (timescaledb.compress=true , timescaledb.compress_segmentby = '"segment"');

3. Set the same compression setting again:

ALTER TABLE events."abc_evt" set (timescaledb.compress=true , timescaledb.compress_segmentby = '"segment"');

The last command above throws the following error:

ERROR:  must specify a column to order by
DETAIL:  The timescaledb.compress_orderby option was previously set and must also be specified in the updated configuration.
SQL state: 0A000

Note that in both statements we did not specified column to order by. Just to be clear, the column to segmentby has nothing do with the error, as it also happens when we executed the following statement twice:

ALTER TABLE events."abc_evt" set (timescaledb.compress=true) 

The documentation states timescaledb.compress_orderby is an optional parameter and "The default is the descending order of the hypertable's time column." However, when we explicit specified the column to orderby in the statement, but using its default value, the error does not happen:

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_orderby = 'timestamp DESC', timescaledb.compress_segmentby = '"segment"' );

In all cases above (with or without timescaledb.compress_orderby), the compression options/setting were the same. We checked using the statement and its result is show in the image below:
select * from timescaledb_information.compression_settings;

Trying to understand the error, we specified the time column as the column to segment by, by executing the following:

ALTER TABLE events."abc_evt" set (timescaledb.compress=false);

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_segmentby = '"timestamp"');

Even without the orderby parameter, executing the last statement twice, the error does not occur. In addition, the compression settings shows a different result:

In our understanding, as the column to order by is not specified in any execution, the settings should have been the same: for timestamp column, the column orderby_asc should be false and orderby_nullsfirst should be true, as this is default option. In addition, executing the compression setting without timescaledb.compress_orderby should have not thrown the stated error (as it does not occur when the default value is explicit used).

TimescaleDB version affected

2.5.1

PostgreSQL version used

12.10

What operating system did you use?

Ubuntu 20.04.4 LTS

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

ERROR: must specify a column to order by
DETAIL: The timescaledb.compress_orderby option was previously set and must also be specified in the updated configuration.
SQL state: 0A000

How can we reproduce the bug?

Described with details above, but to resume:

1. Create hypertable as below:

CREATE TABLE events.abc_evt(   
 timestamp BIGINT NOT NULL,    
 live_timescale_event_id BIGSERIAL NOT NULL,    
 event jsonb NOT NULL,
segment jsonb NOT NULL);

SELECT create_hypertable ('events.abc_evt', 'timestamp', chunk_time_interval => 604800000);

2. Set the following compression settings:

ALTER TABLE events."abc_evt" set (timescaledb.compress=true , timescaledb.compress_segmentby = '"segment"');

3. Set the same compression setting again:

ALTER TABLE events."abc_evt" set (timescaledb.compress=true , timescaledb.compress_segmentby = '"segment"');

[svenklemm]

For compressed hypertables you cannot change the compression options. To change compression options you need to disable compression and set the new options when reenabling compression.

[felipenogueirajack]

From my understanding, I didn't change any compression options, as I executed exacly the same statement. From my understanding, the absence of timescaledb.compress_orderby has different behaviours. In the first execution, it considers the order by as the default option. But, in the following executions, it is being understood as null value.

In other words, I expected same behaviour/settings by executing the two block of codes below in a a hypertable without any compression options enabled.

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_orderby = 'timestamp DESC', timescaledb.compress_segmentby = '"segment"' );

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_orderby = 'timestamp DESC', timescaledb.compress_segmentby = '"segment"' );

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_segmentby = '"segment"' );

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_segmentby = '"segment"' );

[felipenogueirajack]

It is also possible to change options when the hypertable isn't compressed, but the options were previously set.

These statements execute without problems:

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_orderby = 'timestamp DESC');

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_orderby = 'timestamp ASC');

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_orderby = 'live_timescale_event_id DESC');

It is also possible to change the segment column without disabling compression options:

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_orderby = 'timestamp ASC');
								 
ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_orderby = 'timestamp ASC' 
, timescaledb.compress_segmentby = 'event');	

ALTER TABLE events."abc_evt" set (timescaledb.compress=true, timescaledb.compress_orderby = 'timestamp ASC' 
, timescaledb.compress_segmentby = 'live_timescale_event_id');	

Apparentely, the issue is only related to orderby, only when the parameter isn't stated explicitly in the alter table statement, because we were able to change all compression options without disabling it. Is this behavior expected?

[konskov]

Hi @felipenogueirajack , sorry for the delay getting back to you about this issue. Yes, this behavior you noticed was indeed unexpected. The linked pull request should fix that, so the issue is closed.