Обсуждение: BUG #15109: Unhelpful error message on type mismatch
The following bug has been logged on the website:
Bug reference: 15109
Logged by: Bernhard Reutner-Fischer
Email address: rep.dot.nop@gmail.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?):
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)
or mockup:
psql:xxx.sql:19: ERROR: type of parameter at (1) does not match that when
preparing the plan at (2)
CONTEXT: PL/pgSQL function xxx(boolean) line 14 at (3)
7 IF flag = true THEN
8 SELECT 1::bigint AS i
----------------(1)-^
9 INTO row;
10 ELSE
11 SELECT 1::text AS i
----------------(2)-^
12 INTO row;
13 END IF;
14 RETURN JSONB_BUILD_OBJECT('foo', row.i);
-------------------------------------------(3)-^
Or the like.
This is of course not json-specific.
The following bug has been logged on the website:
Bug reference: 15109
Logged by: Bernhard Reutner-Fischer
Email address: rep.dot.nop@gmail.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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Mar 13, 2018 at 1:46 PM, PG Bug reporting form <
> noreply@postgresql.org> wrote:
>> 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
> 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.
Ideally, of course, we'd like to not see this error at all, at least not
in the context of plpgsql --- it should auto-replan affected statements
when a record column changes type. That's been on the to-do list for a
long time, though, and hasn't gotten done. When it does get done it's
unlikely to be something we'd risk back-patching.
So you could call this a bug if you want, but when it does get fixed
it'll be treated as a new feature instead.
regards, tom lane