2017-10-06 6:48 GMT+02:00 Nico Williams <n...@cryptonector.com>: > On Fri, Oct 06, 2017 at 04:52:09AM +0200, Pavel Stehule wrote: > > 2017-10-05 22:31 GMT+02:00 Nico Williams <n...@cryptonector.com>: > > > On Tue, Aug 01, 2017 at 03:36:23PM -0400, Peter Eisentraut wrote: > > > > On 7/21/17 13:14, Jim Mlodgenski wrote: > > > > > When I first saw this thread, my initial thought of a use case is > to > > > > > prepare some key application queries so they are there and ready > to go. > > > > > That would need to be before the ExecutorStart_hook or > > > > > ProcessUtility_hook if an app would just want to execute the > prepared > > > > > statement. > > > > > > > > Isn't that what the preprepare extension does already? > > > > > > more generic facility -> more useful > > > > > > My use case is to pre-create TEMP schema elements that VIEWs, > FUNCTIONs, > > > and TRIGGERs, might need. > > > > It is better to work on GLOBAL TEMP tables. > > I don't disagree. > > In fact, I was scoping out what it might take to do that just yesterday. > > I've too thoughts on that: either a new relpersistence kind that is very > similar to persistent, but which always uses temp heaps, or a modifier > for the persistent kind that says to use temp heaps. Either way it > looks like it should be fairly straightforward (but then, i've only > ever written one thing for PG, earlier this week, the ALWAYS DEFERRED > thing). > > > 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. > > So often creating local temp tables is antipattern (in Postgres) > > unfortunately. > > I do it plenty, but sometimes I use an UNLOGGED table with a txid column > in the PK set to txid_current(), then I clean up where I can. It'd be > nice to have COMMIT triggers for cleaning up such rows, among other > things. I've implemented that using DDL event triggers, but to perform > well it needs to be a native feature. > > > I am not sure, if we should to support this case more :( Probably is > > better, so it is hard to use local TEMP tables. > > No, I want GLOBAL TEMP tables. > me too :) - and lot of customer and users. There is a workaround - you can use a array instead temp tables in 50%. But it is not a solution in other 50%. 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. 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. Regards Pavel > Nico > -- >