Обсуждение: 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

Rumpi Gravenstein <rgravens@gmail.com> 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

On 7/25/25 09:59, Rumpi Gravenstein wrote:
> No ... just one version:
> 

> 
> xxxx_pub_dev_2_db=# \df _sa_setup_role

Do:

\df *._sa_setup_role

> 
> -- 
> Rumpi Gravenstein


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Rumpi Gravenstein <rgravens@gmail.com> writes:
> No ... just one version:

D'oh, actually this would have complained if there was more
than one match, so that theory is wrong:

> xxxx_pub_dev_2_db=# DROP FUNCTION if exists _sa_setup_role;
> DROP FUNCTION

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

Rumpi Gravenstein <rgravens@gmail.com> writes:
> 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.

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

> 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.

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

I wrote:
> 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

On Fri, 2025-07-25 at 14:10 -0400, Tom Lane wrote:
> 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?

... it could also be a type "sa_setup_role", and "_sa_setup_role"
is interpreted as the corresponding array type:

  CREATE DOMAIN sa_setup_role AS varchar;

  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.

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2025-07-25 at 14:10 -0400, Tom Lane wrote:
>> So ... any chance you have a data type named _sa_setup_role?

> ... it could also be a type "sa_setup_role", and "_sa_setup_role"
> is interpreted as the corresponding array type:

Oh, of course --- that's a good deal more likely than my version.

            regards, tom lane

On 8/6/25 1:17 PM, Rumpi Gravenstein wrote:
> Just coming back to this.  Don't know how to interpret this:
> 
> xxx_pub_dev_2_db=# select proname, pronamespace, oid from pg_proc where 
> proname like '%sa_setup%';
>      proname     | pronamespace |   oid
> ----------------+--------------+---------
> _sa_setup_role |      7038406 | 7869125
> (1 row)
> 
> xxx_pub_dev_2_db=# drop FUNCTION if exists _sa_setup_role;
> DROP FUNCTION
> xxx_pub_dev_2_db=# select proname, pronamespace, oid from pg_proc where 
> proname like '%sa_setup%';
> proname | pronamespace | oid
> ---------+--------------+-----
> (0 rows)
> 
> xxx_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');

Do you have a custom type named sa_setup_role?

In psql do:

\dT

or

select * from pg_type where typname = 'sa_setup_role';

>                                ^
> DETAIL:  Array value must start with "{" or dimension information.
> xxx_pub_dev_2_db=#
> 
> On Fri, Jul 25, 2025 at 4:52 PM Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     Laurenz Albe <laurenz.albe@cybertec.at
>     <mailto:laurenz.albe@cybertec.at>> writes:
>      > On Fri, 2025-07-25 at 14:10 -0400, Tom Lane wrote:
>      >> So ... any chance you have a data type named _sa_setup_role?
> 
>      > ... it could also be a type "sa_setup_role", and "_sa_setup_role"
>      > is interpreted as the corresponding array type:
> 
>     Oh, of course --- that's a good deal more likely than my version.
> 
>                              regards, tom lane
> 
> 
> 
> -- 
> Rumpi Gravenstein

-- 
Adrian Klaver
adrian.klaver@aklaver.com

xxx_pub_dev_2_db=# drop FUNCTION if exists _sa_setup_role;
DROP FUNCTION
xxx_pub_dev_2_db=# select proname, pronamespace, oid from pg_proc where proname like '%sa_setup%';
proname | pronamespace | oid
---------+--------------+-----
(0 rows)
 
xxx_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.
xxx_pub_dev_2_db=#

Here's a reproducible test case that causes the problem in different schemas.  The issue appears to be related to creating a table and a function that has the same name as the table with a prepended underscore.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wednesday, August 6, 2025, Rumpi Gravenstein <rgravens@gmail.com> wrote:
>> Here's a reproducible test case that causes the problem in different
>> schemas.  The issue appears to be related to creating a table and a
>> function that has the same name as the table with a prepended underscore.

> Don’t do that.  Naming a function (action) and table (noun) the same seems
> unwise anyway, underscores or no.

Yeah.  The chain of events here is:

* A table has a matching composite type with the same name as the
table.

* The composite type also has an associated array type, which will
normally be named as the composite type's name with a prepended
underscore.

* For any type name, the syntax "type_name(argument)" is understood
as a possible request to cast to that type, the same as
"argument::type_name".  There are restrictions on whether that
interpretation will be applied, but none of them keep you out of
trouble in this example.

This is all covered in our documentation, though not all in one
place.  Perhaps we'd think twice about some of these choices if
we were redesigning in a green field; but these are things that
Postgres has done for decades and we'd surely break applications
if we changed them now.

            regards, tom lane

On 8/6/25 14:39, Rumpi Gravenstein wrote:
> That having a table and function with similar names causes this problem 
> is a bug.

Actually it is that a type and function have the same name. The 
connection is the table as that where the type gets it's name. Further 
complicated by you doing _type_name which turns the type into an array 
type.

Now that you know, as the old joke goes:

Doctor: What is the problem?
Patient: It hurts when I do this.
Doctor: Then don't do that.

:)

> 
> Going forward I'll be sure to avoid the problem.
> 
> On Wed, Aug 6, 2025 at 5:35 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Wednesday, August 6, 2025, Rumpi Gravenstein <rgravens@gmail.com
>     <mailto:rgravens@gmail.com>> wrote:
> 
>         Here's a reproducible test case that causes the problem in
>         different schemas.  The issue appears to be related to creating
>         a table and a function that has the same name as the table with
>         a prepended underscore.
> 
> 
>     Don’t do that.  Naming a function (action) and table (noun) the same
>     seems unwise anyway, underscores or no.
> 
>     David J.
> 
> 
> 
> -- 
> Rumpi Gravenstein


-- 
Adrian Klaver
adrian.klaver@aklaver.com