On Fri, Oct 06, 2017 at 06:37:57PM +0200, Pavel Stehule wrote: > 2017-10-06 6:48 GMT+02:00 Nico Williams <nico@cryptonector.com>: > > On Fri, Oct 06, 2017 at 04:52:09AM +0200, Pavel Stehule wrote: > > > Current TEMP tables, if you do it for any session has pretty significant > > > overhead - with possible risk of performance lost (system catalog > > bloat). > > > > Because of the DDLs for them? > > yes - pg_attribute, pg_class, pg_stats are bloating - and when these tables > are bloated, then DDL is slow.
:( > > No, I want GLOBAL TEMP tables. > > me too :) - and lot of customer and users.
> I though about it, but I have other on my top priority. GLOBAL TEMP TABLE > is on 90% unlogged table. But few fields should be session based instead > shared persistent - statistics, rows in pg_class, filenode.
Unlogged tables don't provide isolation between sessions the way temp tables do, so I don't see the connection.
But the necessary components (temp heaps and such) are all there, and I suspect a PoC could be done fairly quickly. But there are some subtleties like that FKs between GLOBAL TEMP and persistent tables must not be allowed (in either direction), so a complete implementation will take significant work.
The work looks like:
- add syntax (trivial)
- add new kind of persistence (lots of places to touch, but it's mostly mechanical)
- redirect all references to global temp table contents to temp heaps/indexes/whatever
- add logic to prevent FKs between persistent and global temp tables
- what else? > When we talked about this topic, there are two issues: > > a) probably not too hard issue - some internal data can be in session sys > cache. > > b) the session sys data should be visible on SQL level too (for some tools > and consistency) - it is hard task.
Can you expand on this?
If global temporary tables should be effective, then you have not have modify system catalogue after creating. But lot of processes requires it - ANALYZE, query planning.