Postgresql 9.1 pg_last_xact_replay_timestamp limitations

Hi everyone,

I am very pleased to see the addition of the pg_last_xact_replay_timestamp function in Postgresql 9.1 since this, in combination with hot standby and WAL log streaming, will seriously boost the performance of our postgresql database cluster. pg_last_xact_replay_timestamp is important to us because the client application keeps a cache and makes queries with this structure:

select stuff from table_name where not_modified_since > $last_not_modified_since_value_we_gave_to_the_client;

This way the client application only gets recent changes. $last_not_modified_since_value_we_gave_to_the_client is simply "now()" on master databases. In case of queries made on read-only (hot standby) databases, pg_last_xact_replay_timestamp() will be used. However, pg_last_xact_replay_timestamp() returns null when the server is restarted until a new transaction is streamed to the hot standby server. It might take a long time before this happens. Because of this, we can't rely this function completely.

Since forcing an update on the master database is not a clean solution, another possibility would be to create a custom function that takes the value of pg_last_xact_replay_timestamp() and save it on disk. If the value is null (the server was restarted), we then read and return of last value stored on disk instead. Is there any better way? Also, is there any plans to make pg_last_xact_replay_timestamp() reliable even after a restart?

Thank you,
Gabi Julien