Andrew's PostgreSQL blog

It's probably a bit of a pity that PostgreSQL's SHOW command doesn't take more than one setting name. If you want to see several settings at once, pg_settings is a useful thing to know about. Today I wanted to see some of the settings that affect memory usage. Here's the query I ran

select name,setting,unit 
from pg_settings 
where name ~ $$mem|buffers|cache$$

and the output looked like this:

         name         | setting | unit
----------------------+---------+------
 effective_cache_size | 5242880 | 8kB
 maintenance_work_mem | 1048576 | kB
 shared_buffers       | 1572864 | 8kB
 temp_buffers         | 32768   | 8kB
 wal_buffers          | 1024    | 8kB
 work_mem             | 131072  | kB 

I could also have used an IN query.

I was just reading something that reminded me of a piece of advice I have often given people over the last few years: Don't mix use of the "," operator with use of JOIN operators in your FROM clause. It's a recipe for endless confusion and errors, in my experience.

I know of shops that go further and ban the use of the "," altogether, and require only explicit joins. I'm old-fashioned enough not to go that far - back when I was first learning SQL we didn't have fancy things like outer joins, and it was a decade later that DB2 (the system I first learned SQL on) acquired them, so I'm kinda used to using them.

But I have learned to my cost that mixing the old syntax with explicit JOIN syntax confuses me and lots of other people as well. So do one or the other, but don't mix them - that's my advice.

I don't add tools to my toolbox very often. I tend to find that the time spent learning them outweighs the extra benefit I might obtain. Recently I found (or was pointed at) a couple of nice ones, though.

First is ack a nice grep replacement. Among other things it highlights what you're searching for, is automatically recursive, and knows how to ignore .svn and similar directories. Quite cute. It looks like it has a million bells and whistles, but it usually just Does the Right Thing (tm). "ack foo" usually gives me exactly what I want. So I just went looking for "CreateFunctionStmt" in the Postgres sources, and the output was really quite nice - much nicer than "grep -r" would have given me.

Second is Trang, a nifty tool to convert between the various languages for describing XML languages. So you can take a DTD and get an XML Schema or RELAXNG spec from it, for example. I used it to convert the (out of date) RELAXNG spec we had for the new XML explain output in Postgres into an XML Schema, and it worked perfectly. It will only output XML Schemas, it won't take them as input, but that still met my needs. It can also infer a schema from an example XML document, which is very nice indeed. There are limitations, to be sure, but it certainly beats starting from scratch, so it's a very definite time saver.

psql's tab completion mode can be extremely useful. It's also seriously annoying if you're cutting and pasting.

It would be nice if you could turn it off, but it's under the control of the readline library, which, as far as I can tell in a few minutes' googling, doesn't provide a way to toggle it via an API. The only way I can see to switch it is via the .inputrc file.

The psql manual advises that if you want to turn it off, you can put this in your ~/.inputrc file:

$if psql set disable-completion on $endif

But the trouble with that is that it turns it off all the time. However, you can tell readline to use an alternative file, via the environment variable INPUTRC. So I created a little file called ~/.inputrc-nocompletion containing the "set" line above, and then this worked:

INPUTRC=~/.inputrc-nocompletion psql andrew

and lo and behold, no completion just for that session. But that's a pain in the neck to have to type. To the rescue: bash's alias facility. I set up this alias:

alias npsql="INPUTRC=~/.inputrc-nocompletion psql"

So now, if i use the command npsql, I get tab completion turned off, and if I use the normal psql command it's on. It's not perfect (perfect would be if I could use a psql \ command to toggle it), but it's better than nothing.