Обсуждение: 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.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
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
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
Вложения
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
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
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
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
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@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
Вложения
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
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