Обсуждение: How to fire triggers just on "top" level DML
We've been running for about ten years on a framework which fires triggers similar to database triggers in a Java tier close to the database, and we're now trying to convert these to actual PostgreSQL database triggers. Our biggest hitch at the moment is that we defined a class of triggers we called "top" triggers, which only fire from DML submitted by the application, not from DML issued by other triggers. One significant use of this is to block direct modification of summary data (either selected columns or entire tables) which are supposed to be trigger maintained. It's not immediately obvious how to accomplish this within PostgreSQL, although I'm probably missing something. We're not tied to any particular methodology -- a TG_DEPTH variable, if it existed, would do fine, for example. Any suggestions? -Kevin
On Wednesday 19 January 2011 1:59:48 pm Kevin Grittner wrote:
> We've been running for about ten years on a framework which fires
> triggers similar to database triggers in a Java tier close to the
> database, and we're now trying to convert these to actual PostgreSQL
> database triggers. Our biggest hitch at the moment is that we
> defined a class of triggers we called "top" triggers, which only
> fire from DML submitted by the application, not from DML issued by
> other triggers.
>
> One significant use of this is to block direct modification of
> summary data (either selected columns or entire tables) which are
> supposed to be trigger maintained. It's not immediately obvious how
> to accomplish this within PostgreSQL, although I'm probably missing
> something. We're not tied to any particular methodology -- a
> TG_DEPTH variable, if it existed, would do fine, for example.
>
> Any suggestions?
>
> -Kevin
The only thing I can think of off the top of my head is to you trigger arguments
and then use the below to test:
TG_ARGV[]
Data type array of text; the arguments from the CREATE TRIGGER statement.
The index counts from 0. Invalid indexes (less than 0 or greater than or equal
to tg_nargs) result in a null value.
--
Adrian Klaver
adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> wrote: > TG_ARGV[] > > Data type array of text; the arguments from the CREATE > TRIGGER statement. Thanks for the suggestion, but I don't think this does what I need. I need to know whether the trigger was *fired* from inside another trigger, not something about the creation of trigger. -Kevin
On Jan 19, 2011, at 4:59 PM, Kevin Grittner wrote: > We've been running for about ten years on a framework which fires > triggers similar to database triggers in a Java tier close to the > database, and we're now trying to convert these to actual PostgreSQL > database triggers. Our biggest hitch at the moment is that we > defined a class of triggers we called "top" triggers, which only > fire from DML submitted by the application, not from DML issued by > other triggers. > > One significant use of this is to block direct modification of > summary data (either selected columns or entire tables) which are > supposed to be trigger maintained. It's not immediately obvious how > to accomplish this within PostgreSQL, although I'm probably missing > something. We're not tied to any particular methodology -- a > TG_DEPTH variable, if it existed, would do fine, for example. > > Any suggestions? Most PLs include some session-specific storage. In PL/Perl, it is %_SHARED. Setting a flag there should do the trick. Ifyou are using a PL which does not have such a notion (like plpgsql), you can add a call in your triggers to a functionwritten in a PL which does support this. Alternatively, a C function which sets/checks a global flag would work aswell. Cheers, M
"A.M." <agentm@themactionfaction.com> wrote: > Most PLs include some session-specific storage. In PL/Perl, it is > %_SHARED. Setting a flag there should do the trick. If you are > using a PL which does not have such a notion (like plpgsql), you > can add a call in your triggers to a function written in a PL > which does support this. Alternatively, a C function which > sets/checks a global flag would work as well. I thought it might come to that. I'm comfortable writing C functions, and we're not using any languages so far besides C, SQL, and plpgsql, so I'd probably use C. If I'm going that far, though, I'd be rather inclined to implement a TG_DEPTH variable (as being easier for us to use) and offer it to the community in case there's anyone else who would find this useful. If that turns out to be harder than I think, I'll fall back to what you outlined here. Thanks! -Kevin
On Jan 19, 2011, at 5:36 PM, Kevin Grittner wrote: > "A.M." <agentm@themactionfaction.com> wrote: > >> Most PLs include some session-specific storage. In PL/Perl, it is >> %_SHARED. Setting a flag there should do the trick. If you are >> using a PL which does not have such a notion (like plpgsql), you >> can add a call in your triggers to a function written in a PL >> which does support this. Alternatively, a C function which >> sets/checks a global flag would work as well. > > I thought it might come to that. I'm comfortable writing C > functions, and we're not using any languages so far besides C, SQL, > and plpgsql, so I'd probably use C. If I'm going that far, though, > I'd be rather inclined to implement a TG_DEPTH variable (as being > easier for us to use) and offer it to the community in case there's > anyone else who would find this useful. If that turns out to be > harder than I think, I'll fall back to what you outlined here. If you do implement TG_DEPTH, I am curious as to what the difference between TG_DEPTH==34 and TG_DEPTH==35 could mean. Ithink it might cause poor coding practice in making decisions based off assumed trigger order execution. Since you onlycare to distinguish between depth 1 and depth 2 (and not beyond), could you elaborate on a use case where further trigger"depth" information may be useful? Cheers, M
On Wednesday 19 January 2011 2:20:35 pm Kevin Grittner wrote: > Adrian Klaver <adrian.klaver@gmail.com> wrote: > > TG_ARGV[] > > > > Data type array of text; the arguments from the CREATE > > TRIGGER statement. > > Thanks for the suggestion, but I don't think this does what I need. > I need to know whether the trigger was *fired* from inside another > trigger, not something about the creation of trigger. > > -Kevin I misunderstood I thought there where 'top' triggers and other triggers and using arguments would be way to tag them. -- Adrian Klaver adrian.klaver@gmail.com
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "A.M." <agentm@themactionfaction.com> wrote:
>> Most PLs include some session-specific storage. In PL/Perl, it is
>> %_SHARED. Setting a flag there should do the trick. If you are
>> using a PL which does not have such a notion (like plpgsql), you
>> can add a call in your triggers to a function written in a PL
>> which does support this. Alternatively, a C function which
>> sets/checks a global flag would work as well.
> I thought it might come to that.
Correctly resetting such a value after a transaction/subtransaction
abort might be a bit problematic.
regards, tom lane
"A.M." <agentm@themactionfaction.com> wrote: > If you do implement TG_DEPTH, I am curious as to what the > difference between TG_DEPTH==34 and TG_DEPTH==35 could mean. I > think it might cause poor coding practice in making decisions > based off assumed trigger order execution. Since you only care to > distinguish between depth 1 and depth 2 (and not beyond), could > you elaborate on a use case where further trigger "depth" > information may be useful? Well, our current trigger engine in the Java tier keeps a count like that and has a depth limit to protect against runaway recursion. The only time we hit it was in purge logic, which is extremely convoluted due to requirements of statutes, supreme court rules, and auditor requirements. :-/ I might just put some sort of depth check into triggers fired during purges, to provide a more informative message than I might otherwise get. -Kevin
Tom Lane <tgl@sss.pgh.pa.us> wrote: > Correctly resetting such a value after a > transaction/subtransaction abort might be a bit problematic. Good point. Definitely an area to pay particularly close attention. Thanks, -Kevin
> Any suggestions? you might find this approach useful: http://www.depesz.com/index.php/2008/01/18/how-to-check-if-given-update-is-from-trigger-or-why-i-hate-orms/ Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007