== Postgres Weekly News - March 30 2008 ==

== 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(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot)

== 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(dot)GB27458(at)alvh(dot)no-ip(dot)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.