[orthoxerox]

1. It's much easier to debug concurrency issues when you use SPs, but much harder to debug anything else. 2. At some point you will want to move some of your data into another system, and will have to pull the logic out into the application layer. 3. PL/pgSQL (or any other imperative SQL extension) isn't something you can find lots of devs on the market for. 4. Upgrades and rollbacks are much more painful and require downtime.

My team wrote a few critically important pieces of software that are running on Oracle, and here's why we did this:

1. Concurrency issues were the biggest pain point that we tried to avoid. We still had to fix a lot of bugs in the logic itself, and debugging them without unit tests was painful 2. We were tightly integrated with another system written in PL/SQL. When we started on v2, an independent solution, I moved almost all logic out of the database except for the critical synchronization logic. 3. We had a veteran team of PL/SQL devs in house. We still needed to get a subcontractor that wrote the API layer in Java, something PL/SQL isn't suited for at all. 4. Upgrades and rollbacks were a pain, especially after we had to move to a 24x7 SLA that left us with no upgrade window. Oracle has edition-based redefinition, but Postgres doesn't.

[j45]

A middle ground that has had some success is managing a queue in Postgres that falls out business or application logic in the app, whether it’s micro service or monolith.

[orthoxerox]

Yes, that's basically what we ended up doing: a queue of tasks in Postgres that a variable number of workers could access via a clever SP that encapsulated all inter-task conflicts and spat out the next task you were allowed to process.

[j45]

There are some good business process management tools that could manage that mapping instead of the clever SP, but you have me intrigued.

Are there any links that could be helpful to work through the programs and caveats of such a SP?

Part of it for me is creating a table that can store the process in the database to be able to traverse it reasonably.

[orthoxerox]

It was basically a more complicated version of this: https://stackoverflow.com/questions/68809885/how-to-select-f...

[j45]

Nice, thanks!