From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sean Davis <sdavis2@mail.nih.gov>
Cc: John DeSoi <desoi@pgedit.com>
Cc: Carlos Moreno <moreno@mochima.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Question on triggers and plpgsql
Date: Fri, 08 Apr 2005 10:36:26 -0400
Message-ID: <14244.1112970986@sss.pgh.pa.us> (raw)
In-Reply-To: <fef7e50cf33b53ca481ecb2554cd6403@mail.nih.gov>
References: <4255AA15.8000508@mochima.com>
<BE1E8DAA-A829-11D9-9125-000A95B03262@pgedit.com>
<fef7e50cf33b53ca481ecb2554cd6403@mail.nih.gov>
Sean Davis <sdavis2@mail.nih.gov> writes:
> Just one detail, but in the form of a question. In the original
> posting, I think the trigger was doing the logging for something
> happening on a table as a before insert or update--I may be wrong on
> that detail. I would think of doing such actions AFTER the
> update/insert. In the world of transaction-safe operations, is there
> ANY danger in doing the logging as a BEFORE trigger rather than an
> AFTER trigger?
No, actually Carlos wanted to do
new.last_modified = now();
so he *must* use a BEFORE trigger --- AFTER is too late to change the
data that will be stored.
Generalizing freely, I've seen three basic uses for triggers:
1. Modify the data that will be stored.
2. Check that data is valid (eg, consistent with another table).
3. Propagate updates in one place to other places.
Clearly #1 must be done in BEFORE triggers. #2 and #3 could be done
either way. They are often done in AFTER triggers because that way you
*know* that any case-1 triggers have done their work and you are looking
at the correct final state of the row. But you could do them in a
BEFORE trigger if you were willing to assume that no later-fired trigger
would make a change that invalidates your check or propagation. AFTER
triggers are relatively expensive (since the triggering event state has
to be saved and then recalled) so I could see making that tradeoff if
performance is critical.
AFAICS the only way that you could get into a can't-roll-back situation
is if the trigger tries to propagate the update outside the database.
For instance, the proverbial trigger to send mail: once sent you can't
cancel it. But really this is dangerous even in an AFTER trigger ---
the transaction could still be rolled back after the AFTER trigger
fires.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, sdavis2@mail.nih.gov, desoi@pgedit.com, moreno@mochima.com
Subject: Re: Question on triggers and plpgsql
In-Reply-To: <14244.1112970986@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox