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 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
***************
*** 104,115 **** PostgreSQL documentation

<listitem>
<para>
! A safe value for the next multitransaction ID (<option>-m</>)
can be determined by looking for the numerically largest
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 for the 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</>)
can be 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 looking for 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 transaction log\n"));
! printf(_(" -m XID,OLDEST set next multitransaction ID and oldest value\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"));
--- 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