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
combinationwith 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
withthis 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
restarteduntil a new transaction is streamed to the hot standby server. It might take a long time before this happens.
Becauseof 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
functionthat takes the value of pg_last_xact_replay_timestamp() and save it on disk. If the value is null (the server
wasrestarted), we then read and return of last value stored on disk instead. Is there any better way? Also, is there
anyplans to make pg_last_xact_replay_timestamp() reliable even after a restart?
Thank you,
Gabi Julien