Thread: BUG #18101: 'RAISE LOG ...' with omitted trailing ';' does not throw syntax error in certain situations

The following bug has been logged on the website:

Bug reference:      18101
Logged by:          Jason Sander Egan
Email address:      jasonscrumbs+postgres@gmail.com
PostgreSQL version: 14.6
Operating system:   macOS Monterey 12.5.1
Description:

Hi,

In certain situations a 'RAISE <ERROR_LEVEL> ...' statement with a missing
trailing ';' will *not* throw a syntax error and can result in unexpected
behaviour.

System info:
- PostgreSQL 14.6 on aarch64-apple-darwin20.6.0, compiled by Apple clang
version 12.0.5 (clang-1205.0.22.9), 64-bit
- pgAdmin 4 - 7.3
- macOS-12.5.1-x86_64-i386-64bit

The below script reproduces the problem:

```sql
set client_min_messages = 'log';

drop table if exists error_test_tt;
create temp table error_test_tt as (
    select 1 as val
    union
    select 2
);

do $$
begin
    raise log 'test %', '01'
    
    delete from error_test_tt where val = 1;
end
$$;

select * from error_test_tt;
```

Note:
- The missing ';' after the 'raise log ...'
- The statement following the 'raise log ...' is to delete the record with
val 1 from the table

Expected:
- Syntax error

The result:
- Script completes without error
- `LOG:  test 01` is logged successfully
- The select outputs both values 1 and 2
   - Implying the 'delete ...' statement did not execute

In my testing this is reproducible in a few different variants:
- The 'raise log ...' must be using placeholders (%) to interpolate values
in the message
   - i.e. 'raise log 'test 01'' would result in a syntax error as expected
- The statement following the 'raise log ...' can be 'delete', 'return', and
a few others
   - Other statements I tried such as a subsequent raise, i.e. 'raise log
'test 02'', or 'raise log 'test %', '02', resulted in the expected syntax
error

I posted the issue to dba.stackexchange to validate and others confirmed
they could reproduce, including on Postgres v15. For ref:
https://dba.stackexchange.com/questions/331015/strange-behaviour-when-raise-log-is-executed-with-interpolated-value

I've read through the Bug Reporting Guidelines and have tried to include all
relevant info, but this is my first bug report so apologies if there is
something left out.

Thanks,
Jason

PG Bug reporting form <noreply@postgresql.org> writes:
> do $$
> begin
>     raise log 'test %', '01'
>     delete from error_test_tt where val = 1;
> end
> $$;

I don't think this is a bug, although I agree it's surprising.
You've managed to hit what seems to me like a fairly narrow
ambiguous case.  The stuff between the comma and the semicolon
is parsed as a plpgsql expression, which for historical reasons
is defined as "almost anything that could follow SELECT" [1].
So, if we were to write

  SELECT '01' delete from error_test_tt where val = 1;

would we get a syntax error?  No, we would not.

=# SELECT '01' delete from error_test_tt where val = 1;
 delete 
--------
 01
(1 row)

The "delete" is interpreted as a column label, thanks to one of
the SQL committee's poorer decisions which was to allow omission
of AS in SELECT target lists (even before labels that are keywords),
and then the rest of it is a perfectly valid FROM and WHERE clause
for SELECT.  Nor, in this specific example, do we hit the semantic
constraints that the "expression" must produce one column and not
more than one row.

I don't think there's anything we can do to tighten this up that
isn't going to result in breaking a lot of people's plpgsql code.
The fact that a plpgsql expression can be more than just a scalar
expression has been used/abused all over the place for decades.
We don't even speak disapprovingly of it in the docs (again, [1]).

            regards, tom lane

[1] https://www.postgresql.org/docs/current/plpgsql-expressions.html