public inbox for [email protected]help / color / mirror / Atom feed
lo_manage trigger on updates 5+ messages / 3 participants [nested] [flat]
* lo_manage trigger on updates @ 2011-08-12 03:43 Josh Kupershmidt <[email protected]> 2011-10-10 17:18 ` Re: lo_manage trigger on updates Robert Haas <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Josh Kupershmidt @ 2011-08-12 03:43 UTC (permalink / raw) To: pgsql-docs 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 Attachments: [application/octet-stream] lo.diff (1.7K, 2-lo.diff) download | inline diff: diff --git a/doc/src/sgml/lo.sgml b/doc/src/sgml/lo.sgml new file mode 100644 index 3d56ba3..0ced3ae *** a/doc/src/sgml/lo.sgml --- b/doc/src/sgml/lo.sgml *************** *** 69,82 **** <programlisting> CREATE TABLE image (title TEXT, raster lo); ! CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster); </programlisting> <para> For each column that will contain unique references to large objects, create a <literal>BEFORE UPDATE OR DELETE</> trigger, and give the column ! name as the sole trigger argument. If you need multiple <type>lo</> columns in the same table, create a separate trigger for each one, remembering to give a different name to each trigger on the same table. </para> --- 69,90 ---- <programlisting> CREATE TABLE image (title TEXT, raster lo); ! CREATE TRIGGER t_raster BEFORE UPDATE OF raster OR DELETE ON image FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster); </programlisting> <para> For each column that will contain unique references to large objects, create a <literal>BEFORE UPDATE OR DELETE</> trigger, and give the column ! name as the sole trigger argument. 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> as in the example ! above. ! </para> ! ! <para> ! If you need multiple <type>lo</> columns in the same table, create a separate trigger for each one, remembering to give a different name to each trigger on the same table. </para> ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: lo_manage trigger on updates 2011-08-12 03:43 lo_manage trigger on updates Josh Kupershmidt <[email protected]> @ 2011-10-10 17:18 ` Robert Haas <[email protected]> 2011-10-12 00:08 ` Re: lo_manage trigger on updates Josh Kupershmidt <[email protected]> 2012-08-15 03:22 ` Re: lo_manage trigger on updates Bruce Momjian <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Robert Haas @ 2011-10-10 17:18 UTC (permalink / raw) To: Josh Kupershmidt <[email protected]>; +Cc: pgsql-docs On Thu, Aug 11, 2011 at 11:43 PM, Josh Kupershmidt <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: lo_manage trigger on updates 2011-08-12 03:43 lo_manage trigger on updates Josh Kupershmidt <[email protected]> 2011-10-10 17:18 ` Re: lo_manage trigger on updates Robert Haas <[email protected]> @ 2011-10-12 00:08 ` Josh Kupershmidt <[email protected]> 2012-08-15 03:25 ` Re: lo_manage trigger on updates Bruce Momjian <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Josh Kupershmidt @ 2011-10-12 00:08 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: pgsql-docs On Mon, Oct 10, 2011 at 1:18 PM, Robert Haas <[email protected]> wrote: > On Thu, Aug 11, 2011 at 11:43 PM, Josh Kupershmidt <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: lo_manage trigger on updates 2011-08-12 03:43 lo_manage trigger on updates Josh Kupershmidt <[email protected]> 2011-10-10 17:18 ` Re: lo_manage trigger on updates Robert Haas <[email protected]> 2011-10-12 00:08 ` Re: lo_manage trigger on updates Josh Kupershmidt <[email protected]> @ 2012-08-15 03:25 ` Bruce Momjian <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Bruce Momjian @ 2012-08-15 03:25 UTC (permalink / raw) To: Josh Kupershmidt <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-docs On Tue, Oct 11, 2011 at 08:08:32PM -0400, Josh Kupershmidt wrote: > On Mon, Oct 10, 2011 at 1:18 PM, Robert Haas <[email protected]> wrote: > > On Thu, Aug 11, 2011 at 11:43 PM, Josh Kupershmidt <[email protected]> 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 <[email protected]> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: lo_manage trigger on updates 2011-08-12 03:43 lo_manage trigger on updates Josh Kupershmidt <[email protected]> 2011-10-10 17:18 ` Re: lo_manage trigger on updates Robert Haas <[email protected]> @ 2012-08-15 03:22 ` Bruce Momjian <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Bruce Momjian @ 2012-08-15 03:22 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Josh Kupershmidt <[email protected]>; pgsql-docs On Mon, Oct 10, 2011 at 01:18:56PM -0400, Robert Haas wrote: > On Thu, Aug 11, 2011 at 11:43 PM, Josh Kupershmidt <[email protected]> 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 <[email protected]> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + Attachments: [text/x-diff] lo.diff (1.3K, 2-lo.diff) download | inline diff: diff --git a/doc/src/sgml/lo.sgml b/doc/src/sgml/lo.sgml new file mode 100644 index 3d56ba3..0c11fdc *** a/doc/src/sgml/lo.sgml --- b/doc/src/sgml/lo.sgml *************** CREATE TRIGGER t_raster BEFORE UPDATE OR *** 76,82 **** <para> For each column that will contain unique references to large objects, create a <literal>BEFORE UPDATE OR DELETE</> trigger, and give the column ! name as the sole trigger argument. If you need multiple <type>lo</> columns in the same table, create a separate trigger for each one, remembering to give a different name to each trigger on the same table. </para> --- 76,90 ---- <para> For each column that will contain unique references to large objects, create a <literal>BEFORE UPDATE OR DELETE</> trigger, and give the column ! name as the sole trigger argument. You can also restrict the trigger ! to only execute on updates to the column with: ! ! <programlisting> ! CREATE TRIGGER t_raster BEFORE UPDATE OF raster OR DELETE ON image ! FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster); ! </programlisting> ! ! If you need multiple <type>lo</> columns in the same table, create a separate trigger for each one, remembering to give a different name to each trigger on the same table. </para> ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-08-15 03:25 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-08-12 03:43 lo_manage trigger on updates Josh Kupershmidt <[email protected]> 2011-10-10 17:18 ` Robert Haas <[email protected]> 2011-10-12 00:08 ` Josh Kupershmidt <[email protected]> 2012-08-15 03:25 ` Bruce Momjian <[email protected]> 2012-08-15 03:22 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox