== PostgreSQL Weekly News - March 29 2009 ==

== PostgreSQL Weekly News - March 29 2009 ==

The last Commitfest items for 8.4 have been closed out. Look for an
important announcement later this week.

The git repository now takes community logins, and is much easier to
use and manage.
http://git.postgresql.org/

New Survey: What's your favorite PostgreSQL replication tool?
http://www.postgresql.org/community/

Devrim GUNDUZ has released another set of snapshot RPMs. Go forth and
test :)
http://yum.pgsqlrpms.org/news-8.4devel-ready-for-testing.php

== PostgreSQL Product News ==

pgAdmin 1.10 beta 2, a GUI management tool for PostgreSQL, released.
http://www.postgresql.org/ftp/pgadmin3/release/v1.10.0-beta2/

DBD::Pg 2.12.0, a Perl connector for PostgreSQL, released.
http://search.cpan.org/dist/DBD-Pg/

== PostgreSQL Tip of the Week ==

You can use a pg_service file to set up "profiles" of different
servers to which you connect.

== Postgresql 8.4 Feature of the Week ==

SSL Cleanup: Magnus Hagander has cleaned up the SSL connection code so
that it is more intuitive and reliable. It also allows you to specify
which certificate a specific client connection must use.

== PostgreSQL Jobs for March ==

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

== PostgreSQL Local ==

PostgreSQLFr will have a booth at Solutions Linux 2009 March 31-April
2 in Paris. Sign up with sas AT postgresql DOT fr or at the
French-language wiki:
http://postgresql.fr/sl2009:start

PostgreSQL Conference (JDCon) East 2009 registration is now open.
http://www.postgresql.us/purchase

Kevin Kempter will be speaking about backup and recover at Denver
PUG's first meeting on April 8, 2009.
http://www.diapug.org/Meeting.html

PgDay Brasilia will be April 17. Contact fernando.ike AT gmail DOT
com or eduardo DOT santos AT planejamento DOT gov DOT br to
participate.

Percona Performance Conference will be taking place at the Santa Clara
Convention Center, Santa Clara, California USA
http://conferences.percona.com/

CfP is open for PgDay Sao Paulo, which will be April 24. Contact
marins DOT consultoria AT gmail DOT com or marcelojscosta AT gmail DOT
com to participate.

PostgreSQL Conference, U.S. (JDCon) will be holding a PgDay at
LinuxFest Northwest (April 25/26th). The call for papers is out at
http://www.postgresqlconference.org/

There will also be PgDays on April 29 in Porto Velho, RO and on April
30 in Ji-Parana, RO. Contact Luis Fernando Bueno: proflfbueno AT
gmail DOT com to participate.

Michael Renner will be giving a PostgreSQL replication workshop at
Netways OSDC 2009 on April 29 and 30 in Nuremberg, Germany.
http://www.netways.de/english/osdc/y2009/programm/w/michael_renner_postgresql_repliziert_ein_ueberblick/

PGCon 2009 will be held 21-22 May 2009, in Ottawa at the University of
Ottawa. It will be preceded by two days of tutorials on 19-20 May
2009.
http://www.pgcon.org/2009/

Save The Date: pgDay San Jose. Sunday, July 19th 2009 immediately
before OSCON. CfP, more info TBA!

PGCon Brazil will be take place October 23-24 2009 at Unicamp in
Campinas, Sao Paulo state.

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter
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 ==

Peter Eisentraut committed:

- In pgsql/src/pl/tcl/expected/pltcl_queries_1.out, add expected file
for locale-enabled installations.

- In pgsql/src/bin/psql/help.c, fix my typo.

- In pgsql/src/bin/psql/command.c, no period if it's not a sentence.

- Polish SQL/MED terminology

- In pgsql/src/bin/psql/help.c, in the \? output, align the columns
consistently.

- In pgsql/src/bin/psql/help.c, update line count for \? output
(obtained from wc -l).

- In pgsql/src/backend/catalog/catalog.c, complete list of valid fork
names, and use double quotes.

- In pgsql/src/backend/libpq/hba.c, use double quotes instead of
single quotes.

- Gettext plural support. In the backend, I changed only a handful of
exemplary or important-looking instances to make use of the plural
support; there is probably more work there. For the rest of the
source, this should cover all relevant cases.

Bruce Momjian committed:

- In pgsql/doc/src/sgml/release.sgml, reorder 8.4 release note
sections.

- In pgsql/doc/src/sgml/release.sgml, correction: ansi-join
->anti-join.

- In pgsql/doc/src/sgml/release.sgml, document that Datestyle no
longer controls interval output, per suggestion from Ron Mayer

- In pgsql/doc/src/sgml/release.sgml, remove duplicate interval
(fractional seconds) items; Ron Mayer.

- In pgsql/doc/src/sgml/release.sgml, improved release notes interval
wording.

- In pgsql/doc/src/sgml/release.sgml, Fix release note attribution:
"Support the IS0 8601 time interval syntax (Tom, Kevin Grittner)"
per Ron Mayer.

- In pgsql/doc/src/sgml/release.sgml, first round of release note
changes from Guillaume Smet.

- In pgsql/doc/src/sgml/release.sgml, mark Greg Sabino Mullane as the
instigator of the statistics target increase.

- In pgsql/doc/src/sgml/release.sgml, second batch of release note
fixes by Guillaume Smet.

- In pgsql/doc/src/sgml/release.sgml, updated release wording, per
Greg Stark.

- In pgsql/doc/src/sgml/release.sgml, document in release notes that
NOT IN is only for NOT EXIST clauses. Andrew (RhodiumToad) Gierth.

- In pgsql/doc/src/sgml/monitoring.sgml, add documentation of the fact
that dtrace probes evaluate their parameters even when not active.
Explain how to prevent that with an ENABLED() check.

- Better document PQinitSSL(0) behavior in regards to libcrypto.

- In pgsql/doc/src/sgml/ref/alter_role.sgml, better document that SET
ROLE does not uset ALTER ROLE SET settings; suggested wording from
Josh Berkus.

- In pgsql/doc/src/sgml/release.sgml, update release notes to say
citext is multi-byte aware, per suggestion from patch author.

- In pgsql/src/interfaces/libpq/fe-secure.c, clarify variable naming:
pq_initssllib -> pq_init_ssl_lib.

- In pgsql/doc/src/sgml/release.sgml, consistent 8.4 release note
naming for Itagaki Takahiro.

- In pgsql/doc/src/sgml/release.sgml, add SGML markup for
commands/literal/application/etc in release notes; still more work
to do.

- In pgsql/doc/src/sgml/release.sgml, more release note markup.

- In pgsql/doc/src/sgml/release.sgml, more release note markup.

- In pgsql/doc/src/sgml/release.sgml, re-add release notes for release
8.3.7.

- In pgsql/doc/src/sgml/config.sgml, update constraint_exclusion docs
because parameter is now 3-valued, not just enable/disable.

- In pgsql/doc/src/sgml/release.sgml, first version of 8.4 release
notes; markup/cleanup/reorganization still required.

- In pgsql/doc/src/sgml/release.sgml, mention release note items
current as of 2009-03-16.

- In pgsql/doc/src/sgml/release.sgml, adjust AS OF release notes
markup.

Tom Lane committed:

- Const-ify the parse table passed to fillRelOptions. The previous
coding meant it had to be built on-the-fly at each entry to
default_reloptions.

- Implement "fastupdate" support for GIN indexes, in which we try to
accumulate multiple index entries in a holding area before adding
them to the main index structure. This helps because bulk insert is
(usually) significantly faster than retail insert for GIN. This
patch also removes GIN support for amgettuple-style index scans.
The API defined for amgettuple is difficult to support with
fastupdate, and the previously committed partial-match feature
didn't really work with it either. We might eventually figure a way
to put back amgettuple support, but it won't happen for 8.4.
catversion bumped because of change in GIN's pg_am entry, and
because the format of GIN indexes changed on-disk (there's a
metapage now, and possibly a pending list). Teodor Sigaev.

- In pgsql/src/backend/optimizer/plan/planner.c, fix stupid
parenthesization mistake. Per bug #4728 from Bruce Toll.

- Install a search tree depth limit in GIN bulk-insert operations, to
prevent them from degrading badly when the input is sorted or nearly
so. In this scenario the tree is unbalanced to the point of
becoming a mere linked list, so insertions become O(N^2). The
easiest and most safely back-patchable solution is to stop growing
the tree sooner, ie limit the growth of N. We might later consider
a rebalancing tree algorithm, but it's not clear that the benefit
would be worth the cost and complexity. Per report from Sergey
Burladyan and an earlier complaint from Heikki Linnakangas.
Back-patch to 8.2; older versions didn't have GIN indexes.

- In pgsql/contrib/pgcrypto/pgp.h, fix old thinko in pgp.h: the idea
is to declare some named enum types, not global variables of
anonymous enum types. This didn't actually hurt much because most
linkers will just merge the duplicated definitions ... but some
will complain. Per bug #4731 from Ceriel Jacobs. Backpatch to 8.1
--- the declarations don't exist before that.

- Adjust the APIs for GIN opclass support functions to allow the
extractQuery() method to pass extra data to the consistent() and
comparePartial() methods. This is the core infrastructure needed to
support the soon-to-appear contrib/btree_gin module. The APIs are
still upward compatible with the definitions used in 8.3 and before,
although *not* with the previous 8.4devel function definitions.
catversion bump for changes in pg_proc entries (although these are
just cosmetic, since GIN doesn't actually look at the function
signature before calling it...). Teodor Sigaev and Oleg Bartunov

- Add btree_gin contrib module. Teodor Sigaev and Oleg Bartunov.

- In pgsql/doc/src/sgml/release.sgml, fix markup so that 'make
HISTORY' works. A couple very minor editorial improvements.

- If we expect a hash join to be performed in multiple batches,
suppress "physical tlist" optimization on the outer relation (ie,
force a projection step to occur in its scan). This avoids storing
useless column values when the outer relation's tuples are written
to temporary batch files. Modified version of a patch by Michael
Henderson and Ramon Lawrence.

- In pgsql/src/backend/commands/copy.c, adjust phrasing of complaints
about multi-byte COPY delimiter strings. Per pgsql-hackers
discussion of 2009-02-17.

- Fix a couple of places that still claimed LC_COLLATE and LC_CTYPE
can't be changed after initdb.

- In pgsql/contrib/pg_standby/pg_standby.c, Make pg_standby's
maxretries option do what one would expect. Fujii Masao.

- In pgsql/src/backend/catalog/index.c, teach reindex_index() to clear
pg_index.indcheckxmin when possible. Greg Stark, slightly modified
by me.

- Fix possible failures when a tuplestore switches from in-memory to
on-disk mode while callers hold pointers to in-memory tuples. I
reported this for the case of nodeWindowAgg's primary scan tuple,
but inspection of the code shows that all of the calls in
nodeWindowAgg and nodeCtescan are at risk. For the moment, fix it
with a rather brute-force approach of copying whenever one of the
at-risk callers requests a tuple. Later we might think of some sort
of reference-count approach to reduce tuple copying.

- In pgsql/src/backend/utils/adt/xml.c, add an errdetail explaining
why we reject infinite dates and timestamps while converting to XML.
Bernd Helmle

- On Solaris, we should only force use of our own getopt(); it's okay
to use the system's getopt_long(). The previous coding was the
result of a sloppy discussion that failed to draw this distinction.
The result was that PG programs don't handle options as users of
that platform expect. Per gripe from Chuck McDevitt. Although this
is a pre-existing bug, I'm not backpatching since I think we could
do with a bit of beta testing before concluding this is really OK.

Andrew Dunstan committed:

- Remove munging of xml and xpath params to xpath(). The XML must now
be a well formed XML document.

Magnus Hagander committed:

- In pgsql/doc/src/sgml/release.sgml, fix release notes about pg_hba
changes.

- In pgsql/doc/src/sgml/release.sgml, clarify new SSL certificate
verification in libpq.

- In pgsql/doc/src/sgml/release.sgml, fix markup, per Devrim GUNDUZ.

Heikki Linnakangas committed:

- In pgsql/src/bin/psql/tab-complete.c, fix tab completion of ANALYZE
VERBOSE <tab>. It was previously confused with EXPLAIN ANALYZE
VERBOSE. Greg Sabino Mullane, reformatted by myself. Backpatch to
8.1, where the bug was introduced.

== Rejected Patches (for now) ==

Srinath K's patch to implement a global index on partitioned tables,
as it came with an IP disclaimer which precluded anyone's looking at
it.

Hitoshi Harada's patches to fix a tuplestore API issue where a
ScanTupleSlot is not always valid as long as the tuplestore-handling
code expects it to be. Tom Lane used forced copying instead.

== Pending Patches ==

Kedar Potdar sent in a patch to do automatic partioning.

KaiGai Kohei sent in another revision of his SE-PostgreSQL patches.

Zdenek Kotala sent in another patch to fix the regression tests on the
Czech (cs_CZ) locale.

Fujii Masao sent in a patch which adds a new trigger option to
pg_standby to fix a pg_standby failure mode.

Andrew (RhodiumToad) Gierth sent in a patch to change hstore so that
keys and values can be of arbitrary length, so long as the hstore
itself fits in varlena, supports btree and hash opclasses for hstores,
which in turn allow for UNIQUE constraints, and aggregation, adds
binary I/O, a flock of operators which relate hstores to text, text[]
and record, adds GiST and GIN support, converts between hstore and
record, and populates a record from an hstore.

Andrew (RhodiumToad) Gierth sent in a patch which causes GET
DIAGNOSTICS ROW_COUNT to work for MOVE and FETCH in PL/pgSQL.

Pavel Stehule sent in an example contrib module to implement a JSON
type using the WIP transformation hook modules.

Bruce Momjian sent in a patch which makes a more informative error
message when someone changes a GUC parameter which can only take one
value.

Taro Minowa sent in a patch for 8.5 which allows pg_dump to dump
comments on columns which are composite types.