(pgdba@[local]:5830) 21:45:26 [pgdba] # select version(); version ----------------------------------------------------------------------------------------------------------------------- PostgreSQL 8.3.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2) (1 row)
well, actually it is not released yet, but at the very least it's named 8.3 in cvs head 🙂
now, imagine a simple scenario:
simple? yes? well. it was. now, enter disaster: orm.
Continue reading how to check if given update is from trigger or why i hate orms?
nixternal wrote about boost library for c++.
with it he was able to find the answer to title question in miliseconds (he didn't specify how many, but let's assume that is was less than 10 ms).
so i decided to check how fast can i do it in postgresql …
Continue reading how many 1sts of any month were sundays – since 1901-01-01?
just lately we found interesting case about shared_buffers settings.
the database in question is rather simple:
the server we run it on is not really fancy:
Continue reading shared buffers and their impact on performance
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?
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:
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.