tag:blogger.com,1999:blog-86254269197452103362024-03-08T18:16:09.824-03:00Euler TaveiraEuler Taveirahttp://www.blogger.com/profile/08335071854403271396noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-8625426919745210336.post-75498174841275389532019-10-14T19:26:00.000-03:002019-10-14T19:26:28.625-03:00Postgres Object ownershipSometimes I have to fix some object ownership such as tables and views. Let's figure out if there is such object in your database:<br />
<br />
<pre class="brush: csharp">--
-- list tables, views, foreign tables and sequences not owned by role postgres
--
SELECT n.nspname AS SCHEMA,
c.relname AS relation,
pg_get_userbyid(c.relowner) AS ROLE,
'ALTER TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'r'
AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
c.relname AS relation,
pg_get_userbyid(c.relowner) AS ROLE,
'ALTER VIEW ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'v'
AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
c.relname AS relation,
pg_get_userbyid(c.relowner) AS ROLE,
'ALTER FOREIGN TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'f'
AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
c.relname AS relation,
pg_get_userbyid(c.relowner) AS ROLE,
'ALTER SEQUENCE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'S'
AND pg_get_userbyid(c.relowner) <> 'postgres';</pre>
<br />
This UNION ALL query list tables, views, foreign tables and sequences whose owner is <b>not</b> role postgres. They should be candidates for a new owner (mainly because you are adjusting ownership and permission on testing and/or staging environment). Let's say you want to apply such changes after check that some ownerships are wrong. The following query will output SQL command(s) to change ownership.<br />
<br />
<pre class="brush: csharp">--
-- change owner of tables, views, foreign tables and sequences not owned by role postgres
--
SELECT 'ALTER TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'r'
AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT 'ALTER VIEW ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'v'
AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT 'ALTER FOREIGN TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'f'
AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT 'ALTER SEQUENCE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
AND relkind = 'S'
AND pg_get_userbyid(c.relowner) <> 'postgres';</pre>
<br />
And if you are using psql (9.6 and later), you should replace last character (semicolon) with <b>\gexec</b>. In this case, instead of print SQL commands, they will be executed. Voila, all tables, views, foreign tables and sequences that were not owned by postgres will switch to a new owner: postgres.Euler Taveirahttp://www.blogger.com/profile/08335071854403271396noreply@blogger.com1tag:blogger.com,1999:blog-8625426919745210336.post-23907278281537771582018-07-02T00:40:00.000-03:002018-07-03T20:02:59.526-03:00pgquarrel 0.4 releasedA new <a href="https://github.com/eulerto/pgquarrel/releases">pgquarrel</a> version was released. When I started to develop pgquarrel I wanted a software that was: (i) PostgreSQL-centric, (ii) supports different PostgreSQL versions, (iii) does not depend on other software, (iv) run faster, (v) multiplatform, and (vi) flexible. There were a lot of migration tools out there but all of them are generic (it couldn't show me that a reloption was changed or that a new domain was created). After another release, the light in the end of the tunnel seems closer than when I started to code.<br />
<br />
pgquarrel supports the most important objects:<br />
<ul>
<li>tables;</li>
<li>indexes;</li>
<li>functions;</li>
<li>views; </li>
<li>materialized views;</li>
<li>schemas; </li>
<li>sequences; </li>
<li>triggers;</li>
<li>types;</li>
<li>domains.</li>
</ul>
It also supports PostgreSQL-centric objects such as:<br />
<ul>
<li>extensions;</li>
<li>rules;</li>
<li>event triggers;</li>
<li>text search;</li>
<li>operators;</li>
<li>security labels. </li>
</ul>
This new release adds support for partitioning, typed tables, and extended statistics. It adds options to select what kind of objects you want to compare. It also adds a bunch of command line options that turns pgquarrel script-friendly (it is no longer necessary to create a configuration file). <br />
<br />
<div style="background-color: #dddddd; font-style: italic;">
$ pgquarrel --source-dbname=dev --target-dbname=prod --source-host=192.168.0.8 --target-host=192.168.0.6 --function=false --comment=true<br />
--<br />
-- pgquarrel 0.4.0<br />
-- quarrel between 9.1.24 and 10.4<br />
--<br />
<br />
CREATE DOMAIN public.cep AS text<br />
CONSTRAINT cep_check CHECK ((VALUE ~ '^\d{5}-d{3}$'::text));<br />
<br />
CREATE TABLE public.bar (<br />
description character varying(30) NOT NULL,<br />
foo_id integer,<br />
id integer NOT NULL<br />
);<br />
<br />
ALTER TABLE ONLY public.bar<br />
ADD CONSTRAINT bar_pkey PRIMARY KEY (id);<br />
<br />
ALTER TABLE ONLY public.bar<br />
ADD CONSTRAINT bar_foo_id_fkey FOREIGN KEY (foo_id) REFERENCES foo(id);<br />
<br />
COMMENT ON TABLE public.bar IS 'this is another table';<br />
<br />
COMMENT ON COLUMN public.bar.description IS 'this is a column';<br />
<br />
ALTER TABLE ONLY public.foo DROP COLUMN removeit;<br />
<br />
ALTER TABLE public.foo RESET (autovacuum_enabled);</div>
<br />
If you have any questions, suggestions or bugs, open an <a href="https://github.com/eulerto/pgquarrel/issues">issue</a> or send a <a href="https://github.com/eulerto/pgquarrel/pulls">pull request</a>.<br />
<br />
Thanks to <a href="https://github.com/rafalcieslak">Rafał Cieślak</a> and <a href="https://github.com/darold">Gilles Darold</a> for their contributions.Euler Taveirahttp://www.blogger.com/profile/08335071854403271396noreply@blogger.com0tag:blogger.com,1999:blog-8625426919745210336.post-79553988411730211312018-03-31T01:05:00.001-03:002018-03-31T01:05:56.155-03:00wal2json 1.0 released<a href="https://github.com/eulerto/wal2json/releases">wal2json 1.0</a> was released! I started to develop <a href="https://github.com/eulerto/wal2json">wal2json</a> more than 4 years ago. At that time it was just a proof of concept for logical decoding. It was one of the first logical decoding plugins and maybe one of the most popular (because <a href="https://en.wikipedia.org/wiki/Change_data_capture">CDC</a> solutions -- such as <a href="https://kafka.apache.org/">Kafka</a> -- became popular and it is supported by <a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.html">Amazon RDS</a> since 9.5).<br />
<br />
It works on PostgreSQL 9.4+ and in all PostgreSQL supported platforms. Although we don't have packages for it yet, it is easier to install.<br />
<br />
It has some parameters to control what it outputs and its behavior. You can choose to include or exclude:<br />
<ul>
<li>transaction id;</li>
<li>transaction timestamp;</li>
<li>table schema;</li>
<li>type names (and also type modifier);</li>
<li>type oids;</li>
<li><i>not null</i> information;</li>
<li>end of transaction LSN;</li>
<li>unchanged TOAST columns.</li>
</ul>
There is also options to:<br />
<ul>
<li>pretty print output (indentation and formatting);</li>
<li>write after each row instead of after each transaction (useful for big transactions because postgres has memory limitations -- 1 GB).</li>
</ul>
And the last features I implemented:<br />
<ul>
<li>filter tables (don't send rows from those tables);</li>
<li>choose tables (send only rows from those tables).</li>
</ul>
If you have any questions, suggestions or bugs, open an <a href="https://github.com/eulerto/wal2json/issues">issue</a> or send a <a href="https://github.com/eulerto/wal2json/pulls">pull request</a>.<br />
<br />
Thanks to <a href="https://github.com/dvarrazzo">Daniele Varrazzo</a>, <a href="https://github.com/rcoup">Robert Coup</a> and <a href="https://github.com/davidfetter">David Fettter</a> for their contributions.Euler Taveirahttp://www.blogger.com/profile/08335071854403271396noreply@blogger.com0tag:blogger.com,1999:blog-8625426919745210336.post-22470133419242665392013-02-20T00:10:00.000-03:002013-02-20T19:18:07.776-03:00PGBR 2013: Call for Papers<a href="http://pgbr.postgresql.org.br/2013/">PGBR</a> is the major portuguese-speaking PostgreSQL conference. It is the biggest PostgreSQL event in Americas. We're in the fifth edition. This year the conference will be outside Sao Paulo State. It will take place at <a href="http://en.wikipedia.org/wiki/Amazon_rainforest">Amazon Area</a> (<a href="http://en.wikipedia.org/wiki/Porto_Velho">Porto Velho</a>, Rondônia). It is scheduled to be held from August 15 to 17, 2013.<br />
<br />
I was at Porto Velho for a <a href="http://www.flickr.com/photos/eulerto/3520385405/in/set-72157600756262757">PGDay 2009</a> -- invited by Luis Bueno. It was a unique opportunity to meet people that I've never imagined that was using Postgres in their projects. Of course, I can't miss a ride in <a href="http://www.flickr.com/photos/eulerto/3617544033/in/set-72157600756103642">Rio Madeira waters</a> and relish a <a href="http://www.flickr.com/photos/macapuna/5317984985/">typical fish in a sauce</a>.<br />
<br />
Be sure to submit your proposals until March, 15, 2013 at 23:59 BRST (for foreigners) and May, 3, 2013 (for residents). Check the CFP at <a href="http://pgbr.postgresql.org.br/2013/chamada.en.php">http://pgbr.postgresql.org.br/2013/chamada.en.php</a>. The proposals could be in three languages: Portuguese, English or Spanish Also, you can choose among lecture, tutorial, hacker talk, lighting talk and academic panel (only in Portuguese).<br />
<br />
New this year, we are having training courses taught by well-known community members.<br />
<br />
Stay tuned!Euler Taveirahttp://www.blogger.com/profile/08335071854403271396noreply@blogger.com0tag:blogger.com,1999:blog-8625426919745210336.post-52809839176067127832011-11-09T18:35:00.000-02:002011-11-27T13:35:33.883-02:00Understanding WAL nomenclatureThere are some PostgreSQL functions that reports an information such as <b>68A/16E1DA8</b>. But what does it mean? The documentation states that it is a transaction log location. Let me try to explain it in details.<br />
<br />
What is WAL? WAL is an abbreviation of <b>Write-Ahead Log</b>. That is how PostgreSQL named the transaction log (also known as REDO log). Every modern database system has one. It is basically a history of operations that was executed by a DBMS. It improves performance (you only need to flush one file per transaction) and guarantees ACID properties (if the DBMS was in an inconsistent state -- because of software crash, for example -- it reapplies all transactions that are committed but whose changes are not in the database files). PostgreSQL introduced the transaction log in <a href="http://www.postgresql.org/docs/current/static/release-7-1.html">version 7.1</a> (2001). It opted to implement only REDO log (there is no UNDO log on PostgreSQL).<br />
<br />
The transaction log files are stored in <i>$PGDATA/pg_xlog</i> directory. They are named as <b><span class="Apple-style-span" style="color: blue;">00000002</span><span class="Apple-style-span" style="color: orange;">0000070A</span><span class="Apple-style-span" style="color: #38761d;">0000008E</span></b>. They are all hexadecimal digits (0123456789ABCDEF). The first 8 digits identifies the <a href="http://www.postgresql.org/docs/9.1/static/continuous-archiving.html#BACKUP-TIMELINES">timeline</a>, the following 8 digits identifies the (logical) xlog file and the last ones represents the (physical) xlog file (aka segment). The physical files in pg_xlog directory are <b>not</b> actually the xlog files; PostgreSQL calls it <b>segments</b>. By default, a logical xlog file has 255 segments (16 MB each, 4080 MB in total) So in our example, the <span class="Apple-style-span" style="color: orange;"><b>0000070A</b></span> and <span class="Apple-style-span" style="color: #38761d;"><b>0000008E</b></span> are the logical xlog file and segment file, respectively. When segment file <b><span class="Apple-style-span" style="color: #38761d;">000000FE</span></b> is filled up the logical xlog file is incremented by 1 (i.e. <b><span class="Apple-style-span" style="color: orange;">0000070B</span></b>) and the segment file sequence starts up again (i.e. <b><span class="Apple-style-span" style="color: #38761d;">00000000</span></b>) -- <b><span class="Apple-style-span" style="color: blue;">00000002</span><span class="Apple-style-span" style="color: orange;">0000070B</span><span class="Apple-style-span" style="color: #38761d;">00000000</span></b>.<br />
<br />
What means <b><span class="Apple-style-span" style="color: blue;">68A</span>/<span class="Apple-style-span" style="color: #38761d;">16E1DA8</span></b>? <b><span class="Apple-style-span" style="color: blue;">68A</span></b> is the (logical) xlog file and <b><span class="Apple-style-span" style="color: #38761d;">16E1DA8</span></b> is the offset inside the logical xlog file. It means that the offset goes from <b><span class="Apple-style-span" style="color: #38761d;">00000000</span></b> to <b><span class="Apple-style-span" style="color: #38761d;">FF000000</span></b> (4278190080 bytes = 4080 MB). When it reaches FF000000, it means we are in the last segment (aka physical xlog file) and the xlog file number will be incremented by 1 (i.e. <b><span class="Apple-style-span" style="color: blue;">68B</span></b>) and the offset will be restarted (<b><span class="Apple-style-span" style="color: #38761d;">00000000</span></b>).<br />
<br />
PostgreSQL provides a set of functions <a href="http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE">[1]</a> <a href="http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE">[2]</a> that deals with the transaction log location. For example, converting transaction log location to segment file name:<br />
<br />
<code>
postgres=# select pg_xlogfile_name('68A/16E1DA8');<br />
pg_xlogfile_name <br />
--------------------------<br />
000000020000068A00000001<br />
(1 row)<br />
<br />
</code>
Converting transaction log location to segment file name and the offset <u>inside</u> that segment.<br />
<br />
<code>
postgres=# select * from pg_xlogfile_name_offset('68A/16E1DA8');<br />
file_name | file_offset<br />
--------------------------+-------------<br />
000000020000068A00000001 | 7216552<br />
(1 row)<br />
<br />
</code>
<br />
If I want to know which segment file the PostgreSQL server is writing the transaction log now:<br />
<br />
<code>
postgres=# select pg_xlogfile_name(pg_current_xlog_location());<br />
pg_xlogfile_name <br />
--------------------------<br />
000000020000074B000000E4<br />
(1 row)<br />
<br />
</code>
<br />
If you have a replication scenario, you can obtain the receive and replay lags. It is a simple hexadecimal math: master transaction log location minus replica transaction log location.<br />
<br />
In the replica:<br />
<br />
<code>
postgres=# select pg_last_xlog_receive_location();<br />
pg_last_xlog_receive_location<br />
-------------------------------<br />
74B/E4D1C628<br />
(1 row)<br />
<br />
postgres=# select pg_last_xlog_replay_location();<br />
pg_last_xlog_replay_location<br />
------------------------------<br />
74B/E4D1C628<br />
(1 row)<br />
<br />
</code>
In the master:<br />
<br />
<code>
postgres=# select pg_current_xlog_location();<br />
pg_current_xlog_location<br />
--------------------------<br />
74B/E4D3B070<br />
(1 row)<br />
<br />
</code>
If they report the same xlog file (<b><span class="Apple-style-span" style="color: blue;">74B</span></b>), it is a matter of <b><span class="Apple-style-span" style="color: #38761d;">E4D3B070</span></b> - <b><span class="Apple-style-span" style="color: #38761d;">E4D1C628</span></b> = 1EA48 (125512 bytes). It means that the replica is roughly 123 kB behind the master. Receive and replay locations aren't the same all the time; if the replica can't keep up with writing receive location > replay location.<br />
<br />
What about different xlog files? Some tools use the following formula:<br />
<br />
<div style="text-align: center;">
<code>(FFFFFFFF * xlog_master + offset_master) - (FFFFFFFF * xlog_replica + offset_replica)</code>
</div>
<br />
It's wrong ¹. Why? Because the offset goes up to <b><span class="Apple-style-span" style="color: #38761d;">FF000000</span></b> (and not FFFFFFFF). It means that the above formula reports 16 MB times the difference between xlog numbers when the xlog numbers aren't the same. The correct formula is:<br />
<br />
<div style="text-align: center;">
<code>(FF000000 * xlog_master + offset_master) - (FF000000 * xlog_replica + offset_replica)</code>
</div>
<br />
Let's suppose the master and replica transaction location are <b><span class="Apple-style-span" style="color: blue;">67E</span></b>/<b><span class="Apple-style-span" style="color: #38761d;">AFE198</span></b> and <b><span class="Apple-style-span" style="color: blue;">67D</span></b>/<b><span class="Apple-style-span" style="color: #38761d;">FECFA308</span></b>, respectively. The correct lag is 14696080 bytes and not 31473295 bytes (the difference between results is 16 MB!).<br />
<br />
<br />
¹ I will send patches to fix <a href="http://bucardo.org/wiki/Check_postgres">check_postgres</a> and <a href="http://munin-monitoring.org/">Munin</a>. If you know another tool that is using the wrong formula, let me know or even better, the tool's author. FYI, both tools were fixed in a few hours. Thanks, Greg and Magnus!Euler Taveirahttp://www.blogger.com/profile/08335071854403271396noreply@blogger.com14tag:blogger.com,1999:blog-8625426919745210336.post-91335447925981715162011-11-08T23:35:00.000-02:002011-11-08T23:35:21.945-02:00PGBR 2011 is over<a href="http://pgbr.postgresql.org.br/2011/">PGBR 2011</a> is over. It was a challenge to organize an event after 2 years and with a budget 50% higher than the <a href="http://pgbr.postgresql.org.br/2009/">last conference</a>. Yes, we did it! It was an event organized by the community to the community. Thanks to our <a href="http://associacao.python.org.br/associacao/manual-do-big-kahuna">Kahunas</a> (<a href="http://www.midstorm.org/~telles/">Fabio Telles</a> and Flavio Gurgel) and our sponsors.<br />
<br />
This year we had participants from another countries (Argentina, Peru, and USA). We are planning a better support from website and registration next year (sorry <a href="http://pyrseas.wordpress.com/">Joe</a>). Also, some foreign speakers: Alvaro (Chile), <a href="http://momjian.us/">Bruce Momjian</a> (USA), <a href="http://pgsnake.blogspot.com/">Dave Page</a> (UK), Greg Smith (USA), Jaime Casanova (Ecuador), and Koichi Suzuki (Japan).<br />
<br />
I don't have the definitive numbers yet but we had roughly <a href="http://www.flickr.com/photos/tellesr/6322484113/in/pool-1655373@N21/">250 participants</a> from the 5 regions of Brazil. I talked to a lot of participants that bring to me interesting success stories from their companies. One of the many interested success cases I heard of was from <a href="http://www.locaweb.com/">Locaweb</a> (hosting company) that migrated its DNS infrastructure from M$ database to PostgreSQL.<br />
<br />
We had three rooms, 29 sessions (4 sessions from <a href="http://www.timbira.com.br/">Timbira</a>) and a lot of new speakers. In the first day I was at room 1 because I was coordinating the sessions along the day. I had the opportunity to listen Alvaro and Greg talking about <a href="http://pgbr.postgresql.org.br/2011/palestras.php?id=65">locks</a> and <a href="http://pgbr.postgresql.org.br/2011/palestras.php?id=60">benchmarks</a>, respectively. Also, I was in the round table about PostgreSQL service opportunities in Brazil. In the second day, I attended to <a href="http://pgbr.postgresql.org.br/2011/palestras.php?id=15">Jaime's talk</a> (<a href="http://repmgr.org/">repmgr</a>) and <a href="http://pgbr.postgresql.org.br/2011/palestras.php?id=19">Dickson's</a> (extensions). At this year, I decided to submit a newbie tutorial that answers the frequently asked questions in the PostgreSQL mailing lists. Title is <a href="http://pgbr.postgresql.org.br/2011/palestras.php?id=42">Everything you want to know about PostgreSQL but were afraid to ask</a>. It was a challenge to keep the audience awake (1 hour and a half) after the lunch but I think I got it. Oh, I lost the <a href="http://pgbr.postgresql.org.br/2011/palestras.php?id=33">Dojo</a> (because it conflicted with my session). :( The last session I attended was that of <a href="http://pgbr.postgresql.org.br/2011/palestras.php?id=6">Lucio</a>. After that the awards. For the first time, brazilian community has decided to reward prominent members of the community. We had 5 categories (I was nominated in 4) and got one. Thanks for reconnaissance! The last session was the always-funny-session: lightning talks. 8 LTs with speak^H^H^H^H^Hstand-up comedians who entertained the participants (<a href="http://twitter.com/#!/lhcezar">@lhcezar</a> will stop working with databases and will start a career as singer).<br />
<br />
Thumbs up to: local (hotel), coffee-break and beer (200 liters).<br />
<br />
Hope to see you again next year!Euler Taveirahttp://www.blogger.com/profile/08335071854403271396noreply@blogger.com1R. Teixeira da Silva, 647 - Vila Mariana, Sao Paulo - São Paulo, 04002-033, Brazil-23.5733296 -46.6497524-23.575148600000002 -46.6522199 -23.5715106 -46.647284899999995tag:blogger.com,1999:blog-8625426919745210336.post-40928619402812357672011-03-28T23:55:00.002-03:002011-04-05T10:20:22.190-03:00PGBR 2011: Brazilian PostgreSQL ConferenceAfter a lot of discussing about the next nacional PostgreSQL event, we have finally been able to bang the gavel. This year the conference will take place in São Paulo, Brazil, from November 3th to 4th (November 2nd is holiday in Brazil). It consists of two days of technical talks (in English and Portuguese) from beginner to master.<br />
<br />
This is the first year that the conference will be held in a <a href="http://pgbr.postgresql.org.br/2011/evento.en.php">hotel</a>. (The last two editions were held in an university.) So if you don't live in São Paulo, you could stay in the <a href="http://pgbr.postgresql.org.br/2011/local.en.php">conference place</a>.<br />
<br />
There will be a new track (academic) and it will have more slots for case studies and advanced talks. There will be three simultaneous rooms.<br />
<br />
Two community members already confirmed presence: <a href="http://momjian.us/">Bruce</a> and <a href="http://twitter.com/alvherre">Álvaro</a>. Of course, I will be there too. ;)<br />
<br />
Stay tuned: <a href="http://pgbr.postgresql.org.br/">website</a> or <a href="http://twitter.com/#%21/search/%23pgbr2011">twitter</a> (#pgbr2011).<br />
<br />
See you in São Paulo.Euler Taveirahttp://www.blogger.com/profile/08335071854403271396noreply@blogger.com3tag:blogger.com,1999:blog-8625426919745210336.post-83748757756767419542010-11-29T17:27:00.000-02:002010-11-29T17:27:15.616-02:00Comment on catalog tablesWhile building some catalogs queries I always forgot what some column is. At this point I need to access the <a href="http://www.postgresql.org/docs/current/static/catalogs.html">documentation</a> 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 <strike>easy</strike> lazy way to achieve this. I checked the source (<a href="http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=doc/src/sgml/catalogs.sgml;hb=HEAD">catalogs.sgml</a>) to investigate how I could extract such information. I noticed that each section (that starts with <i>catalog-pg-</i>) 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 (<a href="http://dsssl.netfolder.com/">DSSSL</a> or <a href="http://www.w3.org/Style/XSL/">XSL</a>). Easy task? Not that easy.<br />
<br />
<b>DSSSL x XSL</b>. 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?<br />
<br />
First step was converting the SGML file to XML (XSL only works with XML). I used the <a href="http://openjade.sourceforge.net/doc/sx.htm">osx</a> (that is part of <a href="http://openjade.sourceforge.net/">openjade</a>). The <a href="http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=doc/src/sgml/catalogs.sgml;hb=HEAD">catalogs.sgml</a> file doesn't have a header (because it is part of the PostgreSQL documentation that provides its header in a separate file <a href="http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=doc/src/sgml/postgres.sgml;hb=HEAD">postgres.sgml</a>); I had to add the following 3 lines as header.<br />
<br />
<pre>$ cat /path/to/header.sgml
<!doctype chapter PUBLIC "-//OASIS//DTD DocBook V4.2//EN" [
<!entity % standalone-ignore "IGNORE">
<!entity % standalone-include "INCLUDE"> ]>
</pre><br />
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.<br />
<br />
<pre>$ cat /path/to/header.sgml /path/to/catalogs.sgml | osx -x lower > /path/to/catalogs.tmp
</pre><br />
At this point, I had the catalogs file in XML format. Looking at the generated file (<i>catalogs.tmp</i>), I noticed that some special characters (i.e. &, >, <) were not preserved. Fortunately, looking at the <a href="http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=doc/src/sgml/Makefile;hb=HEAD">doc/src/sgml/Makefile</a> I found this command:<br />
<br />
<pre>$ 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
</pre><br />
It converts some entities like <i>[foo ]</i> to <i>&foo;</i> and adds the DOCTYPE header at the second line.<br />
<br />
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 (<i>sect1 id="catalogs-overview"</i>) 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 <i>escape-string-sql</i> 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).<br />
<br />
<pre class="xslt" name="code"><?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>
</pre><br />
Let's check the output:<br />
<br />
<pre>euler@harman:~$ xsltproc /path/to/only-tables.xsl /path/to/catalogs.xml
</pre><pre class="sql" name="code">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;
</pre><br />
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 <i>catalog-pg-*</i>. Inside each section the <i>first</i> table contains the information we want to extract. Those tables can have <i>three or four</i> columns. The following XSL script is capable of extracting the catalog column descriptions. It uses the template <i>escape-string-sql</i> to escape sql strings too. Also, the template <i>build-comment-col</i> centralizes the COMMENT ON COLUMN output.<br />
<br />
<pre class="xslt" name="code"><?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></pre><br />
Let's check the output:<br />
<br />
<pre>euler@harman:~$ xsltproc /path/to/only-columns.xsl /path/to/catalogs.xml
</pre><pre class="sql" name="code">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;
</pre><br />
Checking the generated file, everything seems to be ok except a small detail: some pg_statistic columns presented the form <i>fooN</i> while there were 4 columns to its representation (<i>foo1</i>, <i>foo2</i>, <i>foo3</i>, and <i>foo4</i>). This is true for 4 columns of <i>pg_statistic</i>: <u>stakind</u>, <u>staop</u>, <u>stanumbers</u>, and <u>stavalues</u>. A hack transforms one "virtual" comment into 4 comments.<br />
<br />
Two templates were designed to expand the statistic columns (<i>expand-stat-col</i> and <i>build-comment-stat-col</i>) and another one to convert the string "Nth" to "1st", "2nd", "3rd", or "4th" (<i>convert-ord-number</i>). Also, some tests for those special statistic columns were added in the main template.<br />
<br />
The new version of only-cols.xsl is above. <br />
<br />
<pre class="xslt" name="code"><?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>
</pre><br />
<br />
Let's check the output:<br />
<br />
<pre>euler@harman:~$ xsltproc /path/to/only-columns-2.xsl /path/to/catalogs.xml
</pre><pre class="sql" name="code">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;
</pre><br />
The next step was integrating <i>only-tables.xsl</i> and <i>only-columns-2.xsl</i>. The <i>escape-string-sql</i> template is used in both scripts and the path to extract descriptions are the same. The resulting script <i>extract-catalog.xsl</i> is above.<br />
<br />
<pre class="xslt" name="code"><?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>
</pre><br />
The SQL file can be built with the following command:<br />
<br />
<pre>euler@harman:~$ xsltproc /path/to/extract-catalog.xsl /path/to/catalogs.xml > /path/to/catalogs.sql
</pre><br />
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 <i>foo</i>, those comments will only be visible to users connected to database <i>foo</i>. You can load it to your template database (<i>template1</i>, for example) and any new database created using that template will inherit those comments.<br />
<br />
<pre>euler@harman:~$ psql -f /path/to/catalogs.sql template1
</pre>Euler Taveirahttp://www.blogger.com/profile/08335071854403271396noreply@blogger.com2