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 🙂
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?
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.
luckymurali_81 on freenodes #postgresql had a problem. his query returns data in wrong order. what can we do about it?
ostatnio testowałem interesujący i dosyć prosty pomysł na jedzonko.
zaczęło się od tego, że przypadkiem usłyszałem w telewizji opis jakiejś potrawy: “to jest pieczona ryba (tu była jakaś nazwa, ale jej nie znam) marynowana w sake z syropem klonowym, zwróćcie uwagę na ten wytopiony tłuszcz – ryba musi był tłusta by się potrawa udała". i to było wszystko co usłyszałem. nie znałem proporcji, czasu, temperatury, nic.
ale – zawsze można poeksperymentować 🙂
Bugfixes:
Changes:
SVN repo at: http://svn.depesz.com/svn/analyze.pgsql.logs/trunk
let's imagine simple situation – you have postgresql server. configuration was fine-tuned, hardware is ok. yet the system is not really as fast as it should.
most common problem – slow queries.
second most common problem – fast queries, but too many of them. for example – i once saw a system which did something like this:
reason? very “interesting" orm.
now i'll show you how i deal with these kind of situations 🙂
now, some time ago i wrote about how to “hack" system with postgresql. today i'll write more about how to make pg database as safe as possible.
i'll go through several steps, tell you what's possible, what's easy, and what is not so easy 🙂
so, let's start the tutorial.
Continue reading securing your postgresql database
one of the nice (for some things) features of oracle is rownum() function.
i assume you know what it does – if you don't – i think a quick google will show it.
today i will show how to make rownum in postgresql. if you're not interested in rownum itself – please continue reading anyway, as some functions shown here have other uses as well 🙂
Continue reading rownum anyone? cumulative sum in one query?
< french translation is now available >
very recently i got a task which involved finding out what happened (what sql queries were called) on remote machine.
the problem was that i didn't have any kind of shell access to server – i could connect only to postgresql. the good side was that i had a superuser-level access.
so the task was quite simple – how to find and read a file from psql. on remote machine. with no access to ssh, ftp, nor anything like this.
this is when i got idea that i'll write more about it. some might say that you shouldn't write this kind of information. my view is that i'm not disclosing any secrets – i will be using only basic (remote) psql with superuser access. all things that i will describe in here are in documentation – you just have to know where to look for them.