Re: pg_resetxlog -m documentation not up to date

Peter Eisentraut wrote:

> It was introduced in 0ac5ad5134f2769ccbaefec73844f8504c4d6182 "Improve
> concurrency of foreign key locking", but there is also no explanation
> there.  It is apparently needed in pg_upgrade.
> 
> This should be documented, and I think the help line should be changed
> to something like
> 
>   -m XID,XID       set next and oldest multitransaction ID

I have come up with the attached patch.  Does this match your
expectations?  Also, I'm a bit annoyed that there's no easy "append N
zeroes to a number" recipe for multixact members such as there is for
pg_clog and multixact offsets.  Any suggestions on how to improve the
current recommendation?

(Also, I found out that if the last multixact/offsets file is not
exactly 32 pages long, the server will fail to create further mxacts
after following the recipe in docs; IIRC because it looks up the mxact
previous to the next one.)


*** a/doc/src/sgml/ref/pg_resetxlog.sgml
--- b/doc/src/sgml/ref/pg_resetxlog.sgml
***************
*** 27,33 **** PostgreSQL documentation    <arg choice="opt"><option>-o</option> <replaceable
class="parameter">oid</replaceable></arg>   <arg choice="opt"><option>-x</option> <replaceable
class="parameter">xid</replaceable></arg>   <arg choice="opt"><option>-e</option> <replaceable
class="parameter">xid_epoch</replaceable></arg>
!    <arg choice="opt"><option>-m</option> <replaceable class="parameter">mxid</replaceable></arg>    <arg
choice="opt"><option>-O</option><replaceable class="parameter">mxoff</replaceable></arg>    <arg
choice="opt"><option>-l</option><replaceable class="parameter">xlogfile</replaceable></arg>    <arg
choice="plain"><replaceable>datadir</replaceable></arg>
--- 27,33 ----    <arg choice="opt"><option>-o</option> <replaceable class="parameter">oid</replaceable></arg>    <arg
choice="opt"><option>-x</option><replaceable class="parameter">xid</replaceable></arg>    <arg
choice="opt"><option>-e</option><replaceable class="parameter">xid_epoch</replaceable></arg>
 
!    <arg choice="opt"><option>-m</option> <replaceable class="parameter">mxid</replaceable>,<replaceable
class="parameter">mxid</replaceable></arg>   <arg choice="opt"><option>-O</option> <replaceable
class="parameter">mxoff</replaceable></arg>   <arg choice="opt"><option>-l</option> <replaceable
class="parameter">xlogfile</replaceable></arg>   <arg choice="plain"><replaceable>datadir</replaceable></arg>
 
***************
*** 81,87 **** PostgreSQL documentation    <option>-m</>, <option>-O</>,    and <option>-l</>    options allow the next
OID,next transaction ID, next transaction ID's
 
!    epoch, next multitransaction ID, next multitransaction offset, and WAL    starting address values to be set
manually. These are only needed when    <command>pg_resetxlog</command> is unable to determine appropriate values    by
reading<filename>pg_control</>.  Safe values can be determined as
 
--- 81,87 ----    <option>-m</>, <option>-O</>,    and <option>-l</>    options allow the next OID, next transaction
ID,next transaction ID's
 
!    epoch, next and oldest multitransaction ID, next multitransaction offset, and WAL    starting address values to be
setmanually.  These are only needed when    <command>pg_resetxlog</command> is unable to determine appropriate values
by reading <filename>pg_control</>.  Safe values can be determined as
 
***************
*** 104,115 **** PostgreSQL documentation      <listitem>      <para>
!       A safe value for the next multitransaction ID (<option>-m</>)       can be determined by looking for the
numericallylargest       file name in the directory <filename>pg_multixact/offsets</> under the
 
!       data directory, adding one, and then multiplying by 65536.  As above,
!       the file names are in hexadecimal, so the easiest way to do this is to
!       specify the option value in hexadecimal and add four zeroes.      </para>     </listitem> 
--- 104,119 ----      <listitem>      <para>
!       A safe value for the next multitransaction ID (first part of <option>-m</>)       can be determined by looking
forthe numerically largest       file name in the directory <filename>pg_multixact/offsets</> under the
 
!       data directory, adding one, and then multiplying by 65536.
!       Conversely, a safe value for the oldest multitransaction ID (second part of
!       <option>-m</>)
!       can be determined by looking for the numerically smallest
!       file name in the same directory and multiplying by 65536.
!       As above, the file names are in hexadecimal, so the easiest way to do
!       this is to specify the option value in hexadecimal and append four zeroes.      </para>     </listitem> 
***************
*** 118,126 **** PostgreSQL documentation       A safe value for the next multitransaction offset (<option>-O</>)
canbe determined by looking for the numerically largest       file name in the directory
<filename>pg_multixact/members</>under the
 
!       data directory, adding one, and then multiplying by 65536.  As above,
!       the file names are in hexadecimal, so the easiest way to do this is to
!       specify the option value in hexadecimal and add four zeroes.      </para>     </listitem> 
--- 122,130 ----       A safe value for the next multitransaction offset (<option>-O</>)       can be determined by
lookingfor the numerically largest       file name in the directory <filename>pg_multixact/members</> under the
 
!       data directory, adding one, and then multiplying by 52352.  As above,
!       the file names are in hexadecimal.  There is no simple recipe such as
!       the ones above of appending zeroes.      </para>     </listitem> 
*** a/src/bin/pg_resetxlog/pg_resetxlog.c
--- b/src/bin/pg_resetxlog/pg_resetxlog.c
***************
*** 1036,1042 **** usage(void)     printf(_("  -e XIDEPOCH      set next transaction ID epoch\n"));     printf(_("  -f
            force update to be done\n"));     printf(_("  -l XLOGFILE      force minimum WAL starting location for new
transactionlog\n"));
 
!     printf(_("  -m XID,OLDEST    set next multitransaction ID and oldest value\n"));     printf(_("  -n
noupdate, just show extracted control values (for testing)\n"));     printf(_("  -o OID           set next OID\n"));
printf(_("  -O OFFSET        set next multitransaction offset\n"));
 
--- 1036,1042 ----     printf(_("  -e XIDEPOCH      set next transaction ID epoch\n"));     printf(_("  -f
force update to be done\n"));     printf(_("  -l XLOGFILE      force minimum WAL starting location for new transaction
log\n"));
!     printf(_("  -m XID,XID       set next and oldest multitransaction ID\n"));     printf(_("  -n               no
update,just show extracted control values (for testing)\n"));     printf(_("  -o OID           set next OID\n"));
printf(_(" -O OFFSET        set next multitransaction offset\n"));
 

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services