some time ago i wrote about potential (or not potential, depending on your situation) problem with updates which do not update anything.
now, andrew dunstan wrote c version of trigger which skips such updates.
so, let's test how well it works.
Tag: postgresql
how to check if given update is from trigger or why i hate orms?
- we use orm
- yes, and?
- and we have a problem with it…
- you already said that
now, imagine a simple scenario:
- objects table (let's name it “topics"), which contains column: posts_count
- sub-objects table (posts), with foreign key to topics
- triggers which update posts_count on insert/update/delete on posts
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?
how many 1sts of any month were sundays – since 1901-01-01?
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?
shared buffers and their impact on performance
just lately we found interesting case about shared_buffers settings.
the database in question is rather simple:
- 3 tables
- a bit over 60 gigabytes
- around 150 million rows
the server we run it on is not really fancy:
- 2, single core, 3ghz xeons
- 16 gb of ram
- 6x 72gb, scsi, 15krpm discs in low-end hardware raid 10.
Continue reading shared buffers and their impact on performance
time ranges in postgresql – part 2
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:
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".
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.
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.