Hi.
If I am going into logging, then I would use SET_CONTEXT function. It is
too little documented, but we can add many pairs of (name, value), like
this:set_context('myvar.varname', 'my value here',
false),set_context('abc.anyname', 'any value', false)
The important part is that dot inside the first argument of the function.
But I am looking for a feature that can be used live, while inspecting the
running queries. Thank you for the tip, indeed application_name seems to offer
what I was searching for.Thank you also to all people telling me about it.
Setting context variables in conjunction with row level security helped me
12 years ago (yes, as you can imagine it was an Oracle database) to "force"
users perform the urgent tasks assigned to them (all they could see were the
urgent tasks, and only after solving those tasks they could see other tasks) in
the ERP software we were using at that time.
Relying on SQL comments is also a great solution, but I find it a little
hard to manage precisely. This is why I have not considered it until now.More
than that I am not a fan of the SQL statements embedded in the application,
because the application does not have a kind of SELECT statement to allow us
inspect its metadata. (Yes, it may have reflection, but reflection is much less
powerful that SELECT is at database level). There are other reasons as well,
but this one is the most important for me and for my current job.
Best regards, Cristi Boboc
On Friday, November 5, 2021, 04:16:23 PM GMT+2, Michael Lewis
<[email protected]> wrote:
For my purposes, app name isn't long enough so we put a comment at the start
of every SQL that has a unique ID generated in the application. This ensures
that we can tell one connection apart from another even when both are coming
from the same feature/action (applicationName is set to this) even using
connection pooling. Before, we had different requests reusing the same pid of
course and if they happened to have the same appName, we didn't know it was a
new process on app side.
We also log that same request ID to NewRelic and when an app process needs to
use multiple databases, it links those for research of events after the fact as
well.
I would be interested to hear what others are doing.
