postgres got segmentation fault occasionally when postgrest reload config

[LaurenceChau]

Environment

• PostgreSQL version: postgres 16.1
• PostgREST version: postgrest/postgrest:v12.0.2
• Operating system: Ubuntu 22.04

Description of issue

There is a rare chance the postgresdb got segmentation fault when postgrest reload config

timescaledb-timescaledb-1  | 2024-01-22 23:57:41.757 UTC [1] LOG:  server process (PID 32) was terminated by signal 11: Segmentation fault
timescaledb-timescaledb-1  | 2024-01-22 23:57:41.757 UTC [1] DETAIL:  Failed process was running:
timescaledb-timescaledb-1  |     -- Recursively get the base types of domains
timescaledb-timescaledb-1  |      WITH
timescaledb-timescaledb-1  |      base_types AS (
timescaledb-timescaledb-1  |        WITH RECURSIVE
timescaledb-timescaledb-1  |        recurse AS (
timescaledb-timescaledb-1  |          SELECT
timescaledb-timescaledb-1  |            oid,
timescaledb-timescaledb-1  |            typbasetype,
timescaledb-timescaledb-1  |            COALESCE(NULLIF(typbasetype, 0), oid) AS base
timescaledb-timescaledb-1  |          FROM pg_type
timescaledb-timescaledb-1  |          UNION
timescaledb-timescaledb-1  |          SELECT
timescaledb-timescaledb-1  |            t.oid,
timescaledb-timescaledb-1  |            b.typbasetype,
timescaledb-timescaledb-1  |            COALESCE(NULLIF(b.typbasetype, 0), b.oid) AS base
timescaledb-timescaledb-1  |          FROM recurse t
timescaledb-timescaledb-1  |          JOIN pg_type b ON t.typbasetype = b.oid
timescaledb-timescaledb-1  |        )
timescaledb-timescaledb-1  |        SELECT
timescaledb-timescaledb-1  |          oid,
timescaledb-timescaledb-1  |          base
timescaledb-timescaledb-1  |        FROM recurse
timescaledb-timescaledb-1  |        WHERE typbasetype = 0
timescaledb-timescaledb-1  |      ),
timescaledb-timescaledb-1  |      arguments AS (
timescaledb-timescaledb-1  |        SELECT
timescaledb-timescaledb-1  |          oid,
timescaledb-timescaledb-1  |          array_agg((
timescaledb-timescaledb-1  |            COALESCE(name, ''), -- name
timescaledb-timescaledb-1  |            type::regtype::text, -- type
timescaledb-timescaledb-1  |            CASE type
timescaledb-timescaledb-1  |              WHEN 'bit'::regtype THEN 'bit varying'
timescaledb-timescaledb-1  |              WHEN 'bit[]'::regtype THEN 'bit varying[]'
timescaledb-timescaledb-1  |              WHEN 'character'::regtype THEN 'character varying'
timescaledb-timescaledb-1  |              WHEN 'character[]'::regtype THEN 'character varying[]'
timescaledb-timescaledb-1  |              ELSE type::regtype::text
timescaledb-timescaledb-1  |            END, -- convert types that ignore the lenth and accept any value till maximum size
timescaledb-timescaledb-1  |            idx <= (pr
timescaledb-timescaledb-1  | 2024-01-22 23:57:41.757 UTC [1] LOG:  terminating any other active server processes
timescaledb-timescaledb-1  | 2024-01-22 23:57:41.760 UTC [34] FATAL:  the database system is in recovery mode
timescaledb-timescaledb-1  | 2024-01-22 23:57:41.810 UTC [1] LOG:  all server processes terminated; reinitializing
timescaledb-timescaledb-1  | 2024-01-22 23:57:43.584 UTC [35] LOG:  database system was interrupted; last known up at 2024-01-22 23:57:40 UTC
timescaledb-timescaledb-1  | 2024-01-22 23:57:44.267 UTC [35] LOG:  database system was not properly shut down; automatic recovery in progress
timescaledb-timescaledb-1  | 2024-01-22 23:57:44.277 UTC [35] LOG:  redo starts at 899D/90912B30
timescaledb-timescaledb-1  | 2024-01-22 23:57:44.277 UTC [35] LOG:  invalid record length at 899D/90914518: expected at least 24, got 0

This error can be fixed by truncating the table sometime.

[develop7]

I'd suggest to report an issue against timescaledb instead, because database server should not segfault, period.

[wolfgangwalther]

Interesting that there are two reports of this happening now with #3138 and this. Once it's the pgpro fork and once with the timescaledb extension - although other factors could play into this as well.

@LaurenceChau are you running stock postgres 16.1 or any fork? Are you using any other extensions or just timescaledb?

[LaurenceChau]

Interesting that there are two reports of this happening now with #3138 and this. Once it's the pgpro fork and once with the timescaledb extension - although other factors could play into this as well.

@LaurenceChau are you running stock Postgres 16.1 or any fork? Are you using any other extensions or just timescaledb?

The problem can be fixed by truncating timescaledb hypertable. Not sure if it is related to synchronous_commit, disabled it before.

I am running postgreSQL16.1 and using timescaledb extensions only. Try to enable debug log and turn on synchronous_commit if I can get more findings.

When Segfault occurred, connecting through a psql client would not cause any problem, however connect through postrest will cause the segfault again. I guess a new postgrest connection may trigger a db bug?

[LaurenceChau]

Interesting that there are two reports of this happening now with #3138 and this. Once it's the pgpro fork and once with the timescaledb extension - although other factors could play into this as well.
@LaurenceChau are you running stock Postgres 16.1 or any fork? Are you using any other extensions or just timescaledb?

The problem can be fixed by truncating timescaledb hypertable. Not sure if it is related to synchronous_commit, disabled it before.

I am running postgreSQL16.1 and using timescaledb extensions only. Try to enable debug log and turn on synchronous_commit if I can get more findings.

When Segfault occurred, connecting through a psql client would not cause any problem, however connect through postrest will cause the segfault again. I guess a new postgrest connection may trigger a db bug?

Upgraded to timescaledb 2.13.1. It looks like the error has gone.