Re: Leading comments and client applications
От | Philip Semanchuk |
---|---|
Тема | Re: Leading comments and client applications |
Дата | |
Msg-id | 4134A274-2194-4BED-948E-BF45EA79C3F9@americanefficient.com обсуждение исходный текст |
Ответ на | Re: Leading comments and client applications (Philippe Doussot <philippe.doussot@arche-mc2.fr>) |
Список | pgsql-general |
> On Mar 28, 2022, at 5:42 AM, Philippe Doussot <philippe.doussot@arche-mc2.fr> wrote: > > >Something about the way TextClause changes the raw SQL string causes the behavior I’m seeing, although we didn’t noticeit 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 functionis 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 thatSQLAlchemy sends to postgres is the same. If it’s a commit/transaction problem, it should affect all of our functionsequally, 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 planto 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 <tgl@sss.pgh.pa.us> >>> wrote: >>> >>> Philip Semanchuk >>> <philip@americanefficient.com> >>> writes: >>> >>>> I'm trying to understand a behavior where, with our Postgres client, a leading comment in a SQL script causes the CREATEFUNCTION statement following it to be not executed. I can't figure out if this is a bug somewhere or just a misunderstandingon 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 narrowthis 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 TextClauseand DDLElement. We used to pass a DDLElement to execute(), but a few months ago we switched to passing a TextClausebecause DDLElement interprets % signs anywhere in SQL scripts as Python string interpolation markers and that wascausing us headaches in some scripts. Something about the way TextClause changes the raw SQL string causes the behaviorI’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 fromthis code eventually so maybe this is a good inspiration to move away from execute() now and reduce the number of deprecationwarnings 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 knowthat psql's behavior in this case is a choice and not required behavior for clients. Peter J. Holzer’s psycopg2 examplecould 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 >
В списке pgsql-general по дате отправления:
Предыдущее
От: Adrian KlaverДата:
Сообщение: Re: Performance issues on FK Triggers after replacing a primary column
Следующее
От: Per KaminskyДата:
Сообщение: Re: Performance issues on FK Triggers after replacing a primary column