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

41–43 of 43 posts

Re: PostgreSQL Subtransactions Considered Harmful

#41

Post by crateless »

jakswa wrote: post #38
crateless wrote: post #37

Earlier quoted context omitted.

> 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.

Yeah, that's a valid concern and perhaps you might find an answer here https://stackoverflow.com/questions/34708509/how-to-use-retu...

Re: PostgreSQL Subtransactions Considered Harmful

#42

Post by aeyes »

I believe problem 3 (MultiXact SLRU buffer cache lock contention) can occur even if you don't nest transactions in your code. We traced the problem back to foreign keys, when rows are updated Postgres internally does SELECT .. FOR KEY SHARE on referenced tables. This requires MultiXacts.

I feel that we miss benchmarks and observability tools for the low level locks which are essentially single threaded, if you have lock contention on them the system will grind to a complete halt.

If you can recompile, raising the size of this buffer cache can help.

Re: PostgreSQL Subtransactions Considered Harmful

#43

Post by emaginniss »

detaro wrote: post #13
ahachete wrote: post #9

Earlier quoted context omitted.

That they are hyperbolic, maybe. I disagree with the rest, I believe there are tons of information on both examples about why they are harmful. (edit, typo)

Not in the titles . "X is harmful" could instead be "X is unsafe", "X is often slow", "X has non-obvious corner cases", "X led us to maintenance hell", ... "Harmful" is less information and kind of suggests an overall judgement for all cases vs "things to consider" (which was e.g. also a criticism of the original letter, that it lead to rules like "never use goto" which forced people to do bad workarounds instead of…

They should start calling them "problematic"
Post reply on HN