I want to see if there is a concensus of opinion out there.

We've all known that data loss "could" happen if vacuum is not run and you
perform more than 2b transactions. These days with faster and bigger
computers and disks, it more likely that this problem can be hit in months
-- not years.

To me, the WORST thing a program can do is lose data. (Certainly this is
bad for a database.) I don't think there is any real excuse for this.
While the 2b transaction problem was always there, it seemed so remote
that I never obcessed about it. Now that it seems like a real problem that
more than one user has hit, I am worried.

In fact, I think it is so bad, that I think we need to back-port a fix to
previous versions and issue a notice of some kind.

Here as my suggestions:

(1) As Tom has already said, at some point start issuing warning in the
log that vacuum needs to be run.

(2) At some point, stop accepting transactions on anything but template1,
issuing an error saying the vacuum needs to be run.

(3) Either with psql on template1 or "postgres" or some "vacuumall"
program, open the database in single user mode or on template1 and vacuum
database.

(4) This should remain even after autovacuum is in place. If for some
reason auto vacuum is installed but not running, we still need to protect
the data from a stupid admin. (Last time I looked, auto vacuum used
various stats, and that may be something an admin disables.)

(5) Vacuum could check for a wrap-around condition in the database cluster
and take it upon itself to run more broadly even if it was directed only
towards a table.

We've been saying that mysql is ok if you don't care about your data, I
would hate if people started using this issue against postgresql.



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

pgsql@mohawksoft.com writes:
> In fact, I think it is so bad, that I think we need to back-port a fix to
> previous versions and issue a notice of some kind.

They already do issue notices --- see VACUUM.

A real fix (eg the forcible stop we were talking about earlier) will not
be reasonable to back-port.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane wrote:

>pgsql@mohawksoft.com writes:
>
>
>>In fact, I think it is so bad, that I think we need to back-port a fix to
>>previous versions and issue a notice of some kind.
>>
>>
>
>They already do issue notices --- see VACUUM.
>
>A real fix (eg the forcible stop we were talking about earlier) will not
>be reasonable to back-port.
>

I hope this question isn't too stupid....

Is it be possible to create a "vacuum wraparound" or "vacuum xidreset"
command which would do the work required to fix the wraparound problem,
without being as expensive as a normal vacuum of an entire database?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

More suggestions:

(1) At startup, postmaster checks for an XID, if it is close to a problem,
force a vacuum.

(2) At "sig term" shutdown, can the postmaster start a vacuum?

(3) When the XID count goes past the "trip wire" can it spontaneously
issue a vacuum?


NOTE:
Suggestions 1 and 2 are for 8.0 and prior. 3 is for later than 8.0.1


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

"Matthew T. O'Connor" <matthew@zeut.net> writes:
> I hope this question isn't too stupid....

> Is it be possible to create a "vacuum wraparound" or "vacuum xidreset"
> command which would do the work required to fix the wraparound problem,
> without being as expensive as a normal vacuum of an entire database?

I don't think it'd be worth the trouble. You could skip index cleanup
if you didn't actually delete any tuples, but you'd still have to do all
of the scanning work. The cases where people think they don't need to
do vacuum (because the table didn't have any deleted tuples) wouldn't
get any cheaper at all.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:
> pgsql@mohawksoft.com writes:
>
>>In fact, I think it is so bad, that I think we need to back-port a fix to
>>previous versions and issue a notice of some kind.
>
>
> They already do issue notices --- see VACUUM.
>
> A real fix (eg the forcible stop we were talking about earlier) will not
> be reasonable to back-port.

Would at least a automated warning mechanism be a reasonable backport?

Sincerely,

Joshua D. Drake


>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq


--
Command Prompt, Inc., your source for PostgreSQL replication,
professional support, programming, managed services, shared
and dedicated hosting. Home of the Open Source Projects plPHP,
plPerlNG, pgManage, and pgPHPtoolkit.
Contact us now at: +1-503-667-4564 - http://www.commandprompt.com



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Tom Lane wrote:
>> A real fix (eg the forcible stop we were talking about earlier) will not
>> be reasonable to back-port.

> Would at least a automated warning mechanism be a reasonable backport?

No, because the hard part of the problem actually is detecting that the
condition exists in a reasonably cheap way. The check in VACUUM is
really extremely expensive, which is why we don't make it except after
completing a database-wide vacuum. Once we have an XID limit value
sitting in shared memory then the code to use it (generate warnings
and/or error out) is simple; it's initializing that value during
postmaster start that I consider too complicated/risky to stick into
existing versions.

There is another issue here, which is that I have no faith that the
people who actually need this are going to be clueful enough to update
to 7.4.8 or 7.3.10 or whatever they'd need...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

pgsql@mohawksoft.com writes:
> More suggestions:
> (1) At startup, postmaster checks for an XID, if it is close to a problem,
> force a vacuum.

Useless to a system that's run 24x7; also presumes the existence of a
complete solution anyway (since getting the postmaster to find that out
is the hard part).

> (2) At "sig term" shutdown, can the postmaster start a vacuum?

Certainly not. We have to assume that SIGTERM means we are under a
short-term sentence of death from init. And if it's a manual stop
it doesn't sound much better: the sort of DBA that needs this "feature"
is likely to decide he should kill -9 the postmaster because it's taking
too long to shut down.

> (3) When the XID count goes past the "trip wire" can it spontaneously
> issue a vacuum?

Only in the database you're connected to, which very likely isn't where
the problem is. Moreover, having N backends all decide they need to do
this at once doesn't sound like a winner. Furthermore, this still
presumes the existence of the hard part of the solution, which is
knowing where the trip point is.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> There is another issue here, which is that I have no faith that the
> people who actually need this are going to be clueful enough to update
> to 7.4.8 or 7.3.10 or whatever they'd need...

Well I can't argue with that one

>
> regards, tom lane


--
Command Prompt, Inc., your source for PostgreSQL replication,
professional support, programming, managed services, shared
and dedicated hosting. Home of the Open Source Projects plPHP,
plPerlNG, pgManage, and pgPHPtoolkit.
Contact us now at: +1-503-667-4564 - http://www.commandprompt.com



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Tom Lane <tgl@sss.pgh.pa.us> writes:

> > (3) When the XID count goes past the "trip wire" can it spontaneously
> > issue a vacuum?
>
> Only in the database you're connected to, which very likely isn't where
> the problem is. Moreover, having N backends all decide they need to do
> this at once doesn't sound like a winner. Furthermore, this still
> presumes the existence of the hard part of the solution, which is
> knowing where the trip point is.

Alright, I have a suggestion. If the database kept a "oldest xid" for each
table then there wouldn't be any expensive work to scan the table looking for
an oldest xid. The only time "oldest xid" needs to be updated is when vacuum
is run, which is precisely when it would be known.

There could be a per-database "oldest xid" that any vacuum on any table
updates (by skimming all the "oldest xid"s for the current database). If
that's stored in the shared pg_database table then it's accessible regardless
of what database you connect to, no?

Then on every connection and every n-thousandth transaction you just have to
check the "oldest xid" for all the databases, and make sure the difference
between the oldest one and the current xid is reasonable.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match