Thread: psql crash when running a procedure with an inout parameter and a commit

psql crash when running a procedure with an inout parameter and a commit

From

Chuck Nellis

Date:

16 August 2021, 15:14:57

PostgreSQL folks,

I’d like to report a bug I encountered while running psql on PostgreSQL version 12.5, on RHEL 7.9. I decomposed the problem down to this small example:

CREATE OR REPLACE PROCEDURE proc
(
a_string INOUT VARCHAR
)
AS $$
<< proc >>
DECLARE
val BIGINT;
BEGIN

COMMIT;
SELECT 0 INTO val;

END proc;
$$
LANGUAGE PLPGSQL;

CREATE OR REPLACE PROCEDURE proc_caller()
AS $$
<< proc_caller >>
DECLARE
some_string VARCHAR := 'some_string';
BEGIN

CALL proc(some_string);

END proc_caller;
$$
LANGUAGE PLPGSQL;

CALL proc_caller();

Upon running the final line, psql terminates with the following error:

SSL SYSCALL error: EOF detected

connection to server was lost

A few other observations I’ve made:

Changing the a_string parameter from an INOUT type to IN type prevents the psql crash
Removing the COMMIT prevents the psql crash
Swapping the order of the SELECT and COMMIT prevents the psql crash

Thanks,

Chuck

Re: psql crash when running a procedure with an inout parameter and a commit

From

Tom Lane

Date:

16 August 2021, 16:48:24

Chuck Nellis <crnellis@raytheon.com> writes:
> I'd like to report a bug I encountered while running psql on PostgreSQL version 12.5, on RHEL 7.9.   I decomposed the
problemdown to this small example: 
> ...

I tried this on current code (12.8) and I don't see any crash.
So I suppose this is something we already fixed; please update.

            regards, tom lane

RE: Re: psql crash when running a procedure with an inout parameter and a commit

From

Chuck Nellis

Date:

16 August 2021, 17:24:52

Tom,

Thanks for the quick response.   Unfortunately, I'm not able to upgrade to 12.8 at this time.  I am running on AWS RDS,
whichonly supports up to 12.7.   I may be able to upgrade from 12.5 to 12.7 sometime in the not-to-distant future, but
itcertainly won't happen for at least a few weeks. 

I did get a look at the server log, and I captured this which may be of interest.  It seems to indicate that there was
asegmentation fault in a server process: 

2021-08-16 11:14:23 UTC:XX.XXX.XX.XXX(60984):user1@dbserver001:[26547]:LOG: AUDIT: SESSION,1,1,DDL,CREATE
PROCEDURE,PROCEDURE,user1.proc(charactervarying),"CREATE OR REPLACE PROCEDURE proc 
(
a_string INOUT VARCHAR
)
AS $$
<< proc >>
DECLARE
val BIGINT;
BEGIN

COMMIT;
SELECT 0 INTO val;

END proc;
$$
LANGUAGE PLPGSQL;",<none>
2021-08-16 11:14:23 UTC:XX.XXX.XX.XXX(60984):user1@dbserver001:[26547]:LOG: AUDIT: SESSION,2,1,DDL,CREATE
PROCEDURE,PROCEDURE,user1.proc_caller(),"CREATEOR REPLACE PROCEDURE proc_caller() 
AS $$
<< proc_caller >>
DECLARE
some_string VARCHAR := 'some_string';
BEGIN

CALL proc(some_string);

END proc_caller;
$$
LANGUAGE PLPGSQL;",<none>
2021-08-16 11:14:23 UTC:XX.XXX.XX.XXX(60984):user1@dbserver001:[26547]:LOG: AUDIT:
SESSION,3,1,FUNCTION,EXECUTE,FUNCTION,user1.proc_caller,CALLproc_caller();,<none> 
2021-08-16 11:14:23 UTC:XX.XXX.XX.XXX(60984):user1@dbserver001:[26547]:LOG: AUDIT:
SESSION,3,2,FUNCTION,EXECUTE,FUNCTION,user1.proc,CALLproc_caller();,<none> 
2021-08-16 11:14:23 UTC:XX.XXX.XX.XXX(60984):user1@dbserver001:[26547]:LOG: AUDIT: SESSION,3,3,READ,SELECT,,,SELECT
0,<none>
2021-08-16 11:14:23 UTC::@:[26543]:LOG: server process (PID 26547) was terminated by signal 11: Segmentation fault
2021-08-16 11:14:23 UTC::@:[26543]:DETAIL: Failed process was running: CALL proc_caller();
2021-08-16 11:14:23 UTC::@:[26543]:LOG: terminating any other active server processes
2021-08-16 11:14:23 UTC::@:[4300]:WARNING: terminating connection because of crash of another server process
2021-08-16 11:14:23 UTC::@:[4300]:DETAIL: The postmaster has commanded this server process to roll back the current
transactionand exit, because another server process exited abnormally and possibly corrupted shared memory. 
2021-08-16 11:14:23 UTC::@:[4300]:HINT: In a moment you should be able to reconnect to the database and repeat your
command.
2021-08-16 11:14:23 UTC::@:[26543]:LOG: archiver process (PID 4301) exited with exit code 2
2021-08-16 11:14:23 UTC::@:[26543]:LOG: all server processes terminated; reinitializing
2021-08-16 11:14:23 UTC::@:[26548]:LOG: database system was interrupted; last known up at 2021-08-16 11:13:42 UTC
2021-08-16 11:14:23 UTC::@:[26548]:LOG: database system was not properly shut down; automatic recovery in progress
2021-08-16 11:14:23 UTC::@:[26548]:LOG: redo starts at 403/C00007F8
2021-08-16 11:14:23 UTC::@:[26548]:LOG: invalid record length at 403/C400CD78: wanted 24, got 0
2021-08-16 11:14:23 UTC::@:[26548]:LOG: redo done at 403/C400CD10
2021-08-16 11:14:23 UTC::@:[26548]:LOG: checkpoint starting: end-of-recovery immediate
2021-08-16 11:14:23 UTC::@:[26548]:LOG: checkpoint complete: wrote 11 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1
recycled;write=0.007 s, sync=0.003 s, total=0.023 s; sync files=4, longest=0.003 s, average=0.000 s; distance=65585 kB,
estimate=65585kB 
2021-08-16 11:14:23 UTC::@:[26543]:LOG: database system is ready to accept connections

Thanks,
Chuck

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, August 16, 2021 7:48 AM
To: Chuck Nellis <crnellis@raytheon.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: [External] Re: psql crash when running a procedure with an inout parameter and a commit

Chuck Nellis <crnellis@raytheon.com> writes:
> I'd like to report a bug I encountered while running psql on PostgreSQL version 12.5, on RHEL 7.9.   I decomposed the
problemdown to this small example: 
> ...

I tried this on current code (12.8) and I don't see any crash.
So I suppose this is something we already fixed; please update.

            regards, tom lane

Re: psql crash when running a procedure with an inout parameter and a commit

From

Tom Lane

Date:

16 August 2021, 18:19:33

Chuck Nellis <crnellis@raytheon.com> writes:
> Thanks for the quick response.   Unfortunately, I'm not able to upgrade to 12.8 at this time.  I am running on AWS
RDS,which only supports up to 12.7.   I may be able to upgrade from 12.5 to 12.7 sometime in the not-to-distant future,
butit certainly won't happen for at least a few weeks. 

After a quick look at the commit log, it looks like 12.7 would be enough
to fix this specific issue.

            regards, tom lane

RE: Re: psql crash when running a procedure with an inout parameter and a commit

From

Chuck Nellis

Date:

16 August 2021, 19:38:46

Tom,

Good news: we moved up our 12.7 upgrade to this morning, and I verified that this problem was fixed.

Much appreciated.

Thanks,
Chuck

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, August 16, 2021 9:20 AM
To: Chuck Nellis <crnellis@raytheon.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: [External] Re: psql crash when running a procedure with an inout parameter and a commit

Chuck Nellis <crnellis@raytheon.com> writes:
> Thanks for the quick response.   Unfortunately, I'm not able to upgrade to 12.8 at this time.  I am running on AWS
RDS,which only supports up to 12.7.   I may be able to upgrade from 12.5 to 12.7 sometime in the not-to-distant future,
butit certainly won't happen for at least a few weeks. 

After a quick look at the commit log, it looks like 12.7 would be enough to fix this specific issue.

            regards, tom lane