News for hackers. Stuff that matters.
Re:Ask HN: How do you test SQL? by colanderman on Tuesday January 31, 2023 @08:57PM (#34605317)
One caution with PostgreSQL, CTEs under some circumstances (and in all circumstances, prior to PostgreSQL 12) act as optimization barriers. Specify `NOT MATERIALIZED` before the CTE definition to ensure that they are optimized same as a sub-SELECT would be.
Re:Ask HN: How do you test SQL? by nicoburns on Tuesday January 31, 2023 @09:22PM (#34605519)
This is a good point, although it’s worth noting that there are definitely cases where you want the sun table to be materialised (esp. when that table is small and referenced many times)
Re:Ask HN: How do you test SQL? by dragonwriter on Tuesday January 31, 2023 @09:29PM (#34605576)
Yeah, its definitely one of those things where it is worth reading the docs [0], and not just adopting an “always do it this way” rule of thumb.

[0] https://www.postgresql.org/docs/current/queries-with.html#id...

Re:Ask HN: How do you test SQL? by bolanyo on Wednesday February 01, 2023 @03:01AM (#34607754)
Is 'sun table' a particular concept here? A typo?
Re:Ask HN: How do you test SQL? by nicoburns on Wednesday February 01, 2023 @06:49AM (#34609272)
It's a typo. I was trying to write 'CTE table' and iOS "helpfully" autocorrected it.
Re:Ask HN: How do you test SQL? by timwis on Wednesday February 01, 2023 @01:46AM (#34607286)
Just to flag this behaviour changes in v12: https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
Re:Ask HN: How do you test SQL? by srcreigh on Wednesday February 01, 2023 @10:01AM (#34611366)
Not really. CTEs are still optimization barriers after v12 if they're referenced more than once. That blog post is confused, but the commit message in the post is clear enough.