Month: August 2007

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.

Continue reading better results paging in postgresql 8.2

łosoś w sake [polish only]

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ć 🙂

Continue reading łosoś w sake [polish only]

analyze.pgsql.logs

Bugfixes:

  1. set locale to C for sorting – otherwise sort results will not be really sorted (let's everybody “thank" locale subsystem)
  2. fix a bug that prevented *last* sql-type to be printed

Changes:

  1. add rule for normalizing so: SELECT * FROM table WHERE field = -12 will be normalized to SELECT * FROM table WHERE field = ? instead of “… field = – ?"
  2. remove extra spaces from normalized version – they did happen sometimes 🙁

SVN repo at: http://svn.depesz.com/svn/analyze.pgsql.logs/trunk

effective finding queries to optimize

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:

  • select id from table;
  • for every id do:
  • select * from table where id = ?

reason? very “interesting" orm.

now i'll show you how i deal with these kind of situations 🙂

Continue reading effective finding queries to optimize

rownum anyone? cumulative sum in one query?

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?

“hacking” with postgresql

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

Continue reading “hacking" with postgresql

psql prompt issue

i tend to use more (than standard) informative prompts.

whether it's shell, or psql i set it up to match my needs.

for the longest time i used psql prompt:

\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] %[%012%]%x%# '

(this line should be in .psqlrc).

it gives some information:

(depesz@[local]:5810) 15:26:06 [depesz]
#

at a glance i know what user i am conencted as, to which machine, over which port, what database i'm connected to, and what's current time.

unfortunately this prompt had one issue – whenever i used history, and then something like <ctrl-a> (go to begining of line) something weird happened – cursor got distorted, and it looked like it was on second letter, while in fact it was on first (hard to explain, try it for yourself :).

it was painful, but since i couldn't do anything about it, i just learned to live with it.

now, due to some new environment setup, i decided that i've got enough of it. i tried to find any help in manual (i was looking for something like \[ and \] from bash prompting), but to no avail.

i did find out that the problem lies in %[012%]. i tried to give there literal <enter> (thus making \set statement multi-line), but it didn't work.

and then i simply gave there standard “\n". and it was it – now i have the prompt the way i like it, with no side effects on command line edition.

final prompt1, in case you're interested:

\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x%# '