Re: Survey on backing up unlogged tables: help us with PostgreSQL development!

Steve Crawford <scrawford(at)pinpointresearch(dot)com> writes:
> 1. Would there be restrictions preventing a standard table from having a
> FK or other constraint that depends on an unlogged table? If not, it
> seems like there could be an unwanted ripple-effect from lost of the
> unlogged table.

I would assume that we should disallow an FK referencing an unlogged
table from a regular table. I don't know whether the current patch
covers that point, but if not it's an oversight.

> 2. Would it be possible to accidentally mix logged and unlogged tables
> in an inheritance chain? What would be the impact?

I don't see any logical problem there: some of the data in the
inheritance tree would be subject to loss on crash, other data not.
But the schema is all logged so no inconsistency arises.

> 3. If unlogged data is included in a dump (my vote is no), would this
> lead to inconsistent behavior between dumps taken from a master and
> dumps taken from a hot-standby?

Hmm, that is a really interesting point. You're right that a dump taken
from a standby slave could not possibly include such data, since for
lack of WAL it would never be propagated to the slave. I am not sure
whether that inconsistency is a sufficiently good reason to not dump the
data from the master, though. I think that in any case we are going to
want a pg_dump option to dump/not dump unlogged data --- the argument is
only about which behavior will be default.

I'm not sure that Robert's completely thought through the implications
of this patch for behavior on a slave, anyway. Given the sequence
* take base backup from running system (which will surely
include inconsistent data for unlogged tables)
* install base backup on slave
* run recovery, transitioning to hot standby
* go live
it's unclear to me where along the line the slave has an opportunity to
clean out its bogus images of the unlogged tables. But it had better do
so before opening up for hot standby queries, let alone going live.

> 4. Would it be reasonable for temporary-tables to be unlogged by default?

Temp tables already are, always have been, always will be, unlogged.
This patch is about attempting to bring that performance benefit of
a temp table to regular tables.

regards, tom lane