Updating a different table based on changes from a settings table
How does one implement this in a way that won't crash a db?
For example, a different table (T1) references a central settings(T2) table, and when that setting changes, ALL rows in T1 must update with that new setting
To make it more complicated, T1 has rows which use the setting for calculations, so those values will have to be recalculated as well
----
For context, i'm currently creating a payroll system for a small company using next, postgres
I currently implement the solution to what i mentioned by having a 'bulk regenerate calculations' button (for example for the employee timesheets). Only problem is, with just around 100 of those, supabase already cuts the connection/times out
what would be a better way to approach this?
1 Reply
im not exactly sure why it takes a while to update the 100 rows but my guess is is that the transaction involves querying a few more databases (for settings) before the calculation is ran
the calculations themselves are 99% handled by express though with the DB only doing a COALESCE
for the current approach -
- i pass in an array of timesheet IDs through the api
- i do a for loop inside the transaction looping over every timesheet ID
- i do join queries do get settings associated with an employee (using drizzle's 'with' function)
- do some calculations, then finally update the timesheet
is the for loop somehow causing the timeout?
i just realized i was doing a for loop inside a transaction
i do use an ORM (drizzle)