[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Will using subtransactions will come back to bite me?
From:       Laurenz Albe <laurenz.albe () cybertec ! at>
Date:       2022-03-28 15:31:57
Message-ID: 0a977daf9fe13753b0d97e383804a4518ee6414d.camel () cybertec ! at
[Download RAW message or body]

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


[prev in list] [next in list] [prev in thread] [next in thread]