Thread: Trigger position

> On 15 Sep 2021, at 12:28, Marcos Pegoraro <marcos@f10.com.br> wrote:

> CREATE TRIGGER RECALC_THAT BEFORE UPDATE POSITION 1 ON ORDERS...
> CREATE TRIGGER DO_OTHER_CALC BEFORE UPDATE POSITION 2 ON ORDERS...

For those not familiar with Firebird: triggers are executing in alphabetical
order within a position number, so it multiple triggers are defined for
POSITION 1 then they are individually executed alphabetically.

--
Daniel Gustafsson        https://vmware.com/

Marcos Pegoraro <marcos@f10.com.br> writes:
> Alphabetical order of triggers sometimes makes me write a_Recalc or z_Calc
> to be sure it´ll be the first or the last trigger with same event of that
> table

> Oracle and SQL Server have FOLLOWS and PRECEDES when defining trigger
> execution order. Firebird has POSITION, which I like it more.

Color me skeptical: doesn't that introduce more complication without
fundamentally solving anything?  You still don't know which position
numbers other triggers have used, so it seems like this is just a
different way to spell the same problem.

            regards, tom lane

On 9/15/21 1:40 PM, Tom Lane wrote:
> Marcos Pegoraro <marcos@f10.com.br> writes:
>> Alphabetical order of triggers sometimes makes me write a_Recalc or z_Calc
>> to be sure it´ll be the first or the last trigger with same event of that
>> table
> 
>> Oracle and SQL Server have FOLLOWS and PRECEDES when defining trigger
>> execution order. Firebird has POSITION, which I like it more.
> 
> Color me skeptical: doesn't that introduce more complication without
> fundamentally solving anything?  You still don't know which position
> numbers other triggers have used, so it seems like this is just a
> different way to spell the same problem.

I guess one advantage is that it would make the intent of the DDL author 
more clear to a reader and that it also makes it more clear to people 
new to PostgreSQL that trigger order is something that is important to 
reason about.

If those small advantages are worth the complication is another question 
(I am skpetical), but if we would implement this I prefer the Firebird 
solution over the Oralce/MSSQL solution since the Firebird solution is 
simpler while achieving the same thing plus that the Firefird solution 
seems like it would be obviously backwards compatible with our current 
solution.

Andreas

On 2021-Sep-15, Marcos Pegoraro wrote:

> This problem can raise ... there is a trigger foo using position 1, please
> choose another

This is reminiscent of the old BASIC programming language, where you
eventually learn to choose line numbers that aren't consecutive, so that
if you later have to add lines in between you have some room to do so.
(This happens when modifying a program sufficient times you are forced
to renumber old lines where you want to add new lines that no longer fit
in the sequence.)  It's a pretty bad system.

In a computer system, alphabet letters are just a different way to
present numbers, so you just choose ASCII letters that match what you
want.  You can use "AA_first_trigger", "BB_second_trigger",
"AB_nope_this_is_second" and you'll be fine; you can do
"AAB_oops_really_second" afterwards, and so on.  The integer numbering
system doesn't seem very useful/flexible when seen in this light.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/
"Nunca confiaré en un traidor.  Ni siquiera si el traidor lo he creado yo"
(Barón Vladimir Harkonnen)

In a computer system, alphabet letters are just a different way to

present numbers, so you just choose ASCII letters that match what you

want.  You can use "AA_first_trigger", "BB_second_trigger",

"AB_nope_this_is_second" and you'll be fine; you can do

"AAB_oops_really_second" afterwards, and so on.  The integer numbering

system doesn't seem very useful/flexible when seen in this light.

On 2021-Sep-15, Pavel Stehule wrote:

> Triggers that depend on execution order are pretty hell. It is a clean
> signal of some crazy design and overusing of triggers.

Yeah.  The only case I've seen where order of triggers was important
(beyond the "before" / "after" classification) is where you have
something that you need to ensure runs before the FK triggers.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"XML!" Exclaimed C++.  "What are you doing here? You're not a programming
language."
"Tell that to the people who use me," said XML.
https://burningbird.net/the-parable-of-the-languages/

On 09/15/21 06:28, Marcos Pegoraro wrote:

> Oracle and SQL Server have FOLLOWS and PRECEDES when defining trigger
> execution order. Firebird has POSITION, which I like it more.

Between those two, I think my vote would come down the other way,
assuming FOLLOWS and PRECEDES work the way I am guessing they do:
you would be specifying the firing order between triggers whose
relative order you care about, and leaving it unspecified between
triggers whose relative order doesn't matter.

I find that an appealing general solution that allows the machine
to find a satisfactory order, and is less fussy than trying to manually
create a total order for all of the triggers (even those whose relative
order may not matter) by arbitrarily fussing with names or integers.

It resembles similar constructs in lots of other things, like the way
grammar precedences are specified [0] in SDF.

It may be objected that this makes a trigger order that is less
fully determined in advance, and can lead to issues that are harder
to reason out if you forgot to specify a relative order that matters.

But balancing that is that it may be easier in general to reason about
just the relative orders that matter, undistracted by any that don't.
In some settings, leaving unspecified the ones that don't may increase
opportunities for optimization. (Not that I have any specific optimizations
in mind for this setting.)

One could even think about a test mode that would deliberately randomize
the relative order between triggers where it hasn't been specified.

Regards,
-Chap


[0]
https://www.metaborg.org/en/latest/source/langdev/meta/lang/sdf3/reference.html#priorities