Thread: After Trigger assignment to NEW
Hi, i am currently on 7.4.12, and i what i try to do is having an AFTER row trigger nullify one NEW column, so as to prevent a subsequent (AFTER row) trigger from using this column's data. Unfortunately this nullification (assignment) does not have any effect on the next AFTER trigger. The first trigger is in pl/pgsql, and the second is in C. One possible use of the above (if it worked) would be the following. Suppose the next AFTER triger is the (enhanced version of) dbmirror trigger which takes care of FK constraints and navigates thru the graph in order to mirror all depenent tables' rows too. Suppose there is no need to mirror a specific parent table. Then in the child table i write an AFTER trigger that nullifies this columns which is FK to the said parent table, and so prevent the unwanted traversal from happening in the execution of the next AFTER trigger (dbmirror). I'd like to ask, if it is considered the right behaviour and if there is a plan in changing it. Is there a reason that the NEW values should remain unchanged in AFTER row triggers? -- -Achilleus
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> Is there a reason that the NEW values should remain unchanged in AFTER
> row triggers?
By definition, an AFTER trigger is too late to change what was stored.
Use a BEFORE trigger.
regards, tom lane
Tom Lane wrote: > Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > > Is there a reason that the NEW values should remain unchanged in AFTER > > row triggers? > > By definition, an AFTER trigger is too late to change what was stored. > Use a BEFORE trigger. But a BEFORE trigger would alter the stored tuple, which is not what Achilleus wants AFAIU. I think the desired effect can be had by having DBMirror check the source relation of the inserted tuple (There is a hidden attributa called tableoid IIRC that can be used for that, I think). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
>>> Is there a reason that the NEW values should remain unchanged in AFTER
>>> row triggers?
>>
>> By definition, an AFTER trigger is too late to change what was stored.
>> Use a BEFORE trigger.
> But a BEFORE trigger would alter the stored tuple, which is not what
> Achilleus wants AFAIU.
Oh, I misunderstood what he wanted ... and now that I do understand,
I think it's a really terrible idea :-(. A large part of the point
of using an AFTER trigger is to be certain you know exactly what got
stored. (BEFORE triggers can never know this with certainty because
there might be another BEFORE trigger that runs after them and edits the
tuple some more.) If one AFTER trigger could falsify the data seen by
the next, then that guarantee crumbles. For instance, a minor
programming error in a user-written trigger could break foreign-key
checking. No thanks.
> I think the desired effect can be had by having DBMirror check the
> source relation of the inserted tuple (There is a hidden attributa
> called tableoid IIRC that can be used for that, I think).
I agree --- the correct solution is to change the DBMirror triggers to
incorporate the desired filtering logic.
regards, tom lane
O Tom Lane έγραψε στις Feb 24, 2006 : > Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > > Is there a reason that the NEW values should remain unchanged in AFTER > > row triggers? > > By definition, an AFTER trigger is too late to change what was stored. > Use a BEFORE trigger. Too late if someone wants to store it. I wanna store the intented original values, thats why i use AFTER trigger. But i would like to alter what a final AFTER trigger would see. I'll elabarote a little. An update happens. The row is stored. An after trigger is fired that alters some NEW columns (nullifies them), aiming for a subsequent trigger to see the altered results . It should be something like a pointer to a HeapTuple, (right?), so that would be feasible i suppose. I would not even make a post if it was something that trivial. I hope you get my point. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- -Achilleus
O Tom Lane έγραψε στις Feb 24, 2006 : > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Tom Lane wrote: > >> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > >>> Is there a reason that the NEW values should remain unchanged in AFTER > >>> row triggers? > >> > >> By definition, an AFTER trigger is too late to change what was stored. > >> Use a BEFORE trigger. > > > But a BEFORE trigger would alter the stored tuple, which is not what > > Achilleus wants AFAIU. > > Oh, I misunderstood what he wanted ... and now that I do understand, > I think it's a really terrible idea :-(. A large part of the point > of using an AFTER trigger is to be certain you know exactly what got > stored. (BEFORE triggers can never know this with certainty because > there might be another BEFORE trigger that runs after them and edits the > tuple some more.) If one AFTER trigger could falsify the data seen by > the next, then that guarantee crumbles. For instance, a minor > programming error in a user-written trigger could break foreign-key > checking. No thanks. Alvaro, Tom, thanx a lot, i'll have to incorporate that into dbmirror. > > > I think the desired effect can be had by having DBMirror check the > > source relation of the inserted tuple (There is a hidden attributa > > called tableoid IIRC that can be used for that, I think). > > I agree --- the correct solution is to change the DBMirror triggers to > incorporate the desired filtering logic. > > regards, tom lane > -- -Achilleus
Achilleus Mantzios wrote: > O Tom Lane έγραψε στις Feb 24, 2006 : > > > By definition, an AFTER trigger is too late to change what was > > stored. Use a BEFORE trigger. > > Too late if someone wants to store it. > I wanna store the intented original values, thats why i use > AFTER trigger. > But i would like to alter what a final AFTER trigger would see. > > I'll elabarote a little. > > An update happens. > The row is stored. > An after trigger is fired that alters some NEW columns > (nullifies them), aiming for a subsequent trigger > to see the altered results . > > It should be something like a pointer to a HeapTuple, (right?), > so that would be feasible i suppose. > > I would not even make a post if it was something that trivial. > > I hope you get my point. Your real problem is that the "subsequent" trigger has behaviour you don't like. That's what you should be fixing. If dbmirrorhas no way to exclude specific tables from mirroring, take it up with them as a feature request, or patch dbmirrorto work how you want it to. AFTER triggers *must* receive the row that was actually inserted/updated/deleted. If they could receive a "modified" rowthat didn't reflect what was actually in the database, all sorts of useful trigger-based logging and replication patternswouldn't work, and there's really no other way to implement them. See also Tom Lane's other message for furtherimplications of being able to modify the rows seen by AFTER triggers. I'd also be hesitant to write triggers that have to execute in a specific order.
O Owen Jacobson έγραψε στις Feb 24, 2006 : > Achilleus Mantzios wrote: > > > O Tom Lane έγραψε στις Feb 24, 2006 : > > > > > By definition, an AFTER trigger is too late to change what was > > > stored. Use a BEFORE trigger. > > > > Too late if someone wants to store it. > > I wanna store the intented original values, thats why i use > > AFTER trigger. > > But i would like to alter what a final AFTER trigger would see. > > > > I'll elabarote a little. > > > > An update happens. > > The row is stored. > > An after trigger is fired that alters some NEW columns > > (nullifies them), aiming for a subsequent trigger > > to see the altered results . > > > > It should be something like a pointer to a HeapTuple, (right?), > > so that would be feasible i suppose. > > > > I would not even make a post if it was something that trivial. > > > > I hope you get my point. > > Your real problem is that the "subsequent" trigger has behaviour you don't like. That's what you should be fixing. Ifdbmirror has no way to exclude specific tables from mirroring, take it up with them as a feature request, or patch dbmirrorto work how you want it to. > > AFTER triggers *must* receive the row that was actually inserted/updated/deleted. If they could receive a "modified" rowthat didn't reflect what was actually in the database, all sorts of useful trigger-based logging and replication patternswouldn't work, and there's really no other way to implement them. See also Tom Lane's other message for furtherimplications of being able to modify the rows seen by AFTER triggers. > As i have explained my dbmirror is FK null values gnostic(=aware) already as we speak. It normaly mirrors father rows according to certain criteria. (And the fathers of them and so on). Replication is done over UUCP over 5$/min satelite connections, so replicating just the right data for a slave is critically important. So nullifying a value just before the dbmirror trigger would do exactly the right thing (for me) Now implementing the "nullification on demand" feature in dbmirror means more work when i migrate to 8.x, i have severly modified dbmirror to do many things, and i thought it was time to stop! > I'd also be hesitant to write triggers that have to execute in a specific order. Meaning that would hurt portability? Most people need features rathen than the relief to know they can migrate to another database (which they probably never will) > Back to AFTER trigger changing values issue, i think things are not so dramatic if FK triggers could just be fired first. Anyway i'll modify dbmirror again. Oh BTW, There is a patch for DBMirror.pl (which steven hasnt yet fully reviewed) that solves the previous performance problems. > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- -Achilleus
On Sat, 25 Feb 2006, Achilleus Mantzios wrote: > O Owen Jacobson ������ ���� Feb 24, 2006 : > > > Achilleus Mantzios wrote: > > > > > O Tom Lane ������ ���� Feb 24, 2006 : > > > > > > > By definition, an AFTER trigger is too late to change what was > > > > stored. Use a BEFORE trigger. > > > > > > Too late if someone wants to store it. > > > I wanna store the intented original values, thats why i use > > > AFTER trigger. > > > But i would like to alter what a final AFTER trigger would see. > > > > > > I'll elabarote a little. > > > > > > An update happens. > > > The row is stored. > > > An after trigger is fired that alters some NEW columns > > > (nullifies them), aiming for a subsequent trigger > > > to see the altered results . > > > > > > It should be something like a pointer to a HeapTuple, (right?), > > > so that would be feasible i suppose. > > > > > > I would not even make a post if it was something that trivial. > > > > > > I hope you get my point. > > > > Your real problem is that the "subsequent" trigger has behaviour you > > don't like. That's what you should be fixing. If dbmirror has no way > > to exclude specific tables from mirroring, take it up with them as a > > feature request, or patch dbmirror to work how you want it to. > > > > AFTER triggers *must* receive the row that was actually > > inserted/updated/deleted. If they could receive a "modified" row that > > didn't reflect what was actually in the database, all sorts of useful > > trigger-based logging and replication patterns wouldn't work, and > > there's really no other way to implement them. See also Tom Lane's > > other message for further implications of being able to modify the > > rows seen by AFTER triggers. > > > > As i have explained my dbmirror is FK null values gnostic(=aware) already > as we speak. [...] > So nullifying a value just before the dbmirror trigger would do exactly > the right thing (for me) Yes it does what you want for this very specific case. But, would it do what you want if someone put a trigger before it that changed the values to some non-NULL thing? That seems likely to break your mirroring. > > I'd also be hesitant to write triggers that have to execute in a specific order. > > Meaning that would hurt portability? > Most people need features rathen than the relief to know they can migrate > to another database (which they probably never will) In this case, you're giving up the "feature" that users can write constraints, logging or mirroring after triggers that are guaranteed to get the data that was actually inserted in order to get the feature that a trigger can affect the data to the next trigger. This seems like a general loss in functionality for a larger fraction of users than those who gain. > Back to AFTER trigger changing values issue, > i think things are not so dramatic if > FK triggers could just be fired first. Actually, I think we technically fire the checks too early as it is, so I don't see enshrining that or making it earlier is a good idea.