On Wed, 16 Feb 2005, Joshua D. Drake wrote:

>
> >Do you have a useful suggestion about how to fix it? "Stop working" is
> >handwaving and merely basically saying, "one of you people should do
> >something about this" is not a solution to the problem, it's not even an
> >approach towards a solution to the problem.
> >
> >
> I believe that the ability for PostgreSQL to stop accepting
> queries and to log to the file or STDERR why it stopped working
> and how to resolve it is appropriate.

Right, but since the how to resolve it currently involves executing a
query, simply stopping dead won't allow you to resolve it. Also, if we
stop at the exact wraparound point, can we run into problems actually
trying to do the vacuum if that's still the resolution technique? If so,
how far in advance of wraparound must we stop to guarantee it will
succeed? It's not rocket science, but figuring such things out is part of
actually making a workable solution.



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

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

On Wed, 16 Feb 2005 pgsql@mohawksoft.com wrote:

> > On Wed, 16 Feb 2005 pgsql@mohawksoft.com wrote:
> >
> >> >
> >> > Once autovacuum gets to the point where it's used by default, this
> >> > particular failure mode should be a thing of the past, but in the
> >> > meantime I'm not going to panic about it.
> >>
> >> I don't know how to say this without sounding like a jerk, (I guess
> >> that's
> >> my role sometimes) but would you go back and re-read this sentence?
> >>
> >> To paraphrase: "I know this causes a catestrophic data loss, and we have
> >> plans to fix it in the future, but for now, I'm not going panic about
> >> it."
> >
> > Do you have a useful suggestion about how to fix it? "Stop working" is
> > handwaving and merely basically saying, "one of you people should do
> > something about this" is not a solution to the problem, it's not even an
> > approach towards a solution to the problem.
>
> Actually, it is not a solution to the problem of losing data. It is a drop
> dead last ditch failsafe that EVERY PRODUCT should have before losing
> data.

Let's try again. Saying, "one of you people should do something about
this" is not a solution to the problem or an approach thereto. "Stop
working" is handwaving since I see no approach therein that allows the
user to actually recover the data.


---------------------------(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

>
> On Wed, 16 Feb 2005, Joshua D. Drake wrote:
>
>>
>> >Do you have a useful suggestion about how to fix it? "Stop working" is
>> >handwaving and merely basically saying, "one of you people should do
>> >something about this" is not a solution to the problem, it's not even
>> an
>> >approach towards a solution to the problem.
>> >
>> >
>> I believe that the ability for PostgreSQL to stop accepting
>> queries and to log to the file or STDERR why it stopped working
>> and how to resolve it is appropriate.
>
> Right, but since the how to resolve it currently involves executing a
> query, simply stopping dead won't allow you to resolve it. Also, if we
> stop at the exact wraparound point, can we run into problems actually
> trying to do the vacuum if that's still the resolution technique? If so,
> how far in advance of wraparound must we stop to guarantee it will
> succeed? It's not rocket science, but figuring such things out is part of

I would say, have a GUC parameter set at 1000 transactions. When fewer
than this number are available, postmaster will not run and issue a
message

"Transaction wrap-around error! You must run vacuum in stingle user
postgres mode to correct it, to avoid this message run the vacuum command
more frequently"

Hell, why not block all the PostgreSQL processes and run vacuum? But, for
now, versions of PostgreSQL should stop before losing data.


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

>
> On Wed, 16 Feb 2005 pgsql@mohawksoft.com wrote:
>
>> > On Wed, 16 Feb 2005 pgsql@mohawksoft.com wrote:
>> >
>> >> >
>> >> > Once autovacuum gets to the point where it's used by default, this
>> >> > particular failure mode should be a thing of the past, but in the
>> >> > meantime I'm not going to panic about it.
>> >>
>> >> I don't know how to say this without sounding like a jerk, (I guess
>> >> that's
>> >> my role sometimes) but would you go back and re-read this sentence?
>> >>
>> >> To paraphrase: "I know this causes a catestrophic data loss, and we
>> have
>> >> plans to fix it in the future, but for now, I'm not going panic about
>> >> it."
>> >
>> > Do you have a useful suggestion about how to fix it? "Stop working"
>> is
>> > handwaving and merely basically saying, "one of you people should do
>> > something about this" is not a solution to the problem, it's not even
>> an
>> > approach towards a solution to the problem.
>>
>> Actually, it is not a solution to the problem of losing data. It is a
>> drop
>> dead last ditch failsafe that EVERY PRODUCT should have before losing
>> data.
>
> Let's try again. Saying, "one of you people should do something about
> this" is not a solution to the problem or an approach thereto. "Stop
> working" is handwaving since I see no approach therein that allows the
> user to actually recover the data.
>


Well, it is sort of the the Hockey strike, now that it seems like stoping
normal operation is better than losing billions of rows of data. We can
decide who to do it and how to correct it.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Right, but since the how to resolve it currently involves executing a
> query, simply stopping dead won't allow you to resolve it. Also, if we
> stop at the exact wraparound point, can we run into problems actually
> trying to do the vacuum if that's still the resolution technique?

We'd have to do something with a fair amount of slop. The idea I was
toying with just now involved a forcible shutdown once we get within
say 100,000 transactions of a wrap failure; but apply this check only
when in interactive operation. This would allow the DBA to perform
the needed VACUUMing manually in a standalone backend.

The real question here is exactly how large a cluestick do you want to
hit the DBA with. I don't think we can "guarantee" no data loss with
anything less than forced shutdown, but that's not so much a cluestick
as a clue howitzer.

Maybe

(a) within 200,000 transactions of wrap, every transaction start
delivers a WARNING message;

(b) within 100,000 transactions, forced shutdown as above.

regards, tom lane

---------------------------(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

On Wed, 16 Feb 2005, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > Right, but since the how to resolve it currently involves executing a
> > query, simply stopping dead won't allow you to resolve it. Also, if we
> > stop at the exact wraparound point, can we run into problems actually
> > trying to do the vacuum if that's still the resolution technique?
>
> We'd have to do something with a fair amount of slop. The idea I was
> toying with just now involved a forcible shutdown once we get within
> say 100,000 transactions of a wrap failure; but apply this check only
> when in interactive operation. This would allow the DBA to perform
> the needed VACUUMing manually in a standalone backend.
>
> The real question here is exactly how large a cluestick do you want to
> hit the DBA with. I don't think we can "guarantee" no data loss with
> anything less than forced shutdown, but that's not so much a cluestick
> as a clue howitzer.
>
> Maybe
>
> (a) within 200,000 transactions of wrap, every transaction start
> delivers a WARNING message;
>
> (b) within 100,000 transactions, forced shutdown as above.

This seems reasonable, although perhaps the former could be something
configurable. I'm not sure there's a good reason to allow the latter to
change unless there'd ever be a case where 100,000 transactions wasn't
enough to vacuum or something like that.

All in all, I figure that odds are very high that if someone isn't
vacuuming in the rest of the transaction id space, either the transaction
rate is high enough that 100,000 warning may not be enough or they aren't
going to pay attention anyway and the howitzer might not be bad.

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

Tom Lane wrote:

>Maybe
>
>(a) within 200,000 transactions of wrap, every transaction start
>delivers a WARNING message;
>
>(b) within 100,000 transactions, forced shutdown as above.
>
>
>
>

This seems sound enough, but if the DBA and/or SA can't be bothered
reading the docs where this topic features quite prominently, I suspect
the warning messages won't have much effect either. Basically ISTM we're
talking about people who *need* a clue howitzer.

This will possibly hit us more now we have the Windows port (or maybe
not, if the Windows servers are regularly rebooted ;-) )

cheers

andrew

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

Stephan Szabo wrote:
> On Wed, 16 Feb 2005, Tom Lane wrote:
>
>>Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>>
>>(a) within 200,000 transactions of wrap, every transaction start
>>delivers a WARNING message;
>>
>>(b) within 100,000 transactions, forced shutdown as above.
>
>
> This seems reasonable, although perhaps the former could be something
> configurable. I'm not sure there's a good reason to allow the latter to
> change unless there'd ever be a case where 100,000 transactions wasn't
> enough to vacuum or something like that.
>
> All in all, I figure that odds are very high that if someone isn't
> vacuuming in the rest of the transaction id space, either the transaction
> rate is high enough that 100,000 warning may not be enough or they aren't
> going to pay attention anyway and the howitzer might not be bad.

How would people feel about stopping after the first 100 transactions too?
Pro: Teaches the lesson straight away.
Con: Irritating
Con: Might not be enough time for automated installers

--
Richard Huxton
Archonet Ltd

---------------------------(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

On Wed, Feb 16, 2005 at 09:38:31 -0800,
Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> On Wed, 16 Feb 2005, Tom Lane wrote:
>
> > (a) within 200,000 transactions of wrap, every transaction start
> > delivers a WARNING message;
> >
> > (b) within 100,000 transactions, forced shutdown as above.
>
> This seems reasonable, although perhaps the former could be something
> configurable. I'm not sure there's a good reason to allow the latter to
> change unless there'd ever be a case where 100,000 transactions wasn't
> enough to vacuum or something like that.

I don't think there is much point in making it configurable. If they knew
to do that they would most likely know to vacuum as well.

However, 100K out of 1G seems too small. Just to get wrap around there
must be a pretty high transaction rate, so 100K may not give much warning.
1M or 10M seem to be better.

---------------------------(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

"Joshua D. Drake" <jd@commandprompt.com> writes:

> Christopher Kings-Lynne wrote:
>
> > I wonder if I should point out that we just had 3 people suffering XID
> > wraparound failure in 2 days in the IRC channel...
>
> I have had half a dozen new customers in the last six months that have
> had the same problem. Nothing like the phone call:

How are so many people doing so many transactions so soon after installing?

To hit wraparound you have to do a billion transactions? ("With a `B'") That
takes real work. If you did 1,000 txn/minute for every minute of every day it
would still take a couple years to get there.

And most databases get a mix of updates and selects. I would expect it would
be pretty hard to go that long with any significant level of update activity
and no vacuums and not notice the performance problems from the dead tuples.

What am I missing. Is there a significant percentage of the user base that's
doing nothing but loading huge static databases and then performing massive
loads (like thousands of queries per second) of purely read-only queries
against them?

--
greg


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