pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres 13.5 out parameter argument with explicit cast fails with argument is not writable
5+ messages / 3 participants
[nested] [flat]
* Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
@ 2022-02-14 22:50 Delaney, Ed <Ed.Delaney@ellucian.com>
2022-02-14 23:24 ` Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable David G. Johnston <david.g.johnston@gmail.com>
2022-02-14 23:28 ` Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Delaney, Ed @ 2022-02-14 22:50 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
2022-02-14 22:50 Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Delaney, Ed <Ed.Delaney@ellucian.com>
@ 2022-02-14 23:24 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2022-02-14 23:24 UTC (permalink / raw)
To: Delaney, Ed <Ed.Delaney@ellucian.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Mon, Feb 14, 2022 at 3:50 PM Delaney, Ed <Ed.Delaney@ellucian.com> wrote:
> 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.
>
>
>
> declare
>
> lv_somestring character varying (4000);
>
> begin
>
> call foo(lv_somestring::character varying); -- note cast
>
>
> While no explicit cast is actually required, we have a very large amount
> of generated code that uses explicit type casting this way.
>
>
>
I'm unsure about the regression but in fact the explicit cast is simply
incorrect conceptually. You are supposed to be supplying a variable to the
call. What you are supplying is a constant. That constant has the same
value as the variable but is not itself a variable and even has a different
type. If you write: SELECT lv_somestring::varchar(1) you will get back the
first character of the max 4,000 character value presently in the variable
lv_somestring. But you will not be changing the value stored in
lv_somestring. Extend that concept to CALL and you can see why the error
you are being shown in v13 is correct.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
2022-02-14 22:50 Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Delaney, Ed <Ed.Delaney@ellucian.com>
@ 2022-02-14 23:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-02-15 18:12 ` Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Delaney, Ed <Ed.Delaney@ellucian.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2022-02-14 23:28 UTC (permalink / raw)
To: Delaney, Ed <Ed.Delaney@ellucian.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"Delaney, Ed" <Ed.Delaney@ellucian.com> writes:
> 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$;
I think you're out of luck on that. Releases before last November
ignored that cast entirely, thinking it a no-op. Current releases
do not ignore it, recognizing that in fact it has to be understood
as casting to varchar-of-unspecified-length. But then the argument
isn't a bare variable anymore.
We debated about whether to back-patch such a behavioral change,
and maybe we shouldn't have. But there are scenarios in which
the old behavior makes it just impossible to do things, eg you
may not be able to get a UNION to produce the desired type.
On balance we felt this was a bug fix.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
2022-02-14 22:50 Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Delaney, Ed <Ed.Delaney@ellucian.com>
2022-02-14 23:28 ` Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-02-15 18:12 ` Delaney, Ed <Ed.Delaney@ellucian.com>
2022-02-15 18:17 ` Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Delaney, Ed @ 2022-02-15 18:12 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Thanks for the timely replies.
I've found that either removing the length specification or the cast addresses the issue.
So this all makes sense to me.
I could not find a reference to this change in the release notes, but since it is a breaking change, perhaps it should be noted?
regards
Ed
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Monday, February 14, 2022 at 6:28 PM
To: Delaney, Ed <Ed.Delaney@ellucian.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: [EXT]: Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
"Delaney, Ed" <Ed.Delaney@ellucian.com> writes:
> 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$;
I think you're out of luck on that. Releases before last November
ignored that cast entirely, thinking it a no-op. Current releases
do not ignore it, recognizing that in fact it has to be understood
as casting to varchar-of-unspecified-length. But then the argument
isn't a bare variable anymore.
We debated about whether to back-patch such a behavioral change,
and maybe we shouldn't have. But there are scenarios in which
the old behavior makes it just impossible to do things, eg you
may not be able to get a UNION to produce the desired type.
On balance we felt this was a bug fix.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable
2022-02-14 22:50 Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Delaney, Ed <Ed.Delaney@ellucian.com>
2022-02-14 23:28 ` Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Tom Lane <tgl@sss.pgh.pa.us>
2022-02-15 18:12 ` Re: Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Delaney, Ed <Ed.Delaney@ellucian.com>
@ 2022-02-15 18:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2022-02-15 18:17 UTC (permalink / raw)
To: Delaney, Ed <Ed.Delaney@ellucian.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"Delaney, Ed" <Ed.Delaney@ellucian.com> writes:
> I could not find a reference to this change in the release notes, but since it is a breaking change, perhaps it should be noted?
The 2021-11-11 release notes all say
* Don't discard a cast to the same type with unspecified type modifier (Tom Lane)
For example, if column f1 is of type numeric(18,3), the parser used to
simply discard a cast like f1::numeric, on the grounds that it would
have no run-time effect. That's true, but the exposed type of the
expression should still be considered to be plain numeric, not
numeric(18,3). This is important for correctly resolving the type of
larger constructs, such as recursive UNIONs.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-02-15 18:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-14 22:50 Postgres 13.5 out parameter argument with explicit cast fails with argument is not writable Delaney, Ed <Ed.Delaney@ellucian.com>
2022-02-14 23:24 ` David G. Johnston <david.g.johnston@gmail.com>
2022-02-14 23:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-02-15 18:12 ` Delaney, Ed <Ed.Delaney@ellucian.com>
2022-02-15 18:17 ` Tom Lane <tgl@sss.pgh.pa.us>
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