Re: Statement-level rollback

On Fri, Dec 7, 2018 at 2:57 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> The way I envision this to be used in practice is that it's enabled
> globally when the migration effort starts, then gradually disabled as
> parts of applications affected by these downsides are taught how to deal
> with the other behavior.

I'm not sure how realistic that is, honestly. I think if you add this
feature, people are going to turn it on and leave it on. But even if
you're right and people only use it temporarily, you'd really only
need to do one large bulk load to break the whole system. A
five-minute bulk load could burn through tens of millions of XIDs, and
some EDB customers, at least, do loads that last many hours.

Full disclosure: EDB has a feature like this and has for years, but it
uses a subtransaction per statement, not a subtransaction per row. It
is indeed useful to customers, but it also does cause its share of
problems. It is *very* easy to burn through a *lot* of XIDs this way,
even with a subtransaction per statement. For example, PL code in
function A can call PL code in function B which can call PL code in
function C, and you throw in a loop here and an EXCEPTION block there
and all kinds of fun ensues.

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