PL/pgSQL : notion of deferred execution

On Sun, Dec 11, 2005 at 16:31:59 +0100,
Frank van Vugt <ftm.van.vugt@foxi.nl> wrote:

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

It might also work for you to do this with access rights. The triggers can
have different access rights than the user that caused the trigger to fire.

Bruno Wolff III <bruno@wolff.to> writes:

On Sun, Dec 11, 2005 at 16:31:59 +0100,
Frank van Vugt <ftm.van.vugt@foxi.nl> wrote:

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

It might also work for you to do this with access rights. The triggers can
have different access rights than the user that caused the trigger to fire.

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. 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.

regards, tom lane

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.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:

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).

After reading the explanation, this seems way too specialized a
situation to make a good argument that we need a general feature
of the sort.

You can get to where you need to be by passing an indicator in
the trigger arguments, eg,

CREATE TRIGGER ... BEFORE UPDATE ... EXECUTE PROCEDURE mytrigger(0);
CREATE TRIGGER ... AFTER UPDATE ... EXECUTE PROCEDURE mytrigger(1);

regards, tom lane

After reading the explanation, this seems way too specialized a
situation to make a good argument that we need a general feature
of the sort.

Ok, however, thanks for your time on it, anyway!

You can get to where you need to be by passing an indicator in
the trigger arguments

Argh, it's always the simple stuff ;)

Had been overlooking TG_ARGV[] here, that should prove to be a nice
compromise, thanks for the hint!

--
Best,

Frank.