[YSQL] Cannot create procedure with a bare OUT formal parameter

[bllewell]

Jira Link: DB-1778

Description

I'm using YB-2.13.0.1 on MacOS Big Sur.

See Issue #6133 opened in Oct-2020. It's now closed. Here's the testcase:

drop procedure if exists p(int);
create procedure p(a inout int)
  language plpgsql
as $body$
begin
  a := a + 1;
end;
$body$;
  
do $body$
declare
  a int := 10;
begin
  call p(a);
  raise info '%', a::text;
end;
$body$;

It reports INFO: 11 as expected.

Now derive a test that uses just a bare OUT parameter:

drop procedure if exists p(int);
create procedure p(a out int)
  language plpgsql
as $body$
begin
  a := 42;
end;
$body$;
  
do $body$
declare
  a int := 0;
begin
  call p(a);
  raise info '%', a::text;
end;
$body$;

This runs fine in vanilla PG and reports INFO: 42 as expected. But in YB, the create procedure statement causes this error:

ERROR:  0A000: procedures cannot have OUT arguments
HINT:  INOUT arguments are permitted.
LOCATION:  interpret_function_parameter_list, functioncmds.c:291

Notice that the error code maps to the generic PL/pgSQL exception feature_not_supported.

Will this be a permanent restriction in YB?

[tedyu]

The following two should be backported:

commit 2453ea142233ae57af452019c3b9a443dad1cdd0
Author: Peter Eisentraut <[peter@eisentraut.org](mailto:peter@eisentraut.org)>
Date:   Mon Oct 5 09:09:09 2020 +0200

    Support for OUT parameters in procedures

    Unlike for functions, OUT parameters for procedures are part of the
    signature.  Therefore, they have to be listed in pg_proc.proargtypes
    as well as mentioned in ALTER PROCEDURE and DROP PROCEDURE.

commit e56bce5d43789cce95d099554ae9593ada92b3b7
Author: Tom Lane <[tgl@sss.pgh.pa.us](mailto:tgl@sss.pgh.pa.us)>
Date:   Thu Jun 10 17:11:36 2021 -0400

    Reconsider the handling of procedure OUT parameters.

    Commit 2453ea142 redefined pg_proc.proargtypes to include the types of
    OUT parameters, for procedures only.  While that had some advantages
    for implementing the SQL-spec behavior of DROP PROCEDURE, it was pretty
    disastrous from a number of other perspectives.  Notably, since the
    primary key of pg_proc is name + proargtypes, this made it possible to
    have multiple procedures with identical names + input arguments and
    differing output argument types.  That would make it impossible to call
    any one of the procedures by writing just NULL (or "?", or any other
    data-type-free notation) for the output argument(s).  The change also
    seems likely to cause grave confusion for client applications that
    examine pg_proc and expect the traditional definition of proargtypes.

[bllewell]

I started a thread HERE to the pgsql-general@lists.postgresql.org to report a semantic error when a int := 0; is replaced with a constant int := 0; in my test.

[sushantrmishra]

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2453ea142233ae57af452019c3b9a443dad1cdd0
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e56bce5d43789cce95d099554ae9593ada92b3b7

[bllewell]

What is your point, @sushantrmishra? Vanilla PG, through version 14.2 and therefore presumably for ever, does allow bare OUT parameters, Therefore, for compatibility, so should YB.

[bllewell]

My initial report here should have stated that the PG version in which I tested was 14.2. I'm afraid that I didn't take the time to create a PG Version 11 env in which to run my test. I've done that now. It happens to be PG Version 11.18 in a Ubuntu VM. (It's easy to choose the major version at install time. But it's too hard to control the minor version. This simply tracks the current latest within the major version series when you do "apt update" and then "apt -y upgrade".)

Anyway, I re-ran my testcase using PG 11.18 (and YB-2.17.0.0). The YB outcome is identical to what this issue reports. And the PG outcome is now identical to the YB outcome.)

My comment:

Vanilla PG, through version 14.2 and therefore presumably for ever, does allow bare OUT parameters.

is clearly wrong. It seems that at some new major PG version, 12, 13, or 14, the restriction reported by this:

ERROR:  0A000: procedures cannot have OUT arguments
HINT:  INOUT arguments are permitted.

was lifted.

I'm leaving this issue with status "open". It can be closed when a future YB version is released that's based on a newer version of PG than 11.2.