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
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 |