TimescaleDB Compression ALTER Table Returns ERROR: query returned more than one rowHINT: Make sure the query returns a single row, or use LIMIT 1

[Daniel-Ebert]

Hi everybody,

when issuing the below SQL Command to set the compression settings:
ALTER TABLE ej3.history_statistics SET ( timescaledb.compress, timescaledb.compress_segmentby = ‘“Postfachname”’ );

I’m getting this error:

/* ERROR: query returned more than one rowHINT: Make sure the query returns a single row, or use LIMIT 1.CONTEXT: PL/pgSQL function _timescaledb_functions.get_orderby_defaults(regclass,text) line 17 at SQL statementSQL statement “SELECT (SELECT string_agg(x, ', ') FROM jsonb_array_elements_text(seg_by->‘clauses’) t(x))::text, seg_by->>‘message’, (seg_by->>‘confidence’)::int FROM _timescaledb_functions.get_orderby_defaults(684081, coalesce($1, array::text)) seg_by” */

In other schemas, setting compression works without problems.
Has somebody encountered anything alike or could give me a hint?

TimescaleDB extension version:
2.15.3

[jonatasdp]

Hi Daniel, would you mind sharing more details of the definition of the history_statistics hypertable?

You may fill a bug on github as it looks like the internal function has an unexpected behavior with some different data format.

[Daniel-Ebert]

Hi @jonatasdp ,

thank you for your quick answer!

Yes, the table is defined as the following:
CREATE TABLE “ej3.history_statistics” (
“time” TIMESTAMPTZ NOT NULL,
“column1” TEXT NULL DEFAULT NULL,
“Postfachname” TEXT NULL DEFAULT NULL,
“column3” TEXT NULL DEFAULT NULL,
“column4” BIGINT NULL DEFAULT NULL,
“column5” TEXT NULL DEFAULT NULL,
“column6” TEXT NULL DEFAULT NULL,
KEY “history_statistics_time_idx” (“time”)
)
;
SELECT create_hypertable(‘ej3.history_statistics’, ‘time’, if_not_exists => TRUE);
Is that enough/Should I post it right away as a github bug report?

BR

Daniel

[jonatasdp]

Yes! It’s enough, thanks for the care of anonymizing your columns too!

I think if you have any policies or anything running extra, put it together, like retention policies or extra indices this table might have.

[Daniel-Ebert]

@jonatasdp

I think if you have any policies or anything running extra, put it together, like retention policies or extra indices this table might have.

Perfect, thank you very much

[Bug]: Compression ALTER Table Returns ERROR: query returned more than one rowHINT: Make sure the query returns a single row, or use LIMIT 1

And yes, I also thought anonymizing database columns for forum discussions makes sense

Best

Daniel

[Daniel-Ebert]

The issue was resolved by upgrading the extension, for reference see:

[Bug]: Compression ALTER Table Returns ERROR: query returned more than one rowHINT: Make sure the query returns a single row, or use LIMIT 1

Thanks again for your quick help!