Re: What does this error message mean?

>
> The type of that comment field hasn't changed

Oh, and I'm going to slight eat my words, or at least elaborate. That
comment field has been in all the views unchanged. Until Tuesday, though,
the field wasn't being used or referenced in the function. So that line
195 is actually new as of Tuesday. But the function was replaced at the
time, and definitely has generated rows since then.

But thinking about it some more, the function runs one of 5 possible
queries. 4 of them select NULL as comment (no cast), while the fifth (and
the one that caused this error) selects 'a string'. That actually got me
thinking more, and I can now reproduce the error. If I run the query with
any of the NULL comments, and then with the string, the query consistently
fails. And actually if I run the string query first, the other 4 then will
fail for the rest of the session. (Example below.)

I assume this will go away if I change my lazy query and cast my NULLs, but
still wonder if this is something that should be expected to fail?

Thanks,
Ken

--'ASSIST'=untyped NULL comment, 'ASSIST_PRI'=string comment
SELECT * FROM generate_payments ('12/1/2013','ASSIST','3852',sys_user());
(one row returned)
SELECT * FROM generate_payments
('12/1/2013','ASSIST_PRI','3852',sys_user());
ERROR: type of parameter 70 (text) does not match that when preparing the
plan (unknown)
CONTEXT: PL/pgSQL function generate_payments(date,text,integer,integer)
line 195 at assignment

(quit psql, start psql)

SELECT * FROM generate_payments
('12/1/2013','ASSIST_PRI','3852',sys_user());
(one row returned)
SELECT * FROM generate_payments ('12/1/2013','ASSIST','3852',sys_user());
ERROR: type of parameter 70 (unknown) does not match that when preparing
the plan (text)
CONTEXT: PL/pgSQL function generate_payments(date,text,integer,integer)
line 195 at assignment

On Sun, Nov 17, 2013 at 7:03 PM, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

>
> On Sun, Nov 17, 2013 at 6:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
>> > Hi. I got an error message reported to me that I've never seen before,
>> and
>> > I'm not quite sure what it means or what would cause it.
>>
>> > ERROR: type of parameter 70 (text) does not match that when preparing
>> the
>> > plan (unknown) CONTEXT: PL/pgSQL function
>> > generate_payments(date,text,integer,integer) line 195 at assignment
>>
>> I think this must mean that you changed the schema of table tbl_payment
>> during this session, and then re-ran the function. plpgsql isn't as good
>> as it could be about dealing with intra-session changes of composite
>> types. The reference to "parameter 70" seems a bit odd though, it doesn't
>> seem like you have anywhere near 70 variables in that function ...
>
>
>
>> (thinks about it for a bit) Actually it seems more likely that a change
>> in the rowtype of "payment" caused this, ie some change in the output
>> column set of that "final_query" query. Difficult to guess more than
>> that without more context.
>>
>> regards, tom lane
>>
>
> Thanks Tom. If you really mean it that the schema must have changed
> during the session, that seems impossible (er, highly unlikely). The error
> was generated through a web app that doesn't know how to do any schema
> changing.
>
> If there's a broader window, though, schema changes do seem plausible.
> The type of that comment field hasn't changed, but on Friday I did some
> dropping and recreating of both the generate_payments function and the
> views it draws upon. If the function was created before the view existed,
> would that account for this error? (Leaving aside parameter 70, of
> course). It is possible that this row was the first one generated by the
> function since the schema drops/creates on Friday. (It actually looks like
> it was 9 minutes after another row, but that's based on comparing server
> time to a screenshot of a client's desktop with their clock showing, so I
> wouldn't put too much faith in that.)
>
> And if this error was from the Friday schema changes, would it have
> auto-corrected itself so it only happened the one time?
>
> Cheers,
> Ken
>
> --
> AGENCY Software
> A data system that puts you in control
> 100% Free Software
> *http://agency-software.org/ <http://agency-software.org/>*
> ken(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
> to
> learn more about AGENCY or
> follow the discussion.
>

--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/ <http://agency-software.org/>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.