<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
        {font-family:Tahoma;
        panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
        {margin:0in;
        margin-bottom:.0001pt;
        font-size:12.0pt;
        font-family:"Times New Roman";}
a:link, span.MsoHyperlink
        {color:blue;
        text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
        {color:purple;
        text-decoration:underline;}
pre
        {margin:0in;
        margin-bottom:.0001pt;
        font-size:10.0pt;
        font-family:"Courier New";}
span.EmailStyle17
        {mso-style-type:personal;
        font-family:Arial;
        color:windowtext;}
span.EmailStyle18
        {mso-style-type:personal-reply;
        font-family:Arial;
        color:navy;}
@page Section1
        {size:8.5in 11.0in;
        margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
        {page:Section1;}
/* List Definitions */
@list l0
        {mso-list-id:176316822;
        mso-list-template-ids:-141504322;}
@list l0:level1
        {mso-level-start-at:2;
        mso-level-tab-stop:.5in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l1
        {mso-list-id:495269371;
        mso-list-template-ids:1522681528;}
@list l2
        {mso-list-id:892934630;
        mso-list-type:hybrid;
        mso-list-template-ids:1835954610 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l2:level1
        {mso-level-tab-stop:.5in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l2:level2
        {mso-level-tab-stop:1.0in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l2:level3
        {mso-level-tab-stop:1.5in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l2:level4
        {mso-level-tab-stop:2.0in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l2:level5
        {mso-level-tab-stop:2.5in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l2:level6
        {mso-level-tab-stop:3.0in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l2:level7
        {mso-level-tab-stop:3.5in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l2:level8
        {mso-level-tab-stop:4.0in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l2:level9
        {mso-level-tab-stop:4.5in;
        mso-level-number-position:left;
        text-indent:-.25in;}
@list l3
        {mso-list-id:1389039501;
        mso-list-template-ids:-1600772134;}
@list l3:level1
        {mso-level-start-at:3;
        mso-level-tab-stop:.5in;
        mso-level-number-position:left;
        text-indent:-.25in;}
ol
        {margin-bottom:0in;}
ul
        {margin-bottom:0in;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>OK, I was wrong.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>It turns out that there *<b><span
style='font-weight:bold'>were</span></b>* two slon processes for the same
node. How did I miss this fact? Because they were running on two
different machines. I assumed that all slon processes were running on the
same server (the master DB machine) so it would be sufficient to run a “ps
afx | grep slon” command on that server. But of course this command
completely missed the slon process that was running on the other server.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>How did I discover this? The
following query was helpful…<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<pre><font size=2 face="Courier New"><span style='font-size:10.0pt'>stingray_prod=# select nl.*, psa.client_addr from _stingray_cluster.sl_nodelock nl join pg_stat_activity psa on nl.nl_backendpid=psa.procpid;<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> nl_nodeid | nl_conncnt | nl_backendpid | client_addr<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>-----------+------------+---------------+-------------<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> 2 | 11 | 14774 | (query-2)<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> 2 | 15 | 20569 | (query-2)<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> 1 | 0 | 30549 | (query-1)<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> 4 | 20 | 30566 | (query-1)<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> 4 | 21 | 30571 | (query-1)<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>(5 rows)<o:p></o:p></span></font></pre>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>What are slon processes doing on
query-2??? Ah-ha!<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>It goes to show that it’s not
sufficient to double check and quadruple check, you have to go further.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>--S<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'> Shahaf Abileah <br>
<b><span style='font-weight:bold'>Sent:</span></b> Friday, August 15, 2008 2:35
PM<br>
<b><span style='font-weight:bold'>To:</span></b>
slony1-general@lists.slony.info<br>
<b><span style='font-weight:bold'>Subject:</span></b> duplicate key value
violates unique constraint "sl_nodelock-pkey"</span></font><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hello list,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I’ve been using Slony in production for months and
things have worked out pretty well so far. But this past week I ran into
problems when we switched from Postgres 8.2 to Postgres 8.3 (still using Slony
version 1.2.13). I don’t think the error results from the version
upgrade, but I could be wrong.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I set up the master database (query-1) and then proceeded to
set up replication to two slaves (query-2 and query-4). I kicked off the
slon processes for all three simultaneously. Replication to query-4 got
started right away and succeeded – it took 2 hours for the initial bulk
copy of data, and soon after that it was caught up with incremental
changes. However, replication to query-2 died right away. The slon
process for query-2 quit immediately and I saw the following message in the
log:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>2008-08-15 13:57:01 PDT FATAL localListenThread:
"select "_stingray_cluster".cleanupNodelock(); insert into
"_stingray_cluster".sl_nodelock values ( 2, 0,
"pg_catalog".pg_backend_pid()); " - ERROR: duplicate key
value violates unique constraint "sl_nodelock-pkey"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Now, I searched the interweb for solutions to this problem
and I found several suggestions, but none of them have helped so far…<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<ol style='margin-top:0in' start=1 type=1>
<li class=MsoNormal style='mso-list:l2 level1 lfo3'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>People suggest that this
happens when you try to start multiple slon processes for the same node at
the same time. I’m quite certain that this is not the
case. I’ve checked and double-checked and
quadruple-checked. For what it’s worth, remember that
I’ve successfully run Slony replication to multiple slaves for months.<o:p></o:p></span></font></li>
</ol>
<p class=MsoNormal style='margin-left:.25in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.25in;text-indent:.25in'><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>Here are the ones
that are running:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-indent:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>25537 pts/3
S 0:00 slon -d 2 stingray_cluster
host=query-1.colo.redfin.com dbname=my_db user=my_user password=my_password<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>25541 pts/3
Sl 0:00 \_ slon -d 2 stingray_cluster
host=query-1.colo.redfin.com dbname= my_db user= my_user password= my_password<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>25544 pts/3
S 0:00 slon -d 2 stingray_cluster
host=query-4.colo.redfin.com dbname= my_db user= my_user password= my_password<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>25545 pts/3
Sl 0:00 \_ slon -d 2 stingray_cluster
host=query-4.colo.redfin.com dbname= my_db user= my_user password= my_password<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>
And the one that starts and then immediately dies has query-2 as the host.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<ol style='margin-top:0in' start=2 type=1>
<li class=MsoNormal style='mso-list:l2 level1 lfo3'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Some people suggest that this
happens when a slon process dies and leaves a stale connection, a
connection so deep in the network stack that slony and postgres are not
aware. For this people suggest either waiting about two hours for
some timeout to kick in, or else having the admin kill -2 the zombie process/connection.
Apparently this happens more often if you try to run slony across a wan.<o:p></o:p></span></font></li>
</ol>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Well, our slon processes all run on
the master node and the other nodes are right there in the same LAN. (we
did once try to run Slony across a WAN but that never worked)<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>This duplicate key issue first
happened more than 12 hours ago, and it continues to happen. Each time I
try to start a slon process for query-2, it dies right away with the same
error.<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>So, I don’t think
there’s a zombie connection, but I also don’t know exactly how to
check for one. I can say that all the proc’s in the
pg_stat_activity have a xact_start that is less than an hour old.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<ol style='margin-top:0in' start=3 type=1>
<li class=MsoNormal style='mso-list:l2 level1 lfo3'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>One person mentioned having to
go into the sl_nodelock table and manually whack the bad row.
I’m a little concerned about doing this kind of surgery because I
don’t know the sl_nodelock table and therefore I don’t know
what would be the consequence of whacking a row (especially if I
accidentally whack the wrong row).<o:p></o:p></span></font></li>
</ol>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Here’s what I see in that
table:<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>stingray_prod=# select *
from _stingray_cluster.sl_nodelock;<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> nl_nodeid | nl_conncnt
| nl_backendpid<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>-----------+------------+---------------<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>
2 | 11
| 14774<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>
2 | 15
| 20569<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>
1 | 0
| 25551<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>
4 | 16
| 25568<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>
4 | 17
| 25570<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>(5 rows)</span></font><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>What do these columns mean
exactly? Is there documentation on this table somewhere? Which one
is likely to be the offending row? Is it crazy to whack the row
manually? Would I need to also whack corresponding rows in other tables?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Any help with this would be much appreciated.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>On a related note – is there a psql command (like \d)
that I can use to list all the objects in my _<i><span style='font-style:italic'>stingray</span></i>_cluster
schema?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Thanks,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>--S<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
</div>
</body>
</html>