From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
cc: David Fiedler <david.fido.fiedler@gmail.com>
Subject: Re: WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN
In-reply-to: <706649.1742398190@sss.pgh.pa.us>
References: 
 <CAHjN70-=H5EpTOuZVbC8mPvRS5EfZ4MY2=OUdVDWoyGvKhb+Rw@mail.gmail.com>
 <706649.1742398190@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Wed, 19 Mar 2025 11:29:50 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <1069179.1742486638.0@sss.pgh.pa.us>
Date: Thu, 20 Mar 2025 12:04:19 -0400
Message-ID: <1069227.1742486659@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1069227.1742486659%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1069179.1742486638.1@sss.pgh.pa.us>

[ redirecting to -hackers ]

I wrote:
> David Fiedler <david.fido.fiedler@gmail.com> writes:
>> I've stumbled across a code that used this condition, resulting in
>> unexpected behavior. I think it worths a note that catching 00000 is not
>> possible and that it results in a catch all handler.

> Hmph.  The code thinks
> 	 * OTHERS is represented as code 0 (which would map to '00000', but we
> 	 * have no need to represent that as an exception condition).
> but it evidently didn't consider the possibility of a user writing
> '00000'.  I'm more inclined to consider this a bug and change plpgsql
> to use something else internally to represent OTHERS.  We could use
> -1, which AFAICS cannot be generated by MAKE_SQLSTATE.

Here's a patch for this.  I'm unsure whether to change it in back
branches; is it conceivable that somebody is depending on WHEN
SQLSTATE '00000' mapping to WHEN OTHERS?

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="make-OTHERS-disjoint-from-sqlstates.patch";
	charset="us-ascii"
Content-ID: <1069179.1742486638.2@sss.pgh.pa.us>
Content-Description: make-OTHERS-disjoint-from-sqlstates.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index f36a244140e..6fdba95962d 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -2273,14 +2273,10 @@ plpgsql_parse_err_condition(char *condname)
 	 * here.
 	 */
 =

-	/*
-	 * OTHERS is represented as code 0 (which would map to '00000', but we
-	 * have no need to represent that as an exception condition).
-	 */
 	if (strcmp(condname, "others") =3D=3D 0)
 	{
 		new =3D palloc(sizeof(PLpgSQL_condition));
-		new->sqlerrstate =3D 0;
+		new->sqlerrstate =3D PLPGSQL_OTHERS;
 		new->condname =3D condname;
 		new->next =3D NULL;
 		return new;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index d4377ceecbf..aed75bc20eb 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -1603,7 +1603,7 @@ exception_matches_conditions(ErrorData *edata, PLpgS=
QL_condition *cond)
 		 * assert-failure.  If you're foolish enough, you can match those
 		 * explicitly.
 		 */
-		if (sqlerrstate =3D=3D 0)
+		if (sqlerrstate =3D=3D PLPGSQL_OTHERS)
 		{
 			if (edata->sqlerrcode !=3D ERRCODE_QUERY_CANCELED &&
 				edata->sqlerrcode !=3D ERRCODE_ASSERT_FAILURE)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index aea0d0f98b2..b67847b5111 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -490,11 +490,14 @@ typedef struct PLpgSQL_stmt
  */
 typedef struct PLpgSQL_condition
 {
-	int			sqlerrstate;	/* SQLSTATE code */
+	int			sqlerrstate;	/* SQLSTATE code, or PLPGSQL_OTHERS */
 	char	   *condname;		/* condition name (for debugging) */
 	struct PLpgSQL_condition *next;
 } PLpgSQL_condition;
 =

+/* This value mustn't match any possible output of MAKE_SQLSTATE() */
+#define PLPGSQL_OTHERS (-1)
+
 /*
  * EXCEPTION block
  */

------- =_aaaaaaaaaa0--