[jumski]

Looks really well thought out and I will be testing it for sure!

I'm wondering how I would be able to regression-test functions in my project (pgflow [0]) - it tracks a graph of state machines modeled in few tables. State is mutated only by calling few exposed SQL functions (task queue worker does it).

Given I can't enforce everything I need with check constraints and I try to avoid triggers if possible, I opted for only using exposed SQL API [1] for setting up state in my pgTAP tests.

It is imperative and harder to maintain, like scripts you described in the article, but really my only option, as I want to have maximum confidence level.

Does RegreSQL support some kind of init scripts or I would need to wire it myself and just run RegreSQL after the proper state is set? Would lose the "run once and get report on everything" benefit then :-(

[0] https://pgflow.dev/ [1] https://github.com/pgflow-dev/pgflow/blob/main/pkgs/core/sup...

[radimm]

At this point it supports initialization through the fixtures systems (like inline SQL or SQL files). At the moment they have fixed order, which might lead to some limitations, but I'm already thinking about some pre/post test setup hooks and full schema handling as well (for full schema reloads).

Plus I have whole set of other requirements where RegreSQL suddenly seems to be a good solution.

And without sounding cliche - Thank you for the comment! This is exactly why I forced myself to go public and get this level of feedback.

[jumski]

No cliche at all - I'm in the same boat, showing my stuff online was way out of my comfort zone!

I was postponing proper, dedicated performance testing for some time and would really love to up my game in that regard.

I'm very happy with pgTAP approach of running stuff in transaction and rolling them back after the test - how this works in RegreSQL?

Would love to provide feedback and test the hooks when you will be working on them. I'm mostly interested in performance testing and my use case would be to run them on CI and compare to previous metrics stored somewhere in order to fail CI when performance regressions are introduced.

Happy to connect, got contact info in my profile.

[mickeyp]

IMO, you should not avoid triggers if it helps prevent invariants in your database. That is what they are especially good at preventing.

You can instruct postgres to raise exceptions using the same error code that constraints use: that way your clients do not need to know the difference.

[jumski]

Good point! For the SQL functions I mentioned, I'm comfortable without triggers - all mutations go through functions (no direct table access), and only start_flow is user-fac ing.

That said, there ARE other places that would benefit from triggers (aggregate counts). I've avoided them because they're hot paths and I was worried about perf impact - relyi ng on pgTAP coverage instead.

Your defense-in-depth argument is solid though. I should revisit this and benchmark whether the safety is worth the perf cost. Something like RegreSQL would come in handy