Re: BUG #18463: Possible bug in stored procedures with polymorphic OUT parameters

From: Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
To: drewk(at)cockroachlabs(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #18463: Possible bug in stored procedures with polymorphic OUT parameters
Date: 2024-05-14 15:18:43
Message-ID: 20240514151843.yvq3wlt7n2kechv5@ddolgov.remote.csb
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

> On Tue, May 14, 2024 at 06:45:29AM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18463
> Logged by: Drew Kimball
> Email address: drewk(at)cockroachlabs(dot)com
> PostgreSQL version: 16.3
> Operating system: macOS
> Description:
>
> Hello,
>
> I believe there may be a bug related to stored procedures with
> polymorphic-typed OUT parameters:
>
> CREATE PROCEDURE p(INOUT x ANYELEMENT) LANGUAGE SQL AS $$ SELECT x; $$;
> CALL p(1);
>
> The above example results in an error message "cannot display a value of
> type anyelement", but I would expect it to succeed and output "1". This also
> reproduces with the following stored procedures:
>
> CREATE PROCEDURE p(INOUT x ANYELEMENT) LANGUAGE SQL AS $$ SELECT 1; $$;
> CREATE PROCEDURE p(x ANYELEMENT, OUT y ANYELEMENT) LANGUAGE SQL AS $$ SELECT
> x; $$;
> CREATE PROCEDURE p(x ANYARRAY, OUT y ANYELEMENT) LANGUAGE SQL AS $$ SELECT
> x[1]; $$;
>
> Interestingly, this doesn't seem to reproduce when the OUT param has type
> ANYARRAY. The following example succeeds:
>
> CREATE PROCEDURE p(INOUT x ANYARRAY) LANGUAGE SQL AS $$ SELECT x; $$;
> CALL p(ARRAY[1, 2, 3]);

After looking at this I've got an impression this type of procedures
have to be disallowed in interpret_function_parameter_list. What happens
is that the procedure is created with INOUT anyelement argument and
return type record, because "procedures with output parameters always
return RECORD". I guess this contradicts the way how anyelement has to
be resolved, leading to this behaviour.

At the same time if we try to a function of the same type (INOUT
anyelement argument and returning record), we will get an error right
away:

ERROR: 42P13: function result type must be anyelement because of
OUT parameters

This is I think the behaviour that has to be enforced for procedures as
well. It works for anyarray only because of a side effect that
anyarray_out is allowed, due to some columns in pg_statistic having this
type.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2024-05-14 15:26:15 BUG #18464: Replacing a SQL function silently drops the generated columns that use this function
Previous Message Robert Haas 2024-05-14 14:51:51 Re: BUG #18362: unaccent rules and Old Greek text