== Postgres Weekly News - March 16 2008 == - Mailing list pgsql-announce

== Postgres Weekly News - March 16 2008 ==

ITPUG will meet to plan PGDay.IT, which is happening October 17 and
18, 2008 in Prato.  The meeting will be in Italian and will be on
Tuesday, March 18 at 2130 CET via IRC.
irc://irc.freenode.net/pgday-it

United States PostgreSQL Association launched.
http://www.postgresql.us

Dave Page has set up a user documentation wiki.
http://wiki.postgresql.org/

== Postgres Product News ==

Italian full-text search for PostgreSQL 8.3 released.
http://www.develer.com/~piro/ispell-italian/italian-fts.html

AM Lightning Debugger Client 1.0 released.
http://www.amsoftwaredesign.com

Devrim GUNDUZ has uploaded a new livecd with updates to PostgreSQL
8.3.1, plruby and ptop, now including pg_filedump, ruby-pg and
pgbouncer.
http://yum.pgsqlrpms.org/livecd.php

OpenRPT 2.3.0 released.
http://pgfoundry.org/projects/openrpt/

pgloader 2.3.0 released.
http://pgfoundry.org/projects/pgloader/

PgWorksheet 1.9 released.
http://pgfoundry.org/projects/pgworksheet/

ptop 3.6.2-beta1 released.
http://ptop.projects.postgresql.org/

SE-PostgreSQL v8.3 released.
http://download.fedora.redhat.com/pub/fedora/linux/development

== Postgres Jobs for March ==

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

== Postgres Local ==

Atlanta PUG's first meeting will be March 18, 6:30pm.
http://pugs.postgresql.org/node/366

Jon Asher will talk about PostORM at SFPUG on March 18 at 7:30pm.
http://postgresql.meetup.com/1/calendar/7321956/

Morocco PUG's first meeting will be March 22, 9:00am in Tangier.
http://pugs.postgresql.org/moroccopug

LAPUG will be meeting March 28, 7:00pm in the City of Garden Grove
Training Room.
http://pugs.postgresql.org/lapug

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

PG UK day will be April 2 in Birmingham.
http://www.postgresql.org.uk/

FISL 9.0 will be April 17-19 at PUCRS in Porto Alegre, RS, Brazil.
https://fisl.softwarelivre.org/9.0/

PGCon 2008 will be May 20-23 in Ottawa.
http://www.pgcon.org/2008/

Utah Open Source Conference 2008's CfP is open through June 1.
This 2nd annual conference is August 28-30, 2008 in Salt Lake City, UT
http://2008.utosc.com/

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

Tom Lane committed:

- In pgsql/contrib/pgbench/pgbench.c, fix pgbench's getrand() function
  so that min and max have approximately the same chance of being
  selected as do numbers between them.  Problem noted by Greg Stark;
  fix by Alexey Klyukin.

- Reduce memory consumption during VACUUM of large relations, by using
  FSMPageData (6 bytes) instead of PageFreeSpaceInfo (8 or 16 bytes)
  for the temporary array of page-free-space information.  Itagaki
  Takahiro.

- In pgsql/src/backend/access/transam/xlog.c, remove no-longer-used
  XLogCacheByte field of XLogCtl.  Itagaki Takahiro.

- Add a CaseSensitive parameter to synonym dictionaries.  Simon Riggs.

- Increase the default value of log_min_messages to WARNING, so that
  NOTICE-grade messages are not logged by default.  Per pgsql-hackers
  discussion back on 21-Nov-2007.

- Throw an error for negative LIMIT or OFFSET values, instead of
  silently treating them as zero.  Simon Riggs

- Document and enforce that the usable range of setseed() arguments is
  -1 to 1, not 0 to 1.  The actual behavior for values within this
  range does not change.  Kris Jurka

- In pgsql/src/backend/utils/adt/tsvector.c, fix unportable coding of
  new error message, per Kris Jurka.

- Provide a build-time option to store large relations as single
  files, rather than dividing them into 1GB segments as has been our
  longtime practice.  This requires working support for large files in
  the operating system; at least for the time being, it won't be the
  default.  Zdenek Kotala

- Use -fwrapv in CFLAGS if we are using a version of gcc that accepts
  this flag.  This prevents compiler optimizations that assume
  overflow won't occur, which breaks numerous overflow tests that we
  need to have working.  It is known that gcc 4.3 causes problems and
  possible that 4.1 does.  Per my proposal of some time ago and a
  recent report from Kris Jurka.  Backpatch as far as 8.0, which is as
  far as the patch conveniently goes.  7.x was pretty short of
  overflow tests anyway, so it may not matter there, even assuming
  that anyone cares whether 7.x builds on recent gcc.

- Make TransactionIdIsInProgress check transam.c's single-item XID
  status cache before it goes groveling through the ProcArray.  In
  situations where the same recently-committed transaction ID is
  checked repeatedly by tqual.c, this saves a lot of shared-memory
  searches.  And it's cheap enough that it shouldn't hurt noticeably
  when it doesn't help.  Concept and patch by Simon, some minor
  tweaking and comment-cleanup by Tom.

- In pgsql/contrib/pgbench/pgbench.c, add a comment explaining one of
  the ways that pgbench fails to fully comply with TPC-B.  Per Itagaki
  Takahiro and discussion of bug#3681.

- Fix LISTEN/NOTIFY race condition reported by Laurent Birtz, by
  postponing pg_listener modifications commanded by LISTEN and
  UNLISTEN until the end of the current transaction.  This allows us
  to hold the ExclusiveLock on pg_listener until after commit, with no
  greater risk of deadlock than there was before.  Aside from fixing
  the race condition, this gets rid of a truly ugly kludge that was
  there before, namely having to ignore HeapTupleBeingUpdated failures
  during NOTIFY.  There is a small potential incompatibility, which is
  that if a transaction issues LISTEN or UNLISTEN and then looks into
  pg_listener before committing, it won't see any resulting row
  insertion or deletion, where before it would have.  It seems
  unlikely that anyone would be depending on that, though.  This patch
  also disallows LISTEN and UNLISTEN inside a prepared transaction.
  That case had some pretty undesirable properties already, such as
  possibly allowing pg_listener entries to be made for PIDs no longer
  present, so disallowing it seems like a better idea than trying to
  maintain the behavior.

- In pgsql/src/backend/tcop/postgres.c, fix pg_plan_queries() to
  restore the previous setting of ActiveSnapshot (probably NULL)
  before exiting.  Up to now it's just left the variable as it set it,
  which means that after we're done processing the current client
  message, ActiveSnapshot is probably pointing at garbage (because
  this function is typically run in MessageContext which will get
  reset).  There doesn't seem to have been any code path in which that
  mattered before 8.3, but now the plancache module might try to use
  the stale value if the next client message is a Bind for a prepared
  statement that is in need of replanning.  Per report from Alex
  Hunsaker.

- Fix heap_page_prune's problem with failing to send cache
  invalidation messages if the calling transaction aborts later on.
  Collapsing out line pointer redirects is a done deal as soon as we
  complete the page update, so syscache *must* be notified even if the
  VACUUM FULL as a whole doesn't complete.  To fix, add some
  functionality to inval.c to allow the pending inval messages to be
  sent immediately while heap_page_prune is still running.  The
  implementation is a bit chintzy: it will only work in the context of
  VACUUM FULL.  But that's all we need now, and it can always be
  extended later if needed.  Per my trouble report of a week ago.

- In pgsql/src/backend/utils/adt/varlena.c, fix varstr_cmp's special
  case for UTF8 encoding on Windows so that strings that are reported
  as "equal" by wcscoll() are checked to see if they really are
  bitwise equal, and are sorted per strcmp() if not.  We made this
  happen a couple of years ago in the regular code path, but it
  unaccountably got left out of the Windows/UTF8 case (probably brain
  fade on my part at the time).  As in the prior set of changes,
  affected users may need to reindex indexes on textual columns.
  Backpatch as far as 8.2, which is the oldest release we are still
  supporting on Windows.

- Update to tzdata 2008a distribution (Chilean DST law change).

- In pgsql/doc/src/sgml/release.sgml, update release notes for 8.3.1
  and 8.2.7 releases.

- Stamp version 8.3.1, except for configure.in/configure.

- Stamp version 8.2.7, except for configure.in/configure.

- In pgsql/src/backend/postmaster/autovacuum.c, fix inappropriately-timed
  memory context switch in autovacuum_do_vac_analyze.  This accidentally
  failed to fail before 8.3, because the context we were switching back
  to was long-lived anyway; but it sure looks risky as can be now.
  Well spotted by Pavan Deolasee.

- Change hash index creation so that rather than always establishing
  exactly two buckets at the start, we create a number of buckets
  appropriate for the estimated size of the table.  This avoids a lot
  of expensive bucket-split actions during initial index build on an
  already-populated table.  This is one of the two core ideas of Tom
  Raney and Shreya Bhargava's patch to reduce hash index build time.
  I'm committing it separately to make it easier for people to test
  the effects of this separately from the effects of their other core
  idea (pre-sorting the index entries by bucket number).

Magnus Hagander committed:

- Implement enum type for guc parameters, and convert a couple of
  existing variables to it. More need to be converted, but I wanted to
  get this in before it conflicts with too much...  Other than just
  centralising the text-to-int conversion for parameters, this allows
  the pg_settings view to contain a list of available options and
  allows an error hint to show what values are allowed.

- Bump catversion from guc enum patch, which I forgot. Sorry!

- Some cleanups of enum-guc code, per comments from Tom.

Bruce Momjian committed:

- Add to TODO: "Consider increasing the minimum allowed number of
  shared buffers."

- Add URL for TODO: "Allow administrators to safely terminate
  individual sessions either via an SQL function or SIGTERM."

- Add URL for TODO: "Optimize referential integrity checks."

- Add to TODO: "Set up autovacuum to ignore statement_timeout set in
  postgresql.conf."

- In pgsql/doc/src/sgml/config.sgml, document that statement_timeout
  is not recommended in postgresql.conf because it affects all
  sessions, including autovacuum.

- Add to TODO: "Consider adding buffers the BGW finds reusable to the
  free list" and "Automatically tune bgwriter_delay based on activity
  rather then using a fixed interval."

- Remove from TODO: "Set up autovacuum to ignore statement_timeout set
  in postgresql.conf." per Alvaro Herrera.

- In pgsql/doc/src/sgml/config.sgml, back out doc addition that
  statement_timeout affects autovacuum.

- Add to TODO: "Allow statistics last vacuum/analyze execution times
  to be displayed without requiring stats_row_level to be enabled."

- In pgsql/doc/src/sgml/config.sgml, add "Setting statement_timeout in
  postgresql.conf is not recommended because it affects all sessions."
  Backpatch to 8.3.X.

- Add to TODO: "Prevent pg_dump/pg_restore from being affected by
  statement_timeout.  Using psql to restore a pg_dump dump is also
  affected."

- Add to TODO for PL/PgSQL: "Review handling of MOVE and FETCH."

- Add to TODO: "Add temporal versions of generate_series()."

- Add URL for TODO: "Fix problem when multiple subtransactions of the
  same outer transaction hold different types of locks, and one
  subtransaction aborts."

- Add to TODO for VACUUM: "Consider a more compact data representation
  for dead tuples."

- Add to TODO: "Consider using a hash for joining to a large IN
  (VALUES ...) list."

- Add to TODO: "Consider increasing the number of default statistics
  target, and reduce statistics target overhead.  Also consider having
  a larger statistics target for indexed columns and expression
  indexes."

- Add URL for TODO: "Consider increasing the number of default
  statistics target, and reduce statistics target overhead.  Also
  consider having a larger statistics target for indexed columns and
  expression indexes."

- Add another URL for TODO: "Consider increasing the number of default
  statistics target, and reduce statistics target overhead.  Also
  consider having a larger statistics target for indexed columns and
  expression indexes."

- Add to TODO: "Change memory allocation for multi-byte functions so
  memory is allocated inside conversion functions."

- Add to TODO: "Prefix command-line utilities like createuser with
  'pg_'."

- Add to TODO: "Consider sorting writes during checkpoint."

- Add to TODO: "Consider normalizing fractions in postgresql.conf,
  perhaps using '%'."

- Add URL for TODO: "Reduce storage space for small NUMERICs."

- Update TODO to read: "Consider adding buffers the background writer
  finds reusable to the free list.  Consider whether increasing
  BM_MAX_USAGE_COUNT improves performance."

- Add to TODO: "Test to see if calling PreallocXlogFiles() from the
  background writer will help with WAL segment creation latency."

- Add to TODO: "Research use of signals and sleep wake ups."

- Add to TODO: "Add automated check for invalid C++ source code
  constructs."

- Add to TODO for PL/PgSQL: "Improve logic of determining if an
  identifier is a variable or column name."

- Add to TODO for COPY: "Allow COPY in CSV mode to control whether ""
  is treated as NULL."

- Update the above TODO to read: "Allow COPY in CSV mode to control
  whether a quoted zero-length string is treated as NULL.  Currently
  this is always treated as a zero-length string, which generates an
  error when loading into an integer column."

- In pgsql/src/bin/psql/copy.c, prevent psql \copy from accepting
  duplicate string parameters.  Per report from Stephen Frost.

- Add URL for TODO: "Consider increasing NUM_CLOG_BUFFERS."

- Add URL for TODO: "Allow user configuration of TOAST thresholds."

- Add to TODO: "Consider simplifying how memory context resets handle
  child contexts."

- Add to TODO: "Allow domains to be cast."

- Add URL for TODO: "Improve speed with indexes.  For large table
  adjustments during VACUUM FULL, it is faster to cluster or reindex
  rather than update the index.  Also, index updates can bloat the
  index."

- Add to TODO: "Add array_accum() and array_to_set() functions for
  arrays."

- Add to TODO: "Remove use of MAKE_PTR and MAKE_OFFSET macros."

- Add to TODO for Win32: "Diagnose problem where shared memory can
  sometimes not be attached by postmaster children."

- Add URL for TODO: "Add REINDEX CONCURRENTLY, like CREATE INDEX
  CONCURRENTLY."

- Add to TODO for Win32: "Convert MSVC build system to remove most
  batch files."

- Add URL for TODO: "Consider a simplified API for full text
  searches."

- Update TODO: "Add array_accum() and array_to_set() functions for
  arrays" add "The standards specify array_agg() and UNNEST."

- Back out text search change to TODO.

- Add to TODO: "Consider a special data type for regular expressions."

- Add to TODO: "Expire published xmin for read-only and idle
  transactions."

- Add to TODO: "Include the symbolic SQLSTATE name in verbose error
  reports."

- Add URL for TODO: "Do async I/O for faster random read-ahead of
  data.  Async I/O allows multiple I/O requests to be sent to the disk
  with results coming back asynchronously."

Marc Fournier commited:

- Update configure for 8.3.1 prior to tag.

- Update configure for 8.2.7 prior to tag.

Peter Eisentraut committed:

- Translation updates.

Alvaro Herrera committed:

- Fix vacuum so that autovacuum is really not cancelled when doing an
  emergency job (i.e. to prevent Xid wraparound problems.)  Bug
  reported by ITAGAKI Takahiro in 20080314103837.63D3.52131E4D@oss.ntt.co.jp,
  though I didn't use his patch.

- In pgsql/doc/src/sgml/install-win32.sgml, fix duplicate word, per
  Guillaume Lelarge.

- Modify interactions between sinval.c and sinvaladt.c.  The code that
  actually deals with the queue, including locking etc, is all in
  sinvaladt.c.  This means that the struct definition of the queue,
  and the queue pointer, are now internal "implementation details"
  inside sinvaladt.c.  Per my proposal dated 25-Jun-2007 and followup
  discussion.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Zoltan Boszormenyi sent in a WIP patch to allow 64-bit integers and
floats to get passed by value on 64-bit platforms, and to change
32-bit floats to be passed by value along the way.

Heikki Linnakangas sent in two revisions of a patch which converts
some linked lists into expandable arrays.

Bruce Momjian sent in a patch which rejects conflicting options given
to COPY...CSV in psql.

ITAGAKI Takahiro sent in a patch intended to suppress compiler
warnings in the mingw build.