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.

I just *HATE* Hardware Abstraction Layer

I'm quite happy user of Arch Linux. BUT

Recent upgrade changed Xorg to new version which requires HAL (Hardware Abstraction Layer).

Problem: when HAL is turned on, it totally breaks my keyboard – generates extra “key presses" when using “special" keys – I press windows key, and get letter “F" extra. I also lost ability to self-repeat keys by pressing them longer. Right alt – stopped working. Down arrow – generated also “M" letter. Right shift – doesn't work. All in all one big mess.

Solution was “trivial": remove hal, and add to xorg.conf section to disable hotplugging:

Section "ServerFlags"
    Option "AutoAddDevices" "False"
EndSection

I just have to write it, because I might hit the same problem someplace else as well, so I will now know what to do.

Side note – I happen to use wireless Logitech keyboard, which apparently is a bad thing, because any kind of mention “I have a problem with keyboard and X" prompts questions about my Logitech – even if the same keyboard works flawlessly on console, or in X without this HAL thing.

Maybe I'm getting grumpy, but Linux on desktop actually worked for me much better 2 years ago, than it does now.

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.

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

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

Continue reading Waiting for 8.5 – ordered aggregates

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.

Continue reading Waiting for 8.5 – buffers info for explain

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

Continue reading Waiting for 8.5 – exclusion constraints