On 2014-09-11 14:58:13 -0400, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
> > On 2014-09-11 13:41:37 -0400, Bruce Momjian wrote:
> >> And practically, how would we implement this for upgrades? Would we have
> >> pg_dump emit UNLOGGED for any hash creation command?
>
> > That seems like an almost trivial problem in comparison to the actual
> > difficulty of implementing UNLOGGED indexed on LOGGED tables.
>
> Yeah, we'd need to somehow mark such indexes as INVALID during crash
> recovery, which sounds pretty messy. We've stayed away from the whole
> idea of automatically-applied catalog updates during recovery, for
> good reasons.
Right. There were some ideas floating around, but nothing agreed upon.
I think we can stay away from doing anything *during* recovery, because
surely nothing UNLOGGED will ever be usable during it. So we should just
skip unlogged indexes during planning/executing, the same as !indisvalid/!indisready indexes
are skipped during planning/DML.
But obviously there has to be some way to mark them as invalid at the
end of recovery. I don't think we want to do a disk access during
planning to see if there's some special file system marker marking a
relation as reset everytime a unlogged index is involed in a plan.
There's enough ugly ways to deal with that, but a acceptable one ...
> > Yes, I
> > think forbidding unlogged hash tables + teaching pg_dump a heuristic to
> > treat any < 9.x hash index as unlogged would be ok.
>
> My own opinion, if we had the infrastructure, is that "CREATE INDEX
> ... USING hash" should result in (1) automatically adding the UNLOGGED
> attribute and (2) issuing a NOTICE or WARNING stating that we've done so.
> Throwing an error, when we do not do so today, is just being a nag.
> This approach also eliminates the need to do anything special in pg_dump,
> which is a good thing since we have no way to retroactively fix existing
> dump files.
I think that might be less pain in the short term, but will bite us
*and* our users afterwards. For one, it really really isn't the same
thing to have a unlogged index as a logged index. Any user using
UNLOGGED indexes will need a explicit way to trigger rebuilding them
after a crash-restart. They're not served well by us automatically
hiding that fact from them. For another I think we at some point we're
going to get hash indexes that are logged and then we'll have to
uninstall that heuristic which will just confuse users agin.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
