Re: CLUSTER FREEZE

On Mon, Dec 23, 2013 at 6:53 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-12-22 20:45:02 -0500, Robert Haas wrote:
>> I suspect we ought to extend this to rewriting variants of ALTER TABLE
>> as well, but a little thought is needed there. ATRewriteTables()
>> appears to just call heap_insert() for each updated row, which if I'm
>> not mistaken is an MVCC violation - offhand, it seems like a
>> transaction with an older MVCC snapshot could access the table for
>> this first time after the rewriter commits, and fail to see rows which
>> would have appeared to be there before the rewrite. (I haven't
>> actually tested this, so watch me be wrong.) If we're OK with an MVCC
>> violation here, we could just pass HEAP_INSERT_FROZEN and have a
>> slightly different flavor of violation; if not, this needs some kind
>> of more extensive surgery quite apart from what we do about freezing.
>
> Yes, rewriting ALTER TABLEs certainly aren't MVCC safe. I thought that
> was documented, but apparently not.
> I am not sure it can be fixed easily using the tricks CLUSTER plays,
> there might be nasty edge-cases because of the changes in the column
> definition. Certainly not a trivial project.
>
> I think we should leave ALTER TABLE as a completely separate project and
> just improve CLUSTER for now. The practical impact of rewriting ALTER
> TABLEs not freezing is far smaller, because they are very seldomly
> performed in bigger databases.

OK, I have committed my patch to make CLUSTER and VACUUM FULL freeze
aggressively, and have left ALTER TABLE alone for now.

It would be nice to get to the point where a database-wide VACUUM FULL
would serve to bump datfrozenxid, so as to avoid having to give this
sort of advice:

http://www.postgresql.org/message-id/CA+Tgmobth=AQkwMWtCsQLAEnV59GT4g3oqPQS45CB+fvG9MxSA@mail.gmail.com

However, it doesn't, quite: a bare VACUUM FULL now bumps relfrozenxid
for every table in the database *except pg_class*. It does call
vac_update_datfrozenxid() afterwards, but that only helps if pg_class
is not among the things holding back datfrozenxid. I haven't dug into
this much yet, but I think it might be worth fixing.

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