Postgres Pro
Downloads
Home   >   mailing lists

Missing semicolumn in anonymous plpgsql block does not raise syntax error - Mailing list pgsql-bugs

From Mor Lehr
Subject Missing semicolumn in anonymous plpgsql block does not raise syntax error
Date
Msg-id CALyvM2bp_CXMH_Gyq87pmHJRuZDEhV40u9VP8rX=CAnEt2wUXg@mail.gmail.com
Whole thread Raw
Responses Re: Missing semicolumn in anonymous plpgsql block does not raise syntax error
List pgsql-bugs
Tree view
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 by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #18490: Assert in comparetup_index_btree_tiebreak() fails when pg_class reindexed during a table creation
Next
From: "David G. Johnston"
Date:
Subject: Re: Missing semicolumn in anonymous plpgsql block does not raise syntax error