[Bug]: 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]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Compression

What happened?

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 version affected

2.15.3

PostgreSQL version used

15.7

What operating system did you use?

Docker: Ubuntu 15.7-1.pgdg22.04+1

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

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);
ALTER TABLE ej3.history_statistics SET ( timescaledb.compress, timescaledb.compress_segmentby = ‘“Postfachname”’ );

[zilder]

Hi @Daniel-Ebert,

Thanks for reporting the issue! It looks like a similar issue to this one, caused by having more than one table with the same name in different schemas, which was fixed in 2.16.0. Could you please check if it applies to your case too? If it does, then upgrading timescaledb should help.

[Daniel-Ebert]

Hi @zilder , thank you very much for your quick response! This was indeed the issue and upgrading timescaledb did resolve the problem, thanks again!