Re: Postgres 12.1 : UPPER() in WHERE clause restarts server - Mailing list pgsql-general

Marc <postgres@arcict.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@arcict.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.consulting@comcast.net