Обсуждение: psql crash when running a procedure with an inout parameter and a commit
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
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
От
Chuck Nellis
Дата:
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
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
От
Chuck Nellis
Дата:
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