Re: Postgresql 9.1 pg_last_xact_replay_timestamp limitations

On Thu, Dec 9, 2010 at 1:37 AM, Gabi Julien <gabi(dot)julien(at)broadsign(dot)com> wrote:
> slave# /etc/init.d/postgresql start
> slave# psql -hlocalhost my_db -c "select pg_last_xact_replay_timestamp(), now() as not_modified_since;"
>  pg_last_xact_replay_timestamp |      not_modified_since
> -------------------------------+-------------------------------
>                               | 2010-12-08 16:06:09.920219+00

pg_last_xact_replay_timestamp returns the timestamp of last *replayed*
transaction.
So it returns NULL until at least one transaction has been replayed.

In your case, I guess that you started the master and standby from the
same initial
database cluster or clean-shutdowned one. In this case, since the standby has no
transaction to replay right after the startup, you got NULL until you
executed the
write query on the master.

We should return the timestamp of last valid checkpoint rather than NULL in that
case?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center