== Wöchentlicher PostgreSQL Newsletter - 06. September 2009 ==
Neue Umfrage: Welches 8.5 Alpha1 Feature findest du am
Registrierung für die PostgreSQL Konferenz West ist jetzt offen:
Das PostgreSQL Live CD Projekt hat eine Mailingliste gestartet, um
eine Live CD mit Kommentaren von der Community zu erstellen.
== PostgreSQL Produkt Neuigkeiten ==
GTpB portalBase, eine auf PostgreSQL basierende Web Plattform, ist jetzt
unter der GPL verfügbar.
Das PostgreSQL RPM Building Projekt hat eine andere Version der
PostgreSQL Live CD erstellt, diesmal mit PostgreSQL 8.4.0 und
AnySQL Maestro 9.8, ein GUI zum Administrieren und Entwickeln unter
Windows, ist erschienen.
pg51g, ein Werkzeug zum Vergleichen von Daten für PostgreSQL,
pgDesigner 1.2.11, ein grafisches Programm zum designen von Schemas
für PostgreSQL ist erschienen.
== PostgreSQL Jobs im September ==
== PostgreSQL Lokal ==
SFPUG präsentiert: Nathan Boley über Statistiken und Postgres. Am 8.
September 2009. Details und der Live Webcast unter:
Es findet eine Konferenz in Seattle, Washington, USA am 16.-18.
Oktober 2009 statt. Der CfP ist immer noch offen.
PGCon Brasilien wird am 23.-24. Oktober 2009 auf dem Unicamp in
Campinas, Sao Paulo, stattfinden. Der CfP ist eröffnet!
PGDay.EU 2009 wird an der Telecom ParisTech Universität in Paris,
Frankreich, am 6. und 7. November 2009 stattfinden. Der CfP ist
eröffnet. Vorträge einreichen!
OpenSQL Camp in Portland sucht Sponsoren. Bereite deine Reisepläne
jetzt vor! :)
Die 10. jährliche JPUG Konferenz findet am 20-21. November 2009 in
Tokio, Japan, statt.
FOSDEM 2010 wird am 6./7. Februar 2010 in Brüssel stattfinden.
== PostgreSQL in den News ==
Planet PostgreSQL: http://planet.postgresql.org/
Dieser wöchentliche PostgreSQL Newsletter wurde erstellt von David
Fetter, Josh Berkus und Devrim GUNDUZ.
Sende Neuigkeiten und Ankündigungen bis Sonntag, 15 Uhr Pazifischer
Zeit. Bitte sende englische Beiträge an firstname.lastname@example.org, deutsche an
email@example.com, italienische an firstname.lastname@example.org.
== Angewandte 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
- Bump catversion for flat-file-ectomy. Also remove a missed dead
- 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
- 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
- Final updates of release notes for 8.4.1, 8.3.8, 8.2.14, 8.1.18,
- 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
- 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
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
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.
== Abgelehnte Patches (bis jetzt) ==
No one was disappointed this week :-)
== Eingesandte Patches ==
Heikki Linnakangas sent in a patch to make some of the binary recv
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
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)
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.
Andreas 'ads' Scherbaum
Deutsche PostgreSQL User Group: http://www.pgug.de