"ERROR: transparent decompression only supports tableoid system column" Problem

[hakanozanlagan]

hi,

i am using postgressql+timescaledb as zabbix Database. when housekeepr running it's getting this error and Database size growing very fast. I am looking for solution for a while but didn't find anything.

2022-06-16 12:33:19.255 +03 [1938148] STATEMENT: delete from history where itemid=61683 and ctid = any(array(select ctid from history where itemid=61683 limit 5000))
2022-06-16 12:33:19.259 +03 [1938148] ERROR: transparent decompression only supports tableoid system column


Postgresql version: PostgreSQL 12.10
zabbix version : 5.4


it's getting same error when I run query at dbeaver tool

select ctid, * from trends_uint

.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [XX000]: ERROR: transparent decompression only supports tableoid system column
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStateme ntImpl.executeStatement(JDBCStatementImpl.java:133 )
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJ ob.executeStatement(SQLQueryJob.java:575)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJ ob.lambda$1(SQLQueryJob.java:484)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecut eRecover(DBExecUtils.java:172)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJ ob.executeSingleQuery(SQLQueryJob.java:491)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJ ob.extractData(SQLQueryJob.java:878)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryRe sultsContainer.readData(SQLEditor.java:3526)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJ obDataRead.lambda$0(ResultSetJobDataRead.java:118)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecut eRecover(DBExecUtils.java:172)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJ obDataRead.run(ResultSetJobDataRead.java:116)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetV iewer$ResultSetDataPumpJob.run(ResultSetViewer.jav a:4868)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(Ab stractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.j ava:63)
Caused by: org.postgresql.util.PSQLException: ERROR: transparent decompression only supports tableoid system column
at org.postgresql.core.v3.QueryExecutorImpl.receiveEr rorResponse(QueryExecutorImpl.java:2565)
at org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:2297)
at org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:322)
at org.postgresql.jdbc.PgStatement.executeInternal(Pg Statement.java:481)
at org.postgresql.jdbc.PgStatement.execute(PgStatemen t.java:401)
at org.postgresql.jdbc.PgStatement.executeWithFlags(P gStatement.java:322)
at org.postgresql.jdbc.PgStatement.executeCachedSql(P gStatement.java:308)
at org.postgresql.jdbc.PgStatement.executeWithFlags(P gStatement.java:284)
at org.postgresql.jdbc.PgStatement.execute(PgStatemen t.java:279)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStateme ntImpl.execute(JDBCStatementImpl.java:329)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStateme ntImpl.lambda$0(JDBCStatementImpl.java:131)
at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapD riverActions(SecurityManagerUtils.java:94)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStateme ntImpl.executeStatement(JDBCStatementImpl.java:131 )
... 12 more

[LivingLegend]

Hi, I had the same problem. In my case it was a problem with the compression of History and Trends in Housekeeper. If you used a DB with a time scale and set the history compression without overwriting in the Zabbix frontend. You have the same problem as me. The Zabbix documentation says this,

"For PostgreSQL version 10.2 or higher and TimescaleDB version 1.5 or higher, the timescaledb.sql script sets two additional parameters:

Enable compression
Compress records older than 7 days.
Compression can only be used if both the Override item history period and Override item trend period options are enabled."

The solution to my problem was as follows:

1. Stop Zabbix
2. Decompress data in Timescale DB.
   
   Note that this step requires more disk space for the database.
   
   For decompression, I found a blog post with instructions how to do it. I had to modify the string names, which were different.
   
   Decompress all compressed chunks:
   

   SELECT decompress_chunk(c, true) FROM show_chunks('trends_uint') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('trends') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history_uint') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history_str') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history_log') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history_text') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history') c;​

3. Clean orphaned data:
   

   	DELETE FROM history WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM trends WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM events WHERE source = 0 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);DELETE FROM events WHERE source = 3 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);DELETE FROM events WHERE source = 3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items);
   	​]

4. Run Zabbix
5. Go to the frontend and in the Housekeepr settings, set up overwriting and compression.

[LivingLegend]

Hi, I had the same problem. In my case it was a problem with the compression of History and Trends in Housekeeper. If you used a DB with a time scale and set the history compression without overwriting in the Zabbix frontend. You have the same problem as me. The Zabbix documentation says this,

"For PostgreSQL version 10.2 or higher and TimescaleDB version 1.5 or higher, the timescaledb.sql script sets two additional parameters:

Enable compression
Compress records older than 7 days.
Compression can only be used if both the Override item history period and Override item trend period options are enabled."

The solution to my problem was as follows:

1. Stop Zabbix
2. Decompress data in Timescale DB.
   
   Note that this step requires more disk space for the database.
   
   For decompression, I found a blog post with instructions how to do it. I had to modify the string names, which were different.
   
   Decompress all compressed chunks:
   

   SELECT decompress_chunk(c, true) FROM show_chunks('trends_uint') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('trends') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history_uint') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history_str') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history_log') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history_text') c;
   	SELECT decompress_chunk(c, true) FROM show_chunks('history') c;​

3. Clean orphaned data:
   

   	DELETE FROM history WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM trends WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items);DELETE FROM events WHERE source = 0 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);DELETE FROM events WHERE source = 3 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);DELETE FROM events WHERE source = 3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items);
   	​]

4. Run Zabbix
5. Go to the frontend and in the Housekeepr settings, set up overwriting and compression.

[hakanozanlagan]

Thank you for your reply . I will try this method.