Category: Uncategorized

Waiting for 8.4 – final post (?)

For quite some time I've been posting about new features in 8.4. First post was over a year ago, on 16th of February 2008.

Now, we just (yesterday) got nice surprise: beta is here:

(depesz@[local]:5840) 12:39:15 [depesz]
# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3), 32-bit
(1 row)

Since I don't expect any new features in beta and release candidate stages, let me put some short summary.

Continue reading Waiting for 8.4 – final post (?)

Learning POE: HTTP-2-MUD proxy

Some years ago I learned of existence of (supposedly cool) POE framework for Perl. I tried to use it for some projects, but the learning curve proved to be fatal for my interest.

All the time I felt that POE is great, it's just that I'm too stupid to be able to actively use it.

Time passed by. Something like half a year ago, friend asked me if it would be possible for me to write a cool program – HTTP proxy for their MUD.

Continue reading Learning POE: HTTP-2-MUD proxy

Getting list of all children in “adjacency list” tree structure

So, you have a table which looks like this:

# \d test
                           Table "public.test"
  Column   |  Type   |                     Modifiers
-----------+---------+---------------------------------------------------
 id        | integer | not null default nextval('test_id_seq'::regclass)
 parent_id | integer |
 x         | text    |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "test_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES test(id)
Referenced by:
  "test_parent_id_fkey" IN test FOREIGN KEY (parent_id) REFERENCES test(id)

And you would like to easily get all children starting from given node?

Continue reading Getting list of all children in “adjacency list" tree structure

Waiting for 8.4 – no more -d in pg_dump!

Usually I write about new features in 8.4, but this time I'd like to write about feature that will be actually missing in 8.4. And thank God, it will be missing.

On Mon, 09 Mar 2009 11:22:47 -0400 Greg Sabino Mullane wrote mail to pgsql-hackers list with his patch that removes -d switch from pg_dump.

Later there was some discussion (20 mails) that extended the patch to remove also -D.

And now, today, Tom Lane committed:

Remove the -d and -D options of pg_dump and pg_dumpall.  The functionality
is still available, but you must now write the long equivalent --inserts
or --column-inserts.  This change is made to eliminate confusion with the
use of -d to specify a database name in most other Postgres client programs.
Original patch by Greg Mullane, modified per subsequent discussion.

This is great news. One less way a new user of pg (or one that doesn't read –help pages) can do himself harm, one less thing that is purely illogical.

Set operations in shell

I had this interesting case at work. We have imports of objects. Each object in import file has its “ID" (which can be any string). Same “ID" is in database.

So the idea is pretty simple – we can/should check how many of IDs from import were in database. Unfortunately – we'd rather not really do the comparison in DB, as it is pretty loaded.

Continue reading Set operations in shell

How to find newest file with given name?

This post will probably be boring for you, but this is mostly just a reminder to myself, written in form of a blog post.

So, I have a directory structure: /some/path/imported/DATE/TIME/file, where DATE is date of importing, in format YYYY-MM-DD, and TIME is time of importing, in format HHMMSS.

So, example paths look like this:

./2009-02-26/143251/5a6d001b94e47960fe41a262f70ed96a
./2009-02-26/143321/8e45f68421dad6129914fe068dfa5748
./2009-02-26/143407/aa04aa9c1e8f87b25fef98bd9a64e94d
./2009-02-26/143415/65180d1328e21959229e47b9288b6996
./2009-02-27/083542/5a6d001b94e47960fe41a262f70ed96a
./2009-02-27/084906/aa04aa9c1e8f87b25fef98bd9a64e94d
./2009-02-27/084926/65180d1328e21959229e47b9288b6996
./2009-02-27/155648/65180d1328e21959229e47b9288b6996

As you can see some of the files were imported many times.

Now, I need to find the latest import of given file.

So, I need a way to convert above list into:

./2009-02-26/143321/8e45f68421dad6129914fe068dfa5748
./2009-02-27/083542/5a6d001b94e47960fe41a262f70ed96a
./2009-02-27/084906/aa04aa9c1e8f87b25fef98bd9a64e94d
./2009-02-27/155648/65180d1328e21959229e47b9288b6996

Of course – with 10 imports, it's simple. But what if I had 10000 of them?

Luckily, it is rather simple:

find . -mindepth 3 -maxdepth 3 -exec basename {} \; | \
    sort -u | \
    while read DIR; \
    do \
        find . -name "$DIR" | \
        sort | \
        tail -n 1; \
    done

Of course I typed it originally as one-liner 🙂

While writing the post I realized I could do better:

find . -mindepth 3 -maxdepth 3 | \
    sort -r -t/ -k4,4 -k2,2 | \
    awk -F/ 'BEGIN{prev="/"} ($4!=prev) {print $0; prev=$4}'

Well. I understand the code, and what it does, but it doesn't change the fact that I'm not really fan of shell programming.

find.best.tablespace.split.pl

Robert Treat reported an error with find.best.tablespace.split.pl. In some cases it could die with ‘division by zero' error.

Problem was solved using patch from Robert, which simply treats “0" in number of rows fetched or written, as ‘0.001'.

Additionally, I added a simpler way to specify multiple schemas to use tables from.

Code is available in svn repository.

lpad() and rpad() gotcha

I was lately writing some program for a client of mine, which used UPC codes matching.

Since the codes are given in various ways, there was decision to pad the codes with leading zeros – up to 12 characters.

The code has been done, and worked like this:

# select lpad('123456789', 12, '0');
     lpad
--------------
 000123456789
(1 row)

Continue reading lpad() and rpad() gotcha