== PostgreSQL Weekly News - September 13 2009 ==

== PostgreSQL Weekly News - September 13 2009 ==

Security updates 8.4.1, 8.3.8, 8.2.14, 8.1.18, 8.0.22 and 7.4.26
released.  Upgrade ASAP!

== PostgreSQL Product News ==

Bucardo 4.0.1, a replication system with dual-master capability,
released.
http://bucardo.org/wiki/Bucardo

== PostgreSQL Jobs for September ==

http://archives.postgresql.org/pgsql-jobs/2009-09/threads.php

== PostgreSQL Local ==

There will be a conference in Seattle, Washington, USA October 16-18,
2009.
http://www.postgresqlconference.org/2009/west

PGCon Brazil will be take place October 23-24 2009 at Unicamp in
Campinas, Sao Paulo state.  Registration open!
http://pgcon.postgresql.org.br/2009/

PGDay.EU 2009 will be at Telecom ParisTech in Paris, France on
November 6-7, 2009.
http://www.pgday.eu/

OpenSQL Camp in Portland is looking for sponsors.  Make your travel plans now! :)
http://www.chesnok.com/daily/2009/07/29/opensql-camp-comes-to-portland-november-14-15-2009/

JPUG 10th Anniversary Conference is November 20-21, 2009 in Tokyo, Japan.
http://archives.postgresql.org/pgsql-announce/2009-05/msg00018.php

FOSDEM 2010 will be in Brussels, Belgium on February 6-7, 2010.
http://www.fosdem.org/

Chemnitzer Linuxtage will be in Chemnitz, Germany on March 13-14, 2010.
http://chemnitzer.linuxtage.de/

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

== Applied Patches ==

Tom Lane committed:

- Disallow RESET ROLE and RESET SESSION AUTHORIZATION inside
  security-definer.  functions.  This extends the previous patch that
  forbade SETting these variables inside security-definer functions.
  RESET is equally a security hole, since it would allow regaining
  privileges of the caller; furthermore it can trigger Assert failures
  and perhaps other internal errors, since the code is not expecting
  these variables to change in such contexts.  The previous patch did
  not cover this case because assign hooks don't really have enough
  information, so move the responsibility for preventing this into
  guc.c.  Problem discovered by Heikki Linnakangas.  Security: no CVE
  assigned yet, extends CVE-2007-6600

- Make LOAD of an already-loaded library into a no-op, instead of
  attempting to unload and re-load the library.  The difficulty with
  unloading a library is that we haven't defined safe protocols for
  doing so.  In particular, there's no safe mechanism for getting out
  of a "hook" function pointer unless libraries are unloaded in
  reverse order of loading.  And there's no mechanism at all for
  undefining a custom GUC variable, so GUC would be left with a
  pointer to an old value that might or might not still be valid, and
  very possibly wouldn't be in the same place anymore.  While the
  unload and reload behavior had some usefulness in easing development
  of new loadable libraries, it's of no use whatever to normal users,
  so just disabling it isn't giving up that much.  Someday we might
  care to expend the effort to develop safe unload protocols; but even
  if we did, there'd be little certainty that every third-party
  loadable module was following them, so some security restrictions
  would still be needed.  Back-patch to 8.2; before that, LOAD was
  superuser-only anyway.  Security: unprivileged users could crash
  backend.  CVE not assigned yet

- Remove outside-the-scanner references to "yyleng".  It seems the
  flex developers have decided to change yyleng from int to size_t.
  This has already happened in the latest release of OS X, and will
  start happening elsewhere once the next release of flex appears.
  Rather than trying to divine how it's declared in any particular
  build, let's just remove the one existing not-very-necessary
  external usage.  Back-patch to all supported branches; not so much
  because users in the field are likely to care about building old
  branches with cutting-edge flex, as to keep OSX-based buildfarm
  members from having problems with old branches.

- Replace use of the long-deprecated Bonjour API
  DNSServiceRegistrationCreate with the not-so-deprecated
  DNSServiceRegister.  This patch shouldn't change any user-visible
  behavior, it just gets rid of a deprecation warning in
  --with-bonjour builds.  The new code will fail on OS X releases
  before 10.3, but it seems unlikely that anyone will want to run
  Postgres 8.5 on 10.2.

- Add a boolean GUC parameter "bonjour" to control whether a
  Bonjour-enabled build actually attempts to advertise itself via
  Bonjour.  Formerly it always did so, which meant that packagers had
  to decide for their users whether this behavior was wanted or not.
  The default is "off" to be on the safe side, though this represents
  a change in the default behavior of a Bonjour-enabled build.  Per
  discussion.

- Remove any -arch switches given in ExtUtils::Embed's ldopts from our
  perl_embed_ldflags setting.  On OS X it seems that ExtUtils::Embed
  is trying to force a universal binary to be built, but you need to
  specify that a lot further upstream if you want Postgres built that
  way; the only result of including -arch in perl_embed_ldflags is
  some warnings at the plperl.so link step.  Per my complaint and Jan
  Otto's suggestion.

- Fix bug with WITH RECURSIVE immediately inside WITH RECURSIVE.  99%
  of the code was already okay with this, but the hack that obtained
  the output column types of a recursive union in advance of doing
  real parse analysis of the recursive union forgot to handle the case
  where there was an inner WITH clause available to the non-recursive
  term.  Best fix seems to be to refactor so that we don't need the
  "throwaway" parse analysis step at all.  Instead, teach the
  transformSetOperationStmt code to set up the CTE's output column
  information after it's processed the non-recursive term normally.
  Per report from David Fetter.

- Increase the maximum value of extra_float_digits to 3, and have
  pg_dump use that value when the backend is new enough to allow it.
  This responds to bug report from Keh-Cheng Chu pointing out that
  although 2 extra digits should be sufficient to dump and restore
  float8 exactly, it is possible to need 3 extra digits for float4
  values.

- Fix assertion failure when a SELECT DISTINCT ON expression is
  volatile.  In this case we generate two PathKey references to the
  expression (one for DISTINCT and one for ORDER BY) and they really
  need to refer to the same EquivalenceClass.  However
  get_eclass_for_sort_expr was being overly paranoid and creating two
  different Emmanuel Cecchet's.  Correct behavior is to use the
  SortGroupRef index to decide whether two references to volatile
  expressions that are equal() (ie textually equivalent) should be
  considered the same.  Backpatch to 8.4.  Possibly this should be
  changed in 8.3 as well, but I'll refrain in the absence of evidence
  of a visible failure in that branch.  Per bug #5049.

- In pgsql/src/backend/commands/tablespace.c, install a
  hopefully-temporary workaround for Snow Leopard readdir() bug.  If
  Apple doesn't fix that reasonably soon, we'll have to consider
  back-patching a workaround; but for now, just hack it in HEAD so
  that we can get buildfarm reports on HEAD from OS X machines.  Per
  Jan Otto.

- In pgsql/doc/src/sgml/ref/grant.sgml, improve GRANT reference page's
  description of object ownership privileges by mentioning the
  possibility of granting membership in the owning role.

- Rewrite the planner's handling of materialized plan types so that
  there is an explicit model of rescan costs being different from
  first-time costs.  The costing of Material nodes in particular now
  has some visible relationship to the actual runtime behavior, where
  before it was essentially fantasy.  This also fixes up a couple of
  places where different materialized plan types were treated
  differently for no very good reason (probably just oversights).  A
  couple of the regression tests are affected, because the planner now
  chooses to put the other relation on the inside of a
  nestloop-with-materialize.  So far as I can see both changes are
  sane, and the planner is now more consistently following the
  expectation that it should prefer to materialize the smaller of two
  relations.  Per a recent discussion with Robert Haas.

- Write psql's ~/.psql_history file using history_truncate_file() and
  append_history(), if libreadline is new enough to have those
  functions (they seem to be present at least since 4.2; but libedit
  may not have them).  This gives significantly saner behavior when
  two or more sessions overlap in their use of the history file;
  although having two sessions exit at just the same time is still
  perilous to your history.  The behavior of \s remains unchanged, ie,
  overwrite whatever was there.  Per bug #5052 from Marek W?jtowicz.

Magnus Hagander committed:

- Change our WIN32 API version to be 5.01 (Windows XP), to bring in
  the proper IPV6 headers in newer SDKs.

Peter Eisentraut committed:

- Fix/improve bytea and boolean support in PL/Python.  Before,
  PL/Python converted data between SQL and Python by going through a C
  string representation.  This broke for bytea in two ways: 1. On
  input (function parameters), you would get a Python string that
  contains bytea's particular external representation with backslashes
  etc., instead of a sequence of bytes, which is what you would expect
  in a Python environment.  This problem is exacerbated by the new
  bytea output format.  2. On output (function return value), null
  bytes in the Python string would cause truncation before the data
  gets stored into a bytea datum.  This is now fixed by converting
  directly between the PostgreSQL datum and the Python representation.
  The required generalized infrastructure also allows for other
  improvements in passing: 1. When returning a boolean value, the SQL
  datum is now true if and only if Python considers the value that was
  passed out of the PL/Python function to be true.  Previously, this
  determination was left to the boolean data type input function.  So,
  now returning 'foo' results in true, because Python considers it
  true, rather than false because PostgreSQL considers it false.  2.
  On input, we can convert the integer and float types directly to
  their Python equivalents without having to go through an
  intermediate string representation.  Original patch by Caleb Welton,
  with updates by myself.

- In pgsql/doc/src/sgml/dml.sgml, remove claim that this chapter
  discusses rules and triggers.  Per Bruno Guimaraes Carneiro.

- Remove PL/Python TODO file; it has been added to the main Todo list
  in the wiki.

- Add Unicode support in PL/Python.  PL/Python now accepts Unicode
  objects where it previously only accepted string objects (for
  example, as return value).  Unicode objects are converted to the
  PostgreSQL server encoding as necessary.  This change is also
  necessary for future Python 3 support, which treats all strings as
  Unicode objects.  Since this removes the error conditions that the
  plpython_unicode test file tested for, the alternative result files
  are no longer necessary.

- In pgsql/doc/src/sgml/config.sgml, document that
  autovacuum_max_workers can only be set at server start.  Per Joshua
  Tolley.

- Fix Unicode support in PL/Python.  Check calls of
  PyUnicode_AsEncodedString() for NULL return, probably because the
  encoding name is not known.  Add special treatment for SQL_ASCII,
  which Python definitely does not know.  Since using SQL_ASCII
  produces errors in the regression tests when non-ASCII characters
  are involved, we have to put back various regression test result
  variants.

Heikki Linnakangas committed:

- In pgsql/src/backend/access/transam/xlog.c, on Windows, when a file
  is deleted and another process still has an open file handle on it,
  the file goes into "pending deletion" state where it still shows up
  in directory listing, but isn't accessible otherwise.  That confuses
  RemoveOldXLogFiles(), making it think that the file hasn't been
  archived yet, while it actually was, and it was deleted along with
  the .done file.  Fix that by renaming the file with ".deleted"
  extension before deleting it.  Also check the return value of
  rename() and unlink(), so that if the removal fails for any reason
  (e.g another process is holding the file locked), we don't delete
  the .done file until the WAL file is really gone.  Backpatch to 8.2,
  which is the oldest version supported on Windows.

- Don't error out if recycling or removing an old WAL segment fails at
  the end of checkpoint. Although the checkpoint has been written to
  WAL at that point already, so that all data is safe, and we'll retry
  removing the WAL segment at the next checkpoint, if such a failure
  persists we won't be able to remove any other old WAL segments
  either and will eventually run out of disk space. It's better to
  treat the failure as non-fatal, and move on to clean any other WAL
  segment and continue with any other end-of-checkpoint cleanup.  We
  don't normally expect any such failures, but on Windows it can
  happen with some anti-virus or backup software that lock files
  without FILE_SHARE_DELETE flag.  Also, the loop in pgrename() to
  retry when the file is locked was broken. If a file is locked on
  Windows, you get ERROR_SHARE_VIOLATION, not ERROR_ACCESS_DENIED, at
  least on modern versions. Fix that, although I left the check for
  ERROR_ACCESS_DENIED in there as well (presumably it was correct in
  some environment), and added ERROR_LOCK_VIOLATION to be consistent
  with similar checks in pgwin32_open(). Reduce the timeout on the
  loop from 30s to 10s, on the grounds that since it's been broken,
  we've effectively had a timeout of 0s and no-one has complained, so
  a smaller timeout is actually closer to the old behavior. A longer
  timeout would mean that if recycling a WAL file fails because it's
  locked for some reason, InstallXLogFileSegment() will hold
  ControlFileLock for longer, potentially blocking other backends, so
  a long timeout isn't totally harmless.  While we're at it, set errno
  correctly in pgrename().  Backpatch to 8.2, which is the oldest
  version supported on Windows.  The xlog.c changes would make sense
  on other platforms and thus on older versions as well, but since
  there's no such locking issues on other platforms, it's not worth
  it.

Tatsuo Ishii committed:

- In pgsql/contrib/pgbench/pgbench.c, pgbench has #defines for number
  of branches, tellers, and accounts.  There are used to populate the
  tables with -i, but when running actual benchmark it has values
  separately hard-coded in the query metacommands.  This patch makes
  the metacommands obtain their values from the relevant #defines.
  Patch provided by Jeff Janes.

Alvaro Herrera committed:

- In pgsql/doc/src/sgml/config.sgml, add note that the logging
  collector can block backends in high load situations.

Joe Conway committed:

- In pgsql/contrib/dblink/dblink.c, plug dblink resource leak.  dblink
  generates orphaned connections when called with a connection string,
  fail_on_error = true, and an ERROR occurs. Discovery and patch by
  Tatsuhito Kasahara. Introduced in 8.4.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

ITAGAKI Takahiro sent in two revisions of a patch to implement CREATE
TABLE LIKE...INCLUDING (COMMENTS|STORAGE).

ITAGAKI Takahiro sent in two more revisions of the per-column trigger
patch.

Zoltan Boszormenyi sent in a small patch to fix a typo in an earlier
ECPG patch he sent.

Marko Kreen sent in another revision of the patch to add Unicode
escapes for strings in UTF8 databases.

Emmanuel Cecchet sent in a patch to log errors and do
auto-partitioning.

Heikki Linnakangas sent in another revision of the patch to fix
WAL issues in non-renamable files on Windows.

Josh Tolley sent in a doc patch to mention that autovacuum_max_workers
can only be set on server start.

Robert Haas sent in a proof-of-concept patch to add generic COPY
options.

Jeff Janes sent in a patch to improve XLogInsert.

Andrew Dunstan sent in a patch to allow for ragged CSV input in COPY.

Peter Eisentraut sent in a patch to make translating psql help easier.

ITAGAKI Takahiro sent in a patch to add a GUC parameter
syslog_line_prefix for syslog and eventlog.

Jeff Janes sent in a patch to improve tools/fsync.