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?
Month: November 2007
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.