Timescaledb: PostgreSQL 12.5 on Windows crash when pgAdmin connects to database

[sgorsh]

Relevant system information:

• OS: Windows 10 x64 1909 (also tested on Windows 10 IoT Enterprise LTSB 2016)
• PostgreSQL version: 12.5
• TimescaleDB version: 2.0.0
• Installation method:
  
  
  
  • TimescaleDB: timescaledb-postgresql-12_2.0.0-windows-amd64 installer
  
  
  • PostgreSQL: EDB installer
  
  

Describe the bug
PostgreSQL crashes when pgAdmin 4.28 connects to the database where TimescaleDB is installed. Problem is the following query:
SELECT set_config('bytea_output','hex',false) FROM pg_settings WHERE name = 'bytea_output';

To Reproduce
Reproduction is fairly easy, example using psql on Windows:

postgres=# CREATE DATABASE test;
postgres=# \c test;
test=# CREATE EXTENSION IF NOT EXISTS timescaledb;
WARNING:
WELCOME TO
 _____ _                               _     ____________
|_   _(_)                             | |    |  _  \ ___ \
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 2.0.0
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/getting-started
 2. API reference documentation: https://docs.timescale.com/api
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION
test=# \dx timescaledb;
                                    List of installed extensions
    Name     | Version | Schema |                            Description
-------------+---------+--------+-------------------------------------------------------------------
 timescaledb | 2.0.0   | public | Enables scalable inserts and complex queries for time-series data
(1 row)

test=# SELECT set_config('bytea_output','hex',false) FROM pg_settings WHERE name = 'bytea_output';
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Expected behavior
Command should be executed normally.

Actual behavior
PostgreSQL crashes with exception 0xC0000005.
Log output:

2020-12-26 11:25:33.071 CET [6628] LOG:  server process (PID 1652) was terminated by exception 0xC0000005
2020-12-26 11:25:33.071 CET [6628] DETAIL:  Failed process was running: SELECT set_config('bytea_output','hex',false) FROM pg_settings WHERE name = 'bytea_output';
2020-12-26 11:25:33.071 CET [6628] HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2020-12-26 11:25:33.071 CET [6628] LOG:  terminating any other active server processes
2020-12-26 11:25:33.075 CET [9740] WARNING:  terminating connection because of crash of another server process
2020-12-26 11:25:33.075 CET [9740] 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.
2020-12-26 11:25:33.075 CET [9740] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-12-26 11:25:33.092 CET [9540] FATAL:  the database system is in recovery mode
2020-12-26 11:25:33.094 CET [6628] LOG:  all server processes terminated; reinitializing
2020-12-26 11:25:33.128 CET [12092] LOG:  database system was interrupted; last known up at 2020-12-26 11:25:15 CET
2020-12-26 11:25:33.627 CET [12092] LOG:  database system was not properly shut down; automatic recovery in progress
2020-12-26 11:25:33.632 CET [12092] LOG:  redo starts at F/D0A75018
2020-12-26 11:25:33.659 CET [12092] LOG:  invalid record length at F/D0C1C558: wanted 24, got 0
2020-12-26 11:25:33.660 CET [12092] LOG:  redo done at F/D0C1C420
2020-12-26 11:25:33.823 CET [6628] LOG:  database system is ready to accept connections
2020-12-26 11:25:33.852 CET [916] LOG:  TimescaleDB background worker launcher connected to shared catalogs

[k-rus]

@sgorsh Thank you for reporting.
Have you tried to execute some other SQL statements with TimescaleDB extension through psql?
Have you used previous version of TimescaleDB on the same PostgreSQL installation?

Can you obtain the stack trace of the crash?

[k-rus]

I have tried to reproduce this issue on Windows with PostgreSQL and TimescaleDB installed from sources, but it worked successfully.
I haven't tried with installers yet.

[k-rus]

I was able to reproduce this crash with TimescaleDB installation from the same zip.

[k-rus]

And as expected it is reproduced from the release build of TimescaleDB from the source.

[sgorsh]

OK, let me know if you need any more information.
Thanks.

[Da-Teach]

The issue is closed but the fix isn't released nor is there a warning anywhere that this issue can occur. I ran into this just now after upgrading to 2.0.0 and had to manually recompile the source code with the fix from PR #2780 . After that everything was good again. Considering the severity of this issue, a hotfix might be in order. (this would be less of an issue if you could downgrade back to 1.7.4, but that's not possible so this is a major issue)

[benneharli]

@Da-Teach would you mind sharing the compiled binary so I can test here?

[ucicelos]

It's not fixed for me either. I just downloaded "timescaledb-postgresql-12_2.0.0-windows-amd64.zip" and did fresh install of PostgreSQL 12.5. Created a new database and created timescaledb extension and started getting below message while using PGAdmin 4.

"server closed the connection unexpectedly, This probably means the server terminated abnormally before or while processing the request."

Below is the output from the log if it helps:

2021-01-08 02:00:27.286 PST [8148] LOG: database system is ready to accept connections
2021-01-08 02:00:27.325 PST [14084] LOG: TimescaleDB background worker launcher connected to shared catalogs
2021-01-08 02:03:13.106 PST [8148] LOG: server process (PID 22672) was terminated by exception 0xC0000005
2021-01-08 02:03:13.106 PST [8148] DETAIL: Failed process was running: 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-01-08 02:03:13.106 PST [8148] HINT: See C include file "ntstatus.h" for a description of the hexadecimal value.
2021-01-08 02:03:13.107 PST [8148] LOG: terminating any other active server processes
2021-01-08 02:03:13.109 PST [12836] WARNING: terminating connection because of crash of another server process
2021-01-08 02:03:13.109 PST [12836] 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-01-08 02:03:13.109 PST [12836] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2021-01-08 02:03:13.114 PST [23948] WARNING: terminating connection because of crash of another server process
2021-01-08 02:03:13.114 PST [23948] 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-01-08 02:03:13.114 PST [23948] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2021-01-08 02:03:13.122 PST [8148] LOG: all server processes terminated; reinitializing
2021-01-08 02:03:13.165 PST [22872] LOG: database system was interrupted; last known up at 2021-01-08 02:00:27 PST
2021-01-08 02:03:13.693 PST [18880] FATAL: the database system is in recovery mode
2021-01-08 02:03:13.759 PST [15536] FATAL: the database system is in recovery mode
2021-01-08 02:03:13.943 PST [22872] LOG: database system was not properly shut down; automatic recovery in progress
2021-01-08 02:03:13.946 PST [22872] LOG: redo starts at 0/18687A0
2021-01-08 02:03:13.947 PST [22872] LOG: invalid record length at 0/18687D8: wanted 24, got 0
2021-01-08 02:03:13.947 PST [22872] LOG: redo done at 0/18687A0
2021-01-08 02:03:13.976 PST [8148] LOG: database system is ready to accept connections
2021-01-08 02:03:14.021 PST [24096] LOG: TimescaleDB background worker launcher connected to shared catalogs

[sgorsh]

@ucicelos I've reported the bug in TimescaleDB version 2.0.0 (the version you downloaded). This issue should be fixed in next release of 2.0 version. You can compile the binaries from source code yourself but it takes some effort.

BTW, I didn't have time to compile and test the fix on my PC yet. Maybe next week.

[ucicelos]

@ucicelos I've reported the bug in TimescaleDB version 2.0.0 (the version you downloaded). This issue should be fixed in next release of 2.0 version. You can compile the binaries from source code yourself but it takes some effort.

BTW, I didn't have time to compile and test the fix on my PC yet. Maybe next week.

No worries, will try the next release. I did try and compile from the source but for some reason I was building 2.1-dev and something kept going wrong.....

[Da-Teach]

You can download the compiled version here:
https://easyupload.io/el3k4s
(link will stay valid for 15 days)

Note, it was compiled using the VS2019 toolset so might require this runtime:
https://aka.ms/vs/16/release/vc_redist.x64.exe
(not sure if it's same redist as VS2015)

[benneharli]

@Da-Teach thanks... did you do this for PG 12? I'm still running PG11...

[Da-Teach]

Yes the compiled version is for PG12.

[Flight777]

@Da-Teach Could you share your link one more time, we are struggling with the same now and need a fix urgently. Apparently TimescaleDB is not seeing this as an urgent hotfix... :(

Thanks! 👍

[Da-Teach]

https://easyupload.io/eo9wrl

Valid for 30 days, hopefully it'll be fixed by then.

[Flight777]

Thanks very much 👍 👍

[jbx1]

I am facing the same annoying problem. Why was this closed when it is evidently a big issue?

[benneharli]

I am facing the same annoying problem. Why was this closed when it is evidently a big issue?

It's closed because it's fixed. Have you tried with the 2.0.1 release?

https://timescalereleases.blob.core.windows.net/windows/timescaledb-postgresql-11_2.0.1-windows-amd64.zip
https://timescalereleases.blob.core.windows.net/windows/timescaledb-postgresql-12_2.0.1-windows-amd64.zip

[jbx1]

Well I only installed the latest version literally 2 days ago. Let me try these.