Re: Insert waiting for update?

<table cellspacing='0' cellpadding='0' border='0' ><tr><td style="font: inherit;">Thanks for the replay<br>I think you missed on second detail mail :<br><br><br><br><br>For more details:<br><br>I have two tables master,child.<br>with child having fk to master.<br><br>Now
that master table contains 4M rows . while I update them (Master table)
the inserts are going into waiting mode on child table.<br><br><br>Update&nbsp;
acquired row exclusive lock on master table&nbsp; , and the insert on child
table acquired access share and share lock,row share on master. <br><br>Is
this is what preventing for inserting records into child table, I mean
is the row exclusive lock conflicting with the locks insert(which is again row exclusive log) wants to
hold?<br><br><br><br>With Regards<br>Ashish...<br><br><br>--- On <b>Wed, 9/1/08, Albe Laurenz <i>&lt;laurenz(dot)albe(at)wien(dot)gv(dot)at&gt;</i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;">From: Albe Laurenz &lt;laurenz(dot)albe(at)wien(dot)gv(dot)at&gt;<br>Subject: Re: [GENERAL] Insert waiting for update?<br>To: ashish_postgre(at)yahoo(dot)co(dot)in, "pggeneral" &lt;pgsql-general(at)postgresql(dot)org&gt;<br>Cc: ashish(dot)karalkar(at)netcore(dot)co(dot)in<br>Date: Wednesday, 9 January, 2008, 8:53 PM<br><br><pre>Ashish Karalkar wrote:<br>&gt; I am having table with 4M rows.<br>&gt; I am trying to update all these rows with statement<br>&gt; <br>&gt; update mytable set mycolumn=0;<br>&gt; <br>&gt; At the same time there are insert happening on the table.<br>&gt; but all these insert are in waiting mode. <br>&gt; does update is locking the table for insert?<br>&gt; <br>&gt; does insert and update confilict with each other?<br><br>Not normally, but
it can happen.<br><br>You could<br><br>SELECT l.locktype, t.relname, l.pid, l.transactionid, l.mode, l.granted<br>FROM pg_catalog.pg_locks l LEFT OUTER JOIN<br> pg_catalog.pg_class t ON l.relation = t.oid;<br><br>and<br><br>SELECT procpid, current_query FROM pg_stat_activity;<br><br>while the inserts hang. Maybe the result will indicate why.<br><br>Are there any triggers or rules defined?<br>What indexes are defined on the table?<br><br>Yours,<br>Laurenz Albe<br><br><br><br><br><br><br><br><br>---------------------------(end of broadcast)---------------------------<br>TIP 9: In versions below 8.0, the planner will ignore your desire to<br> choose an index scan if your joining column's datatypes do not<br> match</pre></blockquote></td></tr></table><br>

<!--10--><hr size=1></hr> Chat on a cool, new interface. No download required. <a href="http://in.rd.yahoo.com/tagline_webmessenger_10/*http://in.messenger.yahoo.com/webmessengerpromo.php">Click here.</a>