pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Delaney, Ed <Ed.Delaney@ellucian.com>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
Date: Mon, 14 Feb 2022 22:50:06 +0000
Message-ID: <DM8PR02MB79575CE6CE819DB8C424CCABF1339@DM8PR02MB7957.namprd02.prod.outlook.com> (raw)

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

view thread (5+ messages)  latest in thread

Message-ID: <DM8PR02MB79575CE6CE819DB8C424CCABF1339@DM8PR02MB7957.namprd02.prod.outlook.com>
Permalink:  ../DM8PR02MB79575CE6CE819DB8C424CCABF1339@DM8PR02MB7957.namprd02.prod.outlook.com/
Also on:    postgresql.org/message-id/DM8PR02MB79575CE6CE819DB8C424CCABF1339@DM8PR02MB7957.namprd02.prod.outlook.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: Ed.Delaney@ellucian.com
  Subject: Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
  In-Reply-To: <DM8PR02MB79575CE6CE819DB8C424CCABF1339@DM8PR02MB7957.namprd02.prod.outlook.com>
  References: <DM8PR02MB79575CE6CE819DB8C424CCABF1339@DM8PR02MB7957.namprd02.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox