Thread: lo_manage trigger on updates

lo_manage trigger on updates

From
Josh Kupershmidt
Date:
Hi all,

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?

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.

Josh

Attachment

Re: lo_manage trigger on updates

From
Robert Haas
Date:
On Thu, Aug 11, 2011 at 11:43 PM, Josh Kupershmidt <schmiddy@gmail.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.

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.

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

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: lo_manage trigger on updates

From
Josh Kupershmidt
Date:
On Mon, Oct 10, 2011 at 1:18 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Aug 11, 2011 at 11:43 PM, Josh Kupershmidt <schmiddy@gmail.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

Re: lo_manage trigger on updates

From
Bruce Momjian
Date:
On Mon, Oct 10, 2011 at 01:18:56PM -0400, Robert Haas wrote:
> On Thu, Aug 11, 2011 at 11:43 PM, Josh Kupershmidt <schmiddy@gmail.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.
>
> 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.

I have implemented this suggestion with the attached, applied patch to
9.3.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Attachment

Re: lo_manage trigger on updates

From
Bruce Momjian
Date:
On Tue, Oct 11, 2011 at 08:08:32PM -0400, Josh Kupershmidt wrote:
> On Mon, Oct 10, 2011 at 1:18 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> > On Thu, Aug 11, 2011 at 11:43 PM, Josh Kupershmidt <schmiddy@gmail.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>.

I think I like that idea so I used your text instead of the full
new example.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +