Re: Forcing current WAL file to be archived

On Fri, 2006-08-11 at 08:04 +0100, Simon Riggs wrote:
> On Thu, 2006-08-10 at 08:57 -0400, Tom Lane wrote:
>
> > Anyway, after further thought I've concluded that we really should
> > supply something that returns the Insert pointer, as this would be
> > useful for debugging and system-monitoring purposes. It's clear however
> > that we also need something that returns the Write pointer, as that's
> > what's needed for partial log-shipping.
>
> > So my vote is for two
> > functions, both read-only (and hence not superuser-only).
>
> Thats probably the most important consideration.
>
> > Not sure
> > what to name them exactly.
>
> pg_current_xlog_location() - gives the write pointer i.e. the offset up
> to which you can read() the xlog file and trust what it tells you
>
> pg_current_wal_insert_pointer() - gives the insert pointer :-)
>
> Named sufficiently differently that there is no confusion between them.

Patch implementing the above attached.

Sample execution, with commentary at bottom.

postgres=# select pg_current_wal_insert_pointer(),
pg_current_xlog_location();
pg_current_wal_insert_pointer | pg_current_xlog_location
-------------------------------+--------------------------
0/3A0824 | 0/3A0824
(1 row)

postgres=# begin;insert into blah values (1);
BEGIN
INSERT 0 1

postgres=# select pg_current_wal_insert_pointer(),
pg_current_xlog_location();
pg_current_wal_insert_pointer | pg_current_xlog_location
-------------------------------+--------------------------
0/3A085C | 0/3A0824
(1 row)

postgres=# insert into blah values (1);
INSERT 0 1

postgres=# select pg_current_wal_insert_pointer(),
pg_current_xlog_location();
pg_current_wal_insert_pointer | pg_current_xlog_location
-------------------------------+--------------------------
0/3A0894 | 0/3A0824
(1 row)

postgres=# commit;
COMMIT

postgres=# select pg_current_wal_insert_pointer(),
pg_current_xlog_location();
pg_current_wal_insert_pointer | pg_current_xlog_location
-------------------------------+--------------------------
0/3A08BC | 0/3A08BC
(1 row)

postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/3A091C
(1 row)

postgres=# select pg_current_wal_insert_pointer(),
pg_current_xlog_location();
pg_current_wal_insert_pointer | pg_current_xlog_location
-------------------------------+--------------------------
0/1000020 | 0/1000000
(1 row)

postgres=# select pg_xlogfile_name_offset(pg_current_xlog_location());
pg_xlogfile_name_offset
-----------------------------------
000000010000000000000000 16777216
(1 row)

The above shows that the Insert pointer is always ahead of or the same
as the Write pointer. After a log switch the current location is shown
as being in the next file, though the current filename still shows as
the previous filename (since there has been no write activity yet on the
new file) with an offset of 1 beyond EOF, to indicate that the whole
file may now be read.

pg_switch_xlog() shows the next-to-be written byte in the file that we
have just switched out of, or the current location if we just performed
a log switch. So the following sequence does *not* show there is an
error in the returned pointer values.

postgres=# insert into blah values (1);
INSERT 0 1
postgres=# select pg_xlogfile_name_offset(pg_current_xlog_location());
pg_xlogfile_name_offset
------------------------------
000000010000000000000001 372
(1 row)

postgres=# select pg_xlogfile_name_offset(pg_switch_xlog());
pg_xlogfile_name_offset
------------------------------
000000010000000000000001 400
(1 row)

...a log switch was performed

postgres=# select pg_xlogfile_name_offset(pg_switch_xlog());
pg_xlogfile_name_offset
-----------------------------------
000000010000000000000001 16777216
(1 row)

...a log switch was *not* performed, since we're already at EOF

I've not taken up Jim Nasby's suggestion to make this an SRF with
multiple return rows/columns since that much complexity isn't justified
IMHO.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com