Waiting for 9.0 – Final Post ?

As of now, I am happy user of 9.1devel version of PostgreSQL:

=$ psql -c 'select version()'
                                                       version                                                       
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1devel ON x86_64-unknown-linux-gnu, compiled BY GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
(1 ROW)

So, I guess no new features will make it to 9.0 – after all, 9.0 is currently already beta3.

If you want to get list of what I wrote about for 8.5/9.0 – just check the tag page.

Waiting for 9.0 – pg_upgrade

On May, 12ve, Bruce Momjian committed new contrib module for 9.0 – pg_upgrage.

As I understand – this is what was available before as pg-migrator.

If you're not familiar with it – it's a tool that allows upgrade of $PGDATA from some version to some version. What's the use case? Let's assume you have this 200GB database working as 8.3, and you'd like to go to 8.4 (or 9.0). Normal way is pg_dump + pg_restore – which will take some time. With pg-migrate/pg_upgrade it should be faster, and easier. So, let's play with it.

Continue reading Waiting for 9.0 – pg_upgrade

Waiting for 9.0 – extended frames for window functions

On 12th of February Tom Lane committed patch by Hitoshi Harada:

Log Message:
-----------
Extend the set of frame options supported for window functions.
 
This patch allows the frame to start from CURRENT ROW (in either RANGE or
ROWS mode), and it also adds support for ROWS n PRECEDING and ROWS n FOLLOWING
start and end points.  (RANGE value PRECEDING/FOLLOWING isn't there yet ---
the grammar works, but that's all.)
 
Hitoshi Harada, reviewed by Pavel Stehule

Continue reading Waiting for 9.0 – extended frames for window functions

Waiting for 9.0 – string_agg

On 1st of Februyary, Takahiro Itagaki committed a patch by Pavel Stehule which adds string_agg aggregate:

Log Message:
-----------
Add string_agg aggregate functions. The one argument version concatenates
the input values into a string. The two argument version also does the same
thing, but inserts delimiters between elements.
 
Original patch by Pavel Stehule, reviewed by David E. Wheeler and me.

Continue reading Waiting for 9.0 – string_agg

Waiting for 9.0 – removal of 0000000001.history check

I tend to write about new features in new versions of PostgreSQL, but this patch actually fixes one of the things that annoy me a lot, so here it goes:

On 26th of January, Simon Riggs committed:

Log Message:
-----------
Fix longstanding gripe that we check for 0000000001.history at start of
archive recovery, even when we know it is never present.

If you've ever tried to roll your own restore_command script ( like pg_standby ) then you know that to the algorithm that's presented in docs You always had to add special case for file “0000000001.history" – which was never there, but somehow PostgreSQL always asked for it – despite the fact that it could never arrive.

Now, thanks to this small patch we will no longer need to add this “if" in any script. It's small, and it's not a new feature, but I am SO happy to see it.

Waiting for 9.0 – table and index sizes

On 19th of January Tom Lane committed really brilliant patch:

Log Message:
-----------
Add pg_table_size() and pg_indexes_size() to provide more user-friendly
wrappers around the pg_relation_size() function.
 
Bernd Helmle, reviewed by Greg Smith

Continue reading Waiting for 9.0 – table and index sizes

Waiting for 9.0 – Streaming replication

The BIG feature. The feature that made PostgreSQL leap from 8.4 to 9.0. Patch was written by Fujii Masao, and committed by Heikki Linnakangas on 15th of January 2010:

Log Message:
-----------
Introduce Streaming Replication.
 
This includes two new kinds of postmaster processes, walsenders and
walreceiver. Walreceiver is responsible for connecting to the primary server
and streaming WAL to disk, while walsender runs in the primary server and
streams WAL from disk to the client.
 
Documentation still needs work, but the basics are there. We will probably
pull the replication section to a new chapter later on, as well as the
sections describing file-based replication. But let's do that as a separate
patch, so that it's easier to see what has been added/changed. This patch
also adds a new section to the chapter about FE/BE protocol, documenting the
protocol used by walsender/walreceivxer.
 
Bump catalog version because of two new functions,
pg_last_xlog_receive_location() and pg_last_xlog_replay_location(), for
monitoring the progress of replication.
 
Fujii Masao, with additional hacking by me

Continue reading Waiting for 9.0 – Streaming replication

Waiting for 8.5 – VACUUM FULL change

Some time ago Josh Berkus wrote about possible changes in VACUUM FULL.

Now these changes came to life. By now, I mean 6th of January, when Takahiro Itagaki committed his patch:

Log Message:
-----------
Support rewritten-based full vacuum as VACUUM FULL. Traditional
VACUUM FULL was renamed to VACUUM FULL INPLACE. Also added a new
option -i, --inplace for vacuumdb to perform FULL INPLACE vacuuming.
 
Since the new VACUUM FULL uses CLUSTER infrastructure, we cannot
use it for system tables. VACUUM FULL for system tables always
fall back into VACUUM FULL INPLACE silently.
 
Itagaki Takahiro, reviewed by Jeff Davis and Simon Riggs.

Continue reading Waiting for 8.5 – VACUUM FULL change

Waiting for 8.5 – Hot Standby

On 19th of December Simon Riggs committed a patch that will quite likely be the single most-talked-about change in PostgreSQL 8.5:

Log Message:
-----------
Allow read only connections during recovery, known as Hot Standby. 
 
Enabled by recovery_connections = on (default) and forcing archive recovery
using a recovery.conf. Recovery processing now emulates the original
transactions as they are replayed, providing full locking and MVCC behaviour
for read only queries. Recovery must enter consistent state before
connections are allowed, so there is a delay, typically short, before
connections succeed. Replay of recovering transactions can conflict and in
some cases deadlock with queries during recovery; these result in query
cancellation after max_standby_delay seconds have expired. Infrastructure
changes have minor effects on normal running, though introduce four new
types of WAL record. 
 
New test mode "make standbycheck" allows regression tests of
static command behaviour on a standby server while in recovery. Typical and
extreme dynamic behaviours have been checked via code inspection and manual
testing. Few port specific behaviours have been utilised, though primary
testing has been on Linux only so far. 
 
This commit is the basic patch. Additional changes will follow in this
release to enhance some aspects of behaviour, notably improved handling of
conflicts, deadlock detection and query cancellation. Changes to VACUUM FULL
are also required.
 
Simon Riggs, with significant and lengthy review by Heikki Linnakangas,
including streamlined redesign of snapshot creation and two-phase commit.
 
Important contributions from Florian Pflug, Mark Kirkwood, Merlin Moncure,
Greg Stark, Gianni Ciolli, Gabriele Bartolini, Hannu Krosing, Robert Haas,
Tatsuo Ishii, Hiroyuki Yamada plus support and feedback from many other
community members.

Continue reading Waiting for 8.5 – Hot Standby