There is one more problem with global temporary tables for which I do not know good solution now: collecting statistic.
As far as each backend has its own data, generally them may need different query plans.
Right now if you perform "analyze table" in one backend, then it will affect plans in all backends.
It can be considered not as bug, but as feature if we assume that distribution if data in all backens is similar.
But if this assumption is not true, then it can be a problem.
Last point is probably the most difficult issue and I think about it years.
I have a experience with my customers so 99% of usage temp tables is without statistics - just with good information only about rows. Only few customers know so manual ANALYZE is necessary for temp tables (when it is really necessary).
Sharing meta data about global temporary tables can real problem - probably not about statistics, but surely about number of pages and number of rows.
There are two requirements:
a) we need some special meta data for any instance (per session) of global temporary table (row, pages, statistics, maybe multicolumn statistics, ...)
b) we would not to use persistent global catalogue (against catalogue bloating)
I see two possible solution:
1. hold these data only in memory in special buffers
2. hold these data in global temporary tables - it is similar to normal tables - we can use global temp tables for metadata like classic persistent tables are used for metadata of classic persistent tables. Next syscache can be enhanced to work with union of two system tables.
I prefer @2 because changes can be implemented on deeper level.
Sharing metadata for global temp tables (current state if I understand well) is good enough for develop stage, but It is hard to expect so it can work generally in production environment.
Regards
p.s. I am very happy so you are working on this topic. It is interesting and important problem.
Pavel
