Tag: postgresql

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

Waiting for 8.5 – Application name reporting

On 28th of November Tom Lane committed patch by Dave Page, which adds new capability to system monitoring and logging:

Log Message:
-----------
Add support for an application_name parameter, which is displayed in
pg_stat_activity and recorded in log entries.
 
Dave Page, reviewed by Andres Freund

Continue reading Waiting for 8.5 – Application name reporting

Waiting for 8.5 – better wrapped output in psql

On 22nd of November Tom Lane committed patch by Roger Leigh which fixes my pet peeve:

Log Message:
-----------
Improve psql's tabular display of wrapped-around data by inserting markers
in the formerly-always-blank columns just to left and right of the data.
Different marking is used for a line break caused by a newline in the data
than for a straight wraparound.  A newline break is signaled by a "+" in the
right margin column in ASCII mode, or a carriage return arrow in UNICODE mode.
Wraparound is signaled by a dot in the right margin as well as the following
left margin in ASCII mode, or an ellipsis symbol in the same places in UNICODE
mode.  "\pset linestyle old-ascii" is added to make the previous behavior
available if anyone really wants it.
 
In passing, this commit also cleans up a few regression test files that
had unintended spacing differences from the current actual output.
 
Roger Leigh, reviewed by Gabrielle Roth and other members of PDXPUG.

Continue reading Waiting for 8.5 – better wrapped output in psql

Waiting for 8.5 – conditional triggers

On 20th of November Tom Lane committed patch by Takahiro Itagaki which adds interesting functionality:

ADD a WHEN clause TO CREATE TRIGGER, allowing a BOOLEAN expression TO be
checked TO determine whether the TRIGGER should be fired.
 
FOR BEFORE triggers this IS mostly a matter OF spec compliance; but FOR AFTER
triggers it can provide a noticeable performance improvement, since queuing OF
a DEFERRED TRIGGER event AND re-fetching OF the ROW(s) at END OF statement can
be short-circuited IF the TRIGGER does NOT need TO be fired.
 
Takahiro Itagaki, reviewed BY KaiGai Kohei.

Continue reading Waiting for 8.5 – conditional triggers

Waiting for 8.5 – checking password strength

On 18th of November Tom Lane committed patch by Laurenz Albe which adds very interesting capability:

ADD a hook TO CREATE/ALTER ROLE TO allow an external module TO CHECK the
strength OF DATABASE passwords, AND CREATE a sample implementation OF
such a hook AS a NEW contrib module "passwordcheck".
 
Laurenz Albe, reviewed BY Takahiro Itagaki

Continue reading Waiting for 8.5 – checking password strength