Re: lo_manage trigger on updates

On Mon, Oct 10, 2011 at 1:18 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Aug 11, 2011 at 11:43 PM, Josh Kupershmidt <schmiddy(at)gmail(dot)com> wrote:
>> I think the doc section about using lo_manage() as a trigger:
>>   http://www.postgresql.org/docs/current/static/lo.html
>>
>> could have its example tweaked to use a column-level BEFORE UPDATE
>> trigger, so as to save unnecessary trigger firings. Something like the
>> attached, perhaps?
>
> Uh, wow.  That syntax is horribly surprising, isn't it?  My eyes want
> to parse it as:
>
> BEFORE (UPDATE OF raster) OR (DELETE ON image)
>
> ...which is totally wrong.

Yeah, the syntax we have is really confusing. I notice this tidbit on that page:

| The ability to specify multiple actions for a single trigger using OR
| is a PostgreSQL extension of the SQL standard.

Maybe the folks dreaming up the SQL standard are sharper than they get
credit for.

> I'm inclined to think that maybe we should leave that example as-is,
> and maybe add the variant you're proposing as a second example,
> showing how the basic version can be refined.

The nice thing about keeping the example the way it is, is that it's
pretty simple to understand, and maybe adding the second slightly more
complicated example would just confuse things. We could just add in a
blurb like this at the end of "How to Use It":

You may wish to restrict the trigger
to only fire upon UPDATEs of the <type>lo</> column(s) in the table by
specifying the column name via BEFORE UPDATE OF
<replaceable class="parameter">column_name</replaceable>.

>> On a similar note, the warning on that page about truncates could be
>> softened if we extended the lo_manage() function to handle truncates,
>> and set it up as on ON TRUNCATE trigger as well.
>
> Patch?  :-)

I toyed around with it, but then lost interest because it'd require
almost rewriting the entire lo/ module, and I just wasn't in the mood.
Maybe someone will feel like messing with it.

Josh