Feedback-ID: i76984098:Fastmail
Message-ID: <7da90478-4ea9-47e6-ac22-40689b8d9ff4@aklaver.com>
Date: Fri, 25 Jul 2025 09:54:21 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: PostgreSQL Bug with simple function unexpectedly treating varchar
 parameter as an array
To: Rumpi Gravenstein <rgravens@gmail.com>,
 PostgreSQL <pgsql-general@lists.postgresql.org>
References: <CAEpg1wBxp=25isxj6yt2cf97KtvbwxvJfrJ9qYF8UQxGSBJ93Q@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEpg1wBxp=25isxj6yt2cf97KtvbwxvJfrJ9qYF8UQxGSBJ93Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7da90478-4ea9-47e6-ac22-40689b8d9ff4%40aklaver.com>
Precedence: bulk

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