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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers