From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Tyler Brock <tyler.brock@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Segfault during queries
Date: Fri, 29 Oct 2021 08:27:45 -0600
Message-ID: <C261D8C5-C42D-460E-8BA8-30A3D50835FA@elevated-dev.com> (raw)
In-Reply-To: <CACr_h8TsU0Swpa=0OcXUbHx69BNBeyYJf6B-q-sdEkTYY7rHPg@mail.gmail.com>
References: <CACr_h8TsU0Swpa=0OcXUbHx69BNBeyYJf6B-q-sdEkTYY7rHPg@mail.gmail.com>
Of course I cannot say if this is your case, but I've been using PG since 2004 and every single instance* of a segfault has turned out to be caused by some 3rd-party extension or other. And yes, the extension was never related in any way to the query being run, it just corrupted memory such that PG would die later. So I would audit extensions, and see if any have been recently added, any could be removed temporarily, and so on.
* It's only 2 or 3 cases in all that time, but still, it was never core PG.
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/> On Oct 29, 2021, at 8:05 AM, Tyler Brock <tyler.brock@gmail.com> wrote:
>
> Hi all,
>
> I keep getting segfaults when running queries against postgresql replicas. We have an ETL job that hits these nodes with many small queries (no more than 8 concurrently) and the box has 16 cores and plenty of I/O and ram.
>
> However, during this process postgresql segfaults, usually on COPY commands that have this shape:
>
> COPY
> (
> SELECT row_to_json(t)
> FROM (
> SELECT lead_tag."objectId" AS "lead_id",
> lead_tag."tag" AS "tag_id"
> FROM (
> SELECT "objectId",
> jsonb_array_elements_text("tags") AS tag
> FROM "Lead"
> WHERE true
> AND "Lead"."organization" = 'I6JDWAaZx5'
> AND tags IS NOT NULL
> AND "Lead"."updatedAt" >= '2015-01-01'
> AND "Lead"."updatedAt" < '2021-10-30T11:05:40.389773+00:00' ) AS lead_tag) t) TO stdout
>
> Is there anything I can do to prevent this or anything i can look at to try and diagnose what is happening here? I’m running Postgres 12.7 and cannot tell if this is a symptom of a bug or just misconfiguration.
>
> What I see in the error logs is:
>
> 2021-10-29 11:08:10 UTC:172.23.17.171(54332):postgres@postgres:[22200]:ERROR: cache lookup failed for type 0
> 2021-10-29 11:08:10 UTC:172.23.17.171(54332):postgres@postgres:[22200]:STATEMENT: COPY (SELECT ROW_TO_JSON(t) FROM (… the query above...) TO STDOUT
> 2021-10-29 11:08:10 UTC::@:[19406]:LOG: server process (PID 22200) was terminated by signal 11: Segmentation fault
>
>
> Any help would be greatly appreciated, thanks!
>
> -Tyler
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, tyler.brock@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Segfault during queries
In-Reply-To: <C261D8C5-C42D-460E-8BA8-30A3D50835FA@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox