Re: Leading comments and client applications

On 2022-03-25 11:32:24 -0400, Philip Semanchuk wrote:
> 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.
>
> 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 cannot reproduce this with plain psycopg:

% cat foo
#!/usr/bin/python3

import psycopg2

db = psycopg2.connect("")
csr = db.cursor()

csr.execute(
        """
        -- this is a comment
        CREATE FUNCTION foo(bar text) RETURNS text AS $$
            SELECT bar
        $$
        LANGUAGE sql IMMUTABLE PARALLEL SAFE
        """)
db.commit()
% ./foo
% psql
psql (13.6 (Ubuntu 13.6-1.pgdg20.04+1), server 11.15 (Ubuntu 11.15-1.pgdg20.04+1))
Type "help" for help.

hjp=> \df foo
                        List of functions
╔════════╤══════╤══════════════════╤═════════════════════╤══════╗
║ Schema │ Name │ Result data type │ Argument data types │ Type ║
╟────────┼──────┼──────────────────┼─────────────────────┼──────╢
║ public │ foo  │ text             │ bar text            │ func ║
╚════════╧══════╧══════════════════╧═════════════════════╧══════╝
(1 row)

hjp=> select foo('x*');
╔═════╗
║ foo ║
╟─────╢
║ x*  ║
╚═════╝
(1 row)

Time: 1.296 ms
hjp=> \q

So like others I suspect that SQLAlchemy is doing something weird here.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"