Re: Idea for nested transactions / savepoints

> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > >> The complaints about WAL size amount to "we don't have the disk space
> > >> to keep track of this, for long-running transactions". If it doesn't
> > >> fit on disk, how likely is it that it will fit in memory?
> >
> > > Sure, we can put on the disk if that is better.
> >
> > I think you missed my point. Unless something can be done to make the
> > log info a lot smaller than it is now, keeping it all around until
> > transaction end is just not pleasant. Waving your hands and saying
> > that we'll keep it in a different place doesn't affect the fundamental
> > problem: if the transaction runs a long time, the log is too darn big.
>
> When you said long running, I thought you were concerned about long
> running in duration, not large transaction. Long duration in one-WAL
> setup would cause all transaction logs to be kept. Large transactions
> are another issue.
>
> One solution may be to store just the relid if many tuples are modified
> in the same table. If you stored the command counter for start/end of
> the nested transaction, it would be possible to sequential scan the
> table and undo all the affected tuples. Does that help? Again, I am
> just throwing out ideas here, hoping something will catch.

Actually, we need to keep around nested transaction UNDO information
only until the nested transaction exits to the main transaction:

BEGIN WORK;
BEGIN WORK;
COMMIT;
-- we can throw away the UNDO here
BEGIN WORK;
BEGIN WORK;
...
COMMIT
COMMIT;
-- we can throw away the UNDO here
COMMIT;

We are using the outside transaction for our ACID capabilities, and just
using UNDO for nested transaction capability.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026