Re: Segfault during queries

It doesn’t seem like upgrading plv8 did the trick unfortunately. I’m going
to try and get those stack traces but it may be difficult/impossible
because we are running postgres via AWS RDS and don’t have control of the
underlying VM.

-Tyler

On Oct 29, 2021 at 11:08:57 AM, Tyler Brock <tyler(dot)brock(at)gmail(dot)com> wrote:

> Interesting! Thank you, I am seeing that we have an outdated version of
> plv8 that does not match what we should be running for pg12.x, let me try
> dropping that extension and recreating it with a newer version.
>
> -Tyler
>
>
> On Oct 29, 2021 at 10:27:45 AM, Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
> wrote:
>
>> 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(at)elevated-dev(dot)com
>> https://www.linkedin.com/in/scottribe/
>>
>>
>>
>> On Oct 29, 2021, at 8:05 AM, Tyler Brock <tyler(dot)brock(at)gmail(dot)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(at)postgres:[22200]:ERROR:
>> cache lookup failed for type 0
>>
>> 2021-10-29 11:08:10 UTC:172.23.17.171(54332):postgres(at)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
>>
>>
>>