Re: Postgresql 9.1 pg_last_xact_replay_timestamp limitations

On Fri, Dec 10, 2010 at 1:24 AM, Gabi Julien <gabi.julien@broadsign.com> wrote:

On Wednesday 08 December 2010 21:58:46 you wrote:

On Thu, Dec 9, 2010 at 1:37 AM, Gabi Julien <gabi.julien@broadsign.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

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?

NULL is fine is this server is not a hot standby.

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.

Not sure I get this but it should probably be the highest timestamp value. The thing is, from my perspective, I need to know how up to date the replica his. Perhaps we are trying to squeeze to much into "pg_last_xact_replay_timestamp()" and a new function "pg_replication_timestamp()" is needed that would accurately tell me a simple information: The time is was when the master db server was in the exact same state as this replication is right now.

Regards,

Regards,
Gabi Julien