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

Marc <postgres(at)arcict(dot)com> writes:

> 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 )

Did you remember to re-analyze all tables after importing the data?

Autovac probably will have done it for you for objects non-trivial in
size, but it's worth asking.

Such an omission could certainly result in poor exec plans, large memory
use and in turn automated intervention.

FWIW

>
> 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 Authorised Service H.D. Saviolaan 8
> partner
> B-1700 Dilbeek
> Belgium
> info(at)arcict(dot)com www.arcict.com
> tel. : +32 (0)2 466 50 00 fax. : +32 (0)2 466 88 33
>
>
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net