Log Stacktrace of Python in PostgreSQL trigger
Clash Royale CLAN TAG#URR8PPP
Log Stacktrace of Python in PostgreSQL trigger
I try to find a not reproducible bug which happens from time to time on our production server.
Some value in the DB gets changed in a way which I don't want it to.
I could write a PostgreSQL trigger which fires if this bug happens.
Raising a Exception in the PostgreSQL trigger would be a solution. I would see the Python traceback which executes the unwanted SQL statement.
But in this case I don't want to stop the processing of the request.
Is there a way to log the Python/Django traceback from within a PostgreSQL trigger?
I know that this is not trival since the DB code runs under a different linux process with a different user id.
I am using Python, Django, PostgreSQL, Linux.
I guess this is not easy since the DB trigger runs in a different context than the python interpreter.
Please ask if you need further information.
This question has not received enough attention.
logger.exception()
except
Instead of raising an exception you could
RAISE NOTICE ... , which does not abort the current transaction. Don't know how to include the python stacktrace into the message body. Database EVENTs would be another possibility, but that will require more instrumentation.– joop
Aug 16 at 10:19
RAISE NOTICE ...
@joop The difficult part is how to get the python stacktrace from within postgres.
– guettli
Aug 17 at 8:18
Not clear:
I would see the Python traceback which executes the unwanted SQL statement. You have a trigger function in PL/Python?– joop
Aug 17 at 9:22
I would see the Python traceback which executes the unwanted SQL statement.
@joop up to now I do not execute pl/pythonu, but I could do so, if needed.
– guettli
Aug 17 at 9:38
2 Answers
2
Is there a way to log the Python/Django traceback from within a PostgreSQL trigger?
No, there is not
The only connection between the server (which detects the condition) and the client (which needs to perform the stackdump) is the connected socket. You could try to extend the server's reply (if there is one) by some status code, which is used by the client to stackddump itself. This will only work if the trigger is part of the current transaction, not of some unrelated process.
The other way is: massive logging. Make the DBMS write every submitted SQL to its logfile. This can cause huge amounts of log entries, which you have to inspect.
writing every sql statement to a log file makes no sense in this context. I would see: "Ah, there is the bad SQL statement which I am hunting at the moment". But I would have not clue where the related python code is. In this case the issue is even more complicated, since the statement alone is not enough to decide if the sql statement is the one I search. I need other row-values which are not visible in the sql statement. But I can catch this with a trigger method in postgresql. This works.
– guettli
Aug 18 at 8:36
Given this setup
(django/python) -[SQL connection]-> (PostgreSQL server)
your intuition that
I guess this is not easy since the DB trigger runs in a different context than the python interpreter.
is correct. At least, we won't be able to do this exactly the way you want it; not without much acrobatics.
However, there are options:
Give every SQL transaction, or every django request, an ID (could just be some UUID in werkzeug's request-bound storage manager). From here, we gain more options:
RAISE NOTICE
In the spirit of "Test in Production" espoused by Charity Majors, send every request to a sandbox copy of your Django app that reads/writes a sandboxed copy of your production database. In the sandbox database, raise the exception and log your traceback.
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Have you tried
logger.exception()inexceptblock?– Sraw
Aug 16 at 9:34