Re: Question about todo item

Jan Wieck <JanWieck(at)yahoo(dot)com> writes:
> One of the problems I saw, and that's probably why we don't
> have a proposal yet, is, that the size of the data is
> recorded in the toast reference held in the main tuple. If
> you later open the toast value for writing, you'll change the
> size, but you'd need to change it in the main tuple too,
> what'd require a regular update on the main tuple, what I
> don't think we want to have here.

Well, in fact, maybe we *should*. I was thinking last night about
the fact that large objects as they stand are broken from a
permissions-checking point of view: anyone who knows an LO's OID
can read or write it. A LO-style interface for toasted columns must
not be so brain-dead. This says that a SELECT open_toast_object()
should deliver a read-only object reference, and that if you want
to update, you should have to do an UPDATE.

Now a read-only TOAST LO reference strikes me as no problem. If the
open() function finds that it's been handed a not-toasted value, it
can just save the value verbatim in the open-LO-reference table.
The value is not large, by definition, so this will work fine.

As for the update side of things, the best idea I can come up with
is a multi-phase operation: open the value with a select, read/write
the reference, store the updated reference with UPDATE. Something
like:

1. SELECT writable_toast_reference(column) FROM table WHERE ...;

(Actually, SELECT FOR UPDATE would be the more common idiom.)

2. Read and/or write the LO reference returned by SELECT. Note that
this must be defined to read/write a temporary work area --- if the
transaction aborts in this part, or commits without doing UPDATE,
nothing has happened to the stored value referenced by the main table
row. (I think this happens automatically if we are hacking rows in
a toast table. If we are hacking an in-line value stored in the
LO-reference table, we might at some point decide we need to shove it
out to disk.)

3. UPDATE table SET column = write_toast_reference(objectref) WHERE ...;

write_toast_reference extracts the toastable column's data or reference
from the LO table, closes the open LO reference (so you can't continue
hacking the data afterwards), and proceeds with a normal UPDATE.

It would also be pretty straightforward to extend this to the INSERT
case: we just need an "open" function that creates a new, empty object
of a TOASTable type in the LO reference table. Write on this, and
finally invoke write_toast_reference() in the INSERT.

Kinda grotty, but implementable, and it doesn't require a whole new set
of permissions concepts. Can anyone improve on this?

regards, tom lane