Re: PL/pgSQL : notion of deferred execution - Mailing list pgsql-general

> >> Ratio: when deferred triggers on table A are used to calculate field
> >> values of table B (which then obviously need an update), one might want
> >> to prevent direct updates on these fields of table B

[BW III]
> It might also work for you to do this with access rights.

Well, actually that's kinda how it's working at the moment, however I would
rather avoid this additional dependancy on user/role since it's really
field/situation-dependent (see below).

> > The triggers can have different access rights than the user that
> > caused the trigger to fire.

Yep, and one can even get a bit more granularity switching roles.

[TL]
> Yes.  Keying this off whether the triggers are deferred or not seems
> a fairly bizarre choice --- that's surely not the primary property that
> should determine what they can do to table B.

Well, I didn't want to immediately bore the reader ;)

The choice wasn't made lightely, though. The underlying reason is a
performance enhancement. Basically, what's determining if the particular (!)
fields in B can be updated is the fact whether or not this is done from a
deferred trigger, as it is the sole place where this can be done efficiently.

Table A is updated quite a lot within the same transaction and often the same
rows are updated on different points in time. Table B is amongst other
things, holding derivative information of table A, but this data is difficult
to calculate, i.e. it takes time. A comparable situation would be that A is
holding orderlines and B holds orders with values *that take long to
calculate* because they depend on more rows of A than just the
inserted/changed/deleted one. Or maybe even better, the derivative info in
table B cannot be determined by solely using the inserted/updated/deleted
data in A.

Now, regular triggers obviously can take care of calculating the correct
values for B upon each update of a row in A, but due to the amount of updates
as well as the fact that a lot of the time rows in A are updated multiple
times, this resulted in bad (at least, too bad) performance earlier. I solved
this by using deferred triggers on A that are able to detect whether the
corresponding row of B has already been updated, thus making sure the complex
calculations are only done once.

Now, table B is also in regular use by normal users, so they need normal write
access and I'm keeping track of who's updating B when (your regular
updated_on/by fields and history tables).

> Also, it's quite unclear which part of the system you expect to enforce
> the restriction against which other part. The suggestion as stated seemed
> to be to let a trigger find out whether it itself is being fired in
> a deferred fashion, but surely you don't want to trust the trigger to decide
> whether it may update B.

Ah, no, the trigger must not decide the latter, whether or not B as a whole
may be updated solely depends (as it should) on the access rights of the user
running/triggering the code. It's more like I'm trying to prevent direct
updates of certain fields in B while knowing that the only way such update
can be done efficiently is from within these deferred triggers.

I can use a 'security definer' on the deferred triggers to allow switching
roles, then switch role within them and check for that with 'current_user' in
the regular triggers that will fire upon B's update, while still using
'session_user' to get to the original id that logged in. But as stated, given
the efficiency-knowledge I'd rather know where I'm called from.

I just wondered if there was a way to know the difference. Given the TG_WHEN
var I could imagine there was, but If there is not and it's also not
something expected to be 'nice to have', so be it, no problem ;)

Too bad TriggerEvent doesn't seem to make this info available, either ;)




--
Best,




Frank.