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 problem down 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

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, 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, but it certainly 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 a segmentation 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(character varying),"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(),"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;",<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,CALL proc_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,CALL proc_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 transaction and 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=65585 kB
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 problem down 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

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, but it 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

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, but it 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