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

== Postgres Weekly News - March 30 2008 ==

Summer of Code closes for applications Really Soon(TM).  Get your
application in ASAP!  http://code.google.com/soc

The first commit fest is ending soon, and lots of new features have
come in.  Kudos to all on the hard work.

PostgreSQL Conference East is over.  Kudos to all the organizers,
presenters and participants.

== Postgres Product News ==

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

PostgreSQL Maestro 8.3 released.
http://www.sqlmaestro.com/products/postgresql/maestro/

PL/Lua 0.3 released.
http://pgfoundry.org/projects/pllua/

pg_snapclone 1.0 Beta released.
http://pgfoundry.org/projects/snapclone/

== Postgres Jobs for March ==

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

== Postgres Local ==

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
Selena Deckelmann and Josh Berkus.

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

Bruce Momjian committed:

- Update TODO wording to read: "Allow pre/data/post files when schema
  and data are dumped separately, for performance reasons."

- Add to TODO: "Improve reporting of UNION type mismatches."

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

- Add to TODO: "SMP scalability improvements."

- Add URL for TODO: "SMP scalability improvements."

- Add to TODO: "Research reducing deTOASTing in more places."

- Add to TODO: "Allow xml arrays to be cast to other data types."

- Add to TODO: "Fix regular expression bug when using complex
  back-references."

- Add to TODO: "Create three versions of libpgport to simplify client
  code."

- Add to TODO: "Remove old-style routines for manipulating tuples."

- Add to TODO: "Have /contrib/dblink reuse unnamed connections."

- Add to TODO: "Prevent concurrent CREATE TABLE table1 from sometimes
  returning a cryptic error message."

- Add to TODO: "Be more aggressive about creating WAL files."

- Add to TODO: "Have resource managers report the duration of their
  status changes."

- Add to TODO: "Move pgfoundry's xlogdump to /contrib and have it rely
  more closely on the WAL backend code."

- Add to TODO: "Improve detection of shared memory segments being used
  by other FreeBSD jails."

- Add to TODO: "Consider sorting entries before inserting into btree
  index."

- Add to TODO: "Fix port/rint.c to be spec-compliant."

- Add to psql TODO: "Improve display if enums."

- Add to TODO: "Detect deadlocks involving LockBufferForCleanup()."

- Add to TODO: "Add more cross-data-type operators."

- Add to pl/pgsql TODO: "Consider invalidating the cache or keeping
  separate cached copies when search_path changes."

- Add to TODO: "Add SQL-standard array_agg() and unnest() array
  functions."

- Update TODO wording to read: "Simplify integer cross-data-type
  operators."

- Mark TODO as DONE: "Avoid tuple some tuple copying in sort
  routines."

- Added to TODO: "Consider transaction start/end performance
  improvements."

- Update TODO to read: "Consider keeping separate cached copies when
  search_path changes."

- Strengthen warnings about using pg_dump's -i option.

- In pgsql/doc/src/sgml/array.sgml, improve documentation for odd
  array slice behavior.

- Remove ipcclean utility command --- didn't work on all Unixes and on
  Windows.  Users should use their operating system tools instead.

- Add URL for TODO: "Consider allowing control of upper/lower case
  folding of unquoted identifiers."

- Add to TODO: "Consider being smarter about memory and external files
  used during sorts."

- Add to TODO: "Allow one transaction to see tuples using the snapshot
  of another transaction."

- Add to PL/PgSQL TODO: "Add CASE capability to language (already in
  SQL)."

- Mark TODO as done: "Add ability to trigger on TRUNCATE."

Tom Lane committed:

- Add a new ereport auxiliary function errdetail_log(), which works
  the same as errdetail except the string goes only to the server log,
  replacing the normal errdetail there.  This provides a reasonably
  clean way of dealing with error details that are too
  security-sensitive or too bulky to send to the client.  This commit
  just adds the infrastructure --- actual uses to follow.

- Adjust the recent patch for reporting of deadlocked queries so that
  we report query texts only to the server log.  This eliminates the
  issue of possible leaking of security-sensitive data in other
  sessions' queries.  Since the log is presumed secure, we can now log
  the queries of all sessions involved in the deadlock, whether or not
  they belong to the same user as the one reporting the failure.

- Fix various infelicities that have snuck into usage of errdetail()
  and friends.  Avoid double translation of some messages, ensure
  other messages are exposed for translation (and make them follow the
  style guidelines), avoid unsafe passing of an unpredictable message
  text as a format string.

- Use new errdetail_log() mechanism to provide a less klugy way of
  reporting large numbers of dependencies on a role that couldn't be
  dropped.  Per a comment from Alvaro Herrera.

- When a relation has been proven empty by constraint exclusion,
  propagate that knowledge up through any joins it participates in.
  We were doing that already in some special cases but not in the
  general case.  Also, defend against zero row estimates for the input
  relations in cost_mergejoin --- this fix may have eliminated the
  only scenario in which that can happen, but be safe.  Per report
  from Alex Solovey.

- Adjust DatumGetBool macro so that it isn't fooled by garbage in the
  Datum to the left of the actual bool value.  While in most cases
  there won't be any, our support for old-style user-defined functions
  violates the C spec to the extent of calling functions that might
  return char or short through a function pointer declared to return
  "char *", which we then coerce to Datum.  It is not surprising that
  the result might contain garbage high-order bits ... what is
  surprising is that we didn't see such cases long ago.  Per report
  from Magnus.  This is a back-patch of a change that was made in HEAD
  almost exactly a year ago.  I had refrained from back-patching at
  the time, but now we find that this is *necessary* for contrib to
  work with gcc 4.3.

- Simplify and standardize conversions between TEXT datums and
  ordinary C strings.  This patch introduces four support functions
  cstring_to_text, cstring_to_text_with_len, text_to_cstring, and
  text_to_cstring_buffer, and two macros CStringGetTextDatum and
  TextDatumGetCString.  A number of existing macros that provided
  variants on these themes were removed.  Most of the places that need
  to make such conversions now require just one function or macro
  call, in place of the multiple notational layers that used to be
  needed.  There are no longer any direct calls of textout or textin,
  and we got most of the places that were using handmade conversions
  via memcpy (there may be a few still lurking, though).  This commit
  doesn't make any serious effort to eliminate transient memory leaks
  caused by detoasting toasted text objects before they reach
  text_to_cstring.  We changed PG_GETARG_TEXT_P to PG_GETARG_TEXT_PP
  in a few places where it was easy, but much more could be done.
  Brendan Jurd and Tom Lane

- Fix core dump in contrib/xml2's xpath_table() when the input query
  returns a NULL value.  Per bug #4058.

- Fix bad spelling and worse grammar in recent doc commits.  Propagate
  pg_dump --ignore-version comments into pg_dumpall and pg_restore
  pages.

- Reduce the need for frontend programs to include "postgres.h" by
  refactoring inclusions in src/include/catalog/*.h files.  The main
  idea here is to push function declarations for
  src/backend/catalog/*.c files into separate headers, rather than
  sticking them into the corresponding catalog definition file as has
  been done in the past.  This commit only carries out that idea fully
  for pg_proc, pg_type and pg_conversion, but that's enough for the
  moment --- if pg_list.h ever becomes unsafe for frontend code to
  include, we'll need to work a bit more.  Zdenek Kotala

- In pgsql/src/backend/optimizer/plan/planner.c, when we have
  successfully optimized a MIN or MAX aggregate into an indexscan, the
  query result must be exactly one row (since we don't do this when
  there's any GROUP BY).  Therefore any ORDER BY or DISTINCT attached
  to the query is useless and can be dropped.  Aside from saving
  useless cycles, this protects us against problems with matching the
  hacked-up tlist entries to sort clauses, as seen in a bug report
  from Taiki Yamaguchi.  We might need to work harder if we ever try
  to optimize grouped queries with this approach, but this solution
  will do for now.

- Support statement-level ON TRUNCATE triggers.  Simon Riggs.

- In pgsql/src/backend/optimizer/plan/planner.c, department of second
  thoughts: the rule that ORDER BY and DISTINCT are useless for an
  ungrouped-aggregate query holds regardless of whether
  optimize_minmax_aggregates succeeds.  So we might as well apply the
  optimization in any case.  I'll leave 8.3 as it was, since this
  version is a tad more invasive than my earlier patch.

- In pgsql/src/backend/optimizer/plan/planner.c, revert my erroneous
  fix for Taiki Yamaguchi's DISTINCT MAX() bug.  Whatever we do about
  that, this isn't the path to the solution.

- In pgsql/src/bin/psql/tab-complete.c, improve psql's tab completion
  to handle completing attribute names in cases where the relation
  name was schema-qualified, for example UPDATE foo.bar SET <tab>.
  Also support cases where the relation name was quoted unnecessarily,
  for example UPDATE "foo" SET <tab> Greg Sabino Mullane, slightly
  simplified by myself.

- In pgsql/src/bin/psql/help.c, improve description of \du and \dg,
  per suggestion from Harald Armin Massa.

- In pgsql/src/bin/psql/describe.c, display incoming as well as
  outgoing foreign-key constraints in psql's \d output for a table.
  Kenneth D'Souza, some changes by myself.

- Show database access privileges in psql's \l command.  For \l+, also
  show database size, when available to the current user.  Andrew
  Gilligan

- Fix a number of places that were making file-type tests
  infelicitously.  The places that did, eg,
  (statbuf.st_mode & S_IFMT) == S_IFDIR were correct, but there is no
  good reason not to use S_ISDIR() instead, especially when that's
  what the other 90% of our code does.  The places that did, eg,
  (statbuf.st_mode & S_IFDIR) were flat out *wrong* and would fail in
  various platform-specific ways, eg a symlink could be mistaken for a
  regular file on most Unixen.  The actual impact of this is probably
  small, since the problem cases seem to always involve symlinks or
  sockets, which are unlikely to be found in the directories that PG
  code might be scanning.  But it's clearly trouble waiting to happen,
  so patch all the way back anyway.  (There seem to be no occurrences
  of the mistake in 7.4.)

- Use error message wordings for permissions checks on .pgpass and SSL
  private key files that are similar to the one for the postmaster's
  data directory permissions check.  (I chose to standardize on that
  one since it's the most heavily used and presumably best-wordsmithed
  by now.)  Also eliminate explicit tests on file ownership in these
  places, since the ensuing read attempt must fail anyway if it's
  wrong, and there seems no value in issuing the same error message
  for distinct problems.  (But I left in the explicit ownership test
  in postmaster.c, since it had its own error message anyway.)  Also
  be more specific in the documentation's descriptions of these
  checks.  Per a gripe from Kevin Hunter.

- In pgsql/src/backend/commands/tablecmds.c, fix my brain fade in
  TRUNCATE triggers patch: can't release relcache refcounts while
  EState still contains pointers to those relations.  Exposed by the
  CLOBBER_CACHE_ALWAYS tests that buildfarm member jaguar is running
  (I knew those cycles would pay off...)

Michael Meskes committed:

- In ecpg, added ECPGget_PGconn to exports.txt

- Moved from PQsetdbLogin to PQconnectDB.  Correctly parse connect
  options.  Changed regression tests accordingly.

- Sorry, copied wrong files.

Alvaro Herrera committed:

- Add URLs to TODO: "Be more aggressive about creating WAL files" per
  Simon Riggs.

- Update TODO item to read: "Find a correct rint() substitute on
  Windows."

- Separate snapshot management code from tuple visibility code, create
  a snapmgmt.c file for the former.  The header files have also been
  reorganized in three parts: the most basic snapshot definitions are
  now in a new file snapshot.h, and the also new snapmgmt.h keeps the
  definitions for snapmgmt.c.  tqual.h has been reduced to the bare
  minimum.  This patch is just a first step towards managing live
  snapshots within a transaction; there is no functionality change.
  Per my proposal to pgsql-patches on 20080318191940.GB27458@alvh.no-ip.org
  and subsequent discussion.

- Rename snapmgmt.c/h to snapmgr.c/h, for consistency with other
  files.  Per complaint from Tom Lane.

- Move the HTSU_Result enum definition into snapshot.h, to avoid
  including tqual.h into heapam.h.  This makes all inclusion of
  tqual.h explicit.  I also sorted alphabetically the includes on some
  source files.

Neil Conway committed:

- Add a new tuplestore API function, tuplestore_putvalues(). This is
  identical to tuplestore_puttuple(), except it operates on arrays of
  Datums + nulls rather than a fully-formed HeapTuple. In several
  places that use the tuplestore API, this means we can avoid creating
  a HeapTuple altogether, saving a copy.

- Update documentation for recent DTrace changes. Patch from Robert
  Lor.

- Enable 64-bit integer datetimes by default, per previous discussion.
  This requires a working 64-bit integer type. If such a type cannot
  be found, "--disable-integer-datetimes" can be used to switch back
  to the previous floating point-based datetime implementation.

Magnus Hagander committed:

- In pgsql/src/bin/psql/help.c, include \password in the psql help.
  While at it, change the order of the documented options to be
  alphabetically again.

Heikki Linnakangas committed:

- In pgsql/doc/src/sgml/backup.sgml, clarify documentation on PITR and
  warm standby on the fact that the standby restore_command should
  report failure on non-existent .backup and .history files. Tidy up
  some related text along the way.  Patch by Markus Bertheau, with
  some editing by Simon Riggs and myself.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Pavel Stehule sent in a patch which adds a generate_subscripts()
function.

Pavel Stehule sent in a patch which implements EXECUTE ... USING in
PL/PgSQL.

Zoltan Boszormenyi sent in several more revisions of his patch which
allows 64-bit ints and floats to be passed by value on 64-bit
platforms.

Yoshiyuki Asaba sent in an update to Greg Stark and Neil Conway's
patch to implement non-recursive WITH.

Martin Pihlak sent in another revision of his patch to put stored
procedure statistics in the stats collector.

Bruce Momjian sent in two versions of a patch which removes the
non-universal ipcclean.

Merlin Moncure sent in another revision of his libpq type system
patch.

Alex Hunsaker sent in two revisions of a patch to aAdd logic to
disallow ADD CONSTRAINT ONLY to parent of an inheritance hierarchy and
add logic to mark inherited constraints in the children: This can be
achieved by introducing a new bool "coninherited" attribute in
pg_constraint.

Alvaro Herrera sent in a WIP patch to keep track of the live snapshots
within a transaction.

Andreas Scherbaum sent in a patch which adds "CREATE IF NOT EXISTS
[language]" as an option for creating PLs.

Pierre Caillaud sent in three revisions of a patch which implements
globally prepared statements.