Обсуждение: lo_manage trigger on updates
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
Вложения
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
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
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. +
Вложения
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. +