Thread

RE: postgres 7.2 features.

Vadim Mikheev <vmikheev@sectorbase.com> — 2000-07-11T02:33:21Z

> > > some stuff I want to do with it like resurrecting time travel,
> > 
> > Why don't use triggers for time-travel?
> > Disadvantages of transaction-commit-time based time travel 
> > was pointed out a days ago.
> 
> Triggers for time travel is MUCH less efficient. There is no copying
> involved either in memory or on disk with the original postgres time
> travel, nor is there any logic to be executed.

With the original TT:

- you are not able to use indices to fetch tuples on time base;
- you are not able to control tuples life time;
- you have to store commit time somewhere;
- you have to store additional 8 bytes for each tuple;
- 1 sec could be tooo long time interval for some uses of TT.

And, btw, what could be *really* very useful it's TT + referential integrity
feature. How could it be implemented without triggers?

Imho, triggers can give you much more flexible and useful TT...

Also note that TT was removed from Illustra and authors wrote that
built-in TT could be implemented without non-overwriting smgr.

> > It was mentioned here that triggers could be used for async 
> > replication, as well as WAL.
> 
> Same story. Major inefficency. Replication is tough enough without
> mucking around with triggers. Once the trigger executes you've got
> to go and store the data in the database again anyway. Then figure
> out when to delete it.

What about reading WAL to get and propagate changes? I don't think that
reading tables will be more efficient and, btw, 
how to know what to read (C) -:) ?

Vadim

Re: postgres 7.2 features.

Chris <chrisb@nimrod.itg.telstra.com.au> — 2000-07-11T03:46:42Z

The bottom line is that the original postgres time-travel implementation
was totally cost-free. Actually it may have even speeded things
up since vacuum would have less work to do. Can you convince me that
triggers can compare anywhere near for performance? I can't see how.
All I'm asking is don't damage anything that is in postgres now that
is relevant to time-travel in your quest for WAL....

> With the original TT:
> 
> - you are not able to use indices to fetch tuples on time base;

Sounds not very hard to fix..

> - you are not able to control tuples life time;

From the docs... "Applications that do not want to save historical data
can sepicify a cutoff point for a relation. Cutoff points are defined by
the discard command" The command "discard EMP before "1 week"
deletes data in the EMP relation that is more than 1 week old".

> - you have to store commit time somewhere;

Ok, so?

> - you have to store additional 8 bytes for each tuple;

A small price for time travel.

> - 1 sec could be tooo long time interval for some uses of TT.

So someone in the future can implement finer grains. If time travel
disappears this option is not open.

> And, btw, what could be *really* very useful it's TT + referential integrity
> feature. How could it be implemented without triggers?

In what way does TT not have referential integrity? As long as the
system
assures that every transaction writes the same timestamp to all tuples
then
referential integrity continues to exist.

> Imho, triggers can give you much more flexible and useful TT...
> 
> Also note that TT was removed from Illustra and authors wrote that
> built-in TT could be implemented without non-overwriting smgr.

Of course it can be, but can it be done anywhere near as efficiently?

> > > It was mentioned here that triggers could be used for async
> > > replication, as well as WAL.
> >
> > Same story. Major inefficency. Replication is tough enough without
> > mucking around with triggers. Once the trigger executes you've got
> > to go and store the data in the database again anyway. Then figure
> > out when to delete it.
> 
> What about reading WAL to get and propagate changes? I don't think that
> reading tables will be more efficient and, btw,
> how to know what to read (C) -:) ?

Maybe that is a good approach, but it's not clear that it is the best.
More research is needed. With the no-overwrite storage manager there
exists a mechanism for deciding how long a tuple exists and this
can easily be tapped into for replication purposes. Vacuum could 
serve two purposes of vacuum and replicate.

Re: Storage Manager (was postgres 7.2 features.)

Chris <chrisb@nimrod.itg.telstra.com.au> — 2000-07-11T04:01:50Z

Also, does WAL offer instantaneous crash recovery like no-overwrite?


"Mikheev, Vadim" wrote:
> 
> > > > some stuff I want to do with it like resurrecting time travel,
> > >
> > > Why don't use triggers for time-travel?
> > > Disadvantages of transaction-commit-time based time travel
> > > was pointed out a days ago.
> >
> > Triggers for time travel is MUCH less efficient. There is no copying
> > involved either in memory or on disk with the original postgres time
> > travel, nor is there any logic to be executed.
> 
> With the original TT:
> 
> - you are not able to use indices to fetch tuples on time base;
> - you are not able to control tuples life time;
> - you have to store commit time somewhere;
> - you have to store additional 8 bytes for each tuple;
> - 1 sec could be tooo long time interval for some uses of TT.
> 
> And, btw, what could be *really* very useful it's TT + referential integrity
> feature. How could it be implemented without triggers?
> 
> Imho, triggers can give you much more flexible and useful TT...
> 
> Also note that TT was removed from Illustra and authors wrote that
> built-in TT could be implemented without non-overwriting smgr.
> 
> > > It was mentioned here that triggers could be used for async
> > > replication, as well as WAL.
> >
> > Same story. Major inefficency. Replication is tough enough without
> > mucking around with triggers. Once the trigger executes you've got
> > to go and store the data in the database again anyway. Then figure
> > out when to delete it.
> 
> What about reading WAL to get and propagate changes? I don't think that
> reading tables will be more efficient and, btw,
> how to know what to read (C) -:) ?
> 
> Vadim

Re: postgres 7.2 features.

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-07-11T04:19:53Z

> 
> The bottom line is that the original postgres time-travel implementation
> was totally cost-free. Actually it may have even speeded things
> up since vacuum would have less work to do. Can you convince me that
> triggers can compare anywhere near for performance? I can't see how.
> All I'm asking is don't damage anything that is in postgres now that
> is relevant to time-travel in your quest for WAL....

Basically, time travel was getting in the way of more requested features
that had to be added.  Keeping it around has a cost, and no one felt the
cost was worth the benefit. You may disagree, but at the time, that was
the consensus, and I assume it still is.

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

Re: postgres 7.2 features.

Chris <chrisb@nimrod.itg.telstra.com.au> — 2000-07-11T05:38:50Z

Bruce Momjian wrote:
> 
> >
> > The bottom line is that the original postgres time-travel implementation
> > was totally cost-free. Actually it may have even speeded things
> > up since vacuum would have less work to do. Can you convince me that
> > triggers can compare anywhere near for performance? I can't see how.
> > All I'm asking is don't damage anything that is in postgres now that
> > is relevant to time-travel in your quest for WAL....
> 
> Basically, time travel was getting in the way of more requested features

Do you mean way back when it was removed? How was it getting in the way?

> that had to be added.  Keeping it around has a cost, and no one felt the
> cost was worth the benefit. You may disagree, but at the time, that was
> the consensus, and I assume it still is.

Re: postgres 7.2 features.

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-07-11T13:02:37Z

> Bruce Momjian wrote:
> > 
> > >
> > > The bottom line is that the original postgres time-travel implementation
> > > was totally cost-free. Actually it may have even speeded things
> > > up since vacuum would have less work to do. Can you convince me that
> > > triggers can compare anywhere near for performance? I can't see how.
> > > All I'm asking is don't damage anything that is in postgres now that
> > > is relevant to time-travel in your quest for WAL....
> > 
> > Basically, time travel was getting in the way of more requested features
> 
> Do you mean way back when it was removed? How was it getting in the way?

Yes.  Every tuple had this time-thing that had to be tested.  Vadim
wanted to revove it to clear up the coding, and we all agreed.

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

Re: postgres 7.2 features.

Chris Bitmead <chris@bitmead.com> — 2000-07-11T13:23:18Z

Bruce Momjian wrote:

> > Do you mean way back when it was removed? How was it getting in the way?
> 
> Yes.  Every tuple had this time-thing that had to be tested.  Vadim
> wanted to revove it to clear up the coding, and we all agreed.

And did that save a lot of code?

Re: postgres 7.2 features.

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-07-11T14:47:11Z

> Bruce Momjian wrote:
> 
> > > Do you mean way back when it was removed? How was it getting in the way?
> > 
> > Yes.  Every tuple had this time-thing that had to be tested.  Vadim
> > wanted to revove it to clear up the coding, and we all agreed.
> 
> And did that save a lot of code?
> 

It simplified the code.

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