Re: Postgresql 9.1 pg_last_xact_replay_timestamp limitations

On Fri, Dec 10, 2010 at 1:24 AM, Gabi Julien <gabi(dot)julien(at)broadsign(dot)com> wrote:
> On Wednesday 08 December 2010 21:58:46 you wrote:
>> 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
>
>> We should return the timestamp of last valid checkpoint rather than NULL in that
>> case?
>
> Well, I think this behavior would be more appreciated by postgresql users in general. The case where the slave can be restarted after a clean shutdown is rare but we need to consider it nonetheless. In my case I implemented a custom function that reads the last returned timestamp from a new file on disk. This is not a perfect solution since the value returned might be older then the actual state of the replication but it's good enough for my needs.

The second question is; What should be returned when the server has been
started normally without recovery? NULL? The timestamp of last valid checkpoint?

The third question is; What should be returned while replaying WAL records which
exist between REDO starting point and checkpoint? In this case, it seems bad to
return the timestamp of the checkpoint whenever there is no replay transaction,
since the result timestamp would go back once at least one transaction has been
replayed before reaching the checkpoint record.

Regards,

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