== PostgreSQL Weekly News - January 14 2007 ==

== 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(at)postgresql(dot)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(at)fetter(dot)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.