testing cvs HEAD - HS/SR - xlog timeline 0 pg_xlogfile_name_offset

I have two 9.0devel machines (*not* alpha but cvs as of 2010.02.19 22.48).

One primary, one slave.

In an attempt to keep track of standby progression (versus primary), I ran slave-side:

replicas=# select
pg_last_xlog_replay_location()
pg_xlogfile_name_offset(pg_last_xlog_replay_location())
, pg_last_xlog_receive_location()
, pg_xlogfile_name_offset(pg_last_xlog_receive_location())
;
pg_last_xlog_replay_location | pg_xlogfile_name_offset |
------------------------------+------------------------------------+-
E2/C012AD90 | (00000000000000E2000000C0,1224080) |

pg_last_xlog_receive_location | pg_xlogfile_name_offset
-------------------------------+-----------------------------------
E2/C012AD90 | (00000000000000E2000000C0,1224080)
(1 row)

These zero-timeline filenames look suspicious, no?
I understand timeline-count to normally start at 1, not 0?

The replication seems to be running fine (680 GB).

ps seems to report the right xlog filename (slave):

/var/data1/pg_stuff/pg_installations/pgsql.sr_hotslave/bin/postgres -D
/var/data1/pg_stuff/pg_installations/pgsql.sr_hotslave/data
\_ postgres: startup process recovering 00000001000000E2000000C0
\_ postgres: wal receiver process streaming E2/C012AE28
\_ postgres: writer process
\_ postgres: stats collector process

replicas=# select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.3, 64-bit
(1 row)

Is the filename that pg_xlogfile_name_offset( pg_last_xlog_(replay|receive)_location() ) reports a
bug, or expected as shown?

thanks,

Erik Rijkers

I should have added that (on the slave) all pg_xlog/ files have timeline 1
like: 00000001000000E2000000C0

On Wed, February 24, 2010 20:40, Erikruary 24, 2010 20:40, Er Rijkers wrote:
> I have two 9.0devel machines (*not* alpha but cvs as of 2010.02.19 22.48).
>
> One primary, one slave.
>
> In an attempt to keep track of standby progression (versus primary), I ran slave-side:
>
> replicas=# select
> pg_last_xlog_replay_location()
> pg_xlogfile_name_offset(pg_last_xlog_replay_location())
> , pg_last_xlog_receive_location()
> , pg_xlogfile_name_offset(pg_last_xlog_receive_location())
> ;
> pg_last_xlog_replay_location | pg_xlogfile_name_offset |
> ------------------------------+------------------------------------+-
> E2/C012AD90 | (00000000000000E2000000C0,1224080) |
>
> pg_last_xlog_receive_location | pg_xlogfile_name_offset
> -------------------------------+-----------------------------------
> E2/C012AD90 | (00000000000000E2000000C0,1224080)
> (1 row)
>
> These zero-timeline filenames look suspicious, no?
> I understand timeline-count to normally start at 1, not 0?
>
> The replication seems to be running fine (680 GB).
>
> ps seems to report the right xlog filename (slave):
>
> /var/data1/pg_stuff/pg_installations/pgsql.sr_hotslave/bin/postgres -D
> /var/data1/pg_stuff/pg_installations/pgsql.sr_hotslave/data
> \_ postgres: startup process recovering 00000001000000E2000000C0
> \_ postgres: wal receiver process streaming E2/C012AE28
> \_ postgres: writer process
> \_ postgres: stats collector process
>
>
>
> replicas=# select version();
> version
> ------------------------------------------------------------------------------------------
> PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.3, 64-bit
> (1 row)
>
>
> Is the filename that pg_xlogfile_name_offset( pg_last_xlog_(replay|receive)_location() ) reports a
> bug, or expected as shown?
>
>
> thanks,
>
>
> Erik Rijkers
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Wed, February 24, 2010 20:40, Erik Rijkers wrote:
>
>
> pg_last_xlog_receive_location | pg_xlogfile_name_offset
> -------------------------------+-----------------------------------
> E2/C012AD90 | (00000000000000E2000000C0,1224080)
> (1 row)
>
> These zero-timeline filenames look suspicious, no?

Sorry, I've only now noticed that this issue is already on the v9.0 TODO for Streaming Replication:

"pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name. Because a
backend cannot know the actual timeline which is related to the location.
"

http://archives.postgresql.org/message-id/3f0b79eb1001190135vd9f62f1sa7868abc1ea61d12@mail.gmail.com

so, nevermind..

Erik Rijkers

On Thu, Feb 25, 2010 at 9:31 AM, Erik Rijkers <er(at)xs4all(dot)nl> wrote:
> On Wed, February 24, 2010 20:40, Erik Rijkers wrote:
>>
>>
>>  pg_last_xlog_receive_location |      pg_xlogfile_name_offset
>> -------------------------------+-----------------------------------
>>  E2/C012AD90                   | (00000000000000E2000000C0,1224080)
>>  (1 row)
>>
>> These zero-timeline filenames look suspicious, no?
>
> Sorry, I've only now noticed that this issue is already on the v9.0 TODO for Streaming Replication:
>
> "pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name. Because a
> backend cannot know the actual timeline which is related to the location.
> "
>
> http://archives.postgresql.org/message-id/3f0b79eb1001190135vd9f62f1sa7868abc1ea61d12@mail.gmail.com
>
> so, nevermind..

Yeah, since I thought that the current behavior that you reported
would annoy many users, I added it to the TODO list.

Regards,

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

Fujii Masao wrote:
> On Thu, Feb 25, 2010 at 9:31 AM, Erik Rijkers <er(at)xs4all(dot)nl> wrote:
>> On Wed, February 24, 2010 20:40, Erik Rijkers wrote:
>>>
>>> pg_last_xlog_receive_location | pg_xlogfile_name_offset
>>> -------------------------------+-----------------------------------
>>> E2/C012AD90 | (00000000000000E2000000C0,1224080)
>>> (1 row)
>>>
>>> These zero-timeline filenames look suspicious, no?
>> Sorry, I've only now noticed that this issue is already on the v9.0 TODO for Streaming Replication:
>>
>> "pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name. Because a
>> backend cannot know the actual timeline which is related to the location.
>> "
>>
>> http://archives.postgresql.org/message-id/3f0b79eb1001190135vd9f62f1sa7868abc1ea61d12@mail.gmail.com
>>
>> so, nevermind..
>
> Yeah, since I thought that the current behavior that you reported
> would annoy many users, I added it to the TODO list.

Yeah, returning a filename with TLI 0 sure doesn't seem right.

A quick fix would be to just throw an error if you try to use
pg_xlog_filename() during hot standby. But there seems to be good
reasons to call pg_xlog_filename() during hot standby, given that both
of you ran into the same issue. What exactly were you trying to achieve
with it?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, Feb 25, 2010 at 5:10 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> A quick fix would be to just throw an error if you try to use
> pg_xlog_filename() during hot standby. But there seems to be good
> reasons to call pg_xlog_filename() during hot standby, given that both
> of you ran into the same issue. What exactly were you trying to achieve
> with it?

Nothing ;) Frankly I just found that problem while testing the
combination of SR and system administration functions. But on
second thought, calling pg_xlogfile_name() during HS seems useful
to remove old WAL files from the archive that is shared from
multiple standbys. In this case, '%r' in restore_command cannot
be used, so we would need to calculate the name of the WAL files
that are not required for the subsequent recovery yet by using
pg_xlogfile_name() and pg_controldata etc.

Regards,

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

Fujii Masao wrote:
> On Thu, Feb 25, 2010 at 5:10 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> A quick fix would be to just throw an error if you try to use
>> pg_xlog_filename() during hot standby. But there seems to be good
>> reasons to call pg_xlog_filename() during hot standby, given that both
>> of you ran into the same issue. What exactly were you trying to achieve
>> with it?
>
> Nothing ;) Frankly I just found that problem while testing the
> combination of SR and system administration functions. But on
> second thought, calling pg_xlogfile_name() during HS seems useful
> to remove old WAL files from the archive that is shared from
> multiple standbys. In this case, '%r' in restore_command cannot
> be used, so we would need to calculate the name of the WAL files
> that are not required for the subsequent recovery yet by using
> pg_xlogfile_name() and pg_controldata etc.

Yeah. The current pg_*_last_location() functions don't cut it though,
you need to retain logs back to the redo location of the last
restartpoint. That's what %r returns. Maybe we should add another function?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, Feb 25, 2010 at 7:22 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> Yeah. The current pg_*_last_location() functions don't cut it though,
> you need to retain logs back to the redo location of the last
> restartpoint. That's what %r returns. Maybe we should add another function?

+1

It would be useful if we can know that location via SQL rather
than pg_controldata. Which should that function return, filename
or location? If we'll prevent pg_xlogfile_name() from being called
during recovery according to your suggestion, it should return the
filename.

Regards,

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

On Thu, Feb 25, 2010 at 7:48 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Thu, Feb 25, 2010 at 7:22 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> Yeah. The current pg_*_last_location() functions don't cut it though,
>> you need to retain logs back to the redo location of the last
>> restartpoint. That's what %r returns. Maybe we should add another function?
>
> +1

The attached patch introduces new function 'pg_last_checkpoint_start_location'
(better name?) that reports the XLOG location where the last checkpoint or
restartpoint started (i.e., the REDO starting location). This would be useful
to truncate the archived files to just the minimum required for recovery.

Is it worth applying this patch?

Regards,

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