Обсуждение: plpgsql: Plan type mismatch error
Hi hackers, I recently ran afoul of the following error message: ERROR: type of "varname" does not match that when preparing the plan IMO the message isn't quite in English and doesn't explain the problem very well. I'd like to change it to something more like ERROR: the type of "varname" does not match the type expected by the planner HINT: Ensure that the type does not change between function calls, or use EXECUTE instead Comments? Regards, BJ
"Brendan Jurd" <direvus@gmail.com> writes:
> I recently ran afoul of the following error message:
> ERROR: type of "varname" does not match that when preparing the plan
> IMO the message isn't quite in English and doesn't explain the problem
> very well.
The English is fine. What I want to know about is whether this was a
current release, and if so how you provoked it.
regards, tom lane
On 10/16/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Brendan Jurd" <direvus@gmail.com> writes: > > I recently ran afoul of the following error message: > > ERROR: type of "varname" does not match that when preparing the plan > > IMO the message isn't quite in English and doesn't explain the problem > > very well. > > The English is fine. What I want to know about is whether this was a > current release, and if so how you provoked it. This was in 8.3 beta 1. I provoked the message by having a variable which came from a dynamic query (EXECUTE .. INTO a variable of type RECORD), and a member of that record changed type between one execution of the function and the next. So the plan was cached on the first execution, and in the second execution the type of the variable did not match the type in the plan.At least, that's what I understand happened from lookingat the code which emitted the message. Once I knew what the message was talking about, fixing my function was easy. It's a useful error message, it's just not well articulated ... Cheers, BJ
2007/10/16, Brendan Jurd <direvus@gmail.com>: > On 10/16/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Brendan Jurd" <direvus@gmail.com> writes: > > > I recently ran afoul of the following error message: > > > ERROR: type of "varname" does not match that when preparing the plan > > > IMO the message isn't quite in English and doesn't explain the problem > > > very well. > > > > The English is fine. What I want to know about is whether this was a > > current release, and if so how you provoked it. > > This was in 8.3 beta 1. I provoked the message by having a variable > which came from a dynamic query (EXECUTE .. INTO a variable of type > RECORD), and a member of that record changed type between one > execution of the function and the next. > > So the plan was cached on the first execution, and in the second > execution the type of the variable did not match the type in the plan. > At least, that's what I understand happened from looking at the code > which emitted the message. > > Once I knew what the message was talking about, fixing my function was > easy. It's a useful error message, it's just not well articulated ... > > Cheers, > BJ > please, read: http://www.pgsql.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL Pavel
On 10/16/07, Pavel Stehule <pavel.stehule@gmail.com> wrote: > please, read: > http://www.pgsql.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL > Thanks Pavel, I actually came across that wiki article via Google when I was first trying to learn more about the message. But, I'm not asking for help understanding how plan caching works. As mentioned, I've already fixed the problem with my function. Rather, I'm suggesting that we increase the helpfulness of the error message. BJ