Postgres 12.1 : UPPER() in WHERE clause restarts server

From: "Nick Renders" <postgres(at)arcict(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Postgres 12.1 : UPPER() in WHERE clause restarts server
Date: 2020-02-08 20:09:07
Message-ID: CEF2C288-13E6-4727-81D0-0775F40F313B@arcict.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2020-02-08 20:16:48 Re: Postgres 12.1 : UPPER() in WHERE clause restarts server
Previous Message Peter Geoghegan 2020-02-08 01:07:49 Re: Query returns no results until REINDEX