== PostgreSQL Weekly News - February 15 2009 ==

== PostgreSQL Weekly News - February 15 2009 ==

At Prague PostgreSQL Developer Day, the Czech and Slovak people
reunited to form a Czechoslovakian PostgreSQL Users' Group. Expect
news from them soon.

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

== PostgreSQL Product News ==

Benetl 2.5, an ETL tool for files using PostgreSQL, released.
http://www.benetl.net

pgpool-II 2.2 RC1, a connection pooler and more, released.
http://pgfoundry.org/projects/pgpool/

== PostgreSQL 8.4 Feature of the Week ==

Parallel Restore. Andrew Dunstan parallelized pg_restore to that you
can put all of your processors to work loading (or upgrading) a
database. In testing, this feature cuts load times by 50% with 4
cores and 75% with 8. Even better, this new pg_restore will work with
8.3 databases.

== PostgreSQL Tip of the Week ==

Don't index low-cardinality columns, except as part of a multicolumn
index.

== PostgreSQL Jobs for February ==

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

== PostgreSQL Local ==

Ed Borasky will be speaking about Data Visualization at PDXPUG on
February 19, 2009, 7-9pm in Portland.
http://calagator.org/events/1250456619

PostgreSQL will be at SCALE February 20-22, 2009 in Los Angeles.
http://www.socallinuxexpo.org/

Andreas 'ads' Scherbaum will be presenting a talk at Perl Workshop
2009 on February 25 in Frankfurt am Main, Germany.
http://www.perl-workshop.de/talks/151/view

The German PostgreSQL User Group will have a booth, 2 talks and a
workshop on the Linuxdays Chemnitz on March 14 and 15, 2009. More
information in German here:
http://andreas.scherbaum.la/blog/archives/525-PostgreSQL-auf-den-Chemnitzer-Linuxtagen.html

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

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/papers.php

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

Bruce Momjian committed:

- In pgsql/doc/src/sgml/ref/psql-ref.sgml, fix spelling error in docs.
Erik Rijkers

- In pgsql/doc/src/sgml/ref/pg_dump.sgml, update wording of how to
prevent pg_dump from affecting statistics collection.

- In pgsql/doc/src/sgml/libpq.sgml, clarify PQinitSSL() documentation
to mention what the argument controls.

- In pgsql/doc/src/sgml/ref/create_table.sgml, remove tabs from SGML.

Alvaro Herrera committed:

- Update autovacuum to use reloptions instead of a system catalog, for
per-table overrides of parameters. This removes a whole class of
problems related to misusing the catalog, and perhaps more
importantly, gives us pg_dump support for the parameters. Based on
a patch by Euler Taveira de Oliveira, heavily reworked by me.

- In pgsql/src/bin/psql/describe.c, improve psql \d+ to show TOAST
reloptions too. Per complaint from ITAGAKI Takahiro.

Tom Lane committed:

- Adopt Bob Jenkins' improved hash function for hash_any(). This
changes the contents of hash indexes (again), so bump catversion.
Kenneth Marshall.

- Tweak configure to attempt to add -qnoansialias to CFLAGS whenever
running on AIX with a non-gcc compiler. The previous coding would
do this only if CC was exactly "xlc"; which is a bad idea, as
demonstrated by trouble report from Mihai Criveti.

- Change ALTER TABLE SET WITHOUT OIDS to rewrite the whole table to
physically get rid of the OID column. This eliminates the problem
discovered by Heikki back in November that 8.4's suppression of
"unnecessary" junk filtering in INSERT/SELECT could lead to an
Assert failure, or storing of oids into a table that shouldn't have
them if Asserts are off. While that particular problem could have
been solved in other ways, it seems likely to be just a forerunner
of things to come if we continue to allow tables to contain rows
that disagree with the pg_class.relhasoids setting. It's better to
make this operation slow than to sacrifice performance or risk bugs
in more common code paths. Also, add ALTER TABLE SET WITH OIDS to
rewrite the table to add oids. This was a bit more controversial,
but in view of the very small amount of extra code needed given the
current ALTER TABLE infrastructure, it seems best to eliminate the
asymmetry in features.

- Fix UNLISTEN to fall out quickly if the current backend has never
executed any LISTEN command. This is more important than it used to
be because DISCARD ALL invokes UNLISTEN. Connection-pooled
applications making heavy use of DISCARD ALL were seeing significant
contention for pg_listener, as reported by Matteo Beccati. It seems
unlikely that clients using LISTEN would use pooled connections, so
this simple tweak seems sufficient, especially since the pg_listener
implementation is slated to go away soon anyway. Back-patch to 8.3,
where DISCARD ALL was introduced.

- In pgsql/src/backend/catalog/information_schema.sql, a couple of
marginal performance hacks for the information_schema views: replace
the old recursive-SQL-function implementation of _pg_keysequal()
with use of the built-in array containment operators, and change
table_constraints' UNION to UNION ALL. Per discussion with Octavio
Alvarez. initdb not forced since this doesn't affect results, but
you'd need to initdb or reload the information_schema to see the new
definitions.

- In pgsql/doc/src/sgml/config.sgml, minor wordsmithing.

- Teach the planner to treat a partial unique index as proving a
variable is unique for a particular query, if the index predicate is
satisfied. This requires a bit of reordering of operations so that
we check the predicates before doing any selectivity estimates, but
shouldn't really cause any noticeable slowdown. Per a comment from
Michal Politowski.

Peter Eisentraut committed:

- In pgsql/src/backend/utils/mb/Unicode/UCS_to_most.pl, add
possibility to generate only some files, by passing command-line
arguments.

- In pgsql/src/backend/utils/mb/wchar.c, remove the encoding *numbers*
from the comments. They are useless, and make maintenance harder.

- Support for KOI8U encoding.

- Only unset the locale environment when --no-locale is used (the way
it was presumably designed, but didn't act). This allows running
the temp install tests in a non-C locale, thus exercising users'
real environments better. Document how to change locales for test
runs.

- Unset language-related locale settings in any case, otherwise psql
will speak in tongues and mess up the regression test diffs.

- The Czech (cs_CZ) and Slovak (sk_SK) locales sort numbers after
letters, instead of vice versa. Update the regression test
expectations to support that. In the plpgsql test, adjust the test
data so that this isn't an issue. In the char and varchar tests,
add new expected files.

Magnus Hagander committed:

- In pgsql/src/port/win32env.c, don't call SetEnvironmentVariable()
when removing an environment variable, as this seems to crash on at
least some versions of MingW. Our current usage of this function
does not require it, so it should be ok to ignore.

- In pgsql/src/port/kill.c, loop calling CallNamedPipe() several times
in case it fails, since it can be transient failures, causing kill()
to not properly send signals. Original patch from Steve Marshall,
modified by me.

Andrew Dunstan committed:

- Do not use unsetenv.c on mingw.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Heikki Linnakangas sent in another revision of the recovery
infrastructure patch.

Heikki Linnakangas sent in a change to the way subtransaction cache
works. This is in aid of Hot Standby.

ITAGAKI Takahiro sent in a patch to reject toast.fillfactor as a
reloption, modify the relopt_kind to bit flags, and report the
relation type on "unrecognized parameter" errors.

Teodor Sigaev sent in another revision of the fast GIN insert patch.

Pavel Stehule sent in a patch to add in some parser hooks for
Oracle-compatible DECODE support.

Jeff Davis sent in a patch to advance xmin more aggressively in
long-running transactions.

KaiGai Kohei sent in two more revisions of the SE-PostgreSQL patch.

Alvaro Herrera sent in two revisions of a patch to adjust psql's
describe to do the right thing in when talking to different versions
of PostgreSQL (pre-8.4 vs. later) pursuant to the reloptions patch.

Fujii Masao sent in another revision of the synchronous replication
patch.

Teodor Sigaev sent in another revision of the B-Tree emulation for GIN
patch.

Andrew Chernow sent in two revisions of a patch which adds a
PQInitCrypto(int) function to libpq so that it's possible to use a
crypto library separately from SSL.