== PostgreSQL Weekly News - November 12 2006 == - Mailing list pgsql-announce

== PostgreSQL Weekly News - November 12 2006 ==

PostgreSQL 8.2 beta3 released.  Test :)

Simon Riggs started a thread on -hackers about how to address the "hot
tables" issues.  He also put forth a design proposal.  Discussion
continues.

Lorenzo Alberton has put together a "cheat sheet" for PostgreSQL.
http://www.alberton.info/postgresql_cheat_sheet.html

== PostgreSQL Product News ==

Nairo, www.nairo.eu, is a browser game system that runs on Apache, PHP4
and PostgreSQL 8.1.x or later.  This NairoGMS project is designed to
help people create their own browser games.  Watch the pgfoundry site
for updates.
http://pgfoundry.org/projects/nairogms/

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

pgAdmin III v1.6 Release Candidate 2 released.
http://www.pgadmin.org/download/

MicroOLAP PostgresDAC 2.3.6 released.
http://microolap.com/products/connectivity/postgresdac/

Continuent.org's Sequoia 3.0 Beta released.
http://www.continuent.com/index.php?option=com_content&task=view&id=348&Itemid=88

== PostgreSQL Jobs for November ==

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

== PostgreSQL Local ==

Everything this week was global.

== 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
and Dave Page.

== Applied Patches ==

Tom Lane committed:

- Suppress a few 'uninitialized variable' warnings that gcc emits only
  at -O3 or higher (presumably because it inlines more things).  Per
  gripe from Mark Mielke.

- Fix pg_get_serial_sequence(), which could incorrectly return the
  name of an index on a serial column, rather than the name of the
  associated sequence.  Fallout from recent changes in dependency
  setup for serials.  Per bug #2732 from Basil Evseenko.

- Clean up some references which wrongly described %p as being a full
  path, per Simon Riggs.

- Improve formatting of PQexecParams argument descriptions, per Theo
  Kramer.

- In create_function.sgml, emphasize that immutable and stable
  functions are not allowed to modify the database.  xfunc.sgml was
  already pretty definite on the point, but it doesn't hurt to make it
  clear here too.

- Fix errors in key_column_usage.position_in_unique_constraint column
  recently added to information_schema (per a SQL2003 addition).  The
  original coding failed if a referenced column participated in more
  than one pg_constraint entry.  Also, it did not work if an FK relied
  directly on a unique index without any constraint syntactic sugar.
  This case is outside the SQL spec, but PG has always supported it,
  so it's reasonable for our information_schema to handle it too.  Per
  bug#2750 from Stephen Haberman.

- Fix set_joinrel_size_estimates() to estimate outer-join sizes more
  accurately: we have to distinguish the effects of the join's own ON
  clauses from the effects of pushed-down clauses.  Failing to do so
  was a quick hack long ago, but it's time to be smarter.  Per example
  from Thomas Hallgren.

- Change Windows rename and unlink substitutes so that they time out
  after 30 seconds instead of retrying forever.  Also modify xlog.c so
  that if it fails to rename an old xlog segment up to a future slot,
  it will unlink the segment instead.  Per discussion of bug #2712, in
  which it became apparent that Windows can handle unlinking a file
  that's being held open, but not renaming it.

- Modify aset.c to track the next intended block allocation size
  explicitly.  The former coding relied on the actual allocated size
  of the last block, which made it behave strangely if the first
  allocation in a context was larger than ALLOC_CHUNK_LIMIT:
  subsequent allocations would be referenced to that and not to the
  intended series of block sizes.  Noted while studying a memory
  wastage gripe from Tatsuo Ishii.

- Tweak accumArrayResult() to double the size of its working arrays
  when more space is needed, instead of incrementing by a fixed
  amount; the old method wastes lots of space and time when the
  ultimate size is large.  Per gripe from Tatsuo Ishii.

- Fix performance issues in replace_text(), replace_text_regexp(), and
  text_to_array(): they all had O(N^2) behavior on long input strings
  in multibyte encodings, because of repeated rescanning of the input
  text to identify substrings whose positions/lengths were computed in
  characters instead of bytes.  Fix by tracking the current source
  position as a char pointer as well as a character-count.  Also avoid
  some unnecessary palloc operations.  text_to_array() also leaked
  memory intracall due to failure to pfree temporary strings.  Per
  gripe from Tatsuo Ishii.

- Repair bug #2694 concerning an ARRAY[] construct whose inputs are
  empty sub-arrays.  Per discussion, if all inputs are empty arrays
  then result must be an empty array too, whereas a mix of empty and
  nonempty arrays should (and already did) draw an error.  In the back
  branches, the construct was strict: any NULL input immediately
  yielded a NULL output; so I left that behavior alone.  HEAD was
  simply ignoring NULL sub-arrays, which doesn't seem very sensible.
  For lack of a better idea it now treats NULL sub-arrays the same as
  empty ones.

- Remove temporary Windows-specific debugging code; it seems the
  problem with fopen() not using FILE_SHARE_DELETE was indeed the bug
  we were after, given lack of recent reports.

- Update release notes in preparation for RC1.

- Revert to the pre-8.2 method of probing for libm, that is, always
  include it if it links properly.  It seems too risky to assume that
  standard functions like pow() are not special-cased by the compiler.
  Per report from Andreas Lange that build fails on Solaris cc
  compiler with -fast.  Even though we don't consider that a supported
  option, I'm worried that similar issues will arise with other
  compilers.

- Get rid of some unnecessary dependencies on DataDir: wherever
  possible, the backend should rely on its working-directory setting
  instead.  Also do some message-style police work in contrib/adminpack.

- Back-patched PITR recovery fix (below) to 8.1.  Arguably this should
  be fixed in 8.0 too, but it would require significantly more code
  since 8.0 has no handy startup-time scan of pg_database to piggyback
  on.  Manual solution of the problem is possible in 8.0 (just delete
  the pg_internal.init files before starting WAL replay), so that may
  be a sufficient answer.

- Fix recently-identified PITR recovery hazard: the base backup could
  contain stale relcache init files (pg_internal.init), and there is
  no mechanism for updating them during WAL replay.  Easiest solution
  is just to delete the init files at conclusion of startup, and let
  the first backend started in each database take care of rebuilding
  the init file.  Simon Riggs and Tom Lane.

- Fix recently-understood problems with handling of XID freezing,
  particularly in PITR scenarios.  We now WAL-log the replacement of
  old XIDs with FrozenTransactionId, so that such replacement is
  guaranteed to propagate to PITR slave databases.  Also, rather than
  relying on hint-bit updates to be preserved, pg_clog is not
  truncated until all instances of an XID are known to have been
  replaced by FrozenTransactionId.  Add new GUC variables and
  pg_autovacuum columns to allow management of the freezing policy, so
  that users can trade off the size of pg_clog against the amount of
  freezing work done.  Revise the already-existing code that forces
  autovacuum of tables approaching the wraparound point to make it
  more bulletproof; also, revise the autovacuum logic so that
  anti-wraparound vacuuming is done per-table rather than
  per-database.  initdb forced because of changes in pg_class,
  pg_database, and pg_autovacuum catalogs.  Heikki Linnakangas, Simon
  Riggs, and Tom Lane.

Neil Conway committed:

- In GIN code, fix some typos in comments.

- Do a round of copy-editing for the system catalog chapter.  In
  particular, consistently capitalize the content of the "Description"
  column but do not include a terminating period, as is the convention
  elsewhere in the docs.  Also, remove the "References" column from
  catalog that do not have any referencing columns, for the sake of
  brevity.  Make various other SGML and grammar fixes.

- Minor improvements to the description of archive_timeout.

- Minor code cleanup for pgcrypto: for UDFs declared to be strict,
  checking for NULL-ness of function arguments is wasted code.

- Fix two typos in the tsearch2 README.

- Fix a memory leak in psql: we'd leak a few PGresult handles if a
  connectivity error occurred while executing one of the queries for
  "\d <table>".  Not serious, but still worth fixing.  Patch from
  Brendan Jurd.

- Remove a 15-year old comment questioning behavior that is now well-
  established: referencing an undefined parameter should result in an
  error, not NULL.

- Minor fix for LDAP authentication: if an error occurs, we need to
  manually release the LDAP handle via ldap_unbind().  This isn't a
  significant problem in practice because an error eventually results
  in exiting the process, but we can cleanup correctly without too
  much pain.  In passing, fix an error in snprintf() usage: the "size"
  parameter to snprintf() is the size of the destination buffer,
  including space for the NUL terminator.  Also, depending on the
  value of NAMEDATALEN, the old coding could have allowed for a buffer
  overflow.

Michael Meskes committed:

- In ecpg, applied patch by Peter Harris to free auto_mem structure on
  connect.

Peter Sigaev committed:

- New README for tsearch2.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Andrew Dunstan submitted a patch, the a follow-up patch, which is
designed to work around a potential problem where modules loaded by
PL/PerlU are visible to PL/Perl code.

Brendan Jurd sent in a patch which differentiates deactivated triggers
from activated ones in psql's \d.

Heikki Linnakangas sent in a patch destined for 8.3 intended to
optimize read access to CLUSTERed tables with narrow columns.

Brendan Jurd sent in a patch which changes Guillaume Lelarge's patch
to use IYYY and IDDD for ISO year and week formatting, respectively.

Magnus Hagander sent in a patch which updates the mingw FAQ for
current realities.