Re: Trigger position
We can run triggers using position only, this way we don´t have these few cycles to determine ordering. On creation time we populate position, even if it's not set, so for the first time position will match trigger names. When user changes a trigger position we sum 1 to the followers. regards, Marcos Em qua., 15 de set. de 2021 às 12:13, Euler Taveira escreveu: > On Wed, Sep 15, 2021, at 10:51 AM, Alvaro Herrera wrote: > > 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. > > ... or renumber all trigger positions in a single transaction. I agree that > letters are more flexible than numbers but some users are number-oriented. > > I'm afraid an extra mechanism to determine the order to fire triggers will > confuse programmers if someone decides to use both. Besides that, we have > to > expend a few cycles to determine the exact trigger execution order. > > > -- > Euler Taveira > EDB https://www.enterprisedb.com/ > >
Re: Trigger position
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
Re: Trigger position
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/
Re: Trigger position
st 15. 9. 2021 v 17:14 odesílatel Euler Taveira napsal: > On Wed, Sep 15, 2021, at 10:51 AM, Alvaro Herrera wrote: > > 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. > > ... or renumber all trigger positions in a single transaction. I agree that > letters are more flexible than numbers but some users are number-oriented. > > I'm afraid an extra mechanism to determine the order to fire triggers will > confuse programmers if someone decides to use both. Besides that, we have > to > expend a few cycles to determine the exact trigger execution order. > Triggers that depend on execution order are pretty hell. It is a clean signal of some crazy design and overusing of triggers. Personally I prefer to don't have any similar feature just as a strong signal for developers - Don't do this. Unfortunately (but good for business) . A lot of migrated applications from Oracle use this terrible style. I like PL/SQL, but the most ugly code that I saw was in PL/SQL. So this feature can be necessary for migrations from Oracle, but I don't see reasons to be more visible. Regards Pavel > > -- > Euler Taveira > EDB https://www.enterprisedb.com/ > >
Re: Trigger position
On Wed, Sep 15, 2021, at 10:51 AM, Alvaro Herrera wrote: > 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. ... or renumber all trigger positions in a single transaction. I agree that letters are more flexible than numbers but some users are number-oriented. I'm afraid an extra mechanism to determine the order to fire triggers will confuse programmers if someone decides to use both. Besides that, we have to expend a few cycles to determine the exact trigger execution order. -- Euler Taveira EDB https://www.enterprisedb.com/
Re: Trigger position
When I was writing my initial email I was remembering exactly this, my first basic programs. I would like this feature more because I sometimes have a mess of triggers when this trigger function is fired on several tables and it needs to be the first on this table but not on that table. And usually trigger names have same names as their functions, so for this table I have to have a different name just to be fired first. Em qua., 15 de set. de 2021 às 10:51, Alvaro Herrera < alvhe...@alvh.no-ip.org> escreveu: > 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) >
Re: Trigger position
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)
Re: Trigger position
Correct, we need a field tgposition on pg_trigger and when it´s null we follow normal ordering select * from pg_trigger where tgrelid = X and tgtype = Y order by tgposition nulls last, tgname regards, Marcos Em qua., 15 de set. de 2021 às 09:35, Andreas Karlsson escreveu: > On 9/15/21 1:40 PM, Tom Lane wrote: > > Marcos Pegoraro 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 >
Re: Trigger position
On 9/15/21 1:40 PM, Tom Lane wrote: Marcos Pegoraro 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
Re: Trigger position
This way would be interesting for those are migrating from these databases too. But ok, I´ll forget it. Em qua., 15 de set. de 2021 às 08:40, Tom Lane escreveu: > Marcos Pegoraro 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 >
Re: Trigger position
This problem can raise ... there is a trigger foo using position 1, please choose another Atenciosamente, Em qua., 15 de set. de 2021 às 07:59, Daniel Gustafsson escreveu: > > On 15 Sep 2021, at 12:28, Marcos Pegoraro 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/ > >
Re: Trigger position
Marcos Pegoraro 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
Re: Trigger position
> On 15 Sep 2021, at 12:28, Marcos Pegoraro 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/
Trigger position
Hi Hackers, 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. What do you think about it, do you know an old/abandoned patch that was not committed ? CREATE TRIGGER RECALC_THAT BEFORE UPDATE POSITION 1 ON ORDERS... CREATE TRIGGER DO_OTHER_CALC BEFORE UPDATE POSITION 2 ON ORDERS... Regards, Marcos
