== PostgreSQL Weekly News - March 25 2012 ==

== PostgreSQL Weekly News - March 25 2012 ==

== PostgreSQL Product News ==

devart dotConnect 5.80 for PostgreSQL, an ADO.NET provider, released.
http://www.devart.com/dotconnect/postgresql/

phpPgAdmin 5.0.4, a web-based administrative tool for PostgreSQL, released.
http://phppgadmin.sourceforge.net/

== PostgreSQL Jobs for March ==

http://archives.postgresql.org/pgsql-jobs/2012-03/threads.php

== PostgreSQL Local ==

PGDay Austin 2012 will be held March 28.
http://www.postgresql.org/about/event/1379/

Initial meeting of Arizona PostgreSQL Users Group at 5:00 PM on
Thursday, March 29, 2012. Included will be a presentation by Ken
Rosensteel that is an update of his 2011 PGEast presentation on Bull's
experiences with Large Customer Usage of PostgreSQL. You can join the
mailing list, see the agenda, and RSVP at:
https://www.bigtent.com/groups/azpug

PGDay DC 2012 will be held on March 30.
http://pgday.bwpug.org

PGDay NYC will be held April 2, 2012 at Lighthouse International in
New York City.
http://pgday.nycpug.org

London PostgreSQL Evening Meetup, 17 April 2012
http://www.meetup.com/London-PostgreSQL-Meetup-Group/

PGCon 2012 will be held 17-18 May 2012, in Ottawa at the University of
Ottawa. It will be preceded by two days of tutorials on 15-16 May 2012.
http://www.pgcon.org/2012/

PGDay France will be in Lyon on June 7, 2012.
http://www.pgday.fr

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

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 david(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot) Spanish language
to pwn(at)arpug(dot)com(dot)ar(dot)

== Applied Patches ==

Bruce Momjian pushed:

- In pg_upgrade, remove dependency on pg_config, as that might not be
in the non-development install. Instead, use the LOAD mechanism to
check for the pg_upgrade_support shared object, like we do for other
shared object checks. Backpatch to 9.1. Report from Alvaro Herrera
http://git.postgresql.org/pg/commitdiff/02b183acb42831bbeba819fde568263f19f2acdc

Alvaro Herrera pushed:

- plperl: Package-qualify _TD. Failing to do so causes trigger
invocation to fail when they are nested within a function invocation
that changes the current package. Backpatch to 9.1; previous
releases used a different method to obtain _TD. Per bug report from
Mark Murawski (bug #6511) Author: Alex Hunsaker
http://git.postgresql.org/pg/commitdiff/814e08e895a037e3f5485000db5964cd68dbfa01

- pg_dump: fix double free of query results. This bug was introduced
while refactoring in commit 1631598e --- no need to back-patch. Bug
report and fix from Joachim Wieland.
http://git.postgresql.org/pg/commitdiff/77503a7638a35eedd9cb08d9ca4c54deb203521d

- Update struct Trigger in docs
http://git.postgresql.org/pg/commitdiff/438269eb104446ff0c36d893cd6f01be8405d327

- Fix trigger example code to match header changes. I should have
done this in b93f5a5673b4bb09e14eb80fe28aa21fc20a6271 but didn't
notice the problem at the time. Per report from Marco Nenciarini
http://git.postgresql.org/pg/commitdiff/b251cf31936b4507ad8ba78f34a14f592c664f43

- pg_dump: get rid of die_horribly. The old code was using
exit_horribly or die_horribly other depending on whether it had an
ArchiveHandle on which to close the connection or not; but there
were places that were passing a NULL ArchiveHandle to die_horribly,
and other places that used exit_horribly while having an Alvaro
Herrera available. So there wasn't all that much consistency.
Improve the situation by keeping only one of the routines, and
instead of having to pass the Alvaro Herrera down from the caller,
arrange for it to be present for an on_exit_nicely callback to
operate on. Author: Joachim Wieland. Some tweaks by me Per a
suggestion from Robert Haas, in the ongoing "parallel pg_dump" saga.
http://git.postgresql.org/pg/commitdiff/9d23a70d513aa1312135d6cedd444e7e3e933edb

Tom Lane pushed:

- Restructure SELECT INTO's parsetree representation into
CreateTableAsStmt. Making this operation look like a utility
statement seems generally a good idea, and particularly so in light
of the desire to provide command triggers for utility statements.
The original choice of representing it as SELECT with an IntoClause
appendage had metastasized into rather a lot of places,
unfortunately, so that this patch is a great deal more complicated
than one might at first expect. In particular, keeping EXPLAIN
working for SELECT INTO and CREATE TABLE Alexander Shulgin
subcommands required restructuring some EXPLAIN-related APIs.
Add-on code that calls ExplainOnePlan or ExplainOneUtility, or uses
ExplainOneQuery_hook, will need adjustment. Also, the cases PREPARE
... SELECT INTO and CREATE RULE ... SELECT INTO, which formerly were
accepted though undocumented, are no longer accepted. The PREPARE
case can be replaced with use of CREATE TABLE Alexander Shulgin
EXECUTE. The CREATE RULE case doesn't seem to have much real-world
use (since the rule would work only once before failing with "table
already exists"), so we'll not bother with that one. Both SELECT
INTO and CREATE TABLE Alexander Shulgin still return a command tag
of "SELECT nnnn". There was some discussion of returning "CREATE
TABLE nnnn", but for the moment backwards compatibility wins the
day. Andres Freund and Tom Lane
http://git.postgresql.org/pg/commitdiff/9dbf2b7d75de5af38d087cbe2b1147dd0fd10f0a

- Improve the -l (limit) option recently added to contrib/vacuumlo.
Instead of just stopping after removing an arbitrary subset of
orphaned large objects, commit and start a new transaction after
each -l objects. This is just as effective as the original patch at
limiting the number of locks used, and it doesn't require doing the
OID collection process repeatedly to get everything. Since the
option no longer changes the fundamental behavior of vacuumlo, and
it avoids a known server-side limitation, enable it by default (with
a default limit of 1000 LOs per transaction). In passing, be more
careful about properly quoting the names of tables and fields, and
do some other cosmetic cleanup.
http://git.postgresql.org/pg/commitdiff/64c604898e812aa93c124c666e8709fff1b8dd26

- Back-patch contrib/vacuumlo's new -l (limit) option into 9.0 and
9.1. Since 9.0, removing lots of large objects in a single
transaction risks exceeding max_locks_per_transaction, because we
merged large object removal into the generic object-drop mechanism,
which takes out an exclusive lock on each object to be dropped.
This creates a hazard for contrib/vacuumlo, which has historically
tried to drop all unreferenced large objects in one transaction.
There doesn't seem to be any correctness requirement to do it that
way, though; we only need to drop enough large objects per
transaction to amortize the commit costs. To prevent a regression
from pre-9.0 releases wherein vacuumlo worked just fine, back-patch
commits b69f2e36402aaa222ed03c1769b3de6d5be5f302 and
64c604898e812aa93c124c666e8709fff1b8dd26, which break vacuumlo's
deletions into multiple transactions with a user-controllable upper
limit on the number of objects dropped per transaction. Tim Lewis,
Robert Haas, Tom Lane
http://git.postgresql.org/pg/commitdiff/5bd06e619c82c3b2e29fed40aae5fc39a9620908

- Allow new relmapper entries when allow_system_table_mods is true.
This restores the pre-9.0 situation that it's possible to add new
indexes on pg_class and other mapped-but-not-shared catalogs, so
long as you broke the glass and flipped the big red Dont-Touch-Me
switch. As before, there are a lot of gotchas, and you'd have to be
pretty desperate to try this on a production database; but there
doesn't seem to be a reason for relmapper.c to be preventing such
things all by itself. Per experimentation with a case suggested by
Cody Cutrer.
http://git.postgresql.org/pg/commitdiff/f70f095c9096d5e2689e8d79172b37b57a84e51b

- Fix configure's search for collateindex.pl. PGAC_PATH_COLLATEINDEX
supposed that it could use AC_PATH_PROGS to search for
collateindex.pl, but that macro will only accept files that are
marked executable, and at least some DocBook installations don't
mark the script executable (a case the docs Makefile was already
prepared for). Accept the script if it's present and readable in
$DOCBOOKSTYLE/bin, and otherwise search the PATH as before. Having
fixed that up, we don't need the fallback case that was in the docs
Makefile, and instead can throw an understandable error if configure
didn't find the script. Per recent trouble report from John Lumby.
http://git.postgresql.org/pg/commitdiff/f2386d7136dacbb8bf59ebbe8e5c5b73db202813

- If a role has a password expiration date, show that in psql's \du
output. Per a suggestion from Euler Taveira, it seems like a good
idea to include this information in \du (and \dg) output. This
costs nothing for people who are not using the VALID UNTIL feature,
while for those who are, it's rather critical information. Fabrízio
de Royes Mello
http://git.postgresql.org/pg/commitdiff/04dfc8774966c60e4031ab5d8eeb3263d9568ad3

- Refactor to eliminate duplicate copies of conninfo default-finding
code. Alexander Shulgin, lightly edited by me.
http://git.postgresql.org/pg/commitdiff/e9ce658b6203e9052bff5c89ed6265fba69cdb36

- Fix GET DIAGNOSTICS for case of assignment to function's first
variable. An incorrect and entirely unnecessary "safety check" in
exec_stmt_getdiag() caused the code to treat an assignment to a
variable with dno zero as a no-op. Unfortunately, that's a
perfectly valid dno. This has been broken since GET DIAGNOSTICS was
invented. It's not terribly surprising that the bug went unnoticed
for so long, since in most cases you probably wouldn't use the
function's first-created variable (normally its first parameter) as
a GET DIAGNOSTICS target. Nonetheless, it's broken. Per bug #6551
from Adam Buraczewski.
http://git.postgresql.org/pg/commitdiff/88a4cb30a4c48a6b11fd4641855595d2678aa123

- Code review for protransform patches. Fix loss of previous
expression-simplification work when a transform function fires: we
must not simply revert to untransformed input tree. Instead build a
dummy FuncExpr node to pass to the transform function. This has the
additional advantage of providing a simpler, more uniform API for
transform functions. Move documentation to a somewhat less buried
spot, relocate some poorly-placed code, be more wary of null
constants and invalid typmod values, add an opr_sanity check on
protransform function signatures, and some other minor cosmetic
adjustments. Note: although this patch touches pg_proc.h, no need
for catversion bump, because the changes are cosmetic and don't
actually change the intended catalog contents.
http://git.postgresql.org/pg/commitdiff/0339047bc93147c1c6f78f867ae6b0c215406235

- Refactor simplify_function et al to centralize argument
simplification. We were doing the recursive simplification of
function/operator arguments in half a dozen different places, with
rather baroque logic to ensure it didn't get done multiple times on
some arguments. This patch improves that by postponing argument
simplification until after we've dealt with named parameters and
added any needed default expressions. Marti Raudsepp, somewhat
hacked on by me
http://git.postgresql.org/pg/commitdiff/81a646febe87964725647a36d839f6b4b405f3ae

- Cast some printf arguments to avoid possibly-nonportable behavior.
Per compiler warnings on buildfarm member black_firefly.
http://git.postgresql.org/pg/commitdiff/ed61127be483d8939e13a151773549f1517d6e67

- Fix planner's handling of outer PlaceHolderVars within subqueries.
For some reason, in the original coding of the PlaceHolderVar
mechanism I had supposed that PlaceHolderVars couldn't propagate
into subqueries. That is of course entirely possible. When it
happens, we need to treat an outer-level PlaceHolderVar much like an
outer Var or Aggref, that is SS_replace_correlation_vars() needs to
replace the PlaceHolderVar with a Param, and then when building the
finished SubPlan we have to provide the PlaceHolderVar expression as
an actual parameter for the SubPlan. The handling of the contained
expression is a bit delicate but it can be treated exactly like an
Aggref's expression. In addition to the missing logic in
subselect.c, prepjointree.c was failing to search subqueries for
PlaceHolderVars that need their relids adjusted during subquery
pullup. It looks like everyplace else that touches PlaceHolderVars
got it right, though. Per report from Mark Murawski. In 9.1 and
HEAD, queries affected by this oversight would fail with "ERROR:
Upper-level PlaceHolderVar found where not expected". But in 9.0
and 8.4, you'd silently get possibly-wrong answers, since the value
transmitted into the subquery wouldn't go to null when it should.
http://git.postgresql.org/pg/commitdiff/8279eb4191c7ab9920c72ec8eec5df0e7b8c7530

- Replace empty locale name with implied value in CREATE DATABASE and
initdb. setlocale() accepts locale name "" as meaning "the locale
specified by the process's environment variables". Historically
we've accepted that for Postgres' locale settings, too. However,
it's fairly unsafe to store an empty string in a new database's
pg_database.datcollate or datctype fields, because then the
interpretation could vary across postmaster restarts, possibly
resulting in index corruption and other unpleasantness. Instead, we
should expand "" to whatever it means at the moment of calling
CREATE DATABASE, which we can do by saving the value returned by
setlocale(). For consistency, make initdb set up the initial lc_xxx
parameter values the same way. initdb was already doing the right
thing for empty locale names, but it did not replace non-empty names
with setlocale results. On a platform where setlocale chooses to
canonicalize the spellings of locale names, this would result in
annoying inconsistency. (It seems that popular implementations of
setlocale don't do such canonicalization, which is a pity, but the
POSIX spec certainly allows it to be done.) The same risk of
inconsistency leads me to not venture back-patching this, although
it could certainly be seen as a longstanding bug. Per report from
Jeff Davis, though this is not his proposed patch.
http://git.postgresql.org/pg/commitdiff/c7cea267de3ca05b29a57b9d113b95ef3793c8d8

- Fix COPY FROM for null marker strings that correspond to invalid
encoding. The COPY documentation says "COPY FROM matches the input
against the null string before removing backslashes". It is
therefore reasonable to presume that null markers like E'\\0' will
work ... and they did, until someone put the tests in the wrong
order during microoptimization-driven rewrites. Since then, we've
been failing if the null marker is something that would de-escape to
an invalidly-encoded string. Since null markers generally need to
be something that can't appear in the data, this represents a
nontrivial loss of functionality; surprising nobody noticed it
earlier. Per report from Jeff Davis. Backpatch to 8.4 where this
got broken.
http://git.postgresql.org/pg/commitdiff/e8476f46fc847060250c92ec9b310559293087fc

Peter Eisentraut pushed:

- pg_upgrade: Add new generated file to .gitignore
http://git.postgresql.org/pg/commitdiff/1b12c3e836676e8e94ba94e15c25b100ce0debf0

- pg_dump: Remove undocumented "files" output format. This was for
demonstration only, and now it was creating compiler warnings from
zlib without an obvious fix (see also
d923125b77c5d698bb8107a533a21627582baa43), let's just remove it.
The "directory" format is presumably similar enough anyway.
http://git.postgresql.org/pg/commitdiff/19f45565f581ce605956c29586bfd277f6012eec

- Clean up compiler warnings from unused variables with asserts
disabled. For those variables only used when asserts are enabled,
use a new macro PG_USED_FOR_ASSERTS_ONLY, which expands to
__attribute__((unused)) when asserts are not enabled.
http://git.postgresql.org/pg/commitdiff/0e85abd658738db9ea183c133c89c3c94ee37dae

- Add installing entab to pgindent instructions. And minor other
pgindent documentation tweaks.
http://git.postgresql.org/pg/commitdiff/621eb156f159d4979193939902f49ea597972a7d

Robert Haas pushed:

- Remove stray word from sepgsql documentation.
http://git.postgresql.org/pg/commitdiff/0b43edbca708a33a83d6842e96b837ad77f3e5bc

- Add some CHECK_FOR_INTERRUPTS() calls to the heap-sort call path. I
broke this in commit 337b6f5ecf05b21b5e997986884d097d60e4e3d0, which
among other things arranged for quicksorts to CHECK_FOR_INTERRUPTS()
slightly less frequently. Sadly, it also arranged for heapsorts to
CHECK_FOR_INTERRUPTS() much less frequently. Repair.
http://git.postgresql.org/pg/commitdiff/aefa6d163e21391626c20a508af33373d6ba387e

- Improve connectMaintenanceDatabase() error reporting. The prior
coding instructs the user to pick an alternative maintenance
database, but this is overly clever, since it obscures whatever the
real cause of the failure is. Josh Kupershmidt
http://git.postgresql.org/pg/commitdiff/99081c63e40ac6e33c83406b0958aa9426193fea

- Doc updates for index-only scans. Document that routine vacuuming
is now also important for the purpose of index-only scans; and
mention in the section that describes the visibility map that it is
used to implement index-only scans. Marti Raudsepp, with some
changes by me.
http://git.postgresql.org/pg/commitdiff/5b9c1e6d52dfd022a074462e029bfd7f7bc4321c

- Update docs on numeric storage requirements. Since 9.1, the minimum
overhead is three bytes, not five. Fujii Masao
http://git.postgresql.org/pg/commitdiff/e83f827f5abeeca4750b40bd6d8701d91fead8dd

- Doc clarifications regarding use of varlena. Jay Levitt, reviewed
by Tom Lane.
http://git.postgresql.org/pg/commitdiff/fdf719cd2868144b5bd78971217bb36bb970e15e

- Clarify that PQconninfoParse returns an array with all legal
options. Per discussion with Dmitriy Igrishin and Tom Lane.
http://git.postgresql.org/pg/commitdiff/e08b4101e1daa2f4e6644330918177a10cac0aab

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Marco Nenciarini sent in another revision of the patch to support
arrays each element of which is a foreign key.

Andres Freund sent in a patch to check that the specified tablespace
in a CREATE TABLE AS command is not pg_global.

Daniel Farina sent in another revision of the patch to allow users to
terminate their own backends.

Dimitri Fontaine sent in another revision of the patch to implement
command triggers.

Robert Haas sent in a patch to remove a dead code path in
heap_freeze_tuple().

Dimitri Fontaine sent in three more revisions of the patch to make
finer EXTENSION dependencies.

Ants Aasma sent in another revision of the patch to add timing of
buffer I/O requests.

Peter Geoghegan sent in another revision of the patch to publish
checkpoint timing and sync files summary data to pg_stat_bgwriter.

Alexander Shulgin sent in another revision of the patch to add a URI
connection string to libpq.

Peter Eisentraut sent in another revision of the patch to allow
renaming a DOMAIN constraint.

Marko Kreen sent in another revision of the patch to create a new,
more efficient tuple storage in libpq and use same in dblink.

David Fetter sent in two more revisions of a patch to allow CREATE
FOREIGN TABLE to take a LIKE clause.

Fujii Masao sent in three revisions of a patch to change
pg_controldata so that it reports the name of WAL file containing the
latest checkpoint's REDO record.

Zoltan Boszormenyi sent in another revision of the patch to enable
ECPG fetch readahead.

Joachim Wieland sent in another revision of the patch to enable
parallel pg_dump.

Jeff Davis sent in another revision of the patch to fix initdb's
behavior with fsync.

Peter Geoghegan sent in another revision of the patch to allow
pg_stat_statements to be normalized.