Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable

From: "Delaney, Ed" <Ed(dot)Delaney(at)ellucian(dot)com>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
Date: 2022-02-14 22:50:06
Message-ID: DM8PR02MB79575CE6CE819DB8C424CCABF1339@DM8PR02MB7957.namprd02.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I do not have pg 13.6 installed yet as we just upgraded all of our systems from 11 to 13.5 but I suspect it will be the same.

Here is the test script:
select version();
create or replace procedure foo ( inout p_inout_parameter character varying default null::character varying)
language plpgsql
as $procedure$
declare
lv_this_goes_out character varying;
begin
lv_this_goes_out := 'I am the walrus';
raise notice 'foo called';
p_inout_parameter := lv_this_goes_out;
end;
$procedure$;

create or replace procedure bar ()
language plpgsql
as $procedure$
declare
lv_somestring character varying (4000);
begin
call foo(lv_somestring::character varying); -- note cast
raise notice 'lv_somestring: %', lv_somestring;
end;
$procedure$;

-- this works in pg11 and fails in pg13
do $$
declare l_var text;
begin
call bar ();
end;
$$;
drop routine if exists foo;
drop routine if exists bar;

Expected output: (pg 11.13)

version

------------------------------------------------------------------------------------------------------------------------------------

PostgreSQL 11.13 (Ubuntu 11.13-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

(1 row)

CREATE PROCEDURE

CREATE PROCEDURE

psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:32: NOTICE: foo called

psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:32: NOTICE: lv_somestring I am the walrus

DO

DROP ROUTINE

DROP ROUTINE

Actual output: pg 13.15

version

----------------------------------------------------------------------------------------------------------------------------------

PostgreSQL 13.5 (Ubuntu 13.5-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

(1 row)

CREATE PROCEDURE

CREATE PROCEDURE

psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:32: ERROR: procedure parameter "p_inout_parameter" is an output parameter but corresponding argument is not writable

CONTEXT: PL/pgSQL function bar() line 5 at CALL

SQL statement "CALL bar ()"

PL/pgSQL function inline_code_block line 4 at CALL

psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:33: ERROR: current transaction is aborted, commands ignored until end of transaction block

psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:34: ERROR: current transaction is aborted, commands ignored until end of transaction block

While no explicit cast is actually required, we have a very large amount of generated code that uses explicit type casting this way.

Ed Delaney (he/him) | Principal Architect

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2022-02-14 23:24:34 Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
Previous Message Daniel Gustafsson 2022-02-14 12:20:18 Re: Report a potential memory leak in PostgresSQL 14.1