== Postgres Weekly News - October 28 2007 ==

== Postgres Weekly News - October 28 2007 ==

8.3 beta 2 will be out soon thanks to your testing.  Get ready to test
some more!

== Postgres Product News ==

MicroOLAP Database Designer 1.2.2 for PostgreSQL released.
http://microolap.com/products/database/postgresql-designer/

Npgsql 1.0.1 released.
http://pgfoundry.org/projects/npgsql/

Debian now has orafce packages.
http://pgfoundry.org/projects/orafce/

PgBouncer 1.1.1 bugfix release is out.
http://pgfoundry.org/projects/pgbouncer/

pgpool-II 1.3 released.
http://pgfoundry.org/projects/pgpool/

== Postgres Jobs for October ==

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

== Postgres Local ==

New Zealand PUG's first meeting will take place in Wellington
November 6, 2007.  Details below.
http://pgug.org.nz

Check out the German-language Postgres job board.
http://www.pg-forum.de/jobboerse-marktplatz/


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

Peter Eisentraut committed:

- Translation updates.

Bruce Momjian committed:

- Update release notes to current CVS.

- Add plpgsql doc example of RETURN NEXT.  Ulrich Kroener

- Set Japanese FAQ character code to UTF-8.  Jun Kuwamura

- Remove use of "Postgres" aliases in the FAQ in second-in-paragraph
  cases.

- Remove second-in-paragraph usage of "Postgres" from FAQ_DEV.

Alvaro Herrera committed:

- Minor changes to autovacuum worker: change error handling so that it
  continues with the next table on schedule instead of exiting, in all
  cases instead of just on query cancel.  Add a errcontext() line
  indicating the activity of the worker to the error message when it
  is cancelled.  Change the WorkerInfo struct to contain a pointer to
  the worker's PGPROC instead of just the PID.  Add forgotten
  post-auth delays, per Simon Riggs.  Also to autovac launcher.

- In pgsql/src/bin/initdb/initdb.c, change Danish_Danmark to
  Danish_Denmark.

- Rearrange vacuum-related bits in PGPROC as a bitmask, to better
  support having several of them.  Add two more flags: whether the
  process is executing an ANALYZE, and whether a vacuum is for Xid
  wraparound (which is obviously only set by autovacuum).  Sneakily
  move the worker's recently-acquired PostAuthDelay to a more useful
  place.

- In pgsql/src/port/chklocale.c, add new encoding aliases for Solaris,
  per Zdenek Kotala.

- Use more real-world examples in the text search parser documentation
  (pgsql/doc/src/sgml/textsearch.sgml).

- In pgsql/src/backend/postmaster/autovacuum.c, extract catalog info
  for error reporting before an error actually happens.  Also, remove
  redundant reset of for-wraparound PGPROC flag.  Thanks to Tom Lane
  for noticing both bogosities.

- Allow an autovacuum worker to be interrupted automatically when it
  is found to be locking another process (except when it's working to
  prevent Xid wraparound problems).  Simon Riggs

Magnus Hagander committed:

- Use snprintf instead of wsprintf, and use getenv("APPDATA") instead
  of SHGetFolderPath.  This removes the direct dependency on
  shell32.dll and user32.dll, which eats a lot of "desktop heap" for
  each backend that's started. The desktop heap is a very limited
  resource, causing backends to no longer start once it's been
  exhausted.  We still have indirect depdendencies on user32.dll
  through third party libraries, but those can't easily be removed.
  Patch by Dave Page with editorializing by Tom Lane.

- Change win32 child-death tracking code to use a threadpool to wait
  for childprocess deaths instead of using one thread per child. This
  drastastically reduces the address space usage and should allow for
  more backends running.  Also change the win32_waitpid functionality
  to use an IO Completion Port for queueing child death notices
  instead of using a fixed-size array.

Tom Lane committed:

- Adjust ts_debug's output as per my proposal of yesterday: show the
  active dictionary and its output lexemes as separate columns,
  instead of smashing them into one text column, and lowercase the
  column names.  Also, define the output rowtype using OUT parameters
  instead of a composite type, to be consistent with the other
  built-in functions.

- Clarify example of planner cost computation, per a suggestion from
  James Shaw.  Also update a couple of examples to reflect 8.3's
  improved plan-printing code.

- Fix several bugs in tsvectorin, including crash due to uninitialized
  field and miscomputation of required palloc size.  The crash could
  only occur if the input contained lexemes both with and without
  positions, which is probably not common in practice.  The
  miscomputation would definitely result in wasted space.  Also fix
  some inconsistent coding around alignment of strings and positions
  in a tsvector value; these errors could also lead to crashes given
  mixed with/without position data and a machine that's picky about
  alignment.  And be more careful about checking for overflow of
  string offsets.  Patch is only against HEAD --- I have not looked to
  see if same bugs are in back-branch contrib/tsearch2 code.

- Fix two-argument form of ts_rewrite() so it actually works for cases
  where a later rewrite rule should change a subtree modified by an
  earlier one.  Per my gripe of a few days ago.

- Rename and slightly redefine the default text search parser's "word"
  categories, as per discussion.  asciiword (formerly lword) is still
  ASCII-letters-only, and numword (formerly word) is still the most
  general mixed-alpha-and-digits case.  But word (formerly nlword) is
  now any-group-of-letters-with-at-least-one-non-ASCII, rather than
  all-non-ASCII as before.  This is no worse than before for parsing
  mixed Russian/English text, which seems to have been the design
  center for the original coding; and it should simplify matters for
  parsing most European languages.  In particular it will not be
  necessary for any language to accept strings containing digits as
  being regular "words".  The hyphenated-word categories are adjusted
  similarly.

- Make configure probe for the location of the <uuid.h> header file.
  Needed to accommodate different layout on some platforms (Debian for
  one).  Heikki Linnakangas

- Remove the aggregate form of ts_rewrite(), since it doesn't work as
  desired if there are zero rows to aggregate over, and the API seems
  both conceptually and notationally ugly anyway.  We should look for
  something that improves on the tsquery-and-text-SELECT version
  (which is also pretty ugly but at least it works...), but it seems
  that will take query infrastructure that doesn't exist today.  (Hm,
  I wonder if there's anything in or near SQL2003 window functions
  that would help?)  Per discussion.

- Set read_only = TRUE while evaluating input queries for ts_rewrite()
  and ts_stat(), per my recent suggestion.  Also add a
  possibly-not-needed- but-can't-hurt check for NULL SPI_tuptable,
  before we try to dereference same.

- In pgsql/src/backend/access/heap/pruneheap.c, keep heap_page_prune
  from marking the buffer dirty when it didn't really change anything.
  Per report from Itagaki Takahiro.  Fix by Pavan Deolasee.

- Fix UPDATE/DELETE WHERE CURRENT OF to support repeated update and
  update- then-delete on the current cursor row.  The basic fix is
  that nodeTidscan.c has to apply heap_get_latest_tid() to the
  current-scan-TID obtained from the cursor query; this ensures we get
  the latest row version to work with.  However, since that only works
  if the query plan is a TID scan, we also have to hack the planner to
  make sure only that type of plan will be selected.  (Formerly, the
  planner might decide to apply a seqscan if the table is very small.
  This change is probably a Good Thing anyway, since it's hard to see
  how a seqscan could really win.)  That means the execQual.c code to
  support CurrentOfExpr as a regular expression type is dead code, so
  replace it with just an elog().  Also, add regression tests covering
  these cases.  Note that the added tests expose the fact that
  re-fetching an updated row misbehaves if the cursor used FOR UPDATE.
  That's an independent bug that should be fixed later.  Per report
  from Dharmendra Goyal.

- Fix an error in make_outerjoininfo introduced by my patch of 30-Aug:
  the code neglected to test whether an outer join's join-condition
  actually refers to the lower outer join it is looking at.  (The
  comment correctly described what was supposed to happen, but the
  code didn't do it...)  This often resulted in adding an unnecessary
  constraint on the join order of the two outer joins, which was bad
  enough.  However, it also seems to expose a performance problem in
  an older patch (from 15-Feb): once we've decided that there is a
  join ordering constraint, we will start trying clauseless joins
  between every combination of rels within the constraint, which
  pointlessly eats up lots of time and space if there are numerous
  rels below the outer join.  That probably needs to be revisited :-(.
  Per gripe from Jakub Ouhrabka.

- In pgsql/doc/src/sgml/ref/fetch.sgml, remove obsolete statement that
  you can't update through a cursor.

- Disallow scrolling of FOR UPDATE/FOR SHARE cursors, so as to avoid
  problems in corner cases such as re-fetching a just-deleted row.  We
  may be able to relax this someday, but let's find out how many
  people really care before we invest a lot of work in it.  Per report
  from Heikki and subsequent discussion.  While in the neighborhood,
  make the combination of INSENSITIVE and FOR UPDATE throw an error,
  since they are semantically incompatible.  (Up to now we've accepted
  but just ignored the INSENSITIVE option of DECLARE CURSOR.)

- Tweak new error messages to match the actual syntax of DECLARE
  CURSOR.  (Last night I copied-and-pasted from the WITH HOLD case,
  but that's wrong because of the bizarrely irregular syntax specified
  by the standard.)

- Fix ALTER SEQUENCE so that it does not affect the value of currval()
  for the sequence.  Also, make setval() with is_called = false not
  affect the currval state, either.  Per report from Kris Jurka that
  an implicit ALTER SEQUENCE OWNED BY unexpectedly caused currval() to
  become valid.  Since this isn't 100% backwards compatible, it will
  go into HEAD only; I'll put a more limited patch into 8.2.

- In pgsql/src/backend/postmaster/autovacuum.c, fix memory management
  for new variables -- they must actually survive transaction end, in
  case we decide to do a vacuum analyze (which is done in two xacts).

- Ugly patch to make ALTER SEQUENCE OWNED BY not affect the currval()
  state of the sequence.  Since OWNED BY never existed before 8.2,
  this seems unlikely to create any compatibility issues.  Other forms
  of ALTER SEQUENCE continue to do what they did before, namely update
  currval to match the sequence's actual last_val.  That seems wrong
  on consideration, but we'll not change it in a minor release --- 8.3
  will make that fix.

- Make initdb's selection of default text search configuration depend
  only on the 'language' part of the locale name, ignoring the country
  code.  We may need to be smarter later when there are more built-in
  configurations, but for now this is good enough and avoids having to
  bloat the table.

- Avoid including any backend-only stuff in the zic utility program.
  Per gripe from Zdenek Kotala, though not exactly his patch.

- Avoid having backend-only code compiled into ecpg.  Per Zdenek
  Kotala.

- Change have_join_order_restriction() so that we do not force a
  clauseless join if either of the input relations can legally be
  joined to any other rels using join clauses.  This avoids uselessly
  (and expensively) considering a lot of really stupid join paths when
  there is a join restriction with a large footprint, that is, lots of
  relations inside its LHS or RHS.  My patch of 15-Feb-2007 had been
  causing the code to consider joining *every* combination of rels
  inside such a group, which is exponentially bad :-(.  With this
  behavior, clauseless bushy joins will be done if necessary, but
  they'll be put off as long as possible.  Per report from Jakub
  Ouhrabka.  Backpatch to 8.2.  We might someday want to backpatch to
  8.1 as well, but 8.1 does not have the problem for OUTER JOIN nests,
  only for IN-clauses, so it's not clear anyone's very likely to hit
  it in practice; and the current patch doesn't apply cleanly to 8.1.

- In pgsql/doc/src/sgml/textsearch.sgml, minor wording improvements
  per suggestion from Jeff Davis.  Also tweak hyphenated-word parser
  examples per earlier discussion with Alvaro.

- Stamp 8.3beta2.

- Avoid considering both sort directions as equally useful for
  merging.  This doubles the planning workload for mergejoins while
  not actually accomplishing much.  The only useful case is where one
  of the directions matches the query's ORDER BY request; therefore,
  put a thumb on the scales in that direction, and otherwise
  arbitrarily consider only the ASC direction.  (This is a lot easier
  now than it would've been before 8.3, since we have more semantic
  knowledge embedded in PathKeys now.)

- In pgsql/src/backend/tsearch/wparser_def.c, add some rudimentary
  tracing code to the default text search parser, to help in debugging
  its state-machine rules.  Const-ify all the constant tables.  Minor
  other code cleanup, including using "token" rather than "lexeme" to
  describe the output strings.

- Change text search parsing rules for hyphenated words so that digit
  strings containing decimal points aren't considered part of a
  hyphenated word.  Sync the hyphenated-word lookahead states with the
  subsequent part-by-part reparsing states so that we don't get
  different answers about how much text is part of the hyphenated
  word.  Per my gripe of a few days ago.

- Fix a couple of issues with pg_dump's handling of inheritance child
  tables that have default expressions different from their parent.
  First, if the parent table's default expression has to be split out
  as a separate ALTER TABLE command, we need a dependency constraint
  to ensure that the child's command is given second.  This is because
  the ALTER TABLE on the parent will propagate to the child.  (We
  can't prevent that by using ONLY on the parent's command, since it's
  possible that other children exist that should receive the inherited
  default.)  Second, if the child has a NULL default where the parent
  does not, we have to explicitly say DEFAULT NULL on the child in
  order for this state to be preserved after reload.  (The latter
  actually doesn't work right because of a backend bug, but that is a
  separate issue.) Backpatch as far as 8.0.  7.x pg_dump has enough
  issues with altered tables (due to lack of dependency analysis) that
  trying to fix this one doesn't seem very productive.

- Make pg_dump and friends consistently report both the filename and
  the errno string when complaining of fopen failures.  Per gripe from
  Bob Pawley, it's not always instantly obvious to the user which name
  we tried to open.

- Update FAQ_IRIX to reflect current realities about IRIX compilers.

- Ooops, fat-fingered last commit message.  Should have mentioned
  updating FAQ_IRIX for information about buggy MIPSPro compiler
  version, now confirmed by Herve Boulouis.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

ITAGAKI Takahiro sent in a patch to fix locking in pgstattuple.

Pavel Stehule sent in a patch to implement EXECUTE USING, which lets
people send parameters to dynamically created SQL in a fashion
analogous to PREPARE/EXECUTE in PL/PgSQL.

Gokulakannan Somasun sent in two revisions of a patch which lets
people add snapshot information to indexes.

Heikki Linnakangas sent in a patch which adds some autoconf magic to
fix uuid.h on Debian.

Alvaro Herrera sent in two revisions of a patch which turns a few
boolean vacuum flags in PGPROC into a bitmask.

Zdenek Kotala sent in a patch intended to clean up some headers.

Joe Conway sent in three revisions of a patch to fix crosstab in
contrib/ for NULL-handling.

Kenneth Marshall sent in a patch to implement the 2006 version of the
hash function by Bob Jenkins.
http://burtleburtle.net/bob/hash/doobs.html