psql 14.7/15.2 report a bogus syntax error on function and procedure files that use BEGIN ATOMIC

From: David Adams <dpadams(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: psql 14.7/15.2 report a bogus syntax error on function and procedure files that use BEGIN ATOMIC
Date: 2023-03-13 04:20:28
Message-ID: CAPXPcQuSEWTCqVnoiVALpkdAM_HXXdAf8kYm0WYF8Z2htX6v-A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

BEGIN ATOMIC is one of my favorite additions ot Postgres 14, and I use it
widely. Mostly with CREATE FUNCTION, and sometimes with CREATE PROCEDURE.
It's reassuring to have table and column references baked in as referenced,
rather than stored as text. I can't count how many times the dependency
tracking on views has stopped me from putting things into an inconsistent
state.

I've found a fair few 3rd party tools have, or have had, trouble parsing
and processing functions and procedures that use BEGIN ATOMIC. And,
unfortunately, this seems to be the case with psql too.

My working routines are fairly long, so I've built out a toy example that I
think illustrates the problem. Below are two function definitions, entered
and run from a GUI console:

DROP FUNCTION IF EXISTS tell_me_how_plain();
CREATE OR REPLACE FUNCTION tell_me_how_plain()

RETURNS text

LANGUAGE SQL

return 'plain';

END;

DROP FUNCTION IF EXISTS tools.tell_me_how_atomic();
CREATE OR REPLACE FUNCTION tools.tell_me_how_atomic()

RETURNS text

LANGUAGE SQL

BEGIN ATOMIC

return 'atomic';

END;

select * from tell_me_how_plain(); -- plain
select * from tell_me_how_atomic(); -- atomic

If I run the script files from the command line with psql, the
tell_me_how_plain.sql function builds fine, and the
tell_me_how_atomic.sql throws a bogus syntax error:

ERROR: syntax error at end of input
LINE 5: return 'atomic';
^

I've tried using both the -f flag and the path, and the < path option, like so:

psql -p 5555 -U postgres -d squid -w -v --echo-all -f
"/Users/dpadams2/Desktop/PG_Bug_Tell_Me_How/tell_me_how_atomic.sql"

psql -p 5555 -U postgres -d squid -w -v --echo-all <
"/Users/dpadams2/Desktop/PG_Bug_Tell_Me_How/tell_me_how_atomic.sql"

I really hope that this is *not a bug*, and that I'm missing something
obvious that gets around this error. I noticed in the release notes
for 14.7 and 15.2

that there were changes to psql related to BEGIN ATOMIC on a few
flags, but not -f.

Any help appreciated, or a confirmation so that I can stop trying to
fix this on my own.

Thanks.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2023-03-13 04:34:41 Re: psql 14.7/15.2 report a bogus syntax error on function and procedure files that use BEGIN ATOMIC
Previous Message PG Bug reporting form 2023-03-13 02:58:25 BUG #17835: Two assertions failed in nodeAgg.c and execExprInterp.c with the same SQL