Hello,
I’m using PostgreSQL .9.2.8 on Windows from a .NET application using Npgsql.
I’m working in the Radiology Information System field.
We have thousands of users against a big accounting database.
We’re using the SERIALIZABLE isolation level to ensure data consistency.
Because of the large number of users, and probably because of the database design, we’re facing serialization exception and we retry our transactions.
So far so good.
I was wondering if there was a log level in PostgreSQL that could tell me which query was the trigger of a doomed transaction.
The goal is to understand the failures to improve the database and application designs.
I pushed the logs to the DEBUG5 level with no luck.
After carefully reviewing the documentation, it seems that there was nothing.
So I downloaded the code and looked at it.
Serialization conflict detection is done in src/backend/storage/lmgr/predicate.c, where transactions that are doomed to fail are marked as such with the SXACT_FLAG_DOOMED flag.
I simply added elog(...) calls with the NOTIFY level, each time the flag is set, compiled the code and give it a try.
The results are amazing for me, because this simple modification allows me to know which query is marking other running transactions to fail.
I’m pretty sure that in the production environment of our major customers, there should be no more than a few transaction involved.
I would like to see this useful and simple addition in a future version of PostgreSQL.
Is it in the spirit of what is done when it comes to ease the work of the developer ?
May be the level I’ve chosen is not appropriate ?
Please let me know what you think.
Kind Regards.
Olivier.
