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(dot)julien(at)broadsign(dot)com> wrote:
> > 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.
>
> 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 result so maybe my tests are invalid although the patch looks simple enough. I'll try to reproduce using 9.1alpha. What I have 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