When I execute EXPLAIN(ANALYZE, VERBOSE, BUFFERS) in a certain SQL statement, I get “server closed the connection unexpectedly”

December 13, 2021December 23, 2021

I am running postgres with docker-compose, but when I execute EXPLAIN(ANALYZE, VERBOSE, BUFFERS) with the following SQL statement, I get the following error.

At the same time, I looked at the CPU and memory status, but there didn’t seem to be any problems.

I use pgroonga for image, which is a postgres image with postgres extensions for fast full-text search.

version: "3"

services:
  db:
    image: groonga/pgroonga:latest
    volumes:
      - postgres_data:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=postgres
    ports:
      - "5432:5432"
    user: postgres

volumes:
  postgres_data:

EXPLAIN(ANALYZE, VERBOSE, BUFFERS)
SELECT DISTINCT "videos_video"."id",
                "videos_video"."published_at"
FROM "videos_video"
LEFT OUTER JOIN "videos_video_tags" ON ("videos_video"."id" = "videos_video_tags"."video_id")
LEFT OUTER JOIN "videos_tag" ON ("videos_video_tags"."tag_id" = "videos_tag"."id")
WHERE ("videos_video"."is_public"
       AND "videos_video"."published_at" <= '2021-12-22 09:10:41.917576+00:00'
       AND ("videos_video"."title" &@~ 'word'
            OR "videos_tag"."name" &@~ 'word'))
ORDER BY "videos_video"."published_at" DESC
LIMIT 20;

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.

postgres logs

db_1        | 2021-12-23 07:00:14.637 UTC [1] LOG:  server process (PID 44) was terminated by signal 6: Aborted
db_1        | 2021-12-23 07:00:14.637 UTC [1] DETAIL:  Failed process was running: EXPLAIN(ANALYZE, VERBOSE, BUFFERS)
db_1        |   SELECT DISTINCT "videos_video"."id",
db_1        |                   "videos_video"."published_at"
db_1        |   FROM "videos_video"
db_1        |   LEFT OUTER JOIN "videos_video_tags" ON ("videos_video"."id" = "videos_video_tags"."video_id")
db_1        |   LEFT OUTER JOIN "videos_tag" ON ("videos_video_tags"."tag_id" = "videos_tag"."id")       
db_1        |   WHERE ("videos_video"."is_public"
db_1        |          AND "videos_video"."published_at" <= '2021-12-22 09:10:41.917576+00:00'
db_1        |          AND ("videos_video"."title" &@~ 'word'
db_1        |               OR "videos_tag"."name" &@~ 'word'))
db_1        |   ORDER BY "videos_video"."published_at" DESC
db_1        |   LIMIT 20;
db_1        | 2021-12-23 07:00:14.637 UTC [1] LOG:  terminating any other active server processes        
db_1        | 2021-12-23 07:00:14.638 UTC [47] FATAL:  the database system is in recovery mode
db_1        | 2021-12-23 07:00:14.639 UTC [1] LOG:  all server processes terminated; reinitializing      
db_1        | 2021-12-23 07:00:14.667 UTC [48] LOG:  database system was interrupted; last known up at 2021-12-23 06:58:49 UTC
db_1        | 2021-12-23 07:00:14.955 UTC [48] LOG:  database system was not properly shut down; automatic recovery in progress
db_1        | 2021-12-23 07:00:14.959 UTC [48] LOG:  redo starts at 0/42CD9C50
db_1        | 2021-12-23 07:00:14.961 UTC [48] LOG:  invalid record length at 0/42D1A5B0: wanted 24, got 0
db_1        | 2021-12-23 07:00:14.961 UTC [48] LOG:  redo done at 0/42D1A578 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
db_1        | 2021-12-23 07:00:15.005 UTC [1] LOG:  database system is ready to accept conn

Indocker-compose, postgresql, sql