After reading the summary of Peter's comments on the patches in the
list, I thought I would provide a little more explanation, as he
suggests.
At present, functions that take text arguments retrieve them via the
PG_GETARG macros. This reconstructs the whole value into memory
(potentially time-consuming for a large value). This may be unnecessary
for some applications, where only a specified part of the value is
needed e.g. the header data from an image.
Because a TOASTed value is stored in equal-size chunks, it is
straightforward (for an uncompressed value) to determine which chunk(s)
contain a given substring. My patch adds accessor methods similar to the
existing macros which fetch the minimal number of chunks to satisfy a
particular substring request. (In the case of a compressed value,
"minimal number" == "all of them"). In other words, instead of writing
PG_GETARG_TEXT_P(0) and receiving back a large allocated value, you can
say PG_GETARG_TEXT_P_SLICE(0,0,500) to receive only the first 500 bytes.
This has a minimal performance impact -an indexscan is already used to
retrieve TOAST chunks -the extra accessor routines just specify the
second key as well as the first.
I have rewritten text_substr and bytea_substr to use these methods.
I have also added an ALTER TABLE x ALTER COLUMN y SET STORAGE command to
provide a simple way to change attstorage for a column. (The macros
above will be most efficient with uncompressed values.)
Finally, I am aware of the following items which are not covered by the
patch:
1) Efficient updating of parts of a value. This is not trivial[1].
2) Should the large object interface be handled via TOAST?[2]
As for the applications, well, it foes allow you to stream MP3s out of
the database reasonably efficiently :-)
Any questions, please ask...
John
[1] And I don't know how it might be used. Maybe if anyone has any ideas
about substring assignment (someone did point me in the direction of an
OVERLAY() function) or its applications I can think about that.
[2] AIUI, the underlying mechanism is similar, but implemented
separately.
