== PostgreSQL Weekly News - January 21 2007 ==

== PostgreSQL Weekly News - January 21 2007 ==

After a misunderstanding about their practice of harvesting email
addresses from PostgreSQL mailing lists and sending advertising emails
to those addresses, EnterpriseDB was kind enough to change their
policy on this.

== PostgreSQL Product News ==

Apgdiff 1.0.0beta15 released.
http://pgfoundry.org/projects/apgdiff/

Pgtclng 1.6.0 released.
http://pgfoundry.org/projects/pgtclng/

postgresql-dbi-link 2.0beta1 is now in Fedora Core Extras 7, soon to
appear in Fedora Core Extras 6.
http://people.planetpostgresql.org/devrim/index.php?/archives/78-Pushed-dbi-link-into-Fedora-Extras.html

PostGIS is now in Fedora Core Extras.
http://people.planetpostgresql.org/devrim/index.php?/archives/79-PostGIS-is-in-Fedora-Core-Extras.html

phpPgAdmin 4.1 released. RPMs for Fedora Core 5, 6 and 7 are
available.
http://phppgadmin.sourceforge.net/?page=download

EnterpriseDB is now offering support packages for the main branch of
PostgreSQL.
http://www.enterprisedb.com/products/postgre_pricing.do

pgAdmin3 1.6.2 released.
http://www.pgadmin.org/download/

== PostgreSQL Jobs for January ==

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

== PostgreSQL Local ==

Thanks to Gavin Sherry for running the PostgreSQL Miniconf at
Linuxconf.au.
http://www.gavinsherry.org/blog/index.php?/archives/1-PostgreSQL-miniconf-success.html

PostgreSQLFr will have a booth at SolutionsLinux 2007, in Paris
(France) from 30th january to 1st February. Jean-Paul Argudo,
treasurer of the PostgreSQLFr non-profit assocation, will lead a
session about FLOSS databases. More information in French here:
http://www.solutionslinux.fr/index.php

Austria has a new PostgreSQL community site.
http://community.postgresql.at/

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/

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

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

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

Peter Eisentraut committed:

- Remove some unused variables from the ecpg regression tests.

- In pgsql/src/interfaces/ecpg/test/Makefile.regress, put back libpq
link, but make sure that libpq's in-tree directory is mentioned
before the system directories.

- Update build time estimate.

- Remove remains of old depend target.

- The libpq library directory was mentioned here in the wrong place,
which might lead to a previously installed libpq being used instead.
But we don't actually have to link with libpq here at all, so remove
it.

- Only build ecpg test code when the test is to be performed, in line
with other test code.

- Fix xmlconcat regression test.

- Fix xmlconcat by properly merging the XML declarations. Add
aggregate function xmlagg.

- Add support for converting binary values (i.e. bytea) into xml
values, with new GUC parameter "xmlbinary" that controls the output
encoding, as per SQL/XML standard.

- Optionally use xml2-config to detect installation locations of
libxml.

- Clean up encoding issues in the xml type: In text mode, encoding
declarations are ignored and removed, in binary mode they are
honored as specified by the XML standard.

Bruce Momjian committed:

- Update documenation instructions on how to delete template1.

- Add URL for bitmap index to TODO.

- Add URL for dead space map to TODO.

- Add documentation of memory and time units to postgresql.conf.
Backpatch to 8.2.X for new initdbs.

- Update documentation about postgresqlconf to mention default units
that match the postgresql.conf file. Also add units to descriptions
that lacked them. Wording improvements. Mention pg_settings.unit
as the way to find the default units for setting. Backpatch to
8.2.X.

- Modify max_fsm_pages in postgresql.conf.sample to show a typical
value, rather than a value too high.

- Document that to remove template1, datistemplate must be 'false'.
Backpatch to 8.2.

- Move code that places LOG error level between ERROR and PANIC into
new function is_log_level_output(), for code clarity.

- Added developer wiki URL to TODO.

- Added to TODO: "Allow multiple vacuums so large tables do not starve
small tables," and "Improve control of auto-vacuum."

- Add to TODO URL for GUIDs/UUIDs (Globally/Universally Unique
Identifiers)

- Mark TODO item "autovacuum on by default" as completed.

Tom Lane committed:

- Adjust pgbench so it won't spit up on non-select queries returning
tuples, which is entirely possible with custom scripts (consider
RETURNING, EXPLAIN, etc).

- Add COST and ROWS options to CREATE/ALTER FUNCTION, plus underlying
pg_proc columns procost and prorows, to allow simple user adjustment
of the estimated cost of a function call, as well as control of the
estimated number of rows returned by a set-returning function. We
might eventually wish to extend this to allow function-specific
estimation routines, but there seems to be consensus that we should
try a simple constant estimate first. In particular this provides a
relatively simple way to control the order in which different WHERE
clauses are applied in a plan node, which is a Good Thing in view of
the fact that the recent EquivalenceClass planner rewrite made that
much less predictable than before.

- Refactor some lsyscache routines to eliminate duplicate code and
save a couple of syscache lookups in make_pathkey_from_sortinfo().

- Simplify pg_am representation of ordering-capable access methods:
provide just a boolean 'amcanorder', instead of fields that specify
the sort operator strategy numbers. We have decided to require
ordering-capable AMs to use btree-compatible strategy numbers, so
the old fields are overkill (and indeed misleading about what's
allowed).

- In pgsql/src/backend/utils/error/elog.c, Don't use 'int' where you mean 'bool'.

- Fix broken markup in pgsql/doc/src/sgml/installation.sgml.

- Refactor planner's pathkeys data structure to create a separate,
explicit representation of equivalence classes of variables.

- Add a note pointing out that is_pseudo_constant_clause() doesn't
check for aggregates. This is OK for current uses but could burn
somebody someday...

- Extend yesterday's patch so that the bgwriter is also told to forget
pending fsyncs during DROP DATABASE. Obviously necessary in
hindsight :-(

- Revise bgwriter fsync-request mechanism to improve robustness when a
table is deleted. A backend about to unlink a file now sends a
"revoke fsync" request to the bgwriter to make it clean out pending
fsync requests. There is still a race condition where the bgwriter
may try to fsync after the unlink has happened, but we can resolve
that by rechecking the fsync request queue to see if a revoke
request arrived meanwhile. This eliminates the former kluge of
"just assuming" that an ENOENT failure is okay, and lets us handle
the fact that on Windows it might be EACCES too without introducing
any questionable assumptions. After an idea of mine improved by
Magnus. The HEAD patch doesn't apply cleanly to 8.2, but I'll see
about a back-port later. In the meantime this could do with some
testing on Windows; I've been able to force it through the code path
via ENOENT, but that doesn't prove that it actually fixes the
Windows problem ...

- Fix incorrect permissions check in
information_schema.key_column_usage view: it was checking a
pg_constraint OID instead of pg_class OID, resulting in "relation
with OID nnnnn does not exist" failures for anyone who wasn't owner
of the table being examined. Per bug #2848 from Laurence Rowe.
Note: for existing 8.2 installations a simple version update won't
fix this; the easiest fix is to CREATE OR REPLACE this view with the
corrected definition.

Alvaro Herrera committed:

- Fix pg_regress breakage for PL and contrib tests, by not requiring
"input" and "output" directories to be present.

- Magnus Hagander's patch, with some editorializing, which changes the
sed rules in the regression test for pg_regress hackery to create
the generated files, to help Visual C++ to run these tests. The
tests still pass in VPATH and normal builds.

- Enable autovacuum in the default configuration, per discussion.

- Arrange for autovacuum to be killed when another operation wants to
be alone accessing it, like DROP DATABASE. This allows the
regression tests to pass with autovacuum enabled, which open the
gates for finally enabling autovacuum by default.

Neil Conway committed:

- Update docs for recent change to setseed() return value.

- Make setseed() return void, rather than an int4 without any use. Per
pgsql-patches discussion of September 20, 2006. Bump the catversion.

- List disabled triggers separately in psql's "\d <table>" output.
Previously, disabled triggers were not displayed any differently
than enabled ones, which was quite misleading. Patch from Brendan
Jurd.

- Refactor the index AM API slightly: move currentItemData and
currentMarkData from IndexScanDesc to the opaque structs for the AMs
that need this information (currently gist and hash). Patch from
Heikki Linnakangas, fixes by Neil Conway.

- Regression tests for large objects. Patch from Jeremy Drake.

- Teach psql's \lo slash commands to respect quiet mode, and to output
HTML in HTML mode. Patch from Jeremy Drake.

- Add missing copyright blurb, make ruleutils.c use the standard
layout for its header comment.

- Tweak the width_bucket() regression tests to avoid an unnecessary
dependency on the platform's floating point implementation. Per
report from Stefan Kaltenbrunner.

- vcbuild updates from Magnus Hagander: after Markos patch, now builds
pgcrypto without zlib again, updates README with xml info, xml
requires xslt and iconv, disable unnecessary warning about
__cdecl(), Add a buildenv.bat called from all other bat files to set
up things like PATH for flex/bison. (Can't just set it before
calling, doesn't always work when building from the GUI).

- Implement width_bucket() for the float8 data type.

- Minor improvements to the TODO list. Add some URLs for some existing
TODO items, and re-add a TODO item for an "estimated count"
capability.

- Correct a minor inaccuracy in the pg_dumpall reference page: -g
dumps roles and tablespaces, no longer users and groups. Per Dave
Page. Backport to 8.2 and 8.1.

Teodor Sigaev committed:

- Fix localization support for multibyte encoding and C locale.
Slightly reworked patch from Tatsuo Ishii

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Greg Sabino Mullane sent in a patch which allows pg_dump to
pretty-print DDL for system objects.

Gurjeet Singh sent in new versions of his pg_post_planner_plugin and
pg_index_adviser patches, this time vs. CVS HEAD.

Magnus Hagander sent in a patch to the vcbuild script which builds
pgcrypto without zlib again, updates README with xml info, checks
xml's dependency on xslt and iconv, disables and unnecessary warning
about __cdecl() and adds buildenv.bat called from all other bat files
to set up things like PATH for flex/bison. (Can't just set it before
calling, doesn't always work when building from the GUI)

Dave Page sent in a patch which adds -f (--file=FILENAME) and -l
(--default-database=) to pg_dumpall.

ITAGAKI Takahiro sent in a patch which adds O_DIRECT support for
Windows.

Arul Shaji sent in a patch which implements pg_get_domaindef(oid).

Gevik Babakhani re-sent his patch implementing the UUID data type.