pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18101: 'RAISE LOG ...' with omitted trailing ';' does not throw syntax error in certain situations
2+ messages / 2 participants
[nested] [flat]
* BUG #18101: 'RAISE LOG ...' with omitted trailing ';' does not throw syntax error in certain situations
@ 2023-09-09 02:29 PG Bug reporting form <noreply@postgresql.org>
2023-09-09 20:54 ` Re: BUG #18101: 'RAISE LOG ...' with omitted trailing ';' does not throw syntax error in certain situations Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2023-09-09 02:29 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jasonscrumbs+postgres@gmail.com
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18101: 'RAISE LOG ...' with omitted trailing ';' does not throw syntax error in certain situations
2023-09-09 02:29 BUG #18101: 'RAISE LOG ...' with omitted trailing ';' does not throw syntax error in certain situations PG Bug reporting form <noreply@postgresql.org>
@ 2023-09-09 20:54 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-09-09 20:54 UTC (permalink / raw)
To: jasonscrumbs+postgres@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-09-09 20:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-09 02:29 BUG #18101: 'RAISE LOG ...' with omitted trailing ';' does not throw syntax error in certain situations PG Bug reporting form <noreply@postgresql.org>
2023-09-09 20:54 ` Tom Lane <tgl@sss.pgh.pa.us>
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