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 <mlewis@xxxxxxxxxxx> 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.
