Re: BUG #15109: Unhelpful error message on type mismatch

On Tue, Mar 13, 2018 at 1:46 PM, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15109
> Logged by: Bernhard Reutner-Fischer
> Email address: rep(dot)dot(dot)nop(at)gmail(dot)com
> PostgreSQL version: 10.3
> Operating system: linux
> Description:
>
> Hi!
>
> wishlist item:
> ---8<---
> CREATE OR REPLACE FUNCTION xxx(flag boolean)
> RETURNS JSONB LANGUAGE plpgsql AS
> $$
> DECLARE
> row record;
> BEGIN
> IF flag = true THEN
> SELECT 1::bigint AS i
> INTO row;
> ELSE
> SELECT 1::text AS i
> INTO row;
> END IF;
> RETURN JSONB_BUILD_OBJECT('foo', row.i);
> END
> $$;
>
> select xxx(false);
> select xxx(true);
> ---8<---
> CREATE FUNCTION
> xxx
> --------------
> {"foo": "1"}
> (1 row)
>
> psql:xxx.sql:19: ERROR: type of parameter 4 (bigint) does not match that
> when preparing the plan (text)
> CONTEXT: PL/pgSQL function xxx(boolean) line 12 at RETURN
>
> Would be nice to give more context what "parameter 4" actually is, i.e.
> also
> print the name or underline the exact line the error occurs in or the like
> (and how comes we talk about line 12 and not 14?):
>

​Once you understand the error you will see it is indeed on line 12, count
them including the line with the starting $$

The error itself comes up not because of the "SELECT 1" but because of
"row.i" being passed into jsonb_build_object('foo', $#) - in one pass row.i
is text then the next invocation of the now-cached function-plan it is a
bigint. That row.i changes types is allowed by design - its this specific
usage of row.i that is problematic.

Parameters don't have names, just numbers, so finding a name to pass back
is definitely non-trivial.

mockup:
> psql:xxx.sql:19: ERROR: type of parameter 4 (1::bigint AS i) does not match
> that when preparing the plan (1::text AS i)
>

As noted above, since that isn't where the actual error is occurring a
suggestion like this is even not helpful since you not only want a better
error message but you want something that is not inherently an error (i.e.,
polymorphic record types) to become one just so push the error message
further up the procedure. That isn't acceptable. And tracing from the
statement retrieving "row.i" back to those two statements that set row is
likely impossible.​

In short - this isn't a bug report so this isn't the right list for the
discussion (that would be -general), and while the frustration is real the
proposed alternatives do not seem realistic.

David J.
​