Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

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
adrian.klaver@aklaver.com