TimescaleDB manual decompression

1

I faced this problem with manual decompression following this guide.

Tried steps below:

  1. Find and pause the job related to compression policy.
SELECT alter_job(job_id => 
(
SELECT s.job_id
FROM timescaledb_information.jobs j
INNER JOIN timescaledb_information.job_stats s ON j.job_id = s.job_id
WHERE j.proc_name = 'policy_compression' AND s.hypertable_name = 'sensorsdata'
), scheduled => false, next_start => 'infinity');
  1. Decompress chunks by time interval (all without current one).
SELECT decompress_chunk(chunk, if_compressed => true)
FROM show_chunks('sensorsdata', older_than => now()::timestamp) AS chunk;

or single by the name

SELECT decompress_chunk('_timescaledb_internal._hyper_1_2_chunk', if_compressed => true);

But step 2 causing the db connection to be lost. This occurs in pgadmin and from C# app using EF Core.

Picture 1. Connection loss during decompressing single chunk

But when I do this several times in a row, the command is executed on some attempt.

This I faced in log files.

2021-04-20 21:22:50.188 GMT [7728] LOG:  statement: SELECT decompress_chunk('_timescaledb_internal._hyper_1_5_chunk', if_compressed => true);
2021-04-20 21:22:50.228 GMT [6972] LOG:  server process (PID 7728) was terminated by exception 0xC0000005
2021-04-20 21:22:50.228 GMT [6972] DETAIL:  Failed process was running: SELECT decompress_chunk('_timescaledb_internal._hyper_1_5_chunk', if_compressed => true);
2021-04-20 21:22:50.228 GMT [6972] HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2021-04-20 21:22:50.233 GMT [6972] LOG:  terminating any other active server processes
2021-04-20 21:22:50.248 GMT [16160] WARNING:  terminating connection because of crash of another server process
2021-04-20 21:22:50.248 GMT [16160] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-04-20 21:22:50.248 GMT [16160] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2021-04-20 21:22:50.257 GMT [6972] LOG:  all server processes terminated; reinitializing
2021-04-20 21:22:50.337 GMT [1052] LOG:  database system was interrupted; last known up at 2021-04-20 21:22:49 GMT
2021-04-20 21:22:51.095 GMT [1052] LOG:  database system was not properly shut down; automatic recovery in progress
2021-04-20 21:22:51.102 GMT [1052] LOG:  invalid record length at 0/11801C78: wanted 24, got 0
2021-04-20 21:22:51.104 GMT [1052] LOG:  redo is not required
2021-04-20 21:22:51.159 GMT [6972] LOG:  database system is ready to accept connections
2021-04-20 21:22:51.217 GMT [16440] LOG:  TimescaleDB background worker launcher connected to shared catalogs
2021-04-20 21:22:51.819 GMT [7128] LOG:  statement: SET DateStyle=ISO; SET client_min_messages=notice; SELECT set_config('bytea_output','hex',false) FROM pg_settings WHERE name = 'bytea_output'; SET client_encoding='UNICODE';
2021-04-20 21:22:52.547 GMT [7128] LOG:  statement: SELECT version()
2021-04-20 21:22:52.549 GMT [7128] LOG:  statement: 
    SELECT
        db.oid as did, db.datname, db.datallowconn,
        pg_encoding_to_char(db.encoding) AS serverencoding,
        has_database_privilege(db.oid, 'CREATE') as cancreate, datlastsysoid,
        datistemplate
    FROM
        pg_database db
    WHERE db.datname = current_database()
2021-04-20 21:22:52.555 GMT [7128] LOG:  statement: 
            SELECT
                oid as id, rolname as name, rolsuper as is_superuser,
                CASE WHEN rolsuper THEN true ELSE rolcreaterole END as
                can_create_role,
                CASE WHEN rolsuper THEN true ELSE rolcreatedb END as can_create_db
            FROM
                pg_catalog.pg_roles
            WHERE
                rolname = current_user
2021-04-20 21:22:52.561 GMT [7128] LOG:  statement: SELECT decompress_chunk('_timescaledb_internal._hyper_1_5_chunk', if_compressed => true);
2021-04-20 21:22:52.688 GMT [7388] LOG:  statement: SET DateStyle=ISO; SET client_min_messages=notice; SELECT set_config('bytea_output','hex',false) FROM pg_settings WHERE name = 'bytea_output'; SET client_encoding='UNICODE';
2021-04-20 21:22:53.371 GMT [7388] LOG:  statement: SELECT version()
2021-04-20 21:22:53.373 GMT [7388] LOG:  statement: 
    SELECT
        db.oid as did, db.datname, db.datallowconn,
        pg_encoding_to_char(db.encoding) AS serverencoding,
        has_database_privilege(db.oid, 'CREATE') as cancreate, datlastsysoid,
        datistemplate
    FROM
        pg_database db
    WHERE db.datname = current_database()
2021-04-20 21:22:53.378 GMT [7388] LOG:  statement: 
            SELECT
                oid as id, rolname as name, rolsuper as is_superuser,
                CASE WHEN rolsuper THEN true ELSE rolcreaterole END as
                can_create_role,
                CASE WHEN rolsuper THEN true ELSE rolcreatedb END as can_create_db
            FROM
                pg_catalog.pg_roles
            WHERE
                rolname = current_user
2021-04-20 21:22:53.383 GMT [7388] LOG:  statement: SELECT oid, format_type(oid, NULL) AS typname FROM pg_type WHERE oid IN (2205) ORDER BY oid;

Any ideas?

postgresql
timescaledb
asked on Stack Overflow Apr 20, 2021 by Artur Safiullin • edited Apr 20, 2021 by Artur Safiullin

1 Answer

0

What does your config show for:

select * from timescaledb_information.jobs where job_id=<compression job id>?

Sometimes if the background job has a bad parameter, it might be failing and causing the other connection to close.

answered on Stack Overflow Apr 22, 2021 by Ryan

User contributions licensed under CC BY-SA 3.0