Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\))
Subject: Re: Segfault during queries
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CACr_h8TsU0Swpa=0OcXUbHx69BNBeyYJf6B-q-sdEkTYY7rHPg@mail.gmail.com>
Date: Fri, 29 Oct 2021 08:27:45 -0600
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <C261D8C5-C42D-460E-8BA8-30A3D50835FA@elevated-dev.com>
References: 
 <CACr_h8TsU0Swpa=0OcXUbHx69BNBeyYJf6B-q-sdEkTYY7rHPg@mail.gmail.com>
To: Tyler Brock <tyler.brock@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/C261D8C5-C42D-460E-8BA8-30A3D50835FA%40elevated-dev.com>
Precedence: bulk

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:
>=20
> Hi all,
>=20
> 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.
>=20
> However, during this process postgresql segfaults, usually on COPY =
commands that have this shape:
>=20
> 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" =3D =
'I6JDWAaZx5'
>                                    AND    tags IS NOT NULL
>                                    AND    "Lead"."updatedAt" >=3D =
'2015-01-01'
>                                    AND    "Lead"."updatedAt" < =
'2021-10-30T11:05:40.389773+00:00' ) AS lead_tag) t) TO stdout
>=20
> Is there anything I can do to prevent this or anything i can look at =
to try and diagnose what is happening here? I=E2=80=99m running Postgres =
12.7 and cannot tell if this is a symptom of a bug or just =
misconfiguration.
>=20
> What I see in the error logs is:
>=20
> 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 (=E2=80=A6 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
>=20
>=20
> Any help would be greatly appreciated, thanks!
>=20
> -Tyler