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.