MIME-Version: 1.0
In-Reply-To: <577C01CA.6080208@agliodbs.com>
References: 
 <CAN4RuQtCYxQg0u_NUncLAyLE-Yd9Yv1Ont5R2skJ53Xygvc4Xg@mail.gmail.com>
 <20160704170458.GA425951@alvherre.pgsql>
 <CAN4RuQt5GZEbkHdPmm=j-z2uE3m3U-USWFNhVRiqYAKv5BZqhg@mail.gmail.com>
 <WM!cb06fe75a5c0ee967d48ec5d57f361731ccb0ee74599ec4d865cc727bb7dab293be12a0acd0803fc4c1269ab1f896f29!@mailstronghold-2.zmailcloud.com>
 <577C01CA.6080208@agliodbs.com>
From: Kouber Saparev <kouber@gmail.com>
Date: Wed, 6 Jul 2016 00:03:30 +0300
Message-ID: 
 <CAN4RuQtDxv0tAs=bY-6RyhSfUxPRc=XJV-6iLrP+yyE9snJDQQ@mail.gmail.com>
Subject: Re: DELETE takes too much memory
To: Josh Berkus <josh@agliodbs.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
 pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001a113d72b6fa12e00536e9c892
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a113d72b6fa12e00536e9c892
Content-Type: text/plain; charset=UTF-8

Well, basically there are only INSERTs going on there (it is a table
holding audit records for each DML statement). I do not see how a DELETE
statement could block an INSERT?

You are correct that rebuilding the table will be faster, but then, there
is a chance that some INSERT's will be blocked and eventually will fail
(depending on the duration of the rebuilding, the exact moment I run it,
and the involved operations on the other tables).

Could such a memory consumption be related to a GET DIAGNOSTICS plpgsql
block? The delete itself is within a stored procedure, and then I return
the amount of the deleted rows from the function:

DELETE FROM
  audits.audits
WHERE
  id <= last_synced_audits_id;

GET DIAGNOSTICS counter = ROW_COUNT;

RETURN counter;


2016-07-05 21:51 GMT+03:00 Josh Berkus <josh@agliodbs.com>:

> On 07/04/2016 10:10 AM, Kouber Saparev wrote:
> > No. There are AFTER triggers on other tables that write to this one
> > though. It is an audits table, so I omitted all the foreign keys on
> purpose.
>
> Is it possible that the DELETE blocked many of those triggers due to
> locking the same rows?
>
> Incidentally, any time I get into deleting large numbers of rows, I
> generally find it faster to rebuild the table instead ...
>
> --
> --
> Josh Berkus
> Red Hat OSAS
> (any opinions are my own)
>

--001a113d72b6fa12e00536e9c892
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Well, basically there are only INSERTs going on there (it =
is a table holding audit records for each DML statement). I do not see how =
a DELETE statement could block an INSERT?<div><br></div><div>You are correc=
t that rebuilding the table will be faster, but then, there is a chance tha=
t some INSERT&#39;s will be blocked and eventually will fail (depending on =
the duration of the rebuilding, the exact moment I run it, and the involved=
 operations on the other tables).</div><div><br></div><div>Could such a mem=
ory consumption be related to a GET DIAGNOSTICS plpgsql block? The delete i=
tself is within a stored procedure, and then I return the amount of the del=
eted rows from the function:</div><div><br></div><div><div>DELETE FROM</div=
><div>=C2=A0 audits.audits</div><div>WHERE</div><div>=C2=A0 id &lt;=3D last=
_synced_audits_id;</div><div><br></div><div>GET DIAGNOSTICS counter =3D ROW=
_COUNT;</div><div><br></div><div>RETURN counter;</div><div><br></div></div>=
</div><div class=3D"gmail_extra"><br><div class=3D"gmail_quote">2016-07-05 =
21:51 GMT+03:00 Josh Berkus <span dir=3D"ltr">&lt;<a href=3D"mailto:josh@ag=
liodbs.com" target=3D"_blank">josh@agliodbs.com</a>&gt;</span>:<br><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc s=
olid;padding-left:1ex"><span class=3D"">On 07/04/2016 10:10 AM, Kouber Sapa=
rev wrote:<br>
&gt; No. There are AFTER triggers on other tables that write to this one<br=
>
&gt; though. It is an audits table, so I omitted all the foreign keys on pu=
rpose.<br>
<br>
</span>Is it possible that the DELETE blocked many of those triggers due to=
<br>
locking the same rows?<br>
<br>
Incidentally, any time I get into deleting large numbers of rows, I<br>
generally find it faster to rebuild the table instead ...<br>
<span class=3D"HOEnZb"><font color=3D"#888888"><br>
--<br>
--<br>
Josh Berkus<br>
Red Hat OSAS<br>
(any opinions are my own)<br>
</font></span></blockquote></div><br></div>

--001a113d72b6fa12e00536e9c892--