public inbox for [email protected]
help / color / mirror / Atom feedPostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
3+ messages / 3 participants
[nested] [flat]
* PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
@ 2025-07-25 16:36 Rumpi Gravenstein <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Rumpi Gravenstein @ 2025-07-25 16:36 UTC (permalink / raw)
To: PostgreSQL <[email protected]>
PostgreSQL Experts,
I've been confound by the following behavior that I see in one of our
PostgreSQL 16 instances. In this case I am running this script from psql.
---------------------------------------------------------------------------------------------------------
xxxx_pub_dev_2_db=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 16.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
20210514 (Red Hat 8.5.0-26), 64-bit
(1 row)
xxxx_pub_dev_2_db=# SHOW server_version;
server_version
----------------
16.9
(1 row)
xxxx _pub_dev_2_db=# DROP FUNCTION if exists _sa_setup_role;
DROP FUNCTION
xxxx _pub_dev_2_db=# CREATE OR REPLACE FUNCTION _sa_setup_role(
p_role_to_be_granted varchar)
xxxx _pub_dev_2_db-# RETURNS varchar
xxxx _pub_dev_2_db-# LANGUAGE plpgsql
xxxx _pub_dev_2_db-# AS
xxxx _pub_dev_2_db-# $function$
xxxx _pub_dev_2_db $# declare
xxxx _pub_dev_2_db$# begin
xxxx _pub_dev_2_db$# raise info '%',p_role_to_be_granted::varchar;
xxxx _pub_dev_2_db$# return('Done');
xxxx _pub_dev_2_db$# end;
xxxx _pub_dev_2_db$# $function$;
CREATE FUNCTION
xxxx _pub_dev_2_db=# select _sa_setup_role('af_repo_app');
ERROR: malformed array literal: "af_repo_app"
LINE 1: select _sa_setup_role('af_repo_app');
^
DETAIL: Array value must start with "{" or dimension information.
xxxx _pub_dev_2_db=# select _sa_setup_role('af_repo_app'::varchar);
INFO: af_repo_app
_sa_setup_role
----------------
Done
(1 row)
I've been able to run the same script with no issues in other PostgreSQL
databases, just not this one.
Thoughts?
Here's the script:
SELECT version();
SHOW server_version;
DROP FUNCTION if exists _sa_setup_role;
CREATE OR REPLACE FUNCTION _sa_setup_role( p_role_to_be_granted varchar)
RETURNS varchar
LANGUAGE plpgsql
AS
$function$
declare
begin
raise info '%',p_role_to_be_granted::varchar;
return('Done');
end;
$function$;
select _sa_setup_role('af_repo_app');
select _sa_setup_role('af_repo_app'::varchar);
Best Regards
--
Rumpi Gravenstein
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
@ 2025-07-25 16:54 Adrian Klaver <[email protected]>
parent: Rumpi Gravenstein <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2025-07-25 16:54 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; PostgreSQL <[email protected]>
On 7/25/25 09:36, Rumpi Gravenstein wrote:
> PostgreSQL Experts,
>
> I've been confound by the following behavior that I see in one of our
> PostgreSQL 16 instances. In this case I am running this script from psql.
>
> ---------------------------------------------------------------------------------------------------------
> xxxx_pub_dev_2_db=# SELECT version();
> version
> ---------------------------------------------------------------------------------------------------------
> PostgreSQL 16.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
> 20210514 (Red Hat 8.5.0-26), 64-bit
> (1 row)
>
> xxxx_pub_dev_2_db=# SHOW server_version;
> server_version
> ----------------
> 16.9
> (1 row)
>
> xxxx _pub_dev_2_db=# DROP FUNCTION if exists _sa_setup_role;
> DROP FUNCTION
> xxxx _pub_dev_2_db=# CREATE OR REPLACE FUNCTION
> _sa_setup_role( p_role_to_be_granted varchar)
> xxxx _pub_dev_2_db-# RETURNS varchar
> xxxx _pub_dev_2_db-# LANGUAGE plpgsql
> xxxx _pub_dev_2_db-# AS
> xxxx _pub_dev_2_db-# $function$
> xxxx _pub_dev_2_db $# declare
> xxxx _pub_dev_2_db$# begin
> xxxx _pub_dev_2_db$# raise info '%',p_role_to_be_granted::varchar;
> xxxx _pub_dev_2_db$# return('Done');
> xxxx _pub_dev_2_db$# end;
> xxxx _pub_dev_2_db$# $function$;
> CREATE FUNCTION
> xxxx _pub_dev_2_db=# select _sa_setup_role('af_repo_app');
> ERROR: malformed array literal: "af_repo_app"
> LINE 1: select _sa_setup_role('af_repo_app');
> ^
> DETAIL: Array value must start with "{" or dimension information.
> xxxx _pub_dev_2_db=#select _sa_setup_role('af_repo_app'::varchar);
> INFO: af_repo_app
> _sa_setup_role
> ----------------
> Done
> (1 row)
>
> I've been able to run the same script with no issues in other PostgreSQL
> databases, just not this one.
>
> Thoughts?
You have more then on version of _sa_setup_role in this database, one of
which is looking for an array argument.
In psql do
\df *._sa_setup_role
and see what it returns.
>
> Best Regards
> --
> Rumpi Gravenstein
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
@ 2025-07-25 16:54 Tom Lane <[email protected]>
parent: Rumpi Gravenstein <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2025-07-25 16:54 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; +Cc: PostgreSQL <[email protected]>
Rumpi Gravenstein <[email protected]> writes:
> I've been confound by the following behavior that I see in one of our
> PostgreSQL 16 instances. In this case I am running this script from psql.
I'd bet there is another function named _sa_setup_role() that takes
some kind of array, and the parser is resolving the ambiguity by
choosing that one.
"\df _sa_setup_role" would be illuminating.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-07-25 16:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-25 16:36 PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-07-25 16:54 ` Adrian Klaver <[email protected]>
2025-07-25 16:54 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox