Serialization exception : Who else was involved? - Mailing list pgsql-hackers

<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US">Hello,</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">I’m using PostgreSQL .9.2.8 on Windows from a .NET
applicationusing Npgsql.</span><p class="MsoNormal"><span lang="EN-US">I’m working in the Radiology Information System
field.</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">We have thousands
ofusers against a big accounting database.</span><p class="MsoNormal"><span lang="EN-US">We’re using the SERIALIZABLE
isolationlevel to ensure data consistency.</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">Because of the large number of users, and probably because of the database design,
we’refacing serialization exception and we retry our transactions.</span><p class="MsoNormal"><span lang="EN-US">So far
sogood.</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I was wondering
ifthere was a log level in PostgreSQL that could tell me which query was the trigger of a doomed transaction.</span><p
class="MsoNormal"><spanlang="EN-US">The goal is to understand the failures to improve the database and application
designs.</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I pushed the logs
tothe DEBUG5 level with no luck.</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">Aftercarefully reviewing the documentation, it seems that there was nothing.</span><p
class="MsoNormal"><spanlang="EN-US">So I downloaded the code and looked at it.</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">Serialization conflict detection is done in
<b>src/backend/storage/lmgr/predicate.c</b>,where transactions that are doomed to fail are marked as such with <b>the
SXACT_FLAG_DOOMED</b>flag.</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">Isimply added elog(...) calls with the NOTIFY level, each time the flag is set, compiled the code and give
ita try.</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">The results are
amazingfor me, because this simple modification allows me to know which query is marking other running transactions to
fail.</span><pclass="MsoNormal"><span lang="EN-US">I’m pretty sure that in the production environment of our major
customers,there should be no more than a few transaction involved.</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">I would like to see this useful and simple addition in a
futureversion of PostgreSQL.</span><p class="MsoNormal"><span lang="EN-US">Is it in the spirit of what is done when it
comesto ease the work of the developer ?</span><p class="MsoNormal"><span lang="EN-US">May be the level I’ve chosen is
notappropriate ?</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Please
letme know what you think.</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
style="mso-fareast-language:FR">KindRegards.</span><p class="MsoNormal"><span
style="mso-fareast-language:FR"> </span><pclass="MsoNormal"><span style="mso-fareast-language:FR">Olivier.</span><p
class="MsoNormal"> </div>