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