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

From: Rumpi Gravenstein <[email-masked]>
Date: Fri, 25 Jul 2025 12:59:59 -0400

No ... just one version:

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)

xxxx_pub_dev_2_db=# \df _sa_setup_role
                                      List of functions
 Schema |      Name      | Result data type  |          Argument data types
          | Type
--------+----------------+-------------------+----------------------------------------+------
 sqlapp | _sa_setup_role | character varying | p_role_to_be_granted
character varying | func
(1 row)

On Fri, Jul 25, 2025 at 12:54 PM Tom Lane <[email-masked]> wrote:

> Rumpi Gravenstein <[email-masked]> 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
>

-- 
Rumpi Gravenstein

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 25 Jul 2025 10:02:27 -0700

> xxxx_pub_dev_2_db=# \df _sa_setup_role

Do:

\df *._sa_setup_role

-- 
Adrian Klaver
[email-masked]

---

From: Rumpi Gravenstein <[email-masked]>
Date: Fri, 25 Jul 2025 13:07:20 -0400

There is really only one function with this name.  A rerun of my test
script with the suggested change:

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=# \df *_sa_setup_role
                                      List of functions
 Schema |      Name      | Result data type  |          Argument data types
          | Type
--------+----------------+-------------------+----------------------------------------+------
 sqlapp | _sa_setup_role | character varying | p_role_to_be_granted
character varying | func
(1 row)

xxxx_pub_dev_2_db=#
xxxx_pub_dev_2_db=#

-- 
Rumpi Gravenstein

---

From: Tom Lane <[email-masked]>
Date: Fri, 25 Jul 2025 13:10:22 -0400

> No ... just one version:

D'oh, actually this would have complained if there was more

> xxxx_pub_dev_2_db=# DROP FUNCTION if exists _sa_setup_role;

Now I'm wondering about stray entries in pg_cast.  Also,
do you have any extensions loaded in that DB that aren't
in your other ones?

			regards, tom lane

---

From: Rumpi Gravenstein <[email-masked]>
Date: Fri, 25 Jul 2025 13:18:07 -0400

<snip>
</snip>

Our databases are deployed with automation tools.  They should all be
created the same.  They all have the same 17 extensions.  I've asked a DBA
to confirm.

This issue only appears in the function I have listed.  A similar function,
same contents and parameter but with a different name, works the way I
would expect.

-- 
Rumpi Gravenstein

---

From: Tom Lane <[email-masked]>
Date: Fri, 25 Jul 2025 13:50:19 -0400

> created the same.  They all have the same 17 extensions.  I've asked a DBA

Well, there's got to be *something* different about that database.

> same contents and parameter but with a different name, works the way I

That sure seems like evidence in favor of the similarly-named-function
idea.  But I don't see how the DROP FUNCTION wouldn't have failed if
there were two, nor why we wouldn't see it in \df.

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Fri, 25 Jul 2025 14:10:40 -0400

> Well, there's got to be *something* different about that database.

After looking at the code I remembered that the parser might be taking
this as a type coercion request.  With that idea, I can duplicate the
observed behavior like so:

regression=# select _sa_setup_role('af_repo_app');
INFO:  af_repo_app
 _sa_setup_role 
----------------
 Done
(1 row)

regression=# create domain _sa_setup_role as varchar[];
CREATE DOMAIN
regression=# 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.

So ... any chance you have a data type named _sa_setup_role?

			regards, tom lane