Re: Documenting when to retry on serialization failure

On Thu, Dec 9, 2021 at 7:43 AM Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> I had a conversation with Kevin Grittner about retry some years back
> and it seemed clear that the application should re-execute application
> logic from the beginning, rather than just slavishly re-execute the
> same SQL. But that is not documented either.

Yeah, that would be good to mention somehow.

> Is *automatic* retry possible? In all cases? None? Or maybe Some?
>
> ISTM that we can't retry anything where a transaction has replied to a
> user and then the user issued a subsequent SQL statement, since we
> have no idea whether the subsequent SQL was influenced by the initial
> reply.

I agree.

> But what about the case of a single statement transaction? Can we just
> re-execute then? I guess if it didn't run anything other than
> IMMUTABLE functions then it should be OK, assuming the inputs
> themselves were immutable, which we've no way for the user to declare.
> Could we allow a user-defined auto_retry parameter?

I suppose in theory a user-defined parameter is possible, but I think
it's fundamentally better for this to be managed on the application
side. Even if the transaction is a single query, we don't know how
expensive that query is, and it's at least marginally possible that
the user might care about that. For example, if the user has set a
10-minute timeout someplace, and the query fails after 8 minutes, they
may want to retry. But if we retry automatically then they might hit
their timeout, or just be confused about why things are taking so
long. And they can always decide not to retry after all, but give up,
save it for a less busy period, or whatever.

> We don't mention that a transaction might just repeatedly fail either.

True. I think that's another good argument against an auto-retry system.

The main thing that worries me about an auto-retry system is something
else: I think it would rarely be applicable, and people would try to
apply it to situations where it won't actually work properly. I
believe most users who need to retry transactions that fail due to
serialization problems will need some real application logic to make
sure that they do the right thing. People with single-statement
transactions that can be blindly retried probably aren't using higher
isolation levels anyway, and probably won't have many failures even if
they are. SSI is really for sophisticated applications, and I think
trying to make it "just work" for people with dumb applications will,
well, just not work.

--
Robert Haas
EDB: http://www.enterprisedb.com