When I execute EXPLAIN(ANALYZE, VERBOSE, BUFFERS) in a certain SQL statement, I get “server closed the connection unexpectedly”
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