Adrian, Christoph, Tom,
We identified as the problem being persistent on all tables with many
records ( +600K ) and they all had a JSONB column ( we feel that might
be related )
Luckily we were able to downgraded to version 11.6 with the same system
MacOS 10.14.6 so that the OS impact can ruled out.
We will keep the 12.1 in place so that we can run additional tests to
assist to pin-point the issue.
Feel free to ask but allow us to recover from these hectic days ;-)
Many thanks for the help !
Marc
On 8 Feb 2020, at 21:09, Nick Renders wrote:
Hi,
We have just upgraded our Postgres 9.6 database to 12.1 (pg_dumpall ->
pg_restore on a clean installation) and now we are having some issues
with one of our tables.
When we do the following statement:
SELECT * FROM f_gsxws_schedule WHERE UPPER(gwsc_dossier) = 'TEST'
the Postgres service restarts.
It seems that using UPPER() in the WHERE clause is causing this. The
same statement without UPPER() works just fine.
I have tried to emulate the issue with other tables, but
f_gsxws_schedule seems to be the only one.
The table also has another character field that is indexed, and the
same problem occurs there. Whenever we use UPPER() or LOWER() to do a
case-insensitive search, the service reboots.
Looking at the table's definition, I don't see anything different with
the other tables.
Here is what is logged:
2020-02-08 20:21:19.942 CET [83892] LOG: server process (PID 85456)
was terminated by signal 9: Killed: 9
2020-02-08 20:21:19.942 CET [83892] DETAIL: Failed process was
running: SELECT * FROM f_gsxws_schedule WHERE UPPER(gwsc_dossier) =
'TEST'
2020-02-08 20:21:19.942 CET [83892] LOG: terminating any other active
server processes
2020-02-08 20:21:19.943 CET [85364] WARNING: terminating connection
because of crash of another server process
2020-02-08 20:21:19.943 CET [85364] DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-08 20:21:19.943 CET [85364] HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-08 20:21:19.943 CET [85360] WARNING: terminating connection
because of crash of another server process
2020-02-08 20:21:19.943 CET [85360] DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-08 20:21:19.943 CET [85360] HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-08 20:21:19.943 CET [85269] WARNING: terminating connection
because of crash of another server process
2020-02-08 20:21:19.943 CET [85269] DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-08 20:21:19.943 CET [85269] HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-08 20:21:19.946 CET [83892] LOG: all server processes
terminated; reinitializing
2020-02-08 20:21:19.988 CET [85686] LOG: database system was
interrupted; last known up at 2020-02-08 20:20:48 CET
2020-02-08 20:21:20.658 CET [85686] LOG: database system was not
properly shut down; automatic recovery in progress
2020-02-08 20:21:20.662 CET [85686] LOG: redo starts at C/B99B45A0
2020-02-08 20:21:20.662 CET [85686] LOG: invalid record length at
C/B99B4688: wanted 24, got 0
2020-02-08 20:21:20.662 CET [85686] LOG: redo done at C/B99B4650
2020-02-08 20:21:20.675 CET [83892] LOG: database system is ready to
accept connections
Has anyone noticed anything like this before? Any idea how to fix
this?
Best regards,
Nick Renders
ARC - your Apple service partner
