3 ms·
> as far as i know, i am sticking to standard SQL PostgreSQL documentation is very good about comparing each feature to the standard. For example, the SQL sta
by paulddraper 5y ago
> as far as i know, i am sticking to standard SQL
PostgreSQL documentation is very good about comparing each feature to the standard.
For example, the SQL standard specifies that triggers fire in the historical order they are added to the table.
PostgreSQL chose to fire them alphabetically.
Technically nonconformant, but in practical terms, far better.
- dijit 5y agoAlso the c++ bindings are very mature. This was surprising for my old tech lead who was sheepish on moving away from MySQL. He would later refer to the MySQL bindings as “brain dead”
- ipaddr 5y agoWhy would alphabetically be better? Wouldn't you want first in first out?
- jolux 5y agoIt means that the order they fire is imperative and determined by how you run your migrations.
- paulddraper 5y agoThe problem is that it doesn't leave you a great way to adjust order. It's just kinda weird. (To be fair, table columns share this characteristic.)
- marcosdumay 5y ago> To be fair, table columns share this characteristic. The table columns order is meaningless, but the triggers order is very relevant.
- paulddraper 5y agoTable column order is not meaningless. ``` INSERT INTO foo SELECT * FROM bar ``` The harder problem however, is order in composite types.
- marcosdumay 5y agoYeah, I do really recommend people not to do that sort of thing when they ask me.
- Pamar 5y agoNot only what @setr answered but consider also that by having a consistent ruling you can actually design or alter the order of triggers just by naming them according to a specific scheme. It just makes easier to understand what happens when triggers are invoked, without having to actually force specific cases by direct experimentation. I did not even know that the standard prescribed a FIFO order, looks like a pretty bad idea to me, actually.
- setr 5y agoAlphabetical makes it so two databases with the same triggers have the same behavior. FIFO breaks that, because now it depends on trigger construction order (eg when dump/restoring) and is totally invisible to the user. Additionally, modifying a trigger by drop/creating it would change behavior, unless you also drop/create every trigger (in the original order)
- ipaddr 5y agoThank you most important thing I learned today.
- zaphirplane 5y agoWhat is an example where the order of execution is required, not contrived or bad practise. Looking for a good use case
- setr 5y agoEasiest I can imagine is a user-facing id that gets incremented by trigger (e.g. select max(col)+1 — autoincrement/serial doesn’t guarantee contiguity, or even reasonable increments…). And then any second trigger that relies on that value
- paulddraper 5y agoOh, I think it's probably bad practice to knowingly depend on that. But I also think that it's unnecessarily problematic to make the order non-deterministic.
- throwaway2037 5y agoWhy was this down-voted? It is a good question. I thought exactly the same myself. And the reply from @setr is helpful. (Hat tip) I never thought about the impact of dump/restore!