Postgres Weekly News - 30 Marzo 2008

Post originale all'url:

http://people.planetpostgresql.org/dfetter/index.php?/archives/165-Postgres-Weekly-News-March-30-2008.html

Il Summer of Code chiuderà le proposte Molto Presto(TM).
Inviate le vostre proposte il prima possibile!

http://code.google.com/soc

La prima commit fest sta per finire e molte nuove funzionalità sono
state aggiunte. Onori a tutti per il duro lavoro fatto.

La conferenza su PostgreSQL della costa orientale è terminata. Onori a
tutti gli organizzatori, gli speaker e ai partecipanti.

== Postgres News prodotti ==
Rilasciato check_postgres 1.3.1.
http://bucardo.org/nagios/

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

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

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

== Offerte di lavoro legate a PostgreSQL per il mese di Marzo ==
http://archives.postgresql.org/pgsql-jobs/2008-03/threads.php

== PostgreSQL news locali ==
Il PG UK day ci sarà il 2 aprile a Birmingham.
http://www.postgresql.org.uk/

Il FISL 9.0 ci sarà dal 17 al 19 aprile presso il PUCRS a Porto Alegre, Brasile.
https://fisl.softwarelivre.org/9.0/

Il PGCon 2008 ci sarà dal 20 al 23 maggio a Ottawa.
http://www.pgcon.org/2008/

La conferenza Open Source 2008 dello stato dell'Utah ha aperto il call
for paper fino al primo giugno.
Questa seconda conferenza annuale ci sarà dal 28 al 30 agosto a Salt Lake City
http://2008.utosc.com/

== News su PostgreSQL ==

Planet PostgreSQL: http://www.planetpostgresql.org/

General Bits, archivi e nuovi articoli occasionali:
http://www.varlena.com/GeneralBits/

PostgreSQL Weekly News è stato spedito questa settimana grazie a David
Fetter, Selena Deckelmann e Josh Berkus.

Per segnalare news e annunci invia un email in inglese entro le ore 15,
fuso orario della costa orientale degli U.S.A, di domenica.

Per segnalazioni in inglese david(at)fetter(dot)org, per segnalazioni in
Tedesco pwn(at)pgug(dot)de, per segnalazioni in italiano pwn(at)itpug(dot)org

== Patch applicate ==
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.

== Patch rigettate (per ora) ==
Nessuno e' stato scontentato questa settimana :-)

== Patch in attesa ==
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.

--
(all opinions expressed are my own)
Federico Campoli
PostgreSQL Consulting -> PGHost http://www.pghost.eu