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
Tag: postgresql
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
Waiting for … 9.0
I've written 29 posts about new features in 8.5. And now core team decided to name it 9.0. Great. And now I have to make changes in all of these posts.
Just kidding – change is simple – I just added tag “pg90" and that's all.
Change from 8.5 to 9.0 emphasizes significance of new features in this version, and generally looks cool marketing-wise.
explain.depesz.com – stats after a year
I just checked, and apparently explain.depesz.com is online for over a year now. First plans was added on 2008-12-04 13:20:43+01.
Since then there have been 3602 plans added, 770 of them were set to be private (not displayed on previous explains page.
Longest plan has 2267194 characters (yes, 2.2 megabytes of explain analyze output!), but it's not public, so I can't tell you the url. Longest public plan is this monstrosity, which (while being only explain output, not explain analyze) has 503585 characters.
During this time, there have been 61 days that nobody added any plan on (perhaps problems with software?).
The most heavily used day was 23rd of June 2009, when 71 new plans have been added (4 private).
Thank you all for using it. I hope you find it useful, and I promise, that one day, I will finally sit down, and add all long-due features (like work with new explain formats, fixing some output glitches, stats of used objects per plan).
If you'd like to take a peek at how it works – source of explain.depesz.com is always available.
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.
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.
Waiting for 8.5 – PL/pgSQL by default
On 18th of December Bruce Momjian committed very important, but relatively small, patch:
Log Message: ----------- Install server-side language PL/pgSQL by default.
There is no point in showing it, commit log tells all – basically from 8.5 on PL/pgSQL will be enabled by default in all databases.
There was time when people rejected “stored procedure" suggestions, because they didn't have any PL/, and installing them required superuser privileges. For some time (since 8.3) you no longer need to be superuser to create trusted languages, so the problem mostly vanished, but now, it will simply cease to exist 🙂
Waiting for 8.5 – ordered aggregates
On 15th of December Tom Lane committed patch by Andrew Gierth (aka RhodiumToad), which adds interesting capability:
Log Message: ----------- Support ORDER BY within aggregate function calls, at long last providing a non-kluge method for controlling the order in which values are fed to an aggregate function. At the same time eliminate the old implementation restriction that DISTINCT was only supported for single-argument aggregates. Possibly release-notable behavioral change: formerly, agg(DISTINCT x) dropped null values of x unconditionally. Now, it does so only if the agg transition function is strict; otherwise nulls are treated as DISTINCT normally would, ie, you get one copy. Andrew Gierth, reviewed by Hitoshi Harada
Waiting for 8.5 – buffers info for explain
Today small, but (at least for me) really useful patch. Committed on 15th of December by Robert Haas, and written by Itagaki Takahiro, this patch does:
Log Message: ----------- Add an EXPLAIN (BUFFERS) option to show buffer-usage statistics. This patch also removes buffer-usage statistics from the track_counts output, since this (or the global server statistics) is deemed to be a better interface to this information. Itagaki Takahiro, reviewed by Euler Taveira de Oliveira.
Waiting for 8.5 – exclusion constraints
On 7th of December, Tom Lane committed patch by Jeff Davis that adds general exclusion constraints:
Log Message: ----------- Add exclusion constraints, which generalize the concept of uniqueness to support any indexable commutative operator, not just equality. Two rows violate the exclusion constraint if "row1.col OP row2.col" is TRUE for each of the columns in the constraint. Jeff Davis, reviewed by Robert Haas