BUG #14781: server process was terminated by signal 11: Segmentation fault

maksim_karaba@epam.com writes:

We are getting error "server process was terminated by signal 11:
Segmentation fault" and server goes to recovery mode on production system.
It repeats several times a day, always on complicated updates using
postgres_fdw.

Please show the failing query or queries. (If you don't have postmaster
logs showing them, "p debug_query_string" in the core files should get
the info.) Also, please show EXPLAIN VERBOSE plans for the query(s),
as well as schema information (psql \d output would do) for the
referenced tables.

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thank you for your answer so quickly.

(gdb) p debug_query_string
$1 = 0x2c2e748 "DO $$DECLARE\nBEGIN\n PERFORM dwh.load_trade_record_inc();\nEND$$;"

We added logging to the function and found out that the error always occurs on the same update
(in attachments)
Plan explain verbose in attachments

MAKSIM KARABA
Senior Systems Engineer, EPAM
Office: +375 17 389 0100 x 53194 Cell: +375296772871 Email: maksim_karaba@epam.com
Minsk, Belarus (GMT+3) epam.com

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 16, 2017 4:47 PM
To: Maksim Karaba <Maksim_Karaba@epam.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14781: server process was terminated by signal 11: Segmentation fault

maksim_karaba@epam.com writes:

We are getting error "server process was terminated by signal 11:
Segmentation fault" and server goes to recovery mode on production system.
It repeats several times a day, always on complicated updates using
postgres_fdw.

Please show the failing query or queries. (If you don't have postmaster logs showing them, "p debug_query_string" in the core files should get the info.) Also, please show EXPLAIN VERBOSE plans for the query(s), as well as schema information (psql \d output would do) for the referenced tables.

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

regards, tom lane

Maksim Karaba <Maksim_Karaba@epam.com> writes:

We added logging to the function and found out that the error always occurs on the same update
(in attachments)
Plan explain verbose in attachments

The point of my questions was that it's going to be difficult for anyone
to fix this unless we can reproduce the problem. What you've provided
doesn't even begin to make that possible. Please see the advice about
providing self-contained test cases at
https://www.postgresql.org/docs/current/static/bug-reporting.html

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Unfortunately we cannot reproduce this issue on other servers, only on production system.
And we cannot provide internal database info, schema structure and tables info.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 16, 2017 6:10 PM
To: Maksim Karaba <Maksim_Karaba@epam.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14781: server process was terminated by signal 11: Segmentation fault

The point of my questions was that it's going to be difficult for anyone to fix this unless we can reproduce the problem. What you've provided doesn't even begin to make that possible. Please see the advice about providing self-contained test cases at https://www.postgresql.org/docs/current/static/bug-reporting.html

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Maksim Karaba <Maksim_Karaba@epam.com> writes:

Unfortunately we cannot reproduce this issue on other servers, only on production system.
And we cannot provide internal database info, schema structure and tables info.

[ shrug... ] We may just have to wait for somebody to be more
forthcoming.

FWIW, the stack trace seems to indicate that an incorrect plan has been
generated, ie one that has a remote join node without an EPQ recheck
subplan. That mistake in itself is probably pretty deterministic. The
reason you can't reproduce the crash easily is that the lack of a subplan
only manifests as a crash if we enter the EPQ recheck code, and that only
happens if the query tries to update a row that's just been updated by
some concurrent query. So it's not going to crash except under concurrent
load, which probably also explains why the bug wasn't found long ago.

If you want to push this forward rather than wait for somebody else
to hit the problem, you could try adding something like

if (fsplan->scan.scanrelid == 0 && outerPlanState(node) == NULL &&
(estate->es_plannedstmt->commandType != CMD_SELECT ||
estate->es_rowMarks))
elog(WARNING, "foreign join plan lacks EPQ support");

near the beginning of postgresBeginForeignScan and then running your app
on a test server. I'm not sure offhand that the estate filters are
exactly right, but any statement that produces this warning would be
pretty suspect. At that point you could work on sanitizing the query +
tables + test data to get to a publishable test case; you could probably
boil your real query down quite a bit and still get the failure.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Tom Lane wrote:

Maksim Karaba <Maksim_Karaba@epam.com> writes:

Unfortunately we cannot reproduce this issue on other servers, only on production system.
And we cannot provide internal database info, schema structure and tables info.

[ shrug... ] We may just have to wait for somebody to be more
forthcoming.

FWIW, the stack trace seems to indicate that an incorrect plan has been
generated, ie one that has a remote join node without an EPQ recheck
subplan. That mistake in itself is probably pretty deterministic. The
reason you can't reproduce the crash easily is that the lack of a subplan
only manifests as a crash if we enter the EPQ recheck code, and that only
happens if the query tries to update a row that's just been updated by
some concurrent query. So it's not going to crash except under concurrent
load, which probably also explains why the bug wasn't found long ago.

One way to figure out the exact bug is to explore the sequence of WAL
records that leads to the tuple causing the crash; it should be possible
to create a reproducer by writing an isolationtester script that
produces the same WAL sequence. That's how we found the bug fixed in
https://git.postgresql.org/pg/commitdiff/459c64d3227f8 for example.

If you want to push this forward rather than wait for somebody else
to hit the problem, you could try adding something like

if (fsplan->scan.scanrelid == 0 && outerPlanState(node) == NULL &&
(estate->es_plannedstmt->commandType != CMD_SELECT ||
estate->es_rowMarks))
elog(WARNING, "foreign join plan lacks EPQ support");

near the beginning of postgresBeginForeignScan and then running your app
on a test server.

Hmm, is there a reason this cannot be included as a sanity check always?

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Tom Lane wrote:

If you want to push this forward rather than wait for somebody else
to hit the problem, you could try adding something like

if (fsplan->scan.scanrelid == 0 && outerPlanState(node) == NULL &&
(estate->es_plannedstmt->commandType != CMD_SELECT ||
estate->es_rowMarks))
elog(WARNING, "foreign join plan lacks EPQ support");

near the beginning of postgresBeginForeignScan and then running your app
on a test server.

Hmm, is there a reason this cannot be included as a sanity check always?

That's off-the-cuff rather than something I'm sure is correct. But
yeah, I was wondering about pushing something like that into the
standard code.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thanks for pointing to the possible root cause.
Our Dev team finally have figured out what was the reason and fixed it.
The reason was in using postgres_fdw based cursors like

for scr in (select f1, f2 ... from foreign table (postgre_fdw) ) loop

from scr1 in (select .. from foreign table (postgre_fdw) where ... = scr.f1) loop

Compicated update of local table using foreign tables as source

update of foreign table one record /**/
end loop;
end loop;

Dev team has refactored it to use loop based on arrays instead of fdw to reduce time of foreign session

MAKSIM KARABA

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 16, 2017 7:57 PM
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Maksim Karaba <Maksim_Karaba@epam.com>; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14781: server process was terminated by signal 11: Segmentation fault

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Tom Lane wrote:

If you want to push this forward rather than wait for somebody else
to hit the problem, you could try adding something like

if (fsplan->scan.scanrelid == 0 && outerPlanState(node) == NULL &&
(estate->es_plannedstmt->commandType != CMD_SELECT ||
estate->es_rowMarks))
elog(WARNING, "foreign join plan lacks EPQ support");

near the beginning of postgresBeginForeignScan and then running your
app on a test server.

Hmm, is there a reason this cannot be included as a sanity check always?

That's off-the-cuff rather than something I'm sure is correct. But yeah, I was wondering about pushing something like that into the standard code.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs