Do Hooks fire before transaction Commit?

[OscarVanL]

Hi,

I'm looking to use AfterInsertHook and AfterDeleteHook to provide real-time updates to clients when specific rows get added or removed. If any models.A row gets created or removed, then I want to send each client all models.A that exists in the database.

To do this, I use the hook as a trigger to get all models.As in the database.

However, probably due to some kind of data race, the result of my query for all model.As is missing the models.A that was inserted. It's as if the hook is occurring before the transaction is committed, and even though I'm using AfterInsertHook it's getting the state of the DB prior to insertion.

Any tips here? Is there a way to make the hook fire only after the transaction is committed to avoid this race? Thanks!

[stephenafamo]

SQLBoiler does not know if the queries are performed in a transaction or not.

The hooks are fired on the model itself after the insertion query is successful, which happens before the transaction is committed.

If you want to get all model.As from inside the transaction, you should do this using the boil.ContextExecutor which is passed to the hook. Since the exec will be the transaction executor (i.e. *sql.Tx), it will be able to fetch all the models, including those just inserted by the transaction.

[OscarVanL]

That makes a lot of sense and I now have this working as I wanted. Thank you for explaining this!

[OscarVanL]

One more question, how will hooks behave if I have multiple workers sharing the same Postgres database? (I.E. when my service horizontally scales and has multiple instances)

Will the hook only trigger the function for the worker that did the Insertion or Deletion, or will it trigger for all clients of that database?

Essentially, I want all workers of my server to call the hook function if any one of them makes a change to the model.

If SQLBoiler hooks won't work for this use-case, based on this stack overflow answer, using native Postgres triggers might be the correct approach here.

[stephenafamo]

The hooks are called by the same process that does the insert/update/delete e.t.c.
If you look in the generated code, it is right there in the method.

NOTE: SQLBoiler's hooks only operate on objects modified through SQLBoiler.

[OscarVanL]

The hooks are called by the same process that does the insert/update/delete e.t.c.

If you look in the generated code, it is right there in the method.

NOTE: SQLBoiler's hooks only operate on objects modified through SQLBoiler.

Thank you, that's what I expected 😀