Wal -long transaction
I would like to know how Postgresql works when all the files
(checkpoint_segment *2 + 1)
are full ,
does Postgresql rollback the transaction when all the wal segments are used,
or does the server stop with an error message ?
(I have tried to make the test but without success for finding a long
transaction)
thanks
regards
On Mon, Mar 13, 2006 at 03:59:33PM +0100, Agnes Bocchino wrote:
I would like to know how Postgresql works when all the files
(checkpoint_segment *2 + 1)
are full ,
does Postgresql rollback the transaction when all the wal segments are used,
or does the server stop with an error message ?
(I have tried to make the test but without success for finding a long
transaction)
AIUI it just keeps creating more segments. i.e. checkpoint_segment is
not a hard limit. It's just the number it keeps around and recycles
rather than continually creating and deleteing files.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
Martijn van Oosterhout <kleptog@svana.org> writes:
On Mon, Mar 13, 2006 at 03:59:33PM +0100, Agnes Bocchino wrote:
(I have tried to make the test but without success for finding a long
transaction)
AIUI it just keeps creating more segments. i.e. checkpoint_segment is
not a hard limit. It's just the number it keeps around and recycles
rather than continually creating and deleteing files.
More to the point, having a long transaction has nothing to do with this
(we are not Oracle!). The only thing that determines the amount of WAL
space needed is the time between checkpoints. You can have a
transaction that stays open for many checkpoints without causing WAL to
bloat.
Of course, there's no free lunch --- the price we pay for escaping
rollback-segment-overflow is table bloat if you don't vacuum often
enough.
regards, tom lane
Martijn van Oosterhout wrote:
On Mon, Mar 13, 2006 at 03:59:33PM +0100, Agnes Bocchino wrote:
I would like to know how Postgresql works when all the files
(checkpoint_segment *2 + 1)
are full ,
does Postgresql rollback the transaction when all the wal segments are used,
or does the server stop with an error message ?
(I have tried to make the test but without success for finding a long
transaction)AIUI it just keeps creating more segments. i.e. checkpoint_segment is
not a hard limit. It's just the number it keeps around and recycles
rather than continually creating and deleteing files.Have a nice day,
Thanks for the answer, that's clarify how it's work
I ask this question about 'long transaction' (between two commits)
because it was a problem on old release of IDS Informix.
Please, what is the meaning of 'AIUI' ......
Thanks
Agn�s
On Mon, Mar 13, 2006 at 04:51:03PM +0100, Agnes Bocchino wrote:
Please, what is the meaning of 'AIUI' ......
As I Understand It
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
Please, what is the meaning of 'AIUI' ......
This site was a big help for me as acronyms are popular on this list:
Regards,
Richard
Tom Lane <tgl@sss.pgh.pa.us> writes:
Of course, there's no free lunch --- the price we pay for escaping
rollback-segment-overflow is table bloat if you don't vacuum often
enough.
Well it's worse than that. If you have long-running transactions that would
cause rollback-segment-overflow in Oracle then the equivalent price in
Postgres would be table bloat *regardless* of how frequently you vacuum.
I suppose you can argue it's not "bloat" as long as you reach a steady state.
But the extra space in the tables is a performance cost on every sequential
scan and on every cache miss it causes whatever you call it.
I'm not saying I like rollback segments better, just yes, TANSTAAFL.
--
greg
Greg Stark wrote:
Well it's worse than that. If you have long-running transactions that would
cause rollback-segment-overflow in Oracle then the equivalent price in
Postgres would be table bloat *regardless* of how frequently you vacuum.
Isn't that a bit pessimistic? In tables which mostly grow (as opposed
to deletes and updates) and where most inserts succeed (instead of
rolling back), I would have expected postgresql not to bloat
tables no matter how long my transactions last.
And it's been a while; but I thought transactions like that could
overflow rollback segments in that other database.
And it's been a while; but I thought transactions like that could
overflow rollback segments in that other database.
ORA-01555: snapshot too old: rollback segment number string with name
"string" too small
Cause: Rollback records needed by a reader for consistent read are
overwritten by other writers.
Action: If in Automatic Undo Management mode, increase the setting of
UNDO_RETENTION. Otherwise, use larger rollback segments.
In 10g you can do
ALTER TABLESPACE UNDO_TS2 RETENTION GUARANTEE;
which will automgically grow the undo tablespace until you run out of
disk space or the transaction ends.