Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 36D36B5DBDF for ; Fri, 12 Aug 2011 00:44:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 55636-09 for ; Fri, 12 Aug 2011 03:44:10 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com [209.85.216.46]) by mail.postgresql.org (Postfix) with ESMTP id 0801DB5DBD0 for ; Fri, 12 Aug 2011 00:44:09 -0300 (ADT) Received: by qwk3 with SMTP id 3so1376691qwk.19 for ; Thu, 11 Aug 2011 20:44:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:from:date:message-id:subject:to:content-type; bh=uFuK5NXt0Fsd7axK6K/iu6C0s+f64qSRXZXqFxyXXQ4=; b=YJmsbw77A18Nkl9F4sV7i8Da3lwbjhzBcA7itAkRrZD40g6PjiZ61dA7SWzgWUl4QM U3J8Tshr1OUQTBzW9Dt5HcI2c6VSxXW/ZOjY192icYbpCREezr0RMN7wcW9Q8VfJg2PB vM6cMyr/B+d5abUB7YjZR9clHg5MW6e/o2SS0= Received: by 10.229.25.68 with SMTP id y4mr300432qcb.24.1313120648357; Thu, 11 Aug 2011 20:44:08 -0700 (PDT) MIME-Version: 1.0 Received: by 10.229.34.70 with HTTP; Thu, 11 Aug 2011 20:43:47 -0700 (PDT) From: Josh Kupershmidt Date: Thu, 11 Aug 2011 23:43:47 -0400 Message-ID: Subject: lo_manage trigger on updates To: pgsql-docs Content-Type: multipart/mixed; boundary=0016367f97e2cae68b04aa46b9b6 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201108/8 X-Sequence-Number: 6898 --0016367f97e2cae68b04aa46b9b6 Content-Type: text/plain; charset=ISO-8859-1 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 --0016367f97e2cae68b04aa46b9b6 Content-Type: application/octet-stream; name="lo.diff" Content-Disposition: attachment; filename="lo.diff" Content-Transfer-Encoding: base64 X-Attachment-Id: f_gr7xw85s0 ZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9sby5zZ21sIGIvZG9jL3NyYy9zZ21sL2xvLnNnbWwK bmV3IGZpbGUgbW9kZSAxMDA2NDQKaW5kZXggM2Q1NmJhMy4uMGNlZDNhZQoqKiogYS9kb2Mvc3Jj L3NnbWwvbG8uc2dtbAotLS0gYi9kb2Mvc3JjL3NnbWwvbG8uc2dtbAoqKioqKioqKioqKioqKioK KioqIDY5LDgyICoqKioKICA8cHJvZ3JhbWxpc3Rpbmc+CiAgQ1JFQVRFIFRBQkxFIGltYWdlICh0 aXRsZSBURVhULCByYXN0ZXIgbG8pOwogIAohIENSRUFURSBUUklHR0VSIHRfcmFzdGVyIEJFRk9S RSBVUERBVEUgT1IgREVMRVRFIE9OIGltYWdlCiAgICAgIEZPUiBFQUNIIFJPVyBFWEVDVVRFIFBS T0NFRFVSRSBsb19tYW5hZ2UocmFzdGVyKTsKICA8L3Byb2dyYW1saXN0aW5nPgogIAogICAgPHBh cmE+CiAgICAgRm9yIGVhY2ggY29sdW1uIHRoYXQgd2lsbCBjb250YWluIHVuaXF1ZSByZWZlcmVu Y2VzIHRvIGxhcmdlIG9iamVjdHMsCiAgICAgY3JlYXRlIGEgPGxpdGVyYWw+QkVGT1JFIFVQREFU RSBPUiBERUxFVEU8Lz4gdHJpZ2dlciwgYW5kIGdpdmUgdGhlIGNvbHVtbgohICAgIG5hbWUgYXMg dGhlIHNvbGUgdHJpZ2dlciBhcmd1bWVudC4gIElmIHlvdSBuZWVkIG11bHRpcGxlIDx0eXBlPmxv PC8+CiAgICAgY29sdW1ucyBpbiB0aGUgc2FtZSB0YWJsZSwgY3JlYXRlIGEgc2VwYXJhdGUgdHJp Z2dlciBmb3IgZWFjaCBvbmUsCiAgICAgcmVtZW1iZXJpbmcgdG8gZ2l2ZSBhIGRpZmZlcmVudCBu YW1lIHRvIGVhY2ggdHJpZ2dlciBvbiB0aGUgc2FtZSB0YWJsZS4KICAgIDwvcGFyYT4KLS0tIDY5 LDkwIC0tLS0KICA8cHJvZ3JhbWxpc3Rpbmc+CiAgQ1JFQVRFIFRBQkxFIGltYWdlICh0aXRsZSBU RVhULCByYXN0ZXIgbG8pOwogIAohIENSRUFURSBUUklHR0VSIHRfcmFzdGVyIEJFRk9SRSBVUERB VEUgT0YgcmFzdGVyIE9SIERFTEVURSBPTiBpbWFnZQogICAgICBGT1IgRUFDSCBST1cgRVhFQ1VU RSBQUk9DRURVUkUgbG9fbWFuYWdlKHJhc3Rlcik7CiAgPC9wcm9ncmFtbGlzdGluZz4KICAKICAg IDxwYXJhPgogICAgIEZvciBlYWNoIGNvbHVtbiB0aGF0IHdpbGwgY29udGFpbiB1bmlxdWUgcmVm ZXJlbmNlcyB0byBsYXJnZSBvYmplY3RzLAogICAgIGNyZWF0ZSBhIDxsaXRlcmFsPkJFRk9SRSBV UERBVEUgT1IgREVMRVRFPC8+IHRyaWdnZXIsIGFuZCBnaXZlIHRoZSBjb2x1bW4KISAgICBuYW1l IGFzIHRoZSBzb2xlIHRyaWdnZXIgYXJndW1lbnQuICBZb3UgbWF5IHdpc2ggdG8gcmVzdHJpY3Qg dGhlIHRyaWdnZXIKISAgICB0byBvbmx5IGZpcmUgdXBvbiBVUERBVEVzIG9mIHRoZSA8dHlwZT5s bzwvPiBjb2x1bW4ocykgaW4gdGhlIHRhYmxlIGJ5CiEgICAgc3BlY2lmeWluZyB0aGUgY29sdW1u IG5hbWUgdmlhIEJFRk9SRSBVUERBVEUgT0YgCiEgICAgPHJlcGxhY2VhYmxlIGNsYXNzPSJwYXJh bWV0ZXIiPmNvbHVtbl9uYW1lPC9yZXBsYWNlYWJsZT4gYXMgaW4gdGhlIGV4YW1wbGUKISAgICBh Ym92ZS4KISAgIDwvcGFyYT4KISAKISAgIDxwYXJhPgohICAgIElmIHlvdSBuZWVkIG11bHRpcGxl IDx0eXBlPmxvPC8+CiAgICAgY29sdW1ucyBpbiB0aGUgc2FtZSB0YWJsZSwgY3JlYXRlIGEgc2Vw YXJhdGUgdHJpZ2dlciBmb3IgZWFjaCBvbmUsCiAgICAgcmVtZW1iZXJpbmcgdG8gZ2l2ZSBhIGRp ZmZlcmVudCBuYW1lIHRvIGVhY2ggdHJpZ2dlciBvbiB0aGUgc2FtZSB0YWJsZS4KICAgIDwvcGFy YT4K --0016367f97e2cae68b04aa46b9b6--