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

From Tom Lane
Subject Re: Question about todo item
Date
Msg-id 21461.997109600@sss.pgh.pa.us
Whole thread Raw
In response to Re: Question about todo item  (Jan Wieck <JanWieck@Yahoo.com>)
List pgsql-hackers
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 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


pgsql-hackers by date:

Previous
From: Hannu Krosing
Date:
Subject: Re: AW: Re: OID wraparound: summary and proposal
Next
From: "Zeugswetter Andreas SB SD"
Date:
Subject: RE: AW: partial index