What is the benefit of upgrading PostgreSQL?

Couple of times, in various places, I was asked: what is the benefit from upgrading to some_version.

So far, I just read release docs, and compiled list of what has changed.

But this is not necessarily simple – consider upgrade from 9.3.2 to 10.2. That's a lot of changes.

So, to be able to answer these questions faster in future, I created a site: Why upgrade PostgreSQL?.

Usage should be simple – pick from which version you want to upgrade, to which version you want to upgrade, and press gives me… button.

Hope you'll find it useful.

Fix for parallel scans on explain.depesz.com

On Friday, Zr40 reported on irc that explain.depesz.com doesn't show table/index names for Parallel Seq Scan nodes.

Checked it and found couple of other omissions of the same kind with other Parallel* scans.

Fixed (I hope) all of them in:

The change is not really big, but just figured I'll let you know.

Getting first and last values per group

Every so often someone needs solution to getting first (or couple of first) values for given column. Or last.

For some cases (when there is not many groups) you can use recursive queries. But it's not always the best choice.

Let's try to implement first() and last() aggregates, so these could be easily used by anybody.

Continue reading Getting first and last values per group

Generate short, random, textual IDs

Couple of people asked, on irc, about how to generate unique, short, ids for rows.

Since I wrote code for this for explain.depesz.com, I figured that instead of pointing to sources, and letting everyone to extract the functions, I will, instead, write a set of functions that what is needed in reusable way.

Continue reading Generate short, random, textual IDs

Pg::SQL::Parser

Some time ago I was looking (warning: post in polish) for someone to teach me proper parsing.

One of really great polish Perl programmers – Dozzie – reached out, and helped me. By the way – thanks a lot, Dozzie.

Based on what he taught me, I started writing module for parsing SQL queries. By that I mean proper parsing, with grammar (using Parse::Eyapp), and not set of regular expressions.

My parser is not ready. To say it lightly. Very lightly.

For now, it just knows how to parse the simplest queries like:

  • select 1;
  • select ‘a' as b;

I am working very slowly on it, so don't expect any usable version in any defined future. I will get there, eventually, but it is a project that I work on in my free time, after I finish everything else that I could work on in given moment.

This post is intended to announce that I'm working on it (so I will have kind of obligation to do it). And, if anyone is interested – I more than welcome all contributors/reviewers, and perhaps even critics 🙂

Final note – if you'll review the code, and want to comment on ugly list of regexps in Lexer – I know. It will be eventually replaced by one regular expression, but since it will be regular expression built by Regexp::Optimizer – it will not really be readable (though it will be faster than current approach).

Request for help with Python/Flask – Prośba o pomoc z Pythonem/Flaskiem

( wersja polska poniżej )

I (not-so) recently started to learn Python. To have some playground that I can work on, I decided to write simple website that will let me track scores of a card game that I play with my family – Canasta.

To write it, I chose to use Flask framework, so I learned at the same time both Python and Flask.

Final result (without layout, just functionality) is on github.

If any of you does write Python and/or Flask, I would greatly appreciate all comments. Even the harsh ones. If anything is wrong, or simply not really good – let me know – I'm treating it as a way to learn so all feedback would be good.

Just a word of warning – if you'll decide to look at it – you will be dealing with very bad Python code. Brace yourself.


Niedawno zacząłem uczyć się Pythona. Uczę się najlepiej robiąc coś, więc stwierdziłem, że zrobię prosty site do śledzenia wyników gry w którą gram z rodziną – kanasty.

Zdecydowałem, że użyję Flask'a – dzięki czemu uczyłem się jednocześnie i języka (Python) i frameworka (Flask).

Działająca wersja (bez wyglądu, sama funkcjonalność!) jest na githubie.

Jeśli znasz Pythona i/lub Flaska, byłbym bardzo wdzięczny za przejrzenie tego kodu i skomentowanie. Nawet zbluzganie. Jeśli cokolwiek jest źle, lub nie-za-dobrze, daj mi znać. To (ten soft) to dla mnie metoda na nauczenie się, więc każdy komentarz jest mile widziany.

Słówko ostrzeżenia jedynie: jeśli się zdecydujesz na to spojrzeć, miej świadomość, że to będzie bardzo zły kod w Pythonie. Tragiczny. Bądź gotów.

How to manage changes to your database?

Every now and then somebody asks how to make diff of database schemata.

Usual background is like: we have production database, and development database, and we want to see what is different on development to be able to change production in the same way.

Personally I think that such approach is inherently flawed. Why?

Continue reading How to manage changes to your database?