Re: Temporary tables under hot standby

On Wed, Apr 25, 2012 at 12:30 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Wed, Apr 25, 2012 at 5:19 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> Oh, we're talking about different things, and I'm slightly confused.
>>
>> Yes, we need to support ANALYZE; what we might not need to support, at
>> least initially, is every user of a global temp table having their own
>> SEPARATE copy of the table statistics.
>
> Yes, we are. Global Temp Tables won't solve the "Works on HS" problem,
> so we'd better decide fairly quickly which use case we are addressing,
> and why. ISTM Global Temp Tables is more an Oracle compatibility issue
> than a problem PostgreSQL users have.
>
> ...I have zero basis for deciding whether what you say about Global
> Temp Tables is useful or not.

Well, Noah presented a pretty good outline of how to make global temp
tables work under Hot Standby. As Noah already said, making regular
temporary tables work under Hot Standby is far more difficult. I
think he's right. I'd rather see us get global temp tables working
under HS than insist we have to have regular temp tables working under
HS and ultimately end up with nothing. Even getting global temp
tables working under HS is probably going to require an entire
development cycle, maybe two. So raising the bar still higher seems
rather self-defeating to me. Half a loaf is better than none.

In the interest of full disclosure, I freely admit that global
temporary tables would also be a neat Oracle compatibility feature,
and I do work for a company that sells Oracle compatibility products
based on PostgreSQL, so there are surely some reasons for me to like
that, but AFAICT they aren't all *that* heavily used by most Oracle
users either, which is why I haven't been able to justify doing this
project before now. The important point here as I see it is that
tables of any flavor require catalog entries, and creating and
destroying catalog entries on a standby server does not seem
tractable, so if we want to have writable tables of any flavor on Hot
Standby sometime in the next year or two, we should pick a design that
doesn't require that. What Noah has proposed seems to me to be by far
the simplest way of making that happen, so I think his design is
spot-on.

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