== PostgreSQL Weekly News - April 10 2011 ==

== PostgreSQL Weekly News - April 10 2011 ==

Austin PUG will be meeting April 13:
http://pugs.postgresql.org/austinpug

NYCPUG will meet April 14 at 6pm.  Details and RSVP below:
http://www.meetup.com/postgresql-3/events/17219508/

== PostgreSQL Product News ==

MicroOLAP Database Designer 1.8.0 Beta for PostgreSQL released.
http://microolap.com/products/database/postgresql-designer/

pgbouncer 1.4.1, a lightweight connection pooler for PostgreSQL, released.
http://pgfoundry.org/projects/pgbouncer/

PostgreSQL Multiple Precision Arithmetic 1.0b1 released:
http://pgmp.projects.postgresql.org/

Pyrseas 0.1.0, a toolkit for PostgreSQL version control, released.
http://www.pyrseas.org/

== PostgreSQL Jobs for April ==

http://archives.postgresql.org/pgsql-jobs/2011-04/threads.php

== PostgreSQL Local ==

There will be a large PostgreSQL presence at the MySQL Conference and
Expo, April 11-14, 2011 in Santa Clara, California.
http://en.oreilly.com/mysql2011/public/schedule/tag/postgresql

PostgreSQL Turkey User Group is organizing a one-day event at April
30, 2011.  Follow @PgDayTR for details in Turkish.
http://pgday.postgresql.org.tr/2011/

Open Database Camp will be on May 7-9, 2011 in Sardinia, Italy
http://datacharmer.blogspot.com/2011/01/announcing-open-database-camp-sardinia.html

PGCon will be May 19-20, 2011 at the University of Ottawa, preceded by
two days of tutorials on May 17-18.
http://www.pgcon.org/2011/

PG Session 2, on PostGIS, will be held on June 23rd in Paris.  The CfP
is open!
http://www.postgresql-sessions.org/en/2/

pgbr will be in Sao Paulo, Brazil November 3-4, 2011.
http://pgbr.postgresql.org.br/

== 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@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.  Spanish language
to pwn@arpug.com.ar.

== Reviews ==

== Applied Patches ==

Alvaro Herrera pushed:

- Duplicate expansion of "direction" from FETCH's synopsis into MOVE
  http://git.postgresql.org/pg/commitdiff/8c250f3741f4f897362de32a03f56726cf3f9774

- Uppercase <!ENTITY> and <!DOCTYPE> tags in SGML source.  This
  improves compatibility with external toolchains, such as those used
  by some documentation translation tools.  Gabriele Bartolini
  http://git.postgresql.org/pg/commitdiff/fc3459d70c5d842fe6519522649c8a47454f25bb

- Update MSVC toolchain to match SGML entity uppercasing.  Per Robert
  Haas
  http://git.postgresql.org/pg/commitdiff/34a991619bddf2bfb441cf2d4396b67198326bcd

- Change "Id" to "id" in some SGML tags.  Gabriele Bartolini
  http://git.postgresql.org/pg/commitdiff/38d15f16511cb9f75ed2b1e1f73117c1bc581538

Robert Haas pushed:

- Include pid in pg_lock_status() results even for SIREAD locks.  Dan
  Ports.
  http://git.postgresql.org/pg/commitdiff/a0e50e698beb89a8ec203303581e464a5f69fc41

- Add missing check on invocation of trusted procedures.  KaiGai Kohei
  http://git.postgresql.org/pg/commitdiff/595a441ae999fb155a381fa4bcceadf40f041a33

- Merge synchronous_replication setting into synchronous_commit.  This
  means one less thing to configure when setting up synchronous
  replication, and also avoids some ambiguity around what the behavior
  should be when the settings of these variables conflict.  Fujii
  Masao, with additional hacking by me.
  http://git.postgresql.org/pg/commitdiff/240067b3b0ff989d2b28f4f917f7958f2ec76b05

- Add casts from int4 and int8 to numeric.  Joey Adams, per gripe from
  Ramanujam.  Review by myself and Tom Lane.
  http://git.postgresql.org/pg/commitdiff/f5e524d92be609c709825be8995bf77f10880c3b

- Typo fixes.  Erik Rijkers
  http://git.postgresql.org/pg/commitdiff/ad275473a5ea75f4aa010791884004f75f3c7a65

- Repair some flakiness in CheckTargetForConflictsIn.  When we release
  and reacquire SerializableXactHashLock, we must recheck whether an
  R/W conflict still needs to be flagged, because it could have
  changed under us in the meantime.  And when we release the partition
  lock, we must re-walk the list of predicate locks from the
  beginning, because our pointer could get invalidated under us.  Bug
  report #5952 by Yamamoto Takashi.  Patch by Kevin Grittner.
  http://git.postgresql.org/pg/commitdiff/632f0faa7c975e8984ce0f1d6b631478e41a96c2

- Fix some sloppiness in new PL/python get_source_line() function.
  Jan Urbański
  http://git.postgresql.org/pg/commitdiff/b6bc481d5540a3ad0d39db1e9881e6bd52e54213

- Partially roll back overenthusiastic SSI optimization.  When a
  regular lock is held, Steve Singer can use that in lieu of a
  predicate lock to detect rw conflicts; but if the regular lock is
  being taken by a subtransaction, we can't assume that it'll commit,
  so releasing the parent transaction's lock in that case is a no-no.
  Kevin Grittner
  http://git.postgresql.org/pg/commitdiff/fbc0d07796bcae8b2ee4b09ed236877338c95130

- Fix bug in propagating ALTER TABLE actions to typed tables.  We need
  to propagate such actions to all typed table children of a given
  type, not just the first one.  Noah Misch
  http://git.postgresql.org/pg/commitdiff/0bd155cbf2543cdead70b6e6390e9260035e1468

- Make psql use pg_table_size instead of pg_relation_size on 9.0+
  servers.  Per discussion, pg_table_size() is a more helpful number
  than pg_relation_size().  Bernd Helmle, reviewed by Susanne Ebrecht
  and me.
  http://git.postgresql.org/pg/commitdiff/cba9cd419270a9b7f442877e21685ac0de5730b3

- Truncate the predicate lock SLRU to empty, instead of almost empty.
  Otherwise, the SLRU machinery can get confused and think that the
  SLRU has wrapped around.  Along the way, regardless of whether we're
  truncating all of the SLRU or just some of it, flush pages after
  truncating, rather than before.  Kevin Grittner
  http://git.postgresql.org/pg/commitdiff/cdcdfca40199bbdb30c78d105e4db267b631289c

Simon Riggs pushed:

- Update comments and credit doc proofreaders
  http://git.postgresql.org/pg/commitdiff/35d2a660fea51f8acae6aa0895875757fa09cb26

- Centralise release note items related to recovery and replication
  http://git.postgresql.org/pg/commitdiff/479ee1b962ca2d479310af762c21d7c2056fdeca

- Avoid assuming there will be only 3 states for synchronous_commit.
  Also avoid hardcoding the current default state by giving it the
  name "on" and replace with a meaningful name that reflects its
  behaviour.  Coding only, no change in behaviour.
  http://git.postgresql.org/pg/commitdiff/88f32b7ca29982f286b61a9d0dd29be4b8a01c25

Peter Eisentraut pushed:

- Update key words table for 9.1
  http://git.postgresql.org/pg/commitdiff/bf6848bc8c82e82f857d48185554bc3e6dcf1013

- Add traceback information to PL/Python errors.  This mimics the
  traceback information the Python interpreter prints with exceptions.
  Jan Urbański
  http://git.postgresql.org/pg/commitdiff/2bd78eb8d51cc9ee03ba0287b23ff4c266dcd9b9

- Replace the confusing exit_nicely() by an atexit/on_exit hook
  http://git.postgresql.org/pg/commitdiff/c75163842cf93fd7b6311df2c5545bf09447b870

- Update regression test files for PL/Python traceback patch.
  http://git.postgresql.org/pg/commitdiff/5d0e462366f4521e37744fdb42fed3c6819a3374

- Allow pg_upgrade with PGCLIENTENCODING set.  This used to work, but
  since PGCLIENTENCODING is now a connection option variable,
  pg_upgrade would prevent it.
  http://git.postgresql.org/pg/commitdiff/1eb2231fc46bbfa85b47c19d88f72162b323aa51

- Add collation support on Windows (MSVC build).  There is not yet
  support in initdb to populate the pg_collation catalog, but if that
  is done manually, the rest should work.
  http://git.postgresql.org/pg/commitdiff/11745364d074f3a7ee54c98fad55cfb5c5149326

Tom Lane pushed:

- Revise the API for GUC variable assign hooks.  The previous
  functions of assign hooks are now split between check hooks and
  assign hooks, where the former can fail but the latter shouldn't.
  Aside from being conceptually clearer, this approach exposes the
  "canonicalized" form of the variable value to guc.c without having
  to do an actual assignment.  And that lets us fix the problem
  recently noted by Bernd Helmle that the auto-tune patch for
  wal_buffers resulted in bogus log messages about "parameter
  "wal_buffers" cannot be changed without restarting the server".
  There may be some speed advantage too, because this design lets hook
  functions avoid re-parsing variable values when restoring a previous
  state after a rollback (they can store a pre-parsed representation
  of the value instead).  This patch also resolves a longstanding
  annoyance about custom error messages from variable assign hooks:
  they should modify, not appear separately from, guc.c's own message
  about "invalid parameter value".
  http://git.postgresql.org/pg/commitdiff/2594cf0e8c04406ffff19b1651c5a406d376657c

- Fix collations when we call transformWhereClause from outside the
  parser.  Previous patches took care of assorted places that call
  transformExpr from outside the main parser, but I overlooked the
  fact that some places use transformWhereClause as a shortcut for
  transformExpr + coerce_to_boolean.  In particular this broke
  collation-sensitive index WHERE clauses, as per report from Thom
  Brown.  Trigger WHEN and rule WHERE clauses too.  I'm not forcing
  initdb for this fix, but any affected indexes, triggers, or rules
  will need to be dropped and recreated.
  http://git.postgresql.org/pg/commitdiff/d8d429890d6aa0246e28faa187a55cb0c65efd6e

- Fix SortTocFromFile() to cope with lines that are too long for its
  buffer.  The original coding supposed that a dump TOC file could
  never contain lines longer than 1K.  The folly of that was exposed
  by a recent report from Per-Olov Esgard.  We only really need to see
  the first dozen or two bytes of each line, since we're just trying
  to read off the numeric ID at the start of the line; so there's no
  need for a particularly huge buffer.  What there is a need for is
  logic to not process continuation bufferloads.  Back-patch to all
  supported branches, since it's always been like this.
  http://git.postgresql.org/pg/commitdiff/1471a147f09f737511945ac59ca3162469d1a0c0

- Fix plpgsql's issues with dropped columns in rowtypes in 8.4 branch.
  This is a back-patch of commit dcb2bda9b7042dbf43f876c94ebf35d951de10e9
  of Aug 6 2009, which fixed assorted cases in which plpgsql would
  fail to cope with composite types that contain any dropped columns.
  Per discussion, this fix has been out in 9.0 for long enough to make
  it improbable that it creates any new bugs, so this is a low-risk
  fix.  To make it even lower risk, I did not back-patch the changes
  in execQual.c, but just accepted the duplication of code between
  there and tupconvert.c.  The added files tupconvert.h and
  tupconvert.c match their current states in HEAD.
  http://git.postgresql.org/pg/commitdiff/5d3853a7fa40b28b44b14084863fd83a188c9a9e

- Modernize dlopen interface code for FreeBSD and OpenBSD.  Remove the
  hard-wired assumption that __mips__ (and only __mips__) lacks dlopen
  in FreeBSD and OpenBSD.  This assumption is outdated at least for
  OpenBSD, as per report from an anonymous 9.1 tester.  We can
  perfectly well use HAVE_DLOPEN instead to decide which code to use.
  Some other cosmetic adjustments to make freebsd.c, netbsd.c, and
  openbsd.c exactly alike.
  http://git.postgresql.org/pg/commitdiff/73d9a908140e709b4cb12a8d2257a11baaec974f

- Tweaks for SSI out-of-shared memory behavior.  If we call
  hash_search() with HASH_ENTER, it will bail out rather than return
  NULL, so it's redundant to check for NULL again in the caller.
  Thus, in cases where we believe it's impossible for the hash table
  to run out of slots anyway, we can simplify the code slightly.  On
  the flip side, in cases where it's theoretically possible to run out
  of space, we don't want to rely on dynahash.c to throw an error;
  instead, we pass HASH_ENTER_NULL and throw the error ourselves if a
  NULL comes back, so that we can provide a more descriptive error
  message.  Kevin Grittner
  http://git.postgresql.org/pg/commitdiff/56c7140ca813ba4c7ecb122a1d2b1268e705d092

- Add an example of WITH (UPDATE RETURNING) INSERT to the INSERT ref
  page.  Per a discussion with Gavin Flower.  This barely scratches
  the surface of potential WITH (something RETURNING) use cases, of
  course, but it's one of the simplest compelling examples I can think
  of.
  http://git.postgresql.org/pg/commitdiff/8ad58279fa75636b776e4c648e1ffc7079a2c7d9

- Avoid extra whitespace in the arguments of <indexterm>.  As noted by
  Thom Brown, this confuses the DocBook index processor; it fails to
  merge entries that differ only in whitespace, and sorts them
  unexpectedly as well.  Seems like a toolchain bug, but I'm not going
  to hold my breath waiting for a fix.  Note: easiest way to find
  these is to look for double spaces in HTML.index.
  http://git.postgresql.org/pg/commitdiff/dca30da3433c40b5f92f1704c496cda052decef9

- Modify initdb to complain only when no usable system locales are
  found.  Per discussion, the original behavior seems too noisy.  But
  if things are so broken that none of the locales reported by
  "locale -a" are usable, that's probably worth warning about.
  http://git.postgresql.org/pg/commitdiff/d4c810d570e71b021c93e3ee3ea4ab18af4a053d

- Avoid an unnecessary syscache lookup in parse_coerce.c.  All the
  other fields of the constant are being extracted from the syscache
  entry we already have, so handle collation similarly.  (There don't
  seem to be any other uses for the new function at the moment.)
  http://git.postgresql.org/pg/commitdiff/c5ff3ff49229e8fb7da0e46b463bfc9b12219078

- Tweak collation setup for GIN index comparison functions.  Honor
  index column's collation spec if there is one, don't go to the
  expense of calling get_typcollation when we can reasonably assume
  that all GIN storage types will use default collation, and be sure
  to set a collation for the comparePartialFn too.
  http://git.postgresql.org/pg/commitdiff/1766a5b63a950a667ed39c25256156bf366eb43c

- Fix make_greater_string to not have an undocumented collation
  assumption.  The previous coding worked only if ltproc->fn_collation
  was always either DEFAULT_COLLATION_OID or a C-compatible locale.
  While that's true at the moment, it wasn't documented (and in fact
  wasn't true when this code was committed...).  But it only takes a
  couple more lines to make its internal caching behavior
  locale-aware, so let's do that.
  http://git.postgresql.org/pg/commitdiff/466dac8656c11317b91c1a4bfd5cfca4508cba94

- Clean up minor collation issues in indxpath.c.  Get rid of bogus
  collation test in match_special_index_operator (even for ILIKE, the
  pattern match operator's collation doesn't matter here, and even if
  it did the test was testing the wrong thing).  Fix broken looping
  logic in expand_indexqual_rowcompare.  Add collation check in
  match_clause_to_ordering_op.  Make naming and argument ordering more
  consistent; improve comments.
  http://git.postgresql.org/pg/commitdiff/69f1d5fe1456229aeea3096c372eb24cedc7d597

- Don't show unusable collations in psql's \dO command.  "Unusable"
  collations are those not matching the current database's encoding.
  The former behavior inconsistently showed such collations some of
  the time, depending on the details of the pattern argument.
  http://git.postgresql.org/pg/commitdiff/7c76906b7e24d9ea94a2b0e33396ebcac600437d

- Adjust collation determination rules as per discussion.  Remove
  crude hack that tried to propagate collation through a
  function-returning-record, ie, from the function's arguments to
  individual fields selected from its result record.  That is just
  plain inconsistent, because the function result is composite and
  cannot have a collation; and there's no hope of making this kind of
  action-at-a-distance work consistently.  Adjust regression test
  cases that expected this to happen.  Meanwhile, the behavior of
  casting to a domain with a declared collation stays the same as it
  was, since that seemed to be the consensus.
  http://git.postgresql.org/pg/commitdiff/a19002d4e5da028ff7280554b281e402c609898b

- Remove collate.linux.utf8.sql's assumptions about ".utf8" in locale
  names.  Tweak the test so that it does not depend on the platform
  using ".utf8" as the extension signifying that a locale uses UTF8
  encoding.  For the most part this just requires using the
  abbreviated collation names "en_US" etc, though I had to work a bit
  harder on the collation creation tests.  This opens the door to
  using the test on platforms that spell locales differently, for
  example ".utf-8" or ".UTF-8".  Also, the test is now somewhat useful
  with server encodings other than UTF8; though depending on which
  encoding is selected, different subsets of it will fail for lack of
  character set support.
  http://git.postgresql.org/pg/commitdiff/f89e4dfa755b9c9be9c19f7c65cff50d47b4f33a

- Fix ILIKE to honor collation when working in single-byte encodings.
  The original collation patch only fixed the multi-byte code path.
  This change also ensures that ILIKE's idea of the case-folding rules
  is exactly the same as str_tolower's.
  http://git.postgresql.org/pg/commitdiff/00f11f419caa873b44d70071d4ade0b8c75e7f36

- Clean up overly complex code for issuing some related error
  messages.  The original version was unreadable, and not mechanically
  checkable either.
  http://git.postgresql.org/pg/commitdiff/9a8b73147c07e02e10e0d0a34aa99d72e3336fb2

- Teach regular expression operators to honor collations.  This
  involves getting the character classification and case-folding
  functions in the regex library to use the collations infrastructure.
  Most of this work had been done already in connection with the
  upper/lower and LIKE logic, so it was a simple matter of
  transposition.  While at it, split out these functions into a
  separate source file regc_pg_locale.c, so that they can be correctly
  labeled with the Postgres project's license rather than the
  Scriptics license.  These functions are 100% Postgres-written code
  whereas what remains in regc_locale.c is still mostly not ours, so
  lumping them both under the same copyright notice was getting more
  and more misleading.
  http://git.postgresql.org/pg/commitdiff/1e16a8107db9a50435b39e09c6f9c52c45e63e1a

- Insert dummy "break"s to silence compiler complaints.  Apparently
  some compilers dislike a case label with nothing after it.  Per
  buildfarm.
  http://git.postgresql.org/pg/commitdiff/7aa3f1d08292d43b983075e190eee964c389e347

- Add some more mapping macros for Microsoft wide-character API.  Per
  buildfarm.
  http://git.postgresql.org/pg/commitdiff/f510fc1d904d3266fd433b49664a7685f4fc89ef

Bruce Momjian pushed:

- Simplify structure of query used to gather database object
  information in pg_upgrade.
  http://git.postgresql.org/pg/commitdiff/e69d32158c18069840130f62c5597ca1ef656175

- Preserve pg_largeobject_metadata.relfrozenxid in pg_upgrade.  This
  is needed only in 9.1 because only 9.0 had this and no one is
  upgrading from a 9.0 beta to 9.0 anymore.  We basically don't
  backpatch 9.0 beta fixes at this point.
  http://git.postgresql.org/pg/commitdiff/82a4f370735130418738f8cf0fb9155b1cff5e2b

- Have pg_upgrade properly preserve relfrozenxid in toast tables.
  This fixes a pg_upgrade bug that could lead to query errors when
  clog files are improperly removed.
  http://git.postgresql.org/pg/commitdiff/9c38bce29cb6d3ef49b932086d31f635981b07e9

- pgindent run before PG 9.1 beta 1.
  http://git.postgresql.org/pg/commitdiff/bf50caf105a901c4f83ac1df3cdaf910c26694a4

Andrew Dunstan pushed:

- Avoid use of mixed slash style paths in arguments to xcopy in MSVC
  builds.  Some versions of xcopy, notably on Windows 7 don't like it.
  Backpatch to 8.3, where we first used xcopy.
  http://git.postgresql.org/pg/commitdiff/a53112338c2f5b74383ce075fbec098cd06a3ad7

- Latest consolidated typedef list from buildfarm.
  http://git.postgresql.org/pg/commitdiff/fe1438da8aa8a45f2cee816eb54841f97d3b2f22

- Backport changes to allow building with MinGW 64 bit compiler.
  These changes have been in HEAD for some time with no ill effect.
  They are only being backported to 9.0, as the required WINNT version
  was not high enough before that.
  http://git.postgresql.org/pg/commitdiff/2ea865de5660f75f44f6b1ba78090e41e070cb4c

- Don't make "replication" magical as a user name, only as a database
  name, in pg_hba.conf.  Per gripe from Josh Berkus.
  http://git.postgresql.org/pg/commitdiff/ed557a373c406bbb2a1843544ebbd856ca4cac47

- Adjust regression tests on cube and ECPG for MinGW 64 bit compiler.
  Backport to 9.0, we're not supporting this compiler on earlier
  releases.
  http://git.postgresql.org/pg/commitdiff/210f95f1cd59c6fdfe0f84b922c19d8498ac377d

- Remove CRLF line endings in new cube results file.
  http://git.postgresql.org/pg/commitdiff/c1d82a936f6783f4b084810ff22ca01706f8054e

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Alexey Klyukin sent in an update of Selena Deckelmann's patch to
create a validator for config files.

Tom Lane sent in a patch to document the revised API for GUC assign
hooks via changes to src/backend/utils/misc/README.

Shigeru HANADA sent in patches to implement comments on USER MAPPINGs,
part of SQL/MED.

Peter Eisentraut sent in a patch to fix an issue in the Windows build.

Heikki Linnakangas sent in a patch to have the parser emit an InExpr
node type (similar to the extant CaseExpr), which allows the optimizer
to rearrange IN() to OR() in cases where it is safe to do so.

Shigeru HANADA sent in a patch to implement comments on FOREIGN DATA
WRAPPER and SERVER objects, which are part of SQL/MED.

Josh Kupershmidt sent in a doc patch suggesting using openjade 1.3
rather than 1.4devel as part of the doc build toolset.

KaiGai Kohei sent in a patch for the SE-PostgreSQL supplied module
which checks the process:transition permission between the labels
involved when a client's label changes.

Noah Misch sent in another patch to fix an incompatibility between
pg_dump --binary-upgrade and ALTER TYPE ... DROP ATTRIBUTE.

Noah Misch sent in a patch to fix an issue where
pg_attribute.attinhcount was incorrect.

Peter Eisentraut sent in a patch to add automatic pg_upgrade tests.

Peter Eisentraut sent in a patch to help fix some issues with
collation support on Windows.

Muhammad Usama sent in a patch to remove an extra linefeed from
vacuumlo.

Agent M sent in a patch to use POSIX shared memory, this one
accounting for multiple-postmaster start race conditions.