select * from depesz;

Waiting for 8.5 – “GRANT ALL”

depesz — Sat, 07 Nov 2009 11:54:43 +0000

One of the most common (or perhaps even the most common) question people have regarding PostgreSQL, is how to change privileges for all tables. Usually they want something like MySQL’s: GRANT ... ON database.* TO ... Which is interesting as it is actually 2 distinct features in one: grant privileges on all existing tables automatically grant privileges on all tables [...]

Waiting for 8.5 – ’samehost’ and ’samenet’ in pg_hba.conf

depesz — Mon, 02 Nov 2009 00:45:14 +0000

October, finally. “Only” 1 month of backlog in new features in 8.5, but I’m getting there So, on 1st of October Tom Lane committed patch by Stef Walter: Log Message: ----------- Support "samehost" and "samenet" specifications in pg_hba.conf, by enumerating the machine's IP interfaces to look for a match. Stef Walter For some reason I cannot find it in mailing [...]

Waiting for 8.5 – DO

depesz — Sun, 01 Nov 2009 16:09:24 +0000

On 22nd of September, Tom Lane committed a patch by Petr Jelinek: Log Message: ----------- Implement the DO statement to support execution of PL code without having to create a function for it. Procedural languages now have an additional entry point, namely a function to execute an inline code block. This seemed a better design than trying to hide the transient-ness [...]

Czemu jestem taki ułomny? (wylgaryzmy, polish only)

depesz — Fri, 30 Oct 2009 15:07:43 +0000

Kurde. Mam w piwnicy żarówkę, ale zapala się ją jedynie kontaktem na dole. Czyli, jak wieczorem schodzę do piwnicy, to muszę zejść po ciemnych schodach i dopiero wtedy mogę zapalić światło. Dodatkowo – dzieciaki się boją chodzić do piwnicy bo ciemno. Moja pani, marudziła kilka razy, że trzeba coś z tym zrobić. No więc zrobiłem. Pojechałem [...]

Calculating backlog of events to handle

depesz — Thu, 29 Oct 2009 13:37:04 +0000

Yesterday on my favorite IRC channel fooqux asked interesting question. I took some more questions, and here is problem description: We have a system which, every 5 minutes, takes a number of tasks to be done. Tasks are uniform. Within 5 minutes we can handle at most 100 tasks. Given the history of number of tasks [...]

Koniec romansu ( polish only )

depesz — Thu, 29 Oct 2009 09:24:03 +0000

Parę lat temu, gdy kupowałem swój pierwszy samochód, poszukałem i znalazłem Mitsubishi Space Star. Fajne autko, niedrogie, wygodne. Kupiłem. I byłem zadowolony. Potem Space Star poszedł do ludzi a ja kupiłem Lancera Kombi. Potem dokupiłem sobie zabawkę – Colta CZC. I byłem cały czas zadowolony. Mitsubishi jeździły dobrze, miały genialny Assisstance, a drobne problemy w rodzaju ludzi [...]

Waiting for 8.5 – changes in hstore

depesz — Thu, 15 Oct 2009 12:18:53 +0000

Andrew Gierth (RhodiumToad on irc) wrote, and Tom Lane committed: Assorted improvements in contrib/hstore. Remove the 64K limit on the lengths of keys and values within an hstore. (This changes the on-disk format, but the old format can still be read.) Add support for btree/hash opclasses for hstore --- this is not so much for actual indexing purposes as to [...]

Waiting for 8.5 – MOVE {FORWARD,BACKWARD} X

depesz — Thu, 15 Oct 2009 11:18:25 +0000

On 29th of September (I know, there is a backlog – I’ll work on it, I promise), Tom Lane committed another patch from Pavel Stehule: Allow MOVE FORWARD n, MOVE BACKWARD n, MOVE FORWARD ALL, MOVE BACKWARD ALL in plpgsql. Clean up a couple of corner cases in the MOVE/FETCH syntax. Pavel Stehule Description is pretty obvious, but [...]

Weather where I live

depesz — Wed, 14 Oct 2009 07:53:24 +0000

Total surprise. Couple of days ago my wife was driving open convertible, and today we have around 0° (celsius) and snow. And it looks rather nice. View from my home entrance: and from my office in basement:

Speeding up dump/restore process

depesz — Sat, 19 Sep 2009 17:27:53 +0000

As some of you know, I’ve been working lately for OmniTI company. When doing things for them (PostgreSQL related of course , I stumbled on very interesting problem. One of our clients is working on PostgreSQL 8.2, and wants to upgrade to 8.3. This is generally trivial – pg_dump, pg_restore/psql, and you’re done. But, this situation [...]

[polish only] Historia ku pamięci … – aktualizacja

depesz — Wed, 16 Sep 2009 08:57:46 +0000

W piątek, 14 sierpnia, jakoś tak wieczorkiem dostałem maila od bliżej mi nie znanej osoby z firmy Luxmed. Osoba ta, poprosiła o “pilny kontakt (niezależnie od godziny)”, w sprawie “optymalizacji baz PostgreSQL”. Zadzwoniłem. Okazało się, że potrzebują pomocy, i uważają, że dam radę jakoś “stuningować” Postgresa tak by działał szybciej. I że chodzi o pomoc w dniu jutrzejszym [...]

CREATE ROLE privilege cannot be inherited?!

depesz — Sun, 06 Sep 2009 20:07:03 +0000

One of my clients hit a strange limitation – apparently you cannot inherit CREATE ROLE privilege. First, let’s test if it’s really true: First, let’s create role which will have CREATE ROLE privilege: create role test1 with login createrole; Now, let’s create new role, make it inherit privileges, and grant it test1 role: # create role test2 with login inherit; CREATE [...]

Getting random interfacelift wallpaper

depesz — Fri, 04 Sep 2009 12:32:06 +0000

Just thought I’ll share it – maybe somebody else will use it. I’m using KDE 3.5, so the final “dcop …” command, simply sets the file as current wallpaper. I also use 1680×1050 resolution – which you can probably guess from the code #!/usr/bin/perl -w use strict; use English qw( -no_match_vars ); use WWW::Mechanize; my $agent = WWW::Mechanize->new(); $agent->get("http://interfacelift.com/wallpaper_beta/downloads/random/widescreen/1680x1050/"); exit unless 200 [...]

Waiting for 8.5 – Machine readable EXPLAIN

depesz — Fri, 28 Aug 2009 09:11:00 +0000

After very long discussions, on 10th of August, Tom Lane committed patch by Robert Haas: Log Message: ----------- Extend EXPLAIN to support output in XML or JSON format. There are probably still some adjustments to be made in the details of the output, but this gets the basic structure in place. Robert Haas This is a very important news. Every once in [...]

Waiting for 8.5 – Multi-threaded pgbench

depesz — Wed, 26 Aug 2009 09:57:28 +0000

On 3rd of August, Tatsuo Ishii committed patch by ITAGAKI Takahiro: Log Message: ----------- Multi-threaded version of pgbench contributed by ITAGAKI Takahiro, reviewed by Greg Smith and Josh Williams. Following is the proposal from ITAGAKI Takahiro: Pgbench is a famous tool to measure postgres performance, but nowadays it does not work well because it cannot use multiple CPUs. On the other hand, postgres [...]

Getting session variables without touching postgresql.conf

depesz — Thu, 20 Aug 2009 18:41:26 +0000

This post has been updated with new code that uses temporary table – the code is at the end of post! There was this question on Stack Overflow. For future reference: guy asked how to do session variables – i.e. something he could define once in session, and later reuse in standard sql queries – without modifying [...]

Waiting for 8.5 – hinting for number of distinct values

depesz — Wed, 12 Aug 2009 14:45:28 +0000

Robert Haas wrote, and Tom Lane committed (on 2nd of August) patch which adds interesting capability: Log Message: ----------- Add ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT Robert Haas Log message is rather terse, so what does it exactly do? To plan query for execution PostgreSQL has to know some basic statistics for table. These are updated whenever you [...]

Waiting for 8.5 – better messages for unique violation

depesz — Wed, 12 Aug 2009 09:02:58 +0000

On 1st of August Tom Lane committed patch from Itagaki Takahiro: Improve unique-constraint-violation error messages to include the exact values being complained of. In passing, also remove the arbitrary length limitation in the similar error detail message for foreign key violations. Itagaki Takahiro and later added an extension to it: Department of second thoughts: let's show the exact key during unique index build [...]

Waiting for 8.5 – deferrable uniqueness

depesz — Tue, 11 Aug 2009 11:24:19 +0000

On 29th of July ( I know, there is a long backlog in waiting for 8.5 series. I’m working on it , Tom Lane committed patch by Dean Rasheed: Support deferrable uniqueness constraints. The current implementation fires an AFTER ROW trigger for each tuple that looks like it might be non-unique according to the index contents at [...]

1000 posts

depesz — Wed, 22 Jul 2009 09:51:41 +0000

I just checked my WordPress dashboard, and it says: 1,000 Posts 1,847 Comments First entry was posted on 13th of May 2006 – 1166 days ago. During this time I had 2 breakdowns when I said that I close the blog, switched language, and moved from “everything that interests me” to “99% PostgreSQL only”. Blog helped me get new contacts, contracts [...]

Waiting for 8.5 – pgsql: DROP IF EXISTS for columns and constraints.

depesz — Wed, 22 Jul 2009 09:41:33 +0000

On 205h of July Andrew Dunstan committed patch by Andres Freund : Log Message: ----------- DROP IF EXISTS for columns and constraints. Andres Freund. The functionality is self-explanatory, so let’s just show some usage examples: Test table: # create table test (a int4, b int4); CREATE TABLE Drop non-existing column old way: # alter table test drop column c; ERROR: column "c" of relation [...]

Calculating median

depesz — Mon, 13 Jul 2009 14:13:36 +0000

Today, on irc (#postgresql on freenode.net) Dim mentioned about writing median calculation code. It got me thinking, and consequently writing my version of median calculation code. Before I will go any further, let me just say – after I finished, and was quite happy with what I wrote, RhodiumToad showed his approach which is (of course) much [...]

Slow vim startup

depesz — Fri, 10 Jul 2009 21:58:13 +0000

I had today a very weird situation. From my laptop, I ssh to another system, and from there to yet another. On this final system I noticed that vim starts relatively slow. i.e. this command: time vim -u /dev/null -c “:q” returned time in around 3.5 seconds, while on my laptop (which is much less powerful) it is: => time [...]

Getting list of unique elements

depesz — Fri, 10 Jul 2009 09:48:53 +0000

Every so often you need to get list of unique elements in some column. The standard way to do it is: select distinct column from table; or select column from table group by column; The only problem is that it’s slow – as it has to seq scan whole table. Can it be done faster? Usually when somebody wants to [...]

bf hacking for “fun” and definitely no profit

depesz — Sat, 04 Jul 2009 19:37:27 +0000

#!/usr/bin/bf +++++[>++[>>++++>+++++++>+++>+>++++> ++++>++++>+++++++++.--.+. .--..+++.---.

Waiting for 8.5 – Add log_line_prefix placeholder %e to contain the current SQL state

depesz — Sat, 04 Jul 2009 18:26:35 +0000

Also yesterday, and also Peter Eisentraut, committed patch by Guillaume Smet, which: Add log_line_prefix placeholder %e to contain the current SQL state Author: Guillaume Smet What exactly does it do, and how the state looks? Let’s find out. I defined log_line_prefix to be ‘%m %u@%d %p %r {%e} ‘. log_min_duration_statement was set to 0. Then I issued some queries. Logs looked [...]

Waiting for 8.5 – Have \d show child tables that inherit from the specified parent

depesz — Sat, 04 Jul 2009 15:22:42 +0000

Yesterday Peter Eisentraut committed a patch, written by Damien Clochard, that modifies \d output in psql: Have \d show child tables that inherit from the specified parent As per discussion, \d shows only the number of child tables, because that could be hundreds, when used for partitioning. \d+ shows the actual list. Author: Damien Clochard How does it [...]

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