Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ufLPV-005ydX-Hn for pgsql-general@arkaria.postgresql.org; Fri, 25 Jul 2025 16:36:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ufLPT-002d6J-IS for pgsql-general@arkaria.postgresql.org; Fri, 25 Jul 2025 16:36:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ufLPT-002d68-1N for pgsql-general@lists.postgresql.org; Fri, 25 Jul 2025 16:36:39 +0000 Received: from mail-lf1-x136.google.com ([2a00:1450:4864:20::136]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ufLPR-000kRx-1c for pgsql-general@lists.postgresql.org; Fri, 25 Jul 2025 16:36:38 +0000 Received: by mail-lf1-x136.google.com with SMTP id 2adb3069b0e04-55b50590cb2so2437209e87.0 for ; Fri, 25 Jul 2025 09:36:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1753461395; x=1754066195; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=qrfBv33msUQ3RPBYcmbkOJYYDfUFaMt9ghbvtaKTqyM=; b=AtVfybprSZBY++R/80AJHaYIWK00Vmnw1jS3BHh8b/MfDPxH0fALSg6gwVEn2TAm/4 RmcqdPRQIzBY/GH6v5jDSeTEYLoU5S6UY3cDNjjlmu/5gYJQ0Cp2n/4XFBkvxfsbEkKq OlMJ9NV5pjtPY7iYbUECZdLbu6dgF1rvnhZlKy49RQhYnh8cKWVK4Xa5AaMgDQ5ecQeO qXjH2haBOGFHDMWZSP43eQtwXpF6Y65vH0S+lZzkznQ6ziwBIeXfgVcNZfEmgsXBl73h cD4BB9+EqeYkQw12ezUociekZUUba+xGSmCraMo7VR+xDeH0UUIci7zqCp09coIUDSPz zfgQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1753461395; x=1754066195; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=qrfBv33msUQ3RPBYcmbkOJYYDfUFaMt9ghbvtaKTqyM=; b=Bgm1K2F61HjMwgAvstGFC2X+H2/BTmtvAtiTzab2VcoHPbEbLaMwP29xCtJgjSRGQq ABEZ+jBa2VszbRsvK756GqYWJCCa0QqWFgygrfoVSuq6N9ONYNZ4rIs1Voh9zOLEeAgT q5MAn+o5ZOxqRkq7JXnHeG1TnI6ah0h0wPRGByQbag7KT3pYolMcrsLG//akMiE3MIMz iODtt25L0xLHbRFp6qMSGf50+9dc2IAHU/6m5wAjbc9nOqG0cyzbIO3EtiOJxPpprdCU 8vZPSU7x3XpNlZEcyYJPCKdTY/KoKaL3iuFYyaUKHl5Ow/bdk7IRHhjlwoooQH64cGdk Mprg== X-Gm-Message-State: AOJu0YzIaSytaGddqEE/fqerSMs4yiR4optbqTIv0dRzLq0JJBmh6l7a VnOKz4FwVvnrIOkU0jPr2iFJq3LE7HlxATx6oBBrs5DNUoXv+Hf3i2EH/+kvLW9mpumgcfQHp5v /om2oykt2nMbHSz0MubSDwQppf+tCzYK9fv2C X-Gm-Gg: ASbGncu4H9/s/eVc162O98D8kZc2/3V59WBnK+/x2zOH26jr0xrAjzslAcs1U2lc8IP hW8/N6LElSqbRuE1Ws5edlWN5E8heczmDzApP5sPyOIWD9kxUNFFaaySpU8sZGKAcZNMFZjcV/r UyJ2OhcBYb6lAVVemug+fe2PTsPRSXrx9MsFvstrD+yuM7J/4aSmmy+lPQ9yEySRh0TK7qppGiw NpuQicp X-Google-Smtp-Source: AGHT+IH7jY5IfA7vzwlkeE6IPGsHaZsQj9RcvWNom6xUDEC5SfdO9QncmxFJn8ZT/1eVGADFVrKeOeiyaNNKUTst/Jg= X-Received: by 2002:a05:6512:402a:b0:54d:69a3:6109 with SMTP id 2adb3069b0e04-55b5f3d5c1fmr764069e87.8.1753461394858; Fri, 25 Jul 2025 09:36:34 -0700 (PDT) MIME-Version: 1.0 From: Rumpi Gravenstein Date: Fri, 25 Jul 2025 12:36:23 -0400 X-Gm-Features: Ac12FXyqCnhLEsOSttwK5bcLTmkva3SRLTvb2wfXcb2znGMZwdBFJezURXcohk8 Message-ID: Subject: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array To: PostgreSQL Content-Type: multipart/alternative; boundary="0000000000009168f9063ac38bfb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009168f9063ac38bfb Content-Type: text/plain; charset="UTF-8" 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? Here's the script: SELECT version(); SHOW server_version; DROP FUNCTION if exists _sa_setup_role; CREATE OR REPLACE FUNCTION _sa_setup_role( p_role_to_be_granted varchar) RETURNS varchar LANGUAGE plpgsql AS $function$ declare begin raise info '%',p_role_to_be_granted::varchar; return('Done'); end; $function$; select _sa_setup_role('af_repo_app'); select _sa_setup_role('af_repo_app'::varchar); Best Regards -- Rumpi Gravenstein --0000000000009168f9063ac38bfb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
PostgreSQL Experts,

I've= been confound by the following behavior that I see in one of our PostgreSQ= L 16 instances.=C2=A0 In this case I am running this script from psql.

------------------------------------------------------= ---------------------------------------------------
xxxx_pub_dev_= 2_db=3D# SELECT version();
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0version
--= ---------------------------------------------------------------------------= ----------------------------
=C2=A0PostgreSQL 16.9 on x86_64-pc-linux-gn= u, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit
(1 ro= w)

xxxx_pub_dev_2_db=3D# SHOW server_version;
=C2=A0server_versio= n
----------------
=C2=A016.9
(1 row)

xxxx _pub_dev_2_db=3D= # DROP FUNCTION if exists _sa_setup_role;
DROP FUNCTION
xxxx _pub_dev= _2_db=3D# CREATE OR REPLACE FUNCTION _sa_setup_role( p_role_to_be_granted v= archar)
xxxx _pub_dev_2_db-# RETURNS varchar
xxxx _pub_dev_2_db-# =C2= =A0 =C2=A0 LANGUAGE plpgsql
xxxx _pub_dev_2_db-# AS
xxxx _pub_dev_2_d= b-# $function$
xxxx _pub_dev_2_db $# declare
xxxx _pub_dev_2_db$# beg= in
xxxx _pub_dev_2_db$# raise info '%',p_role_to_be_granted::var= char;
xxxx _pub_dev_2_db$# =C2=A0 return('Done');
xxxx _pub_d= ev_2_db$# end;
xxxx _pub_dev_2_db$# $function$;
CREATE FUNCTION
xx= xx _pub_dev_2_db=3D# select _sa_setup_role('af_= repo_app');
ERROR: =C2=A0malformed array literal: "af_repo_app&= quot;
LINE 1: select _sa_setup_role('af_repo_app');
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 ^
DETAIL: =C2=A0Array value must start with &qu= ot;{" or dimension information.
xxxx _pub_dev_2_db=3D# select _sa_setup_role('af_repo_app'::varchar);INFO: =C2=A0af_repo_app
=C2=A0_sa_setup_role
----------------
= =C2=A0Done
(1 row)

I've been able to= run the same script with no issues in other PostgreSQL databases, just not= this one.

Thoughts?

Here= 's the script:

SELECT version();

SHO= W server_version;

DROP= FUNCTION= if exists _sa_setup_role;

CREATE OR REPLACE FUNCTION _sa_setup_role( p_role_to_be_granted varchar)

RETURNS varchar

L= ANGUAGE plpgsql

AS

$function$

declare=

begin

raise info '%',p_role_to_be_granted::varchar;

return('Done');

end;

<= p style=3D"margin:0px">= $function$;

select _sa_setup_role('= ;af_repo_app');

se= lect _sa_setup_role('af_repo_app'::varchar);


Best Regards
--
Rumpi Gravenstein=
--0000000000009168f9063ac38bfb--