== Postgres Weekly News - January 13 2008 == - Mailing list pgsql-announce

== Postgres Weekly News - January 13 2008 ==

Security releases 8.3 RC1, 8.2.6, 8.1.11, 8.0.15, 7.4.19 and 7.3.21
are out.  Upgrade ASAP!

ASQL, a Data Warehouse engine atop Postgres, has been announced.
http://www.analyticsql.org/index.html

Devrim GUNDUZ and Darcy Buskermolen have created the PostgreSQL RPM
Buildfarm, which will provide native binaries for many RPM-based
distributions.

Devrim GUNDUZ and Darcy Buskermolen have created a yum repository for
Postgres.
http://yum.pgsqlrpms.org

7.3.21 is the final release in the community 7.3 series.  Upgrade from
7.3 as soon as you can.

== Postgres Product News ==

Open Technology Group now has PostGIS and UMN Mapserver Training.
http://www.otg-nc.com

PGCluster 1.7.0rc8 released.
http://pgfoundry.org/projects/pgcluster/

pg_proboscis and pg_typical 0.9.2 released.
http://pgfoundry.org/projects/python/

PL/Lua 0.2 released.
http://pgfoundry.org/projects/pllua/

PL/Proxy 2.0.4 released.
http://pgfoundry.org/projects/plproxy/

== Postgres Jobs for January ==

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

== Postgres Local ==

What's new in PostgreSQL 8.3 - Breakfast with Bruce Momjian
24th January 2008 - London.  Email below to get in.
info.emea <AT> enterprisedb.com

PGCon 2008 will be May 20-23 in Ottawa - now accepting proposals.
http://www.pgcon.org/2008/papers.php

The BSD and PostgreSQL teams share a developer room at FOSDEM 2008 in
Brussels February 23-34, 2008.  If you want to give a talk or help
managing the dev room, contact fosdem@pgug.eu.  For more information,
see:
http://fosdem.org/2008/schedule/devroom/bsdpostgresql

PostgreSQL Conference East '08 talks are March 29 and 30 at the
University of Maryland, College Park.  The Call for Papers is open.
http://www.postgresqlconference.org/

== Postgres in the News ==

Planet PostgreSQL: http://www.planetpostgresql.org/

General Bits, Archives and occasional new articles:
http://www.varlena.com/GeneralBits/

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

Michael Meskes committed:

- In ecpg, fixed lexer to parse C quotes correctly.

- In ecpg, changed prototype for ECPGdo because some compilers don't
  like int/enum aliasing in there.

Magnus Hagander committed:

- In pgsql/src/include/port/win32.h, don't enforce 32-bit time_t for
  FRONTEND apps.  Fixes standalone builds of libpq in both 32 and
  64-bit.  Per gripe from Hiroshi Saito.

- In pgsql/src/interfaces/libpq/win32.mak, generate and include
  manifest in standalone libpq build on Windows.  Hiroshi Saito

Alvaro Herrera committed:

- In pgsql/doc/src/sgml/protocol.sgml, add index entry for
  frontend-backend protocol.

Bruce Momjian committed:

- In pgsql/src/tools/RELEASE_CHANGES, mention use of
  src/tools/major_release_split for creating back-branch release
  notes.

- Add URLs to two excellent web pages about SSL API and certificate
  usage.

- In pgsql/doc/src/sgml/info.sgml, remove mention of /contrib README
  files from documentation because files are moved to SGML.

- In pgsql/src/backend/utils/misc/postgresql.conf.sample,
  vacuum_cost_limit has a minimum value of 1, not zero;  update
  postgresql.conf comment to match.

- Add to TODO: "Add ability to trigger on TRUNCATE."

- In pgsql/doc/src/sgml/datatype.sgml, update documentation for
  tsvector duplicate elimination.

Neil Conway committed:

- In pgsql/src/backend/nodes/outfuncs.c, fix a minor bug in outfuncs
  support for SetOp: dupOperators is an array of Oid, and therefore
  should use the "%u" escape sequence rather than "%d".

- In pgsql/src/backend/nodes/outfuncs.c, fix an omission in the
  outfuncs.c support for Agg nodes: the grpColIdx and grpOperators
  fields were not emitted by _outAgg().

- In pgsql/src/backend/utils/adt/xml.c, minor perf tweak for
  _SPI_strdup(): if we're going to call strlen() anyway, it is faster
  to memcpy() than to strcpy().

- Fix two places in xml.c that neglected to check the return values of
  SPI_prepare() and SPI_cursor_open(), to silence a Coverity warning.

Tom Lane committed:

- Fix assorted security-grade bugs in the regex engine.  All of these
  problems are shared with Tcl, since it's their code to begin with,
  and the patches have been copied from Tcl 8.5.0.  Problems:
  CVE-2007-4769: Inadequate check on the range of backref numbers
  allows crash due to out-of-bounds read.  CVE-2007-4772: Infinite
  loop in regex optimizer for pattern '($|^)*'.  CVE-2007-6067: Very
  slow optimizer cleanup for regex with a large NFA representation, as
  well as crash if we encounter an out-of-memory condition during NFA
  construction.  Part of the response to CVE-2007-6067 is to put a
  limit on the number of states in the NFA representation of a regex.
  This seems needed even though the within-the-code problems have been
  corrected, since otherwise the code could try to use very large
  amounts of memory for a suitably-crafted regex, leading to potential
  DOS by driving the system into swap, activating a kernel OOM killer,
  etc.  Although there are certainly plenty of ways to drive the
  system into effective DOS with poorly-written SQL queries, these
  problems seem worth treating as security issues because many
  applications might accept regex search patterns from untrustworthy
  sources.  Thanks to Will Drewry of Google for reporting these
  problems.  Patches by Will Drewry and Tom Lane.  Security:
  CVE-2007-4769, CVE-2007-4772, CVE-2007-6067

- Make standard maintenance operations (including VACUUM, ANALYZE,
  REINDEX, and CLUSTER) execute as the table owner rather than the
  calling user, using the same privilege-switching mechanism already
  used for SECURITY DEFINER functions.  The purpose of this change is
  to ensure that user-defined functions used in index definitions
  cannot acquire the privileges of a superuser account that is
  performing routine maintenance.  While a function used in an index
  is supposed to be IMMUTABLE and thus not able to do anything very
  interesting, there are several easy ways around that restriction;
  and even if we could plug them all, there would remain a risk of
  reading sensitive information and broadcasting it through a covert
  channel such as CPU usage.  To prevent bypassing this security
  measure, execution of SET SESSION AUTHORIZATION and SET ROLE is now
  forbidden within a SECURITY DEFINER context.  Thanks to Itagaki
  Takahiro for reporting this vulnerability.  Security: CVE-2007-6600

- The original patch to disallow non-passworded connections to
  non-superusers failed to cover all the ways in which a connection
  can be initiated in dblink.  Plug the remaining holes.  Also,
  disallow transient connections in functions for which that feature
  makes no sense (because they are only sensible as part of a sequence
  of operations on the same connection).  Joe Conway Security:
  CVE-2007-6601

- Update release notes for security releases.  Security:
  CVE-2007-4769, CVE-2007-4772, CVE-2007-6067, CVE-2007-6600,
  CVE-2007-6601

- Stamp release 8.3RC1.  Security: CVE-2007-4769, CVE-2007-4772,
  CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 8.2.6.  Security: CVE-2007-4769, CVE-2007-4772,
  CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 8.1.11.  Security: CVE-2007-4769, CVE-2007-4772,
  CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 8.0.15.  Security: CVE-2007-4769, CVE-2007-4772,
  CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 7.4.19.  Security: CVE-2007-4769, CVE-2007-4772,
  CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 7.3.21.  Security: CVE-2007-4769, CVE-2007-4772,
  CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- In pgsql/src/backend/utils/adt/tsquery.c, remove unnecessary comma
  in enum definition ... some C compilers don't like that.  Per report
  from J6M.

- informix.c was violating the coding rule about not including any
  system headers before c.h.  Per report from J6M.

- lmgr.c:DescribeLockTag was never taught about virtual xids, per Greg
  Stark.  Also a couple of minor tweaks to try to future-proof the
  code a bit better against future locktag additions.

- Fix some planner issues found while investigating Kevin Grittner's
  report of poorer planning in 8.3 than 8.2: 1. After pushing a
  constant across an outer join --- ie, given "a LEFT JOIN b ON (a.x =
  b.y) WHERE a.x = 42", we can deduce that b.y is sort of equal to 42,
  in the sense that we needn't fetch any b rows where it isn't 42 ---
  loop to see if any additional deductions can be made.  Previous
  releases did that by recursing, but I had mistakenly thought that
  this was no longer necessary given the EquivalenceClass machinery.
  2. Allow pushing constants across outer join conditions even if the
  condition is outerjoin_delayed due to a lower outer join.  This is
  safe as long as the condition is strict and we re-test it at the
  upper join.  3. Keep the outer-join clause even if we successfully
  push a constant across it.  This is *necessary* in the
  outerjoin_delayed case, but even in the simple case, it seems better
  to do this to ensure that the join search order heuristics will
  consider the join as reasonable to make.  Mark such a clause as
  having selectivity 1.0, though, since it's not going to eliminate
  very many rows after application of the constant condition.  4.
  Tweak have_relevant_eclass_joinclause to report that two relations
  are joinable when they have vars that are equated to the same
  constant.  We won't actually generate any joinclause from such an
  EquivalenceClass, but again it seems that in such a case it's a good
  idea to consider the join as worth costing out.  5. Fix a bug in
  select_mergejoin_clauses that was exposed by these changes: we have
  to reject candidate mergejoin clauses if either side was equated to
  a constant, because we can't construct a canonical pathkey list for
  such a clause.  This is an implementation restriction that might be
  worth fixing someday, but it doesn't seem critical to get it done
  for 8.3.

- Fix a bug in 8.2.x that was exposed while investigating Kevin
  Grittner's report of poor planning in 8.3: it's unsafe to push a
  constant across an outer join when the outer-join condition is
  delayed by lower outer joins, unless we recheck the outer-join
  condition at the upper outer join.  8.2.x doesn't really have the
  ability to tell whether this is the case or not, but fortunately it
  doesn't matter --- it seems most desirable to keep the join
  condition whether it's entirely redundant or not.  However, it's
  usually mostly redundant, so force its selectivity to 1.0.  It might
  be a good idea to back-patch this into 8.1 as well, but I'll refrain
  until/unless there's evidence that 8.1 actually fails on any cases
  that this would fix.

- Fix CREATE INDEX CONCURRENTLY to not deadlock against an automatic
  or manual VACUUM that is blocked waiting to get lock on the table
  being indexed.  Per report and fix suggestion from Greg Stark.

- In pgsql/src/backend/utils/mb/mbutils.c, remove incorrect (and
  ill-advised anyway) pfree's in pg_convert_from and pg_convert_to.
  Per bug #3866 from Andrew Gilligan.

- Restructure the shutdown procedure for the archiver process to allow
  it to finish archiving everything (when there's no error), and to
  eliminate various hazards as best we can.  This fixes a previous 8.3
  patch that caused the postmaster to kill and then restart the
  archiver during shutdown (!?).  The new behavior is that the
  archiver is allowed to run unmolested until the bgwriter has exited;
  then it is sent SIGUSR2 to tell it to do a final archiving cycle and
  quit.  We only SIGQUIT the archiver if we want a panic stop; this is
  important since SIGQUIT will also be sent to any active
  archive_command.  The postmaster also now doesn't SIGQUIT the stats
  collector until the bgwriter is done, since the bgwriter can send
  stats messages in 8.3.  The postmaster will not exit until both the
  archiver and stats collector are gone; this provides some defense
  (not too bulletproof) against conflicting archiver or stats
  collector processes being started by a new postmaster instance.  We
  continue the prior practice that the archiver will check for
  postmaster death immediately before issuing any archive_command;
  that gives some additional protection against conflicting archivers.
  Also, modify the archiver process to notice SIGTERM and refuse to
  issue any more archive commands if it gets it.  The postmaster
  doesn't ever send it SIGTERM; we assume that any such signal came
  from init and is a notice of impending whole-system shutdown.  In
  this situation it seems imprudent to try to start new archive
  commands --- if they aren't extremely quick they're likely to get
  SIGKILL'd by init.  All per discussion.

- Fix a conceptual error in my patch of 2007-10-26 that avoided
  considering clauseless joins of relations that have unexploited join
  clauses.  Rather than looking at every other base relation in the
  query, the correct thing is to examine the other relations in the
  "initial_rels" list of the current make_rel_from_joinlist()
  invocation, because those are what we actually have the ability to
  join against.  This might be a subset of the whole query in cases
  where join_collapse_limit or from_collapse_limit or full joins have
  prevented merging the whole query into a single join problem.  This
  is a bit untidy because we have to pass those rels down through a
  new PlannerInfo field, but it's necessary.  Per bug #3865 from Oleg
  Kharin.

- Fix an old error in clause_selectivity: the default selectivity
  estimate for unhandled clause types ought to be 0.5, not 1.0.  I
  fear I introduced this silliness due to misreading the intent of the
  very-poorly-structured code that was there when we inherited the
  file from Berkeley.  The lack of sanity in this behavior was exposed
  by an example from Sim Zacks.  (Arguably this is a bug fix and
  should be back-patched, but I'm a bit hesitant to introduce a
  possible planner behavior change in the back branches; it might
  detune queries that worked acceptably in the past.) While at it,
  make estimation for DistinctExpr do something marginally realistic,
  rather than just defaulting.

- The original implementation of polymorphic aggregates didn't really
  get the checking of argument compatibility right; although the
  problem is only exposed with multiple-input aggregates in which some
  arguments are polymorphic and some are not.  Per bug #3852 from
  Sokolov Yura.

- Fix logical errors in constraint exclusion: we cannot assume that a
  CHECK constraint yields TRUE for every row of its table, only that
  it does not yield FALSE (a NULL result isn't disallowed).  This
  breaks a couple of implications that would be true in two-valued
  logic.  I had put in one such mistake in an 8.2.5 patch: foo IS NULL
  doesn't refute a strict operator on foo.  But there was another in
  the original 8.2 release: NOT foo doesn't refute an expression whose
  truth would imply the truth of foo.  Per report from Rajesh Kumar
  Mallah.  To preserve the ability to do constraint exclusion with one
  partition holding NULL values, extend
  relation_excluded_by_constraints() to check for attnotnull flags,
  and add col IS NOT NULL expressions to the set of constraints we
  hope to refute.

- In pgsql/src/backend/utils/adt/xml.c, it turns out the
  LIBXML_TEST_VERSION macro calls xmlInitParser().  Therefore we must
  xmlCleanupParser(), or we risk leaving behind dangling pointers to
  whatever memory context is current when xml_init() is called.  This
  seems to fix bug #3860, though we might still want the more invasive
  solution being worked on by Alvaro.

- In pgsql/doc/src/sgml/ddl.sgml, add note pointing out that read-only
  commands no longer consume command IDs.

- Fix a regression test that fails if default_text_search_config isn't
  'english'.

- Fix CREATE INDEX CONCURRENTLY so that it won't use synchronized scan
  for its second pass over the table.  It has to start at block zero,
  else the "merge join" logic for detecting which TIDs are already in
  the index doesn't work.  Hence, extend heapam.c's API so that
  callers can enable or disable syncscan.  (I put in an option to
  disable buffer access strategy, too, just in case somebody needs
  it.)  Per report from Hannes Dorbath.

- Use an indexscan not a heapscan to search pg_index in
  get_pkey_attnames.  Noted while looking for heapscans that might
  need to start from block zero.

- Fix pgstat_heap() to not be broken by syncscans starting from a
  block higher than zero.  Same problem as just detected in CREATE
  INDEX CONCURRENTLY.

== Rejected Patches (for now) ==

Erik Rijkers's doc patch for textsearch.sgml on grounds of not
including the tables referred to in the patch.

Alvaro Herrera's patch to fix BUG #3860.  The fix should use the
palloc infrastructure instead.

== Pending Patches ==

Tom Lane sent in 3 revisions of a patch intended to fix archiver
shutdown behavior.