Re: making an unlogged table logged

On Wed, Jan 5, 2011 at 3:58 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Hm- if the unlogged tables are being used as HTTP transient state storage, it would be handy to have that (admittedly non-essential) data on the standby when it becomes master, even if there are no guarantees surrounding the data beyond "it looked like this at some point". Since the tables are not writing WAL, would it be possible to allow for writing to unlogged tables on the standby to allow for out-of-band syncing? Otherwise, it seems the only alternative is to push changes to a separate database on the standby machine and then suck the data in when it becomes master.
>
> It would be useful (this would allow per-standby buffer tables, for that
> matter), but it would also be tremendously difficult.  Seems worthy of a
> TODO, though.

I think that's probably a dead end - just to take one example, if you
don't sync often enough, the standby might have transaction ID
wraparound problems. Autovacuum on the master will prevent that for
permanent tables, but not for an only-occasionally-updated copy of an
unlogged table. If what you want is a possibly-slightly-stale copy of
a fast table on the master, there are ways to engineer that using
technology we already have, such as asynchronous replication + Hot
Standby + synchronous_commit=off, or by using Slony. Admittedly, none
of these solutions are quite perfect, but it would probably be much
easier to improve those solutions or develop something completely new
rather than try to somehow go through the mammoth contortions that
would be needed to make it work with unlogged tables.

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