Re: Consistent segfault in complex query

>>>>> "Kyle" == Kyle Samson <kysamson(at)tripadvisor(dot)com> writes:

Kyle> Hello,

Kyle> We encountered a query that has been able to frequently segfault
Kyle> one of our postgres instances under certain conditions which we
Kyle> have not fully been able to isolate for reproduction. We were
Kyle> able to get a core dump out of one of the crashes and have poked
Kyle> at it, but we believe the answer is beyond our knowledge of
Kyle> postgres internals. This is on a 9.3.19 server and we saw no
Kyle> mention of a fix in the release notes since this version and we
Kyle> do not know if it affects later major releases as well.

There's a relevant commit from Feb this year (ea6d67cf8) specifically
referring to the case of CTEs inside subplans inside EvalPlanQual, which
is exactly the scenario you have in your query. So you need to try this
in 9.3.22 or later (ideally 9.3.24, the latest) which contain this fix.

This is the relevant release note:

* Fix misbehavior of concurrent-update rechecks with CTE references
appearing in subplans (Tom Lane)

If a CTE (WITH clause reference) is used in an InitPlan or SubPlan,
and the query requires a recheck due to trying to update or lock a
concurrently-updated row, incorrect results could be obtained.

If this is indeed the problem, you may be able to narrow down the
required conditions more tightly: the problem will occur only if the row
to be updated was concurrently updated by another transaction. This
shouldn't be too hard to arrange - update a row in another transaction
but don't commit it yet, run the failing update statement such that it
will update that same row (it will block), then commit the first update.

--
Andrew (irc:RhodiumToad)