Horton

Thread

Commits

  1. plpgsql: make WHEN OTHERS distinct from WHEN SQLSTATE '00000'.

  1. WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN

    David Fiedler <david.fido.fiedler@gmail.com> — 2025-03-19T14:03:34Z

    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>*

  2. Re: WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-19T15:29:50Z

    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

  3. Re: WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-03-19T15:32:20Z

    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

  4. Re: WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-20T16:04:19Z

    [ 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