simple and nice

It’s quite common for me to issue some DDL statement in my query together with an INSERT, UPDATE or any statement that modified the state of the db and then rolling it back. Mostly for testing purpose of a new change or addition to the schema. It was something that I take for granted ever since I know PostgreSQL. Few days ago, someone mentioned about Transactional DDL which is actually unique to Postgres only. And today, a discussion followed up which explain in detail what is mean by Transactional DDL and a brief comparison with MySQL which doesn’t have it. I just can’t imagine if Postgres doesn’t have this.

Suse 10 is out there and everyone’s talking about it. I tried it and really hoped to make it as my default distro in the future. I’m currently using Archlinux and there’s nothing wrong with it but should go for the better one if it exists, right ? After using it for a while, I’d start to like it. Everything seem’s to work out of the box. Installing new linux system is never been that easy. Wifi (installing it on my new IBM laptop), X, sound etc are working out of the box without any hard work on my part. Just one thing, no postgresql packages was included in the 5 CDs release !!!

Quick check confirmed that postgresql was not included with this release. Failing to create the package myself, I looked their bugzilla and found bug #116688. It point me to these mailing list thread:-

Date: Mon, 12 Sep 2005 16:32:01 +0200 (CEST)
From: Christoph Thiel 
Message-ID: 
Subject: Re: [opensuse] Postgresql  Client Missing in 10rc1


On Mon, 12 Sep 2005, Gomez, Daniel wrote:

> I'm not sure if was intential or just an oversite, but suse10rc1 does
> not have the postgresql.
>
> Here I do a find against my suse installation directories. As you can
> see, version 10rc1 is quite light. Will the other packages be included
> at the official release?

Please add

http://download.opensuse.orgdistribution/SL-10.0-OSS-RC1/inst-source/

to your installation source and get all the postgres packages from there.
It's simply impossible to fit all packages on the 5 CDs, that's why some
packages are only in the ftp trees...

Regards
        Christoph 

So now I’m adding the new installation sources through YAST but I guess the 35MB ARCHIVES file made YAST waiting forever to get it finished. Still waiting ……..

I spent this night upgrading my postgresql 7.4.3 to postgresql 8.0.3. These has been on my to do list for quite some time but never get done. It’s been quite a pain lately since my hosting provider are running postgresql 8 and there’s problem when importing the dump back to my postgresql 7. Reading through the manual pages, it’s recommended to have both instance of the installation running on the machine before wiping out the old one. I try to figure out how to that in freebsd by looking on the net if someone have done it before. Not much I got actually but I guess adding the option PREFIX during make install would be suffice:-

# cd /usr/ports/database/postgresql80-server
# make PREFIX=/aur/local install

It complained that it need postgresql80-client but I had postgresql74-client installed and aborting. I go through the Makefile and edit the line:-

#WANT_PGSQL_VER=${PORTVERSION:C/([0-9][0-9]*)\.([0-9][0-9]*).*/\1\2/g}
WANT_PGSQL_VER= 74${:C/([0-9][0-9]*)\.([0-9][0-9]*).*/\1\2/g}

and run the make. this time, it success. So now I had postgresql-8 installed in my /aur/local (I had /aur which is mounted on seperate partition in my system to keep all my stuff). Then I create a new directory to hold the new database cluster and edit /aur/local/etc/rc.d/010.pgsql.sh to change the postgresql_data to reflect the new one:-

# mkdir /aur/pgsql8
# vi /aur/local/etc/rc.d/010.pgsql.sh

Edit the line postgresql_data="/aur/pgsql8/data" and try to init the db.

# /aur/local/etc/rc.d/010.pgsql.sh initdb
/aur/local/bin/initdb: not found

Looking into /aur/local/bin I just found two file named postmaster and postgres. When I looked back to the pkg_list while in the port directory, there’s no mention about initdb. I thought it was some bug with the port and same as before, searching on Google and Freebsd mailinglist archive doesn’t help much. So, I try logging to the #postgresql irc channel at freenode.net. Speaking about this irc channel, the community’s there were very helpfull. A guy told me that initdb was in postgresql80-client port, not in postgresql80-server. Quite strange since it’s nothing to do with the client. I disable the client, so that’s why I don’t have initdb.

# cd /usr/ports/postgresql80-client
# make PREFIX=/aur/local install
# /aur/local/etc/rc.d/010.pgsql.sh initdb

I’d also edit the postgresql.conf file and set the listening port to 5433 since 5432 already used by postgresql 7.

# /aur/local/etc/rc.d/010.pgsql.sh start

Yes !! The server were started. Then I try to start my postgresql-7 instance but it just stuck. Checking the /var/log/messages, I found this error:-

FATAL:  could not create semaphores: No space left on device
DETAIL:  Failed system call was semget(5432001, 17, 03600).  
HINT:  This error does *not* mean that you have run out of disk space.
t occurs when either the system limit for the maximum number of semaphore sets    (SEMMNI), or the syst
em wide maximum number of
semaphores (SEMMNS), would be exceeded.  You need to raise the respective kernel     parameter.  Alternatively, reduce PostgreSQL's

Asking on the channel again, some other guy hinting that I probably exceeding the system resources allocated for postgresql. Reading the manual, I know that I have to change the default kernel settings especially for the shared memory value. A bit scary for me. I try to decrease the number of maximum connection allowed to 4 (default is 40) and now both instance of the installation able to start up. The next step was to dump all the database from 7 to 8:-

# pg_dumpall -p 5432 | /aur/local/bin/psql -d template1 -p 5433 -U pgsql

The process took up about 15 minutes to restore my 1.5G database cluster on P4 2.4GHz with 512MB RAM. So far everything look’s good and I hope I can be freed from 7.4 soon. One final task is to create a symlink from my /usr/local/bin to /aur/local/bin since it’s not in my PATH.

# cd /usr/local/bin
# ln -s /aur/local/bin/psql psql8 
# ln -s /aur/local/bin/createdb createdb8
 ..............

Looking back at the process, upgrading PostgreSQL does not really hard, except for my problem with ports. At last, one big task done.

I got a very useful tips from PostgreSQL mailing list. If you put option -E to your psql command, it will display all the query that it used for the meta command such as \d in psql console. Cool. You can use the query if you need to peek into PostgreSQL catalog for some reason. For instance, command \d will give you the sql statement used by psql to display all object in the database.

-E
--echo-hidden
      Echo the actual queries generated by \d and other backslash commands. You can use this if you  wish  to include  similar  functionality  into  your  own  programs.  This is equivalent to setting the variable
      ECHO_HIDDEN from within psql.

some example:-

Tue Sep 20 ...> psql -E drupal
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

drupal=> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************