encrypted passwords in database

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.

Continue reading encrypted passwords in database

what should be fixed in postgresql

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.

Continue reading what should be fixed in postgresql

who has birthday tomorrow?

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?

Continue reading who has birthday tomorrow?

i just “love” locale issues.

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.

grant XXX on * ?

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:

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.

Continue reading grant XXX on * ?

find.best.tablespace.split.pl

Changes:

  1. get connection settings from command line:
    ./find.best.tablespace.split.pl “dbi:Pg:dbname=depesz;host=127.0.0.1;port=12345" depesz
  2. calculate only for public schema (can be easily changed)
  3. distribute indexes as well – always put indexes on another tablespace than table
  4. add comment in generated file about total filesizes in tablespaces *after* migration – so it will be clearer on what to move

svn is located here.