Re: tracking commit timestamps

Fujii Masao wrote:
> On Tue, Nov 25, 2014 at 7:58 AM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
> >> And here is v10 which fixes conflicts with Heikki's WAL API changes (no
> >> changes otherwise).
> >
> > After some slight additional changes, here's v11, which I intend to
> > commit early tomorrow. The main change is moving the test module from
> > contrib to src/test/modules.
>
> When I specify the XID of the aborted transaction in pg_xact_commit_timestamp(),
> it always returns 2000-01-01 09:00:00+09. Is this intentional?

Well, when a transaction has not committed, nothing is written so on
reading we get all zeroes which corresponds to the timestamp you give.
So yeah, it is intentional. We could alternatively check pg_clog and
raise an error if the transaction is not marked either COMMITTED or
SUBCOMMITTED, but I'm not real sure there's much point.

The other option is to record a "commit" time for aborted transactions
too, but that doesn't seem very good either: first, this doesn't do
anything for crashed or for in-progress transactions; and second, how
does it make sense to have a "commit" time for a transaction that
doesn't actually commit?

> Can I check my understanding? Probably we cannot use this feature to calculate
> the actual replication lag by, for example, comparing the result of
> pg_last_committed_xact() in the master and that of
> pg_last_xact_replay_timestamp()
> in the standby. Because pg_last_xact_replay_timestamp() can return even
> the timestamp of aborted transaction, but pg_last_committed_xact()
> cannot. Right?

I don't think it's suited for that. I guess if you recorded the time
of the last transaction that actually committed, you can use that.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services