public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kouber Saparev <[email protected]>
To: Josh Berkus <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: [email protected]
Subject: Re: DELETE takes too much memory
Date: Wed, 6 Jul 2016 00:03:30 +0300
Message-ID: <CAN4RuQtDxv0tAs=bY-6RyhSfUxPRc=XJV-6iLrP+yyE9snJDQQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAN4RuQtCYxQg0u_NUncLAyLE-Yd9Yv1Ont5R2skJ53Xygvc4Xg@mail.gmail.com>
<[email protected]>
<CAN4RuQt5GZEbkHdPmm=j-z2uE3m3U-USWFNhVRiqYAKv5BZqhg@mail.gmail.com>
<WM!cb06fe75a5c0ee967d48ec5d57f361731ccb0ee74599ec4d865cc727bb7dab293be12a0acd0803fc4c1269ab1f896f29!@mailstronghold-2.zmailcloud.com>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 <[email protected]>:
> 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)
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: DELETE takes too much memory
In-Reply-To: <CAN4RuQtDxv0tAs=bY-6RyhSfUxPRc=XJV-6iLrP+yyE9snJDQQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox