Re: Will using subtransactions will come back to bite me?

On Mon, 2022-03-28 at 01:49 -0700, Kannan Goundan wrote:
> I'm a backend web developer working on a pretty typical Postgres-backed web application.
> Each HTTP request is handled within a "SERIALIZABLE" transaction.
>
> For some requests, we need to perform a sub-operation (which might fail) and record the success/failure
> in the DB. Subtransactions offer a simple way to do that -- the overall request is still wrapped in a
> transaction, and the sub-operation would be wrapped a subtransaction.
>
> But a few things I've read online have made me wary of subtransactions:
> 1. https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
> 2. https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/
> 3. https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/
>
> But those articles seem to describe a use case that's more demanding than  mine. They talk about
> deeply-nested subtransactions, whereas I will only have 1 (maybe 2) levels of nesting within the top-level transaction.
>
> I'd appreciate any pointers on how to determine whether a particular use of subtransactions will
> run into the issues described in the linked articles.
>
> (I'm currently using Postgres 13.5 on GCP. Upgrading would require some effort, but I'm open to it!)

Typically, if you have only a few subtransactions per transaction, you are fine.

The second article also describes a different problem with replication and subtransactions.
You might want to check your use case if you hit that problem.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com