== PostgreSQL Weekly News - January 14 2007 == - Mailing list pgsql-announce

== PostgreSQL Weekly News - January 14 2007 ==

Security updates 8.2.1, 8.1.6, 8.0.10, 7.4.15 and 7.3.17 are out
including packages for Debian, Fedora, Fink, FreeBSD, Red Hat and
Ubuntu.  Upgrade ASAP.

== PostgreSQL Product News ==

Another PostgreSQL Diff Tool 1.0.0_beta13 released.
http://pgfoundry.org/projects/apgdiff/

pfm 1.4.3 released.
http://pgfoundry.org/projects/pfm/

mysql2pgsql-1.0.2 released.
http://gborg.postgresql.org/project/mysql2psql/projdisplay.php

PostgreSQL SDBC Driver 0.7.3 was released:
http://dba.openoffice.org/drivers/postgresql/

Navicat PostgreSQL 7.2.10 for Windows released.
http://pgsql.navicat.com/

Slony-I 1.2.6 is out.
http://slony.info/

audittrail2-beta1 released.
http://pgfoundry.org/projects/audittrail2/

PGCluster 1.5.0rc13 and 1.7.0rc2 released.
http://pgfoundry.org/projects/pgcluster/

== PostgreSQL Local ==

The German PostgreSQL Usergroup ( http://www.pgug.de ) is creating
three new flyers in english and german. The topics will be 1) an
abstract about PG, 2) PG and replication, 3) PG compared to others.
Iv you have material to add or want to participate, please contact
info at pgug dot de.

Please submit your PGCon 2007 proposals.  PGCon is taking place in
Ottawa, Ontario, Canada.  The deadline for submissions is January 19th.
http://www.pgcon.org/2007/submissions.php

There will be a PostgreSQL booth at FOSDEM on February 24 and 25 in
Brussels, Belgium.  Many of the usual suspects from the EU PostgreSQL
communities will be there.  Contact de@postgresql.org to participate.
http://www.fosdem.org/2007/

Pavel Stehule will be teaching a course on stored procedures in
PostgreSQL.  This will be on January 11, 2007 in Prague, and conducted
in Czech.
http://www.root.cz/zpravicky/skoleni-o-ulozenych-procedurach-v-postgresql/

The Italian PostgreSQL community will be holding a PostgreSQL day this
summer.  Bookmark the link below to participate.
http://www.pgday.it

Gavin Sherry is running a PostgreSQL miniconf at Linux.Conf.Au in
Sydney on Tuesday the 16th of January 2007.
http://lca2007.linux.org.au/Miniconfs/PostgreSQL If you would like to
attending, email gavin AT alcove . com . au

== PostgreSQL in the News ==

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

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

PostgreSQL Weekly News is brought to you this week by David Fetter,
Devrim GUNDUZ and Robert Treat

To get your submission into the upcoming issue, get it to
david@fetter.org by Sunday at 3:00pm Pacific Time.

== Applied Patches ==

Alvaro Herrera committed:

- Marko Kreen's patch to replace unnecessary DISABLE_ZLIB define in
  pgcrypto with HAVE_LIBZ from core.

- Fix autovacuum to avoid leaving non-permanent Xids in
  non-connectable databases.  Apply to the 8.1 branch only, as the new
  8.2 (and HEAD) coding does not have this problem.

Michael Meskes committed:

- adjust MinGW expected file to new setup create expected file with
  correct port number.

- Simplified ecpg regression handling, added patch by Joachim Wieland
  to work around OpenBSD bug in regression suite.

- In ecpg, applied Joachim's patch for a --regression option.  Made
  this option mark the .c files, so the environment variable is no
  longer needed.  Created a special MinGW file with the special error
  message.  Do not print port into log file when running regression
  tests.

Neil Conway committed:

- Add a note to the docs describing NaN's equality and ordering
  behavior.  Per recent -hackers thread, this is noteworthy because
  Postgres behaves differently from most implementations of NaN,
  including IEEE754.

- A patch from Magnus Hagander and Joachim Wieland to
  pgsql/src/tools/msvc/gendef.pl which fixes two problems: gendef
  works from inside visual studio - use a tempfile instead of
  redirection, because for some reason you can't redirect dumpbin from
  inside and gendef must process only *.obj, or you get weird errors
  in some build scenarios when it tries to process a logfile.

- This patch to pgsql/src/tools/msvc/build.bat enables verbose output
  when building all projects. This is the same output level that was
  used when building a single project before, and really needed to get
  reasonable information about what happens (non-verbose just says
  "starting build of foo" and "done building foo", more or less).

ISHII Tatsuo committed:

- Back port patch to versions 8.0, 7.4 and 7.3.  This patch calls
  srandom() instead of srand().  pgbench calls random() later, so it
  should have called srandom().  On most platforms except Windows
  srandom() is actually identical to srand(), so the bug only bites
  Windows users.  per bug report from Akio Ishida.

- In contrib/pgbench, backport patch.  Call srandom() instead of
  srand().  pgbench calls random() later, so it should have called
  srandom().  On most platforms except Windows srandom() is actually
  identical to srand(), so the bug only bites Windows users.  per bug
  report from Akio Ishida.

- In contrib/pgbench, update copyright year.

Bruce Momjian committed:

- Remove completed and no longer wanted items FROM TODO list,
  including "Fix memory leak from exceptions" (done), "Allow
  constraint_exclusion to work for UNIONs like it does for
  inheritance, allow it to work for UPDATE and DELETE statements, and
  allow it to be used for all statements with little performance
  impact" (done), "Add estimated_count(*) to return an estimate of
  COUNT(*)" (no longer wanted)

- Update to_char("CC") description.

- Update error message wording in pgsql/src/backend/parser/analyze.c.

- Update ORDER BY UNION function/exprssion wording (again).

- Update UNION/INTERSECT/EXCEPT ORDER BY error wording for
  expressions/functions.

- Improve error wording of ORDER BY in UNION that uses new expressions
  in ORDER BY.

- Added to TODO: "Add URL item for psql -c changes"
  http://archives.postgresql.org/pgsql-hackers/2007-01/msg00291.php

- Added to TODO: "Fix transaction restriction checks for CREATE
  DATABASE and other commands"
  http://archives.postgresql.org/pgsql-hackers/2007-01/msg00133.php

- Added to TODO: "Add URL for PQexec() for disallowing multiple
  queries"
  http://archives.postgresql.org/pgsql-hackers/2007-01/msg00184.php

- Added to TODO: "Extend timezone code to allow 64-bit values so we
  can represent years beyond 2038."
  http://archives.postgresql.org/pgsql-hackers/2006-09/msg01363.php

- Added to TODO: "Move NAMEDATALEN from postgres_ext.h to
  pg_config_manual.h and consider making it more configurable in
  future releases."

- L Bayuk's patch that allows BCC to compile libpq and psql.

- For pg_ctl -w, add reference to additional environment variables and
  pgpass.

- Remove SGML makefile .SECONDARY tag so html rules will work
  properly; Documentation/comment improvements.

- Improve SGML build rules for non-HTML output, per Peter Eisentraut.

- Update copyright script to allow spaces around dash.

- In SGML Makefile, set proper targets for recursive calls.

- Add "Improve merge join performance by allowing mark/restore of
  tuple sources" to TODO list.

- Michael Fuhr's patch which updates the UTF-8 RFC reference.  RFC
  2044 was obsoleted by RFC 2279, which was obsoleted by RFC 3629.

- Build SGML documention output several times if necessary to have
  proper indexes;  add 'draft' option to disable it.

- Have log_temp_files be in kilobytes, remove trace call.

- Remove trace macro call from new log_temp_files, until it gets more
  research.

- Heikki Linnakangas's patch which enables another five tuple status
  bits by using the high bits of the nattr field, and renames the
  field.

- In TODO, mark as done: "Add ability to monitor the use of temporary
  sort files"

- Bruce Moran's patch which adds a GUC called log_temp_files to log
  the use of temporary files.

- In TODO, mark as done: "Allow the creation of indexes with mixed
  ascending/descending."  Tom Lane's ORDER BY ... NULL FIRST/LAST
  patch implements this.

Tom Lane committed:

- Fix handling of CC (century) format spec in to_date/to_char.
  According to standard convention the 21st century runs from
  2001-2100, not 2000-2099, so make it work like that.  Per bug #2885
  from Akio Iwaasa.  Backpatch to 8.2, but no further, since this is
  really a definitional change; users of older branches are probably
  more interested in stability.

- Add some notes to pgsql/src/backend/access/nbtree/README about the
  basic mathematical laws that the system presumes hold true for
  operators in a btree operator family.  This is mostly to clarify my
  own thinking about what the planner can assume for optimization
  purposes.  (blowing dust off an old abstract-algebra textbook...)

- Fix a performance problem in databases with large numbers of tables
  (or other types of pg_class entry): the function
  pgstat_vacuum_tabstat, invoked during VACUUM startup, had runtime
  proportional to the number of stats table entries times the number
  of pg_class rows; in other words O(N^2) if the stats collector's
  information is reasonably complete.  Replace list searching with a
  hash table to bring it back to O(N) behavior.  Per report from kim
  at myemma.com.  Back-patch as far as 8.1; 8.0 and before use
  different coding here.

- Teach nodeMergejoin how to handle DESC and/or NULLS FIRST sort
  orders.  So far only tested by hacking the planner ...

- Make sure BYTE_ORDER gets defined in 64-bit builds on Solaris, per
  Stefan Kaltenbrunner.

- Change the planner-to-executor API so that the planner tells the
  executor which comparison operators to use for plan nodes involving
  tuple comparison (Agg, Group, Unique, SetOp).  Formerly the executor
  looked up the default equality operator for the datatype, which was
  really pretty shaky, since it's possible that the data being fed to
  the node is sorted according to some nondefault operator class that
  could have an incompatible idea of equality.  The planner knows what
  it has sorted by and therefore can provide the right equality
  operator to use.  Also, this change moves a couple of catalog
  lookups out of the executor and into the planner, which should help
  startup time for pre-planned queries by some small amount.  Modify
  the planner to remove some other cavalier assumptions about always
  being able to use the default operators.  Also add "nulls
  first/last" info to the Plan node for a mergejoin --- neither the
  executor nor the planner can cope yet, but at least the API is in
  place.

- Marginal tweaks in the documentation for ORDER BY; in particular
  point out the common error that ORDER BY x, y DESC does not mean the
  same as ORDER BY x DESC, y DESC.

- In pgsql/src/backend/access/hash/README, add a citation to Seltzer
  and Yigit's Usenix '91 paper about hash table management.  The paper
  clearly describes many of the ideas embodied in our current hashing
  code, but as far as I could find out there is not a direct code
  heritage.  (Mike Olsen recalls discussion of this paper at Postgres
  meetings but believes it "informed the Postgres implementation
  probably just at the design level".  Margo herself says she wasn't
  involved with Postgres' hash code.)  Credit where credit is due 'n
  all that, even if fifteen years after the fact.

- Magnus Hagander's patch to pgsql/src/tools/msvc which fixes vcbuild
  to allow building without OpenSSL and/or zlib.

- In pgsql/src/tools/msvc, add README with vcbuild documentation from
  Magnus Hagander and Dave Page.

- pltcl regression test needs to actually create an opclass, not just
  one operator.

- Support ORDER BY ... NULLS FIRST/LAST, and add ASC/DESC/NULLS
  FIRST/NULLS LAST per-column options for btree indexes.  The
  planner's support for this is still pretty rudimentary; it does not
  yet know how to plan mergejoins with nondefault ordering options.
  The documentation is pretty rudimentary, too.  I'll work on
  improving that stuff later.  Note incompatible change from prior
  behavior: ORDER BY ... USING will now be rejected if the operator is
  not a less-than or greater-than member of some btree opclass.  This
  prevents less-than-sane behavior if an operator that doesn't
  actually define a proper sort ordering is selected.

- Tweak joinlist creation to avoid generating useless one-element
  subproblems when collapsing of JOIN trees is stopped by
  join_collapse_limit.  For instance a list of 11 LEFT JOINs with
  limit 8 now produces something like ((1 2 3 4 5 6 7 8) 9 10 11 12)
  instead of (((1 2 3 4 5 6 7 8) (9)) 10 11 12) The latter structure
  is really only required for a FULL JOIN.  Noted while studying an
  example from Shane Ambler.

- Remove cost_hashjoin's very ancient hack to discourage (once,
  entirely forbid) hash joins with the estimated-larger relation on
  the inside.  There are several cases where doing that makes perfect
  sense, and in cases where it doesn't, the regular cost computation
  really ought to be able to figure that out.  Make some marginal
  tweaks in said computation to try to get results approximating
  reality a bit better.  Per an example from Shane Ambler.  Also, fix
  an oversight in the original patch to add seq_page_cost: the costs
  of spilling a hash join to disk should be scaled by seq_page_cost.

D'Arcy Cain committed:

- Widen the money type to 64 bits.

Peter Eisentraut committed:

- Fix reverse compilation of IS DOCUMENT expression.

- Add support for xmlval IS DOCUMENT expression.

- In pgsql/src/backend/parser/parse_expr.c, fix compiler warning.

- Use XML output escaping also in XMLFOREST.

- Allow for arbitrary data types as content in XMLELEMENT.  The
  original coercion to type xml was a mistake.  Escape values so they
  are valid XML character data.

- Use libxml's xmlwriter API for producing XML elements, instead of
  doing our own printing dance.  This does a better job of quoting and
  escaping the values.

- Prevent duplicate attribute names in XMLELEMENT.

- Some fine-tuning of xmlpi in corner cases: correct error codes, do
  syntax checks in correct order and strip leading spaces of argument.

- Check and document minimum required version of libxml.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Magnus Hagander sent in a patch which fixes vcbuild so you can build
without OpenSSL and libz should you want to.  It disables the sslinfo
and pgcrypto modules because they require the libraries to build at
all.

Michael Fuhr sent in a patch which updates the UTF-8 RFC reference.
RFC 2044 was obsoleted by RFC 2279, which was obsoleted by RFC 3629.

Simon Riggs sent in two versions of a patch which allows you to avoid
writing WAL at all for COPY inside an explicit transaction.

Gurjeet Singh sent in another rev of his Index Advisor patch.

Magnus Hagander sent in a patch which fixes gendef from inside visual
studio.

Magnus Hagander sent in a patch which enables verbose output when
building all projects.  This is the same output level that was used
when building a single project before, and really needed to get
reasonable information about what happens (non-verbose just says
"starting build of foo" and "done building foo", more or less).

Neil Conway sent in a doc patch which explains PostgreSQL's handling
of NaN.

Dave Page sent in a patch which lets pg_dumpall dump roles and
tablespaces.

Pavel Stehule sent in a patch which adds scrollable cursor support to
SPI.