Re: Postgresql 9.1 pg_last_xact_replay_timestamp limitations

On Tuesday 07 December 2010 21:58:56 you wrote:
> On Wed, Dec 8, 2010 at 1:31 AM, Gabi Julien <gabi.julien@broadsign.com> wrote:
> > pg_last_xact_replay_timestamp() returns null when the server is restarted until a new transaction is streamed to
thehot standby server. It might take a long time before this happens. Because of this, we can't rely this function
completely.
>
> I couldn't reproduce this. Could you provide a self-contained test case?

I have merge the pg_last_xact_replay_timestamp path (7ba6e4f0e08bd7bdf4d12974ac1e37fb0459c97c) to 9.0 to get that
resultso maybe my tests are invalid although the patch looks simple enough. I'll try to reproduce using 9.1alpha. What
Ihave is 1 master and 1 slave. I do not use WAL log shipping, only streaming. Here's my recovery.conf on the slave: 

========
standby_mode = 'on'
primary_conninfo = 'host=master_host_name port=5432'
trigger_file = '/opt/postgresql/data/finish.replication'
========

The master postgresql.conf is fairly normal except for this:

=======
wal_level = hot_standby
=======

Same for the slave except for this:

=======
hot_standby = on
=======

Now if I do:

============
master# /etc/init.d/postgresql start

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

master# psql -hlocalhost my_db -c "create table trigger_transaction_shipping(a numeric); drop table
trigger_transaction_shipping;"
DROP TABLE

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:07:34.408892+00 | 2010-12-08 16:08:07.969588+00
============

Only after the first update from the master do I get my pg_last_xact_replay_timestamp timestamp.

Regards,
Gabi Julien