Horton

Thread

  1. timestamp of the last replayed transaction

    Fujii Masao <masao.fujii@gmail.com> — 2010-11-02T11:38:28Z

    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

  2. Re: timestamp of the last replayed transaction

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2010-11-02T13:38:27Z

    Fujii Masao <masao.fujii@gmail.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

  3. Re: timestamp of the last replayed transaction

    Fujii Masao <masao.fujii@gmail.com> — 2010-11-04T01:27:33Z

    On Tue, Nov 2, 2010 at 10:38 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Fujii Masao <masao.fujii@gmail.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

  4. Re: timestamp of the last replayed transaction

    Fujii Masao <masao.fujii@gmail.com> — 2010-11-05T01:00:41Z

    On Thu, Nov 4, 2010 at 10:27 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Tue, Nov 2, 2010 at 10:38 PM, Dimitri Fontaine
> <dimitri@2ndquadrant.fr> wrote:
>> Fujii Masao <masao.fujii@gmail.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

  5. Re: timestamp of the last replayed transaction

    Robert Haas <robertmhaas@gmail.com> — 2010-11-06T00:58:31Z

    On Thu, Nov 4, 2010 at 9:00 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Thu, Nov 4, 2010 at 10:27 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> On Tue, Nov 2, 2010 at 10:38 PM, Dimitri Fontaine
>> <dimitri@2ndquadrant.fr> wrote:
>>> Fujii Masao <masao.fujii@gmail.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

  6. Re: timestamp of the last replayed transaction

    Fujii Masao <masao.fujii@gmail.com> — 2010-11-08T11:00:50Z

    On Sat, Nov 6, 2010 at 9:58 AM, Robert Haas <robertmhaas@gmail.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

  7. Re: timestamp of the last replayed transaction

    Robert Haas <robertmhaas@gmail.com> — 2010-11-08T16:05:28Z

    On Mon, Nov 8, 2010 at 6:00 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Sat, Nov 6, 2010 at 9:58 AM, Robert Haas <robertmhaas@gmail.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

  8. Re: timestamp of the last replayed transaction

    Fujii Masao <masao.fujii@gmail.com> — 2010-11-09T13:44:33Z

    On Tue, Nov 9, 2010 at 1:05 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Nov 8, 2010 at 6:00 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> On Sat, Nov 6, 2010 at 9:58 AM, Robert Haas <robertmhaas@gmail.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

  9. Re: timestamp of the last replayed transaction

    Josh Berkus <josh@agliodbs.com> — 2010-11-09T18:28:15Z

    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

  10. Re: timestamp of the last replayed transaction

    Fujii Masao <masao.fujii@gmail.com> — 2010-11-10T01:55:46Z

    On Wed, Nov 10, 2010 at 3:28 AM, Josh Berkus <josh@agliodbs.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

  11. Re: timestamp of the last replayed transaction

    Robert Haas <robertmhaas@gmail.com> — 2010-11-10T03:57:26Z

    On Tue, Nov 9, 2010 at 8:55 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Wed, Nov 10, 2010 at 3:28 AM, Josh Berkus <josh@agliodbs.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

  12. Re: timestamp of the last replayed transaction

    Fujii Masao <masao.fujii@gmail.com> — 2010-11-10T04:05:27Z

    On Wed, Nov 10, 2010 at 12:57 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Nov 9, 2010 at 8:55 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> On Wed, Nov 10, 2010 at 3:28 AM, Josh Berkus <josh@agliodbs.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