Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Subtransactions Considered Harmful

postgres.ai

Post reply on HN
Print view View original

31–40 of 43 posts

Re: PostgreSQL Subtransactions Considered Harmful

#31

Post by ltbarcly3 »

samokhvalov wrote: post #24
ltbarcly3 wrote: post #22

Using subtransactions appropriately is unlikely to trigger any noticeable performance differences, any more than 'having less transactions' overall will. When doing multiple steps that need to track external state, subtransactions can greatly simplify the schema you need as well as make the database interactions more efficient. You need to safely record progress at each step, and you can't afford to lose previous ste…

What does "appropriately" mean in this context?

Not automatically inserting subtransactions that aren't necessary, especially when you aren't setting your application up to ever actually use them, as a side effect of something else. Like Django's transaction.atomic(), which at the top nesting level starts and commits a transaction, but which any dynamically nested calls start and commit sub transactions. This is almost never what anybody wants, I can count on one hand the number of times I've seen code that handles failed sub transactions and continues the overall transaction, and which most programmers don't even know how it works or what it does.

Re: PostgreSQL Subtransactions Considered Harmful

#32

Post by gurjeet »

jakswa wrote: post #26

Unique constraints roll your transaction back. Put your `INSERT` in a nested transaction and you can fall back to `SELECT`, and keep going without throwing away previous work. In multi-table, write-heavy workloads, this insert-or-select path begs for nested transactions so you can lean on uniqueness constraints while keeping your transaction afloat. So far I'm not convinced to abandon that pattern by this article, bu…

If that's what you want, you can happily use Postgres' UPSERT implementation, and abandon subtransactions very easily.

https://www.postgresql.org/docs/current/sql-insert.html#SQL-...

Re: PostgreSQL Subtransactions Considered Harmful

#33

Post by SPBS »

ltbarcly3 wrote: post #22

Using subtransactions appropriately is unlikely to trigger any noticeable performance differences, any more than 'having less transactions' overall will. When doing multiple steps that need to track external state, subtransactions can greatly simplify the schema you need as well as make the database interactions more efficient. You need to safely record progress at each step, and you can't afford to lose previous ste…

> When doing multiple steps that need to track external state

Do you have an example of this? Many explanations for SAVEPOINT say it is useful when 'recalculation is deemed too expensive' but I cannot come up with a satisfactory example where this is truly the case.

Re: PostgreSQL Subtransactions Considered Harmful

#34

Post by jakswa »

gurjeet wrote: post #32
jakswa wrote: post #26

Unique constraints roll your transaction back. Put your `INSERT` in a nested transaction and you can fall back to `SELECT`, and keep going without throwing away previous work. In multi-table, write-heavy workloads, this insert-or-select path begs for nested transactions so you can lean on uniqueness constraints while keeping your transaction afloat. So far I'm not convinced to abandon that pattern by this article, bu…

If that's what you want, you can happily use Postgres' UPSERT implementation, and abandon subtransactions very easily. https://www.postgresql.org/docs/current/sql-insert.html#SQL-...

It's a good idea to revisit, but I think I need SELSERT or something. Are you saying the `DO NOTHING` clause can still result in a row being returned with `RETURNING *`? My impression is that it can't but I'd be excited to be wrong!

edit: Ah, I bet the implicit suggestion was to do `INSERT ... ON CONFLICT DO NOTHING RETURNING *` and if no row was returned, you hope to know/guess the conflict and do an extra `SELECT`. I'll think more on this, thanks.

Re: PostgreSQL Subtransactions Considered Harmful

#35

Post by cakoose »

samokhvalov wrote: post #25
vvern wrote: post #18

cockroachdb implements subtransactions and I don't think any of these issues apply to cockroach's implementation.

They have an interesting section, "Beware: don't (over) use nested transactions", in their blog post: https://www.cockroachlabs.com/blog/nested-transactions-in-co... > ...we do not recommend their use in new applications. > The reality is that nested transactions are a product of the early days of software engineering, in the 1990s, back when systems were tightly coupled and the Internet and the Cloud were not yet ve…

Does anybody here understand the argument they're making? The language is vague and most examples seem to apply to un-nested transactions as well.

> Additionally, nested transactions can amplify performance anomalies...

This is a function of a transaction's duration and volume of writes, right? Does it matter whether there is nesting?

> It is not just the database state that must be rolled back; all the possible side effects performed by the components holding the transaction must also be rolled back.

Doesn't this apply to un-nested transactions as well? Having external API calls run "within" a DB transaction is common source of inconsistency.

Re: PostgreSQL Subtransactions Considered Harmful

#36

Post by spencercw »

jrockway wrote: post #8

I agree with this. I think we get baited into using subtransactions by how we structure our code. Each function feels like a transaction -- it gets its own local variables, and if it fails, it doesn't have any effect on the rest of the program. (Not strictly true, of course, I'm sure some failing functions modify global state, or their receiver.) We then mindlessly copy that to our database code -- each mutation func…

I do basically the same thing. If a method tries to start a transaction when a transaction is already open then it's immediately rejected.

I don't really like automatic transaction joining behaviour because it makes it hard to reason about the application behaviour. Will this transactional method commit when it returns? It's impossible to tell without looking at who's calling it.

It also encourages annoying behaviour like, oh this method uses the database, better make it @Transactional.

Re: PostgreSQL Subtransactions Considered Harmful

#37

Post by crateless »

jakswa wrote: post #34
gurjeet wrote: post #32

Earlier quoted context omitted.

If that's what you want, you can happily use Postgres' UPSERT implementation, and abandon subtransactions very easily. https://www.postgresql.org/docs/current/sql-insert.html#SQL-...

It's a good idea to revisit, but I think I need SELSERT or something. Are you saying the `DO NOTHING` clause can still result in a row being returned with `RETURNING *`? My impression is that it can't but I'd be excited to be wrong! edit: Ah, I bet the implicit suggestion was to do `INSERT ... ON CONFLICT DO NOTHING RETURNING *` and if no row was returned, you hope to know/guess the conflict and do an extra `SELECT`.…

> The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

Therefore you can do DO UPDATE SET table_name.column = Excluded.column WHERE table_name.column IS DISTINCT FROM Excluded.column RETURNING *

Re: PostgreSQL Subtransactions Considered Harmful

#38

Post by jakswa »

crateless wrote: post #37
jakswa wrote: post #34

Earlier quoted context omitted.

It's a good idea to revisit, but I think I need SELSERT or something. Are you saying the `DO NOTHING` clause can still result in a row being returned with `RETURNING *`? My impression is that it can't but I'd be excited to be wrong! edit: Ah, I bet the implicit suggestion was to do `INSERT ... ON CONFLICT DO NOTHING RETURNING *` and if no row was returned, you hope to know/guess the conflict and do an extra `SELECT`.…

> The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table. Therefore you can do DO UPDATE SET table_name.column = Excluded.column WHERE table_name.column IS DISTINCT FROM Excluded.column RETURNING *

Am I right to interpret this as "do a non-changing UPDATE so that you get a returned row"? It's another great idea I hadn't considered, thank you. I'm worried this causes the row to get re-written on disk (an actual UPDATE, even though values aren't changing). That could be a moot concern, I'll dig more.

Re: PostgreSQL Subtransactions Considered Harmful

#39

Post by SPBS »

> It is recommended to learn if subtransactions are used in your systems. If they are, it does not immediately mean that they need to be eliminated – it all depends on the risks of your particular case. Evaluating such risks is a complex task that requires deep Postgres expertise, so if you need such an analysis, consider hiring a PostgreSQL expert.

It is quite disheartening that this essentially echoes this quote from the related blog post https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-...:

> It is entirely possible to operate Postgres safely and with high performance and throughput at scale … but essentially the only way to do it is to have ready access to deep PostgreSQL experience and expertise on your team; people who Just Know where the landmines are because they’ve seen them before.

And https://blog.nelhage.com/post/some-opinionated-sql-takes/:

> My personal choice: MySQL

> The devil is in the details, but for me, as a default, and despite all these pet peeves, I would start with MySQL.

> As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it slightly wrong can utterly tank your performance or availability. In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.

Why should you need expert consultation in order to use core features, when other databases "just work" without all these caveats? Transaction ID exhaustion and VACUUM tuning comes to mind here.

Re: PostgreSQL Subtransactions Considered Harmful

#40

Post by akvadrako »

unportant wrote: post #29
cakoose wrote: post #17

Earlier quoted context omitted.

The original was Dijkstra's "Go To Statement Considered Harmful". Many considered that title unnecessarily provocative, but it ended up creating a new CS/programming meme. Now, I see "Considered Harmful" as just a concise way of alluding to that style of article, basically saying "here are the not-well-known downsides of a commonly-used thing". Many "Considered Harmful" articles do not live up to that promise, but I…

The funny thing is that Dijkstra named this article "A Case Against the Goto Statement". ACM editor didn't find that clickbaity enough and changed the title.

That original title is so much better. If articles like this would use it instead of the clickbait one it would give them more credability.
Post reply on HN