Missing semicolumn in anonymous plpgsql block does not raise syntax error

Поиск
Список
Период
Сортировка
От Mor Lehr
Тема Missing semicolumn in anonymous plpgsql block does not raise syntax error
Дата
Msg-id CALyvM2bp_CXMH_Gyq87pmHJRuZDEhV40u9VP8rX=CAnEt2wUXg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Missing semicolumn in anonymous plpgsql block does not raise syntax error  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
Hi,

I would like to report a potential bug in postgres 15.4, also reproduced on 15.6.

The exact sequence of steps:
Connect to a postgres 15.4 database and run the following statements:

CREATE TABLE foo3(id serial PRIMARY key, txt text);

INSERT INTO foo3 (txt) VALUES ('aaa'),('bbb');

DO $$

DECLARE

l_cnt int;

BEGIN

l_cnt := 1

DELETE FROM foo3 WHERE id=1;

END; $$;


The output you got:

1. The script passes (no error message) even though there's a missing semicolon (;) after "l_cnt := 1"
2. The script doesn't actually delete the record from foo3


This caused us a production issue where we thought changes were applied (script passed successfully) but changes weren't actually applied.


If I move the line "l_cnt := 1" to after the DELETE statement like so:

DO $$

DECLARE

l_cnt int;

BEGIN

DELETE FROM foo3 WHERE id=1;

l_cnt := 1

END; $$;

I get the error - as expected:

SQL Error [42601]: ERROR: syntax error at end of input
  Position: 89


Furthermore, replacing the DELETE statement with an UPDATE statement in the original code does raise an error:

DO $$

DECLARE

l_cnt int;

BEGIN

l_cnt := 1

UPDATE foo3 SET txt='ccc' WHERE id=1;

END; $$;

SQL Error [42601]: ERROR: syntax error at or near "foo3"
  Position: 62

But adding the semicolon - it works correctly with either UPDATE or DELETE.


I ran the original code using the following clients to make sure it's not a client problem:

1. psql

2. DBeaver using standard JDBC drivers

3. Flyway using JDBC drivers




Versions:

PostgreSQL 15.6 (Homebrew) on x86_64-apple-darwin23.2.0, compiled by Apple clang version 15.0.0 (clang-1500.1.0.2.5), 64-bit l - running locally on my MacBook


PostgreSQL 15.4 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit - running on AWS RDS Aurora


Installed Extensions (On AWS RDS):

 oid       |extname                  |extowner|extnamespace|extrelocatable|extversion|extconfig   |extcondition|
----------+-------------------------+--------+------------+--------------+----------+------------+------------+
     16463|btree_gist               |      10|        2200|true          |1.7       |NULL        |NULL        |
1463651797|deel_password_check_rules|   16399|        2200|false         |1.0       |NULL        |NULL        |
     16464|fuzzystrmatch            |      10|        2200|true          |1.1       |NULL        |NULL        |
 958297705|pg_repack                |      10|        2200|false         |1.4.8     |NULL        |NULL        |
     16465|pg_stat_statements       |      10|        2200|true          |1.9       |NULL        |NULL        |
1463506085|pg_tle                   |      10|  1463506084|false         |1.1.1     |{1463506117}|{""}        |
     16467|pg_trgm                  |      10|        2200|true          |1.6       |NULL        |NULL        |
     16468|pgcrypto                 |      10|        2200|true          |1.3       |NULL        |NULL        |
     14498|plpgsql                  |      10|          11|false         |1.0       |NULL        |NULL        |
     16469|postgres_fdw             |      10|        2200|true          |1.1       |NULL        |NULL        |
     16470|tablefunc                |      10|        2200|true          |1.0       |NULL        |NULL        |
     16471|unaccent                 |      10|        2200|true          |1.1       |NULL        |NULL        |
     16472|uuid-ossp                |      10|        2200|true          |1.1       |NULL        |NULL        |


Please let me know what other information I can provide.


Thanks,

Mor

В списке pgsql-bugs по дате отправления:

Предыдущее
От: PG Bug reporting form
Дата:
Сообщение: BUG #18490: Assert in comparetup_index_btree_tiebreak() fails when pg_class reindexed during a table creation
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Missing semicolumn in anonymous plpgsql block does not raise syntax error