public inbox for [email protected]
help / color / mirror / Atom feedPostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
7+ messages / 4 participants
[nested] [flat]
* PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
@ 2025-08-06 20:43 David G. Johnston <[email protected]>
2025-08-06 21:29 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2025-08-06 20:43 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; +Cc: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; PostgreSQL <[email protected]>
On Wednesday, August 6, 2025, Rumpi Gravenstein <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-08-06 20:43 PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
@ 2025-08-06 21:29 ` Rumpi Gravenstein <[email protected]>
2025-08-06 21:35 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Rumpi Gravenstein @ 2025-08-06 21:29 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; PostgreSQL <[email protected]>
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 <[email protected]>
wrote:
> On Wednesday, August 6, 2025, Rumpi Gravenstein <[email protected]>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-08-06 20:43 PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
2025-08-06 21:29 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
@ 2025-08-06 21:35 ` David G. Johnston <[email protected]>
2025-08-06 21:39 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-08-06 21:46 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Tom Lane <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: David G. Johnston @ 2025-08-06 21:35 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; +Cc: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; PostgreSQL <[email protected]>
On Wednesday, August 6, 2025, Rumpi Gravenstein <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-08-06 20:43 PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
2025-08-06 21:29 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-08-06 21:35 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
@ 2025-08-06 21:39 ` Rumpi Gravenstein <[email protected]>
2025-08-07 00:18 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
2025-08-07 01:15 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Adrian Klaver <[email protected]>
1 sibling, 2 replies; 7+ messages in thread
From: Rumpi Gravenstein @ 2025-08-06 21:39 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; PostgreSQL <[email protected]>
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 <[email protected]>
wrote:
> On Wednesday, August 6, 2025, Rumpi Gravenstein <[email protected]>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-08-06 20:43 PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
2025-08-06 21:29 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-08-06 21:35 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
2025-08-06 21:39 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
@ 2025-08-07 00:18 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2025-08-07 00:18 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; +Cc: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; PostgreSQL <[email protected]>
On Wed, Aug 6, 2025 at 2:39 PM Rumpi Gravenstein <[email protected]> wrote:
> That having a table and function with similar names causes this problem is
> a bug.
>
>
Arguably. But it seems likely it's one we are not going to fix.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-08-06 20:43 PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
2025-08-06 21:29 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-08-06 21:35 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
2025-08-06 21:39 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
@ 2025-08-07 01:15 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2025-08-07 01:15 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; PostgreSQL <[email protected]>
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
> <[email protected] <mailto:[email protected]>> wrote:
>
> On Wednesday, August 6, 2025, Rumpi Gravenstein <[email protected]
> <mailto:[email protected]>> 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 protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-08-06 20:43 PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
2025-08-06 21:29 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-08-06 21:35 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
@ 2025-08-06 21:46 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2025-08-06 21:46 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Rumpi Gravenstein <[email protected]>; Laurenz Albe <[email protected]>; PostgreSQL <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Wednesday, August 6, 2025, Rumpi Gravenstein <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-08-07 01:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-06 20:43 PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array David G. Johnston <[email protected]>
2025-08-06 21:29 ` Rumpi Gravenstein <[email protected]>
2025-08-06 21:35 ` David G. Johnston <[email protected]>
2025-08-06 21:39 ` Rumpi Gravenstein <[email protected]>
2025-08-07 00:18 ` David G. Johnston <[email protected]>
2025-08-07 01:15 ` Adrian Klaver <[email protected]>
2025-08-06 21:46 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox