Re: Postgres 12.1 : UPPER() in WHERE clause restarts server

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