<div dir="ltr"><div><div>Hi Steve,<br><br></div>As you said the deadlock was caused by process 15366 but I did not find it because I was looking in the wrong server. Once the process was identified I could stop it and run the commands successfully.<br><br></div>Thank you for your help!<br></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Dec 17, 2014 at 1:22 PM, Steve Singer <span dir="ltr"><<a href="mailto:ssinger@ca.afilias.info" target="_blank">ssinger@ca.afilias.info</a>></span> wrote:<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">On 12/17/2014 08:48 AM, Carlos Henrique Reimer wrote:<br>
> Hi,<br>
><br>
> We are trying to upgrade our PG from 8.3 to 9.3 with slony 2.2.3.<br>
><br>
> The complete database (21.000 tables) is now being sincronized with<br>
> SLONY and I would like to terminate the replication processing in order<br>
> to another team test the application with the new 9.3 replicated<br>
> database. They will need several hours to test the application. Once I<br>
> get they green light, will repeat the processing again and replicate the<br>
> database from scratch.<br>
><br>
> I'm trying to unsubscribe a receiver from the master set but I'm getting<br>
> an out of shared memory message:<br>
><br>
</span>> cat <a href="http://unsubscribe2.sl" target="_blank">unsubscribe2.sl</a> <<a href="http://unsubscribe2.sl" target="_blank">http://unsubscribe2.sl</a>><br>
<span class="">> cluster name = slcluster;<br>
> node 1 admin conninfo = 'dbname=FiscalWeb host=192.168.23.10 user=slonyo';<br>
> node 2 admin conninfo = 'dbname=FiscalWeb host=192.168.23.11 user=slonyn';<br>
> unsubscribe set ( id = 1 , receiver = 2);<br>
><br>
</span>> slonik < <a href="http://unsubscribe2.sl" target="_blank">unsubscribe2.sl</a> <<a href="http://unsubscribe2.sl" target="_blank">http://unsubscribe2.sl</a>><br>
<span class="">> <stdin>:4: WARNING: out of shared memory<br>
> CONTEXT: SQL statement "drop trigger "_slcluster_logtrigger" on<br>
> "8359_wsn"."tbpgdas01502""<br>
> PL/pgSQL function altertabledroptriggers(integer) line 47 at EXECUTE<br>
> statement<br>
> SQL statement "SELECT "_slcluster".alterTableDropTriggers(v_tab_row.tab_id)"<br>
> PL/pgSQL function unsubscribeset(integer,integer,boolean) line 49 at PERFORM<br>
> <stdin>:4: PGRES_FATAL_ERROR lock table "_slcluster".sl_event_lock,<br>
> "_slcluster".sl_config_lock;select "_slcluster".unsubscribeSet(1,<br>
> 2,false); - ERROR: out of shared memory<br>
> HINT: You might need to increase max_locks_per_transaction.<br>
> CONTEXT: SQL statement "drop trigger "_slcluster_logtrigger" on<br>
> "8359_wsn"."tbpgdas01502""<br>
> PL/pgSQL function altertabledroptriggers(integer) line 47 at EXECUTE<br>
> statement<br>
> SQL statement "SELECT "_slcluster".alterTableDropTriggers(v_tab_row.tab_id)"<br>
> PL/pgSQL function unsubscribeset(integer,integer,boolean) line 49 at PERFORM<br>
> [postgres@00002-NfseNet-SGDB reimer]$ ^C<br>
><br>
> max_locks_per_transaction is set to 255. Changed to 4096 and will<br>
> restart the database during the night change window.<br>
><br>
> Tried to repeat the process again and now I'm getting always the same error:<br>
> [postgres@00002-NfseNet-SGDB reimer]$ slonik < <a href="http://unsubscribe2.sl" target="_blank">unsubscribe2.sl</a><br>
</span>> <<a href="http://unsubscribe2.sl" target="_blank">http://unsubscribe2.sl</a>><br>
<span class="">> <stdin>:4: PGRES_FATAL_ERROR lock table "_slcluster".sl_event_lock,<br>
> "_slcluster".sl_config_lock;select "_slcluster".unsubscribeSet(1,<br>
> 2,false); - ERROR: deadlock detected<br>
> DETAIL: Process 15366 waits for AccessExclusiveLock on relation 29564<br>
> of database 16384; blocked by process 14994.<br>
> Process 14994 waits for RowExclusiveLock on relation 84222 of database<br>
> 16384; blocked by process 15366.<br>
> HINT: See server log for query details.<br>
> CONTEXT: SQL statement "lock table "7481_spunico"."sincdc" in access<br>
> exclusive mode"<br>
> PL/pgSQL function altertabledroptriggers(integer) line 42 at EXECUTE<br>
> statement<br>
> SQL statement "SELECT "_slcluster".alterTableDropTriggers(v_tab_row.tab_id)"<br>
> PL/pgSQL function unsubscribeset(integer,integer,boolean) line 49 at PERFORM<br>
> [postgres@00002-NfseNet-SGDB reimer]$<br>
><br>
> The two pids reported by the deadlock message are probably temporary<br>
> processed created by SLONY as I did not find them in the system.<br>
><br>
> The increase in the max_locks_per_transactions and the server restart<br>
> will fix this issue?<br>
><br>
<br>
<br>
</span>If you have 27,000 tables in your replication set then I think<br>
max_locks_per_transaction might needs to be at least that big. The<br>
alterTableDropTriggers function will take a lock on each table in the set.<br>
<br>
As for your deadlock you should figure out what process 15366 is<br>
Since this operation takes an exclusive lock on all tables in the set on<br>
the replica you probably don't want any other processes accessing those<br>
tables at that point in time.<br>
<span class=""><br>
<br>
<br>
<br>
<br>
> Thank you!<br>
><br>
> --<br>
> Reimer<br>
> 47-3347-1724 47-9183-0547 msn: <a href="mailto:carlos.reimer@opendb.com.br">carlos.reimer@opendb.com.br</a><br>
</span>> <mailto:<a href="mailto:carlos.reimer@opendb.com.br">carlos.reimer@opendb.com.br</a>><br>
><br>
><br>
> _______________________________________________<br>
> Slony1-general mailing list<br>
> <a href="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</a><br>
> <a href="http://lists.slony.info/mailman/listinfo/slony1-general" target="_blank">http://lists.slony.info/mailman/listinfo/slony1-general</a><br>
><br>
<br>
_______________________________________________<br>
Slony1-general mailing list<br>
<a href="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</a><br>
<a href="http://lists.slony.info/mailman/listinfo/slony1-general" target="_blank">http://lists.slony.info/mailman/listinfo/slony1-general</a><br>
</blockquote></div><br clear="all"><br>-- <br><div class="gmail_signature">Reimer<br>47-3347-1724 47-9183-0547 msn: <a href="mailto:carlos.reimer@opendb.com.br">carlos.reimer@opendb.com.br</a><br></div>
</div>