Re: lo_manage trigger on updates
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.htmlcould 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
Import Notes
Reply to msg id not found: CAK3UJRHaEuzfnZaxarcV8=RP1sC5xLxumzqx6iiyqpJVS89rjw@mail.gmail.comReference msg id not found: CAK3UJRHaEuzfnZaxarcV8=RP1sC5xLxumzqx6iiyqpJVS89rjw@mail.gmail.com
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.htmlcould 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
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.htmlcould 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. +
Attachments:
lo.difftext/x-diff; charset=us-asciiDownload+10-10
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.htmlcould 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. +