2

Autovacuuming process holds RowExclusiveLock and ShareUpdateExclusiveLock on the table, which I want to drop. I read that autovacuum should be canceled automatically, when submitting DROP TABLE command, but it doesn't happen. Maybe I have wrong configuration of postgresql? Furthermore I don't have any delete or update operations of those tables — only inserts and drops. Can I disable autovacuum on those particular tables?

1

If no delete or update happened, autovacuum shouldn't process the table, per autovacuum_analyze_threshold. See Automatic Vacuuming for the relevant configuration parameters.

You may set log_autovacuum_min_duration to zero to help figure out what's being autovacuumed.

In any case, it is possible to completely disable autovacuum for a particular table, with:

ALTER TABLE tablename SET (autovacuum_enabled = false,  toast.autovacuum_enabled = false);

This is documented in Storage parameters.

  • Thank you, Daniel! I'll try it. Сan I tell to autovacuum to release locks in addition when I try to drop table? – Borodin.Mik Aug 28 '14 at 6:46
1

Is the autovac "to prevent wraparound" (as reported in pg_stat_activity)? If so, PostgreSQL considers that an emergency and refuses to yield the lock.

Which is rather unfortunate in this case, as once you drop the table the situation will be resolved. But it doesn't know why you want the lock.

0

If You use Linux you should list processes with ps aux and find Autovacuum process. Then just kill it(autovacuum should be working as another process if it's turned on).

  • Unfortunately, it is not a suitable solution for this case. I have to drop the oldest table every day, so I can't do it manually. – Borodin.Mik Aug 27 '14 at 14:45
0

"In any case, it is possible to completely disable autovacuum for a particular table."

No, you can not disable autovacuum if it is to prevent transaction ID wraparound.

  • What's the difference between your answer, and the answer posted by jjanes several years ago? This seems to be redundant. – RDFozz Sep 25 '17 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.