Обсуждение: timestamp of the last replayed transaction

Поиск
Список
Период
Сортировка

timestamp of the last replayed transaction

От
Fujii Masao
Дата:
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


Re: timestamp of the last replayed transaction

От
Dimitri Fontaine
Дата:
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


Re: timestamp of the last replayed transaction

От
Fujii Masao
Дата:
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


Re: timestamp of the last replayed transaction

От
Fujii Masao
Дата:
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

Вложения

Re: timestamp of the last replayed transaction

От
Robert Haas
Дата:
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


Re: timestamp of the last replayed transaction

От
Fujii Masao
Дата:
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


Re: timestamp of the last replayed transaction

От
Robert Haas
Дата:
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


Re: timestamp of the last replayed transaction

От
Fujii Masao
Дата:
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


Re: timestamp of the last replayed transaction

От
Josh Berkus
Дата:
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
 


Re: timestamp of the last replayed transaction

От
Fujii Masao
Дата:
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

Вложения

Re: timestamp of the last replayed transaction

От
Robert Haas
Дата:
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


Re: timestamp of the last replayed transaction

От
Fujii Masao
Дата:
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