Re: Postgresql 9.1 pg_last_xact_replay_timestamp limitations

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(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
>
>