new commit info from cvs:
Log Message: ----------- Stamp releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20.
it doesn't mean that we will see them today, but it means that we should be getting them really soon now 🙂 (at least that's what i hope)
Author: depesz
31337 vision express / super optyk [polish only]
znajomy zadzwonił i poprosił bym mu nagrał jakiś serial w tv.
nagrałem i w trakcje oglądania spojrzałem na reklamy. pojawiła się reklama “vision express" gdzie dają zniżki procentowo takie ile ma się lat.
reklama jak reklama, ale to co mnie całkiem powaliło to wygląd tej planszy do sprawdzania wzroku (litery różnej wielkości).
oto 2 “screenshoty" z tej reklamy:
drugie zdjęcie jest nieostre (kamera w reklamie była w ruchu), ale pokazuję, że na górze jest “O".
powstaje pytanie – czy ten co zatwierdzał reklamę zwrócił uwagę na żarcik? z treści reklamy nie wynika by miała być ona “z przymrużeniem oka".
avoiding “empty” updates
just recently i saw a very interesting situation.
one table in customer database (with about 1.5 milion rows) was *very often* updated. using series of simple:
update table set field = .., where id = ...
updates always updated 1 record, search was using primary key, so it was quite fast.
what was strange was the fact that the table get about 20 times more updates then the next most updated table.
after careful checking i found out that a lot (about 60-70%) of the updates actually didn't change anything!
they were simply setting values that were already there.
so, i started to think about hwo to avoid this kind of situation.
and this is what i found out.
analyze.pgsql.logs
Bugfixes:
- removed warning in regexp
- change multiple placeholders into 1 (??? => ?)
Changes:
- change ‘NULL' values to placeholders
- add handling and normalization of cursor declarations and fetches.
SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk/
(postgre)sql gotchas
i would like to write about a things that are not really errors by themselves – they are simply situations when database acts in undesired way. a way that's perfectly logical, but where the logic is not always clearly seen “at first sight".
so, don't expect any postgresql-bashing. if you want some, indicate so in comments, i'll find some things to bash postgresql for.
but in here i'd rather bash us – people – for making mistakes. the ones that are not really easy to see as mistakes for database.
indexable “field like ‘%something'” – update
thomas reiss just published his follow up to my post about indexed searches in “like ‘%something'" cases.
i don't understand french so i can't tell what exactly he wrote, but i understand c code and graphs 🙂
basically – he wrote c function that does reverse() of strings, and benchmarked it against pl/pgsql and pl/perl versions. of course c version is the fastest, but just take a look at how much faster it is. and how simple it is.
how many transactions per second?
i wanted to know how many transactions per second is my machine processing.
how to do so? a simple select to pg_stat_database will do the job (actually 2 selects 🙂
but since i have to write it anyway, perhaps i can/should make it so it will print the current value continuously?
and, while i'm at it, some kind of graph wouldn't be bad 🙂
postgresql tips & tricks
faber4 on irc asked about how to get ascii-based sorting, while his postgresql was initdb'ed with utf-8 based locale (en_US.UTF-8 to be exact).
what can we do about it?
better results paging in postgresql 8.2
some time ago merlin moncure wrote about one of new features of postgresql 8.2 – row-wise comparison.
i also read about it, but at first didn't find anything very useful about it – after all it doesn't give you any new functionality. any kind “(a, b, c) > (…)" can be written with standard column-based operators. so it's basically just a syntactic-sugar.
that's true. but merlin pointed me to not-so-obvious benefit – basically it allows much better paging of results. how? let's see.
postgresql tips & tricks
luckymurali_81 on freenodes #postgresql had a problem. his query returns data in wrong order. what can we do about it?