Re: DELETE takes too much memory

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(at)agliodbs(dot)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)
>