3 ms·
At a slight tangent, I don't think I've ever used a temporary table without living to regret it. - Queries that perform complex operations involving the creati
by radiowave 10y ago
At a slight tangent, I don't think I've ever used a temporary table without living to regret it.
- Queries that perform complex operations involving the creation of temp tables cannot be joined onto themselves[1].
- Operations that create temp tables can never be used as part of the definition of a materialized view[1]. This is a permissions issue, but not one that can be resolved by altering permissions. It's baked in.
- Confusion over how to test for the existance of a temp table, "it's in the catalog but it's not in my session".
[1] - Without resorting to dblink shenanigans, or such like.
- mjevans 10y agoHave you had better experiences when you modify the design to use an UNLOGGED table and update such a table within a transaction? (I assume most of the use cases are as a cache for data that actually exists elsewhere)
- radiowave 10y agoYes. That's what I tend to end up replacing the temp tables with. It can be a bit fiddly to design, but I haven't had any operational problems arising from it. In some of my cases, the temporary data is some kind of projection of future stock requirements, arising from a user doing a "what if" operation. Since multiple users could be doing this, the replacement unlogged non-temporary table needs to be "multi-tennant", which involves an extra column (usually called "invocation_id"), and a sequence, such that each invocation of the operation gets a new invocation_id value, which it uses in all of the rows it then generates. The extra complexity is ensuring the invocation_id gets passed around to all of the functions that are operating on the data, including the clean-up when we're done with it.