Re: plpgsql plan changes causing failure after repeated invocation

Merlin Moncure wrote:
> 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:

[...]
> (_plpgsql_var = 'yyy' and q.data::int = foo.foo_id)
[...]

> 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.

This must be the "custom plan" feature added in 9.2 switching over to
a generic plan after 5 executions.

But you are right, it is not nice.

Yours,
Laurenz Albe