Re: BUG #11638: Transaction safety fails when constraints are dropped and analyze is done

Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-10-21 22:12:29 -0400, Tom Lane wrote:
>> I don't much care for this patch.  Aside from cosmetic issues like having
>> named the new argument backwards and failed to update the function header
>> comment that the patch largely invalidates, it seems to me to be likely
>> to have unforeseen side effects, in that there may now be assumptions
>> elsewhere that we don't force a pg_class update for this type of change.

> I'm unconvinced that that's a problem.

[ shrug... ]  The fact that you haven't thought of a problem doesn't mean
there is not one.  We hadn't thought of the current problem either.

> What's the problem you're suspecting?

If I could put my finger on something, I'd have pointed it out rather
than just handwaving :-(.  But the (theorized) sequence of decisions
that got us into this mess should convince you that switching between
transactional and nontransactional updates is not something to be
done lightly.

> Even if it doesn't arrise to the level of data corruption, I suspect in
> many cases updating the stats nontransactionally in an later aborted
> transaction will surprise some users. The normal reason for doing a
> ANALYZE in a transaction is that you changed the data dramatically.

Well, the pg_statistic updates *are* transactional.  What we're discussing
here is the reltuples/relpages fields, and it's worth thinking twice
before claiming that you want to roll such an update back.  If the
transaction rolls back that's not going to make physically added pages go
away, so its relpages value is unconditionally better than the old one.
In the simplest case of a transaction that's UPDATEd all/most rows of the
table, it'd be best to keep the reltuples/relpages updates because that
will correctly reflect the fact that the live tuple density is about half
what it used to be.  (A rollback would mean that a different half of the
tuples are live, but the ratio is still correct.)  If the transaction was
mostly deletes or mostly inserts, then its reltuples number will be
inaccurate if the transaction rolls back ... but the old relpages number
will be inaccurate too, so it's pretty hard to argue that one state is
better than the other.  Given the complete lack of user complaints in
this area, I'm disinclined to change the behavior more than we have to.

            regards, tom lane