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

ARC - your Apple service partner