Tag: postgresql

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

So, you have a table which looks like this:

# \d test
                           Table "public.test"
  Column   |  Type   |                     Modifiers
-----------+---------+---------------------------------------------------
 id        | integer | not null default nextval('test_id_seq'::regclass)
 parent_id | integer |
 x         | text    |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "test_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES test(id)
Referenced by:
  "test_parent_id_fkey" IN test FOREIGN KEY (parent_id) REFERENCES test(id)

And you would like to easily get all children starting from given node?

Continue reading Getting list of all children in “adjacency list" tree structure

Waiting for 8.4 – no more -d in pg_dump!

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 was some discussion (20 mails) that extended the patch to remove also -D.

And now, today, Tom Lane committed:

Remove the -d and -D options of pg_dump and pg_dumpall.  The functionality
is still available, but you must now write the long equivalent --inserts
or --column-inserts.  This change is made to eliminate confusion with the
use of -d to specify a database name in most other Postgres client programs.
Original patch by Greg Mullane, modified per subsequent discussion.

This is great news. One less way a new user of pg (or one that doesn't read –help pages) can do himself harm, one less thing that is purely illogical.

find.best.tablespace.split.pl

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

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
--------------
 000123456789
(1 ROW)

Continue reading lpad() and rpad() gotcha

Waiting for 8.4 – parallel restoration of dumps

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 switch.

Continue reading Waiting for 8.4 – parallel restoration of dumps

Waiting for 8.4 – column level privileges.

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

Continue reading Waiting for 8.4 – column level privileges.

Waiting for 8.4 – window functions

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 SQL:2008.
 
Hitoshi Harada, with some kibitzing from Heikki and Tom.

Continue reading Waiting for 8.4 – window functions

Waiting for 8.4 – pg_stat_statements

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

Continue reading Waiting for 8.4 – pg_stat_statements