Re: Question about todo item

Tom Lane wrote:
>
> 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 think so too, as we shouldnt do in-place modification in the toast
table anyway but give changed pages new trx ids, i.e UPDATE them.

it could be somewhat tricky to change just a few pages if there are
some inter page pointers in toast-table. If its all done with regular
index only then this should pose no problem.

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

but in both inline and toast-table modified pages should have new
transaction id's like regular tuples and thus be handled by regular
transaction commit/abort mechanics, at least this seema as a postgres
way to do it .

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

If toast table has the same permissions as the main table and lo_write
honours these then we should be ok.

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