Re: Leading comments and client applications

On Fri, Mar 25, 2022 at 8:32 AM Philip Semanchuk <
philip(at)americanefficient(dot)com> wrote:

>
> Here's the contents of foo.sql --
>
> -- this is a comment
> CREATE FUNCTION foo(bar text) RETURNS text AS $$
> SELECT bar
> $$
> LANGUAGE sql IMMUTABLE PARALLEL SAFE
> ;
>
> When I feed that to 'psql -f foo.sql', the function is created as I
> expect. In the Postgres log, the leading comment *doesn't* appear. I see
> the same behavior if I just copy/paste the function into psql.
>
> Our test system uses Python 3.8, SQLAlchemy 1.3.6, and psycopg 2.8.5, and
> when our test harness reads foo.sql and passes it to SQLAlchemy's
> execute(), I can see in the Postgres log that the leading comment is *not*
> stripped, and the function isn't created.
>
>
I think you need to provide these log entries you are referring to.

The comment form using the -- prefix ends at the first newline
encountered. This is server behavior, not client-side [1]. But the
comment doesn't actually belong with any individual command, it (the line)
is simply ignored by the server when seen.

I suspect that the newline is being removed in order by SQLAlchemy to do
all of its helpful stuff, like statement caching. I also suspect that you
are probably mis-using the driver since the execute(string) method is
marked deprecated [2], possibly for this very reason, but you also haven't
shown that code so it is hard to say (I don't actually use the tool myself
though).

[1]
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-COMMENTS
[2]
https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Connection.execute

David J.