On Wed, Sep 07, 2005 at 01:05:52PM -0400, Alvaro Herrera wrote:
> Anyway you are just moving the storage somewhere else -- instead of
> having 4 fields in the tuple itself, you have one field which points
> the same 4 fields elsewhere. I don't see how is that a win; it's
> actually worse because you have to do two lookups. (And actually you
> have just enlarged the storage requirements because you need to store
> the "vis_id" twice.)

It would only be of use if the table had few transactions in it; in
other words, if it was mostly read-only. For a true read-only table
there are other options people have suggested that are probably better.

BTW, this becomes even more attractive if vis_id is int2; in that case
you can keep the entire mapping in memory in ~1MB.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Wed, Sep 07, 2005 at 01:20:27PM -0400, Tom Lane wrote:

> Anyway the fundamental insight has been completely lost here. The
> original point was that cmin and cmax are only interesting within the
> originating transaction, and not to anyone else, and thus perhaps don't
> need to be kept in permanent storage; while xmin/xmax are different
> animals because they *are* of interest to other transactions.

I'm curious to know how can you store the cmin/cmax pair completely out
of the tuple. It's easy to see how to store a single identifier in each
tuple that would be an index to a structure in local memory. However,
to eliminate both you'd have to keep a list of all tuples you have
created or obsoleted, with the cmin and cmax of each. This seems like
an awful amount of memory.

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu. Five minutes later I realize that it's also talking
about food" (Donald Knuth)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I'm curious to know how can you store the cmin/cmax pair completely out
> of the tuple. It's easy to see how to store a single identifier in each
> tuple that would be an index to a structure in local memory. However,
> to eliminate both you'd have to keep a list of all tuples you have
> created or obsoleted, with the cmin and cmax of each. This seems like
> an awful amount of memory.

Yeah. I think a reasonable compromise scheme is to try to get down to
three fields per tuple:

xmin same as now
xmax same as now
cid/xvac

xvac can share storage with the command ID info as long as VACUUM FULL
never tries to move a tuple whose originating or deleting transaction
is still running ... which is pretty much the same restriction we had
before.

For the command IDs, I am imagining:

if created in current transaction: use cid to store cmin

if deleted in current transaction: use cid to store cmax

if both created and deleted in current transaction: cid is an index
into an in-memory data structure that contains cmin and cmax.

"current transaction" would have to have the loose definition that
includes any subxact of the current top xact, but still, I think that
the case where you need both fields is relatively uncommon.

The in-memory data structure would only need to contain an entry for
each distinct combination of cmin and cmax used in the current xact,
so I think we could assume that it would never get unreasonably large.
The entries would be created "on demand" much like we do for
multixact ids (I guess you'd want a hash table to map requested
cmin/cmax to an existing entry ID quickly).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I think this could be done with our "SLRU" mechanism, just like pg_clog,
> pg_subtrans and pg_multixact do. Whether it's really a good idea or
> not, it's another story. The problem is that you would be creating new
> ones all the time, it would become a huge source of contention, and it
> would use a lot of memory.

.... and you couldn't expire the data in a reasonable period of time.
pg_subtrans and pg_multixact have only very short active ranges.
pg_clog is longer-lived, but at only 2 bits per transaction, we can
stand it. 16 bytes per tuple is a whole lot more data.

Anyway the fundamental insight has been completely lost here. The
original point was that cmin and cmax are only interesting within the
originating transaction, and not to anyone else, and thus perhaps don't
need to be kept in permanent storage; while xmin/xmax are different
animals because they *are* of interest to other transactions.
The storage scheme Jim proposes takes no advantage of that whatever.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

This is now in the TODO list:

* Merge xmin/xmax/cmin/cmax back into three header fields

Before subtransactions, there used to be only three fields needed to
store these four values. This was possible because only the current
transaction looks at the cmin/cmax values. If the current transaction
created and expired the row the fields stored where xmin (same as
xmax), cmin, cmax, and if the transaction was expiring a row from a
another transaction, the fields stored were xmin (cmin was not
needed), xmax, and cmax. Such a system worked because a transaction
could only see rows from another completed transaction. However,
subtransactions can see rows from outer transactions, and once the
subtransaction completes, the outer transaction continues, requiring
the storage of all four fields. With subtransactions, an outer
transaction can create a row, a subtransaction expire it, and when the
subtransaction completes, the outer transaction still has to have
proper visibility of the row's cmin, for example, for cursors.

One possible solution is to create a phantom cid which represents a
cmin/cmax pair and is stored in local memory. Another idea is to
store both cmin and cmax only in local memory.


---------------------------------------------------------------------------

Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > I'm curious to know how can you store the cmin/cmax pair completely out
> > of the tuple. It's easy to see how to store a single identifier in each
> > tuple that would be an index to a structure in local memory. However,
> > to eliminate both you'd have to keep a list of all tuples you have
> > created or obsoleted, with the cmin and cmax of each. This seems like
> > an awful amount of memory.
>
> Yeah. I think a reasonable compromise scheme is to try to get down to
> three fields per tuple:
>
> xmin same as now
> xmax same as now
> cid/xvac
>
> xvac can share storage with the command ID info as long as VACUUM FULL
> never tries to move a tuple whose originating or deleting transaction
> is still running ... which is pretty much the same restriction we had
> before.
>
> For the command IDs, I am imagining:
>
> if created in current transaction: use cid to store cmin
>
> if deleted in current transaction: use cid to store cmax
>
> if both created and deleted in current transaction: cid is an index
> into an in-memory data structure that contains cmin and cmax.
>
> "current transaction" would have to have the loose definition that
> includes any subxact of the current top xact, but still, I think that
> the case where you need both fields is relatively uncommon.
>
> The in-memory data structure would only need to contain an entry for
> each distinct combination of cmin and cmax used in the current xact,
> so I think we could assume that it would never get unreasonably large.
> The entries would be created "on demand" much like we do for
> multixact ids (I guess you'd want a hash table to map requested
> cmin/cmax to an existing entry ID quickly).
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly