Prepared statements

[adriangb]

I’m curious what the sqlcommenter story is with prepared statements. At least in Postgres there is, for obvious reasons, no way to inject data into prepared statements without re-preparing it every time (various things in the pipeline, client libraries, poolers and Postgres itself hash query text).

Is there any work in progress to discuss with the databases themselves a solution to inject metadata into queries without hacking through comments?

[dkuku]

I'm working on adding sql commenter to elixir ecto. We often have problems to find where a query originates from.
The only solution I found is to use metadata that is mostly static. Normally when I have a query select id from users it's prepared once. But when i call it from multiple places then it may be generated and prepared multiple times. I think this is still fine.

select id from users /*app='myapp',caller='UserController.get_id',team=`team_users`*/
select id from users /*app='myapp',caller='SessionController.get_id',team=`team_login`*/
select id from users /*app='myapp',caller='JobRunner.send_email',team=`team_emails`*/

The problems start to arise when you start adding more dynamic data like session_id or traces. I plan to allow this with some kind of switch, when I need traces I could add a feature flag and disable prepared statements for these queries.

[adriangb]

Maybe DB drivers should track query execution time and if a prepared query takes more than X ms do a run where it injects the tracing info and then go back to using the prepared query? So something akin to sampling.

[dkuku]

I think it should be something that runs along the query and stores execution times somewhere? but you know the execution time after the query is run - its too late to inject the data. At this point you can only log it. Also when you add the comment metadata then the execution play may be completly different than it was on a slow prepared statement.