Thread: Multiple call of GetTransactionSnapshot in single flow

<div class="WordSection1"><p class="MsoNormal">I have observed  in some places like exec_bind_message  and
exec_simple_query,<p class="MsoNormal">Though these two function have already got snapshot but before they call
functionPortalStart, <p class="MsoNormal">current snapshot gets popped off and then they pass InvalidSnapshot  as
parameter<p class="MsoNormal">because of which inside PortalStart again snapshot is taken.<p class="MsoNormal"> <p
class="MsoNormal">Incases where many transactions are running, taking snapshot multiple times may be very costly.<p
class="MsoNormal"> <pclass="MsoNormal">What is the reason for taking snapshot multiple time:<p class="MsoListParagraph"
style="text-indent:-18.0pt;mso-list:l0level1 lfo1"><span style="mso-list:Ignore">1.<span style="font:7.0pt "Times New
Roman"">      </span></span>Is this implementation to make sure snapshot is at more granular level ?<p
class="MsoListParagraph"style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><span style="mso-list:Ignore">2.<span
style="font:7.0pt"Times New Roman"">       </span></span>Is it something do with current command id of the snapshot?<p
class="MsoListParagraph"style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><span style="mso-list:Ignore">3.<span
style="font:7.0pt"Times New Roman"">       </span></span>Or there is any other specific reason for this, which I am not
ablevisualize?<p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><span
style="mso-list:Ignore">4.<spanstyle="font:7.0pt "Times New Roman"">       </span></span>Or am I missing something
else?<pclass="MsoNormal"> <p class="MsoNormal">If it is just reason 1, then maybe we can try to pass the same snapshot
toPortalStart as taken in caller, it can enhance the performance in many case.<p class="MsoNormal"
style="text-autospace:none">Withthis change, I  did one small performance test on pgbench with “<b><i><span
style="font-size:12.0pt;font-family:"Cambria","serif";color:black">preparedqueries for pgbench select with 16
users</span></i></b><b><spanstyle="font-size:12.0pt;font-family:"Cambria","serif";color:black"> and observed
performancebenefit of 10%</span></b><span style="font-size:10.0pt;font-family:"Cambria","serif";color:black">”.
</span><pclass="MsoNormal"> <p class="MsoNormal">Please provide your opinion?<i><u><span
style="color:black"></span></u></i><pclass="MsoNormal"><i><span style="color:black"> </span></i><p
class="MsoNormal"><i><spanstyle="color:black">Thanks and Regards,</span></i><p class="MsoNormal"><i>Kumar Rajeev
Rastogi<spanstyle="color:black"> </span></i><p class="MsoNormal"> </div> 

Rajeev rastogi <rajeev.rastogi@huawei.com> writes:
> What is the reason for taking snapshot multiple time:

To get the right answer.  I believe Robert Haas has been down this rabbit
hole most recently before you: see commits d573e239f and 532994299.
The execution snapshot has to be (at minimum) later than acquisition of
all table locks for the query, and that means it'd better be different
from the snapshot used for parse/plan activities.

The right solution here is not so much to reduce the number of snapshots
as to make them cheaper.  Heikki was working on something for that,
but I'm not sure where he is with that patch.
        regards, tom lane