More info

Tim Perdue <tperdue@valinux.com> writes:

Any idea why 6.5.3 would have created tens of thousands of files like
these in the /data/base/db_geocrawler/ directory?

Funny you should mention that, because I was just in process of testing
a fix when your mail came in. The low-level routine that accesses a
particular segment of a multi-segment relation develops a serious case
of Sorcerer's Apprentice syndrome if higher levels hand it a silly block
number. If you tell it to access, say, block# 2 billion, it will
merrily start creating empty segment files till it gets to the segment
number that corresponds to that block number.

The routine does need to be able to create *one* new segment, in case it
is asked to access the block just past the current EOF (when EOF is at a
segment boundary) ... but not more than one. As of current sources, it
knows not to do more.

This bug has been known for a while. It doesn't directly answer your
problem though, since the real issue is "what generated the silly block
number, and why"?

I can't quite resist the temptation to suggest that you should be
running 7.0.2 ...

regards, tom lane

Tom Lane wrote:

I can't quite resist the temptation to suggest that you should be
running 7.0.2 ...

I'll tell you what I *really* want... How about 7.1.2 (I'm afraid of
7.1.0)

Tim

--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Tim Perdue <tperdue@valinux.com> writes:

Tom Lane wrote:

I can't quite resist the temptation to suggest that you should be
running 7.0.2 ...

I'll tell you what I *really* want... How about 7.1.2 (I'm afraid of
7.1.0)

And when that's out, you'll be waiting for 7.2.2?

I can understand reluctance to install whatever.0 as a production
server on its first day of release. But we have enough field experience
now with 7.0.* to say confidently that it is more stable than 6.5.*,
and we know for a fact that we have fixed hundreds of bugs in it
compared to 6.5.*. Frankly, if I had to bet today, I'd bet on 7.1.*
being less stable than 7.0.*, at least till we shake out all the
implications of TOAST, WAL, etc.

If you're being bitten by 6.5.* bugs then an update seems in order.

regards, tom lane

I can understand reluctance to install whatever.0 as a production
server on its first day of release. But we have enough field experience
now with 7.0.* to say confidently that it is more stable than 6.5.*,
and we know for a fact that we have fixed hundreds of bugs in it
compared to 6.5.*. Frankly, if I had to bet today, I'd bet on 7.1.*
being less stable than 7.0.*, at least till we shake out all the
implications of TOAST, WAL, etc.

Is WAL planned for 7.1? What is the story with WAL? I'm a bit concerned
that the current storage manager is going to be thrown in the bit bucket
without any thought for its benefits. There's some stuff I want to do
with it like resurrecting time travel, some database replication stuff
which can make use of the non-destructive storage method etc. There's a
whole lot of interesting stuff that can be done with the current storage
manager.

WAL is 7.1. It doesn't affect the storage manager very much. A new
storage manager is scheduled for 7.2.

I can understand reluctance to install whatever.0 as a production
server on its first day of release. But we have enough field experience
now with 7.0.* to say confidently that it is more stable than 6.5.*,
and we know for a fact that we have fixed hundreds of bugs in it
compared to 6.5.*. Frankly, if I had to bet today, I'd bet on 7.1.*
being less stable than 7.0.*, at least till we shake out all the
implications of TOAST, WAL, etc.

Is WAL planned for 7.1? What is the story with WAL? I'm a bit concerned
that the current storage manager is going to be thrown in the bit bucket
without any thought for its benefits. There's some stuff I want to do
with it like resurrecting time travel, some database replication stuff
which can make use of the non-destructive storage method etc. There's a
whole lot of interesting stuff that can be done with the current storage
manager.

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

Is WAL planned for 7.1? What is the story with WAL?

Yes.

I'm a bit concerned that the current storage manager is going to be
thrown in the bit bucket without any thought for its benefits. There's
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.

some database replication stuff which can make use of the non-destructive

It was mentioned here that triggers could be used for async replication,
as well as WAL.

storage method etc. There's a whole lot of interesting stuff that can be
done with the current storage manager.

Vadim

"Mikheev, Vadim" wrote:

Yes.

I'm a bit concerned that the current storage manager is going to be
thrown in the bit bucket without any thought for its benefits. There's
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. Then you've got to figure out strategies
for efficiently deleting old data if you want to. The old postgres was
the
Right Thing, if you want access to time travel.

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.

Bruce Momjian wrote:

WAL is 7.1. It doesn't affect the storage manager very much. A new
storage manager is scheduled for 7.2.

Is it going to hurt my ability to resurrect time travel using the
original
methodology?

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

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.

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

"Mikheev, Vadim" 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
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

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?

At 11:27 11/07/00 +1000, Chris Bitmead wrote:

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.

The WAL *should* be the most efficient technique for replication (this said
without actually having seen it ;-}).

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Has sufficient research been done to warrant destruction of what is
currently there?

According to the postgres research papers, the no-overwrite storage
manager has the following attributes...

* It's always faster than WAL in the presence of stable main memory.
(Whether the stable caches in modern disk drives is an approximation I
don't know).

* It's more scalable and has less logging contention. This allows
greater scalablility in the presence of multiple processors.

* Instantaneous crash recovery.

* Time travel is available at no cost.

* Easier to code and prove correctness. (I used to work for a database
company that implemented WAL, and it took them a large number of years
before they supposedly corrected every bug and crash condition on
recovery).

* Ability to keep archival records on an archival medium.

Is there any research on the level of what was done previously to
warrant abandoning these benefits? Obviously WAL has its own benefits, I
just don't want to see the current benefits lost.

Chris Bitmead wrote:

Has sufficient research been done to warrant destruction of what is
currently there?

What's currently there doesn't have TT any more. So there is
nothing we would destroy with an overwriting SMGR.

According to the postgres research papers, the no-overwrite storage
manager has the following attributes...

I started using (and hacking) Postgres in version 4.2, which
was the last official release from Stonebrakers team at UCB
(and the last one with the PostQUEL query language).

The no-overwriting SMGR concept was one of the things, the
entire project should aprove. The idea was to combine
rollback and logging information with the data itself, by
only storing new values and remembering when something
appeared or disappeared. Stable memory just means "if I know
my write made it to some point, I can read it back later even
in the case of a crash".

This has never been implemented to a degree that is capable
to catch hardware failures like unexpected loss of power. So
the project finally told "it might be possible". Many other
questions have been answered by the project, but exactly this
one is still open.

* It's always faster than WAL in the presence of stable main memory.
(Whether the stable caches in modern disk drives is an approximation I
don't know).

For writing, yes. But for high updated tables, the scans will
soon slow down due to the junk contention.

* It's more scalable and has less logging contention. This allows
greater scalablility in the presence of multiple processors.

* Instantaneous crash recovery.

Because this never worked reliable, Vadim is working on WAL.

* Time travel is available at no cost.

* Easier to code and prove correctness. (I used to work for a database
company that implemented WAL, and it took them a large number of years
before they supposedly corrected every bug and crash condition on
recovery).

* Ability to keep archival records on an archival medium.

Has this ever been implemented?

Is there any research on the level of what was done previously to
warrant abandoning these benefits? Obviously WAL has its own benefits, I
just don't want to see the current benefits lost.

I see your points. Maybe we can leave the no-overwriting SMGR
in the code, and just make the new one the default.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

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

Jan Wieck wrote:

What's currently there doesn't have TT any more. So there is
nothing we would destroy with an overwriting SMGR.

I know, but I wanted to resurrect it at some stage, and I think a lot of
important bits are still there.

* It's always faster than WAL in the presence of stable main memory.
(Whether the stable caches in modern disk drives is an approximation I
don't know).

For writing, yes. But for high updated tables, the scans will
soon slow down due to the junk contention.

I imagine highly updated applications won't be interested in time
travel. If they are then the alternative of a user-maintained time-stamp
and triggers will still leave you with "junk".

* Instantaneous crash recovery.

Because this never worked reliable, Vadim is working on WAL.

Postgres recovery is not reliable?

At 11:27 11/07/00 +1000, Chris Bitmead wrote:

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.

The WAL *should* be the most efficient technique for
replication (this said
without actually having seen it ;-}).

That depends on how much you need replicated. If you replicate all or most
tables WAL will be very good, if you only need a few tables it wont.

Andreas