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.

finding missing pairs

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?

Continue reading finding missing pairs

pgsql-tools/analyze.pgsql.logs.pl

Changes:

  1. doesn't use temp files – does everything in-memory. makes the whole process *way* faster
  2. allow setting header information using postgresql log_line_prefix syntax
  3. removes dependency on readonly perl module – it is not really common module, and the functionality in this program is very limited
  4. allows setting database to track as command line option, or track sqls from all databases

SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk

“FATAL: Ident authentication failed”, or how cool ideas get bad usage schemas

UPDATE (2012-06-24): Version 9.1 of PostgreSQL renamed ident to peer (for local connections). So if you're having errors about “Peer authentication failed" – it is the same as “Ident authentication failed", and all described in this blogpost is still relevant.

ever seen one of those? i mean the “fatal: ident authentication failed"?

or, ever seen anyone having this problem when connecting to postgresql?

how often is this problem related to debian/post-debian linux distributions? 99%? 100%?

on #postgresql on irc.freenode.net it is the most common problem. my own irc logs show that “ident" showed over 300 times over last 41 days. now, that's something. and how come we have this problem? what can be done with it? read on.

Continue reading “FATAL: Ident authentication failed", or how cool ideas get bad usage schemas