> On Mar 28, 2022, at 5:42 AM, Philippe Doussot <philippe.dous...@arche-mc2.fr>
> wrote:
>
> >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.
>
>
> Alternate option if you want continue to use TextClause:
>
> use /* comment */ for first prefix comment.
>
> Comment is logged and query executed (tested on Java ( not on SQLAlchemy )).
> We use it to track back the request id executed like that
>
> query = em.createNativeQuery("/*requete_enregistree_num_" + requete.getId() +
> "*/ " + requete.getReqRequete().trim());
Thanks for the suggestion! In my testing, both single line and multiline
comment blocks cause the same problem for me. I *was* able to resolve this with
a simple change. I was calling SQLAlchemy’s engine.execute(). When I call
connection.execute() instead, the problem resolves. This also solves a future
deprecation problem for us. engine.execute() is deprecated in SQLAlchemy 1.4,
but connection.execute() is not.
I didn’t expect this to fix the problem. There’s no difference in the Postgres
log that I can see, so I think the SQL that SQLAlchemy sends to postgres is the
same. If it’s a commit/transaction problem, it should affect all of our
functions equally, not just the ones that start with comments.
I clearly don’t understand this problem fully. Although I'm curious about it,
I’m eager to move on to other things. I plan to proceed with this fix and not
investigate any more.
THanks everyone for all the help and suggestions
Cheers
Philip
>
> On 25/03/2022 19:05, Philip Semanchuk wrote:
>>
>>> 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
>>
>>
>>
>>
>>
>
>
> --
>
> 📌 Le nom de domaine de nos adresses mails évolue et devient @arche-mc2.fr.
>
>
> arche-mc2.fr
>
>
>
>
> Philippe DOUSSOT
>
> ARCHITECTE TECHNIQUE
>
> DIRECTION DES SOLUTIONS ARCHE MC2 DOMICILE
>
> philippe.doussot@arche‑mc2.fr
>
