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

On Oct 15, 2014, at 12:15 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
>
> Perhaps a solution would be to document properly that analyze should not be run in the same transaction as schema changes.

If at all possible, we really need to be able to run it transactionally. The use case where we encountered this is when we deploy upgrades. We develop numerous changes for the next version of some of our software products (which share a single database), and our deploy process consolidates the database changes required for each change into a single transaction. If this transaction fails for any reason, the database needs to go back to the state it was originally with no changes whatsoever, and it should not complete until everything including the analyzes are done. One common type of change is to reload the data of a table by truncating it and then COPYing in the new data. In the past analyzes did not happen upon table data reloads, and this would result in terrible application behavior after deploys for several hours until auto-analyze would finally catch up. Adding analyze after each data reload within the deploy process is what eliminated this problem and kept us from having unpredictable performance.

In theory we could make everything but the analyzes run in a transaction and then execute the analyzes afterwards, and not consider the deploy done until they finish, but there is value in having every deploy being exactly one all-or-nothing transaction.

Best wishes,
--
Casey Allen Shobe