Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-03-11 Thread Andrew Sullivan
On Sat, Feb 19, 2005 at 04:49:03PM -0500, [EMAIL PROTECTED] wrote: > PostgreSQL is such an awesome project. The only thing it seems to suffer > from is a disregard for its users. Gee. And all this time I thought that "free support from the guy who wrote the code and gave it to you" was better reg

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-21 Thread Bruce Momjian
Jeff wrote: > > On Feb 20, 2005, at 11:02 AM, Stephan Szabo wrote: > > > My last company's experience with Oracle support still leaves me > > questioning that claim. They basically got "don't do that then or > > move to > > the newest major revision" when they had a construct which caused the >

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-21 Thread Jeff
On Feb 20, 2005, at 11:02 AM, Stephan Szabo wrote: My last company's experience with Oracle support still leaves me questioning that claim. They basically got "don't do that then or move to the newest major revision" when they had a construct which caused the server to stop responding. For the re

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-20 Thread Mark Kirkwood
Tom Lane wrote: The question is whether we are willing to back-patch a fairly large amount of not-very-well-tested code into 8.0. See http://archives.postgresql.org/pgsql-patches/2005-02/msg00123.php http://archives.postgresql.org/pgsql-committers/2005-02/msg00127.php http://archives.postgresql.or

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-20 Thread Stephan Szabo
On Sun, 20 Feb 2005 [EMAIL PROTECTED] wrote: > > On Sun, 20 Feb 2005 [EMAIL PROTECTED] wrote: > > > >> > On Sat, Feb 19, 2005 at 18:04:42 -0500, > >> >> > >> >> Now, lets imagine PostgreSQL is being developed by a large company. > >> QA > >> >> announces it has found a bug that will cause all the

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-20 Thread pgsql
> On Sun, 20 Feb 2005 [EMAIL PROTECTED] wrote: > >> > On Sat, Feb 19, 2005 at 18:04:42 -0500, >> >> >> >> Now, lets imagine PostgreSQL is being developed by a large company. >> QA >> >> announces it has found a bug that will cause all the users data to >> >> disappear if they don't run a maintenenc

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-20 Thread Stephan Szabo
On Sun, 20 Feb 2005 [EMAIL PROTECTED] wrote: > > On Sat, Feb 19, 2005 at 18:04:42 -0500, > >> > >> Now, lets imagine PostgreSQL is being developed by a large company. QA > >> announces it has found a bug that will cause all the users data to > >> disappear if they don't run a maintenence program c

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-20 Thread Robert Treat
On Sunday 20 February 2005 00:30, Tom Lane wrote: > Mark Kirkwood <[EMAIL PROTECTED]> writes: > > To be fair to Mark, there does seem to be an increasing number of > > reports of this issue. In spite of the in-the-works fix for 8.1, it > > would be a pity to see customers losing data from xid wrap-

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-20 Thread pgsql
> On Sat, Feb 19, 2005 at 18:04:42 -0500, >> >> Now, lets imagine PostgreSQL is being developed by a large company. QA >> announces it has found a bug that will cause all the users data to >> disappear if they don't run a maintenence program correctly. Vacuuming >> one >> or two tables is not enoug

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-20 Thread Bruno Wolff III
On Sat, Feb 19, 2005 at 18:04:42 -0500, > > Now, lets imagine PostgreSQL is being developed by a large company. QA > announces it has found a bug that will cause all the users data to > disappear if they don't run a maintenence program correctly. Vacuuming one > or two tables is not enough, you ha

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread Tom Lane
Mark Kirkwood <[EMAIL PROTECTED]> writes: > To be fair to Mark, there does seem to be an increasing number of > reports of this issue. In spite of the in-the-works fix for 8.1, it > would be a pity to see customers losing data from xid wrap-around. The question is whether we are willing to back-

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread pgsql
> [ Shrugs ] and looks at other database systems ... > > CA has put Ingres into Open Source last year. > > Very reliable system with a replicator worth looking at. > > Just a thought. The discussion on hackers is how to make PostgreSQL better. There are many different perspectives, differences are

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread Mark Kirkwood
Andrew Dunstan wrote: There is no news in the problem you're complaining of. It's completely known and documented. You've stated before that you've been using PostgreSQL for years - why is this suddenly so urgent that we have to drop everything and backpatch old releases? Please move along, ther

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread Andrew Dunstan
[EMAIL PROTECTED] wrote: PostgreSQL is such an awesome project. The only thing it seems to suffer from is a disregard for its users. Mark, This is completely untrue and very offensive. Here's a tip I've often found useful even though I have also often ignored it (and later regretted doing so)

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread pgsql
> On Sat, Feb 19, 2005 at 13:35:25 -0500, > [EMAIL PROTECTED] wrote: >> >> The catastrophic failure of the database because a maintenence function >> is >> not performed is a problem with the software, not with the people using >> it. > > There doesn't seem to be disagreement that something shoul

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread pgsql
> On Fri, 18 Feb 2005 22:35:31 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: >> [EMAIL PROTECTED] writes: >> > I think there should be a 100% no data loss fail safe. >> >> Possibly we need to recalibrate our expectations here. The current >> situation is that PostgreSQL will not lose data if: >> >>

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread Bruno Wolff III
On Sat, Feb 19, 2005 at 13:35:25 -0500, [EMAIL PROTECTED] wrote: > > The catastrophic failure of the database because a maintenence function is > not performed is a problem with the software, not with the people using > it. There doesn't seem to be disagreement that something should be done goi

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread lsunley
In <[EMAIL PROTECTED]>, on 02/19/05 at 02:23 PM, Jaime Casanova <[EMAIL PROTECTED]> said: >On Fri, 18 Feb 2005 22:35:31 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > >[EMAIL PROTECTED] writes: >> > I think there should be a 100% no data loss fail safe. >> >> Possibly we need to recalibrate our

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread Jaime Casanova
On Fri, 18 Feb 2005 22:35:31 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] writes: > > I think there should be a 100% no data loss fail safe. > > Possibly we need to recalibrate our expectations here. The current > situation is that PostgreSQL will not lose data if: > >1

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread Jürgen Cappel
[ Shrugs ] and looks at other database systems ... CA has put Ingres into Open Source last year. Very reliable system with a replicator worth looking at. Just a thought. Ursprüngliche Nachricht Betreff: Re: [HACKERS] Data loss, vacuum, transaction wrap-around Datum: Sat, 19 Feb

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-19 Thread pgsql
> [EMAIL PROTECTED] writes: >> I think there should be a 100% no data loss fail safe. OK, maybe I was overly broad in my statement, but I assumed a context that I guess you missed. Don't you think that in normal operations, i.e. with no hardware of OS failure, we should see any data loss as unacce

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Tom Lane
[EMAIL PROTECTED] writes: > I think there should be a 100% no data loss fail safe. Possibly we need to recalibrate our expectations here. The current situation is that PostgreSQL will not lose data if: 1. Your disk drive doesn't screw up (eg, lie about write complete, or just

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread lsunley
In <[EMAIL PROTECTED]>, on 02/18/05 at 09:48 PM, Andrew Dunstan <[EMAIL PROTECTED]> said: >Russell Smith wrote: >>On Sat, 19 Feb 2005 04:10 am, Tom Lane wrote: >> >> >>>[EMAIL PROTECTED] writes: >>> >>> In fact, I think it is so bad, that I think we need to back-port a fix to

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Andrew Dunstan
Russell Smith wrote: On Sat, 19 Feb 2005 04:10 am, Tom Lane wrote: [EMAIL PROTECTED] 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

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread pgsql
> On Sat, 19 Feb 2005 04:10 am, Tom Lane wrote: >> [EMAIL PROTECTED] 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 forcibl

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Russell Smith
On Sat, 19 Feb 2005 04:10 am, Tom Lane wrote: > [EMAIL PROTECTED] 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

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Tom Lane
Greg Stark <[EMAIL PROTECTED]> writes: > 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

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> 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 d

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Joshua D. Drake
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 rep

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Tom Lane
[EMAIL PROTECTED] 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 par

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Tom Lane
"Joshua D. Drake" <[EMAIL PROTECTED]> 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

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Joshua D. Drake
Tom Lane wrote: [EMAIL PROTECTED] 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 reasonabl

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread pgsql
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.

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Tom Lane
"Matthew T. O'Connor" 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

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Matthew T. O'Connor
Tom Lane wrote: [EMAIL PROTECTED] 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 re

Re: [HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread Tom Lane
[EMAIL PROTECTED] 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-p

[HACKERS] Data loss, vacuum, transaction wrap-around

2005-02-18 Thread pgsql
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