== Postgres Weekly News - April 06 2008 == - Mailing list pgsql-announce

== Postgres Weekly News - April 06 2008 ==

== Postgres Product News ==

Benetl 1.7 released.
http://www.benetl.net

check_postgres 1.4.1 released.
http://bucardo.org/nagios/

phpPgAdmin 4.2 released.
http://phppgadmin.sourceforge.net/

== Postgres Jobs for April ==

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

== Postgres Local ==

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

PostgreSQL Day Unicamp 2008 will be on April 22 in Campinas, SP, Brazil.
http://www.dextra.com.br/dia-postgresql.htm

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:

- Apply my original fix for Taiki Yamaguchi's bug report about
  DISTINCT MAX().  Add some regression tests for plausible failures in
  this area.

- Fix an oversight I made in a cleanup patch over a year ago:
  eval_const_expressions needs to be passed the PlannerInfo ("root")
  structure, because in some cases we want it to substitute values for
  Param nodes.  (So "constant" is not so constant as all that ...)
  This mistake partially disabled optimization of unnamed
  extended-Query statements in 8.3: in particular the
  LIKE-to-indexscan optimization would never be applied if the LIKE
  pattern was passed as a parameter, and constraint exclusion
  depending on a parameter value didn't work either.

- Add SPI-level support for executing SQL commands with one-time-use
  plans, that is commands that have out-of-line parameters but the
  plan is prepared assuming that the parameter values are constants.
  This is needed for the plpgsql EXECUTE USING patch, but will
  probably have use elsewhere.  This commit includes the SPI functions
  and documentation, but no callers nor regression tests.  The
  upcoming EXECUTE USING patch will provide regression-test coverage.
  I thought committing this separately made sense since it's logically
  a distinct feature.

- Support EXECUTE USING in plpgsql.  Pavel Stehule, with some
  improvements by myself.

- Revert my bad decision of about a year ago to make PortalDefineQuery
  responsible for copying the query string into the new Portal.  Such
  copying is unnecessary in the common code path through
  exec_simple_query, and in this case it can be enormously expensive
  because the string might contain a large number of individual
  commands; we were copying the entire, long string for each command,
  resulting in O(N^2) behavior for N commands.  (This is the cause of
  bug #4079.)  A second problem with it is that PortalDefineQuery
  really can't risk error, because if it elog's before having set up
  the Portal, we will leak the plancache refcount that the caller is
  trying to hand off to the portal.  So go back to the design in which
  the caller is responsible for making sure everything is copied into
  the portal if necessary.

- Teach ANALYZE to distinguish dead and in-doubt tuples, which it
  formerly classed all as "dead"; also get it to count DEAD item
  pointers as dead rows, instead of ignoring them as before.  Also
  improve matters so that tuples previously inserted or deleted by our
  own transaction are handled nicely: the stats collector's live-tuple
  and dead-tuple counts will end up correct after our transaction
  ends, regardless of whether we end in commit or abort.  While
  there's more work that could be done to improve the counting of
  in-doubt tuples in both VACUUM and ANALYZE, this commit is enough to
  alleviate some known bad behaviors in 8.3; and the other stuff
  that's been discussed seems like research projects anyway.  Pavan
  Deolasee and Tom Lane

- Remove heap_release_fetch, which is no longer used anywhere; this
  simplifies heap_fetch a little.

- Add a variant of the Levenshtein string-distance function that lets
  the user specify the cost values to use, instead of always using
  1's.  Volkan Yazici.  In passing, remove fuzzystrmatch.h, which
  contained a bunch of stuff that had no business being in a .h file;
  fold it into its only user, fuzzystrmatch.c.

- In pgsql/src/backend/utils/misc/guc.c, remove no-longer-used
  function assign_backslash_quote().

- Re-implement division for numeric values using the traditional
  "schoolbook" algorithm.  This is a good deal slower than our old
  roundoff-error-prone code for long inputs, so we keep the old code
  for use in the transcendental functions, where everything is
  approximate anyway.  Also create a user-accessible function
  div(numeric, numeric) to provide access to the exact result of
  trunc(x/y) --- since the regular numeric / operator will round off
  its result, simply computing that expression in SQL doesn't reliably
  give the desired answer.  This fixes bug #3387 and various related
  corner cases, and improves the usefulness of PG for high-precision
  integer arithmetic.

- Defend against JOINs having more than 32K columns altogether.  We
  cannot currently support this because we must be able to build Vars
  referencing join columns, and varattno is only 16 bits wide.
  Perhaps this should be improved in future, but considering that it
  never came up before, I'm not sure the problem is worth much effort.
  Per bug #4070 from Marcello Ceschia.  The problem seems largely
  academic in 8.0 and 7.4, because they have (different) O(N^2)
  performance issues with such wide joins, but back-patch all the way
  anyway.

- In pgsql/src/bin/psql/mainloop.c, a small visit from the portability
  and localization police.

- Improve hash_any() to use word-wide fetches when hashing suitably
  aligned data.  This makes for a significant speedup at the cost that
  the results now vary between little-endian and big-endian machines;
  which forces us to add explicit ORDER BYs in a couple of regression
  tests to preserve machine-independent comparison results.  Also,
  force initdb by bumping catversion, since the contents of hash
  indexes will change (at least on big-endian machines).  Kenneth
  Marshall and Tom Lane, based on work from Bob Jenkins.  This commit
  does not adopt Bob's new faster mix() algorithm, however, since we
  still need to convince ourselves that that doesn't degrade the
  quality of the hashing.

- Make plpgsql support FOR over a query specified by a cursor
  declaration, for improved compatibility with Oracle.  Pavel Stehule,
  with some fixes by me.

Bruce Momjian committed:

- Remove from TODO due to survey/discussion: "Prefix command-line
  utilities like createuser with 'pg_'"

- Add URLs for TODO: "Add SQL:2003 WITH RECURSIVE (hierarchical)
  queries to SELECT."

- Add to TODO: "Improve how ANALYZE computes in-doubt tuples."

- Add Wiki URLs for TODO: "Allow encoding on a per-column basis
  optionally using the ICU library."

- Implement current_query(), that shows the currently executing query.
  At the same time remove dblink/dblink_current_query() as it is no
  longer necessary *BACKWARD COMPATIBILITY ISSUE* for dblink.  Tomas
  Doran

- In pgsql/contrib/dblink/dblink.c, remove unneed #include now that
  current_query() has moved to the backend.

- Allow 'help' in psql to show \? help, for novice assistance.  Greg
  Sabino Mullane.

- Have psql command 'help' suggest the use of \?, updated version.
  Greg Sabino Mullane

- Have pg_stop_backup() wait for all archive files to be sent, rather
  than returing right away.  This guarantees that when
  pg_stop_backup() returns, you have a valid backup.  Simon Riggs

- Re-add dblink_current_query() for backward compatibility.

- Make dblink_current_query() reference pg_catalog.current_query(),
  per Tom.

- In pgsql/doc/src/sgml/func.sgml, add documentation clarification for
  IS [NOT] NULL and row-valued expressions.

- Add to TODO: "Add ability to obfuscate function bodies."

Magnus Hagander committed:

- Convert three more guc settings to enum type:
  default_transaction_isolation, session_replication_role and
  regex_flavor.

- In pgsql/src/backend/utils/misc/guc.c, convert syslog_facility guc
  to enum type.

- In pgsql/src/backend/utils/misc/guc.c, oops, add proper #ifdef for
  systems without support for syslog.  Per buildfarm member mastodon.

- Turn xmlbinary and xmloption GUC variables into enums.

- Convert backslash_quote guc to use enum.

- In pgsql/src/backend/parser/scan.l, oops, change should go in scan.l
  to survive a clean checkout and not just a make clean...

Peter Eisentraut committed:

- In pgsql/doc/src/sgml/cvs.sgml, remove -C from rsync call, because
  it omits directories named "core".

== Rejected Patches (for now) ==

Theo Schlossnagle's patch which exposes the start and finish
checkpoint times to SQL on grounds of design, completeness and
correctness.

== Pending Patches ==

NikhilS sent in another revision of his patch for the TODO items "Add
logic to disallow ADD CONSTRAINT ONLY to parent of an inheritance
hierarchy," "Add logic to mark inherited constraints in the children,"
"Add logic to disallow dropping inherited constraints directly on
children" and "Modify the pg_dump logic to use the new pg_constraint
based attributes logic."

Pavel Stehule sent in a patch to add CASE to PL/PgSQL.

Laurenz Albe sent in a patch intended to improve shutdown behavior
during online backup.

Bernd Helmle sent in a patch to psql to implement command aliases.

Simon Riggs sent in a patch to implement a waiting pg_stop_backup().

Chris Johnson sent in a patch to add -x <auxiliary command> to
pg_standby.

Peter Eisentraut sent in a patch which changes how shared libraries
and dynamically loadable modules are built.

Dawid Kuroczko sent in a patch to add \G to psql.  This works like \g
does, only it forces \x formatting.

Robert Hell sent in a patch which adds a GUC parameter for
tuple_fraction of cursors.

Fujii Masao sent in a patch which replace offnum++ by
OffsetNumberNext.

Pavel Stehule sent in three revisions of a patch which adds CASE to
PL/PgSQL.

Teodor Sigaev sent in a patch which adds a partial match to GIN.

Nobuhiro Iwamatsu sent in a patch to port PostgreSQL to Reneas SuperH.

Pavel Stehule sent in a patch which adds RETURN QUERY EXECUTE ...
USING... to PL/PgSQL.

Tom Dunstan sent in a WIP patch which implements [UN]INSTALL MODULE.