patch from Aleksander ‘A.L.E.C' Machniak.
Changes:
Some edits by me, mostly indentation.
SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk
i'm recently under impression that pg developers are overprotective. who do they protect?
us, users.
i mean – don't get me wrong – i live with, and (to some extent) thanks to the piece of marvelous technology which is postgresql ordbms.
i use it, i try to help other use it. i don't think there is any better database available now (at the very least – for my purposes).
but, just lately, i some to see some cases where decissions are made in a way to “protect us (dbas) from our faults".
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.
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.
edit: title was changed because apparently it was too offensive
the title might a bit too offensive, but perhaps it will make it more visible.
some time ago (march 2007) i asked on polish db-related newsgroup about things that people hate about their databases.
to give some example i wrote about 5 things that i hate (dislike?) about postgresql. today i looked back at this topic to check what has changed. many things did change. some things don't piss me anymore. some new things showed up, so, here we go – what i (personally) see as things to be fixed/removed soon to get nicer (for users) database system.
zapraszam na nowo-otwarty blog motoryzacyjny auta.cc.
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?
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.