== PostgreSQL Weekly News - October 16 2011 ==

== PostgreSQL Weekly News - October 16 2011 ==

== PostgreSQL Product News ==

/Devart's PgDAC 3.00 and dbExpress Driver for PostgreSQL 2.0 released.
http://www.devart.com/dbx/postgresql/

MyJSQLView 3.31, a GUI tool that can be used with PostgreSQL, released.
http://dandymadeproductions.com/projects/MyJSQLView/

pgpoolAdmin 3.1.0, an administration tool for pgpool-II, released.
http://pgfoundry.org/frs/download.php/3149/pgpoolAdmin-3.1.0.tar.gz

Postgres-XC 0.9.6, a multi-master system based on PostgreSQL, released.
http://postgres-xc.sourceforge.net/

== PostgreSQL Jobs for October ==

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

== PostgreSQL Local ==

PostgreSQL Conference Europe 2011 will be held on October 18-21 in
Amsterdam.
http://2011.pgconf.eu/

PG-Day Denver 2011 will be held on Friday, October 21st, 2011 at
the Auraria Campus near downtown Denver, Colorado.
http://pgday.consistentstate.com/

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

PGConf.DE 2011, the German-speaking PostgreSQL Conference, will
take place on November 11th in the Rheinisches Industriemuseum in
Oberhausen, Germany.  The schedule is now available, and registration
is open.
http://2011.pgconf.de/

The fifth edition of the Italian PostgreSQL Day (PGDay.IT 2011) will
be held on November 25, 2011 in Prato, Italy.
http://2011.pgday.it/

The Call for Papers for is open for FLOSS UK, which will be held in
Edinburgh from the 20th to the 22nd March 2012.  The deadline for
proposals is the 18th November 2011 and selected speakers will be
notified by the 25th November 2011.  Proposals should be submitted to
postgresql2012 AT flossuk DOT org.  More information at:
http://www.flossuk.org/Events/Spring2012

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

Bruce Momjian pushed:

- In pg_upgrade, add -o/-O options to pass parameters to the servers,
  and document its use for config-only directory installs.
  http://git.postgresql.org/pg/commitdiff/0dc3f57ba0aa9a99308d3d097eeb2cbcbd5fb52c

- Mark GUC external_pid_file's default as '' in postgresql.conf,
  rather than '(none)'.
  http://git.postgresql.org/pg/commitdiff/e26d5fcd94d11c3f367ab5fbe246c0288cc9eb44

- Document that not backing up postmaster.pid and postmaster.opts
  might help prevent pg_ctl from getting confused.  Backpatch to 9.1.
  http://git.postgresql.org/pg/commitdiff/47cacfc0f171c7794a8a953e4676db60e1fb261c

- Improve entab's Makefile install entry.  Andrew Dunstan
  http://git.postgresql.org/pg/commitdiff/e991930e8a2394169d95e9a4c175a1fdf812ef04

- Add Up/Home link to the top of the HTML doc output.  Backpatch to
  9.0.X and 9.1.X.
  http://git.postgresql.org/pg/commitdiff/b8691d838be0d9a85fa9ede6a931c4f194895c36

- Modify up/home macro to match standard parameter list;  fixes doc
  build.
  http://git.postgresql.org/pg/commitdiff/e0d273500a84ab94c69cbfa10ea0537604fbdda3

- Modify pgindent to use a renamed pg_bsd_indent binary.  New features
  include the ability to supply a typedef file, rather than list them
  on the command line.  Also improve the README.
  http://git.postgresql.org/pg/commitdiff/6e22ba03a9fe6853e812268ef404e45aac4866ab

- Modify RelationGetBufferForTuple() to use a typedef, rather than a
  struct, to help pgindent.
  http://git.postgresql.org/pg/commitdiff/484af9b376f3dca3805932d5c40c45ecf6fca0d4

- Clarify wording of foreign key documentation to mention null entries
  as not matching the primary key.  Report from Grzegorz Szpetkowski.
  http://git.postgresql.org/pg/commitdiff/f2b36d8e10b628ffe0ba73c90826c3719ea20176

- Document how to accent Alvaro Herrera in the release notes.
  http://git.postgresql.org/pg/commitdiff/e0b268fb82fadb53171e9e7401ae8090cf36a264

- Remove tab in sgml file.
  http://git.postgresql.org/pg/commitdiff/4c32f817667ea5461832897a6374be5cfc9a1bf5

- Document who can drop a table (owner and user with permissions).
  http://git.postgresql.org/pg/commitdiff/cf72528e87dd59ba6833c912e3712413dde6669d

- Have pg_ctl return an exit status of 3 if the server is not running,
  to match the Linux Standard Base Core Specification 3.1.  Aaron W.
  Swenson
  http://git.postgresql.org/pg/commitdiff/12ff9fa7715611d7df6a78447fce6cc5096cf335

- Update documentation about ts_rank().
  http://git.postgresql.org/pg/commitdiff/fb4340c5ea2b11319f220e8bbbf9b3f6bc9d827b

- Remove all "traces" of trace_userlocks, because userlocks were
  removed in PG 8.2.
  http://git.postgresql.org/pg/commitdiff/0180bd6180511875db046bf8ddcaa633a2952dfd

- Document actual string that has to be returned by the client for MD5
  authentication.  Report and pseudo code by Cyan Ogilvie
  http://git.postgresql.org/pg/commitdiff/ad30d366424aee9736f7a71be87e6941d7dd12df

- Allow a major PG version psql .psqlrc file to be used if a minor
  matching version file does not exist.  This avoids needing to rename
  .psqlrc files after minor version upgrades.
  http://git.postgresql.org/pg/commitdiff/2795592e52c88e510ae4bcbc17b305d6adc0b2b6

- Improve doc wording of drop table permission.
  http://git.postgresql.org/pg/commitdiff/2deba6d4056f0cc4fa0019471194d6b5942c38ab

- Document that is the psql version number, not the server version
  number, that controls .psqlrc.
  http://git.postgresql.org/pg/commitdiff/b4aec388d88cb3a27768e5a1cf3a1f6b11a9ede0

Robert Haas pushed:

- Attempt to reduce locale dependencies in regression tests.  This
  appears to be another case where the relative sort order of letters
  vs. numbers can throw things off.  Pavel Stehule
  http://git.postgresql.org/pg/commitdiff/3e9a2672d25aed15ae6b4a09decbd8927d069868

- Some minor wordsmithing for the cascading replication documentation.
  Per report from Thom Brown.
  http://git.postgresql.org/pg/commitdiff/0ff7ea5d3c6d126ba9fd05ea1845d49e8c0b4fd9

- Document DELETE/UPDATE command tag behavior when triggers are
  involved.  Marti Raudsepp
  http://git.postgresql.org/pg/commitdiff/61dd737c29661763448d61786ec1aa260db0412a

- Add doc index entry for pg_resetxlog.  Fujii Masao
  http://git.postgresql.org/pg/commitdiff/48a62278ed34e17f65b80d5cf1a84c4c1ab5d8b9

- Fix typo in docs for libpq keepalives_count option.  Shigehiro Honda
  http://git.postgresql.org/pg/commitdiff/322019ed2e08e7cad4ef126e6229a8188643170f

- Improve documentation of how to fiddle with SCSI drives on FreeBSD.
  Per suggestions from Achilleas Mantzios and Greg Smith.
  http://git.postgresql.org/pg/commitdiff/e8bb5f7245afe5037429d6b20f5f136bf929cc78

- Make the reference to "CREATE USER" in the CREATE ROLE page a link.
  This might help to avoid confusion between the CREATE USER command,
  and the deprecated CREATEUSER option to CREATE ROLE, as per a recent
  complaint from Ron Adams.  At any rate, having a cross-link here
  seems like a good idea; two commands that are so similar should
  reference each other.
  http://git.postgresql.org/pg/commitdiff/5b9102cef2edcc621daef423d42969b517359136

- Repair breakage in VirtualXactLock.  I broke this in commit
  84e37126770dd6de903dad88ce150a49b63b5ef9.  Report and fix by Fujii
  Masao.
  http://git.postgresql.org/pg/commitdiff/e76bcaba9cb330b32e9a80dfe9cd510d802121c4

- Replace hardcoded switch in object_exists() with a lookup table.
  There's no particular advantage to this change on its face; indeed,
  it's possible that this might be slightly slower than the old way.
  But it makes this information more easily accessible to other
  functions, and therefore paves the way for future code
  consolidation.  Performance isn't critical here, so there's no need
  to be smart about how we do the search.  This is a heavily cut-down
  version of a patch from KaiGai Kohei, with several fixes by me.
  Additional review from Dimitri Fontaine.
  http://git.postgresql.org/pg/commitdiff/fa351d5a0db0672b6f586315720302e493116f27

- Avoid potential relcache leak in objectaddress.c.  Nobody using the
  missing_ok flag yet, but let's speculate that this will be a better
  interface for future callers.  KaiGai Kohei, with some adjustments
  by me.
  http://git.postgresql.org/pg/commitdiff/393e828e31a27845def4c3ff20cdfbee5cec1da9

- Dump all roles first, then all config settings on roles.  This way,
  if a role's config setting uses the name of another role, the
  validity of the dump isn't dependent on the order in which those two
  roles are dumped.  Code by Phil Sorber, comment by me.
  http://git.postgresql.org/pg/commitdiff/dea95c7a7beb5ef66ce89269dd0e84d0c26e5523

Tom Lane pushed:

- Rearrange the implementation of index-only scans.  This commit
  changes index-only scans so that data is read directly from the
  index tuple without first generating a faux heap tuple.  The only
  immediate benefit is that indexes on system columns (such as OID)
  can be used in index-only scans, but this is necessary
  infrastructure if we are ever to support index-only scans on
  expression indexes.  The executor is now ready for that, though the
  planner still needs substantial work to recognize the possibility.
  To do this, Vars in index-only plan nodes have to refer to index
  columns not heap columns.  I introduced a new special varno,
  INDEX_VAR, to mark such Vars to avoid confusion.  (In passing, this
  commit renames the two existing special varnos to OUTER_VAR and
  INNER_VAR.)  This allows ruleutils.c to handle them with logic
  similar to what we use for subplan reference Vars.  Since index-only
  scans are now fundamentally different from regular indexscans so far
  as their expression subtrees are concerned, I also chose to change
  them to have their own plan node type (and hence, their own executor
  source file).
  http://git.postgresql.org/pg/commitdiff/a0185461dd94c8d31d8d55a7f2839b0d2f172ab9

- Consider index-only scans even when there is no matching qual or
  ORDER BY.  By popular demand.
  http://git.postgresql.org/pg/commitdiff/600d3206d1b3f8b540397b79905486a536ac7f78

- Generate index-only scan tuple descriptor from the plan node's
  indextlist.  Dept. of second thoughts: as long as we've got that
  tlist hanging around anyway, we can apply ExecTypeFromTL to it to
  get a suitable descriptor for the ScanTupleSlot.  This is a nicer
  solution than the previous one because it eliminates some hard-wired
  knowledge about btree name_ops, and because it avoids the somewhat
  shaky assumption that we needn't set up the scan tuple descriptor in
  EXPLAIN_ONLY mode.  It doesn't change what actually happens at
  run-time though, and I'm still a bit nervous about that.
  http://git.postgresql.org/pg/commitdiff/cb6771fb32cbdb11c8d84b7d62ee940bdba38d52

- Add comment on why pulling data from a "name" index column can't
  crash.  It's been bothering me for several days that pretending that
  the cstring data stored in a btree name_ops column is really a
  "name" Datum could lead to reading past the end of memory.  However,
  given the current memory layout used for index-only scans in the
  btree code, a crash is in fact not possible.  Document that so we
  don't break it.  I have not thought of any other solutions that
  aren't fairly ugly too, and most of them lose the functionality of
  index-only scans on name columns altogether, so this seems like the
  way to go.
  http://git.postgresql.org/pg/commitdiff/8c8ba6d11b06e5a8b9fe5653a1cd17c437af5f7b

- Improve documentation of psql's \q command.  The documentation
  neglected to explain its behavior in a script file (it only ends
  execution of the script, not psql as a whole), and failed to mention
  the long form \quit either.
  http://git.postgresql.org/pg/commitdiff/80c6409c2bb9417c059603f0b5b88209517c7593

- Throw a useful error message if an extension script file is fed to
  psql.  We have seen one too many reports of people trying to use 9.1
  extension files in the old-fashioned way of sourcing them in psql.
  Not only does that usually not work (due to failure to substitute
  for MODULE_PATHNAME and/or @extschema@), but if it did work they'd
  get a collection of loose objects not an extension.  To prevent
  this, insert an \echo ...  \quit line that prints a suitable error
  message into each extension script file, and teach
  commands/extension.c to ignore lines starting with \echo.  That
  should not only prevent any adverse consequences of loading a script
  file the wrong way, but make it crystal clear to users that they
  need to do it differently now.  Tom Lane, following an idea of
  Andrew Dunstan's.  Back-patch into 9.1 ... there is not going to be
  much value in this if we wait till 9.2.
  http://git.postgresql.org/pg/commitdiff/458857cc9d7d00711b272a0dabbcb591b506d6b8

- Don't mark auto-generated types as extension members.  Relation
  rowtypes and automatically-generated array types do not need to have
  their own extension membership dependency entries.  If we create
  such then it becomes more difficult to remove items from an
  extension, and it's also harder for an extension upgrade script to
  make sure it duplicates the dependencies created by the extension's
  regular installation script.  I changed the code in such a way that
  this happened in commit 988cccc620dd8c16d77f88ede167b22056176324, I
  think because of worries about the shell-type-replacement case; but
  that cure was worse than the disease.  It would only matter if one
  extension created a shell type that was replaced with an
  auto-generated type in another extension, which seems pretty
  far-fetched.  Better to make this work unsurprisingly in normal
  cases.  Report and patch by Robert Haas, comment adjustments by me.
  http://git.postgresql.org/pg/commitdiff/7b96519fe24b6a675b2cd39ed3b89302b8f1fedb

- Fix typo in dummy_seclabel documentation.  dummy_label ->
  dummy_seclabel.  Thom Brown
  http://git.postgresql.org/pg/commitdiff/de1bf53a254a2a832ddbc46395e9af2b918d9302

- Fix up Perl-to-Postgres datatype conversions in pl/perl.  This patch
  restores the pre-9.1 behavior that pl/perl functions returning VOID
  ignore the result value of their last Perl statement.  9.1.0
  unintentionally threw an error if the last statement returned a
  reference, as reported by Amit Khandekar.  Also, make sure it works
  to return a string value for a composite type, so long as the string
  meets the type's input format.  We already allowed the equivalent
  behavior for arrays, so it seems inconsistent to not allow it for
  composites.  In addition, ensure we throw errors for attempts to
  return arrays or hashes when the function's declared result type is
  not an array or composite type, respectively.  Pre-9.1 versions
  rather uselessly returned strings like ARRAY(0x221a9a0) or
  HASH(0x221aa90), while 9.1.0 threw an error for the hash case and
  returned a garbage value for the array case.  Also, clean up
  assorted grotty coding in Perl array conversion, including use of a
  session-lifespan memory context to accumulate the array value
  (resulting in session-lifespan memory leak on error), failure to
  apply the declared typmod if any, and failure to detect some cases
  of non-rectangular multi-dimensional arrays.  Alex Hunsaker and Tom
  Lane
  http://git.postgresql.org/pg/commitdiff/23610daf8af0f5b468b5c0d4774295cc02ad30a9

- Measure the number of all-visible pages for use in index-only scan
  costing.  Add a column pg_class.relallvisible to remember the number
  of pages that were all-visible according to the visibility map as of
  the last VACUUM (or ANALYZE, or some other operations that update
  pg_class.relpages).  Use relallvisible/relpages, instead of an
  arbitrary constant, to estimate how many heap page fetches can be
  avoided during an index-only scan.  This is pretty primitive and
  will no doubt see refinements once we've acquired more field
  experience with the index-only scan mechanism, but it's way better
  than using a constant.  Note: I had to adjust an underspecified
  query in the window.sql regression test, because it was changing
  answers when the plan changed to use an index-only scan.  Some of
  the adjacent tests perhaps should be adjusted as well, but I didn't
  do that here.
  http://git.postgresql.org/pg/commitdiff/e6858e665731c0f56d3ecc9fbb245c32d24f8ef7

- Measure the number of all-visible pages for use in index-only scan
  costing.  Add a column pg_class.relallvisible to remember the number
  of pages that were all-visible according to the visibility map as of
  the last VACUUM (or ANALYZE, or some other operations that update
  pg_class.relpages).  Use relallvisible/relpages, instead of an
  arbitrary constant, to estimate how many heap page fetches can be
  avoided during an index-only scan.  This is pretty primitive and
  will no doubt see refinements once we've acquired more field
  experience with the index-only scan mechanism, but it's way better
  than using a constant.  Note: I had to adjust an underspecified
  query in the window.sql regression test, because it was changing
  answers when the plan changed to use an index-only scan.  Some of
  the adjacent tests perhaps should be adjusted as well, but I didn't
  do that here.
  http://git.postgresql.org/pg/commitdiff/e6858e665731c0f56d3ecc9fbb245c32d24f8ef7

- Fix bugs in information_schema.referential_constraints view.  This
  view was being insufficiently careful about matching the FK
  constraint to the depended-on primary or unique key constraint.
  That could result in failure to show an FK constraint at all, or
  showing it multiple times, or claiming that it depended on a
  different constraint than the one it really does.  Fix by joining
  via pg_depend to ensure that we find only the correct dependency.
  Back-patch, but don't bump catversion because we can't force initdb
  in back branches.  The next minor-version release notes should
  explain that if you need to fix this in an existing installation,
  you can drop the information_schema schema then re-create it by
  sourcing $SHAREDIR/information_schema.sql in each database (as a
  superuser of course).
  http://git.postgresql.org/pg/commitdiff/d26e1ebaf5f8f59c27327e8fd810fa4b26431a1f

- Marginal improvements to documentation of plpgsql's OPEN cursor
  statement.  Rearrange text to improve clarity, and add an example of
  implicit reference to a plpgsql variable in a bound cursor's query.
  Byproduct of some work I'd done on the "named cursor parameters"
  patch before giving up on it.
  http://git.postgresql.org/pg/commitdiff/0898d71f66ed884af726556ac9ffc8081dddc757

- Teach btree to handle ScalarArrayOpExpr quals natively.  This allows
  "indexedcol op ANY(ARRAY[...])" conditions to be used in plain
  indexscans, and particularly in index-only scans.
  http://git.postgresql.org/pg/commitdiff/9e8da0f75731aaa7605cf4656c21ea09e84d2eb1

- Fix collate.linux.utf8 expected output for recent error message
  change.  Noted by Jeff Davis.
  http://git.postgresql.org/pg/commitdiff/e661c3dfd320487aaa1d6223e732e00c1b5c3cc2

- Avoid assuming that index-only scan data matches the index's
  rowtype.  In general the data returned by an index-only scan should
  have the datatypes originally computed by FormIndexDatum.  If the
  index opclasses use "storage" datatypes different from their input
  datatypes, the scan tuple will not have the same rowtype attributed
  to the index; but we had a hard-wired assumption that that was true
  in nodeIndexonlyscan.c.  We'd already hacked around the issue for
  the one case where the types are different in btree indexes (btree
  name_ops), but this would definitely come back to bite us if we ever
  implement index-only scans in GiST.  To fix, require the index AM to
  explicitly provide the tupdesc for the tuple it is returning.  btree
  can just pass back the index's tupdesc, but GiST will have to work
  harder when and if it supports index-only scans.  I had previously
  proposed fixing this by allowing the index AM to fill the scan tuple
  slot directly; but on reflection that seemed like a module layering
  violation, since TupleTableSlots are creatures of the executor.  At
  least in the btree case, it would also be less efficient, since the
  tuple deconstruction work would occur even for rows later found to
  be invisible to the scan's snapshot.
  http://git.postgresql.org/pg/commitdiff/336c1d7a515b4d6de237679022d70082d7b69d9a

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Fujii Masao sent in another revision of the patch to unite
recovery.conf and postgresql.conf.

Jun Ishiduka sent in four more revisions of the patch to allow taking
a base backup from a hot standby.

Kyotaro HORIGUCHI sent in another revision of the patch to fix the
issue where make_greater_string() does not return a string in some
cases.

KaiGai Kohei sent in another revision of the patch to rework DROP to
use a unified infrastructure.

Heikki Linnakangas and Jeff Davis traded new revisions of the patch to
add range types.

Fujii Masao sent in another revision of a patch to fix some conditions
wich can cause loss of transactions in streaming replication.

Willy-Bas Loos sent in a patch to make it possible to record
automatically the time a table is created.

Florian Pflug sent in a patch to fix an issue in walsender when
calling out to do_pg_stop_backup.

Alexander Korotkov sent in another revision of the patch to collect
frequency statistics for arrays.

Jan Urbanski sent in a patch implementing the usage of SPI cursors in
PL/Python.

Kerem Kat sent in another revision of a patch adding CORRESPONDING set
operations.