== PostgreSQL Weekly News - March 06 2011 ==

== PostgreSQL Weekly News - March 06 2011 ==

The list of talks and speakers for PGCon 2011 has been released.
http://www.pgcon.org/2011/schedule/day_2011-05-19.en.html

Selena Deckelmann is organizing a PL developer summit at PgCon.  Help
make it great!
http://www.chesnok.com/daily/2011/02/28/pl-developer-summit-at-pgcon-may-21/

There is now a Spanish language version of Planet PostgreSQL.
http://planeta.postgresql.org.es/

A webcast in Spanish for PostgreSQL 9.0 will happen March 9th.
http://inicio.helpame.com/inicio/?q=node/3

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 Jobs for March ==

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

== PostgreSQL Local ==

Gabriele Bartolini of ITPUG will present a talk on PostgreSQL 9.0 at Codemotion
in Rome on Saturday, March 5th, 2011 at 4:10PM.  More information:
http://www.codemotion.it/en/

PostgreSQL Conference East 2011: NYC March 22nd - 25th
http://www.postgresqlconference.org

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/

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

Peter Eisentraut pushed:

- PL/Python custom SPI exceptions.  This provides a separate exception
  class for each error code that the backend defines, as well as the
  ability to get the SQLSTATE from the exception object.  Jan
  Urbański, reviewed by Steve Singer
  http://git.postgresql.org/pg/commitdiff/474a42473adf9b18417242f1fc0691a857ec578b

- Fix regression tests after PL/Python custom SPI exceptions patch.
  http://git.postgresql.org/pg/commitdiff/4b853c879d3c298b7ea7a09fcb74314bc54c37e4

- Dump the COLLATABLE attribute in CREATE TYPE This was previously
  omitted by accident.
  http://git.postgresql.org/pg/commitdiff/16143d64513e4dc3c72bad7ae98d3df0b5a23013

- Additional PL/Python regression test expected file.
  plpython_subtransaction test needs a separate expected file
  specifically for Python 2.5.
  http://git.postgresql.org/pg/commitdiff/2f363590c17c0e02186efab5fa0b197b82a5b3ae

- Support for DragonFly BSD.  Mapped to NetBSD, the closest existing
  match.  (Even though DragonFly BSD is derived from FreeBSD, the
  shared library version numbering matches NetBSD, and the rest is
  mostly the same among all BSD variants.) per "Rumko"
  http://git.postgresql.org/pg/commitdiff/6094c242d1ee40a08f3138811425d7540e8269e4

- Add collations to information_schema.usage_privileges.  This is
  faked information like for domains.
  http://git.postgresql.org/pg/commitdiff/091bda0188250c9802cebca066b4ca9e049616e6

- When creating a collation, check that the locales can be loaded.
  This is the same check that would happen later when the collation is
  used, but it's friendlier to check the collation already when it is
  created.
  http://git.postgresql.org/pg/commitdiff/4442e1975d3c4c96a0b573b7abd864b0cbe26f9d

- Don't allow CREATE TABLE AS to create a column with invalid
  collation.  It is possible that an expression ends up with a
  collatable type but without a collation.  CREATE TABLE AS could then
  create a table based on that.  But such a column cannot be dumped
  with valid SQL syntax, so we disallow creating such a column.  per
  test report from Noah Misch.
  http://git.postgresql.org/pg/commitdiff/b9cff97fdf486eca7e563a9696a7391048814d0f

- Update of SQL feature conformance.
  http://git.postgresql.org/pg/commitdiff/9650364b7b2fc5d676f87cca7b5f9ff991cdb7af

Robert Haas pushed:

- Rename pg_stat_replication.apply_location to replay_location.  For
  consistency with pg_last_xlog_replay_location.  Per discussion.
  http://git.postgresql.org/pg/commitdiff/92c30fd2edee6da6e018f41a1d67b4ac2f8fcbdd

- Avoid excessive Hot Standby feedback messages.  Without this patch,
  when wal_receiver_status_interval=0, indicating that no status
  messages should be sent, Hot Standby feedback messages are instead
  sent extremely frequently.  Fujii Masao, with documentation changes
  by me.
  http://git.postgresql.org/pg/commitdiff/59d6a7594288ebc909a24f3fc9e502f097bbe2ff

- Refactor seclabel.c to use the new check_object_ownership function.
  This avoids duplicate (and not-quite-matching) code, and makes the
  logic for SECURITY LABEL match COMMENT and ALTER EXTENSION ADD/DROP.
  http://git.postgresql.org/pg/commitdiff/efa415da8c0fb8c1c0bc50faebac430f8b917571

- Remove emphasis from 9.1alpha3 items.  In preparation for 9.1alpha4
  release notes, where only the 9.1alpha4 features will be emphasized.
  http://git.postgresql.org/pg/commitdiff/a4406da967093a9793db0fbceb6e226e318932b6

- First cut at 9.1alpha4 release notes.
  http://git.postgresql.org/pg/commitdiff/5c44f38d3b63c5a11f58fe558adfe4d3cf4dcc39

- Add missing word.
  http://git.postgresql.org/pg/commitdiff/f31e532492aa950c62c3ded32d07d1e42017e557

- Remove one copy of duplicated alpha4 release note.  Noted by Andy
  Colson.
  http://git.postgresql.org/pg/commitdiff/c0f2b2e256a3e98c2a066cee8623b7ab156b7f9c

Andrew Dunstan pushed:

- Unbreak vpath builds broken by commit 474a42473adf9b18417242f1fc0691a857ec578b.
  http://git.postgresql.org/pg/commitdiff/57e9bda5ec6a032e1e6d51dad5e534a11669c6bf

- Fix plpython breakage detected on certain Fedora machines on
  buildfarm.  Patch from Jan Urbański.
  http://git.postgresql.org/pg/commitdiff/4c966d920fb75a5d0366b887c2ef28e6d87c1eda

- Add a comment explaining the recent fix for plpython breakage in
  commit 4c966d9.  Mostly text supplied by Jan Urbański.
  http://git.postgresql.org/pg/commitdiff/12bf602f3fe963a9d155778b9385186c3a0b5ed3

- Add PL extension files to MSVC Install procedure.
  http://git.postgresql.org/pg/commitdiff/a956b160261579ae2422b539ccf73d75684351fb

Tom Lane pushed:

- Rearrange snapshot handling to make rule expansion more consistent.
  With this patch, portals, SQL functions, and SPI all agree that
  there should be only a CommandCounterIncrement between the queries
  that are generated from a single SQL command by rule expansion.
  Fetching a whole new snapshot now happens only between original
  queries.  This is equivalent to the existing behavior of EXPLAIN
  ANALYZE, and it was judged to be the best choice since it eliminates
  one source of concurrency hazards for rules.  The patch should also
  make things marginally faster by reducing the number of snapshot
  push/pop operations.  The patch removes pg_parse_and_rewrite(),
  which is no longer used anywhere.  There was considerable discussion
  about more aggressive refactoring of the query-processing functions
  exported by postgres.c, but for the moment nothing more has been
  done there.  I also took the opportunity to refactor snapmgr.c's API
  slightly: the former PushUpdatedSnapshot() has been split into two
  functions.  Marko Tiikkaja, reviewed by Steve Singer and Tom Lane.
  http://git.postgresql.org/pg/commitdiff/c0b00760365c74308e9e0719c993eadfbcd090c2

- Update discussion of EXPLAIN to reflect existence of ModifyTable
  nodes.  Back-patch to 9.0, since this was changed then.
  http://git.postgresql.org/pg/commitdiff/09b49a8439fde63826f93f55430868cf2585644b

- Include the target table in EXPLAIN output for ModifyTable nodes.
  Per discussion, this seems important for plans involving writable
  CTEs, since there can now be more than one ModifyTable node in the
  plan.  To retain the same formatting as for target tables of scan
  nodes, we show only one target table, which will be the parent table
  in case of an UPDATE or DELETE on an inheritance tree.  Individual
  child tables can be determined by inspecting the child plan trees if
  needed.
  http://git.postgresql.org/pg/commitdiff/97c4ee94adf15f7a0a39cbb5549159e2aa0679d1

- Correct mistaken claims about EXPLAIN ANALYZE's handling of
  triggers.  Time spent executing AFTER triggers is not included in
  the runtime of the associated ModifyTable node; in my patch of
  yesterday I confused queuing of these triggers with their actual
  execution.  Spotted by Marko Tiikkaja.
  http://git.postgresql.org/pg/commitdiff/7e49330cad885a76441253da74b88e7d2f27c9f0

- Fix erroneous documentation of the syntax of CREATE CONSTRAINT
  TRIGGER.  The grammar requires a specific ordering of the clauses,
  but the documentation showed a different order.  This error was
  introduced in commit b47953f9c69d48a9261bd643e3170017b93f6337, which
  merged the CREATE CONSTRAINT TRIGGER documentation into the CREATE
  TRIGGER page.  There is no code bug AFAICS.
  http://git.postgresql.org/pg/commitdiff/2f6c8453cf3f38a70adbcb59489630cd5be92570

- Add KNNGIST support to contrib/btree_gist.  This extends GiST's
  support for nearest-neighbor searches to many of the standard data
  types.  Teodor Sigaev
  http://git.postgresql.org/pg/commitdiff/8436489c81c23af637696ac69cdaafddcc907ee1

- Add 'collatable' to the Parameters section of CREATE TYPE.
  http://git.postgresql.org/pg/commitdiff/43bdf3583a9a5912e1800ab140b8ca495ae69d85

- Mark operator implementation functions as such in their comments.
  Historically, we've not had separate comments for built-in
  pg_operator entries, but relied on the comments for the underlying
  functions.  The trouble with this approach is that there isn't much
  of anything to suggest to users that they'd be better off using the
  operators instead.  So, move all the relevant comments into
  pg_operator, and give each underlying function a comment that just
  says "implementation of XXX operator".  There are only about half a
  dozen cases where it seems reasonable to use the underlying function
  interchangeably with the operator; in these cases I left the same
  comment in place on the function as on the operator.  While at it,
  establish a policy that every built-in function and operator entry
  should have a comment: there are now queries in the opr_sanity
  regression test that will complain if one doesn't.  This only
  required adding a dozen or two more entries than would have been
  there anyway.  I also spent some time trying to eliminate gratuitous
  inconsistencies in the style of the comments, though it's hopeless
  to suppose that more won't creep in soon enough.  Per my proposal of
  2010-10-15.
  http://git.postgresql.org/pg/commitdiff/94133a935414407920a47d06a6e22734c974c3b8

- Add -lm to SHLIB_LINK for contrib/btree_gist.  Now that btree_gist
  contains a reference to isinf(), this is necessary at least on some
  platforms.  Per buildfarm.
  http://git.postgresql.org/pg/commitdiff/8c4164540b30434845be02f84973663bfeb231ab

- Run a portal's cleanup hook immediately when pushing it to DONE
  state.  This works around the problem noted by Yamamoto Takashi in
  bug #5906, that there were code paths whereby we could reach
  AtCleanup_Portals with a portal's cleanup hook still unexecuted.
  The changes I made a few days ago were intended to prevent that from
  happening, and I think that on balance it's still a good thing to
  avoid, so I don't want to remove the Assert in AtCleanup_Portals.
  Hence do this instead.
  http://git.postgresql.org/pg/commitdiff/6252c4f9e201f619e5eebda12fa867acd4e4200e

- Fix citext's upgrade-from-unpackaged script to set its collation
  correctly.  Although there remains some debate about how CREATE TYPE
  should represent the collation property, this doesn't really affect
  what we need to do in citext's script, so go ahead and fix that.
  http://git.postgresql.org/pg/commitdiff/94be9e3f0ca9e7ced66168397eb586565bced9ca

- Further refine patch for commenting operator implementation
  functions.  Instead of manually maintaining the "implementation of
  XXX operator" comments in pg_proc.h, delete all those entries and
  let initdb create them via a join.  To let initdb figure out which
  name to use when there is a conflict, change the comments for
  deprecated operators to say they are deprecated --- which seems like
  a good thing to do anyway.
  http://git.postgresql.org/pg/commitdiff/908ab80286401bb20a519fa7dc7a837631f20369

- In initialize_SSL, don't fail unnecessarily when home dir is
  unavailable.  Instead, just act as though the certificate file(s)
  are not present.  There is only one case where this need be a hard
  failure condition: when sslmode is verify-ca or verify-full, not
  having a root cert file is an error.  Change the logic so that we
  complain only in that case, and otherwise fall through cleanly.
  This is how it used to behave pre-9.0, but my patch
  4ed4b6c54e5fab24ab2624d80e26f7546edc88ad of 2010-05-26 broke the
  case.  Per report from Christian Kastner.
  http://git.postgresql.org/pg/commitdiff/bd58d9d883111ee84de4af480ebb018fe9b0bf27

- Allow non-superusers to create (some) extensions.  Remove the
  unconditional superuser permissions check in CREATE EXTENSION, and
  instead define a "superuser" extension property, which when false
  (not the default) skips the superuser permissions check.  In this
  case the calling user only needs enough permissions to execute the
  commands in the extension's installation script.  The superuser
  property is also enforced in the same way for ALTER EXTENSION UPDATE
  cases.  In other ALTER EXTENSION cases and DROP EXTENSION, test
  ownership of the extension rather than superuserness.  ALTER
  EXTENSION ADD/DROP needs to insist on ownership of the target object
  as well; to do that without duplicating code, refactor comment.c's
  big switch for permissions checks into a separate function in
  objectaddress.c.  I also removed the superuserness checks in
  pg_available_extensions and related functions; there's no strong
  reason why everybody shouldn't be able to see that info.  Also
  invent an IF NOT EXISTS variant of CREATE EXTENSION, and use that in
  pg_dump, so that dumps won't fail for installed-by-default
  extensions.  We don't have any of those yet, but we will soon.  This
  is all per discussion of wrapping the standard procedural languages
  into extensions.  I'll make those changes in a separate commit; this
  is just putting the core infrastructure in place.
  http://git.postgresql.org/pg/commitdiff/8d3b421f5f7b955e7ac7d156aa74ee6a6fe4e9f6

- Create extension infrastructure for the core procedural languages.
  This mostly just involves creating control, install, and
  update-from-unpackaged scripts for them.  However, I had to adjust
  plperl and plpython to not share the same support functions between
  variants, because we can't put the same function into multiple
  extensions.  catversion bump forced due to new contents of
  pg_pltemplate, and because initdb now installs plpgsql as an
  extension not a bare language.  Add support for regression testing
  these as extensions not bare languages.  Fix a couple of other
  issues that popped up while testing this: my initial hack at pg_dump
  binary-upgrade support didn't work right, and we don't want an extra
  schema permissions test after all.  Documentation changes still to
  come, but I'm committing now to see whether the MSVC build scripts
  need work (likely they do).
  http://git.postgresql.org/pg/commitdiff/63b656b7bf39beb5b6095ab2b5e1e4e9d8c6a965

- Update documentation to reflect that standard PLs are now
  extensions.  Recommend use of CREATE EXTENSION rather than plain
  CREATE LANGUAGE where relevant.  Encourage PL authors to provide
  extension wrappers for their PLs.
  http://git.postgresql.org/pg/commitdiff/c2903fb3d2aeb0a7206945478ad28bc47bd6c6e8

- Convert createlang/droplang to use CREATE/DROP EXTENSION.  In
  createlang this is a one-line change.  In droplang there's a whole
  lot of cruft that can be discarded since the extension mechanism now
  manages removal of the language's support functions.  Also, add
  deprecation notices to these two programs' reference pages, since
  per discussion we may toss them overboard altogether in a release or
  two.
  http://git.postgresql.org/pg/commitdiff/ba0c93a0f46ca70cae47a8817067c1e98482979f

- Make plpythonu language use plpython2 shared library directly.  The
  original scheme for this was to symlink plpython.$DLSUFFIX to
  plpython2.$DLSUFFIX, but that doesn't work on Windows, and only
  accidentally failed to fail because of the way that CREATE LANGUAGE
  created or didn't create new C functions.  My changes of yesterday
  exposed the weakness of that approach.  To fix, get rid of the
  symlink and make pg_pltemplate show what's really going on.
  http://git.postgresql.org/pg/commitdiff/bfd7f8cbb2ddf8c48ac310a80d33c951087c12c4

- Adjust documentation about pg_pltemplate to reflect latest thinking.
  It's more likely that pg_pltemplate will go away in the future than
  that we'll add additional specialized infrastructure for it.
  http://git.postgresql.org/pg/commitdiff/0b5d7345925dd0212d4509195a337f1912781af1

- Fix incorrect access to pg_index.indcollation.  Since this field is
  after a variable-length field, it can't simply be accessed via the C
  struct for pg_index.  Fortunately, the relcache already did the
  dirty work of pulling the information out to where it can be
  accessed easily, so this is a one-line fix.  Andres Freund
  http://git.postgresql.org/pg/commitdiff/149b2673c244b92b59411dd2292d6ddcfb03d5d4

- Fix pg_dump's dump order for collations versus extensions.  Mixing
  them together alphabetically won't be nice.  Per my gripe of
  2011-02-12.

- Add missing "static" marker to internal_ping().  Per testing with a
  compiler that doesn't like that.
  http://git.postgresql.org/pg/commitdiff/775464e8450677602833b1d11b919838e7b2b29a

- Fix a couple more missing "static" markers.
  http://git.postgresql.org/pg/commitdiff/dfe18f18d262df731378cbf38a4136f08ca119a5

- Suppress some "variable might be clobbered by longjmp" warnings.
  Seen with an older gcc version.  I'm not sure these represent any
  real risk factor, but still a bit scary.  Anyway we have lots of
  other volatile-marked variables in this code, so a couple more won't
  hurt.
  http://git.postgresql.org/pg/commitdiff/4172bd88300524efd1728c247fbc1eff3c1ca6ed

Heikki Linnakangas pushed:

- Fix bugs in Serializable Snapshot Isolation.  Change the way UPDATEs
  are handled. Instead of maintaining a chain of tuple-level locks in
  shared memory, copy any existing locks on the old tuple to the new
  tuple at UPDATE. Any existing page-level lock needs to be duplicated
  too, as a lock on the new tuple. That was neglected previously.
  Store xmin on tuple-level predicate locks, to distinguish a lock on
  an old already-recycled tuple from a new tuple at the same physical
  location.  Failure to distinguish them caused loops in the
  tuple-lock chains, as reported by YAMAMOTO Takashi. Although we
  don't use the chain representation of UPDATEs anymore, it seems like
  a good idea to store the xmin to avoid some false positives if no
  other reason.  CheckSingleTargetForConflictsIn now correctly handles
  the case where a lock that's being held is not reflected in the
  local lock table. That happens if another backend acquires a lock on
  our behalf due to an UPDATE or a page split.
  PredicateLockPageCombine now retains locks for the page that is
  being removed, rather than removing them. This prevents a
  potentially dangerous false-positive inconsistency where the local
  lock table believes that a lock is held, but it is actually not.
  Dan Ports and Kevin Grittner
  http://git.postgresql.org/pg/commitdiff/47ad79122bc099c1f0ea8a7ae413fcd8d45e26a6

- Change pg_last_xlog_receive_location() not to move backwards. That
  makes it a lot more useful for determining which standby is most
  up-to-date, for example. There was long discussions on whether
  overwriting existing existing WAL makes sense to begin with, and
  whether we should do some more extensive variable renaming, but this
  change nevertheless seems quite uncontroversial.  Fujii Masao,
  reviewed by Jeff Janes, Robert Haas, Stephen Frost.
  http://git.postgresql.org/pg/commitdiff/6eba5a7c57d1d61f46b6966026bf7bc07f8e087c

- Add tab-completion for table name after JOIN.  Andrey Popp
  http://git.postgresql.org/pg/commitdiff/8e2d8b1497ac64142bb7df05e39a58ebf3e62bf6

- You must hold a lock on the heap page when you call
  CheckForSerializableConflictOut(), because it can set hint bits.
  YAMAMOTO Takashi
  http://git.postgresql.org/pg/commitdiff/ee3838b1d35cffec21ca40dbfc409ec40ddf3e28

Michael Meskes pushed:

- Added new version of ecpg's parser generator script.  This one was
  written by Andy Colson.
  http://git.postgresql.org/pg/commitdiff/32fce70564999a90d48a27c4279a8908e90f3ece

Bruce Momjian pushed:

- Fix parallel gmake for extension directory addition in PL languages.
  http://git.postgresql.org/pg/commitdiff/c15c1f1c15fc19d20c0699aeb312664f5529c87a

- Print clearer failure message when pg_upgrade fails due to a fatal
  error.
  http://git.postgresql.org/pg/commitdiff/a3375becfaebde56f8e6260a3f77c467425fbe51

- Fix pg_upgrade to print the proper database name for file transfer
  failures.
  http://git.postgresql.org/pg/commitdiff/f7b70dfc7649772cf35035754ae7e77f2533da1d

- Restructure pg_upgrade checks because pre-8.4 Postgres did not
  rename toast file names to match the new relfilenode.
  http://git.postgresql.org/pg/commitdiff/9e5bed2df1693a46dfaed862d7462ba2379f8f79

- Update new pg_upgrade comment about pre-8.4 TOAST tables.
  http://git.postgresql.org/pg/commitdiff/8f87dcd863c2fe2940129ea58343bc0e56cb290c

- Improve pg_upgrade relation name check logic for pre-8.4 servers.
  http://git.postgresql.org/pg/commitdiff/a54ba23c087a23222a7c76b33c61cddad5278712

- Tighten pg_upgrade check for pre-8.4 toast table name matching.
  http://git.postgresql.org/pg/commitdiff/05d93c38a791836eeceaf8edb0ea8cb19cdf2760

Simon Riggs pushed:

- Efficient transaction-controlled synchronous replication.  If a
  standby is broadcasting reply messages and we have named one or more
  standbys in synchronous_standby_names then allow users who set
  synchronous_replication to wait for commit, which then provides
  strict data integrity guarantees. Design avoids sending and
  receiving transaction state information so minimises bookkeeping
  overheads. We synchronize with the highest priority standby that is
  connected and ready to synchronize. Other standbys can be defined to
  takeover in case of standby failure.  This version has very strict
  behaviour; more relaxed options may be added at a later date.  Simon
  Riggs and Fujii Masao, with reviews by Yeb Havinga, Jaime Casanova,
  Heikki Linnakangas and Robert Haas, plus the assistance of many
  other design reviewers.
  http://git.postgresql.org/pg/commitdiff/a8a8a3e0965201df88bdfdff08f50e5c06c552b7

- Add new files for syncrep missed in previous commit.
  http://git.postgresql.org/pg/commitdiff/966fb05b588ab33a6c99c6a26308941e1b3a1188

- Catversion increment for pg_stat_replication changes for syncrep.
  http://git.postgresql.org/pg/commitdiff/dcfe3f60c12880c52fd3cb9b5d51ff44c946dd6c

- Dynamic array required within pg_stat_replication.
  http://git.postgresql.org/pg/commitdiff/cae4974e3d4c0cb1237568b55a2cee4482d42f40

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Fujii Masao sent in another revision of the replication server timeout
patch.

Dan Ports sent in a patch to fix a bug in SSI.

Jan Urbanski sent in a patch to fix PL/PythonU tracebacks.

Heikki Linnakangas and Merlin Moncure traded patches to fix an issue
where the PD_ALL_VISIBLE flag was set incorrectly.

Andy Colson sent in a cleaned-up version of check_rules.pl to conform
with Perl 5.12.

Jan Urbanski sent in another revision of the patch to add tracebacks
to PL/PythonU.

Fujii Masao sent in another revision of the patch to add replication
server timeout.

Kevin Grittner sent in a documentation patch to explain SET
TRANSACTION...DEFERRABLE better in the context of SSI.