== PostgreSQL Weekly News - January 11 2009 == - Mailing list pgsql-announce

== PostgreSQL Weekly News - January 11 2009 ==

8.4 snapshot installers are now available.  Take the future 8.4 for a
spin!
http://www.postgresql.org/download/snapshots

Less than 2 weeks left to submit talks to pgCon! www.pgcon.org

== PostgreSQL Product News ==

MyJSQLView 3.01 released.
http://myjsqlview.sourceforge.net/

== PostgreSQL Jobs for January ==

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

== Tip of the Week: ==

In the psql command shell, "\x" allows you to toggle "expanded display"
for query results.  This is very useful for result sets with long rows.

== PostgreSQL Local ==

Jeff Davis will be speaking at the San Francisco PostgreSQL Users
Group on Tuesday January 13, 2009 at Blurb.  RSVP below for admission
and food.
http://postgresql.meetup.com/1/calendar/9351228/

Richard Broersma is coordinating booth volunteers for SCALE (below).
Contact him at richard DOT broersma AT gmail DOT com.

The Moscow PostgreSQL and MySQL groups will be holding a join panel
January 22, 2009 in Moscow.  Details and sign-up below.
http://forum.postgresqlrussia.org/viewtopic.php?f=6&t=96

SYDPUG will be meeting February 3, 2009 at 6:30pm.  Watch this space
for details.
http://archives.postgresql.org/sydpug/

Many of the usual suspects will be at FOSDEM February 7-8 in Brussels,
Belgium.
http://wiki.postgresql.eu/wiki/FOSDEM_2009

PostgreSQL will be at SCALE February 20-22 in Los Angeles.
http://www.socallinuxexpo.org/

Andreas 'ads' Scherbaum will be presenting a talk at Perl Workshop
2009 on February 25 in Frankfurt am Main, Germany.
http://www.perl-workshop.de/talks/151/view

PostgreSQL Conference, U.S. will be holding a PgDay at LinuxFest
Northwest (April 25/26th). The call for papers is out at
http://www.postgresqlconference.org/

PGCon 2009 will be held 21-22 May 2009, in Ottawa at the University of
Ottawa.  It will be preceded by two days of tutorials on 19-20 May
2009.
http://www.pgcon.org/2009/papers.php

PGCon Brazil will be take place October 23-24 2009 at Unicamp in
Campinas, Sao Paulo state.

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter,
Josh Berkus, and Dave Page.

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

Peter Eisentraut committed:

- Allow out-of-tree builds on mingw and cygwin.  Author: Richard
  Evans.

- Fix for cross-compilation between mingw32 and something else.  The
  choice of pwd vs. pwd -W is correctly a function of the build
  system, not the host system.

- When cross-compiling, allow and require an external zic program to
  be used when --with-system-tzdata is not used.  Initial patch by
  Richard Evans

- Produce a full version string for Sun Studio.  From Zdenek Kotala.

- In pgsql/doc/src/sgml/ddl.sgml, add note that not all SQL commands
  support ONLY in the same way.

- Rewrite update-po target, so that it works less like a shell script
  and more like a makefile with real dependencies.  Instead of
  overwriting the old po file, write the new one to .po.new.  This is
  less annoying and integrates better with the NLS web site.  Also, we
  can now merge languages that don't have a po file yet, by merging
  against all other po files of that language, to pick up recurring
  translations automatically.  This previously only worked when a po
  file already existed.

- In pgsql/src/nls-global.mk, more portable use of "find".

- Change chapter titles thus: "III. Server Administration  15.
  Installation from Source Code  16. Installation from Source Code on
  Windows  17. Server Setup and Operation" to give users of binary
  installations a better idea where to start reading.  Suggested by
  Nikolay Samokhvalov

- Remove fairly useless mixed-case identifier test that causes locale
  dependencies.

- Make tests pass with or without locale.

Tom Lane committed:

- In pgsql/src/backend/utils/misc/guc.c, fix define_custom_variable to
  preserve any sourcefile/sourceline information present in the
  placeholder.  Noted while testing pg_stat_statements.

- Add EmitWarningsOnPlaceholders calls to contrib modules that are
  likely to get listed in custom_variable_classes.

- Quiet repeated "loaded library" messages from child backends that
  are just re-loading a library already loaded into the postmaster.
  ITAGAKI Takahiro

- Add a --role option to pg_dump, pg_dumpall, and pg_restore.  This
  allows performing dumps and restores in accordance with a security
  policy that forbids logging in directly as superuser, but instead
  specifies that you should log into an admin account and then SET
  ROLE to the superuser.  In passing, clean up some ugly and
  mostly-broken code for quoting shell arguments in pg_dumpall.
  Benedek Laszlo, with some help from Tom Lane

- In pgsql/src/backend/optimizer/util/clauses.c, fix an oversight in
  the function-default-arguments patch: after adding some default
  expressions to a function call, eval_const_expressions must recurse
  on those expressions.  Else they don't get simplified, and in
  particular we fail to insert additional default arguments if any
  functions needing defaults are in there.  Per report from Rushabh
  Lathia.

- In pgsql/src/backend/commands/functioncmds.c, add some comments
  about why function parameter default expressions are restricted.

- Doesn't seem like a good idea to be doing AC_CHECK_SIZEOF(void *) so
  much earlier than all the other sizeof checks, and it certainly
  fails to follow the order suggested at the file head.  Rearrange.

- In pgsql/src/backend/utils/adt/pg_lzcompress.c, revert the default
  toast compression strategy to the former behavior where there's no
  limit on the size of datum we'll try to compress.  Other 8.4 tweaks
  to the behavior remain in place.  Per discussion.

- Change a couple of ill-advised uses of INFO elog level to WARNINGs;
  in particular this allows EmitWarningsOnPlaceholders messages to
  show up in the postmaster log by default.  Update elog.h comment to
  make it clearer what INFO is for, and fix one example in the SGML
  docs that was misusing it.  Per my gripe of yesterday.

- Remove references to pgsql-ports and pgsql-patches mailing lists
  from various documentation, since those lists are now
  dead/deprecated.  Point to pgsql-bugs and/or pgsql-hackers as
  appropriate.

- In pgsql/src/backend/commands/analyze.c, clarify a confusing comment
  about MCVs vs histogram entries.  Per Nathan Boley.

- Fix executor/spi.h to follow our usual conventions for include
  files, ie, not include postgres.h nor anything else it doesn't
  directly need.  Add #includes to calling files as needed to
  compensate.  Per my proposal of yesterday.  This should be noted as
  a source code change in the 8.4 release notes, since it's likely to
  require changes in add-on modules.

- Insert conditional SPI_push/SPI_pop calls into InputFunctionCall,
  OutputFunctionCall, and friends.  This allows SPI-using functions to
  invoke datatype I/O without concern for the possibility that a
  SPI-using function will be called (which could be either the I/O
  function itself, or a function used in a domain check constraint).
  It's a tad ugly, but not nearly as ugly as what'd be needed to make
  this work via retail insertion of push/pop operations in all the
  PLs.  This reverts my patch of 2007-01-30 that inserted some retail
  SPI_push/pop calls into plpgsql; that approach only fixed plpgsql,
  and not any other PLs.  But the other PLs have the issue too, as
  illustrated by a recent gripe from Christian Schroeder.  Back-patch
  to 8.2, which is as far back as this solution will work.  It's also
  as far back as we need to worry about the domain-constraint case,
  since earlier versions did not attempt to check domain constraints
  within datatype input.  I'm not aware of any old I/O functions that
  use SPI themselves, so this should be sufficient for a back-patch.

- Create a third option named "partition" for constraint_exclusion,
  and make it the default.  This setting enables constraint exclusion
  checks only for appendrel members (ie, inheritance children and
  UNION ALL arms), which are the cases in which constraint exclusion
  is most likely to be useful.  Avoiding the overhead for simple
  queries that are unlikely to benefit should bring the cost down to
  the point where this is a reasonable default setting.  Per today's
  discussion.

- In pgsql/src/backend/parser/analyze.c, defend against null input in
  analyze_requires_snapshot(), per report from Rushabh Lathia.

- Arrange for function default arguments to be processed properly in
  expressions that are set up for execution with ExecPrepareExpr
  rather than going through the full planner process.  By introducing
  an explicit notion of "expression planning", this patch also lays a
  bit of groundwork for maybe someday allowing sub-selects in
  standalone expressions.

- In pgsql/src/interfaces/libpq/fe-protocol3.c, fix libpq so that it
  reports PGRES_EMPTY_QUERY not PGRES_COMMAND_OK when an empty query
  string is passed to PQexecParams and related functions.  Its
  handling of the NoData response to Describe messages was subtly
  incorrect.  Per my report of yesterday.  Although I consider this a
  bug, it's a behavioral change that might affect applications, so not
  back-patched.  In passing fix a second issue in the same code: it
  didn't react well to an out-of-memory failure while trying to make
  the PGresult object.

- In pgsql/doc/src/sgml/ref/psql-ref.sgml, a further attempt at
  clarifying the \distv business.

- Revise the TIDBitmap API to support multiple concurrent iterations
  over a bitmap.  This is extracted from Greg Stark's posix_fadvise
  patch; it seems worth committing separately, since it's potentially
  useful independently of posix_fadvise.

- Re-enable the old code in xlog.c that tried to use posix_fadvise(),
  so that we can get some buildfarm feedback about whether that
  function is still problematic.  (Note that the planned async-preread
  patch will not really prove anything one way or the other in
  buildfarm testing, since it will be inactive with default GUC
  settings.)

- Implement prefetching via posix_fadvise() for bitmap index scans.  A
  new GUC variable effective_io_concurrency controls how many
  concurrent block prefetch requests will be issued.  (The best way to
  handle this for plain index scans is still under debate, so that
  part is not applied yet --- tgl)

Bruce Momjian committed:

- Update Japanese FAQ.  Jun Kuwamura

- In pgsql/src/timezone/Makefile, fix new timezone cross-compile rule
  to avoid a bug in gmake 3.78.1; document change.

- In pgsql/src/backend/access/common/reloptions.c, suppress compiler
  warning.

- Update SELECT version() to show whether it is a 32 or 64-bit backend
  binary.

- In pgsql/src/backend/access/common/reloptions.c, suppress compiler
  warning in a different way, per Alvaro Herrera.

- In pgsql/src/bin/pg_dump/pg_restore.c, update pg_restore --help text
  to match the SGML documentation.

- Make pg_dump and pg_dumpall --clean options match the SGML docs, for
  consistency.

- In pgsql/doc/src/sgml/installation.sgml, document that Cygwin does
  not support OpenSSL.

- This makes all the \dX commands (most importantly to most: \df) work
  like \dt does, in that it requires a \dXS to see system items.  Greg
  Sabino Mullane

- Add documentation for new \d*S* patch, and clean up some of the
  docs.  Fix \do and trigger display for the patch too.

- In pgsql/src/bin/psql/help.c, improve \z psql \? help display.

- In pgsql/src/bin/psql/help.c, add spaces around psql \d* columns,
  per idea from Joshua Drake.

- In pgsql/src/bin/psql/help.c, remove duplicate \dd psql help
  mention.

- Update MinGW so it handles fseeko() similar to Unix.

- In pgsql/src/backend/utils/error/elog.c, make the log output of
  'vxid' between csvlog and stderr/syslog consistent. Currently, in
  csvlog, vxid of an auxiliary process isn't displayed. On the other
  hand, in stderr/syslog, invalid vxid (-1/0) of that is displayed.
  Fujii Masao

- In pgsql/doc/src/sgml/ref/psql-ref.sgml, break out \distv into four
  separate lines in the psql documentation, for clarity.

- In pgsql/src/backend/tcop/postgres.c, update comment associated with
  'debug_query_string'.

- In pgsql/src/backend/utils/adt/misc.c, add comment that it is
  difficult to access the more accurate 'query_string' from
  current_query().

- Have current_query() use ActivePortal->sourceText rather than
  debug_query_string;  this allows current_query() to be more
  accurate; docs updated;  per idea from Tom Lane.

- In pgsql/src/backend/parser/gram.y, add comment about why BETWEEN
  uses operator strings and not opclasses, with URL pointing to email
  discussion.

- Revert current_query() change to use debug_query_string  again;  add
  comment.

- In pgsql/doc/src/sgml/func.sgml, document current_query() as being
  the _client_ query.

- In pgsql/doc/src/sgml/func.sgml, document that txid_visible_in_snapshot()
  cannot be used with subtransaction ids.

- In pgsql/src/test/regress/pg_regress.c, fix memory leak for file
  name if expect file name contains a dot, per report from dvice_null
  AT yahoo DOT com.

- Update release notes for  8.3.5, 8.2.11, and 8.1.15 to mention the
  need to reindex GiST indexes to read: "If you were running a
  previous 8.X.X release, REINDEX all GiST indexes after the upgrade."

- In pgsql/doc/src/sgml/catalogs.sgml, remove tabs from SGML docs.

- In pgsql/doc/src/sgml/README.links, add file to explain SGML linking
  options, for use by SGML document writers.

- In pgsql/doc/src/sgml/README.links, add guidelines section.

- In pgsql/doc/src/sgml/README.links, update link guidelines.

- In pgsql/doc/src/sgml/libpq.sgml, modify libpq environment variable
  documentation to point to the relevant connection options
  documentation section, per idea from Magnus.

Alvaro Herrera committed:

- Change the reloptions machinery to use a table-based parser, and
  provide a more complete framework for writing custom option
  processing routines by user-defined access methods.  Catalog version
  bumped due to the general API changes, which are going to affect
  user-defined "amoptions" routines.

- Fix string reloption handling, per KaiGai Kohei.

- A couple further reloptions improvements, per KaiGai Kohei: add a
  validation function to the string type and add a couple of macros
  for string handling.  In passing, fix an off-by-one bug of mine.

Tatsuo Ishii committed:

- Remove outdated Japanese README files per discussion.

Heikki Linnakangas committed:

- In pgsql/src/backend/commands/vacuumlazy.c, fix logic in lazy vacuum
  to decide if it's worth trying to truncate the heap.  If the table
  was smaller than REL_TRUNCATE_FRACTION (= 16) pages, we always tried
  to acquire AccessExclusiveLock on it even if there were no empty
  pages at the end.  Report by Simon Riggs.  Back-patch all the way to
  7.4.

Magnus Hagander committed:

- In pgsql/src/backend/utils/adt/pg_locale.c, code review of strftime
  patch, per comments from Tom Lane.  Use correct buffer size
  MAX_L10N_DATA, and use strlcpy instead of StrNCpy.

Modified Files:
--------------

- In pgsql/src/tools/msvc/Solution.pm, make the MSVC build output
  "32-bit" at the end of the version string, the same way the unix
  build now does.

- In pgsql/src/interfaces/libpq/fe-secure.c, don't require
  pqGetHomeDirectory to succeed if the user has specified hardcoded
  paths for SSL rootcert/crl/clientcert/key.  As noted by Andrew
  Chernow.

- In pgsql/doc/src/sgml/config.sgml, add appropriate text for SIGHUP
  parameters instead of just removing it like previous patch did.  Per
  note from Tom Lane.

- Allow krb_realm (krb5, gssapi and sspi) and krb_server_hostname
  (krb5 only) authentication options to be set in pg_hba.conf on a
  per-line basis, to override the defaults set in postgresql.conf.

- Add hba parameter include_realm to krb5, gss and sspi
  authentication, used to pass the full username@realm string to the
  authentication instead of just the username. This makes it possible
  to use pg_ident.conf to authenticate users from multiple realms as
  different database users.

- Make krb_realm and krb_server_hostname be pg_hba options only, and
  remove their GUCs.  In passing, noted that the pg_hba options for
  krb5 authentication weren't listed at all - so add this.

- In pgsql/src/backend/utils/adt/pg_locale.c, fix strftime usage on
  Win32 when trying to fetch the locale-aware parts of a time string
  so it properly handles different encodings.  Original patch by
  Hiroshi Saito, heavily reworked by me and ITAGAKI Takahiro.

Andrew Dunstan committed:

- In pgsql/src/include/pg_config.h.win32, define HAVE_FSEEKO for MSVC.

- In pgsql/doc/src/sgml/catalogs.sgml, document values for
  pg_constraint confupdtype, confdeltype and confmatchtype columns.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

KaiGai Kohei sent in two more revisions of his SE-PostgreSQL patches.

Martin Pihlak sent in another revision of his SQL/MED compatible
connection manager code including functions which list foreign
connection options as a string and as a result set.

Nathan Boley sent in a fix where in heavy-tailed distributions, it's
possible for ANALYZE to duplicate histogram boundaries by limiting
mcvs to be twice the statistics target and when it finds values
frequently enough, reduces the number of histogram buckets.

Bryce Cutt sent in another revision of the patch to improve
performance of multi-batch hash join for skewed data sets.

KaiGai Kohei sent in a fix atop Stephen Frost's column-level
privileges patch which makes sure only available columns are used in
JOINs, etc.

Robert Haas sent in a cleaned-up version of the patch to improve
performance of multi-batch hash join for skewed data sets.

Heikki Linnakangas sent in a patch to unlock index blocks atop Simon
Riggs's hot standby patch.

Fujii Masao sent in another revision of the signal handling patch
which is infrastructure for synchronous replication.

ITAGAKI Takahiro sent in a patch to fix LC_TIME on Windows in
Japanese.

David Wheeler sent in a patch which adds a mention of CITEXT to the
FAQ that discusses case-insensitive comparisons.

Hitoshi Harada sent in an example of a user-defined windowing
function.

Stephen Frost sent in four more revisions of his column-level
permissions patch.

Heikki Linnakangas sent in a patch to help the hot standby patch by
refactoring things so that redo-functions are responsible for calling
RestoreBkpBlocks.

Andrew Dunstan sent in another revision of his parallel restore patch.

Magnus Hagander sent in two approaches to fixing a kerberos problem,
the first, removing support for getting the username, and the second,
to suppress the error message until later.

Hiroshi Inoue sent in another revision of a patch to fix lc_messages
on Windows for Japanese language.

Peter Eisentraut sent in a patch which adds ONLY support to TRUNCATE
and LOCK, making the default behavior recursive on inherited tables.

KaiGai Kohei sent in another revision of his SE-PostgreSQL patches.

Alvaro Herrera sent in a patch which fixes the reloptions code.

Jeff Davis sent in an updated version of Kenneth Marshall's patch
which updates the hash functions.

Bernd Helmle sent in two more revisions of his updateable views patch.

Heikki Linnakangas sent in a patch which removes the slotids from the
hot standby patch.

Fujii Masao sent in another revision of the synchronous replication
patch.

Robert Haas sent in another revision of the POSIX fadvise patch.

Jeff Davis sent in an updated version of the B-Tree emulation for GIN
patch.