On 205h of July Andrew Dunstan committed patch by Andres Freund :
Log Message: ----------- DROP IF EXISTS for columns and constraints. Andres Freund.
Continue reading Waiting for 8.5 – pgsql: DROP IF EXISTS for columns and constraints.
Calculating median
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.
Slow vim startup
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 vim -u /dev/null -c ":q" real 0m0.073s user 0m0.056s sys 0m0.016s
I tried to debug the situation, and it got weirder. If I did “su – another_user" (on the final system) – it became fast. What's more: if I did su – depesz (my account) back – vim was still fast!
After some debugging it occurred to me: I have automatic X11 forwarding turned on. I checked – and yes, in the shell that vim starts slowly, i had “DISPLAY" variable, set to localhost:10.0!
Quick unset DISPLAY, and suddenly vim starts 0.027s!
Lesson for future – do not use automatic X11 forwarding for long-distance ssh connections, or make sure you run vim with “-X" option.
Later I learned why it tries X11 connection – to get access to X copy/paste buffer (available as * register). Nice feature, but with quite problematic side effects.
Getting list of unique elements
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?
bf hacking for “fun” and definitely no profit
#!/usr/bin/bf +++++[>++[>>++++>+++++++>+++>+<<<<<-]>>+>+<<<<-]+++++[>+++++[>>>>>>++++> ++++>++++>++++<<<<<<<<<-]>>>>>>>+>++>+++<<<<<<<<<<-]>>>>-.>>>>>>++.--.+. <<<<<++.>>---.>>.+.>.<<-.<++++.>>>--.<<<<<.>>---.>>>.<<<-.>+.>-.<<+++++. >>>+++.<<<---.>++.<<<.>>>---.<--.++.>+++.<++.>>>---.<<<<<<<+.>>>.<<<<[-]
If you can read it, then something is definitely wrong with you ;-P
Waiting for 8.5 – Add log_line_prefix placeholder %e to contain the current SQL state
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
Waiting for 8.5 – Have \d show child tables that inherit from the specified parent
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 <damien@dalibo.info>
Continue reading Waiting for 8.5 – Have \d show child tables that inherit from the specified parent
Waiting for 8.5 – let’s start
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(); version ----------------------------------------------------------------------------------------------------------- PostgreSQL 8.5devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4), 32-bit (1 row)
Which means: I can start new blog posts series.
explain.depesz.com – update
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
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
Table "public.users"
Column | Type | Modifiers
------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
username | text |
registered | timestamp with time zone |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)Continue reading Tips N’ Tricks – Generating readable reports with plain SQL