Re: timestamp of the last replayed transaction

Hi,

After 9.0 release, I've often heard that some people want to know
how far transactions have been replayed in the standby in timestamp
rather than LSN. So I'm thinking to include the function which returns
the timestamp of the last applied transaction (i.e., commit/abort WAL
record) in the core.

Name: pg_last_replay_xact_timestamp (better name?)
Return Type: timestamp with time zone

Thought?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
> After 9.0 release, I've often heard that some people want to know
> how far transactions have been replayed in the standby in timestamp
> rather than LSN. So I'm thinking to include the function which returns
> the timestamp of the last applied transaction (i.e., commit/abort WAL
> record) in the core.
>
> Name: pg_last_replay_xact_timestamp (better name?)
> Return Type: timestamp with time zone
>
> Thought?

How do you want to implement the tracking? Will it look like the
proposal in this thread:

http://archives.postgresql.org/pgsql-hackers/2010-05/msg01209.php

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Tue, Nov 2, 2010 at 10:38 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
>> After 9.0 release, I've often heard that some people want to know
>> how far transactions have been replayed in the standby in timestamp
>> rather than LSN. So I'm thinking to include the function which returns
>> the timestamp of the last applied transaction (i.e., commit/abort WAL
>> record) in the core.
>>
>> Name: pg_last_replay_xact_timestamp (better name?)
>> Return Type: timestamp with time zone
>>
>> Thought?
>
> How do you want to implement the tracking?

I'm thinking to just expose GetLatestXTime(), i.e., XLogCtl->recoveryLastXTime.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Thu, Nov 4, 2010 at 10:27 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Tue, Nov 2, 2010 at 10:38 PM, Dimitri Fontaine
> <dimitri(at)2ndquadrant(dot)fr> wrote:
>> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
>>> After 9.0 release, I've often heard that some people want to know
>>> how far transactions have been replayed in the standby in timestamp
>>> rather than LSN. So I'm thinking to include the function which returns
>>> the timestamp of the last applied transaction (i.e., commit/abort WAL
>>> record) in the core.
>>>
>>> Name: pg_last_replay_xact_timestamp (better name?)
>>> Return Type: timestamp with time zone
>>>
>>> Thought?
>>
>> How do you want to implement the tracking?
>
> I'm thinking to just expose GetLatestXTime(), i.e., XLogCtl->recoveryLastXTime.

I attached the patch.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Thu, Nov 4, 2010 at 9:00 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Thu, Nov 4, 2010 at 10:27 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Tue, Nov 2, 2010 at 10:38 PM, Dimitri Fontaine
>> <dimitri(at)2ndquadrant(dot)fr> wrote:
>>> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
>>>> After 9.0 release, I've often heard that some people want to know
>>>> how far transactions have been replayed in the standby in timestamp
>>>> rather than LSN. So I'm thinking to include the function which returns
>>>> the timestamp of the last applied transaction (i.e., commit/abort WAL
>>>> record) in the core.
>>>>
>>>> Name: pg_last_replay_xact_timestamp (better name?)
>>>> Return Type: timestamp with time zone
>>>>
>>>> Thought?
>>>
>>> How do you want to implement the tracking?
>>
>> I'm thinking to just expose GetLatestXTime(), i.e., XLogCtl->recoveryLastXTime.
>
> I attached the patch.

This looks good, but how about adding:

if (!RecoveryInProgress())
PG_RETURN_NULL();

Otherwise, if we're in Hot Standby mode for a while and then enter
normal running, wouldn't this still return a (stale) value?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sat, Nov 6, 2010 at 9:58 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> This looks good, but how about adding:
>
> if (!RecoveryInProgress())
>    PG_RETURN_NULL();
>
> Otherwise, if we're in Hot Standby mode for a while and then enter
> normal running, wouldn't this still return a (stale) value?

Yes, but isn't that (stale) value useful to check how far WAL records
have been replayed, *after failover*?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Mon, Nov 8, 2010 at 6:00 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Sat, Nov 6, 2010 at 9:58 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> This looks good, but how about adding:
>>
>> if (!RecoveryInProgress())
>>    PG_RETURN_NULL();
>>
>> Otherwise, if we're in Hot Standby mode for a while and then enter
>> normal running, wouldn't this still return a (stale) value?
>
> Yes, but isn't that (stale) value useful to check how far WAL records
> have been replayed, *after failover*?

Oh, OK. I guess that makes sense. One other question - should we say
pg_last_xact_replay_timestamp() rather than
pg_xact_last_replay_timestamp(), for consistency with
pg_last_xlog_replay_location()?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Nov 9, 2010 at 1:05 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Nov 8, 2010 at 6:00 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Sat, Nov 6, 2010 at 9:58 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> This looks good, but how about adding:
>>>
>>> if (!RecoveryInProgress())
>>>    PG_RETURN_NULL();
>>>
>>> Otherwise, if we're in Hot Standby mode for a while and then enter
>>> normal running, wouldn't this still return a (stale) value?
>>
>> Yes, but isn't that (stale) value useful to check how far WAL records
>> have been replayed, *after failover*?
>
> Oh, OK.  I guess that makes sense.  One other question - should we say
> pg_last_xact_replay_timestamp() rather than
> pg_xact_last_replay_timestamp(), for consistency with
> pg_last_xlog_replay_location()?

Yeah, pg_last_xact_replay_timestamp was my first idea. But, for me
(with poor English),
that sounded to return the timestamp of when the last transaction has
been replayed in
the standby, rather than the timestamp of the last replayed
transaction (i.e., when the
last replayed transaction has been committed in the master). So I
didn't choose that name.

But, pg_last_xact_replay_timestamp is more intuitive for many people?
If so, let's change
the name.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On 11/9/10 5:44 AM, Fujii Masao wrote:
> But, pg_last_xact_replay_timestamp is more intuitive for many people?
> If so, let's change
> the name.

*None* of these names are intuitive. So let's just go for consistency.

If you want an intuitive name, it would be:

pg_replication_log_timestamp()

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Wed, Nov 10, 2010 at 3:28 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 11/9/10 5:44 AM, Fujii Masao wrote:
>> But, pg_last_xact_replay_timestamp is more intuitive for many people?
>> If so, let's change
>> the name.
>
> *None* of these names are intuitive.  So let's just go for consistency.

OK. I changed the name to pg_last_xact_replay_timestamp.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Tue, Nov 9, 2010 at 8:55 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Wed, Nov 10, 2010 at 3:28 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> On 11/9/10 5:44 AM, Fujii Masao wrote:
>>> But, pg_last_xact_replay_timestamp is more intuitive for many people?
>>> If so, let's change
>>> the name.
>>
>> *None* of these names are intuitive.  So let's just go for consistency.
>
> OK. I changed the name to pg_last_xact_replay_timestamp.

Committed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Nov 10, 2010 at 12:57 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Nov 9, 2010 at 8:55 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Wed, Nov 10, 2010 at 3:28 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> On 11/9/10 5:44 AM, Fujii Masao wrote:
>>>> But, pg_last_xact_replay_timestamp is more intuitive for many people?
>>>> If so, let's change
>>>> the name.
>>>
>>> *None* of these names are intuitive.  So let's just go for consistency.
>>
>> OK. I changed the name to pg_last_xact_replay_timestamp.
>
> Committed.

Thanks!

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center