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(dot)klaver(at)gmail(dot)com

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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. 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.

Cheers,
M

"A.M." <agentm(at)themactionfaction(dot)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(at)themactionfaction(dot)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. 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?

Cheers,
M

On Wednesday 19 January 2011 2:20:35 pm Kevin Grittner wrote:
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> "A.M." <agentm(at)themactionfaction(dot)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(at)themactionfaction(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007