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

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 <tgl@sss.pgh.pa.us> wrote:

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

--
Rumpi Gravenstein

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

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=# 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)

xxxx_pub_dev_2_db=#
xxxx_pub_dev_2_db=#

On Fri, Jul 25, 2025 at 1:02 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

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

<snip>
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?
</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.

On Fri, Jul 25, 2025 at 1:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

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

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');
^
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> wrote:

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

--
Rumpi Gravenstein

Puzzling.

I'd do:
pg_dump --schema-only xxx_pub_dev_2_db | grep -i -A5 _sa_setup_role

Note the -i. That _might_ be important.

On Wed, Aug 6, 2025 at 4:18 PM Rumpi Gravenstein <rgravens@gmail.com> 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');
^
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> wrote:

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

--
Rumpi Gravenstein

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

On Wednesday, August 6, 2025, Rumpi Gravenstein <rgravens@gmail.com> wrote:

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=#

Yeah, we’ve already pretty much decided this function has nothing to do
with it. Go look at pg_type per the last example demonstrating the same
error without the involvement of any user-defined function.

David J.

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.

rumpi_test -- table name
_rumpi_test -- function name

Here's the test case;

SELECT version();

drop table if exists rumpi_test;

create table rumpi_test( col1 varchar, col2 varchar);

drop function if exists rumpi_test;

CREATE OR REPLACE FUNCTION _rumpi_test( col1 varchar)

RETURNS varchar

LANGUAGE plpgsql

AS

$function$

declare

begin

raise info '%',_col1::varchar;

return('Done');

end;

$function$;

select _rumpi_test('hello');

Here what I get when I run this in psql:
xxx_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)

xxx_pub_dev_2_db=>
xxx_pub_dev_2_db=> drop table if exists rumpi_test;
DROP TABLE
xxx_pub_dev_2_db=>
xxx_pub_dev_2_db=> create table rumpi_test( col1 varchar, col2 varchar);
CREATE TABLE
xxx_pub_dev_2_db=>
xxx_pub_dev_2_db=>
xxx_pub_dev_2_db=>
xxx_pub_dev_2_db=> drop function if exists rumpi_test;
NOTICE: function rumpi_test() does not exist, skipping
DROP FUNCTION
xxx_pub_dev_2_db=>
xxx_pub_dev_2_db=> CREATE OR REPLACE FUNCTION _rumpi_test( col1 varchar)
xxx_pub_dev_2_db->
xxx_pub_dev_2_db-> RETURNS varchar
xxx_pub_dev_2_db->
xxx_pub_dev_2_db-> LANGUAGE plpgsql
xxx_pub_dev_2_db->
xxx_pub_dev_2_db-> AS
xxx_pub_dev_2_db->
xxx_pub_dev_2_db-> $function$
xxx_pub_dev_2_db$>
xxx_pub_dev_2_db$> declare
xxx_pub_dev_2_db$>
xxx_pub_dev_2_db$> begin
xxx_pub_dev_2_db$>
xxx_pub_dev_2_db$> raise info '%',_col1::varchar;
xxx_pub_dev_2_db$>
xxx_pub_dev_2_db$> return('Done');
xxx_pub_dev_2_db$>
xxx_pub_dev_2_db$> end;
xxx_pub_dev_2_db$>
xxx_pub_dev_2_db$> $function$;
CREATE FUNCTION
xxx_pub_dev_2_db=>
xxx_pub_dev_2_db=>
xxx_pub_dev_2_db=>
xxx_pub_dev_2_db=> select _rumpi_test('hello');
ERROR: malformed array literal: "hello"
LINE 1: select _rumpi_test('hello');
^
DETAIL: Array value must start with "{" or dimension information.
xxx_pub_dev_2_db=>

On Wed, Aug 6, 2025 at 4:43 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Wednesday, August 6, 2025, Rumpi Gravenstein <rgravens@gmail.com>
wrote:

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=#

Yeah, we’ve already pretty much decided this function has nothing to do
with it. Go look at pg_type per the last example demonstrating the same
error without the involvement of any user-defined function.

David J.

--
Rumpi Gravenstein

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.

David J.

That having a table and function with similar names causes this problem is
a bug.

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

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.

David J.

--
Rumpi Gravenstein

"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