Re: PROPOSAL: Fast temporary tables

As far as I know we are trying to kill two birds with one stone:
1. Reduce overhead of accessing temporary tables
2. Make it possible to create temporary tables on replica.

Replicas with hot-standby are widely used for running read-only OLAP queries.
But such queries usually stores intermediate results in temporary tables.
Unfortunately creating temporary table at read-only replica is impossible now.
So some customers do the following tricks: them create pool of file FDWs at master and then use them at replicas.
But IMHO it is ugly and inefficient hack.

Ideally we should be able to create temporary tables at replica, not affecting system catalog.
But there are a lot of problems: where it should be stores, how to assign XIDs to the ruples inserted in temporary table,...

Unfortunately, looks like there is no simple solution of the problem.
The 100% solution is multimaster (which we are currently developing), but it is completely different story...

On 03/01/2016 10:17 PM, Jim Nasby wrote:
> On 3/1/16 10:05 AM, Atri Sharma wrote:
>> Fair point, that means inventing a whole new OID generation structure..
>
> Generation is just the tip of the iceberg. You still need the equivalent to foreign keys (ie: pg_depend). While you would never have a permanent object depend on a temp object, the reverse certainly needs to be supported.
>
> If I were attempting to solve this at a SQL level, I'd be thinking about using table inheritance such that the permanent objects are stored in a permanent parent. New backends would create UNLOGGED children off of that parent. There would be a pid column
> that was always NULL in the parent, but populated in children. That means children could use their own local form of an OID. When a backend terminates you'd just truncate all it's tables.
>
> Actually translating that into relcache and everything else would be a serious amount of work.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company