Tag: postgresql

Waiting for 8.5 – deferrable uniqueness

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 the
time of insertion.  This works well as long as there aren't many conflicts,
but won't scale to massive unique-key reassignments.  Improving that case
is a TODO item.
 
Dean Rasheed

Continue reading Waiting for 8.5 – deferrable uniqueness

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

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

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.

Continue reading Waiting for 8.5 – let's start

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

explain.depesz.com – update

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 – ability to hide parts or just visually see what the Nested Loop is calling in 2nd stage greatly helps me see the flow of query – you can test it, for example, here.

By the way – BIG THANK YOU for Metys for help on it (well, actually, for doing it).