Insert waiting for update?

<table cellspacing='0' cellpadding='0' border='0' ><tr><td style="font: inherit;"><br><br>--- On <b>Wed, 9/1/08, Ashish Karalkar <i>&lt;ashish_postgre@yahoo.co.in&gt;</i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;">From: Ashish Karalkar &lt;ashish_postgre@yahoo.co.in&gt;<br>Subject: [GENERAL] Insert waiting for update?<br>To: "pggeneral" &lt;pgsql-general@postgresql.org&gt;<br>Cc: ashish.karalkar@netcore.co.in<br>Date: Wednesday, 9 January, 2008, 4:29 PM<br><br><div id="yiv2031388839"><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font-family: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; font-size: inherit; line-height: inherit; font-size-adjust: inherit; font-stretch: inherit;">Dear list members,<br>I am having table with 4M rows.<br>I am trying to update all these rows with statement<br><br>update mytable set mycolumn=0;<br><br>At
the same time there are insert happening on the table.<br>but all these insert are in waiting mode. <br>does update is locking the table for insert?<br><br>does insert and update confilict with each other?<br>With regards<br>Ashihsh<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 wants to hold?<br><br><br><br>With Regards<br>Ashish...<br><br><br><br><br><br><br><br><br></td></tr></tbody></table><br>

<hr size="1"> Save all your chat conversations. <a rel="nofollow" target="_blank" href="http://in.rd.yahoo.com/tagline_webmessenger_3/*http://in.messenger.yahoo.com/webmessengerpromo.php&quot;&gt;Find them online.</a>
</div></blockquote></td></tr></table><br>

<!--1--><hr size=1></hr> Why delete messages? Unlimited storage is <a href="http://in.rd.yahoo.com/tagline_mail_1/*http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html/&quot;&gt;just a click away.</a>

Ashish Karalkar wrote:

I am having table with 4M rows.
I am trying to update all these rows with statement

update mytable set mycolumn=0;

At the same time there are insert happening on the table.
but all these insert are in waiting mode.
does update is locking the table for insert?

does insert and update confilict with each other?

Not normally, but it can happen.

You could

SELECT l.locktype, t.relname, l.pid, l.transactionid, l.mode, l.granted
FROM pg_catalog.pg_locks l LEFT OUTER JOIN
pg_catalog.pg_class t ON l.relation = t.oid;

and

SELECT procpid, current_query FROM pg_stat_activity;

while the inserts hang. Maybe the result will indicate why.

Are there any triggers or rules defined?
What indexes are defined on the table?

Yours,
Laurenz Albe

<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.albe@wien.gv.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.albe@wien.gv.at&gt;<br>Subject: Re: [GENERAL] Insert waiting for update?<br>To: ashish_postgre@yahoo.co.in, "pggeneral" &lt;pgsql-general@postgresql.org&gt;<br>Cc: ashish.karalkar@netcore.co.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&quot;&gt;Click here.</a>

Ashish Karalkar wrote:

I am having table with 4M rows.
I am trying to update all these rows with statement

update mytable set mycolumn=0;

At the same time there are insert happening on the table.
but all these insert are in waiting mode.
does update is locking the table for insert?

does insert and update confilict with each other?

For more details:

I have two tables master,child.
with child having fk to master.

Now that master table contains 4M rows . while I update them
(Master table) the inserts are going into waiting mode on child table.

Update acquired row exclusive lock on master table , and
the insert on child table acquired access share and share
lock,row share on master.

Is this is what preventing for inserting records into child
table, I mean is the row exclusive lock conflicting with the
locks insert wants to hold?

Please post the table definitions including indexes and constraints.

Yours,
Laurenz Albe