1. Name and conformance with standard of this feature - because we doesn't support modules, a mapping ANSI -> PG should not be trivial
2. Implementation
I see three possible ways how to implement it:
2.a - using on demand created temp tables - most simple solution, but doesn't help with catalogue bloating
2.b - using unlogged tables for holding statistics, relfilenode, and all necessary data
3.c - store ephemeral metadata only in memory without MVCC
With 2.a, essentially, we are defining a global definition of a temp table, but the actual per session objects still follow the same rules are our current temp tables do?
2.b seems like a lot of new data, and if we are defining new paths for e.g. statistics for global temp tables, we might end up adding new logic in planner to use those tables. I am not seeing how this will work.
Could you elaborate a bit on 3.c please?
Something that really bothers me here, on a different note, is the catalog churn this could cause. Of course, you mentioned in as a point in your email, but I feel that 2.a's showstopper could be the massive catalog churn it causes. Maybe have a way to manage such tables without getting pg_class to bloat pretty quickly (I am assuming you do not mean metadata as the catalog metadata in 3.c).