Adding Color to the Console: Round 2: Highlight vs. Pygments vs. Source-highlight

A month ago I compared the Pygments and Source-highlight packages for use as a syntax highlighting tool. A commenter pointed me toward another package called Highlight. So let's compare those three, based on the same criteria as last time:

	Pygments	Source-highlight	Highlight
Debian package	python-pygments	source-highlight	highlight
Dependencies	Python	C++, Boost	C++
License	BSD	GPL 2+	GPL 3+
Input Languages	136	30	143
Output Languages	~11	~6	~9
Styles	16	5	40
Performance test	6 min	64 sec	67 sec

So for interactive use, it looks like a pretty close call between Source-highlight and Highlight, unless you need one of the additional input formats supplied by Highlight. It appears that Highlight supports SGML files out of the box, unlike the other two, which could make it interesting for me.

How to find all tables without primary key

Getting a list of all tables that don't have a primary key is occasionally useful when you need to update a legacy schema or repair someone else's database "design". There are a few recipes out there that peek into the PostgreSQL system catalogs, but here is a way that uses the information schema:

SELECT table_catalog, table_schema, table_name
    FROM information_schema.tables
    WHERE (table_catalog, table_schema, table_name) NOT IN
          (SELECT table_catalog, table_schema, table_name
               FROM information_schema.table_constraints
               WHERE constraint_type = 'PRIMARY KEY')
      AND table_schema NOT IN ('information_schema', 'pg_catalog');

The last line is useful because many predefined tables don't have primary keys.

Since you're using the standardized information schema, this query should be portable to other SQL database systems. The identical query works on MySQL, for example.

The UserVoice Votes Have Materialized

A few weeks ago, I started a PostgreSQL feedback forum at UserVoice. The votes have stabilized now, so it's time to take a first look.These are the five most requested features:

1. Hot standby
2. Materialized views
3. Synchronous replication
4. Parallel queries
5. Better administration and monitoring tools

1, 3, and 5 should not be surprises; we already saw them among Josh's development priorities. Parallel queries are also mentioned occasionally. But it's interesting to see materialized views so high.

I remember many years ago, materialized views were a somewhat hot topic, but things have cooled down significantly now. In a broader sense, materialized views are queries caches, and I did hear something about that at PGCon. In the meantime, Jonathan Gardner's article Materialized Views in PostgreSQL appears to remain the most frequently cited treatment on the subject. (The original link is down, unfortunately.) The PGCon 2008 tutorial Materialized Views that Really Work by Dan Chak is also worth looking at.

So, who's up for implementing built-in materialized views/query caching?

Also, if you haven't done so, submit your suggestions and votes at the UserVoice forum.

How can I get the compiler to check my SQL queries?

Over at Coding Horror, I found this piece of code:

var posts = 
  (from p in DB.Posts
  where 
  p.PostTypeId == PostTypeId.Question &&
  p.DeletionDate == null &&
  p.Score >= minscore
  orderby p.LastActivityDate descending
  select p).
  Take(maxposts);

This is C# code with a piece of infrastructure called LINQ to SQL. And I learn:

The big hook here is that this is code the compiler actually understands. You get code completion, compiler errors if you rename a database field or mistype the syntax, and so forth. Perhaps best of all, you get an honest to goodness post object as output!

Is there any open-source stack, possibly involving PostgreSQL, that can do that? And I don't mean ECPG, which can check the SQL syntax, but does not verify the existence and types of tables, columns, etc. at compile time.

Any ideas?

Adding Color to the Console: Pygments vs. Source-highlight

The other day I write about code syntax highlighting with less and source-highlight, when someone pointed out the Pygments package, that can do the same thing. This called for

the great source-highlight vs. pygments face-off

Installation

Source-highlight is in C++ and needs the Boost regex library. About 3.5 MB together. Pygments is in Python and has no dependencies beyond that. About 2 MB installed. No problem on either side.

In Debian, the packages are source-highlight and python-pygments. Note that the Pygments command-line tool is called pygmentize.

Source-highlight is licensed under the GPL, Pygments under a BSD license.

Getting Started

pygmentize file.c

writes a colored version of file.c to the standard output. Nice.

source-highlight file.c

writes a colored version of file.c to file.c.html. As I had written before, the correct invocation for this purpose is

source-highlight -fesc -oSTDOUT file.c

That makes pygmentize slightly easier to use, I suppose.

Supported Languages

Source-highlight supports 30 languages, Pygments supports 136.

Source-highlight can produce output for DocBook, ANSI console, (X)HTML, Javadoc, LaTeX, Texinfo. Pygments can produce output for HTML, LaTeX, RTF, SVG, and several image formats.

Source-highlight supports styles, but only ships a few. Pygments supports styles and filters, and ships over a dozen styles.

So more options with Pygments here.

Also note that Pygments is a Python library that can be used, say, in web applications for syntax highlighting. This is used in Review Board, for example. Source-highlight is just a command-line tool, but it could of course also be invoked by other packages. Horde uses this, for instance.

Speed

To process all the C files in the PostgreSQL source tree (709271 lines), writing the ANSI console colored version of each file.c to file.c.txt:

source-highlight

25 seconds

pygmentize

5 minutes

So for interactive use with less, source-highlight is probably still the better option.

Robustness

pygmentize gave me a few errors of this sort during the processing of the PostgreSQL source tree:

*** Error while highlighting:
UnicodeEncodeError: 'ascii' codec can't encode character u'\xb7' in position 83: ordinal not in range(128)
  (file "/usr/lib/python2.5/site-packages/Pygments-1.0-py2.5.egg/pygments/formatters/terminal.py", line 93, in format)

*** Error while highlighting:
UnicodeDecodeError: 'utf8' codec can't decode bytes in position 196-198: invalid data
  (file "/usr/lib/python2.5/encodings/utf_8.py", line 16, in decode)

That probably shouldn't happen.

source-highlight gave no spurious errors in my limited testing.

Miscellaneous

Source-highlight can highlight its own configuration files, which are in a custom language, and Pygments' configuration files, which are in Python. Conversely, Pygments can of course highlight its own configuration files, but doesn't know what to do with those of Source-highlight.

Summary

I will stick with Source-highlight for interactive use, but Pygments is a great alternative when you need more formatting options or want to integrate the package as a library.

Solid-State Drive Benchmarks With Latency Measurements

After my previous round of SSD benchmarks, I was asked to repeat the tests with a newer bonnie version to get some latency measurements. Here are the results:

Version      1.96   ------Sequential Output------ --Sequential Input- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
hdd             64G           13939   2 10973   2           56604   5  42.0   1
Latency                       20356ms    3310ms             60419us     593ms
hdd             64G           13957   2 10933   2           56628   5  42.7   1
Latency                        4956ms    2243ms             66937us     523ms
hdd             64G           13938   2 10958   2           56613   5  42.7   1
Latency                        5259ms    2577ms             94416us     609ms
hdd             64G           13906   2 10976   2           56602   5  42.5   2
Latency                       20689ms    2510ms             56957us     593ms
hdd             64G           13914   2 10990   2           56635   5  42.2   2
Latency                        5266ms    2357ms             94285us     518ms
ssd             48G           106763  19 63722  12           254350  21  1825  72
Latency                       15396ms     421ms             53517us    7830us
ssd             48G           110696  19 63812  10           254238  21  1971  69
Latency                       12050ms     456ms             10615us    1334us
ssd             48G           107193  19 64260  11           254179  21  1823  79
Latency                       14950ms     789ms             10542us    8046us
ssd             48G           108723  19 63380  11           254226  21  1801  74
Latency                       14118ms    1720ms             40106us    1477us
ssd             48G           108848  19 63363   9           254035  21  1960  75
Latency                       15320ms    1717ms             10895us    1387us
hdraid          64G           280086  58 115494  21           450230  40 552.0  49
Latency                        1685ms    1325ms               619ms   33518us
hdraid          64G           283360  55 115566  20           467900  45 589.1  42
Latency                        1738ms    1109ms               298ms   33042us
hdraid          64G           285003  56 115642  20           460394  43 571.2  37
Latency                        1890ms    1272ms               759ms   46262us
hdraid          64G           286838  56 115059  20           453657  43 570.1  43
Latency                        1808ms    1065ms               513ms   65779us
hdraid          64G           291125  57 116871  21           445977  43 589.4  40
Latency                        1081ms    1057ms               577ms   97685us

I have posted an HTML version of this table at http://wiki.postgresql.org/wiki/User:Petere/SSD_benchmarks, for easier viewing.

Legend: hdd = single hard disk, ssd = Intel X25-E, hdraid = 8 hard disk RAID 10 array

The random seek numbers aren't quite the same as in the last test. I have at times noticed deviations of up to 50% over many measurements.

So the SSD beats everything else in latency, but not by much in the sequential tests. It's difficult to say, however, what exactly these numbers might mean in practice.

A side observation is the 75% or so CPU utilization during the random seek test on the SSD. With multiple disks, you might actually end up exhausting the CPUs before the disks!?!

Adding Color to the Console: Code Syntax Highlighting with less and source-highlight

Syntax highlighting is a standard feature of every nontrivial editor, but if you just want to look at a file or the output of a command, then you will often only stumble upon niche solutions such as colordiff, or you end up using vim as your file viewer. But one very flexible program that apparently doesn't get much attention is source-highlight, available from the GNU project or as a Debian package. Besides being able to syntax-highlight all the usual programming and markup languages, it can also produce various output formats such as HTML, LaTeX, and of course plain text.

The standalone usage is a bit bizarre, because it tries to produce HTML by default and write the output to a file named infile.html. To produce colored plain text on standard output, use something like

source-highlight -fesc -oSTDOUT FILENAME

To get the most out of this, integrate it with the less pager program, so all the files you look at are automatically colored. Create a file .lessfilter in your home directory with the following contents:

#!/bin/sh

source-highlight -fesc -oSTDOUT "$1" 2>/dev/null

and make this file executable. Be sure to redirect the error output away as shown, so that you don't get annoying messages when source-highlight complains in case it doesn't know the format of the file. Then put the following in your shell startup file (.bashrc or .zshrc):

export LESS="-R"
eval "$(lesspipe)"

The first line makes sure that less will show colors, and the second line sets things up so that the .lessfilter file is invoked.

That's it. It recognizes the file type by the file name extension, so try anything like

less somefile.c
less somefile.py
less somefile.patch

to see the colors. It won't work when less is at the end of a pipe; I have some ideas on how to work around that, which I will post here later.