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.paquier@gmail.com> wrote:
>=20
> 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,
--=20
Casey Allen Shobe=