== PostgreSQL Weekly News - September 06 2009 ==

== PostgreSQL Weekly News - September 06 2009 ==

New Survey: Which 8.5 Alpha1 feature do you find the most
interesting?
http://www.postgresql.org/community

Registration for PostgreSQL Conference West is now open:
http://www.postgresql.us/purchase

PostgreSQL Live CD Project started a mailing list to build a live CD
with comments from community:
http://www.pglivecd.org/community.php

== PostgreSQL Product News ==

GTpB portalBase, a web plaform based on PostgreSQL, is now available
under the GPL.
http://www.gtportalbase.com/

PostgreSQL RPM Building Project another version of The PostgreSQL
Live CD, this time based on PostgreSQL 8.4.0 and CentOS 5.3:
http://yum.pgsqlrpms.org/livecd.php

AnySQL Maestro 9.8, a GUI for administration and development on
Windows, released.
http://www.sqlmaestro.com/products/anysql/maestro/

pg51g, a data diff toolkit for PostgreSQL, released.
http://pgdba.net/pg51g/

pgDesigner 1.2.11, a graphical schema design program for PostgreSQL,
released.
http://pgdesigner.sourceforge.net/en/index.html

== PostgreSQL Jobs for September ==

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

== PostgreSQL Local ==

SFPUG presents: Nathan Boley on Statistics and Postgres.
September 8, 2009. Details including live webcast info below.
http://postgresql.meetup.com/1/calendar/11030245/

There will be a conference in Seattle, Washington, USA October 16-18,
2009. The CfP is still open.
http://www.postgresqlconference.org/2009/west

PGCon Brazil will be take place October 23-24 2009 at Unicamp in
Campinas, Sao Paulo state. The CfP is open!
http://pgcon.postgresql.org.br/2009/chamadas.en.php

PGDay.EU 2009 will be at Telecom ParisTech in Paris, France on
November 6-7, 2009. The CfP is out. Submit!
http://www.pgday.eu/

OpenSQL Camp in Portland is looking for sponsors. Make your travel plans now! :)
http://www.chesnok.com/daily/2009/07/29/opensql-camp-comes-to-portland-november-14-15-2009/

JPUG 10th Anniversary Conference is November 20-21, 2009 in Tokyo, Japan.
http://archives.postgresql.org/pgsql-announce/2009-05/msg00018.php

FOSDEM 2010 will be in Brussels on February 6-7, 2010.
http://www.fosdem.org/

== PostgreSQL in the News ==

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

PostgreSQL Weekly News is brought to you this week by David Fetter,
Josh Berkus, and Devrim GUNDUZ.

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

Tom Lane committed:

- Track the current XID wrap limit (or more accurately, the oldest
unfrozen XID) in checkpoint records. This eliminates the need to
recompute the value from scratch during database startup, which is
one of the two remaining reasons for the flatfile code to exist. It
should also simplify life for hot-standby operation. To avoid
bloating the checkpoint records unreasonably, I switched from
tracking the oldest database by name to tracking it by OID. This
turns out to save cycles in general (everywhere but the
warning-generating paths, which we hardly care about) and also helps
us deal with the case that the oldest database got dropped instead
of being vacuumed. The prior coding might go for a long time
without updating the wrap limit in that case, which is bad because
it might result in a lot of useless autovacuum activity.

- Change the autovacuum launcher to read pg_database directly, rather
than via the "flat files" facility. This requires making it enough
like a backend to be able to run transactions; it's no longer an
"auxiliary process" but more like the autovacuum worker processes.
Also, its signal handling has to be brought into line with
backends/workers. In particular, since it now has to handle
procsignal.c processing, the special autovac-launcher-only signal
conditions are moved to SIGUSR2. Alvaro Herrera, with some cleanup
from Tom Lane.

- Move processing of startup-packet switches and GUC settings into
InitPostgres, to fix the problem that SetClientEncoding needs to be
done before InitializeClientEncoding, as reported by Zdenek Kotala.
We get at least the small consolation of being able to remove the
bizarre API detail that had InitPostgres returning whether user is a
superuser.

- Bump catversion for flat-file-ectomy. Also remove a missed dead
extern declaration.

- In pgsql/src/backend/access/transam/twophase.c, actually, we need to
bump the format identifier on twophase files because of readjustment
of 2PC rmgr IDs for flatfile removal.

- Force VACUUM to recalculate oldestXmin even when we haven't changed
our own database's datfrozenxid, if the current value is old enough
to be forcing autovacuums or warning messages. This ensures that a
bogus value is replaced as soon as possible. Per a comment from
Heikki.

- Fix pg_ctl's readfile() to not go into infinite loop on an empty
file (could happen if either postgresql.conf or postmaster.opts is
empty). It's been broken since the C version was written for 8.0,
so patch all the way back. initdb's copy of the function is broken
in the same way, but it's less important there since the input files
should never be empty. Patch that in HEAD only, and also fix some
cosmetic differences that crept into that copy of the function. Per
report from Corry Haines and Jeff Davis.

- Fix subquery pullup to wrap a PlaceHolderVar around the entire
RowExpr that's generated for a whole-row Var referencing the
subquery, when the subquery is in the nullable side of an outer
join. The previous coding instead put PlaceHolderVars around the
elements of the RowExpr. The effect was that when the outer join
made the subquery outputs go to null, the whole-row Var produced
ROW(NULL,NULL,...) rather than just NULL. There are arguments afoot
about whether those things ought to be semantically
indistinguishable, but for the moment they are not entirely so, and
the planner needs to take care that its machinations preserve the
difference. Per bug #5025. Making this feasible required
refactoring ResolveNew() to allow more caller control over what is
substituted for a Var. I chose to make ResolveNew() a wrapper
around a new general-purpose function replace_rte_variables(). I
also fixed the ancient bogosity that ResolveNew might fail to set a
query's hasSubLinks field after inserting a SubLink in it. Although
all current callers make sure that happens anyway, we've had bugs of
that sort before, and it seemed like a good time to install a proper
solution. Back-patch to 8.4. The problem can be demonstrated clear
back to 8.0, but the fix would be too invasive in earlier branches;
not to mention that people may be depending on the subtly-incorrect
behavior. The 8.4 series is new enough that fixing this probably
won't cause complaints, but it might in older branches. Also, 8.4
shows the incorrect behavior in more cases than older branches do,
because it is able to flatten subqueries in more cases.

- In pgsql/src/bin/initdb/initdb.c, remove initdb's rather gratuitous
check to see if the backend created a flat password file, because it
never will anymore. We had managed to miss this during the recent
flat-file-ectomy because it only happens if --pwfile or --pwprompt
is specified to initdb. Apparently, few hackers use those.
Reported by Erik Rijkers.

- Update time zone data files to tzdata release 2009l: DST law changes
in Egypt, Mauritius, Bangladesh.

- Install a workaround for a longstanding gcc bug that allows SIGFPE
traps to occur for division by zero, even though the code is
carefully avoiding that. All available evidence is that the only
functions affected are int24div, int48div, and int28div, so patch
just those three functions to include a "return" after the ereport()
call. Backpatch to 8.4 so that the fix can be tested in production
builds. For older branches our recommendation will continue to be
to use -O1 on affected platforms (which are mostly non-mainstream
anyway).

- Final updates of release notes for 8.4.1, 8.3.8, 8.2.14, 8.1.18,
8.0.22, 7.4.26.

- In pgsql/src/backend/postmaster/pgstat.c, remove pgstat's
discrimination against MsgVacuum and MsgAnalyze messages. Formerly,
these message types would be discarded unless there was already a
stats hash table entry for the target table. However, the intent of
saving hash table space for unused tables was subverted by the fact
that the physical I/O done by the vacuum or analyze would result in
an immediately following tabstat message, which would create the
hash table entry anyway. All that we had left was surprising loss
of statistical data, as in a recent complaint from Jaime Casanova.
It seems unlikely that a real database would have many tables that
go totally untouched over the long haul, so the consensus is that
this "optimization" serves little purpose anyhow. Remove it, and
just create the hash table entry on demand in all cases.

- In pgsql/src/backend/Makefile, revert ill-considered restriction of
dtrace support to Solaris only.

- In pgsql/src/backend/Makefile, put back "ifeq ($(PORTNAME),
solaris)", this time with some documentation of why it's not as
broken as it appears on first glance.

- In pgsql/doc/src/sgml/ref/copy.sgml, add a note warning that COPY
BINARY is very datatype-specific. Per a complaint from Gordon
Shannon.

- Update the tznames reference files, and add IDT (Israel Daylight
Time) to the Default timezone abbreviation set. Back-port the the
current file set to all branches that contain tznames. This
includes adding SGT to the Default set in pre-8.4 releases. Joachim
Wieland.

Alvaro Herrera committed:

- This patch removes flatfiles.c for good. It doesn't change the
keeping of locks in dbcommands.c and user.c, because at least some
of them are still required. Regarding sync commits that previously
happen and now won't, I think the only case worth worrying about is
the one in vacuum.c. Do we need a ForceSyncCommit() in there? I'm
not sure if vacuum itself already forces sync commit.

- Remove flatfiles.c, which is now obsolete. Recent commits have
removed the various uses it was supporting. It was a performance
bottleneck, according to bug report #4919 by Lauris Ulmanis; seems
it slowed down user creation after a billion users.

Peter Eisentraut committed:

- In pgsql/src/backend/access/gist/gist.c, improve picksplit debug
message. Missed this earlier because the translation site was
broken for the 7.4 branch.

- Translation updates

Magnus Hagander committed:

- In pgsql/src/interfaces/ecpg/preproc/Makefile, revert Makefile
modification that broke the MSVC build.

Michael Meskes committed:

- In pgsql/src/interfaces/ecpg/ecpglib/misc.c, do not set connection
values if no connection is open.

- In ECPG, removed some variables no longer needed.

- In pgsql/src/interfaces/ecpg/ecpglib/execute.c, fixed incorrect
memory management.

Marc Fournier committed:

- Tag 8.4.1, 8.3.8, 8.2.14, 8.1.18, 8.0.22, and 7.4.26.

Heikki Linnakangas committed:

- In pgsql/src/backend/utils/adt/xml.c, fix encoding handling in xml
binary input function. If the XML header didn't specify an encoding
explicitly, we used to treat it as being in database encoding when
we parsed it, but then perform a UTF-8 -> database encoding
conversion on it, which was completely bogus. It's now consistently
treated as UTF-8.

- Tighten binary receive functions so that they reject values that the
text input functions don't accept either. While the backend can
handle such values fine, they can cause trouble in clients and in
pg_dump/restore. This is followup to the original issue on time
datatype reported by Andrew McNamara a while ago. Like that one,
none of these seem worth back-patching.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Heikki Linnakangas sent in a patch to make some of the binary recv
functions stricter.

KaiGai Kohei sent in another revision of the patch to add access
controls for large objects.

Greg Sabino Mullane sent in another revision of the patch to add YAML
as a formatting option for EXPLAIN output.

Kevin Grittner sent in two more revisions of the patch to add a LSB
conformant init script to contrib/start-scripts.

Peter Eisentraut sent in another patch to help with PL/PythonU data
type conversion improvements.

Jeff Janes sent in a patch to rearrange pgbench's handling of
#define'd constants.

Sam Mason sent in a patch which throws an error when anything but a
one-byte char is sent to the "char" type.

ITAGAKI Takahiro sent in a patch to add column-level triggers.

KaiGai Kohei sent in another revision of the patch to refactor ACL
facilities in PostgreSQL.

Zoltan Boszormenyi sent in another revision of the patch to implement
SELECT...FOR UPDATE [WAIT integer | NOWAIT].

Zoltan Boszormenyi sent in new revisions of the ECPG patches for
dynamic cursor name, sqlda, describe, and fixes for cursor scope error
handling in Informix compatibility mode.

Alvaro Herrera sent in a doc patch for logging at high loads.

Simon Riggs sent in a patch to skip SignalAutoVacuumWorkers(SIGTERM)
during recovery.

Zoltan Boszormenyi sent in another revision of the cursor-handling
patch for ECPG.

Robert Haas sent in another revision of the join removal patch.

Marko (johto) Tiikkaja sent in another revision of the patch to make
DML nodes in support of writeable CTEs.

KaiGai Kohei sent in another revision of the patch to add access
controls to large objects.