Re: Question about todo item - Mailing list pgsql-hackers

Tom Lane wrote:
> 
> Jan Wieck <JanWieck@yahoo.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