Der Originalartikel befindet sich unter:
== Wöchentlicher PostgreSQL Newsletter - 10. Januar 2010 ==
Glückwünsche an Alvaro Herrera für die Geburt seiner Tochter.
Die Hot Standby Usergruppe trifft sich nun remote am 13. Januar
um 16 Uhr UTC statt wie vorher angekündigt am 6. Januar. Weitere
== PostgreSQL Produkt Neuigkeiten ==
Bucardo, ein asynchrones Replikationssystem mit Master-Master
Fähigkeiten ist jetzt als Fedora-Paket verfügbar, CentOS und RHEL
pgfincore, ein Set von Funktionen um das Low-Level Management von
Relationen, unter Zuhilfenahme von mincore, zu handhaben und um
den Cache Memory zu erforschen, ist erschienen.
== PostgreSQL 8.5 Feature der Woche ==
Du kannst jetzt den SQL State für Anfragen, Fehler ect protokollieren,
indem du %e im log_line_prefix nutzt.
== PostgreSQL Jobs im Januar ==
== PostgreSQL Lokal ==
Die PUG des New York Capital Districts trifft sich zu ihrem erstem
Meeting am 7. Januar 2010.
Stefan Keller gibt einen Kurs "Einführung in PostGIS/PostgreSQL" an
der HSR Hochschule für Technik Rapperswil (HSR) am 14. Januar 2010
in Zürich, Schweiz. Details für diese und weitere Veranstaltungen
Standhelfer für die Open Day Linux Conf AU in Wellington, Neuseeland
am 23. Januar 2010, 11-14 Uhr, werden gesucht. Kontaktiere
selena -at- postgresql.org um zu helfen.
Der Call for Papers für East ist offen bis zum 30. Januar 2010.
PGDay Kuba 2010 findet vom 4.-6. Februar 2010 in La Havana an der
Universität für Wissenschaftliche Informatik statt. Teilnehmer aus
Kuba, der Dominikanischen Republik und Venezuela sind eingeladen.
FOSDEM 2010 findet am 6. und 7. Februar 2010 in Brüssel,
Die Deutsche PostgreSQL User Gruppe hat einen Stand im Open Source
Part auf der Cebit 2010 vom 02. bis 06. März 2010 in Hannover,
Die Chemnitzer Linuxtage finden am 13. und 14. März in
Chemnitz, Deutschland statt.
PgCon 2010 findet vom 20.-21. Mai 2010 in Ottawa statt mit Tutorials
vorher am 18. und 19. Der Call for Papers ist offen!
Der Call for Proposals für die OSCON ist eröffnet. OSCON findet in
Portland, Oregon vom 19. bis 23. Juli 2010 statt.
== PostgreSQL in den News ==
Planet PostgreSQL: http://planet.postgresql.org/
Dieser wöchentliche PostgreSQL Newsletter wurde erstellt von David
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.
== Reviews ==
== Angewandte Patches ==
Tom Lane committed:
- When estimating the selectivity of an inequality "column > constant"
or "column < constant", and the comparison value is in the first or
last histogram bin or outside the histogram entirely, try to fetch
the actual column min or max value using an index scan (if there is
an index on the column). If successful, replace the lower or upper
histogram bound with that value before carrying on with the
estimate. This limits the estimation error caused by moving min/max
values when the comparison value is close to the min or max. Per a
complaint from Josh Berkus. It is tempting to consider using this
mechanism for mergejoinscansel as well, but that would inject index
fetches into main-line join estimation not just endpoint cases. I'm
refraining from that until we can get a better handle on the costs
of doing this type of lookup.
- Improve PGXS makefile system to allow the module's makefile to
specify where to install DATA and DOCS files. This is mainly
intended to allow versioned installation, eg, install into
contrib/fooM.N/ rather than directly into contrib/. Mark
- Get rid of the need for manual maintenance of the initial contents
of pg_attribute, by having genbki.pl derive the information from the
various catalog header files. This greatly simplifies modification
of the "bootstrapped" catalogs. This patch finally kills genbki.sh
and Gen_fmgrtab.sh; we now rely entirely on Perl scripts for those
build steps. To avoid creating a Perl build dependency where there
was not one before, the output files generated by these scripts are
now treated as distprep targets, ie, they will be built and shipped
in tarballs. But you will need a reasonably modern Perl (probably
at least 5.6) if you want to build from a CVS pull. The changes to
the MSVC build process are untested, and may well break --- we'll
soon find out from the buildfarm. John Naylor, based on ideas from
Robert Haas and others.
- In pgsql/src/backend/Makefile, fix parallel-make timing problem.
- Remove too-smart-for-its-own-good optimization of not overwriting
the output files when they haven't changed. This confuses make
because the build fails to update the file timestamps, and so it
keeps on doing the action over again.
- Fix a few places where we needed -I. in CPPFLAGS to work properly in
VPATH builds. We had this already in several places, but not all.
- In pgsql/src/backend/catalog/genbki.pl, further code review for
genbki.pl. Improve comments, fix some rather random code choices,
don't slavishly duplicate the original pg_attribute.h's failure to
put an OID into Schema_pg_index entries.
- Fix genbki.pl and Gen_fmgrtab.pl to use PID-specific temp file
names, so that it's safe if a parallel make chooses to run two
concurrent copies. Also, work around a memory leak in some versions
- Add support for doing FULL JOIN ON FALSE. While this is really a
rather peculiar variant of UNION ALL, and so wouldn't likely get
written directly as-is, it's possible for it to arise as a result of
simplification of less-obviously-silly queries. In particular, now
that we can do flattening of subqueries that have constant outputs
and are underneath an outer join, it's possible for the case to
result from simplification of queries of the type exhibited in bug
#5263. Back-patch to 8.4 to avoid a functionality regression for
this type of query.
- In pgsql/contrib/auto_explain/auto_explain.c, add missing 'static'
- Fix missing <manvolnum> markers in dblink reference entries.
Silences complaints from "make man", and allows these man pages to
be included in man3/.
- Make the makefile pass $MAJORVERSION to genbki.pl, not $VERSION
which is overridden in the snapshot build script. $MAJORVERSION is
what it really wanted anyway, so we can tighten up the parsing of
- In pgsql/src/backend/catalog/genbki.pl, make error messages for bad
--set-version argument more useful. Per Stefan Kaltenbrunner.
- In pgsql/src/backend/utils/cache/spccache.c, access hash entry
before freeing it, not after.
- In pgsql/src/backend/utils/cache/spccache.c, fix spccache.c to not
suppose that a cache entry will live across database access, per
testing with CLOBBER_CACHE_ALWAYS. Minor other editorialization.
- Alter the configure script to fail immediately if the C compiler
does not provide a working 64-bit integer datatype. As recently
noted, we've been broken on such platforms since early in the 8.4
development cycle. Since it took nearly two years for anyone to
even notice, it seems that the rationale for continuing to support
such platforms has reached the point of non-existence. Rather than
thrashing around to try to make it work again, we'll just admit up
front that this no longer works. Back-patch to 8.4 since that
branch is also broken. We should go around to remove
INT64_IS_BUSTED support, but just in HEAD, so that seems like
material for a separate commit.
- Make configure check the version of Perl we're building with, and
reject versions < 5.8. Also, if there's no Perl, emit a warning
informing the user that he won't be able to build from a CVS pull.
This is exactly the same treatment we give Bison and Perl, and for
the same reasons.
- Fail in a nicer way if we have --with-perl and no Perl.
- Remove all the special-case code for INT64_IS_BUSTED, per decision
that we're not going to support that anymore. I did keep the
64-bit-CRC-with-32-bit-arithmetic code, since it has a performance
excuse to live. It's a bit moot since that's all ifdef'd out, of
- Fix (some of the) breakage introduced into query-cancel processing
by Hot Standby. It is absolutely not okay to throw an
ereport(ERROR) in any random place in the code just because
DoingCommandRead is set; interrupting, say, OpenSSL in the midst of
its activities is guaranteed to result in heartache. Instead of
that, undo the original optimizations that threw away
QueryCancelPending anytime we were starting or finishing a command
read, and instead discard the cancel request within
ProcessInterrupts if we find that there is no Hiroshi Saito reason
for forcing a cancel and we are DoingCommandRead. In passing, may I
once again condemn the practice of changing the code and not fixing
the adjacent comment that you just turned into a lie?
- In pgsql/src/backend/utils/adt/varbit.c, make bit/varbit substring()
treat any negative length as meaning "all the rest of the string".
The previous coding treated only -1 that way, and would produce an
invalid result value for other negative values. We ought to fix it
so that 2-parameter bit substring() is a different C function and
the 3-parameter form throws error for negative length, but that
takes a pg_proc change which is impractical in the back branches;
and in any case somebody might be relying on -1 working this way.
So just do this as a back-patchable fix.
- Fix 3-parameter form of bit substring() to throw error for negative
length, as required by SQL standard.
- In pgsql/src/backend/executor/execMain.c, fix oversight in
EvalPlanQualFetch: after failing to lock a tuple because someone
else has just updated it, we have to set priorXmax to that tuple's
xmax (ie, the XID of the other xact that updated it) before looping
back to examine the next tuple. Obviously, the next tuple in the
update chain should have that XID as its xmin, not the same xmin as
the preceding tuple that we had been trying to lock. The mismatch
would cause the EvalPlanQual logic to decide that the tuple chain
ended in a deletion, when actually there was a live tuple that
should have been found. I inserted this error when recently adding
logic to EvalPlanQual to make it lock tuples before returning them
(as opposed to the old method in which the lock would occur much
later, causing a great deal of work to be wasted if we only then
discover someone else updated it). Sigh. Per today's report from
Takahiro Itagaki of inconsistent results during pgbench runs.
- In pgsql/src/pl/plperl/GNUmakefile, fix makefile so it works for
- In pgsql/src/backend/executor/execQual.c, make ExecEvalFieldSelect
throw a more intelligible error if it's asked to extract a system
column, and remove a couple of lines that are useless in light of
the fact that we aren't ever going to support this case. There
isn't much point in trying to make this work because a tuple Datum
does not carry many of the system columns. Per experimentation with
a case reported by Dean Rasheed; we'll have to fix his problem
- Improve plpgsql's handling of record field references by forcing all
potential field references in SQL expressions to have RECFIELD
datum-array entries at parse time. If it turns out that the
reference is actually to a SQL column, the RECFIELD entry is
useless, but it costs little. This allows us to get rid of the
previous use of FieldSelect applied to a whole-row Param for the
record variable; which was not only slower than a direct RECFIELD
reference, but failed for references to system columns of a
trigger's NEW or OLD record. Per report and fix suggestion from
- In pgsql/src/pl/plpgsql/src/gram.y, improve plpgsql parsing to
report "foo is not a known variable", rather than a generic syntax
error, when seeing "foo := something" and foo isn't recognized.
This buys back most of the helpfulness discarded in my previous
patch by not throwing errors when a qualified name appears to match
a row variable but the last component doesn't match any field of the
row. It covers other cases where our error messages left something
to be desired, too.
- In pgsql/src/pl/plperl/GNUmakefile, remove unnecessary use of temp
file. Tim Bunce.
- In pgsql/src/include/utils/rel.h, some trivial adjustments in
comments for struct RelationData.
Heikki Linnakangas committed:
- Write an end-of-backup WAL record at pg_stop_backup(), and wait for
it at recovery instead of reading the backup history file. This is
more robust, as it stops you from prematurely starting up an
inconsisten cluster if the backup history file is lost for some
reason, or if the base backup was never finished with
pg_stop_backup(). This also paves the way for a simpler streaming
replication patch, which doesn't need to care about backup history
files anymore. The backup history file is still created and
archived as before, but it's not used by the system anymore. It's
just for informational purposes now. Bump PG_CONTROL_VERSION as the
location of the backup startpoint is now written to a new field in
pg_control, and catversion because initdb is required Original patch
by Fujii Masao per Simon's idea, with further fixes by Heikki
Magnus Hagander committed:
- In pgsql/src/include/storage/s_lock.h, add a Win64-specific
spin_delay() function. We can't use the same as before, since MSVC
on Win64 doesn't support inline assembly.
- In pgsql/src/include/storage/s_lock.h, use _mm_pause() for win64
spin_delay(), per note from Tsutomu Yamada.
- In pgsql/src/tools/msvc/Project.pm, disable linker warning 4197 on
Win64, that is caused by the fact that we export symbols both using
__declspec(dllexport) (via the PGDLLIMPORT macro) and using
full-dll-export. This works without warning on Win32, but not on
Win64. In passing, fix the fact that the framework could never deal
with more than one disbled linker warning - because MSVC wants
commas between linker warnings, and semicolons between compiler
- Move the default configuration for the MSVC build system to
config_default.pl, and allow using config.pl to override the
defaults. config.pl is removed from the repository, so changes there
will no longer show up when doing diff, and will not prevent
switching branches and such things. config.pl would normally be
used to override single values, but if an old-style config.pl is
read, it will override the entire default configuration, making it
- Create typedef pgsocket for storing socket descriptors. This
silences some warnings on Win64. Not using the proper SOCKET
datatype was actually wrong on Win32 as well, but didn't cause any
warnings there. Also create define PGINVALID_SOCKET to indicate an
invalid/non-existing socket, instead of using a hardcoded -1 value.
- In pgsql/doc/src/sgml/installation.sgml, update Windows installation
notes. pginstaller isn't used anymore, in favor of the one-click
installers. Make it clear that we support Windows 2000 and newer
with the native port, instead of first saying we support NT4 and
then saying we don't.
Andrew Dunstan committed:
- In pgsql/src/pl/plperl/plperl.c, check values passed back from
PLPerl to the database, via function return, trigger tuple
modification or SPI call, to ensure they are valid in the server
encoding. Along the way, replace uses of SvPV(foo, PL_na) with
SvPV_nolen(foo) as recommended in the perl docs. Bug report from
- In pgsql/src/backend/utils/mb/wchar.c, remove sometimes inaccurate
error hint about source of wrongly encoded data.
- Tidy up and refactor plperl.c. 1.Changed MULTIPLICITY check from
runtime to compiletime. No loads the large Config module.
2.Changed plperl_init_interp() to return new interp and not alter
the global interp_state 3. Moved plperl_safe_init() call into
check_interp(). 4. Removed plperl_safe_init_done state variable as
interp_state now covers that role. 5. Changed plperl_create_sub()
to take a plperl_proc_desc argument. 6. Simplified return value
handling in plperl_create_sub. 7. Changed perl.com link in the docs
to perl.org and tweaked wording to clarify that require, not use, is
what's blocked. 8. Moved perl code in large multi-line C string
literal macros out to plc_*.pl files. 9. Added a test2macro.pl
utility to convert the plc_*.pl files to macros in a perlchunks.h
file which is #included 10. Simplifed plperl_safe_init() slightly
11. Optimized pg_verifymbstr calls to avoid unneeded strlen()s.
Patch from Tim Bunce, with minor editing from me.
- In pgsql/src/tools/msvc/Mkvcbuild.pm, build perlchunks.h for plperl
- Provide regression testing for plperlu, and for plperl+plperlu
interaction. The latter are only run if the platform can run both
interpreters in the same backend.
ITAGAKI Takahiro committed:
- In pgsql/src/backend/utils/adt/genfile.c, verify input in
- In pgsql/contrib/pgbench/pgbench.c, add verification of variable
names in pgbench. Variables must consist of only alphabets,
numerals and underscores. We had allowed to set variables with
invalid names, but could not refer them in queries. Thanks to
Robert Haas for the review.
- In pgsql/contrib/pgbench/pgbench.c, adjust headers to allow pgbench
to be built with PGXS.
- Support rewritten-based full vacuum as VACUUM FULL. Traditional
VACUUM FULL was renamed to VACUUM FULL INPLACE. Also added a new
option -i, --inplace for vacuumdb to perform FULL INPLACE vacuuming.
Since the new VACUUM FULL uses CLUSTER infrastructure, we cannot use
it for system tables. VACUUM FULL for system tables always fall back
into VACUUM FULL INPLACE silently. Itagaki Takahiro, reviewed by
Jeff Davis and Simon Riggs.
- In pgsql/src/backend/commands/cluster.c, silence compiler warning
about uninitialized variables. This initialization is not necessary
needed, but some compilers complain about it.
- Add buffer access counters to pg_stat_statements. This uses the
same infrastructure with EXPLAIN BUFFERS to support
columns in the pg_stat_statements view. The dumped file format also
updated. Thanks to Robert Haas for the review.
- In pgsql/src/include/executor/instrument.h, pgBufferUsage needs
PGDLLIMPORT for pg_stat_statements on Windows.
Michael Meskes committed:
- Applied patch by Zoltan Boszormenyi to add sqlda support to ecpg in
both native and compatiblity mode.
- Adjusted CVS headers for ECPG.
- ECPG still not correct.
- In ECPG, third try. It seems my two checkouts need some cleaning up.
- Ah, should read the file completely. I got so used to git that I
almost forgot about the cvs stuff.
- And then forgot to commit the fixed files. ARGH!
- And another one of the same problem.
- And another one.
- Applied Zoltan Boszormenyi's patch to remove hardware dependant
offset logging and superfluous include files.
- In ECPG, removed more inttypes.h stuff.
- In ECPG, removed test case using nan as float value because printf's
output for nan is OS specific with some distinguishing between
signaling and quiet nans. It's not really importnat for us here
- In ECPG, remove __FUNCTION__ keyword that is not recognized by some
- In pgsql/src/interfaces/ecpg/ecpglib/sqlda.c, replaced int64_t with
int64 as Andrew Dunstan suggested.
- In pgsql/src/interfaces/ecpg/ChangeLog, add Zoltan Boszormenyi's
recent ECPG cleanup patch.
- Backported fix for protecting ecpg against applications freeing
strings to 8.4.
Robert Haas committed:
- Support ALTER TABLESPACE name SET/RESET ( tablespace_options ).
This patch only supports seq_page_cost and random_page_cost as
parameters, but it provides the infrastructure to scalably support
many more. In particular, we may want to add support for
effective_io_concurrency, but I'm leaving that as future work for
now. Thanks to Tom Lane for design help and Alvaro Herrera for the
- Improve a couple of comments relating to large object snapshot
- Further fixes for per-tablespace options patch. Add missing varlena
header to TableSpaceOpts structure. And, per Tom Lane, instead of
calling tablespace_reloptions in CacheMemoryContext, call it in the
caller's memory context and copy the value over afterwards, to
reduce the chances of a session-lifetime memory leak.
- In pgsql/src/backend/utils/cache/relcache.c, document why we copy
reloptions into CacheMemoryContext after-the-fact.
- In pgsql/doc/src/sgml/catalogs.sgml, document
pg_tablespace.spcoptions. This should have been part of the
original tablespace-options patch, but I overlooked the need to
update this portion of the documentation.
- Remove partial, broken support for NULL pointers when fetching
attributes. Previously, fastgetattr() and heap_getattr() tested
their fourth argument against a null pointer, but any attempt to use
them with a literal-NULL fourth argument evaluated to *(void *)0,
resulting in a compiler error. Remove these NULL tests to avoid
leading future readers of this code to believe that this has a
chance of working. Also clean up related legacy code in
nocachegetattr(), heap_getsysattr(), and nocache_index_getattr().
The new coding standard is that any code which calls a getattr-type
function or macro which takes an isnull argument MUST pass a valid
boolean pointer. Per discussion with Bruce Momjian, Tom Lane,
Bruce Momjian committed:
- In pgsql/src/backend/commands/tablespace.c, use OIDCHARS for oid
character length, rather than '10', in tablespace code.
- Remove tabs in SGML. Move OIDCHARS to proper include file.
- Implement pg_dump --only-analyze for use by pg_migrator to only
analyze all databases.
- Preserve relfilenodes: Add support to pg_dump --binary-upgrade to
preserve all relfilenodes, for use by pg_migrator.
- In pgsql/src/include/catalog/catversion.h, update catalog version
for recent relfilenode patch, so pg_migrator can identify the new
- In pgsql/src/bin/pg_dump/pg_dumpall.c, remove erroneous comma added
to pg_dumpall tablespace query when running with PG version 8.2-8.4.
- binary upgrade: Preserve relfilenodes for views and composite types
--- even though we don't store data in, them, they do consume
relfilenodes. Bump catalog version.
- In pgsql/src/bin/scripts/vacuumdb.c, corrected CVS entry:
--only-analyze mode is for _vacuumdb_, not pg_dump.
- PG_MAJORVERSION: For simplicity, use PG_MAJORVERSION rather than
PG_VERSION for creation of the PG_VERSION file.
- In pgsql/src/backend/commands/tablespace.c, clarify
- In pgsql/src/backend/commands/tablespace.c, more tablespace.c
- Rename new vacuumdb option to --analyze-only from --only-analyze.
- Use -Z for vacuumdb --analyze-only, rather than -o.
Simon Riggs committed:
- During Hot Standby, set DatabasePath correctly during relcache init
file deletion, so that we attempt to unlink the correct filepath.
unlink() errors are ignorable there, so lack of a DatabasePath
initialization step did not cause visible problems until a related
bug showed up on Solaris. Code refactored from xact_redo_commit()
to ProcessCommittedInvalidationMessages() in inval.c. Recovery may
replay shared invalidation messages for many databases, so we cannot
SetDatabasePath() once as we do in normal backends. Read the
databaseid from the shared invalidation messages, then set
DatabasePath temporarily before calling
RelationCacheInitFileInvalidate(). Problem report by Robert Treat,
analysis and fix by me.
- During Hot Standby, fix drop database when sessions idle.
Previously we only cancelled sessions that were in-transaction.
Simple fix is to just cancel all sessions without waiting. Doing it
this way avoids complicating common code paths, which would not be
worth the trouble to cover this rare case. Problem report and fix
by Andres Freund, edited somewhat by me
- In pgsql/doc/src/sgml/backup.sgml, docs for behaviour change of drop
database during Hot Standby Adding missing docs for previous Hot
== Abgelehnte Patches (bis jetzt) ==
No one was disappointed this week :-)
== Eingesandte Patches ==
KaiGai Kohei sent in another revision of the patch to disallow ALTER
TABLE...ALTER COLUMN...RENAME TO... in situations where the column
originates from more than one distinct parent in its inheritance
Hitoshi Harada sent in another revision of the patch to extend
windowing functions to include (ROWS|RANGE) (PRECEDING|FOLLOWING).
Zoltan Boszormenyi sent in another revision of the patch to add
DESCRIBE OUTPUT to ECPG.
Guillaume Lelarge sent in two approaches to the patch which lets libpq
have named parameters.
Robert Haas sent in two WIP patches to add attribute options,
following the model of reloptions and tablespace options.
Pavel Stehule sent in another revision of the patch to add
quote_literal and quote_ident behavior to psql variables.
Fujii Masao sent in another revision of the patch to add a new
transaction log record which indicates that a WAL has been skipped.
Marko (johto) Tiikkaja sent in another revision of the writeable CTE
patch, this time allowing INSERT, UPDATE or DELETE at the top level.
Magnus Hagander sent in a patch which fixes an issue in the win32
Arie Bikker sent in three revisions of a patch to improve xpath
Fujii Masao sent in a patch to add a "replication" keyword to
John Naylor sent in another patch to fix the BKI refactor patch.
Simon Riggs sent in a patch to fix an issue in hot standby where
cancelling queries misbehaved.
Alex Hunsaker sent in a patch to set oom to -17 in the startup script
so the OOM killer won't kill.
ITAGAKI Takahiro sent in a patch to fix an issue where paths have
Leonardo F sent in a patch to add substring and replace() to bit
Alex Hunsaker sent in a patch to avoid the Linux OOM killer.
Joachim Wieland sent in another revision of the patch to fix cases in
LISTEN/NOTIFY when the queue is full
Kevin Grittner sent in a patch intended to be infrastructure for a
better implementation of SERIALIZABLE isolation.
Tim Bunce sent in a patch which adds a flock of features to PL/Perl.
Joachim Wieland sent in a patch which implements synchronized
snapshots, which will among other things allow pg_dump to work in
Heikki Linnakangas sent in another revsion of the streaming
Magnus Hagander sent in a patch to enable RADIUS authentication.
Peter Eisentraut sent in a patch to implement typed tables per