Re: unlogged tables

On Sat, Nov 13, 2010 at 8:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Sat, Nov 13, 2010 at 7:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> That seems pretty gross.  If you're going to have to take a special
>>> action at startup anyway, why wouldn't it take the form of "truncate,
>>> then if it's an index, call the appropriate ambuild function"?
>
>> We've been over this ground before.  You can't read from non-shared
>> catalogs without binding to a database, and you must reinitialize all
>> unlogged relations before opening the database for a connection.  So
>> what you're proposing would involving launching a worker process for
>> each database in the cluster, having it do its thing and then exit,
>> and only after all that's done opening the database for connections.
>> That seems vastly more complex and less performant than what I've done
>> here.
>
> The fact that it's easy doesn't make it workable.  I would point out for
> starters that AMs might (do) put WAL locations and/or XIDs into indexes.
> Occasionally copying very old LSNs or XIDs back into active files seems
> pretty dangerous.

I haven't examined the GIST, GIN, or hash index code in detail so I am
not sure whether there are any hazards there; the btree case does not
seem to have any issues of this type.  Certainly, if an index AM puts
an XID into an empty index, that's gonna break.  I would consider that
a pretty odd thing to do, though.  An LSN seems less problematic since
the LSN space does not wrap; it should just look like an index that
was created a long time ago and never updated (which, in effect, it
is).

> Cleanup at first connection is something we've been avoiding for years,
> but maybe it's time to bite the bullet and do that?

There would certainly be some advantage to doing cleanup at first
connection even if we stick with the overall approach I've adopted
here, because you could avoid the overhead of cleaning up databases
that are never actually accessed.  There are a few downsides, though.
If you happened to leave a large amount of unlogged data on disk after
a crash, and then for some reason never connected to that database
again, you'd never reclaim that disk space; although perhaps you could
somehow arrange for autovacuum to clean up in that case.  Also, the
first connection to the offending database would need to lock out all
other connections until cleanup was completed, although I suppose
that's still better than doing the cleanup in the startup process as
is presently the case.  I guess the main problem is you'd need a
reliable and *inexpensive* way of identifying the first connection to
each database.  Paying something extra at startup time is better than
paying even a small penalty on each individual connection; goodness
knows our connections are expensive enough already.

> BTW, how will all of this activity look to a hot-standby slave?

The table will appear to exist but you'll get an error if you try to
access it.  I think at present it'll complain about the underying
files being missing; that could probably be fine-tuned if we're so
inclined.

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