VACUUM and transaction ID wraparound

On Fri, May 16, 2003 at 09:11:19PM -0700, Dean Gibson (DB Administrator) wrote:

The documentation says (Admin Guide, 8.2.3), "... each database-wide VACUUM automatically delivers a warning" if individual tables need VACUUMing to prevent transaction ID wraparound.

But I thought a database-wide VACUUM automatically VACUUMed each table in the database (Ref Manual, 'VACUUM'), making (at least to my way of thinking) VACUUMing an individual table unnecessary, and thus making the warning superfluous.

Is there something here I don't understand?

I beleive they are referring to the difference between VACUUM and VACUUM
FULL. The former is faster and doesn't lock tables, but the latter is
required to solve transaction wraparound.

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

"Dean Gibson (DB Administrator)" <dba-sql@ultimeth.net> writes:

The documentation says (Admin Guide, 8.2.3), "... each database-wide
VACUUM automatically delivers a warning" if individual tables need
VACUUMing to prevent transaction ID wraparound.

Not individual tables, but whole databases (databases other than the one
you just vacuumed, obviously).

Is there something here I don't understand?

The warning is intended to alert you in case you have entire databases
you've forgotten to vacuum for a good long while. It's a compromise
between being too much in-your-face and letting you go down to
disaster...

regards, tom lane

On Sat, May 17, 2003 at 02:34:19PM +1000, Martijn van Oosterhout wrote:

I beleive they are referring to the difference between VACUUM and VACUUM
FULL. The former is faster and doesn't lock tables, but the latter is
required to solve transaction wraparound.

It is? I didn't think VACUUM FULL was ever required.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Andrew Sullivan <andrew@libertyrms.info> writes:

On Sat, May 17, 2003 at 02:34:19PM +1000, Martijn van Oosterhout wrote:

I beleive they are referring to the difference between VACUUM and VACUUM
FULL. The former is faster and doesn't lock tables, but the latter is
required to solve transaction wraparound.

It is? I didn't think VACUUM FULL was ever required.

Either one will get the job done as far as avoiding wraparound goes.
If there's someplace in the docs that seems to you to give a different
impression, please tell me where.

regards, tom lane

On Wed, 21 May 2003, Andrew Sullivan wrote:

On Sat, May 17, 2003 at 02:34:19PM +1000, Martijn van Oosterhout wrote:

I beleive they are referring to the difference between VACUUM and VACUUM
FULL. The former is faster and doesn't lock tables, but the latter is
required to solve transaction wraparound.

It is? I didn't think VACUUM FULL was ever required.

I'm not sure if it's vacuum full or vacuum, but I do know you have to
vacuum ALL the tables in ALL your databases to prevent wrap around.

Full or not, I'm not really sure. But I run fulls every night anyway.

On Thu, May 22, 2003 at 10:11:17AM -0400, Tom Lane wrote:

Andrew Sullivan <andrew@libertyrms.info> writes:

On Sat, May 17, 2003 at 02:34:19PM +1000, Martijn van Oosterhout wrote:

I beleive they are referring to the difference between VACUUM and VACUUM
FULL. The former is faster and doesn't lock tables, but the latter is
required to solve transaction wraparound.

It is? I didn't think VACUUM FULL was ever required.

Either one will get the job done as far as avoiding wraparound goes.
If there's someplace in the docs that seems to you to give a different
impression, please tell me where.

Sorry, must be my memory. I remember something about requiring a VACUUM FULL
at least once every two billion transactions. I must have misremembered,
sorry.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/