Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fighting the autovacuumer (to prevent wraparound)

Fighting the autovacuumer (to prevent wraparound)

From
Michael Graham
Date:
Hi all,

We partition the data in postgres in a per-month basis and run a script
to delete old partitions.  Sometimes this script fails and the delete
doesn't happen because of a deadlock, today I noticed that it was the
autovacuumer that fighting with the script.

Is it possible to stop the autovacuumer temporarily (and cancel any on
going autovacuum) so that my script can remove the table that the
autovacuumer wants to vacuum?

I'm on 9.1.4 if it matter.

Cheers,
--
Michael Graham <mgraham@bloxx.com>

Re: Fighting the autovacuumer (to prevent wraparound)

From
bricklen
Date:

On Thu, Oct 3, 2013 at 11:48 AM, Michael Graham <mgraham@bloxx.com> wrote:
Hi all,

We partition the data in postgres in a per-month basis and run a script
to delete old partitions.

Does "delete" = "drop"?
 
 Sometimes this script fails and the delete
doesn't happen because of a deadlock, today I noticed that it was the
autovacuumer that fighting with the script.

Are you uninheriting the partition before attempting to get rid of it?
Eg.
ALTER TABLE your_partition NO INHERIT the_parent_table;




Re: Fighting the autovacuumer (to prevent wraparound)

From
Michael Graham
Date:
On Thu, 2013-10-03 at 11:53 -0700, bricklen wrote:
>
> On Thu, Oct 3, 2013 at 11:48 AM, Michael Graham <mgraham@bloxx.com>
> wrote:
>         Hi all,
>
>         We partition the data in postgres in a per-month basis and run
>         a script
>         to delete old partitions.
>
>
> Does "delete" = "drop"?

No I'm truncating the tables for that partition (sorry I should have
made that clear in my original message).


>          Sometimes this script fails and the delete
>         doesn't happen because of a deadlock, today I noticed that it
>         was the
>         autovacuumer that fighting with the script.
>
>
> Are you uninheriting the partition before attempting to get rid of it?
> Eg.
>
> ALTER TABLE your_partition NO INHERIT the_parent_table;

I'm not no, will that help?  I see that the autovacuumer is vacuuming
the actual partition that I want to truncate to the parent but I do do
an analyse on the parent table after the truncate but in the same
transaction.

Thanks,
--
Michael Graham <mgraham@bloxx.com>

Re: Fighting the autovacuumer (to prevent wraparound)

From
John R Pierce
Date:
On 10/3/2013 11:53 AM, bricklen wrote:
> Are you uninheriting the partition before attempting to get rid of it?
> Eg.
> ALTER TABLE your_partition NO INHERIT the_parent_table;

in a completely different but similar scenario, we've tried this, but NO
INHERIT also blocks and and can deadlock if there's locks on the parent
table, which in our case has nearly continuous OLTP style insert,
update, and select queries running



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast