select * from depesz;

Waiting for 8.5 – let’s start

depesz — Fri, 03 Jul 2009 20:08:15 +0000

Everybody wrote that 8.4 was released, so it’s not a news now. But. Starting from yesterday, my own PostgreSQL reports it’s version like this: # select version(); [...]

explain.depesz.com – update

depesz — Thu, 25 Jun 2009 13:04:44 +0000

I just updated explain.depesz.com with bugfix, which changes the way Bitmap Index Scan and Bitmap Heap Scan are displayed. Apparently index and table names were not shown previously. Thanks go to Viktor Rosenfeld for spotting and reporting the bug.

Tips N’ Tricks – Generating readable reports with plain SQL

depesz — Tue, 23 Jun 2009 11:51:43 +0000

Let’s say you imported some data, but it contains duplicates. You will have to handle them in some way, but to make sensible choice on how to handle it, you need more information. So, let’s start. We have table: # \d users [...]

explain.depesz.com – update

depesz — Wed, 17 Jun 2009 15:48:18 +0000

I just updated explain.depesz.com with 2 new interface features: When you put mouse cursor over node, it will mark direct child nodes with When you click on node, all child nodes (even indirect) will be hidden, and the node you clicked on will be marked with Both features are directed towards people who analyze longer plans [...]

explain.depesz.com – update

depesz — Fri, 29 May 2009 12:16:19 +0000

I just modified the internals of explain.depesz.com. Now, it finally stores the plans in database (previously it stored the plans as files in dedicated directory). Effect for enduser is just that history page should load faster. But, having the data in database makes it possible to add more features. One such feature is already added – ability to [...]

explain.depesz.com – update

depesz — Wed, 27 May 2009 12:11:23 +0000

I just updated explain.depesz.com with the newest explain-parsing library version (Pg::Explain v 0.09). This version will hit CPAN mirrors in next couple of hours. Changes: Fix exclusive time calculations Make PE::Node understand Bitmap scans (heap and index) Add proper handling of nodes that were “never executed” Add ->is_analyzed method to PE::Node to make it easy to distinguish between EXPLAIN and EXPLAIN [...]

pgsql-tools/analyze.pgsql.logs.pl

depesz — Sat, 23 May 2009 18:40:34 +0000

Changes: Apply patch from Filip Rembiałkowski, which adds first header and last header display – for example to see time range the analysis was on. SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk

Waiting for 8.4 – final post (?)

depesz — Fri, 10 Apr 2009 20:54:15 +0000

For quite some time I’ve been posting about new features in 8.4. First post was over a year ago, on 16th of February 2008. Now, we just (yesterday) got nice surprise: beta is here: (depesz@[local]:5840) 12:39:15 [depesz] # select version(); [...]

Learning POE: HTTP-2-MUD proxy

depesz — Wed, 08 Apr 2009 18:42:05 +0000

Some years ago I learned of existence of (supposedly cool) POE framework for Perl. I tried to use it for some projects, but the learning curve proved to be fatal for my interest. All the time I felt that POE is great, it’s just that I’m too stupid to be able to actively use it. Time passed [...]

pgsql-tools/analyze.pgsql.logs.pl

depesz — Fri, 03 Apr 2009 22:23:18 +0000

Changes: Fix bug spotted by Selena Deckelmann %l is *number* not some string. SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk

Getting list of all children in “adjacency list” tree structure

depesz — Mon, 23 Mar 2009 21:08:24 +0000

So, you have a table which looks like this: # \d test Table "public.test" Column | Type | [...]

Waiting for 8.4 – no more -d in pg_dump!

depesz — Sun, 22 Mar 2009 19:06:58 +0000

Usually I write about new features in 8.4, but this time I’d like to write about feature that will be actually missing in 8.4. And thank God, it will be missing. On Mon, 09 Mar 2009 11:22:47 -0400 Greg Sabino Mullane wrote mail to pgsql-hackers list with his patch that removes -d switch from pg_dump. Later there [...]

Set operations in shell

depesz — Thu, 12 Mar 2009 21:40:50 +0000

I had this interesting case at work. We have imports of objects. Each object in import file has its “ID” (which can be any string). Same “ID” is in database. So the idea is pretty simple – we can/should check how many of IDs from import were in database. Unfortunately – we’d rather not really do [...]

How to find newest file with given name?

depesz — Mon, 02 Mar 2009 19:09:54 +0000

This post will probably be boring for you, but this is mostly just a reminder to myself, written in form of a blog post. So, I have a directory structure: /some/path/imported/DATE/TIME/file, where DATE is date of importing, in format YYYY-MM-DD, and TIME is time of importing, in format HHMMSS. So, example paths look like this: ./2009-02-26/143251/5a6d001b94e47960fe41a262f70ed96a ./2009-02-26/143321/8e45f68421dad6129914fe068dfa5748 ./2009-02-26/143407/aa04aa9c1e8f87b25fef98bd9a64e94d ./2009-02-26/143415/65180d1328e21959229e47b9288b6996 ./2009-02-27/083542/5a6d001b94e47960fe41a262f70ed96a ./2009-02-27/084906/aa04aa9c1e8f87b25fef98bd9a64e94d ./2009-02-27/084926/65180d1328e21959229e47b9288b6996 ./2009-02-27/155648/65180d1328e21959229e47b9288b6996 As you can [...]

find.best.tablespace.split.pl

depesz — Thu, 19 Feb 2009 22:00:09 +0000

Robert Treat reported an error with find.best.tablespace.split.pl. In some cases it could die with ‘division by zero’ error. Problem was solved using patch from Robert, which simply treats “0″ in number of rows fetched or written, as ‘0.001′. Additionally, I added a simpler way to specify multiple schemas to use tables from. Code is available in svn repository.

lpad() and rpad() gotcha

depesz — Mon, 09 Feb 2009 23:15:17 +0000

I was lately writing some program for a client of mine, which used UPC codes matching. Since the codes are given in various ways, there was decision to pad the codes with leading zeros – up to 12 characters. The code has been done, and worked like this: # select lpad('123456789', 12, '0'); lpad -------------- [...]

Waiting for 8.4 – parallel restoration of dumps

depesz — Mon, 09 Feb 2009 12:27:46 +0000

On 2nd of February Andrew Dunstan committed his patch (with editing by Tom Lane) that: Log Message: ----------- Provide for parallel restoration from a custom format archive. Each data and post-data step is run in a separate worker child (a thread on Windows, a child process elsewhere) up to the concurrent number specified by the new pg_restore command-line --multi-thread | -m [...]

Waiting for 8.4 – column level privileges.

depesz — Sat, 31 Jan 2009 16:09:06 +0000

On 22nd of January, Tom Lane committed patch by Stephen Frost, which adds column level privileges: Log Message: ----------- Support column-level privileges, as required by SQL standard. Stephen Frost, with help from KaiGai Kohei and others In case you don’t understand – it means that you can now grant/revoke privileges to specific columns of tables, not whole tables (this is [...]

Waiting for 8.4 – window functions

depesz — Wed, 21 Jan 2009 17:06:25 +0000

A long overdue post about new functionality. At this moment it is no longer such new, as it was committed on 28th of December (yes, I know, I should have written it earlier, Sorry). On this day Tom Lane committed patch by Hitoshi Harada which adds support for so called window functions: Support window functions a la [...]

Waiting for 8.4 – pg_stat_statements

depesz — Tue, 13 Jan 2009 15:37:26 +0000

On 4th of January, Tom Lane committed patch by Takahiro Itagaki, which adds new contrib module – pg_stat_statements: Log Message: ----------- Add contrib/pg_stat_statements for server-wide tracking of statement execution statistics. Takahiro Itagaki What is it for? Well, actually, it takes some work away from projects like pgFouine or my own analyze.pgsql.logs.pl. To this day, when you want/need to see some query statistics [...]

Waiting for 8.4 – remove system objects from \dX

depesz — Thu, 08 Jan 2009 19:12:05 +0000

On 6th of January Bruce Momjian committed patch by Greg Sabino Mulane, which fixes one of a very long standing annoyances in PostgreSQL – removes system objects from \dX commands in psql. Commit message: This makes all the \dX commands (most importantly to most: \df) work like \dt does, in that it requires a \dXS to see system [...]

Maintainable queries – my point of view

depesz — Sun, 04 Jan 2009 21:34:06 +0000

This post is basically just an reply to Josh Berkus blog post. Additionally, it refers to “SQL Coding Standards To Each His Own” by Leo Hsu and Regina Obe. Well, I’ve read what Josh wrote, and I though to myself – oh my, I must be doing something wrong, as I would never use long aliases. [...]

Waiting for 8.4 – Window Functions – teaser

depesz — Mon, 29 Dec 2008 18:51:16 +0000

Yesterday Tom Lane committed enormous patch, which had commit log: Support window functions a la SQL:2008. Hitoshi Harada, with some kibitzing from Heikki and Tom. … and that would be all. I will write more about it, its use cases, and some detailed examples but now I’m on vacation, and will stay here for some time. You can [...]

explain.depesz.com – update

depesz — Tue, 23 Dec 2008 15:39:58 +0000

After nearly 3 weeks of functioning, I just uploaded update to explain.depesz.com. New things are not really spectacular – I added help with proper explanation on what each color means and contact form in case somebody had ideas on what to do next (or complaints).

Waiting for 8.4 – Default values for function arguments + integer in any base

depesz — Tue, 09 Dec 2008 12:31:36 +0000

On 4th of December Peter Eisentraut committed patch by Pavel Stehule (with Peters tweaks) which adds default values for function arguments: Default values for function arguments Pavel Stehule, with some tweaks by Peter Eisentraut Well, the functionality is pretty obvious, so let’s see how to use it in real code. To do so I’ll write a simple functions [...]

Waiting for 8.4 – Visibility maps

depesz — Mon, 08 Dec 2008 12:37:19 +0000

Yeah. This one patch alone is worth upgrading to 8.4. On 3rd of December Heikki Linnakangas committed his patch. Commit message: Introduce visibility map. The visibility map is a bitmap with one bit per heap page, where a set bit indicates that all tuples on the page are visible to all transactions, and the page therefore doesn't need vacuuming. It [...]

explain.depesz.com.

depesz — Thu, 04 Dec 2008 12:33:09 +0000

Long time ago I wrote small program to filter EXPLAIN ANALYZE output, and add summary of time. A bit later (I guess, I don’t recall exact time line, it could have been earlier) Michael Glaesemann started explain-analyze.info – cool tool for checking what might be wrong with given plan. I’m not really happy with the emphasis Michael [...]

Getting list of most common domains

depesz — Sun, 30 Nov 2008 22:03:02 +0000

Today, on #postgresql on IRC, guy (can’t contact him now to get his permission to name him), said: I have a table called problematic_hostnames. It contains a list of banned hostnames in column “hostname” (varchar). I would like to display the top 10 troll ISPs based on this. Does PG have a way of spotting a [...]

Recovering from a lost PostgreSQL password.

depesz — Fri, 28 Nov 2008 21:36:40 +0000

Let’s say you’re in situation when you have to connect to PostgreSQL, but you have no idea on what password might be set. But some definitely is, as you get this error message: => psql Password: psql: FATAL: password authentication failed for user "depesz" Now what? If you have access to shell account on the machine PostgreSQL is running, [...]

depesz kocha ubezpieczycieli

depesz — Mon, 24 Nov 2008 13:18:05 +0000

Jakis czas temu pisalem o mojej przygodzie na Wegrzech i “jazdach” z HDI. Dzis inna historyjka/przypowiesc, nt. firmy “Inter Partner Assistance” i BRE Leasing. Stwierdzilem, ze kupuje nowy samochod. Nie zamiast tego lancera, tylko jako drugi – moja Pani potrzebuje miec czym jezdzic – ja tez. Drugi samochod jest rozwiazaniem logicznym. Poniewaz jestem pod wrazeniem dzialania Assistance [...]