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
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.247
diff -c -r1.247 xlog.c
*** src/backend/access/transam/xlog.c 7 Aug 2006 16:57:56 -0000 1.247
--- src/backend/access/transam/xlog.c 15 Aug 2006 14:27:00 -0000
***************
*** 6336,6346 ****
--- 6336,6383 ----
/*
* Report the current WAL location (same format as pg_start_backup etc)
+ *
+ * This is the current Write pointer, so is useful for determining the
+ * current byte offset within a WAL file that has valid data written to it.
+ * Note that data written is not always committed yet, see XLogInsert()
*/
Datum
pg_current_xlog_location(PG_FUNCTION_ARGS)
{
text *result;
+ char location[MAXFNAMELEN];
+
+ /*
+ * Get the current end-of-WAL position by updating LogwrtResult
+ */
+ {
+ /* use volatile pointer to prevent code rearrangement */
+ volatile XLogCtlData *xlogctl = XLogCtl;
+
+ SpinLockAcquire(&xlogctl->info_lck);
+ LogwrtResult = xlogctl->LogwrtResult;
+ SpinLockRelease(&xlogctl->info_lck);
+ }
+
+ snprintf(location, sizeof(location), "%X/%X",
+ LogwrtResult.Write.xlogid, LogwrtResult.Write.xrecoff);
+
+ result = DatumGetTextP(DirectFunctionCall1(textin,
+ CStringGetDatum(location)));
+ PG_RETURN_TEXT_P(result);
+ }
+
+ /*
+ * Report the current WAL location (same format as pg_start_backup etc)
+ *
+ * This is the current Insert pointer. The name is deliberately chosen
+ * to be different from pg_current_xlog_location so people do not confuse
+ * the two functions. This function is mostly for debugging purposes.
+ */
+ Datum
+ pg_current_wal_insert_pointer(PG_FUNCTION_ARGS)
+ {
+ text *result;
XLogCtlInsert *Insert = &XLogCtl->Insert;
XLogRecPtr current_recptr;
char location[MAXFNAMELEN];
Index: src/include/access/xlog_internal.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/xlog_internal.h,v
retrieving revision 1.15
diff -c -r1.15 xlog_internal.h
*** src/include/access/xlog_internal.h 7 Aug 2006 16:57:57 -0000 1.15
--- src/include/access/xlog_internal.h 15 Aug 2006 14:27:01 -0000
***************
*** 244,249 ****
--- 244,250 ----
extern Datum pg_stop_backup(PG_FUNCTION_ARGS);
extern Datum pg_switch_xlog(PG_FUNCTION_ARGS);
extern Datum pg_current_xlog_location(PG_FUNCTION_ARGS);
+ extern Datum pg_current_wal_insert_pointer(PG_FUNCTION_ARGS);
extern Datum pg_xlogfile_name_offset(PG_FUNCTION_ARGS);
extern Datum pg_xlogfile_name(PG_FUNCTION_ARGS);
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.420
diff -c -r1.420 pg_proc.h
*** src/include/catalog/pg_proc.h 6 Aug 2006 03:53:44 -0000 1.420
--- src/include/catalog/pg_proc.h 15 Aug 2006 14:27:06 -0000
***************
*** 3105,3110 ****
--- 3105,3112 ----
DESCR("Switch to new xlog file");
DATA(insert OID = 2849 ( pg_current_xlog_location PGNSP PGUID 12 f f t f v 0 25 "" _null_ _null_ _null_ pg_current_xlog_location - _null_ ));
DESCR("current xlog location");
+ DATA(insert OID = 2852 ( pg_current_wal_insert_pointer PGNSP PGUID 12 f f t f v 0 25 "" _null_ _null_ _null_ pg_current_wal_insert_pointer - _null_ ));
+ DESCR("current wal insert pointer");
DATA(insert OID = 2850 ( pg_xlogfile_name_offset PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_ pg_xlogfile_name_offset - _null_ ));
DESCR("xlog filename and byte offset, given an xlog location");
DATA(insert OID = 2851 ( pg_xlogfile_name PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_ pg_xlogfile_name - _null_ ));
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
