plpgsql plan changes causing failure after repeated invocation

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: plpgsql plan changes causing failure after repeated invocation
Date: 2014-11-11 14:15:59
Message-ID: CAHyXU0xdzPAC2ofVDP9tbsfA=7YPqVEmoKY0LTyg4UeEm5xu9g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I chased down a problem today where users were reporting sporadic
failures in the application. Turns out, the function would work
exactly 5 times and then fail; this is on 9.2. I think I understand
why this is happening and I'm skeptical it's a bug in postgres, but I
thought I'd socialize it.

What's happening here is a query structured like this, somewhat deep
into a pl/pgsql function:

SELECT row_to_json(q) FROM
(
SELECT *
FROM
(
<complex_inner_query>
) q
LEFT JOIN foo f ON
_plpgsql_var != 'xxx'
AND (
(_plpgsql_var = 'yyy' and q.data::int = foo.foo_id)
OR (_plpgsql_var = 'zzz' and q.data = _other_var)
)
) q;

What is happening, along with some triggers I don't completely
understand (this problem started hitting when I made an unrelated
change in the function) is that the cast (q.data::int) started to
fail. In cases where _plpgsql_var is not 'yyy', the cast was getting
applied where previously it did not.

The workaround was simple, insert a case statement so that q.data::int
becomes CASE WHEN _plpgsql_var = 'yyy' THEN q.data::int ELSE NULL END.
That being said, it does bring up some interesting points.

*) relying on A being checked first in 'A OR B' is obviously not
trustworthy, and it shouldn't be. Generally I assume the planner will
do the cheaper of the two first (along with some extra encouragement
to put it on the left side), but this can't be relied upon.

*) It's possible to write queries so that they will fail depending on
plan choice. This is not good, and should be avoided when possible
(the query isn't great I'll admit), but the interaction with execution
count is a little unpleasant.

merlin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Albe Laurenz 2014-11-11 14:28:08 Re: plpgsql plan changes causing failure after repeated invocation
Previous Message Robert Haas 2014-11-11 13:31:01 Re: [v9.5] Custom Plan API