Re: Creation of temporary tables on read-only standby servers

On Mon, Oct 18, 2010 at 6:05 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> On Mon, Oct 18, 2010 at 1:27 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> It'd be kinda cool if we had it, but the work required to get there
>>> seems far out of proportion to the benefits ...
>>
>> I agree.  I think that's backing into the problem from the wrong end.
>> The limiting factor here is that we require the entire cluster to be
>> replicated.  If you could replicate individual tables/schemas, then
>> this problem disappears.  Of course, that's not easy either, but if
>> you're going to solve a really hard problem, you might as well pick
>> that one.
>
> That seems like an orthogonal issue. You can have a replica with fewer
> objects -- which requires ignoring parts of the logs -- or more
> objects -- which requires being able to do local modifications and
> have local transaction states independent of the master. They're both
> potentially useful features and neither replaces the need for the
> other.
>
> Simon talked about filtering the transaction logs a while back and got
> a lot of pushback. But the more uses we put the slaves to the more it
> will come up. I figure we'll eventually do something for this though
> we might want more experience with the current setup before we dive
> into it.
>
> Adding extra objects in the slaves sounds massively harder. The idea
> of using temp tables might be a useful simplification because in the
> general case what you want is a separate set of xids with a separate
> clog and wal. That sounds like it would be a lot more complex.

Well, temp tables really want a separate set of XIDs with a separate
CLOG, too. Admittedly, they don't necessarily need WAL, if you can
make them work without catalog entries, but that's not so easy either.

> Another possibility though is to use the MED stuff. If we could
> support creating tables locally that were actually hosted by a
> separate database then you could do updates, inserts, etc against
> those tables since they're actually happening in the remote database.
> Alternately you set up your slave to be the target of remote tables in
> a read-write data warehouse database. But this approach has
> limitations of its own until our MED implementation is a lot more
> powerful than it is today.

Agreed on all points.

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