Monday, November 29, 2010

Comment on catalog tables

While building some catalogs queries I always forgot what some column is. At this point I need to access the documentation or (if I don't have Internet access) build the documentation in my laptop and check it out. Sometimes it is an annoying task. Thinking about this scenario I imagined why we don't have comments on catalogs? I started to research a easy lazy way to achieve this. I checked the source (catalogs.sgml) to investigate how I could extract such information. I noticed that each section (that starts with catalog-pg-) contains a catalog and the description is stored in a table (the first one). I decided to use the same infrastructure PostgreSQL uses to build the docs (DSSSL or XSL). Easy task? Not that easy.

DSSSL x XSL. I had never done any formatting in DSSSL; it is greek for me so I headed to XSL. I have already played with XSL some time ago; it wouldn't be that hard to extract those descriptions, would it?

First step was converting the SGML file to XML (XSL only works with XML). I used the osx (that is part of openjade). The catalogs.sgml file doesn't have a header (because it is part of the  PostgreSQL documentation that provides its header in a separate file postgres.sgml); I had to add the following 3 lines as header.

$ cat /path/to/header.sgml
<!doctype chapter PUBLIC "-//OASIS//DTD DocBook V4.2//EN" [
<!entity % standalone-ignore "IGNORE">
<!entity % standalone-include "INCLUDE"> ]>

Next step was run the osx tool to obtain the xml output. The verbose messages generated are just broken links for other documentation parts; it was safe to ignore them.

$ cat /path/to/header.sgml /path/to/catalogs.sgml | osx -x lower > /path/to/catalogs.tmp

At this point, I had the catalogs file in XML format. Looking at the generated file (catalogs.tmp), I noticed that some special characters (i.e. &, >, <) were not preserved. Fortunately, looking at the doc/src/sgml/Makefile I found this command:

$ perl -p -e 's/\[(amp|copy|egrave|gt|lt|mdash|nbsp|ouml|pi|quot|uuml) *\]/\&\1;/g;' \
> -e '$_ .= qq{<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN" "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd">\n} if $. == 1;' \
> < /path/to/catalogs.tmp > /path/to/catalogs.xml

It converts some entities like [foo  ] to &foo; and adds the DOCTYPE header at the second line.

Once I had the file in XML format, I started to investigate how to extract descriptions for COMMENT ON TABLE. I noticed that the first section (sect1 id="catalogs-overview") has a table with the information I was looking for. The following XSL script is capable of extracting the catalog table descriptions. (The only difficulty in this script was to build the template escape-string-sql to escape sql strings. This was necessary because the replace function is only available at XSL version 2.0; I was using version 1.0).

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0"
                xmlns="http://www.w3.org/TR/xhtml1/transitional"
                exclude-result-prefixes="#default">

<xsl:output method="text" encoding="utf-8"/>

<!-- escape SQL string -->
<xsl:template name="escape-string-sql">
 <xsl:param name="mystr"/>
 <xsl:choose>
  <!-- &quot;&apos;&quot; = "'" -->
  <xsl:when test="contains($mystr, &quot;&apos;&quot;)">
   <xsl:value-of select="substring-before($mystr, &quot;&apos;&quot;)"/>
   <!-- replacing ' (&apos;) with '' -->
   <xsl:text>&apos;&apos;</xsl:text>
   <xsl:call-template name="escape-string-sql">
    <xsl:with-param name="mystr" select="substring-after($mystr, &quot;&apos;&quot;)"/>
   </xsl:call-template>
  </xsl:when>
  <xsl:otherwise>
   <xsl:value-of select="$mystr"/>
  </xsl:otherwise>
 </xsl:choose>
</xsl:template>

<xsl:template match="/chapter[@id='catalogs']">

 <xsl:text>BEGIN;&#xa;</xsl:text>

 <xsl:text>&#xa;-- comment on catalog tables</xsl:text>

 <xsl:for-each select="sect1">

  <xsl:choose>
  <!-- comment on catalog tables -->
  <xsl:when test="self::sect1[@id='catalogs-overview']">
   <xsl:for-each select="table[@id='catalog-table']/tgroup/tbody/row">
COMMENT ON TABLE pg_catalog.<xsl:value-of select="entry[1]"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space(entry[2])"/></xsl:call-template>';</xsl:for-each>
   <xsl:text>&#xa;</xsl:text>
  </xsl:when>
  </xsl:choose>

 </xsl:for-each>

 <xsl:text>&#xa;END;&#xa;</xsl:text>

</xsl:template>

</xsl:stylesheet>

Let's check the output:

euler@harman:~$ xsltproc /path/to/only-tables.xsl /path/to/catalogs.xml
BEGIN;

-- comment on catalog tables
COMMENT ON TABLE pg_catalog.pg_aggregate IS 'aggregate functions';
COMMENT ON TABLE pg_catalog.pg_am IS 'index access methods';
COMMENT ON TABLE pg_catalog.pg_amop IS 'access method operators';
COMMENT ON TABLE pg_catalog.pg_amproc IS 'access method support procedures';
.
.
.
COMMENT ON TABLE pg_catalog.pg_ts_parser IS 'text search parsers';
COMMENT ON TABLE pg_catalog.pg_ts_template IS 'text search templates';
COMMENT ON TABLE pg_catalog.pg_type IS 'data types';
COMMENT ON TABLE pg_catalog.pg_user_mapping IS 'mappings of users to foreign servers';

END;

The next step was extracting descriptions from catalog tables (COMMENT ON COLUMNS). I observed that each section contains a catalog and this section is identified with catalog-pg-*. Inside each section the first table contains the information we want to extract. Those tables can have three or four columns. The following XSL script is capable of extracting the catalog column descriptions. It uses the template escape-string-sql to escape sql strings too. Also, the template build-comment-col centralizes the COMMENT ON COLUMN output.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0"
                xmlns="http://www.w3.org/TR/xhtml1/transitional"
                exclude-result-prefixes="#default">

<xsl:output method="text" encoding="utf-8"/>

<!-- escape SQL string -->
<xsl:template name="escape-string-sql">
 <xsl:param name="mystr"/>
 <xsl:choose>
  <!-- &quot;&apos;&quot; = "'" -->
  <xsl:when test="contains($mystr, &quot;&apos;&quot;)">
   <xsl:value-of select="substring-before($mystr, &quot;&apos;&quot;)"/>
   <!-- replacing ' (&apos;) with '' -->
   <xsl:text>&apos;&apos;</xsl:text>
   <xsl:call-template name="escape-string-sql">
    <xsl:with-param name="mystr" select="substring-after($mystr, &quot;&apos;&quot;)"/>
   </xsl:call-template>
  </xsl:when>
  <xsl:otherwise>
   <xsl:value-of select="$mystr"/>
  </xsl:otherwise>
 </xsl:choose>
</xsl:template>

<!-- build catalog column comment -->
<xsl:template name="build-comment-col">
 <xsl:param name="mytab"/>
 <xsl:param name="mycol"/>
 <xsl:param name="mystr"/>
COMMENT ON COLUMN pg_catalog.<xsl:value-of select="$mytab"/>.<xsl:value-of select="$mycol"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space($mystr)"/></xsl:call-template>';
</xsl:template>

<xsl:template match="/chapter[@id='catalogs']">

 <xsl:text>BEGIN;&#xa;</xsl:text>

 <xsl:for-each select="sect1">

  <xsl:choose>
  <!-- comment on catalog columns -->
  <xsl:when test="contains(self::sect1/@id,'catalog-pg-')">
   <xsl:text>&#xa;-- comment on </xsl:text><xsl:value-of select="table/title/structname"/><xsl:text> columns</xsl:text>

   <xsl:variable name="tab" select="table/title/structname"/>
   <xsl:variable name="numcol" select="table/tgroup/@cols"/>

   <!-- consider only the first table of each section -->
   <xsl:for-each select="table[1]/tgroup/tbody/row">
    <xsl:choose>
    <!-- there are tables with 3 or 4 columns -->
    <xsl:when test="$numcol = 3">
     <xsl:call-template name="build-comment-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[3]"/>
     </xsl:call-template>
    </xsl:when>
    <xsl:when test="$numcol = 4">
     <xsl:call-template name="build-comment-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    </xsl:choose>
   </xsl:for-each>
  </xsl:when>
  </xsl:choose>

 </xsl:for-each>

 <xsl:text>&#xa;END;</xsl:text>

</xsl:template>

</xsl:stylesheet>

Let's check the output:

euler@harman:~$ xsltproc /path/to/only-columns.xsl /path/to/catalogs.xml
BEGIN;

-- comment on catalog tables
-- comment on pg_aggregate columns
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggfnoid IS 'pg_proc OID of the aggregate function';
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggtransfn IS 'Transition function';
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggfinalfn IS 'Final function (zero if none)';
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggsortop IS 'Associated sort operator (zero if none)';
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggtranstype IS 'Data type of the aggregate function''s internal transition (state) data';
COMMENT ON COLUMN pg_catalog.pg_aggregate.agginitval IS 'The initial value of the transition state. This is a text field containing the initial value in its external string representation. If this field is null, the transition state value starts out null.';
.
.
.
COMMENT ON COLUMN pg_catalog.pg_statistic.stawidth IS 'The average stored width, in bytes, of nonnull entries';

COMMENT ON COLUMN pg_catalog.pg_statistic.stadistinct IS 'The number of distinct nonnull data values in the column. A value greater than zero is the actual number of distinct values. A value less than zero is the negative of a multiplier for the number of rows in the table; for example, a column in which values appear about twice on the average could be represented by stadistinct = -0.5. A zero value means the number of distinct values is unknown.';

COMMENT ON COLUMN pg_catalog.pg_statistic.stakindN IS 'A code number indicating the kind of statistics stored in the Nth slot of the pg_statistic row.';

COMMENT ON COLUMN pg_catalog.pg_statistic.staopN IS 'An operator used to derive the statistics stored in the Nth slot. For example, a histogram slot would show the < operator that defines the sort order of the data.';
.
.
.
-- comment on pg_user_mapping columns
COMMENT ON COLUMN pg_catalog.pg_user_mapping.umuser IS 'OID of the local role being mapped, 0 if the user mapping is public';
COMMENT ON COLUMN pg_catalog.pg_user_mapping.umserver IS 'The OID of the foreign server that contains this mapping';
COMMENT ON COLUMN pg_catalog.pg_user_mapping.umoptions IS 'User mapping specific options, as keyword=value strings';

END;

Checking the generated file, everything seems to be ok except a small detail: some pg_statistic columns presented the form fooN while there were 4 columns to its representation (foo1, foo2, foo3, and foo4). This is true for 4 columns of pg_statistic: stakind, staop, stanumbers, and stavalues. A hack transforms one "virtual" comment into 4 comments.

Two templates were designed to expand the statistic columns (expand-stat-col and build-comment-stat-col) and another one to convert the string "Nth" to "1st", "2nd", "3rd", or "4th" (convert-ord-number). Also, some tests for those special statistic columns were added in the main template.

The new version of only-cols.xsl is above.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0"
                xmlns="http://www.w3.org/TR/xhtml1/transitional"
                exclude-result-prefixes="#default">

<xsl:output method="text" encoding="utf-8"/>

<!-- escape SQL string -->
<xsl:template name="escape-string-sql">
 <xsl:param name="mystr"/>
 <xsl:choose>
  <!-- &quot;&apos;&quot; = "'" -->
  <xsl:when test="contains($mystr, &quot;&apos;&quot;)">
   <xsl:value-of select="substring-before($mystr, &quot;&apos;&quot;)"/>
   <!-- replacing ' (&apos;) with '' -->
   <xsl:text>&apos;&apos;</xsl:text>
   <xsl:call-template name="escape-string-sql">
    <xsl:with-param name="mystr" select="substring-after($mystr, &quot;&apos;&quot;)"/>
   </xsl:call-template>
  </xsl:when>
  <xsl:otherwise>
   <xsl:value-of select="$mystr"/>
  </xsl:otherwise>
 </xsl:choose>
</xsl:template>

<!-- expand fooN to foo1, foo2, foo3, and foo4 (pg_statistic has columns like fooN) -->
<xsl:template name="expand-stat-col">
 <xsl:param name="mycol"/>
 <xsl:param name="mynum"/>
 <xsl:value-of select="substring-before($mycol, 'N')"/>
 <xsl:value-of select="$mynum"/>
</xsl:template>

<!-- convert Nth to 1st, 2nd, 3rd, or 4th -->
<xsl:template name="convert-ord-number">
 <xsl:param name="mystr"/>
 <xsl:param name="myord"/>
 <xsl:choose>
  <xsl:when test="contains($mystr, 'Nth')">
   <xsl:value-of select="substring-before($mystr, 'Nth')"/>
   <xsl:choose>
    <xsl:when test="$myord = 1">1st</xsl:when>
    <xsl:when test="$myord = 2">2nd</xsl:when>
    <xsl:when test="$myord = 3">3rd</xsl:when>
    <xsl:when test="$myord = 4">4th</xsl:when>
   </xsl:choose>
   <xsl:value-of select="substring-after($mystr, 'Nth')"/>
  </xsl:when>
 </xsl:choose>
</xsl:template>

<!-- build catalog column comment -->
<xsl:template name="build-comment-col">
 <xsl:param name="mytab"/>
 <xsl:param name="mycol"/>
 <xsl:param name="mystr"/>
COMMENT ON COLUMN pg_catalog.<xsl:value-of select="$mytab"/>.<xsl:value-of select="$mycol"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space($mystr)"/></xsl:call-template>';
</xsl:template>

<!-- build special catalog stat column comment -->
<xsl:template name="build-comment-stat-col">
 <xsl:param name="mytab"/>
 <xsl:param name="mycol"/>
 <xsl:param name="mystr"/>
 <!-- first column -->
 <xsl:call-template name="build-comment-col">
  <xsl:with-param name="mytab" select="$mytab"/>
  <xsl:with-param name="mycol">
   <xsl:value-of select="substring-before($mycol, 'N')"/>
   <xsl:text>1</xsl:text>
  </xsl:with-param>
  <xsl:with-param name="mystr">
   <xsl:call-template name="convert-ord-number">
    <xsl:with-param name="mystr" select="$mystr"/>
    <xsl:with-param name="myord">1</xsl:with-param>
   </xsl:call-template>
  </xsl:with-param>
 </xsl:call-template>
 <!-- second column -->
 <xsl:call-template name="build-comment-col">
  <xsl:with-param name="mytab" select="$mytab"/>
  <xsl:with-param name="mycol">
   <xsl:value-of select="substring-before($mycol, 'N')"/>
   <xsl:text>2</xsl:text>
  </xsl:with-param>
  <xsl:with-param name="mystr">
   <xsl:call-template name="convert-ord-number">
    <xsl:with-param name="mystr" select="$mystr"/>
    <xsl:with-param name="myord">2</xsl:with-param>
   </xsl:call-template>
  </xsl:with-param>
 </xsl:call-template>
 <!-- third column -->
 <xsl:call-template name="build-comment-col">
  <xsl:with-param name="mytab" select="$mytab"/>
  <xsl:with-param name="mycol">
   <xsl:value-of select="substring-before($mycol, 'N')"/>
   <xsl:text>3</xsl:text>
  </xsl:with-param>
  <xsl:with-param name="mystr">
   <xsl:call-template name="convert-ord-number">
    <xsl:with-param name="mystr" select="$mystr"/>
    <xsl:with-param name="myord">3</xsl:with-param>
   </xsl:call-template>
  </xsl:with-param>
 </xsl:call-template>
 <!-- fourth column -->
 <xsl:call-template name="build-comment-col">
  <xsl:with-param name="mytab" select="$mytab"/>
  <xsl:with-param name="mycol">
   <xsl:value-of select="substring-before($mycol, 'N')"/>
   <xsl:text>4</xsl:text>
  </xsl:with-param>
  <xsl:with-param name="mystr">
   <xsl:call-template name="convert-ord-number">
    <xsl:with-param name="mystr" select="$mystr"/>
    <xsl:with-param name="myord">4</xsl:with-param>
   </xsl:call-template>
  </xsl:with-param>
 </xsl:call-template>
</xsl:template>

<xsl:template match="/chapter[@id='catalogs']">

 <xsl:text>BEGIN;&#xa;</xsl:text>

 <xsl:for-each select="sect1">

  <xsl:choose>
  <!-- comment on catalog columns -->
  <xsl:when test="contains(self::sect1/@id,'catalog-pg-')">
   <xsl:text>&#xa;-- comment on </xsl:text><xsl:value-of select="table/title/structname"/><xsl:text> columns</xsl:text>

   <xsl:variable name="tab" select="table/title/structname"/>
   <xsl:variable name="numcol" select="table/tgroup/@cols"/>

   <!-- consider only the first table of each section -->
   <xsl:for-each select="table[1]/tgroup/tbody/row">
    <xsl:choose>
    <!-- comment on special stat columns -->
    <xsl:when test="contains(entry[1]/structfield, 'stakindN')">
     <xsl:call-template name="build-comment-stat-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    <xsl:when test="contains(entry[1]/structfield, 'staopN')">
     <xsl:call-template name="build-comment-stat-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    <xsl:when test="contains(entry[1]/structfield, 'stanumbersN')">
     <xsl:call-template name="build-comment-stat-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    <xsl:when test="contains(entry[1]/structfield, 'stavaluesN')">
     <xsl:call-template name="build-comment-stat-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    <!-- there are tables with 3 or 4 columns -->
    <xsl:when test="$numcol = 3">
     <xsl:call-template name="build-comment-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[3]"/>
     </xsl:call-template>
    </xsl:when>
    <xsl:when test="$numcol = 4">
     <xsl:call-template name="build-comment-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    </xsl:choose>
   </xsl:for-each>
  </xsl:when>
  </xsl:choose>

 </xsl:for-each>

 <xsl:text>&#xa;END;</xsl:text>

</xsl:template>

</xsl:stylesheet>


Let's check the output:

euler@harman:~$ xsltproc /path/to/only-columns-2.xsl /path/to/catalogs.xml
BEGIN;
.
.
.
COMMENT ON COLUMN pg_catalog.pg_statistic.stadistinct IS 'The number of distinct nonnull data values in the column. A value greater than zero is the actual number of distinct values. A value less than zero is the negative of a multiplier for the number of rows in the table; for example, a column in which values appear about twice on the average could be represented by stadistinct = -0.5. A zero value means the number of distinct values is unknown.';
COMMENT ON COLUMN pg_catalog.pg_statistic.stakind1 IS 'A code number indicating the kind of statistics stored in the 1st slot of the pg_statistic row.';
COMMENT ON COLUMN pg_catalog.pg_statistic.stakind2 IS 'A code number indicating the kind of statistics stored in the 2nd slot of the pg_statistic row.';
COMMENT ON COLUMN pg_catalog.pg_statistic.stakind3 IS 'A code number indicating the kind of statistics stored in the 3rd slot of the pg_statistic row.';
COMMENT ON COLUMN pg_catalog.pg_statistic.stakind4 IS 'A code number indicating the kind of statistics stored in the 4th slot of the pg_statistic row.';
.
.
.
END;

The next step was integrating only-tables.xsl and only-columns-2.xsl. The escape-string-sql template is used in both scripts and the path to extract descriptions are the same. The resulting script extract-catalog.xsl is above.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0"
                xmlns="http://www.w3.org/TR/xhtml1/transitional"
                exclude-result-prefixes="#default">

<xsl:output method="text" encoding="utf-8"/>

<!-- escape SQL string -->
<xsl:template name="escape-string-sql">
 <xsl:param name="mystr"/>
 <xsl:choose>
  <!-- &quot;&apos;&quot; = "'" -->
  <xsl:when test="contains($mystr, &quot;&apos;&quot;)">
   <xsl:value-of select="substring-before($mystr, &quot;&apos;&quot;)"/>
   <!-- replacing ' (&apos;) with '' -->
   <xsl:text>&apos;&apos;</xsl:text>
   <xsl:call-template name="escape-string-sql">
    <xsl:with-param name="mystr" select="substring-after($mystr, &quot;&apos;&quot;)"/>
   </xsl:call-template>
  </xsl:when>
  <xsl:otherwise>
   <xsl:value-of select="$mystr"/>
  </xsl:otherwise>
 </xsl:choose>
</xsl:template>

<!-- expand fooN to foo1, foo2, foo3, and foo4 (pg_statistic has columns like fooN) -->
<xsl:template name="expand-stat-col">
 <xsl:param name="mycol"/>
 <xsl:param name="mynum"/>
 <xsl:value-of select="substring-before($mycol, 'N')"/>
 <xsl:value-of select="$mynum"/>
</xsl:template>

<!-- convert Nth to 1st, 2nd, 3rd, or 4th -->
<xsl:template name="convert-ord-number">
 <xsl:param name="mystr"/>
 <xsl:param name="myord"/>
 <xsl:choose>
  <xsl:when test="contains($mystr, 'Nth')">
   <xsl:value-of select="substring-before($mystr, 'Nth')"/>
   <xsl:choose>
    <xsl:when test="$myord = 1">1st</xsl:when>
    <xsl:when test="$myord = 2">2nd</xsl:when>
    <xsl:when test="$myord = 3">3rd</xsl:when>
    <xsl:when test="$myord = 4">4th</xsl:when>
   </xsl:choose>
   <xsl:value-of select="substring-after($mystr, 'Nth')"/>
  </xsl:when>
 </xsl:choose>
</xsl:template>

<!-- build catalog column comment -->
<xsl:template name="build-comment-col">
 <xsl:param name="mytab"/>
 <xsl:param name="mycol"/>
 <xsl:param name="mystr"/>
COMMENT ON COLUMN pg_catalog.<xsl:value-of select="$mytab"/>.<xsl:value-of select="$mycol"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space($mystr)"/></xsl:call-template>';
</xsl:template>

<!-- build special catalog stat column comment -->
<xsl:template name="build-comment-stat-col">
 <xsl:param name="mytab"/>
 <xsl:param name="mycol"/>
 <xsl:param name="mystr"/>
 <!-- first column -->
 <xsl:call-template name="build-comment-col">
  <xsl:with-param name="mytab" select="$mytab"/>
  <xsl:with-param name="mycol">
   <xsl:value-of select="substring-before($mycol, 'N')"/>
   <xsl:text>1</xsl:text>
  </xsl:with-param>
  <xsl:with-param name="mystr">
   <xsl:call-template name="convert-ord-number">
    <xsl:with-param name="mystr" select="$mystr"/>
    <xsl:with-param name="myord">1</xsl:with-param>
   </xsl:call-template>
  </xsl:with-param>
 </xsl:call-template>
 <!-- second column -->
 <xsl:call-template name="build-comment-col">
  <xsl:with-param name="mytab" select="$mytab"/>
  <xsl:with-param name="mycol">
   <xsl:value-of select="substring-before($mycol, 'N')"/>
   <xsl:text>2</xsl:text>
  </xsl:with-param>
  <xsl:with-param name="mystr">
   <xsl:call-template name="convert-ord-number">
    <xsl:with-param name="mystr" select="$mystr"/>
    <xsl:with-param name="myord">2</xsl:with-param>
   </xsl:call-template>
  </xsl:with-param>
 </xsl:call-template>
 <!-- third column -->
 <xsl:call-template name="build-comment-col">
  <xsl:with-param name="mytab" select="$mytab"/>
  <xsl:with-param name="mycol">
   <xsl:value-of select="substring-before($mycol, 'N')"/>
   <xsl:text>3</xsl:text>
  </xsl:with-param>
  <xsl:with-param name="mystr">
   <xsl:call-template name="convert-ord-number">
    <xsl:with-param name="mystr" select="$mystr"/>
    <xsl:with-param name="myord">3</xsl:with-param>
   </xsl:call-template>
  </xsl:with-param>
 </xsl:call-template>
 <!-- fourth column -->
 <xsl:call-template name="build-comment-col">
  <xsl:with-param name="mytab" select="$mytab"/>
  <xsl:with-param name="mycol">
   <xsl:value-of select="substring-before($mycol, 'N')"/>
   <xsl:text>4</xsl:text>
  </xsl:with-param>
  <xsl:with-param name="mystr">
   <xsl:call-template name="convert-ord-number">
    <xsl:with-param name="mystr" select="$mystr"/>
    <xsl:with-param name="myord">4</xsl:with-param>
   </xsl:call-template>
  </xsl:with-param>
 </xsl:call-template>
</xsl:template>

<xsl:template match="/chapter[@id='catalogs']">

 <xsl:text>BEGIN;&#xa;</xsl:text>

 <xsl:text>&#xa;-- comment on catalog tables</xsl:text>

 <xsl:for-each select="sect1">

  <xsl:choose>
  <!-- comment on catalog tables -->
  <xsl:when test="self::sect1[@id='catalogs-overview']">
   <xsl:for-each select="table[@id='catalog-table']/tgroup/tbody/row">
COMMENT ON TABLE pg_catalog.<xsl:value-of select="entry[1]"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space(entry[2])"/></xsl:call-template>';</xsl:for-each>
   <xsl:text>&#xa;</xsl:text>
  </xsl:when>
  <!-- comment on catalog columns -->
  <xsl:when test="contains(self::sect1/@id,'catalog-pg-')">
   <xsl:text>&#xa;-- comment on </xsl:text><xsl:value-of select="table/title/structname"/><xsl:text> columns</xsl:text>

   <xsl:variable name="tab" select="table/title/structname"/>
   <xsl:variable name="numcol" select="table/tgroup/@cols"/>

   <!-- consider only the first table of each section -->
   <xsl:for-each select="table[1]/tgroup/tbody/row">
    <xsl:choose>
    <!-- comment on special stat columns -->
    <xsl:when test="contains(entry[1]/structfield, 'stakindN')">
     <xsl:call-template name="build-comment-stat-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    <xsl:when test="contains(entry[1]/structfield, 'staopN')">
     <xsl:call-template name="build-comment-stat-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    <xsl:when test="contains(entry[1]/structfield, 'stanumbersN')">
     <xsl:call-template name="build-comment-stat-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    <xsl:when test="contains(entry[1]/structfield, 'stavaluesN')">
     <xsl:call-template name="build-comment-stat-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>

    <!-- there are tables with 3 or 4 columns -->
    <xsl:when test="$numcol = 3">
     <xsl:call-template name="build-comment-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[3]"/>
     </xsl:call-template>
    </xsl:when>
    <xsl:when test="$numcol = 4">
     <xsl:call-template name="build-comment-col">
      <xsl:with-param name="mytab" select="$tab"/>
      <xsl:with-param name="mycol" select="entry[1]/structfield"/>
      <xsl:with-param name="mystr" select="entry[4]"/>
     </xsl:call-template>
    </xsl:when>
    </xsl:choose>
   </xsl:for-each>
  </xsl:when>
  </xsl:choose>

 </xsl:for-each>

 <xsl:text>&#xa;END;</xsl:text>

</xsl:template>

</xsl:stylesheet>

The SQL file can be built with the following command:

euler@harman:~$ xsltproc /path/to/extract-catalog.xsl /path/to/catalogs.xml > /path/to/catalogs.sql

The final step was to load the sql file into PostgreSQL. You need to have in mind that comments are not shared across databases; if you load the script in database foo, those comments will only be visible to users connected to database foo. You can load it to your template database (template1, for example) and any new database created using that template will inherit those comments.

euler@harman:~$ psql -f /path/to/catalogs.sql template1

Tuesday, November 23, 2010

Hot Standby e Streaming Replication

A partir da versão 9.0 do PostgreSQL, é possível combinar hot standby e envio de registros (streaming replication) para compor uma solução de replicação. Os benefícios dessa solução são aqueles citados no artigo anterior (disponibilidade, confiabilidade, desempenho e/ou balanceamento de carga). Neste caso, teremos o envio assíncrono das transações realizadas no servidor principal para o servidor secundário e, além disso, o servidor secundário aceitará consultas somente leitura (consultas que não modificam dados, ou seja, não escrevem no log de transação tais como SELECT, COPY TO, BEGIN, END, ROLLBACK); comandos como INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, nextval() não são permitidos.  A figura abaixo ilustra esse cenário:



 A implementação de tal cenário está descrita abaixo. Vale lembrar que os pré-requisitos apresentados no artigo anterior são utilizados aqui.

No servidor principal, editamos o arquivo /bd/primario/postgresql.conf:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 20

O parâmetro listen_addresses permite que o servidor secundário se conecte ao servidor principal para receber os logs de transação. O parâmetro wal_level indica o nível de log de transação armazenado no servidor principal; minimal (padrão) não permite que este tipo de replicação seja realizada. O parâmetro max_wal_senders indica o número máximo de servidores secundários permitidos. O parâmetro wal_keep_segments só é necessário se (i) for realizar a cópia dos arquivos com o servidor principal em atividade e (ii) se houverem paradas programadas do servidor secundário; este valor deve ser maior do que o número de arquivos de log de transação gerados durante a cópia dos arquivos. Neste caso, 20 é igual a 320 MB (20 * 16 MB).

A modificação desses parâmetros exige que o PostgreSQL do servidor principal seja reiniciado. Assim se a sua janela de manutenção não é flexível, programe-se. A aplicação dessas modificações não implica ter que iniciar a replicação imediatamente.

postgres@principal:~$ pg_ctl restart -D /bd/primario
waiting for server to shut down.... done
server stopped
server starting


No servidor principal, crie uma role (usuário) para replicar os dados. O PostgreSQL exige que o usuário que realiza a replicação seja um super-usuário e possa se conectar ao servidor primário.

postgres@principal:~$ psql
psql (9.0.1)
Type "help" for help.
postgres=# CREATE ROLE usuario SUPERUSER LOGIN;
CREATE ROLE
postgres=# \q


Dependendo da sua política de segurança, você pode precisar definir uma senha para este usuário. Neste caso, a senha deve ser informada no arquivo de senhas (~postgres/.pgpass ou %APPDATA%\postgresql\pgpass.conf no Windows) ou na string de conexão no arquivo /bd/secundario/recovery.conf.

postgres@principal:~$ psql
psql (9.0.1)
Type "help" for help.
postgres=# \password usuario
Enter new password:
Enter it again:
postgres=# \q


No servidor principal, edite o arquivo /bd/primario/pg_hba.conf. O método de autenticação vai depender da sua política de segurança. Se você decidiu colocar senha para o usuário que realiza a replicação, utilize o método de autenticação md5; caso contrário, utilize o método de autenticação trust.

host    replication        usuario        10.1.1.2/32        md5


A adição de uma nova regra de autenticação, exige a recarga das regras de autenticação.

postgres@principal:~$ pg_ctl reload -D /bd/primario
server signaled


No servidor secundário, edite o arquivo /bd/secundario/postgresql.conf:

hot_standby = on


O parâmetro hot_standby indica se o servidor secundário aceita conexões.

No servidor secundário, crie o arquivo /bd/secundario/recovery.conf. Este arquivo conterá informações sobre a aplicação das modificações realizadas no servidor principal e transmitidas para o servidor secundário.

standby_mode = 'on'
primary_conninfo = 'host=10.1.1.1 port=5432 user=usuario password=minhasenha'
trigger_file = '/bd/secundario/failover.trg'


O parâmetro standby_mode especifica se o servidor PostgreSQL é um servidor secundário (standby). O parâmetro primary_conninfo especifica como se conectar ao servidor principal. A senha (especificado em password) pode ser omitida ali e especificada no arquivo de senhas (~postgres/.pgpass ou %APPDATA%\postgresql\pgpass.conf no Windows). Se o parâmetro trigger_file for utilizado, indica que a presença do arquivo citado (/bd/secundario/failover.trg), termina a recuperação, ou seja, o servidor passa a ser um servidor principal (que aceita quaisquer tipos de comandos; não somente aqueles comandos somente leitura).

No servidor principal, teremos que realizar a cópia física dos arquivos para o servidor secundário. Há duas maneiras de realizar esta cópia dependendo da disponibilidade do servidor principal:
  • com o servidor principal parado;
  • com o servidor principal em atividade.
Se o servidor principal puder ficar parado durante a cópia, podemos fazer:

postgres@principal:~$ pg_ctl stop -D /bd/primario
waiting for server to shut down.... done
server stopped
postgres@principal:~$ rsync -av --exclude postgresql.conf \
--exclude pg_hba.conf --exclude pg_xlog/* --exclude pg_log/* \
/bd/primario/ postgres@10.1.1.2:/bd/secundario
postgres@principal:~$ pg_ctl start -D /bd/primario
server starting


Caso o servidor principal não possa parar, podemos fazer todo processo online. Neste caso precisamos executar os seguintes comandos no servidor principal:

postgres@principal:~$ psql
psql (9.0.1)
Type "help" for help.
postgres=# select pg_start_backup('replicacao', true);
pg_start_backup
-----------------
0/5044CB4
(1 row)

postgres=# \q
postgres@principal:~$ rsync -av --exclude postmaster.pid \
--exclude postgresql.conf --exclude pg_hba.conf \
--exclude backup_label --exclude pg_xlog/* --exclude pg_log/* \
/bd/primario/ postgres@10.1.1.2:/bd/secundario
postgres@principal:~$ psql
psql (9.0.1)
Type "help" for help.
postgres=# select pg_stop_backup();
NOTICE:  WAL archiving is not enabled; you must ensure that all required
WAL segments are copied through other means to complete the backup
pg_stop_backup
----------------
0/90D7950
(1 row)

postgres=# \q


A primeira consulta (select pg_start_backup()) prepara o servidor para iniciar a cópia dos arquivos. O próximo passo é copiar os arquivos (utilizamos o rsync mas pode ser qualquer outro aplicativo) e, neste caso, temos que excluir alguns arquivos (postmaster.pid, postgresql.conf, pg_hba.conf, backup_label, pg_xlog/* e pg_log/*). Indicamos que a cópia já foi concluída executando a função (pg_stop_backup()). Vale lembrar que o parâmetro wal_keep_segments no servidor principal deve ser maior do que o número de arquivos de log de transação gerados durante a cópia. Isto porque o servidor secundário precisará destes arquivos (eles não podem ser reciclados) para "acompanhar" o servidor principal. Caso o parâmetro wal_keep_segments não seja suficiente, (i) você terá que aumentar o valor e realizar todo este passo novamente ou (ii) caso você esteja arquivando os arquivos de log de transação basta copiá-los para o diretório pg_xlog do servidor secundário.

Neste momento você pode iniciar o servidor secundário. Não se esqueça de criar os arquivos postgresql.conf e pg_hba.conf no servidor secundário.

postgres@secundario:~$ pg_ctl start -D /bd/secundario
server starting


Caso os logs estejam habilitados (logging_collector = on) no servidor secundário, as seguintes mensagens indicam que a replicação está acontecendo com sucesso:

LOG:  database system was interrupted; last known up at 2010-11-14 14:08:19 BRT
LOG:  entering standby mode
LOG:  redo starts at 0/5044CB4
LOG:  restartpoint starting: xlog
LOG:  consistent recovery state reached at 0/A000000
LOG:  database system is ready to accept read only connections
LOG:  streaming replication successfully connected to primary

Neste momento, espera-se que o servidor secundário esteja "acompanhando" o servidor principal mas sempre em um estado consistente. Assim, a mesma consulta em ambos servidores podem retornar resultados diferentes (lembre-se que a replicação é assíncrona).

Se você tiver perguntas, as listas de discussão pgbr-geral (português) e pgsql-general (inglês) são bons lugares para perguntar.

Thursday, November 11, 2010

Replicação no PostgreSQL

Este é o primeiro artigo (introdutório) de uma série de artigos sobre replicação no PostgreSQL. A ideia é desvendar como realizar replicação no PostgreSQL explicando cada passo a ser realizado. Os primeiros artigos conterão soluções de replicação nativas do PostgreSQL; na sequência irei abordar outras ferramentas (que não são distribuídas com o PostgreSQL) utilizadas para fazer replicação. Então, por que preciso replicar?

Um servidor de banco de dados pode estar trabalhando bem em uma solução que contém vários servidores de aplicação e milhares de clientes. No entanto, as empresas estão muito interessadas em distribuir o processamento enquanto mantêm a integração dos recursos de informação. Em um determinado momento poderá surgir a necessidade de ter um outro servidor de banco de dados que contenha todos os dados ou parte deles para fins de disponibilidade, confiabilidade, desempenho e/ou balanceamento de carga.

Vários tipos de servidores, tais como servidores web, servidores DNS e servidores de aplicação, que contém dados estáticos podem ser combinados facilmente para atender requisições balanceando a carga entre eles. Da mesma maneira, isso pode ser feito para servidores de bancos de dados. No entanto, servidores de bancos de dados geralmente não possuem dados puramente estáticos.Se permitirmos alteração no servidor de banco de dados, ainda temos que garantir a atomicidade (vide ACID) -- o que nem sempre é um problema nos outros tipos de servidores. Isto torna soluções de replicação em servidores de bancos de dados mais complexas do que em outros tipos de servidores.

As soluções de replicação disponíveis em SGBDs podem ser classificadas quanto a sincronia, escrita, fragmentação, envio e modo.
  • sincronia: os dados podem ser sincronizados "simultaneamente" ou após algum tempo;
    • síncrono: a transação é efetivada "simultaneamente" em todos os servidores que participam da replicação;
    • assíncrono: após a transação ser efetivada em um servidor, ela é propagada para os outros servidores.
  • escrita: os dados podem ser escritos em qualquer servidor, alguns servidores ou somente em um servidor. Servidor principal (também chamado de mestre) é aquele que aceita escrita. Servidor secundário (também chamado de escravo) é aquele que aceita somente leitura;
    • múltiplos mestres: os dados podem ser escritos em múltiplos servidores e serão enviados para os outros servidores que participam da replicação;
    • um mestre: somente um servidor (principal/mestre) aceita escrita de dados. Os outros servidores só recebem as alterações feitas no servidor principal.
  • fragmentação: os dados de uma relação são particionados em múltiplos servidores.
    • horizontal: cada fragmento (que está em um servidor) contém um subconjunto das tuplas da relação completa, ou seja, a união dos fragmentos resulta na relação completa;
    • vertical: cada fragmento (que está em um servidor) contém um subconjunto dos atributos da relação, ou seja, cada fragmento é uma projeção da relação completa;
  • envio: os dados podem ser enviados quando o arquivo que contém logs de transação é fechado ou quando uma transação é efetivada;
    • envio de arquivo: após o preenchimento (fechamento) de um arquivo que contém logs de transação, este é enviado a outros servidores que aplicam os logs de transação. Este tipo geralmente é utilizado em combinação com envio assíncrono;
    • envio de registro: após a efetivação de uma transação, o log (registro) da transação é enviado e aplicado nos servidores que participam da replicação;
  • modo: os servidores secundários podem aceitar ou não consultas (somente leitura). Este tipo é utilizado com escrita somente em um servidor principal;
    • warm standby: os servidores secundários (aqueles que não recebem escrita) não aceitam conexões (e consultas);
    • hot standby: os servidores secundários aceitam conexões e consultas que não modificam dados;
Uma solução de replicação pode combinar várias dessas caraterísticas para atender uma determinada necessidade. O PostgreSQL aceita nativamente quase todos as características citadas acima exceto síncrono (possivelmente teremos na versão 9.1), múltiplos mestres (sem previsão -- uma ferramenta externa como o Bucardo pode suprir esta necessidade) e fragmentação (uma ferramenta externa como o PL/Proxy pode suprir esta necessidade).
Pela complexidade de gerenciamento e perda de performance, a escrita em múltiplos servidores ao mesmo tempo não é muito comum. Cenários com múltiplos servidores mestres exigem que as aplicações "saibam" que há múltiplos servidores que aceitam escrita para evitar possíveis conflitos (mesmo dado alterado em dois servidores) ou impasses (deadlocks). As soluções de replicação mais comuns são aquelas que recebem alterações somente em um servidor (principal) e enviam as modificações realizadas no mesmo para os outros servidores (secundários). Caso o servidor principal falhe, podemos promover um servidor secundário a servidor principal.

Podemos caracterizar as soluções nativas do PostgreSQL quanto a sincronia (assíncrono), escrita (um mestre), envio (arquivo, registro) e modo (warm standby, hot standby). Assim, há quatro possíveis cenários para replicação nativo no PostgreSQL:
  • warm standby e envio de arquivo: os servidores secundários não aceitam conexões e se mantêm atualizados de modo assíncrono através dos arquivos de log de transação enviados pelo servidor principal após serem preenchidos ou depois de determinado tempo. Esta solução está disponível desde a versão 8.0;
  • warm standby envio de registro: os servidores secundários não aceitam conexões e se mantêm atualizados de modo assíncrono através dos logs de transação que são enviados pelo servidor principal e aplicados nos servidores secundários. Esta solução está disponível desde a versão 9.0;
  • hot standby e envio de arquivo: os servidores secundários aceitam consultas somente leitura e se mantêm atualizados de modo assíncrono através dos arquivos de log de transação enviados pelo servidor principal após serem preenchidos ou depois de determinado tempo. Esta solução está disponível desde a versão 9.0;
  • hot standby e envio de registro: os servidores secundários aceitam consultas somente leitura e se mantêm atualizados de modo assíncrono através dos logs de transação enviados pelo servidor principal e aplicados nos servidores secundários. Esta solução está disponível desde a versão 9.0.
Apresentaremos a implementação dos quatros tipos de replicação descritos acima utilizando o seguinte cenário.




Os pré-requisitos são:
  • 2 servidores: utilizaremos dois servidores. Um servidor principal (aceita leitura e modificação de dados) e outro servidor secundário (pode aceitar ou não consultas somente leitura). Estes servidores devem ser preferencialmente (quase) idênticos pois a replicação é binária, sendo incompatível em arquiteturas e/ou sistemas operacionais distintos. A arquitetura deve ser a mesma; o sistema operacional pode ser diferente mas para evitar problemas de incompatibilidade de hardware, bugs ou perda de performance, aconselhamos que utilize a mesma versão;
  • PostgreSQL: a mesma versão deve ser utilizada. A mesma versão corretiva não é necessária (podemos utilizar 9.0.0 e 9.0.1) mas não é aconselhável ter servidores secundários com versão menor do que servidores principais. Se possível, utilize exatamente a mesma versão em todos os servidores;
  • Acesso: você deve ter acesso remoto aos 2 servidores. Talvez seja necessário o acesso super-usuário (root) no servidor secundário para criar o diretório de dados e definir permissões. É necessário acesso super-usuário no PostgreSQL do servidor principal;
  • Rede: deve ser possível acessar a porta (geralmente 5432) do servidor principal a partir do servidor secundário. Certifique que o firewall não esteja bloqueando este acesso. Além disso, no momento da cópia física dos arquivos deve ser possível enviá-los (por ssh, ftp, dentre outros) do servidor principal para o servidor secundário ou vice-versa;
  • Interface de Rede: os servidores principal e secundário possuem respectivamente os endereços de rede 10.1.1.1 e 10.1.1.2;
  • Diretório de Dados: os diretórios de dados do servidor principal e secundário estão respectivamente em /bd/primario e /bd/secundario. O arquivamento de arquivos de log de transação feito no servidor secundário está em /bd/arquivamento;
  • Usuário: o usuário do sistema operacional que iniciará o PostgreSQL em ambos servidores será o postgres.