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.
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.
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?