public inbox for [email protected]
help / color / mirror / Atom feedRe: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
7+ messages / 3 participants
[nested] [flat]
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
@ 2025-07-25 16:59 Rumpi Gravenstein <[email protected]>
2025-07-25 17:02 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Adrian Klaver <[email protected]>
2025-07-25 17:10 ` 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: Rumpi Gravenstein @ 2025-07-25 16:59 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: PostgreSQL <[email protected]>
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 protected]> wrote:
> Rumpi Gravenstein <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-07-25 16:59 Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
@ 2025-07-25 17:02 ` Adrian Klaver <[email protected]>
2025-07-25 17:07 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2025-07-25 17:02 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; Tom Lane <[email protected]>; +Cc: PostgreSQL <[email protected]>
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
[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-07-25 16:59 Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-07-25 17:02 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Adrian Klaver <[email protected]>
@ 2025-07-25 17:07 ` Rumpi Gravenstein <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Rumpi Gravenstein @ 2025-07-25 17:07 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Tom Lane <[email protected]>; PostgreSQL <[email protected]>
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 <[email protected]>
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
> [email protected]
>
--
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-07-25 16:59 Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
@ 2025-07-25 17:10 ` Tom Lane <[email protected]>
2025-07-25 17:18 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-07-25 17:10 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; +Cc: PostgreSQL <[email protected]>
Rumpi Gravenstein <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-07-25 16:59 Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-07-25 17:10 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Tom Lane <[email protected]>
@ 2025-07-25 17:18 ` Rumpi Gravenstein <[email protected]>
2025-07-25 17:50 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Rumpi Gravenstein @ 2025-07-25 17:18 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: PostgreSQL <[email protected]>
<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 <[email protected]> wrote:
> Rumpi Gravenstein <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-07-25 16:59 Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-07-25 17:10 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Tom Lane <[email protected]>
2025-07-25 17:18 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
@ 2025-07-25 17:50 ` Tom Lane <[email protected]>
2025-07-25 18:10 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-07-25 17:50 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; +Cc: PostgreSQL <[email protected]>
Rumpi Gravenstein <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array
2025-07-25 16:59 Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-07-25 17:10 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Tom Lane <[email protected]>
2025-07-25 17:18 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-07-25 17:50 ` Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Tom Lane <[email protected]>
@ 2025-07-25 18:10 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2025-07-25 18:10 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; +Cc: PostgreSQL <[email protected]>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-07-25 18:10 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-25 16:59 Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array Rumpi Gravenstein <[email protected]>
2025-07-25 17:02 ` Adrian Klaver <[email protected]>
2025-07-25 17:07 ` Rumpi Gravenstein <[email protected]>
2025-07-25 17:10 ` Tom Lane <[email protected]>
2025-07-25 17:18 ` Rumpi Gravenstein <[email protected]>
2025-07-25 17:50 ` Tom Lane <[email protected]>
2025-07-25 18:10 ` 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