WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN

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

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

[ 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