Re: Temporary tables under hot standby

On Thu, Apr 26, 2012 at 9:57 AM, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Thu, Apr 26, 2012 at 9:18 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Implementing a feature that *requires* those things is madness and
>> obscuring those crucial points is not balanced or fair.
>
> I think this whole discussion started the wrong way around. If the
> goal of implementing GTTs is to solve a need with replication then
> yes, it's kind of weird. But GTTs solve lots of problems with our
> existing implementation of temporary tables. Our existing temporary
> tables are really normal tables that just get cleaned up automatically
> but incur almost all the overhead of a real table including needless
> heavyweight DDL in your OLTP application. It's a bad design and
> providing GTTs would be providing a nice feature that allows people to
> implement much better systems. As a side benefit they would be easy to
> support on a standby as well which would be a pretty nice feature.

+1 to all that.

> I do think storing local xids is a bit scary. It might be a dead-end
> if we ever want to support having persistent non-local objects in the
> standby database. We'll need some way to generate a separate xid space
> (along with other feature we don't need for GTTs of course) in that
> case and then the ability to store local xids and check snapshots
> against them would be kind of useless once we have that.

Yeah, I think the separate XID space thing is pretty complicated. On
the other hand, there are lots of reasons to think it would be cool if
Noah can pull it off, and I think his chances are better than most.
Aside from the fact that it would improve temp tables, there's the
possibility of doing an XID space per database, as Heikki mentioned,
and the possibility of having an unlogged XID space also, as I
mentioned. Whether any of these things are feasible and which ones
actually make sense is not totally clear, but I think it's completely
worthwhile for someone to do the legwork to figure it out.

I also think that the idea of having persistent non-local objects on a
Hot Standby database is likely a complete dead-end. By the time
you've managed to engineer that, it's not going to look much like Hot
Standby as we know it today any more. Perhaps we will get there
eventually, but I think logical replication is a more likely bet.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company