in previous post i described how to find overlapping ranges in a way that will work with indexes.
now, i'd like to discuss something else.
how to check how many ranges are in given moment?
find overlapping time ranges
hannesd on irc had a problem with finding overlapping date/time ranges.
basically – in postgresql there is “overlaps" operator, but unfortunately it doesn't use indexes:
pgsql-tools/analyze.pgsql.logs.pl
another patch from Aleksander ‘A.L.E.C' Machniak.
Changes:
- make it possible to sort by avg query runtime
- round() summary values
SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk
pgsql-tools/analyze.pgsql.logs.pl
patch from Aleksander ‘A.L.E.C' Machniak.
Changes:
- It's now possible to return analysis as html table (set $HTML_OUTPUT to 1)
- It's now easier to change sorting of generated analysis (set $SORT_ORDER)
- In html output, all numbers are formatted to %.2f.
Some edits by me, mostly indentation.
SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk
overprotective developers
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".
pgsql-tools/analyze.pgsql.logs.pl
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.
text to hstore migration
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.
glasbergen for today
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.