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](http://archives.postgresql.org/pgsql-odbc/2005-09/msg00161.php) or [google](http://www.google.com/search?q=%22client+encoding+mismatch%22+postgres). 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 SET client_encoding to LATIN1;

Here’s my list of pointers:

* [Power PostgreSQL](http://www.powerpostgresql.com/Docs/) – really detailed and up-to-date information on setting PostgreSQL runtime parameters. Don’t forget to check the annotated [postgresql.conf](http://www.powerpostgresql.com/Downloads/annotated_conf_80.pdf) with comments. BTW: Looking forward to reading the forthcoming book.
* [Collection of PostgreSQL performance tips](http://archives.postgresql.org/pgsql-sql/2002-04/msg00318.php) – 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](http://www.revsys.com/writings/postgresql-performance.html) – a decent link, explaining both runtime settings (some of this might be obsolete) and the general concepts: “EXPLAIN“, “ANALYZE“, indices.
* [Postgres 8.x docs](http://developer.postgresql.org/docs/postgres/runtime-config.html) – 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.

2. 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