PostgreSQL Weekly News – July 17 2011

== PostgreSQL Weekly News – July 17 2011 ==

PostgreSQL 9.1 beta 3 is out. Test!

PGDay Porto Alegre will be on August 19, 2011 in Porto Alegre, RS, Brazil.

The patches section this week has been truncated in the interest of
getting the newsletter out the door.

== PostgreSQL Product News ==

PostgreSQL Maestro 11.7, a Windows admin tool for PostgreSQL, released.

pg_blockinfo 0.1, a Perl tool to examine your PostgreSQL heap data files, released.

pgbuildfarm client 4.6 released.

PostgreSQL Enterprise Manager Beta released.

pgpool-II 3.1.0 alpha3, a connection pooler and more, released.

PostgreDAC 2.6.4, a Delphi/C++ builder for PostgreSQL, released.

psqlODBC 09.00.0310, an ODBC driver for PostgreSQL, released.

== PostgreSQL Jobs for July ==

== PostgreSQL Local ==

CHAR(11), the PostgreSQL Conference on Clustering, High Availability
and Replication is now open for online registration and bookings.
July 11-12 2011 in Cambridge, UK.

PostgreSQL Conference China 2011 will be held in Guangzhou July
15-16, 2011.

PDXPUG is hosting PgDay on Sunday, July 24, 2011, one day before
OSCON, in Portland, Oregon, USA. Details here:

Postgres Open 2011, a conference focused on disruption of the database
industry through PostgreSQL, will take place September 14-16, 2011 in
Chicago, Illinois at the Westin Michigan Avenue hotel.

PG-Day Denver 2011 will be held on Friday, October 21st, 2011 at
the Auraria Campus near downtown Denver, Colorado.

PostgreSQL Conference West (#PgWest) will be held September 27th-30th,
2011 at the San Jose Convention center in San Jose, California, USA.

PostgreSQL Conference Europe 2011 will be held on October 18-21 in

pgbr will be in Sao Paulo, Brazil November 3-4, 2011.

PGConf.DE 2011 is the German-speaking PostgreSQL Conference and will
take place on November 11th in the Rheinisches Industriemuseum in
Oberhausen, Germany. Call for Papers is open.

== PostgreSQL in the News ==

Planet PostgreSQL:

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to, German language
to, Italian language to Spanish language

== Reviews ==

== Applied Patches ==

Simon Riggs pushed:

– Reset ALTER TABLE lock levels to AccessExclusiveLock in all cases.
Locks on inheritance parent remain at lower level, as they were
before. Remove entry from 9.1 release notes.

– Alter test results to comply with new ALTER TABLE behaviour.

Heikki Linnakangas pushed:

– Remove silent_mode. You get the same functionality with “pg_ctl -l
postmaster.log”, or nohup. There was a small issue with
LINUX_OOM_ADJ and silent_mode, namely that with silent_mode the
postmaster process incorrectly used the OOM settings meant for
backend processes. We certainly could’ve fixed that directly, but
since silent_mode was redundant anyway, we might as well just remove

– Clarify that you need ActiveState perl 5.8 *or later* to build on

– Fix a bug with SSI and prepared transactions: If there’s a dangerous
structure T0 —> T1 —> T2, and T2 commits first, we need to abort
something. If T2 commits before both conflicts appear, then it
should be caught by OnConflict_CheckForSerializationFailure. If
both conflicts appear before T2 commits, it should be caught by
PreCommit_CheckForSerializationFailure. But that is actually run
when T2 *prepares*. Fix that in OnConflict_CheckForSerializationFailure,
by treating a prepared T2 as if it committed already. This is
mostly a problem for prepared transactions, which are in prepared
state for some time, but also for regular transactions because they
also go through the prepared state in the SSI code for a short
moment when they’re committed. Kevin Grittner and Dan Ports

– SSI has a race condition, where the order of commit sequence numbers
of transactions might not match the order the work done in those
transactions become visible to others. The logic in SSI, however,
assumed that it does. Fix that by having two sequence numbers for
each serializable transaction, one taken before a transaction
becomes visible to others, and one after it. This is easier than
trying to make the the transition totally atomic, which would
require holding ProcArrayLock and SerializableXactHashLock at the
same time. By using prepareSeqNo instead of commitSeqNo in a few
places where commit sequence numbers are compared, we can make those
comparisons err on the safe side when we don’t know for sure which
committed first. Per analysis by Kevin Grittner and Dan Ports, but
this approach to fix it is different from the original patch.

– There’s a small window wherein a transaction is committed but not
yet on the finished list, and we shouldn’t flag it as a potential
conflict if so. We can also skip adding a doomed transaction to the
list of possible conflicts because we know it won’t commit. Dan
Ports and Kevin Grittner.

– Fix one overflow and one signedness error, caused by the patch to
calculate OLDSERXID_MAX_PAGE based on BLCKSZ. MSVC compiler warned
about these.

– Fix two ancient bugs in GiST code to re-find a parent after page
split: First, when following a right-link, we incorrectly marked the
current page as the parent of the right sibling. In reality, the
parent of the right page is the same as the parent of the current
page (or some page to the right of it, gistFindCorrectParent() will
sort that out). Secondly, when we follow a right-link, we must
prepend, not append, the right page to our list of pages to visit.
That’s because we assume that once we hit a leaf page in the list,
all the rest are leaf pages too, and give up. To hit these bugs,
you need concurrent actions and several unlucky accidents. Another
backend must split the root page, while you’re in process of
splitting a lower-level page. Furthermore, while you scan the
internal nodes to re-find the parent, another backend needs to again
split some more internal pages. Even then, the bugs don’t
necessarily manifest as user-visible errors or index corruption.
While we’re at it, make the error reporting a bit better if
gistFindPath() fails to re-find the parent. It used to be an
assertion, but an elog() seems more appropriate. Backpatch to all
supported branches.

– Change the way the offset of downlink is stored in GISTInsertStack.
GISTInsertStack.childoffnum used to mean “offset of the downlink in
this node, pointing to the child node in the stack”. It’s now
replaced with downlinkoffnum, which means “offset of the downlink in
the parent of this node”. gistFindPath() already used childoffnum
with this new meaning, and had an extra step at the end to pull all
the childoffnum values down one node in the stack, to adjust the
stack for the meaning that childoffnum had elsewhere. That’s no
longer required. The reason to do this now is this new
representation is more convenient for the GiST fast build patch that
Alexander Korotkov is working on. While we’re at it, replace the
linked list used in gistFindPath with a standard List, and make
gistFindPath() static. Alexander Korotkov, with some changes by me.

Andrew Dunstan pushed:

– Back-patch Fix bat file quoting of %ENV from commit 19b7fac8.

– Reimplement pgbison and pgflex as perl scripts instead of bat files.
In the process, remove almost all knowledge of individual .y and .l
files, and instead get invocation settings from the relevant make
files. The exception is plpgsql’s gram.y, which has a target with a
different name. It is hoped that this will make the scripts more
future-proof, so that they won’t require adjustment every time we
add a new .l or .y file. The logic is also notably less tortured
than that forced on us by the idiosyncrasies of the Windows command
processor. The .bat files are kept as thin wrappers for the perl

– Fix comments over eagerly c&p’d.

– Improve wording of documentation on default privileges. Per recent
-hackers discussion.

Tom Lane pushed:

– Remove missed reference to SilentMode.

– Fix psql’s counting of script file line numbers during COPY.
handleCopyIn incremented pset.lineno for each line of COPY data read
from a file. This is correct when reading from the current script
file (i.e., we are doing COPY FROM STDIN followed by in-line data),
but it’s wrong if the data is coming from some other file. Per bug
#6083 from Steve Haslam. Back-patch to all supported versions.

– Improve documentation about foreign data wrapper validator
functions. Modified version of a patch by Shigeru Hanada.

– Restructure foreign data wrapper chapter so it has more than one
section. As noted by Laurenz Albe, our SGML tools deal rather oddly
with chapters having just one <sect1>. Perhaps the tooling could be
fixed, but really the design of this chapter’s introduction is
pretty bogus anyhow. Split it into a true introduction and a
<sect1> about the FDW functions, so that it reads better and dodges
the lack-of-a-chapter-TOC problem.

– Make the file_fdw validator check that a filename option has been
provided. This was already a runtime failure condition, but it’s
better to check at validation time if possible. Lightly modified
version of a patch by Shigeru Hanada.

– Call FDW validator functions even when the options list is empty.
This is useful since a validator might want to require certain
options to be provided. The passed array is an empty text array in
this case. Per suggestion by Laurenz Albe, though this is not quite
his patch.

– Remove assumptions that not-equals operators cannot be in any
opclass. get_op_btree_interpretation assumed this in order to save
some duplication of code, but it’s not true in general anymore
because we added <> support to btree_gist. (We still assume it for
btree opclasses, though.) Also, essentially the same logic was baked
into predtest.c. Get rid of that duplication by generalizing
get_op_btree_interpretation so that it can be used by predtest.c.
Per bug report from Denis de Bernardy and investigation by Jeff
Davis, though I didn’t use Jeff’s patch exactly as-is. Back-patch
to 9.1; we do not support this usage before that.

– Finish disabling reduced-lock-levels-for-DDL feature. Previous
patch only covered the ALTER TABLE changes, not changes in other
commands; and it neglected to revert the documentation changes.

– Reclassify replication-related GUC variables as “master” and
“standby”. Per discussion, this structure seems more understandable
than what was there before. Make config.sgml and
postgresql.conf.sample agree. In passing do a bit of editorial work
on the variable descriptions.

– Update 9.1 release notes to reflect commits through today.

– Update examples for string-related functions. In the example for
decode(), show the bytea result in hex format, since that’s now the
default. Use an E” string in the example for quote_literal(), so
that it works regardless of the standard_conforming_strings setting.
On the functions-for-binary-strings page, leave the examples as-is
for readability, but add a note pointing out that they are shown in
escape format. Per comments from Thom Brown. Also, improve the
description for encode() and decode() a tad. Backpatch to 9.0,
where bytea_output was introduced.

– Tag 9.1beta3.

– Fix another oversight in logging of changes in postgresql.conf
settings. We were using GetConfigOption to collect the old value of
each setting, overlooking the possibility that it didn’t exist yet.
This does happen in the case of adding a new entry within a custom
variable class, as exhibited in bug #6097 from Maxim Boguk. To fix,
add a missing_ok parameter to GetConfigOption, but only in 9.1 and
HEAD — it seems possible that some third-party code is using that
function, so changing its API in a minor release would cause
problems. In 9.0, create a near-duplicate function instead.

– Avoid listing ungrouped Vars in the targetlist of
Agg-underneath-Window. Regular aggregate functions in combination
with, or within the arguments of, window functions are OK per spec;
they have the semantics that the aggregate output rows are computed
and then we run the window functions over that row set. (Thus, this
combination is not really useful unless there’s a GROUP BY so that
more than one aggregate output row is possible.) The case without
GROUP BY could fail, as recently reported by Jeff Davis, because
sloppy construction of the Agg node’s targetlist resulted in extra
references to possibly-ungrouped Vars appearing outside the
aggregate function calls themselves. See the added regression test
case for an example. Fixing this requires modifying the API of
flatten_tlist and its underlying function pull_var_clause. I chose
to make pull_var_clause’s API for aggregates identical to what it
was already doing for placeholders, since the useful behaviors turn
out to be the same (error, report node as-is, or recurse into it).
I also tightened the error checking in this area a bit: if it was
ever valid to see an uplevel Var, Aggref, or PlaceHolderVar here,
that was a long time ago, so complain instead of ignoring them.
Backpatch into 9.1. The failure exists in 8.4 and 9.0 as well, but
seeing that it only occurs in a basically-useless corner case, it
doesn’t seem worth the risks of changing a function API in a minor
release. There might be third-party code using pull_var_clause.

– Update some comments to clarify who does what in targetlist
creation. No code changes; just avoid blaming query_planner for
things it doesn’t really do.

– In planner, don’t assume that empty parent tables aren’t really
empty. There’s a heuristic in estimate_rel_size() to clamp the
minimum size estimate for a table to 10 pages, unless we can see
that vacuum or analyze has been run (and set relpages to something
nonzero, so this will always happen for a table that’s actually
empty). However, it would be better not to do this for inheritance
parent tables, which very commonly are really empty and can be
expected to stay that way. Per discussion of a recent
pgsql-performance report from Anish Kejariwal. Also prevent it from
happening for indexes (although this is more in the nature of
documentation, since CREATE INDEX normally initializes relpages to
something nonzero anyway). Back-patch to 9.0, because the ability
to collect statistics across a whole inheritance tree has improved
the planner’s estimates to the point where this relatively small
error makes a significant difference. In the referenced report,
merge or hash joins were incorrectly estimated as cheaper than a
nestloop with inner indexscan on the inherited table. That was less
likely before 9.0 because the lack of inherited stats would have
resulted in a default (and rather pessimistic) estimate of the cost
of a merge or hash join.

– Replace errdetail(“%s”, …) with errdetail_internal(“%s”, …).
There may be some other places where we should use
errdetail_internal, but they’ll have to be evaluated case-by-case.
This commit just hits a bunch of places where invoking gettext is
obviously a waste of cycles.

– Add an errdetail_internal() ereport auxiliary routine. This
function supports untranslated detail messages, in the same way that
errmsg_internal supports untranslated primary messages. We’ve
needed this for some time IMO, but discussion of some cases in the
SSI code provided the impetus to actually add it. Kevin Grittner,
with minor adjustments by me

– Use errdetail_internal() for SSI transaction cancellation details.
Per discussion, these seem too technical to be worth translating.
Kevin Grittner

– Improve make_subplanTargetList to avoid including Vars unnecessarily.
If a Var was used only in a GROUP BY expression, the previous
implementation would include the Var by itself (as well as the
expression) in the generated targetlist. This wouldn’t affect the
efficiency of the scan/join part of the plan at all, but it could
result in passing unnecessarily-wide rows through sorting and
grouping steps. It turns out to take only a little more code, and
not noticeably more time, to generate a tlist without such
redundancy, so let’s do that. Per a recent gripe from HarmeekSingh

– Add temp_file_limit GUC parameter to constrain temporary file space
usage. The limit is enforced against the total amount of temp file
space used by each session. Mark Kirkwood, reviewed by Cédric
Villemain and Tatsuo Ishii

– Further thoughts about temp_file_limit patch. Move FileClose’s
decrement of temporary_files_size up, so that it will be executed
even if elog() throws an error. This is reasonable since if the
unlink() fails, the fact the file is still there is not our fault,
and we are going to forget about it anyhow. So we won’t count it
against temp_file_limit anymore. Update fileSize and
temporary_files_size correctly in FileTruncate. We probably don’t
have any places that truncate temp files, but fd.c surely should not
assume that.

Peter Eisentraut pushed:

– Remove unused variable to silence compiler warning

– Set user_defined_types.data_type to null. On re-reading the
standard, this field is only used for distinct or reference types.

– Small documentation tweaks

– Message style tweaks

– Postgres -> PostgreSQL in error message

– Message style improvements of errmsg_internal() calls

– Fix use of unportable %m format.

– Message style improvements

– Add item about Clang support to 9.1 release notes

– Fix and clarify information schema interval_precision fields. The
fields were previously wrongly typed as character_data; change to
cardinal_number. Update the documentation and the implementation to
show more clearly that this applies to a feature not available in
PostgreSQL, rather than just not yet being implemented in the
information schema.

– Remove excessively backpatched gitignore files. These caused
directories from future releases to appear in the backbranch tree.

– Make pg_upgrade output more consistent with project style. Add
errno-based output to error messages where appropriate, reformat
blocks to about 72 characters per line, use spaces instead of tabs
for indentation, and other style adjustments.

– Mark collation-related information schema columns as not implemented
rather than not applicable. They have already been implemented in

– Implement information schema interval_type columns. Also correct
reporting of interval precision when field restrictions are
specified in the typmod.

– Remove README.mb.big5 and They were wildly outdated.

– Small update on suggested startup file locations. Debian/Ubuntu
don’t have a /etc/rc.d/ directory, so add some alternative names as

– Set information_schema.routines.is_udt_dependent to NO. It
previously said YES, but that is incorrect.

– Set information_schema.tables.commit_action to null. The commit
action of temporary tables is currently not cataloged, so we can’t
easily show it. The previous value was outdated from before we had
different commit actions.

Alvaro Herrera pushed:

– Don’t try to use a constraint name as domain name. The bug that
caused this to be discovered is that the code was trying to
dereference a NULL or ill-defined pointer, as reported by Michael
Mueller; but what it was doing was wrong anyway, per Heikki
Linnakangas. This patch is Heikki’s suggested fix.

– Move Trigger and TriggerDesc structs out of rel.h into a new
reltrigger.h. This lets us stop including rel.h into execnodes.h,
which is a widely used header.

– Add support for blocked commands in isolationtester. This enables
us to test that blocking commands (such as foreign keys checks that
conflict with some other lock) act as intended. The set of tests
that this adds is pretty minimal, but can easily be extended by
adding new specs. The intention is that this will serve as a basis
for ensuring that further tweaks of locking implementation preserve
(or improve) existing behavior. Author: Noah Misch

– Blind attempt at fixing isolation_tester on Win32

– Fix broken markup

– Set different deadlock_timeout on each session in new isolation
tests. This provides deterministic deadlock-detection ordering for
new isolation tests, fixing the sporadic failures in them. Author:
Noah Misch

Magnus Hagander pushed:

– Fix typo in sslmode documentation. Per bug #6089, noted by Sidney

– perltidy run over msvc build system

– Fix SSPI login when multiple roundtrips are required. This fixes
SSPI login failures showing “The function requested is not
supported”, often showing up when connecting to localhost. The
reason was not properly updating the SSPI handle when multiple
roundtrips were required to complete the authentication sequence.
Report and analysis by Ahmed Shinwari, patch by Magnus Hagander

Robert Haas pushed:

– Attempt to standardize formatting of psql queries. Most queries end
with a backslash, but not a newline, so try to standardize on that,
for the convenience of people using psql -E to extract queries.
Josh Kupershmidt, reviewed by Merlin Moncure.

– Add \ir command to psql. \ir is short for “include relative”; when
used from a script, the supplied pathname will be interpreted
relative to the input file, rather than to the current working
directory. Gurjeet Singh, reviewed by Josh Kupershmidt, with
substantial further cleanup by me.

– Adjust OLDSERXID_MAX_PAGE based on BLCKSZ. The value when BLCKSZ =
8192 is unchanged, but with larger-than-normal block sizes we might
need to crank things back a bit, as we’ll have more entries per page
than normal in that case. Kevin Grittner

– Try to acquire relation locks in RangeVarGetRelid. In the previous
coding, we would look up a relation in RangeVarGetRelid, lock the
resulting OID, and then AcceptInvalidationMessages(). While this
was sufficient to ensure that we noticed any changes to the relation
definition before building the relcache entry, it didn’t handle the
possibility that the name we looked up no longer referenced the same
OID. This was particularly problematic in the case where a table
had been dropped and recreated: we’d latch on to the entry for the
old relation and fail later on. Now, we acquire the relation lock
inside RangeVarGetRelid, and retry the name lookup if we notice that
invalidation messages have been processed meanwhile. Many
operations that would previously have failed with an error in the
presence of concurrent DDL will now succeed. There is a good deal
of work remaining to be done here: many callers of RangeVarGetRelid
still pass NoLock for one reason or another. In addition, nothing
in this patch guards against the possibility that the meaning of an
unqualified name might change due to the creation of a relation in a
schema earlier in the user’s search path than the one where it was
previously found. Furthermore, there’s nothing at all here to guard
against similar race conditions for non-relations. For all that,
it’s a start. Noah Misch and Robert Haas

Bruce Momjian pushed:

– Add C comment that txid_current() assigns an XID if one is not
already assigned.

– Use clearer woring for pg_locks columns, identifying which items are
related to lock objects.

– In docs, start window function sentence with “The asterisk (*)”
rather than “*”; it is confusing to start a sentence with a symbol.

– Doc clarification for pg_locks columns. Florian Pflug

== Rejected Patches (for now) ==

No one was disappointed this week 🙂

== Pending Patches ==

Heikki Linnakangas sent in another revision of the patch to create a
latch implementation that wakes on postmaster death on both Win32 and

Shigeru HANADA sent in a patch to add a “force not NULL” option to the
file FDW.

Shigeru HANADA and Pavel Stehule traded patches to enhance PL/pgsql’s
GET DIAGNOSTICS by adding an optional STACKED keyword.

Fujii Masao sent in another revision of the patch to enable cascading

KaiGai Kohei sent in three more revisions of the patch to fix some
leaks in views.

KaiGai Kohei sent in another revision of the patch to add SECURITY
LABEL to shared database objects.

Comments are closed.