select * from depesz;

• we use orm
• yes, and?
• and we have a problem with it…
• you already said that

now, imagine a simple scenario:

• objects table (let’s name it “topics”), which contains column: posts_count
• sub-objects table (posts), with foreign key to topics
• triggers which update posts_count on insert/update/delete on posts

simple? yes? well. it was. now, enter disaster: orm.

- MORE -

i needed a way to generate ed2k urls based on existing files on my harddrive.

ed2k link looks like this:

ed2k://|file|FILENAME|FILESIZE|CHECKSUM|/

filename and filesize are of course known, but what about checksum? i tried to find some ready program to calculate them, but failed. it might be because i spent something like 3 minutes on it, but anyway - i didn’t find it. so i tried to find algorithm description.

luckily there is a nice algorithms description. algorithms, as there apparently are two separate algorithms, not fully compatible with each other.

based on the information i was able to write a short perl script which does the job:

=> cat ed2ksum.pl
#!/usr/bin/perl -l
use Digest::MD4 qw(md4 md4_hex);open$f,pop or die$!;$c.=md4$b while sysread$f,$b,9728000;print uc md4_hex$c

yes, it is unreadable. but it works. first version was longer (about 15 lines), but then i decided to try to make it shorter. and shorter. and then even shorter. most probably it is not the shortest possible way, but i’m safisfied with it.

how does it work? simply:

=> ./ed2ksum.pl Slony-I-concept.pdf
E8715CD212CD75E0EE4B6C526D5BF36A

hope you’ll find it useful.

nixternal wrote about boost library for c++.

with it he was able to find the answer to title question in miliseconds (he didn’t specify how many, but let’s assume that is was less than 10 ms).

so i decided to check how fast can i do it in postgresql …

- MORE -

in most applications you have some variant of this table:

CREATE TABLE users (
id serial PRIMARY KEY,
username TEXT NOT NULL,
passwd TEXT
);

and, usually, the passwd stores user password in clear text way.

this is usually not a problem, but in case you’d like to add password encryption in database, there are some ways to do it - and i’ll show you which way i like most.

- MORE -

in postgresql 8.2, in contrib, is great new datatype called hstore.

if you’re not familiar with it - check the docs.

in short - this is indexable associative-array (hash) in one field.

i was given a task to convert some text field to hstore field.

- MORE -

so, there you have a users table, with a very basic structure:

Table "public.users"
Column | Type | Modifiers
-----------+---------+-----------
id | integer | not null
birthdate | date |
Indexes:
"x_pkey" PRIMARY KEY, btree (id)

then, you have a task: find a query that will return all users which have birthday tomorrow. how will you do it?

- MORE -

nice machine with 2 gb of ram, 800 megabytes in 2 logfiles. single word as search phrase. polish utf-8 locale (pl_PL.UTF-8), gnu grep 2.5.1. results?

=> time grep -in reloading postgresql-2007-10-22_000000.log postgresql-2007-10-22_120909.log
postgresql-2007-10-22_000000.log:40001:2007-10-22 10:50:13.528 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
postgresql-2007-10-22_120909.log:1215696:2007-10-22 12:15:21.769 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
real 1m21.212s
user 1m20.909s
sys 0m0.284s

same, check without -i:

=> time grep -n reloading postgresql-2007-10-22_000000.log postgresql-2007-10-22_120909.log
postgresql-2007-10-22_000000.log:40001:2007-10-22 10:50:13.528 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
postgresql-2007-10-22_120909.log:1215696:2007-10-22 12:15:21.769 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
real 0m1.147s
user 0m0.868s
sys 0m0.268s

after setting locale to C:

=> time grep -in reloading postgresql-2007-10-22_000000.log postgresql-2007-10-22_120909.log
postgresql-2007-10-22_000000.log:40001:2007-10-22 10:50:13.528 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
postgresql-2007-10-22_120909.log:1215696:2007-10-22 12:15:21.769 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
real 0m1.209s
user 0m0.896s
sys 0m0.316s

all tests were repeated many times to get all data in memory, and check for extreme values.

does anybody need another proof that locale “thing” is broken? of course it might be that only locale handling in grep is bad, but anyway - it’s still locale issue.

one of the more common problems new users have with postgresql (especially those that came from mysql background), is the lack of easy way to grant/revoke/do-something with many objects (tables/sequences/views) at once.

there are number of pages that deal with the problem, let’s just name some from #postgresql infobots:

• http://pgedit.com/public/sql/acl_admin/index.html
• http://www.archonet.com/pgdocs/grant-all.html

now, both of these pages have their benefits, but i’d like to show something simpler, yet (perhaps) more powerful.

instead of giving you the fish (figuratively speaking) i will give you the net and the skills so you’ll be able to do the magic yourself.

- MORE -

let’s assume we have a simple table:

Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
b | integer | not null
Indexes:
"test_pkey" PRIMARY KEY, btree (a, b)
"q" UNIQUE, btree (b, a)

now, let’s insert some rows to it:

# insert into test select * from generate_series(1,100) as a, generate_series(1,100) as b^J;
INSERT 0 10000

remove rows with a = b:

# delete from test where a = b;
DELETE 100

and prepare test-case by randomly removing some rows:

# delete from test where random() < 0.002;
DELETE 17

the question is - find all pairs of (a,b) where there is no row (a’,b’) where (a’=b and b’=a).

in other words - every row (a,b) should be paired. rows with a = 2 and b = 3, is paired by row with a = 3 and b = 2.

how to find incomplete pairs?

- MORE -

just recently we got another array for out main production database. this means - we will be able to add new tablespace, thus making everything go faster.

in theory - it’s nice. but which tables to move to the other?

the basic assumption is simple - index on table should not be on the same tablespace as the table itself. that’s easy. but - should we really put all tables on one tablespace, and all indexes on another?

we decided that the important things that should be “boosted” are seeks and writes. sequential reads are (in our situation) more or less irrelevant.

read on to check how we split the load.

- MORE -