postgresql12-plpython: python3.6m crash database after ssh session close

Hi,

I just received this bug report for postgresql 12 & plpython. There seems to be a problem with the default python-3.6 package for FreeBSD together with plpython. Any ideas how to pursue this?

https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=241951 <https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=241951>

Palle

--

12.0-RELEASE-p10
PostgreSQL 12

In pkg and ports postgresql uses libpython3.6m.so.1.0 by default and this is
buged for plpython I think.

Problem scenario:

1. Connect via ssh or use ssh tunnel via pgadmin4
2. Execute any plpython3u function via psql or pgadmin4 - it's will be OK
3. Close ssh session
4. Repeat steps 1-2 and now postgresql server will crash and autorecovery on
every call to function which use plpython3u until manualy "service postgresql
restart"

Some digging:

After each crash in log file:

LOG: server process (PID 17414) was terminated by signal 6: Abort trap
DETAIL: Failed process was running: select pyver();
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 0/1787B98
LOG: invalid record length at 0/1787BD0: wanted 24, got 0
LOG: redo done at 0/1787B98
LOG: database system is ready to accept connections

Using gdb and postgres.core can see:

Program terminated with signal SIGABRT, Aborted.
#0 0x000000080142745a in thr_kill () from /lib/libc.so.7
(gdb) bt
#0 0x000000080142745a in thr_kill () from /lib/libc.so.7
#1 0x0000000801425844 in raise () from /lib/libc.so.7
#2 0x0000000801398079 in abort () from /lib/libc.so.7
#3 0x000000080b103535 in Py_FatalError () from
/usr/local/lib/libpython3.6m.so.1.0
#4 0x000000080b1032eb in _Py_InitializeEx_Private () from
/usr/local/lib/libpython3.6m.so.1.0
#5 0x000000080af0c920 in PLy_initialize () from
/usr/local/lib/postgresql/plpython3.so
#6 0x000000080af0caaa in plpython3_call_handler () from
/usr/local/lib/postgresql/plpython3.so
#7 0x000000000063cf90 in ?? ()
#8 0x000000000066bad8 in ?? ()
#9 0x0000000000643c2d in standard_ExecutorRun ()
#10 0x000000000079d68d in ?? ()
#11 0x000000000079d272 in PortalRun ()
#12 0x000000000079c247 in ?? ()
#13 0x000000000079a27d in PostgresMain ()
#14 0x000000000071f836 in ?? ()
#15 0x000000000071eef0 in ?? ()
#16 0x000000000071c099 in PostmasterMain ()
#17 0x00000000006906de in main ()

Maybe problem in python version with "m" which mean compiled with
"--with-pymalloc", some version of memory allocation different from default.
Can't explain how ssh session affect on this.

I can fix it only with using different python version on make step

make PYTHON_VERSION=python3.8 install

---

I recevied this reply from Tom Lane:

That's just bizarre --- how could a previous session affect the state
of Python inside a new session? Is it possible the submitter put
plpython into shared_preload_libraries?

Is this reproducible?

The stack trace reminds me of
https://www.postgresql.org/message-id/flat/25662.1560896200%40sss.pgh.pa.us <https://www.postgresql.org/message-id/flat/25662.1560896200%40sss.pgh.pa.us>
although that's probably a red herring, because the mechanism there
seems quite NetBSD-specific.

regards, tom lane