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?
yes .. it means "global temp table is template for local temp table"
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.
The advantages of this method is transactional behave and moving some bloating content to specific smaller and unlogged tables.
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).
3.c is good protection against catalog bloating - on second hand - implementation will be probably more complex.
