== PostgreSQL Weekly News - April 17 2011 ==

== PostgreSQL Weekly News - April 17 2011 ==

PostgreSQL update releases 9.0.4, 8.4.8, 8.3.15, and 8.2.21 will be
out soon.  Get ready to upgrade!

== PostgreSQL Product News ==

tail_n_mail 1.18.1, a Postgres log watcher program, released:
http://bucardo.org/wiki/Tail_n_mail

Bucardo 4.4.3, a Postgres multi-master replication system, released:
http://bucardo.org/wiki/Bucardo

== PostgreSQL Jobs for April ==

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

== PostgreSQL Local ==

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 ==

Heikki Linnakangas pushed:

- TransferPredicateLocksToNewTarget should initialize a new lock
  entry's commitSeqNo to that of the old one being transferred, or
  take the minimum commitSeqNo if it is merging two lock entries.
  Also, CreatePredicateLock should initialize commitSeqNo for to
  InvalidSerCommitSeqNo instead of to 0. (I don't think using 0 would
  actually affect anything, but we should be consistent.) I also added
  a couple of assertions I used to track this down: a lock's
  commitSeqNo should never be zero, and it should be
  InvalidSerCommitSeqNo if and only if the lock is not held by
  OldCommittedSxact.  Dan Ports, to fix leak of predicate locks
  reported by YAMAMOTO Takashi.
  http://git.postgresql.org/pg/commitdiff/dad1f4638235e5ff5696b948b88ba24cd99b415e

- Fix the size of predicate lock manager's shared memory hash tables
  at creation.  This way they don't compete with the regular lock
  manager for the slack shared memory, making the behavior more
  predictable.
  http://git.postgresql.org/pg/commitdiff/7c797e7194d969f974abf579cacf30ffdccdbb95

- On IA64 architecture, we check the depth of the register stack in
  addition to the regular stack. The code to do that is platform and
  compiler specific, add support for the HP-UX native compiler.
  http://git.postgresql.org/pg/commitdiff/b5bb040da63806f7386b3434c38e1fa98860ea88

- Revert the patch to check if we've reached end-of-backup also when
  doing crash recovery, and throw an error if not. hubert depesz
  lubaczewski pointed out that that situation also happens in the
  crash recovery following a system crash that happens during an
  online backup.  We might want to do something smarter in 9.1, like
  put the check back for backups taken with pg_basebackup, but that's
  for another patch.
  http://git.postgresql.org/pg/commitdiff/54685b1c2b356b4b9c0938f6b8dcc52e173c0400

- On HP/UX, the structs used by ioctl(SIOCGLIFCONF) are named
  differently than on other platforms, and only IPv6 addresses are
  returned.  Because of those two issues, fall back to
  ioctl(SIOCGIFCONF) on HP/UX, so that it at least compiles and finds
  IPv4 addresses. This function is currently only used for
  interpreting samehost/samenet in pg_hba.conf, which isn't that
  critical.
  http://git.postgresql.org/pg/commitdiff/40e64017f3a4f1f7547dcbc62c2dcf80511ff842

- Reduce the initial size of local lock hash to 16 entries.  The hash
  table is seq scanned at transaction end, to release all locks, and
  making the hash table larger than necessary makes that slower.  With
  very simple queries, that overhead can amount to a few percent of
  the total CPU time used.  At the moment, backend startup needs 6
  locks, and a simple query with one table and index needs 3 locks. 16
  is enough for even quite complicated transactions, and it will grow
  automatically if it fills up.
  http://git.postgresql.org/pg/commitdiff/4c37c1e3b2a7ba7b5519e5e366720e7444878a78

- setlocale() on Windows doesn't work correctly if the locale name
  contains apostrophes or dots. There isn't much hope of Microsoft
  fixing it any time soon, it's been like that for ages, so we better
  work around it. So, map a few common Windows locale names known to
  cause problems to aliases that work.
  http://git.postgresql.org/pg/commitdiff/d5a7bf8c11c8b66c822bbb1a6c90e1a14425bd6e

- On Windows, if the encoding implied by locale is not allowed as a
  server-encoding, fall back to UTF-8. It happens at least with the
  Chinese locale, which implies BIG5. This is safe, because on Windows
  all locales are compatible with UTF-8.
  http://git.postgresql.org/pg/commitdiff/1f943dc8fe1377d93253fad9f01e4b0aa8599bbd

Tom Lane pushed:

- Teach pattern_fixed_prefix() about collations.  This is necessary,
  not optional, now that ILIKE and regexes are collation aware ---
  else we might derive a wrong comparison constant for index optimized
  pattern matches.
  http://git.postgresql.org/pg/commitdiff/3c381a55b0fa1c4ca328211e39df8e1a715129cb

- Fix RI_Initial_Check to use a COLLATE clause when needed in its
  query.  If the referencing and referenced columns have different
  collations, the parser will be unable to resolve which collation to
  use unless it's helped out in this way.  The effects are sometimes
  masked, if we end up using a non-collation-sensitive plan; but if we
  do use a mergejoin we'll see a failure, as recently noted by Robert
  Haas.  The SQL spec states that the referenced column's collation
  should be used to resolve RI checks, so that's what we do.  Note
  however that we currently don't append a COLLATE clause when writing
  a query that examines only the referencing column.  If we ever
  support collations that have varying notions of equality, that will
  have to be changed.  For the moment, though, it's preferable to
  leave it off so that we can use a normal index on the referencing
  column.
  http://git.postgresql.org/pg/commitdiff/921b993677e165607029a52e7f866bbd112345a5

- Be more wary of missing statistics in eqjoinsel_semi().  In
  particular, if we don't have real ndistinct estimates for both
  sides, fall back to assuming that half of the left-hand rows have
  join partners.  This is what was done in 8.2 and 8.3 (cf
  nulltestsel() in those versions).  It's pretty stupid but it won't
  lead us to think that an antijoin produces no rows out, as seen in
  recent example from Uwe Schroeder.
  http://git.postgresql.org/pg/commitdiff/3f5d2fe3029b181fe773a02f1d4b34624c357634

- Suppress compiler warnings about "value computed is not used".  The
  recent patch to remove gcc 4.6 warnings created some new ones, at
  least on my rather old gcc version.  Try to make everybody happy by
  casting to "void" when we just want to discard the result.
  http://git.postgresql.org/pg/commitdiff/88543ecfec9c754b5f14b898bccbc68d941748b3

- Pass collations to functions in FunctionCallInfoData, not FmgrInfo.
  Since collation is effectively an argument, not a property of the
  function, FmgrInfo is really the wrong place for it; and this
  becomes critical in cases where a cached FmgrInfo is used for
  varying purposes that might need different collation settings.  Fix
  by passing it in FunctionCallInfoData instead.  In particular this
  allows a clean fix for bug #5970 (record_cmp not working).  This
  requires touching a bit more code than the original method, but
  nobody ever thought that collations would not be an invasive
  patch...
  http://git.postgresql.org/pg/commitdiff/d64713df7e5996ab3ab337b5e0901cf2c53773f9

- Fix broken pg_dump query.  The 8.4 branch failed when talking to 7.0
  servers.  Per testing requested by Bruce.
  http://git.postgresql.org/pg/commitdiff/a2f9219e7053bf2395c50b198379d1073dbc2298

- Add some useful cross-references to the CREATE EXTENSION reference
  page.  Per a suggestion from Josh Kupershmidt, though I modified his
  patch quite a lot.
  http://git.postgresql.org/pg/commitdiff/dd95a1d3dd52cb9848d54ab9962156f666c4ca39

- Update time zone data files to tzdata release 2011f.  DST law
  changes in Chile, Cuba, Falkland Islands, Morocco, Samoa, Turkey.
  Historical corrections for South Australia, Alaska, Hawaii.
  http://git.postgresql.org/pg/commitdiff/170aeb54074ae2e21b22b79d1dd5c665700f7025

- Ensure mark_dummy_rel doesn't create dangling pointers in
  RelOptInfos.  When we are doing GEQO join planning, the current
  memory context is a short-lived context that will be reset at the
  end of geqo_eval().  However, the RelOptInfos for base relations are
  set up before that and then re-used across many GEQO cycles.  Hence,
  any code that modifies a baserel during join planning has to be
  careful not to put pointers to the short-lived context into the
  baserel struct.  mark_dummy_rel got this wrong, leading to
  easy-to-reproduce-once-you-know-how crashes in 8.4, as reported
  off-list by Leo Carson of SDSC.  Some improvements made in 9.0 make
  it difficult to demonstrate the crash in 9.0 or HEAD; but there's no
  doubt that there's still a risk factor here, so patch all branches
  that have the function.  (Note: 8.3 has a similar function, but it's
  only applied to joinrels and thus is not a hazard.)
  http://git.postgresql.org/pg/commitdiff/eca75a12a27d28b972fc269c1c8813cd8eb15441

- Update release notes for releases 9.0.4, 8.4.8, 8.3.15, and 8.2.21.
  http://git.postgresql.org/pg/commitdiff/98eded936c1e81e6af5a4228ba3eb7b6352d6c91

- Guard against incoming rowcount estimate of NaN in cost_mergejoin().
  Although rowcount estimates really ought not be NaN, a bug elsewhere
  could perhaps result in that, and that would cause Assert failure in
  cost_mergejoin, which I believe to be the explanation for bug #5977
  from Anton Kuznetsov.  Seems like a good idea to expend a couple
  more cycles to prevent that, even though the real bug is elsewhere.
  Not back-patching, though, because we don't encourage running
  production systems with Asserts on.
  http://git.postgresql.org/pg/commitdiff/72826fb362c4aada6d2431df0b706df448806c02

- Suppress unused-function warning on non-WIN32 builds.
  http://git.postgresql.org/pg/commitdiff/ff5565f0a4aa3c584792d220a3e566369a77f479

- Prevent incorrect updates of pg_index while reindexing pg_index
  itself.  The places that attempt to change pg_index.indcheckxmin
  during a reindexing operation cannot be executed safely if pg_index
  itself is the subject of the operation.  This is the explanation for
  a couple of recent reports of VACUUM FULL failing with
    ERROR: duplicate key value violates unique constraint "pg_index_indexrelid_index"
    DETAIL:  Key (indexrelid)=(2678) already exists.
  However, there isn't any real need to update indcheckxmin in such a
  situation, if we assume that pg_index can never contain a truly
  broken HOT chain.  This assumption holds if new indexes are never
  created on it during concurrent operations, which is something we
  don't consider safe for any system catalog, not just pg_index.
  Accordingly, modify the code to not manipulate indcheckxmin when
  reindexing any system catalog.  Back-patch to 8.3, where HOT was
  introduced.  The known failure scenarios involve 9.0-style VACUUM
  FULL, so there might not be any real risk before 9.0, but let's not
  assume that.
  http://git.postgresql.org/pg/commitdiff/4b6106ccfea21e86943f881edcf3cfc03661a415

- Clean up collation processing in prepunion.c.  This area was a few
  bricks shy of a load, and badly under-commented too.  We have to
  ensure that the generated targetlist entries for a set-operation
  node expose the correct collation for each entry, since higher-level
  processing expects the tlist to reflect the true ordering of the
  plan's output.  This hackery wouldn't be necessary if
  SortGroupClause carried collation info ... but making it do so would
  inject more pain in the parser than would be saved here.  Still, we
  might want to rethink that sometime.
  http://git.postgresql.org/pg/commitdiff/121f49a00e432ee9cfad7270d99504350cd1015f

- Simplify reindex_relation's API.  For what seem entirely historical
  reasons, a bitmask "flags" argument was recently added to
  reindex_relation without subsuming its existing boolean argument
  into that bitmask.  This seems a bit bizarre, so fold them together.
  http://git.postgresql.org/pg/commitdiff/2d3320d3d256eed7ea3deb8383348f2021e6a247

- Add an Assert that indexam.c isn't used on an index awaiting
  reindexing.  This might have caught the recent embarrassment over
  trying to modify pg_index while its indexes were being rebuilt.
  Noah Misch
  http://git.postgresql.org/pg/commitdiff/d2f60a3ab055fb61c8e1056a7c5652f1dec85e00

- foreach() and list_delete() don't mix.  Fix crash when releasing
  duplicate entries in the encoding conversion cache list, caused by
  releasing the current entry of the list being chased by foreach().
  We have a standard idiom for handling such cases, but this loop
  wasn't using it.  This got broken in my recent rewrite of GUC assign
  hooks.  Not sure how I missed this when testing the modified code,
  but I did.  Per report from Peter.
  http://git.postgresql.org/pg/commitdiff/88dc6fa7a164c306d8a1295769edb818d8520a3f

- Support a COLLATE clause in plpgsql variable declarations.  This
  allows the usual rules for assigning a collation to a local variable
  to be overridden.  Per discussion, it seems appropriate to support
  this rather than forcing all local variables to have the
  argument-derived collation.
  http://git.postgresql.org/pg/commitdiff/c94732585647437291ec3f4a9902eeffc65a6945

- Add check for matching column collations in ALTER TABLE ... INHERIT.
  The other DDL operations that create an inheritance relationship
  were checking for collation match already, but this one got missed.
  Also fix comments that failed to mention collation checks.
  http://git.postgresql.org/pg/commitdiff/49a642ab1802ea4cd80f9c184e7699219688197f

- Fix pg_dump to handle collations applied to columns of composite
  types.  CREATE TYPE and ALTER TYPE ADD ATTRIBUTE handle this, so I
  suppose it's an intended feature, but pg_dump didn't know about it.
  http://git.postgresql.org/pg/commitdiff/acfa1f45ed3ee2144581954c77769056bd149f5c

- Document COLLATE option in CREATE TYPE reference page.  Curiously,
  it was already documented in ALTER TYPE ADD ATTRIBUTE, but not here.
  http://git.postgresql.org/pg/commitdiff/2d4617126f6d708c5777b378d9ef282fb2c08930

- Fix assorted infelicities in collation handling in psql's
  describe.c.  In \d, be more careful to print collation only if it's
  not the default for the column's data type.  Avoid assuming that the
  name "default" is magic.  Fix \d on a composite type so that it will
  print per-column collations.  It's no longer the case that a
  composite type cannot have modifiers.  (In consequence, the expected
  outputs for composite-type regression tests change.) Fix \dD so that
  it will print collation for a domain, again only if it's not the
  same as the base type's collation.
  http://git.postgresql.org/pg/commitdiff/c29abc8b6f5334ac2f7046a33b233776ead12395

Peter Eisentraut pushed:

- Clean up most -Wunused-but-set-variable warnings from gcc 4.6.  This
  warning is new in gcc 4.6 and part of -Wall.  This patch cleans up
  most of the noise, but there are some still warnings that are
  trickier to remove.
  http://git.postgresql.org/pg/commitdiff/5caa3479c2efd31fca1b271db687e5e57c7de2d4

- Translation updates.
  http://git.postgresql.org/pg/commitdiff/65a188e549a8bf7d0e180d7bbbf6823e77c953bb

- Rename pg_regress option --multibyte to --encoding.  Also refactor
  things a little bit so that the same methods for setting test locale
  and encoding can be used everywhere.
  http://git.postgresql.org/pg/commitdiff/f536d4194293f900577c728bfa4e62075a4c7da0

- Set client encoding explicitly in plpython_unicode test.  This will
  (hopefully) eliminate the need for the plpython_unicode_0.out
  expected file.
  http://git.postgresql.org/pg/commitdiff/5809a6458450a59f8c4a7a25beaefc81cff563d1

Andrew Dunstan pushed:

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

Bruce Momjian pushed:

- Fix pg_upgrade crash.  Fix pg_upgrade crash in 9.0 caused by a new
  cluster database that doesn't exist in the old cluster;  instead
  throw an error.  This was reported to me by EnterpriseDB testing
  staff.  This bug does not exist in git head.
  http://git.postgresql.org/pg/commitdiff/052e621cc2c25166037fe9fadd3082ae70045fec

Robert Haas pushed:

- Avoid incorrectly granting replication to roles created with
  NOSUPERUSER.  Andres Freund
  http://git.postgresql.org/pg/commitdiff/0a49c95c734d58385e9c5e335088da1cd54e2344

- Fix toast table creation.  Instead of using slightly-too-clever
  heuristics to decide when we must create a TOAST table, just check
  whether one is needed every time the table is altered.  Checking
  whether a toast table is needed is cheap enough that we needn't
  worry about doing it on every ALTER TABLE command, and the previous
  coding is apparently prone to accidental breakage: commit
  04e17bae50a73af524731fa11210d5c3f7d8e1f9 broken ALTER TABLE ..  SET
  STORAGE, which moved some actions from AT_PASS_COL_ATTRS to
  AT_PASS_MISC, and commit 6c5723998594dffa5d47c3cf8c96ccf89c033aae
  broke ALTER TABLE .. ADD COLUMN by changing the way that adding
  columns recurses into child tables.  Noah Misch, with one comment
  change by me
  http://git.postgresql.org/pg/commitdiff/39a68e5c6ca7b41b889e773ca58535324af69630

- Remove obsolete comment.  The lock level for adding a parent table
  is now ShareUpdateExclusiveLock; see commit
  fbcf4b92aa64d4577bcf25925b055316b978744a.  This comment didn't get
  updated to match, but it doesn't seem important to mention this
  detail here, so rather than updating it now, just take it out.
  http://git.postgresql.org/pg/commitdiff/0c80b57d0775eb2015b7672fd8b20c63846d2a0d

- Advise Debian/Ubuntu users to use openjade1.3.  The latest openjade
  packages for Ubuntu 10.10 seg fault when building our documentation.
  Josh Berkus
  http://git.postgresql.org/pg/commitdiff/07e58cbe19d78027eec8fd3e414220903b125c97

Marc Fournier pushed:

- Tag 9.0.4, 8.4.8, 8.3.15, and 8.2.21.

Magnus Hagander pushed:

- Note that Bison on GnuWin32 has trouble with paths with spaces.
  Peter Eisentraut
  http://git.postgresql.org/pg/commitdiff/3affae58b795fb238bed17c35cadcefabdc1f0ee

- Specify which versions of the Platform SDK are supported.  Anything
  including Visual Studio 2010 compilers is not yet supported for
  building on Windows.
  http://git.postgresql.org/pg/commitdiff/9e1526eddd34bf78327a9e7be3db47a60b59b688

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Roman Zotov sent in another revision of the patch to turn PREFERRED
for type from a boolean to an int2, which allows for an expression of
precedence.

Robert Haas sent in a patch to fix a bug in recoveryStopsHere().

Greg Smith sent in a patch to remove some of the magic from running
pgindent.

Andrew Dunstan sent in two revisions of a patch to remove pgindent
magic in different ways from Greg Smith's.