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

From: "David G. Johnston" <[email-masked]>
Date: Wed, 6 Aug 2025 13:43:22 -0700

On Wednesday, August 6, 2025, Rumpi Gravenstein <[email-masked]> 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.

---

From: Rumpi Gravenstein <[email-masked]>
Date: Wed, 6 Aug 2025 17:29:26 -0400

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

-- 
Rumpi Gravenstein

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 6 Aug 2025 14:35:24 -0700

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

---

From: Rumpi Gravenstein <[email-masked]>
Date: Wed, 6 Aug 2025 17:39:33 -0400

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.

-- 
Rumpi Gravenstein

---

From: Tom Lane <[email-masked]>
Date: Wed, 06 Aug 2025 17:46:32 -0400

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 6 Aug 2025 17:18:47 -0700

> That having a table and function with similar names causes this problem is
Arguably.  But it seems likely it's one we are not going to fix.

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 6 Aug 2025 18:15:36 -0700

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.

:)

> On Wed, Aug 6, 2025 at 5:35 PM David G. Johnston 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
>     On Wednesday, August 6, 2025, Rumpi Gravenstein <[email-masked]
>     <mailto:[email-masked]>> 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
[email-masked]