TOAST & vacuum

JanWieck@t-online.de (Jan Wieck) writes:

TOAST is now vacuum-safe. When needed, the toaster creates a
second heap tuple, containing only plain or compressed
values. This one is then returned by the heap access methods
to the caller, so indices will never contain external
references.

That should be sufficient for insertions into existing indexes,
but what about CREATE INDEX on a column that already contains
toasted values? That works with fetched tuples, not ones formed
during insert/update.

I think a cleaner and more reliable short-term hack would be to twiddle
index_formtuple() to detoast any externally-stored attributes. AFAIK,
in current sources all paths for creating an index tuple go through that
routine, and it has a tupdesc handy so it knows which attributes are of
varlena type.

This way you wouldn't need to hack up the tuptoaster itself.

Also, this would work for functional indexes whereas the way you are
doing it will not (a function could return a toasted Datum extracted
from some other table, no?).

regards, tom lane

Tom Lane wrote:

JanWieck@t-online.de (Jan Wieck) writes:

TOAST is now vacuum-safe. When needed, the toaster creates a
second heap tuple, containing only plain or compressed
values. This one is then returned by the heap access methods
to the caller, so indices will never contain external
references.

That should be sufficient for insertions into existing indexes,
but what about CREATE INDEX on a column that already contains
toasted values? That works with fetched tuples, not ones formed
during insert/update.

I think a cleaner and more reliable short-term hack would be to twiddle
index_formtuple() to detoast any externally-stored attributes. AFAIK,
in current sources all paths for creating an index tuple go through that
routine, and it has a tupdesc handy so it knows which attributes are of
varlena type.

This way you wouldn't need to hack up the tuptoaster itself.

You're right. Will do it that way.

Also, this would work for functional indexes whereas the way you are
doing it will not (a function could return a toasted Datum extracted
from some other table, no?).

Don't know of a function that does it that way right now. But
that doesn't mean no such exists - you're right again. 2
donut's for U.

Jan

--

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

FYI,

TOAST is now vacuum-safe. When needed, the toaster creates a
second heap tuple, containing only plain or compressed
values. This one is then returned by the heap access methods
to the caller, so indices will never contain external
references.

The changes are covered by #ifdef TOAST_INDICES, so can
easily be disabled at the time we have file versioning and
can recreate indices during vacuum.

Seems this has some performance advantages. Un-toasting to traverse a
btree index would be pretty slow. This way, it is all in the index.

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

TOAST is now vacuum-safe. When needed, the toaster creates a
second heap tuple, containing only plain or compressed
values. This one is then returned by the heap access methods
to the caller, so indices will never contain external
references.

The changes are covered by #ifdef TOAST_INDICES, so can
easily be disabled at the time we have file versioning and
can recreate indices during vacuum.

Seems this has some performance advantages. Un-toasting to traverse a
btree index would be pretty slow. This way, it is all in the index.

Yes, but it limits the key size. I was suggesting to Jan, that he reconsider
the "toast choice made for the heap tuple" for each index key, and store the
key
compressed if it fits into 1/4 page.

I think the current setup (index toasting disabled) must be a lot better
performance
wise than if you enable the current "index toasting" state of things.

Andreas