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
