Re: Postgresql 9.1 pg_last_xact_replay_timestamp limitations - Mailing list pgsql-general
From | Gabi Julien |
---|---|
Subject | Re: Postgresql 9.1 pg_last_xact_replay_timestamp limitations |
Date | |
Msg-id | 201012081515.32995.gabi.julien@broadsign.com Whole thread Raw |
In response to | Re: Postgresql 9.1 pg_last_xact_replay_timestamp limitations (Gabi Julien <gabi.julien@broadsign.com>) |
Responses |
Re: Postgresql 9.1 pg_last_xact_replay_timestamp limitations
|
List | pgsql-general |
I just tried with postgresql 9.1alpha from http://www.enterprisedb.com/products/pgdevdownload.do (linux x86-32): postgres=# select pg_last_xact_replay_timestamp(), now() as not_modified_since; ERROR: function pg_last_xact_replay_timestamp() does not exist LINE 1: select pg_last_xact_replay_timestamp(), now() as not_modifie... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. A bug in the package? I guess they must have forgot to run genbki.sh and the function is missing from ./share/postgresql/postgres.bki.If I add the line manually and create the data folder after, it is still not working. A few precisions concerning my postgresql.conf I mentionned earlier: master as: ===== wal_level = hot_standby max_wal_senders = 30 wal_keep_segments = 100 ===== slave as: ===== hot_standby = on ===== On Wednesday 08 December 2010 11:37:51 Gabi Julien wrote: > 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 > >
pgsql-general by date: