Re: Question about todo item
От | Tom Lane |
---|---|
Тема | Re: Question about todo item |
Дата | |
Msg-id | 21461.997109600@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: Question about todo item (Jan Wieck <JanWieck@Yahoo.com>) |
Список | 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 по дате отправления: