From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jasonscrumbs+postgres@gmail.com
Subject: BUG #18101: 'RAISE LOG ...' with omitted trailing ';' does not throw syntax error in certain situations
Date: Sat, 09 Sep 2023 02:29:29 +0000
Message-ID: <18101-10af06fb12aeab34@postgresql.org> (raw)
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-int...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jasonscrumbs+postgres@gmail.com
Subject: Re: BUG #18101: 'RAISE LOG ...' with omitted trailing ';' does not throw syntax error in certain situations
In-Reply-To: <18101-10af06fb12aeab34@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox