Re: autonomous transactions

<div class="WordSection1"><div style="border:none;border-left:solid blue 1.5pt;padding:0mm 0mm 0mm 4.0pt"><p><b><font
face="Tahoma"size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";font-weight:bold">From:</span></font></b><fontface="Tahoma"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">
pgsql-hackers-owner@postgresql.org[mailto:pgsql-hackers-owner@postgresql.org] <b> <span style="font-weight:bold">On
BehalfOf </span></b>Craig Ringer<br /></span></font><span lang="EN-US">> Of course, if we could decrease the startup
costof a bgworker</span><p><font face="ＭＳ Ｐゴシック" size="3"><span lang="EN-US" style="font-size:12.0pt">For this use in
autonomoustx's we could probably pool workers. Or at least lazily terminate them so that the loop cases work better by
re-usingan existing bgworker.</span></font><p><font color="#1f497d" face="ＭＳ Ｐゴシック" size="3"><span lang="EN-US"
style="font-size:12.0pt;color:#1F497D"> </span></font><p><fontcolor="#1f497d" face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">ThoughI may say something odd, isn’t the
bgworkerapproach going to increase context switches?  I thought PostgreSQL has made efforts to decrease context
switches,e.g. </span></font><p><font color="#1f497d" face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D"> </span></font><p
style="margin-left:18.0pt"><fontcolor="#1f497d" face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">*Each backend itself writes WAL to disk unlike
Oraclerequests LGWR process to write REDO to disk.</span></font><p><font color="#1f497d" face="Arial" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D"> </span></font><p><font
color="#1f497d"face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">*Releasing and re-acquiring a lwlock appears to
tryto avoid context switches.</span></font><p style="margin-left:5.25pt"><font color="#1f497d" face="Arial"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D"> </span></font><p
style="margin-left:5.25pt"><fontcolor="#1f497d" face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">          /*</span></font><p
style="margin-left:5.25pt"><fontcolor="#1f497d" face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">          * Loop here to try to acquire lock
aftereach time we are signaled by</span></font><p style="margin-left:5.25pt"><font color="#1f497d" face="Arial"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">           *
LWLockRelease.</span></font><pstyle="margin-left:5.25pt"><font color="#1f497d" face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">          *</span></font><p
style="margin-left:5.25pt"><fontcolor="#1f497d" face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">          * NOTE: it might seem better to have
LWLockReleaseactually grant us the</span></font><p style="margin-left:5.25pt"><font color="#1f497d" face="Arial"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">           * lock,
ratherthan retrying and possibly having to go back to sleep. But</span></font><p style="margin-left:5.25pt"><font
color="#1f497d"face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">          * in practice that is no good because
itmeans a process swap for every</span></font><p style="margin-left:5.25pt"><font color="#1f497d" face="Arial"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">           * lock
acquisitionwhen two or more processes are contending for the same</span></font><p style="margin-left:5.25pt"><font
color="#1f497d"face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">          * lock.  Since LWLocks are normally
usedto protect not-very-long</span></font><p style="margin-left:5.25pt"><font color="#1f497d" face="Arial"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">           *
sectionsof computation, a process needs to be able to acquire and</span></font><p style="margin-left:5.25pt"><font
color="#1f497d"face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">          * release the same lock many times
duringa single CPU time slice, even</span></font><p style="margin-left:5.25pt"><font color="#1f497d" face="Arial"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">           * in the
presenceof contention.  The efficiency of being able to do that</span></font><p style="margin-left:5.25pt"><font
color="#1f497d"face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">          * outweighs the inefficiency of
sometimeswasting a process dispatch</span></font><p style="margin-left:5.25pt"><font color="#1f497d" face="Arial"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">           * cycle
becausethe lock is not free when a released waiter finally gets</span></font><p style="margin-left:5.25pt"><font
color="#1f497d"face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">          * to run.  See pgsql-hackers archives
for29-Dec-01.</span></font><p><font color="#1f497d" face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">           */</span></font><p><font
color="#1f497d"face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D"> </span></font><p><fontcolor="#1f497d"
face="Arial"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">I’m not
surewhether to be nervous about the context switch cost in the use cases of autonomous
transactions.</span></font><p><fontcolor="#1f497d" face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D"> </span></font><p><fontcolor="#1f497d"
face="Arial"size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">Regards</span></font><p><fontcolor="#1f497d"
face="Arial"size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D">TakayukiTsunakawa</span></font><p><font
color="#1f497d"face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D"> </span></font></div></div>