== Postgres Weekly News 06 aprile 2008 ==

== Postgres News prodotti ==
Rilasciato Benetl 1.7.
http://www.benetl.net

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

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

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

== PostgreSQL news locali ==

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

L'Unicamp PostgreSQL Day 2008 ci sara' il 22 aprile a Campinas, SP, Brasile.
http://www.dextra.com.br/dia-postgresql.htm

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.

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

== Patch rigettate (per ora) ==
Theo Schlossnagle's patch which exposes the start and finish
checkpoint times to SQL on grounds of design, completeness and
correctness.

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

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