Обсуждение: BUG #16690: database connection aborted with a signal 9 when query is run on MacOS Catalina

The following bug has been logged on the website:

Bug reference:      16690
Logged by:          Mark Phillips
Email address:      mark.phillips@mophilly.com
PostgreSQL version: 12.1
Operating system:   Various, see note.
Description:

The following query works on...
Windows 10 Education Build 1909 18363.1139 PostgreSQL 12.1, compiled by
Visual C++ build 1914, 64-bit
Mojave 10.14.6 PostgreSQL 12.1 on x86_64-apple-darwin, compiled by Apple
LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit

but causes a database connection to be aborted (signal 9) when run on...
Catalina 10.15.6 PostgreSQL 12.1 on x86_64-apple-darwin, compiled by Apple
LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit

The Query:
SELECT distinct(a.division_order_uid)
FROM division_order A
JOIN division_order divo ON divo.division_order_uid = a.division_order_uid
LEFT JOIN note_division_order an ON an.division_order_uid =
a.division_order_uid
JOIN legal_desc_div land ON a.division_order_uid = land.division_order_uid
LEFT JOIN gpcounty gc ON gc.ctnum = a.ctnum
LEFT JOIN gpstate gs ON gs.stnum = gc.stnum
LEFT JOIN customer dic ON  dic.customer_uid = divo.customer_uid
LEFT JOIN customer cst ON  cst.customer_uid = divo.customer_uid
LEFT JOIN customer doo ON doo.customer_uid = divo.operator_uid
LEFT JOIN customer dol ON dol.customer_uid = divo.lessee_id
LEFT JOIN division_title dt ON dt.legal_desc_div_uid =
land.legal_desc_div_uid
LEFT JOIN legal_desc_title ldt ON ldt.legal_desc_title_uid =
dt.legal_desc_title_uid
LEFT JOIN legal_title lt ON lt.legal_title_uid = ldt.legal_title_uid
LEFT JOIN doc_type ttp ON ttp.doc_type_code = lt.ttl_type
LEFT JOIN division_lease dl ON dl.legal_desc_div_uid =
land.legal_desc_div_uid
LEFT JOIN legal_desc_lease ldl ON ldl.legal_desc_lease_uid =
dl.legal_desc_lease_uid
LEFT JOIN lease l ON l.lease_uid = ldl.lease_uid
LEFT JOIN doc_type ltp on ltp.doc_type_uid = l.doc_type_uid
LEFT JOIN well_division wd ON wd.legal_desc_div_uid =
land.legal_desc_div_uid
LEFT JOIN legal_desc_well ldw ON ldw.legal_desc_well_uid =
wd.legal_desc_well_uid
LEFT JOIN well w ON w.well_uid = ldw.well_uid
WHERE  upper(cst.name) ilike E'%M%'

PG Bug reporting form <noreply@postgresql.org> writes:
> The following query works on...
> Windows 10 Education Build 1909 18363.1139 PostgreSQL 12.1, compiled by
> Visual C++ build 1914, 64-bit
> Mojave 10.14.6 PostgreSQL 12.1 on x86_64-apple-darwin, compiled by Apple
> LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit

> but causes a database connection to be aborted (signal 9) when run on...
> Catalina 10.15.6 PostgreSQL 12.1 on x86_64-apple-darwin, compiled by Apple
> LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit

Not a lot we can do with that level of detail.  Can you provide a
self-contained test case?  (That is, besides the query, we need the
table definitions and some possibly-dummy data.  Ideally, provide a
SQL script that provokes the crash starting from an empty database.)

It'd also be quite useful to know what build options you used.
Notably, I'm wondering about whether you enabled JIT (--with-llvm).

            regards, tom lane

... BTW, if this is EDB's build rather than something you built
yourself, it might be the issue described here:

https://www.postgresql.org/message-id/flat/CAM9w-_%3DnArgTfn6jSgVCEkUo%3DzoMzGVvZHQRmox74c93vY3QMA%40mail.gmail.com

            regards, tom lane

To close the loop on this.

That bug was indeed at the heart of the issue. We tested the app and query running Postgres 12.4 and the problem did
notoccur. 

Thanks to all who responded.

> On Oct 29, 2020, at 2:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> ... BTW, if this is EDB's build rather than something you built
> yourself, it might be the issue described here:
>
> https://www.postgresql.org/message-id/flat/CAM9w-_%3DnArgTfn6jSgVCEkUo%3DzoMzGVvZHQRmox74c93vY3QMA%40mail.gmail.com
>
>             regards, tom lane