Обсуждение: BUG #4059: Vacuum full not always cleaning empty tables
The following bug has been logged online: Bug reference: 4059 Logged by: Mark Steben Email address: msteben@autorevenue.com PostgreSQL version: 8.2.5 Operating system: Linux redhat Description: Vacuum full not always cleaning empty tables Details: I sent an email to 'pgsql-bugs@postgresql.org' entitled 'Possible bug with VACUUM FULL' and also an addendum But neglected to also send this bug report form. You can refer to those emails; in a nutshell, I have found that VACUUM FULL will not clean dead tuples out of an empty table when other queries are accessing the database. Test plans/scenarios are included.
Mark Steben wrote: > I sent an email to 'pgsql-bugs@postgresql.org' entitled > 'Possible bug with VACUUM FULL' and also an addendum > But neglected to also send this bug report form. > You can refer to those emails; in a nutshell, I have found that VACUUM FULL > will not clean dead tuples out of an empty table when other queries are > accessing the database. Test plans/scenarios are included. Yes, if other sessions have open transactions whose start time predate the start time of the transaction that deletes of tuples, those tuples cannot be removed by VACUUM. This is known and expected. Is this what's happening here? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Mark Steben wrote: > Yes, there are transactions that predate the start time of the vacuum full. > However those transactions do not touch the table being vacuumed, they have > nothing to do with it. Is this the expected behavior? Yes. The system has no way to know that they will not examine the table in the future, so it has to be conservative about what it removes. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Yes, there are transactions that predate the start time of the vacuum full. However those transactions do not touch the table being vacuumed, they have nothing to do with it. Is this the expected behavior? Mark Steben Senior Database Administrator @utoRevenueT A Dominion Enterprises Company 480 Pleasant Street Suite B200 Lee, MA 01238 413-243-4800 Home Office 413-243-4809 Corporate Fax msteben@autorevenue.com Visit our new website at www.autorevenue.com IMPORTANT: The information contained in this e-mail message is confidential and is intended only for the named addressee(s). If the reader of this e-mail message is not the intended recipient (or the individual responsible for the delivery of this e-mail message to the intended recipient), please be advised that any re-use, dissemination, distribution or copying of this e-mail message is prohibited. If you have received this e-mail message in error, please reply to the sender that you have received this e-mail message in error and then delete it. Thank you. -----Original Message----- From: Alvaro Herrera [mailto:alvherre@commandprompt.com] Sent: Tuesday, March 25, 2008 9:24 PM To: Mark Steben Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #4059: Vacuum full not always cleaning empty tables Mark Steben wrote: > I sent an email to 'pgsql-bugs@postgresql.org' entitled > 'Possible bug with VACUUM FULL' and also an addendum > But neglected to also send this bug report form. > You can refer to those emails; in a nutshell, I have found that VACUUM FULL > will not clean dead tuples out of an empty table when other queries are > accessing the database. Test plans/scenarios are included. Yes, if other sessions have open transactions whose start time predate the start time of the transaction that deletes of tuples, those tuples cannot be removed by VACUUM. This is known and expected. Is this what's happening here? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Mark Steben wrote: > [Mark Steben] Sorry for pressing the point, just making sure I > > understand what you're saying. > > So, the only way to ensure that a vacuum full will clean out > an > > Empty table is to run it on a stand-alone server? No -- the other alternative is to wait long enough for those other transactions to go away. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "La primera ley de las demostraciones en vivo es: no trate de usar el sistema. Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)