Re: [HACKERS] [PATCH] A hook for session start - Mailing list pgsql-hackers

From Nico Williams
Subject Re: [HACKERS] [PATCH] A hook for session start
Date
Msg-id 20171006183927.GF1251@localhost
Whole thread Raw
In response to Re: [HACKERS] [PATCH] A hook for session start  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: [HACKERS] [PATCH] A hook for session start
List pgsql-hackers
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?

Nico
-- 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

pgsql-hackers by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple
Next
From: Pavel Stehule
Date:
Subject: Re: [HACKERS] [PATCH] A hook for session start