> > My point is that we'll need in dynamic cleanup anyway and UNDO is
> > what should be implemented for dynamic cleanup of aborted changes.
> 
> I do not yet understand why you want to handle aborts different than
> outdated tuples.

Maybe because of aborted tuples have shorter Time-To-Live.
And probability to find pages for them in buffer pool is higher.

> The ratio in a well tuned system should well favor outdated tuples.
> If someone ever adds "dirty read" it is also not the case that it
> is guaranteed, that nobody accesses the tuple you currently want
> to undo. So I really miss to see the big difference.

It will not be guaranteed anyway as soon as we start removing
tuples without exclusive access to relation.

And, I cannot say that I would implement UNDO because of
1. (cleanup) OR 2. (savepoints) OR 4. (pg_log management)
but because of ALL of 1., 2., 4.

Vadim

Mon, 21 May 2001 11:01:45 -0700
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: Plans for solving the VACUUM problem 
[ Charset ISO-8859-1 unsupported, converting... ]
> > > My point is that we'll need in dynamic cleanup anyway and UNDO is
> > > what should be implemented for dynamic cleanup of aborted changes.
> > 
> > I do not yet understand why you want to handle aborts different than
> > outdated tuples.
> 
> Maybe because of aborted tuples have shorter Time-To-Live.
> And probability to find pages for them in buffer pool is higher.

This brings up an idea I had about auto-vacuum.  I wonder if autovacuum
could do most of its work by looking at the buffer cache pages and
commit xids.  Seems it would be quite easy record freespace in pages
already in the buffer and collect that information for other backends to
use.  It could also move tuples between cache pages with little
overhead.

There wouldn't be an I/O overhead, and frequently used tables are
already in the cache.

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

Tue, 22 May 2001 13:32:40 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Plans for solving the VACUUM problem
> > The ratio in a well tuned system should well favor outdated tuples.
> > If someone ever adds "dirty read" it is also not the case that it
> > is guaranteed, that nobody accesses the tuple you currently want
> > to undo. So I really miss to see the big difference.
> 
> It will not be guaranteed anyway as soon as we start removing
> tuples without exclusive access to relation.
> 
> And, I cannot say that I would implement UNDO because of
> 1. (cleanup) OR 2. (savepoints) OR 4. (pg_log management)
> but because of ALL of 1., 2., 4.

OK, I understand your reasoning here, but I want to make a comment.

Looking at the previous features you added, like subqueries, MVCC, or
WAL, these were major features that greatly enhanced the system's
capabilities.

Now, looking at UNDO, I just don't see it in the same league as those
other additions.  Of course, you can work on whatever you want, but I
was hoping to see another major feature addition for 7.2.  We know we
badly need auto-vacuum, improved replication, and point-in-time recover.

I can see UNDO improving row reuse, and making subtransactions and
pg_log compression easier, but these items do not require UNDO.  

In fact, I am unsure why we would want an UNDO way of reusing rows of
aborted transactions and an autovacuum way of reusing rows from
committed transactions, expecially because aborted transactions account
for <5% of all transactions.  It would be better to put work into one
mechanism that would reuse all tuples.

If UNDO came with no limitations, it may be a good option, but the need
to carry tuples until transaction commit does add an extra burden on
programmers and administrators, and I just don't see what we are getting
for it.

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

Tue, 22 May 2001 13:47:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Plans for solving the VACUUM problem
Bruce Momjian wrote:
> 
> > > The ratio in a well tuned system should well favor outdated tuples.
> > > If someone ever adds "dirty read" it is also not the case that it
> > > is guaranteed, that nobody accesses the tuple you currently want
> > > to undo. So I really miss to see the big difference.
> >
> > It will not be guaranteed anyway as soon as we start removing
> > tuples without exclusive access to relation.
> >
> > And, I cannot say that I would implement UNDO because of
> > 1. (cleanup) OR 2. (savepoints) OR 4. (pg_log management)
> > but because of ALL of 1., 2., 4.
> 
> OK, I understand your reasoning here, but I want to make a comment.
> 
> Looking at the previous features you added, like subqueries, MVCC, or
> WAL, these were major features that greatly enhanced the system's
> capabilities.
> 
> Now, looking at UNDO, I just don't see it in the same league as those
> other additions. 

Hmm hasn't it been an agreement ? I know UNDO was planned
for 7.0 and I've never heard objections about it until
recently. People also have referred to an overwriting smgr
easily. Please tell me how to introduce an overwriting smgr
without UNDO.

regards,
Hiroshi Inoue

Wed, 23 May 2001 09:49:33 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Plans for solving the VACUUM problem
> > Looking at the previous features you added, like subqueries, MVCC, or
> > WAL, these were major features that greatly enhanced the system's
> > capabilities.
> > 
> > Now, looking at UNDO, I just don't see it in the same league as those
> > other additions. 
> 
> Hmm hasn't it been an agreement ? I know UNDO was planned
> for 7.0 and I've never heard objections about it until
> recently. People also have referred to an overwriting smgr
> easily. Please tell me how to introduce an overwriting smgr
> without UNDO.

I guess that is the question.  Are we heading for an overwriting storage
manager?  I didn't see that in Vadim's list of UNDO advantages, but
maybe that is his final goal.  If so UNDO may make sense, but then the
question is how do we keep MVCC with an overwriting storage manager?

The only way I can see doing it is to throw the old tuples into the WAL
and have backends read through that for MVCC info.

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

Tue, 22 May 2001 20:53:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Plans for solving the VACUUM problem


Bruce Momjian wrote:
> 
> > > Looking at the previous features you added, like subqueries, MVCC, or
> > > WAL, these were major features that greatly enhanced the system's
> > > capabilities.
> > >
> > > Now, looking at UNDO, I just don't see it in the same league as those
> > > other additions.
> >
> > Hmm hasn't it been an agreement ? I know UNDO was planned
> > for 7.0 and I've never heard objections about it until
> > recently. People also have referred to an overwriting smgr
> > easily. Please tell me how to introduce an overwriting smgr
> > without UNDO.
> 
> I guess that is the question.  Are we heading for an overwriting storage
> manager?

I've never heard that it was given up. So there seems to be
at least a possibility to introduce it in the future.
PostgreSQL could have lived without UNDO due to its no
overwrite smgr. I don't know if avoiding UNDO is possible
to implement partial rollback(I don't think it's easy
anyway). However it seems harmful for the future 
implementation of an overwriting smgr if we would
introduce it.

> I didn't see that in Vadim's list of UNDO advantages, but
> maybe that is his final goal.
> If so UNDO may make sense, but then the
> question is how do we keep MVCC with an overwriting storage manager?
> 

It doesn't seem easy. ISTM it's one of the main reason we
couldn't introduce an overwriting smgr in 7.2.

regards,
Hiroshi Inoue

Wed, 23 May 2001 12:05:23 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Plans for solving the VACUUM problem
Hiroshi Inoue wrote:
> 
> People also have referred to an overwriting smgr easily.

I am all for an overwriting smgr, but as a feature that can be selected 
on a table-by table basis (or at least in compile time), not as an
overall change

> Please tell me how to introduce an overwriting smgr
> without UNDO.

I would much more like a dead-space-reusing smgr on top of MVCC which
does 
not touch live transactions.

------------------
Hannu

Wed, 23 May 2001 12:11:49 +0200
Hannu Krosing <hannu@tm.ee>
Re: Plans for solving the VACUUM problem
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
>> I guess that is the question.  Are we heading for an overwriting storage
>> manager?

> I've never heard that it was given up. So there seems to be
> at least a possibility to introduce it in the future.

Unless we want to abandon MVCC (which I don't), I think an overwriting
smgr is impractical.  We need a more complex space-reuse scheme than
that.

			regards, tom lane

Wed, 23 May 2001 15:31:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Plans for solving the VACUUM problem 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> >> I guess that is the question.  Are we heading for an overwriting storage
> >> manager?
> 
> > I've never heard that it was given up. So there seems to be
> > at least a possibility to introduce it in the future.
> 
> Unless we want to abandon MVCC (which I don't), I think an overwriting
> smgr is impractical. 

Impractical ? Oracle does it.

> We need a more complex space-reuse scheme than
> that.
> 

IMHO we have to decide which to go now.
As I already mentioned, changing current handling
of transactionId/CommandId to avoid UNDO is not
only useless but also harmful for an overwriting
smgr.

regards,
Hiroshi Inoue

Thu, 24 May 2001 08:15:06 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Plans for solving the VACUUM problem
At 08:15 AM 5/24/01 +0900, Hiroshi Inoue wrote:

>> Unless we want to abandon MVCC (which I don't), I think an overwriting
>> smgr is impractical. 
>
>Impractical ? Oracle does it.

It's not easy, though ... the current PG scheme has the advantage of being
relatively simple and probably more efficient than scanning logs like
Oracle has to do (assuming your datafiles aren't thoroughly clogged with
old dead tuples).

Has anyone looked at InterBase for hints for space-reusing strategies? 

As I understand it, they have a tuple-versioning scheme similar to PG's.

If nothing else, something might be learned as to the efficiency and
effectiveness of one particular approach to solving the problem.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Wed, 23 May 2001 16:24:48 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Plans for solving the VACUUM problem
Don Baccus wrote:
> 
> At 08:15 AM 5/24/01 +0900, Hiroshi Inoue wrote:
> 
> >> Unless we want to abandon MVCC (which I don't), I think an overwriting
> >> smgr is impractical.
> >
> >Impractical ? Oracle does it.
> 
> It's not easy, though ... the current PG scheme has the advantage of being
> relatively simple and probably more efficient than scanning logs like
> Oracle has to do (assuming your datafiles aren't thoroughly clogged with
> old dead tuples).
> 

I think so too. I've never said that an overwriting smgr
is easy and I don't love it particularily.

What I'm objecting is to avoid UNDO without giving up
an overwriting smgr. We shouldn't be noncommittal now. 

regards,
Hiroshi Inoue

Thu, 24 May 2001 11:22:00 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Plans for solving the VACUUM problem
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Tom Lane wrote:
>> Unless we want to abandon MVCC (which I don't), I think an overwriting
>> smgr is impractical. 

> Impractical ? Oracle does it.

Oracle has MVCC?

			regards, tom lane

Wed, 23 May 2001 23:02:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Plans for solving the VACUUM problem 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Tom Lane wrote:
> >> Unless we want to abandon MVCC (which I don't), I think an overwriting
> >> smgr is impractical.
> 
> > Impractical ? Oracle does it.
> 
> Oracle has MVCC?
> 

Yes.

regards,
Hiroshi Inoue

Thu, 24 May 2001 12:24:20 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Plans for solving the VACUUM problem
At 11:02 PM 5/23/01 -0400, Tom Lane wrote:
>Hiroshi Inoue <Inoue@tpf.co.jp> writes:
>> Tom Lane wrote:
>>> Unless we want to abandon MVCC (which I don't), I think an overwriting
>>> smgr is impractical. 
>
>> Impractical ? Oracle does it.
>
>Oracle has MVCC?

With restrictions, yes.  You didn't know that?  Vadim did ...



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Wed, 23 May 2001 21:42:43 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Plans for solving the VACUUM problem 
Don Baccus wrote:
> 
> At 08:15 AM 5/24/01 +0900, Hiroshi Inoue wrote:
> 
> >> Unless we want to abandon MVCC (which I don't), I think an overwriting
> >> smgr is impractical.
> >
> >Impractical ? Oracle does it.
> 
> It's not easy, though ... the current PG scheme has the advantage of being
> relatively simple and probably more efficient than scanning logs like
> Oracle has to do (assuming your datafiles aren't thoroughly clogged with
> old dead tuples).
> 
> Has anyone looked at InterBase for hints for space-reusing strategies?
> 
> As I understand it, they have a tuple-versioning scheme similar to PG's.
> 
> If nothing else, something might be learned as to the efficiency and
> effectiveness of one particular approach to solving the problem.

It may also be beneficial to study SapDB (which is IIRC a branch-off of 
Adabas) although they claim at http://www.sapdb.org/ in features
section:

NOT supported features:

              Collations

              Result sets that are created within a stored procedure and
fetched outside. This feature is planned to be
              offered in one of the coming releases.
              Meanwhile, use temporary tables.
              see Reference Manual: SAP DB 7.2 and 7.3 -> Data
definition -> CREATE TABLE statement: Owner of a
              table

              Multi version concurrency for OLTP
              It is available with the object extension of SAPDB only.

              Hot stand by
              This feature is planned to be offered in one of the coming
releases. 

So MVCC seems to be a bolt-on feature there.

---------------------
Hannu

Fri, 25 May 2001 02:05:19 +0500
Hannu Krosing <hannu@tm.ee>
Re: Plans for solving the VACUUM problem