== PostgreSQL Weekly News - November 16 2008 ==

== PostgreSQL Weekly News - November 16 2008 ==

FOSDEM is soon in Brussels.  See below for details.

== PostgreSQL Product News ==

MicroOLAP Database Designer 1.2.6 for PostgreSQL released.
http://microolap.com/products/database/postgresql-designer/

PGUnit released.
http://en.dklab.ru/lib/dklab_pgunit/

PyReplica 1.0.5 beta released.
http://pgfoundry.org/projects/pyreplica/

== PostgreSQL Jobs for November ==

http://archives.postgresql.org/pgsql-jobs/2008-11/threads.php

== PostgreSQL Local ==

PgDay Rio de la Plata is looking for sponsors.  Interested parties
should contact admin AT postgres-arg DOT org.

Dickson Guedes is looking for volunteers to help with a PgMeeting in
Florianópolis.  Write to guediz AT gmail DOT com if you want to help.

David Fetter will be speaking at PGDay Rio de la Plata Buenos Aires
November 22.
http://pgday.postgres-arg.org/

David Fetter will be speaking at OSDC Sydney December 3-5.
http://www.osdc.com.au/2008/index.html

FOSDEM 2009 will take place february 7-8 2009 in Brussels, Belgium.
PostgreSQL will share a booth with the BSD groups and have a developer
room.   Submit your 20-minute, 45 minute or lightning talks to
fosdem (at) postgresql (dot) org.

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

- Fix old bug in contrib/sslinfo: X509_NAME_to_text freed the
  BIO_s_mem buffer it was using too soon.  In a situation where
  pg_do_encoding_conversion is a no-op, this led to garbage data
  returned.  In HEAD, also modify the code that's ensuring null
  termination to make it a tad more obvious what's happening.

- pg_do_encoding_conversion cannot return NULL (at least not unless
  the input is NULL), so remove some useless tests for the case.

- In pgsql/src/backend/utils/adt/xml.c, fix bugs in sqlchar_to_unicode
  and unicode_to_sqlchar: both were measuring the length of a UTF8
  character with pg_mblen (wrong if DB encoding isn't UTF8), and the
  latter was blithely assuming that a static buffer would somehow
  revert to all zeroes for each use.

- Add support for input and output of interval values formatted per
  ISO 8601; specifically, we can input either the "format with
  designators" or the "alternative format", and we can output the
  former when IntervalStyle is set to iso_8601.  Ron Mayer.

- In pgsql/src/backend/utils/mb/mbutils.c, add an explicit caution
  about how to use pg_do_encoding_conversion with non-null-terminated
  input.  Per discussion with ITAGAKI Takahiro.

- Fix sloppy omission of now-required #include's.

- Get rid of adjust_appendrel_attr_needed(), which has been broken
  ever since we extended the appendrel mechanism to support UNION ALL
  optimization.  The reason nobody noticed was that we are not
  actually using attr_needed data for appendrel children; hence it
  seems more reasonable to rip it out than fix it.  Back-patch to 8.2
  because an Assert failure is possible in corner cases.  Per
  examination of an example from Jim Nasby.  In HEAD, also get rid of
  AppendRelInfo.col_mappings, which is quite inadequate to represent
  UNION ALL situations; depend entirely on translated_vars instead.

- In pgsql/src/backend/optimizer/prep/prepjointree.c, ensure that the
  phrels sets of PlaceHolderVars appearing in an AppendRelInfo's
  translated_vars list get updated when pulling up an appendrel
  member.  It's not clear that this really matters at present, since
  relatively little gets done with the outputs of an appendrel child
  relation; but it probably will come back to bite us sometime if we
  leave them with the wrong values.

- Clean up the ancient decision to show only two fractional-seconds
  digits in "postgres_verbose" intervalstyle, and the equally
  arbitrary decision to show at least two fractional-seconds digits in
  most other datetime display styles.  This results in some minor
  changes in the expected regression test outputs.  Also, coalesce a
  lot of repetitive code in datetime.c into subroutines, for clarity
  and ease of maintenance.  In particular this roughly halves the
  number of #ifdef HAVE_INT64_TIMESTAMP segments.  Ron Mayer, with
  some additional kibitzing from Tom Lane

- In pgsql/src/include/catalog/pg_proc.h, if we're going to use a SQL
  function for this, at least make it schema-proof.

- In pgsql/src/backend/optimizer/util/predtest.c, install a limit on
  the number of branches we will process in AND, OR, or equivalent
  clauses: if there are too many (more than 100) just exit without
  proving anything.  This ensures that we don't spend O(N^2) time
  trying (and most likely failing) to prove anything about very long
  IN lists and similar cases.  Also, install a couple of
  CHECK_FOR_INTERRUPTS calls to ensure that a long proof attempt can
  be interrupted.  Per gripe from Sergey Konoplev.  Back-patch the
  whole patch to 8.2 and just the CHECK_FOR_INTERRUPTS addition to
  8.1.  (The rest of the patch doesn't apply cleanly, and since 8.1
  doesn't show the complained-of behavior anyway, it doesn't seem
  necessary to work

- In pgsql/src/backend/optimizer/util/predtest.c, arrange to cache the
  results of looking up a btree predicate proof comparison operator.
  The result depends only on the two input operators and the proof
  direction (imply or refute), so it's easy to cache.  This provides a
  very large savings in cases such as Sergey Konoplev's long
  NOT-IN-list example, where predtest spends all its time repeatedly
  figuring out that the same pair of operators cannot be used to prove
  anything.  (But of course the O(N^2) behavior still catches up with
  you eventually.)  I'm not convinced it buys a whole lot when
  constraint_exclusion isn't turned on, but it's not a lot of added
  code so we might as well cache all the time.

- In pgsql/src/include/utils/pg_crc.h, PGDLLIMPORT-ize the global
  variables referenced in pg_crc.h.  I think this will fix current
  mingw buildfarm failures for pg_trgm.

- Prevent synchronous scan during GIN index build, because GIN is
  optimized for inserting tuples in increasing TID order.  It's not
  clear whether this fully explains Ivan Sergio Borgonovo's complaint,
  but simple testing confirms that a scan that doesn't start at block
  0 can slow GIN build by a factor of three or four.  Backpatch to
  8.3.  Sync scan didn't exist before that.

- In pgsql/src/timezone/pgtz.c, suppress leap-second-aware timezones
  in the output of pg_tzenumerate_next, and thereby in the
  pg_timezone_names view.  Although we allow such zones to be used in
  certain limited contexts like AT TIME ZONE, we don't allow them in
  SET TIME ZONE, and bug #4528 shows that they're more likely to
  confuse users than do anything useful.  So hide 'em.  (Note that we
  don't even generate these zones when installing our own timezone
  database.  But they are likely to be present when using a
  system-provided database.)

- In pgsql/doc/src/sgml/func.sgml, marginal editorial improvements for
  array_agg patch documentation.

- In pgsql/src/backend/utils/adt/array_userfuncs.c, minor code clarity
  improvements in array_agg functions, and add a comment about how
  this is playing fast and loose with the type system.

- Implement the basic form of UNNEST, ie unnest(anyarray) returns
  setof anyelement.  This lacks the WITH ORDINALITY option, as well as
  the multiple input arrays option added in the most recent SQL specs.
  But it's still a pretty useful subset of the spec's functionality,
  and it is enough to allow obsoleting contrib/intagg.

- Actually, instead of whining about how type internal might not
  safely store a pointer, why don't we just fix that.  Every known use
  of "internal" really means a pointer anyway.

- In pgsql/src/interfaces/ecpg/preproc/Makefile, improve comment about
  when to clean generated files.

- In pgsql/src/backend/commands/conversioncmds.c, make CREATE
  CONVERSION verify that a putative encoding conversion function
  returns VOID.  This is the last of the easy fixes I recommended in
  11870.1218838360@sss.pgh.pa.us --- the others got done awhile ago
  but I forgot about this one.

- In pgsql/src/interfaces/ecpg/preproc/preproc.y, ecpg's preproc.y is
  now generated as needed, so remove from CVS.

- In CREATE AGGREGATE, allow the transition datatype to be "internal",
  but only if the user is superuser.  This makes available to
  extension modules the same sort of trick being practiced by
  array_agg().  The reason for the superuser restriction is that you
  could crash the system by connecting up an incompatible pair of
  internal-using functions as an aggregate.  It shouldn't interfere
  with any legitimate use, since you'd have to be superuser to create
  the internal-using transition and final functions anyway.

- Reduce contrib/intagg to a thin wrapper around the new core
  functions array_agg() and unnest().  We could drop it entirely in
  the future, but let's keep it for a release or two as a
  compatibility assist.

- In pgsql/src/include/utils/pg_crc.h, second try at fixing DLLIMPORT
  problem for pg_crc.h on Cygwin.

- In pgsql/src/bin/pg_resetxlog/pg_resetxlog.c, one more hack to see
  if we can get the cygwin machines building again.  This continues
  the saga of trying to get PGDLLIMPORT to work in pg_crc.h in both
  backend and frontend environments.

- In pgsql/doc/src/sgml/libpq.sgml, document a gotcha that happens on
  Windows when using libpq's new event procedure support: it's
  possible to get confused because exported procedures have two
  different addresses.  Per Andrew Chernow.

- Make SELECT FOR UPDATE/SHARE work on inheritance trees, by having
  the plan return the tableoid as well as the ctid for any FOR UPDATE
  targets that have child tables.  All child tables are listed in the
  ExecRowMark list, but the executor just skips the ones that didn't
  produce the current row.  Curiously, this longstanding restriction
  doesn't seem to have been documented anywhere; so no doc changes.

- In pgsql/src/interfaces/ecpg/preproc/Makefile, add missing
  dependencies to preproc.y build rule.

- Modify UPDATE/DELETE WHERE CURRENT OF to use the FOR UPDATE
  infrastructure to locate the target row, if the cursor was declared
  with FOR UPDATE or FOR SHARE.  This approach is more flexible and
  reliable than digging through the plan tree; for instance it can
  cope with join cursors.  But we still provide the old code for use
  with non-FOR-UPDATE cursors.  Per gripe from Robert Haas.

Alvaro Herrera committed:

- Add a --locale switch to createdb, to ease the creation of databases
  with different locales.  This is just syntactical sweetener over
  --lc-collate and --lc-ctype.  Per discussion.  While at it, properly
  document --lc-ctype and --lc-collate in SGML docs, which apparently
  were forgotten (or purposefully ommited?) when they were created.

- Fix a case of string building in text search.

- In pgsql/src/include/utils/pg_crc.h, update URL to Ross Williams'
  CRC paper.  Per note from Devrim GUNDUZ.

- Replace the usage of heap_addheader to create pg_attribute tuples
  with regular heap_form_tuple.  Since this removes the last remaining
  caller of heap_addheader, remove it.  Extracted from the column
  privileges patch from Stephen Frost, with further code cleanups by
  me.

Heikki Linnakangas committed:

- In pgsql/src/backend/utils/adt/formatting.c, fix 'Q' format char
  parsing in the new to_timestamp() code.  Used to crash.

- Change error messages to print the physical path, like
  "base/11517/3767_fsm", instead of symbolic names like
  "1663/11517/3767/1", per Alvaro's suggestion. I didn't change the
  messages in the higher-level index, heap and FSM routines, though,
  where the fork is implicit.

- In pgsql/src/backend/postmaster/autovacuum.c, fix off-by-one error
  in autovacuum shmem struct sizing. This could lead to autovacuum
  worker sending SIGUSR1 signal to wrong process, per Zou Yong's
  report.  Backpatch to 8.3.

- In pgsql/src/backend/storage/smgr/md.c, fix oversight in previous
  error-reporting patch; mustn't pfree path string before passing it
  to elog.

Michael Meskes committed:

- Replaced manually synced preproc.y by the one created by the new
  script.  Adapted regression test files accordingly.

- In pgsql/src/backend/parser/gram.y, do not use ICONST/SCONST in
  rules other than Iconst/Sconst.

- In pgsql/src/backend/parser/gram.y, removed two non-terminals:
  FloatOnly, only used by NumericOnly, instead put the FloatOnly
  production into NumericOnly, and IntegerOnly, only used by
  NumericOnly and one ALTER TABLE rule, replacement SignedIconst is
  already used in several other places

- Updated parser file to the one generated by the latest version of
  parse.[awk|pl] from the latest version of gram.y.  Some small
  corrections to test suite.

- Add pgsql/src/interfaces/ecpg/preproc/parse.pl script that generates
  preproc.y from gram.y to CVS.

- Added files containing changes between gram.y and preproc.y.

- In ecpg, fixed test for output_filename == stdout.

- In ecpg, enable script to generate preproc.y in build process.

Magnus Hagander committed:

- In pgsql/src/bin/psql/command.c, make psql report "SSL connection
  (unknown cipher)" when libpq has set up a SSL connection, but psql
  is compiled without support for it.  Not a really realistic
  use-case, but the patch also cuts down on the number of places with
  #ifdef's...

- In pgsql/doc/src/sgml/monitoring.sgml, mention the tup_fetched
  column in pg_stat_database.  Greg Sabino Mullane.

- Fix libpq certificate validation for SSL connections.  Add config
  parameter "sslverify" to control the verification. Default is to do
  full verification.  Clean up some old SSL code that never really
  worked.

- In pgsql/src/tools/msvc/Mkvcbuild.pm, exclude contrib/intagg from
  the list of MSVC project files to be generated, since it's now just
  a SQL module and no C code.

Andrew Dunstan committed:

- Detect and error out on inability to get proper linkage information
  required for plperl, usually due to absence of perl ExtUtils::Embed
  module. Backpatch as far as 8.1.

- In pgsql/src/interfaces/ecpg/preproc/ecpg.trailer, add missing
  semicolon, per gripe from Alex Hunsaker.

Peter Eisentraut committed:

- array_length() function, and for SQL compatibility also
  cardinality() function as a special case.  This version still has
  the suspicious behavior of returning null for an empty array (rather
  than zero), but this may need a wholesale revision of empty array
  behavior, currently under discussion.  Jim Nasby, Robert Haas, Peter
  Eisentraut

- In pgsql/doc/src/sgml/dblink.sgml, add refentrytitle elements to
  refentry pages.  Their absence confused the man page tools somewhat.

- Remove unnecessary whitespace in refname elements.  This confuses
  the man page tools.

- In pgsql/doc/src/sgml/Makefile, support for man page links, if a man
  page has more than one refname.  We don't actually use this
  anywhere, but it might come in handy for dealing with
  SELECT/WITH/TABLE.  It works with both the old and the new man page
  target (for some value of "works").

- In pgsql/doc/src/sgml/func.sgml, since the xmlagg section was moved,
  "below" is no longer accurate.

- array_agg aggregate function, as per SQL:2008, but without ORDER BY
  clausea.  Rearrange the documentation a bit now that array_agg and
  xmlagg have similar semantics and issues.  Best of Robert Haas, Jeff
  Davis, Peter Eisentraut

- Set SQL man pages to be section 7 by default, and only transform
  them to another section if required by the platform (instead of the
  old way of building them in section "l" and always transforming them
  to the platform-specific section).  This speeds up the installation
  on common platforms, and it avoids some funny business with the man
  page tools and build process.

- In pgsql/doc/src/Makefile, since doc/src/sgml already builds the
  HTML docs twice to resolve index entries, we don't have to do two
  builds here as well.

- Fix crash of xmlconcat(NULL).   Also backpatched to 8.3.

Teodor Sigaev committed:

- Support of multibyte encoding for pg_trgm.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

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

ITAGAKI Takahiro sent in another revision of his auto-explain contrib
module.

Hitoshi Harada sent in two more revisions of his windowing functions
patch.

Tom Lane sent in a revised version of Simon Riggs' patch to reduce
some DDL locks to ShareLock.

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

Hiroshi Saito sent in another revision of his locale patch.

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

Alvaro Herrera sent in a patch to update pg_filedump for CVS HEAD.

Pavel Stehule sent in a patch to add FOUND and GET DIAGNOSTICS support
to RETURN QUERY in PL/PgSQL.

Pavel Stehule sent in another revision of his GROUPING SETS patch.

Teodor Sigaev sent in another revision of his B-Tree emulation for
GIN patch, now with more data types.

Magnus Hagander sent in another revision of his usermap regex support
patch.

Heikki Linnakangas sent in another revision of his visibility
map/partial vacuum patch.

ITAGAKI Takahiro sent in another revision of his pg_stat_statements
patch.

Jeff Davis sent in another revision of the auto_explain contrib
module.

Magnus Hagander and Alex Hunsaker sent in a flock of patches to extend
and fix the SSL implementation.