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
