Tadek’s Blog

While connecting to PostgreSQL using UnixODBC (from R, but this doesn’t matter) I got the following error:

[unixODBC]client encoding mismatch

It seems that there is some problem with PostgreSQL 8.1 and the encoding names: see this post or google. It looks that UTF8 used by default with the new Postgres is not compatible with old ODBC drivers. I am not sure where the problem really is, but a quick fix is to change default encoding for the user connecting to the database:

alter user <user> SET client_encoding to LATIN1;

Here’s my list of pointers:

• Power PostgreSQL - really detailed and up-to-date information on setting PostgreSQL runtime parameters. Don’t forget to check the annotated postgresql.conf with comments. BTW: Looking forward to reading the forthcoming book.
• Collection of PostgreSQL performance tips - a shopping lists of tips (both configuration as well as application design). The posts is a bit old, but many things (ecpesially the more general ones are still very relevant).
• Performance Tuning PostgreSQL - a decent link, explaining both runtime settings (some of this might be obsolete) and the general concepts: EXPLAIN, ANALYZE, indices.
• Postgres 8.x docs - explains runtime parameters in some detail.

A few links I found useful while installing DB2 on Debian/Gentoo, etc:

• An alternate method of installing DB2 on Unix platforms
• RedBook: Up and Running with DB2 on Linux

I recently had to upgrade my PostgreSQL 7.4 to 8.0 in Debian and learned that there is a cool way of doing this. This procedure is described in /usr/share/doc/postgresql-common/architecture.html.

In a nutshell:

1. Install the new version in packages: postgresql-*-8.0. Mote that all the pg_* tools still point to the old versions.
2. Make sure that both servers are up and running.
3. Run: “pg_upgradecluster 7.4 main” to upgrade the old database to the new one.
4. Now all the pg_* tools point to the new versions, also the new server will listen on the standard port. Check if everything works fine.
5. Run “pg_dropcluster 7.4 main” to delete the old database.

Cool. I wonder how Gentoo does this

Cool. It looks that PostgreSQL8.0 supports XML as an additional extension (contrib driectory). In gentoo you can compile it specifying +xml2 option.

Now how to use it: 1. you need to create xml functions in a database using a supplied script, most typically /usr/share/postgresql/contrib/pgxml.sql. Note that this script has been created (customized) by autoconf and contains a hard-coded location of your pgxml.so library, typically: /usr/lib/postgresql/pgxml.so.

1. If you get no error…. well that’s it

See this link and the documentation contrib/xml2 for more details.

BTW: If your database stores XML as binary strings (bytea) you must convert them to normal strings using encode(<byteacolumn>, ‘escape’);

Postgres is strange. If you make a query

select min(column_with_index) from table;

the database performs a sequential scan - you can only imagine how long it takes on a 10M record database. This seems to be a feature (!@!#!@), but indices can will used if you use the following workaround:

select column_with_index from table order by 1 (desc) limit 1;

This trick is explained here.

A drawing tool generaring UML diagrams from Postgresql database in DIA format, HTML and a few others. Not perfect, but quite neat and the diagrams can be eidted with DIA (there also exists a plugin to generate a schema from those DIA UML diagrams).

PostgreSQL Autodoc