Subject: lo_manage trigger on updates

From: Josh Kupershmidt <[email-masked]>
Date: Thu, 11 Aug 2011 23:43:47 -0400

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

---

From: Robert Haas <[email-masked]>
Date: Mon, 10 Oct 2011 13:18:56 -0400

> trigger, so as to save unnecessary trigger firings. Something like the

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.

> and set it up as on ON TRUNCATE trigger as well.

Patch?  :-)

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

---

From: Josh Kupershmidt <[email-masked]>
Date: Tue, 11 Oct 2011 20:08:32 -0400

> ...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.

> 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>.

>> and set it up as on ON TRUNCATE trigger as well.

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

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 14 Aug 2012 23:22:20 -0400

> showing how the basic version can be refined.

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

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
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>

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 14 Aug 2012 23:25:49 -0400

>     <replaceable class="parameter">column_name</replaceable>.

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