agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedWHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN
4+ messages / 3 participants
[nested] [flat]
* WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN
@ 2025-03-19 14:03 David Fiedler <david.fido.fiedler@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: David Fiedler @ 2025-03-19 14:03 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
Hi,
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.
What do you think? Should I post the expected text somewhere?
Thanks,
David Fiedler
--
*David Fiedler*
*737472531*
*david.fido.fiedler@gmail.com <david.fido.fiedler@gmail.com>*
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN
@ 2025-03-19 15:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Fiedler <david.fido.fiedler@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-03-19 15:29 UTC (permalink / raw)
To: David Fiedler <david.fido.fiedler@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
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.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN
@ 2025-03-19 15:32 Laurenz Albe <laurenz.albe@cybertec.at>
parent: David Fiedler <david.fido.fiedler@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-03-19 15:32 UTC (permalink / raw)
To: David Fiedler <david.fido.fiedler@gmail.com>; pgsql-docs@lists.postgresql.org
On Wed, 2025-03-19 at 15:03 +0100, David Fiedler wrote:
> 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.
> What do you think? Should I post the expected text somewhere?
The code makes no sense, but what about this:
DO $$BEGIN RAISE EXCEPTION SQLSTATE '00000'; END;$$;
ERROR: 00000
CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN
@ 2025-03-20 16:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2025-03-20 16:04 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: David Fiedler <david.fido.fiedler@gmail.com>
[ 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
Attachments:
[text/x-diff] make-OTHERS-disjoint-from-sqlstates.patch (1.8K, ../../1069227.1742486659@sss.pgh.pa.us/2-make-OTHERS-disjoint-from-sqlstates.patch)
download | inline diff:
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") == 0)
{
new = palloc(sizeof(PLpgSQL_condition));
- new->sqlerrstate = 0;
+ new->sqlerrstate = PLPGSQL_OTHERS;
new->condname = condname;
new->next = 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, PLpgSQL_condition *cond)
* assert-failure. If you're foolish enough, you can match those
* explicitly.
*/
- if (sqlerrstate == 0)
+ if (sqlerrstate == PLPGSQL_OTHERS)
{
if (edata->sqlerrcode != ERRCODE_QUERY_CANCELED &&
edata->sqlerrcode != 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
*/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-03-20 16:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-19 14:03 WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN David Fiedler <david.fido.fiedler@gmail.com>
2025-03-19 15:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-03-20 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-03-19 15:32 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox