Re: pg_prepared_xact_status

On Mon, Oct 2, 2017 at 9:09 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sat, Sep 30, 2017 at 2:10 AM, konstantin knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> txid_status() also not always be able to return status of transaction (if wraparound happen).
>> But it is still considered as one of the key features of 10 (transaction traceability...).
>
> Not by me. It's a feature, though, for sure. It's also a LOT more
> stable than what you're proposing. Even on a busy system, it takes a
> while to go through 200 million transactions; you probably can't
> realistically do that in under an hour, and you'll probably raise the
> 200 million transaction limit if you're anywhere close to that rate.
> In practice, you'll almost always be able to look up transactions for
> several days, and often weeks or months. With what you're proposing
> here, the information could disappear nearly instantly. I can't see
> how that works out to a usable feature.

Also txid_status is way more performant as it requires only a clog
lookup. Going through potentially hundreds of WAL segments to get one
result status could take way longer than that. Even if network can
become easily the bottleneck when doing cross-node transaction
resolution, this would put too much load into the backends.
--
Michael