> On Mar 25, 2022, at 11:59 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> Philip Semanchuk <phi...@americanefficient.com> writes:
>> I'm trying to understand a behavior where, with our Postgres client, a
>> leading comment in a SQL script causes the CREATE FUNCTION statement
>> following it to be not executed. I can't figure out if this is a bug
>> somewhere or just a misunderstanding on my part. I would appreciate some
>> help understanding.
>
> Are you certain there's actually a newline after the comment?
> The easiest explanation for this would be if something in the
> SQLAlchemy code path were munging the newline.
I verified that there is a newline after the comment. But yes, thanks to your
suggestion and others, I was able to narrow this down to something in
SQLAlchemy behavior. In case anyone else comes across this and is wondering --
In addition to accepting a plain string, execute() accepts a number of
different SQLAlchemy data types, including TextClause and DDLElement. We used
to pass a DDLElement to execute(), but a few months ago we switched to passing
a TextClause because DDLElement interprets % signs anywhere in SQL scripts as
Python string interpolation markers and that was causing us headaches in some
scripts. Something about the way TextClause changes the raw SQL string causes
the behavior I’m seeing, although we didn’t notice it at the time of the
changeover. I don’t know what exactly it’s doing yet, but when I switch back to
passing a DDLElement to execute(), my SQL function is created as I expected.
https://docs.sqlalchemy.org/en/13/core/connections.html#sqlalchemy.engine.Connection.execute
As David J pointed out, execute() is deprecated as of version 1.4. We’re still
on 1.3 but we’ll have to move away from this code eventually so maybe this is a
good inspiration to move away from execute() now and reduce the number of
deprecation warnings we have to deal with in the future.
> As far as the comparison behavior goes, psql's parser strips
> comments that start with double dashes, for $obscure_reasons.
> The server is perfectly capable of ignoring those by itself,
> though. (Awhile back I tried to remove that psql behavior,
> but it caused too much churn in our regression tests.)
Thanks, this is most helpful. I use psql to double check I think SQLAlchemy is
doing something odd. It’s good to know that psql's behavior in this case is a
choice and not required behavior for clients. Peter J. Holzer’s psycopg2
example could have showed me the same; I wish I had thought of that.
I appreciate all the help!
Cheers
Philip
