News for hackers. Stuff that matters.
Re:Ask HN: How do you test SQL? by paulmd on Tuesday January 31, 2023 @11:08PM (#34606222)
If your database supports it, unit tests are an absolutely ideal use-case for temporary tables or global temporary tables. A global temporary table can be defined with the same schema as the correct table and will "exist" for the purpose of view/CTE definitions, but any data inserted into the table will only ever be visible from that specific thread context. The rules depend but basically either it exists until the thread is closed, or until the session calls commit, but the semantics will (theoretically) be optimized for one thread and rapid inserts/clears.

If you build your unit tests so that a thread will not commit until it's finished, or clear it before/after, it's pretty ideal for that. You can feed in data for that test and it won't be visible anywhere else.

Potentially if you scripted your table creates you could add an additional rule that GLOBAL TEMPORARY TABLE gets added to any other tabledefs but only for unit tests. Or just update both in parallel.

The other useful use-case I've found for this is that you can do an unlimited-size "WHERE myId IN (:1, :2 ...)" by doing a GTT expressed as "INNER JOIN myId = myGtt.myid", and this has the advantage of not thrashing the query planner for every literal sqltext resulting from different lengths of myList (this will fill up your query cache!). Since it's one literal string it always hits the same query plan cache.

I am told this has problems in oracle's OCI when using PDBs, apparently GTTs will shit up the redo tablespace (WAL equivalent). But so do many many things, PDBs are apparently a much different and much less capable and incredibly immature implementation that seems essentially abandoned/not a focus from what I've been told. I was very surprised having originally written that code in regular Oracle and not having had problems but PDBs just aren't the same (right down to nulls being present in indexes!).

Re:Ask HN: How do you test SQL? by n0n0n4t0r on Tuesday January 31, 2023 @11:49PM (#34606512)
Off topic incoming (sorry )

I used this trick (join temporaryFoo instead of where foo in ...) in production fifteen years ago, using MySQL. The gain was really astonishing. Several instructions can be optimized using joins on specialty craft tables (I know of LIMIT for instance).

This is one of the worst drawbacks of orm everywhere: nobody even seems to think about those optimisations anymore.

Re:Ask HN: How do you test SQL? by paulmd on Tuesday January 31, 2023 @11:59PM (#34606570)
also, views can be defined as ORM objects/POJOs. They can be read-only, some views are "trivially-remappable" (if there is a 1:1 mapping from view columns to table columns) or even you can use INSTEAD OF INSERT/UPDATE triggers to take writes on that view and do something completely else with it. ;)

I've used that to do dumb shit like lever a table schema into an ORM mapping that it wasn't really designed for, that I had to maintain fallback compatibility conditions onto the tables.

views are really a db-level "interface" implementation that few people really exploit fully. Here is the definition, here is the implementation. And yes global temporary tables are such a cute cheat for unlimited-size query-specific data ;)

Re:Ask HN: How do you test SQL? by MrBuddyCasino on Wednesday February 01, 2023 @02:26AM (#34607537)
That is why ORMs have fallzout of fashion.