== Wöchentlicher PostgreSQL Newsletter - 28. August 2011 ==

== Wöchentlicher PostgreSQL Newsletter - 28. August 2011 ==

am 01.09.2011 01:58:45 von adsmail

Der Originalartikel befindet sich unter:


== Wöchentlicher PostgreSQL Newsletter - 28. August 2011 ==

PostgreSQL 9.1 RC1 ist verfügbar. Testen!

== PostgreSQL Produkt Neuigkeiten ==

EnterpriseDB Postgres Plus Cloud Server erschienen.

EnterpriseDB Enterprise Manager, ein Werkzeug das mit
PostgreSQL arbeitet, ist erschienen.

oracle_fdw beta, ein Foreign Data Wrapper für Oracle,
ist erschienen.

pgpool-II 3.1.0 beta2, ein Connection Pooler und mehr,
ist erschienen.

pg_sample 0.06, ein Werkzeug zum Exportieren von kleinen
Beispieldatensätzen aus einer großen PostgreSQL Datenbank, ist er=

Pyrseas 0.3.1, eine Werkzeugsammlung für PostgreSQL Versionskontrolle,=
ist auf PGXN erschienen.

Version 1.20.1 von tail_n_mail, ein PostgreSQL Logbeobachter,
ist erschienen.

== PostgreSQL Jobs im August ==

http://archives.postgresql.org/pgsql-jobs/2011-08/threads.ph p

== PostgreSQL Lokal ==

Postgres Open 2011, eine Konferenz die sich auf den Umbruch der
Datenbankindustrie durch PostgreSQL konzentriert, wird vom 14. bis 16.
September 2011 in Chicago, Illinois im Westin Michigan Avenue
Hotel stattfinden.

PG-Day Denver 2011 findet am Freitag, dem 21. Oktober 2011 auf dem
Auraria Campus in der Nähe von Downtown Denver, Colorado statt.

PostgreSQL Conference West (#PgWest) findet vom 27. bis 30. September
2011 im San Jose Convention Center in Jan Jose, Kalifornen, USA statt.

PostgreSQL Conference Europe 2011 findet vom 18. bis
21. Oktober in Amsterdam statt.

pgbr findet in Sao Paulo, Brazilien, am 3. und 4. November 2011 statt.

PGConf.DE 2011 ist die Deutschsprachige PostgreSQL Konferenz
und wird am 11. November 2011 im Rheinischen Industriemuseum
in Oberhausen, Deutschland, stattfinden. Der Call for Papers ist offen.

== PostgreSQL in den News ==

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

Dieser wöchentliche PostgreSQL Newsletter wurde erstellt von David Fet=

Sende Neuigkeiten und Ankündigungen bis Sonntag, 15 Uhr Pazifischer
Zeit. Bitte sende englische Beiträge an david@fetter.org, deutsche an
pwn@pgug.de, italienische an pwn@itpug.org, spanische an pwn@arpug.com.ar.

== Reviews ==

== Angewandte Patches ==

Tom Lane pushed:

- Fix trigger WHEN conditions when both BEFORE and AFTER triggers
exist. Due to tuple-slot mismanagement, evaluation of WHEN
conditions for AFTER ROW UPDATE triggers could crash if there had
been a BEFORE ROW trigger fired for the same update. Fix by not
trying to overload the use of estate->es_trig_tuple_slot. Per
report from Yoran Heling. Back-patch to 9.0, when trigger WHEN
conditions were introduced.
http://git.postgresql.org/pg/commitdiff/b33f78df17c32364d51f 6e5128f8d81d7d3=

- Fix handling of extension membership when filling in a shell
operator. The previous coding would result in deleting and not
re-creating the extension membership pg_depend rows, since there was
no CommandCounterIncrement that would allow
recordDependencyOnCurrentExtension to see that the deletion had
happened. Make it work like the shell type case, ie, keep the
existing entries (and then throw an error if they're for the wrong
extension). Per bug #6172 from Hitoshi Harada. Investigation and
fix by Dimitri Fontaine.
http://git.postgresql.org/pg/commitdiff/660a081c3f6892dae353 136fa0883cd3c69=

- Fix overoptimistic assumptions in column width estimation for
subqueries. set_append_rel_pathlist supposed that, while computing
per-column width estimates for the appendrel, it could ignore child
rels for which the translated reltargetlist entry wasn't a Var.
This gave rise to completely silly estimates in some common cases,
such as constant outputs from some or all of the arms of a UNION
ALL. Instead, fall back on get_typavgwidth to estimate from the
value's datatype; which might be a poor estimate but at least it's
not completely wacko. That problem was exposed by an Assert in
set_subquery_size_estimates, which unfortunately was still
overoptimistic even with that fix, since we don't compute
attr_widths estimates for appendrels that are entirely excluded by
constraints. So remove the Assert; we'll just fall back on
get_typavgwidth in such cases. Also, since
set_subquery_size_estimates calls set_baserel_size_estimates which
calls set_rel_width, there's no need for set_subquery_size_estimates
to call get_typavgwidth; set_rel_width will handle it for us if we
just leave the estimate set to zero. Remove the unnecessary code.
Per report from Erik Rijkers and subsequent investigation.
http://git.postgresql.org/pg/commitdiff/43f0c20839aa82705700 e4de5bb452b7f04=

- Make CREATE EXTENSION check schema creation permissions. When
creating a new schema for a non-relocatable extension, we neglected
to check whether the calling user has permission to create schemas.
That didn't matter in the original coding, since we had already
checked superuserness, but in the new dispensation where users need
not be superusers, we should check it. Use CreateSchemaCommand()
rather than calling NamespaceCreate() directly, so that we also
enforce the rules about reserved schema names. Per complaint from
KaiGai Kohei, though this isn't the same as his patch.
http://git.postgresql.org/pg/commitdiff/d4aa491493e6cfa7542d 16deba4018c2fd7=

- Fix multiple bugs in extension dropping. When we implemented
extensions, we made findDependentObjects() treat EXTENSION
dependency links similarly to INTERNAL links. However, that logic
contained an implicit assumption that an object could have at most
one INTERNAL dependency, so it did not work correctly for objects
having both INTERNAL and DEPENDENCY links. This led to failure to
drop some extension member objects when dropping the extension.
Furthermore, we'd never actually exercised the case of recursing to
an internally-referenced (owning) object from anything other than a
NORMAL dependency, and it turns out that passing the incoming
dependency's flags to the owning object is the Wrong Thing. This
led to sometimes dropping a whole extension silently when we should
have rejected the drop command for lack of CASCADE. Since we
obviously were under-testing extension drop scenarios, add some
regression test cases. Unfortunately, such test cases require some
extensions (duh), so we can't test for problems in the core
regression tests. I chose to add them to the earthdistance contrib
module, which is a good test case because it has a dependency on the
cube contrib module. Back-patch to 9.1. Arguably these are
pre-existing bugs in INTERNAL dependency handling, but since it
appears that the cases can never arise pre-9.1, I'll refrain from
back-patching the logic changes further than that.
http://git.postgresql.org/pg/commitdiff/cb5c2ba2d82688d29b59 02d86b993a54355=

- Avoid locale dependency in expected output. We'll have to settle
for just listing the extensions' data types, since function
arguments seem to sort differently in different locales. Per
buildfarm results.
http://git.postgresql.org/pg/commitdiff/ba69b419a8015986d018 e25173f8cf4233a=

- Fix pgxs.mk to always add --dbname=3D$(CONTRIB_TESTDB) to
REGRESS_OPTS. The previous coding resulted in contrib modules
unintentionally overriding the use of CONTRIB_TESTDB. There seems
no particularly good reason to allow that (after all, the makefile
can set CONTRIB_TESTDB if that's really what it intends). In
passing, document REGRESS_OPTS where the other pgxs.mk options are
documented. Back-patch to 9.1 --- in prior versions, there were no
cases of contrib modules setting REGRESS_OPTS without including the
--dbname switch, so while the coding was fragile there was no actual
http://git.postgresql.org/pg/commitdiff/d1d388603e4f9233d3e0 1847405b239972a=

- Fix pgstatindex() to give consistent results for empty indexes. For
an empty index, the pgstatindex() function would compute 0.0/0.0 for
its avg_leaf_density and leaf_fragmentation outputs. On machines
that follow the IEEE float arithmetic standard with any care, that
results in a NaN. However, per report from Rushabh Lathia,
Microsoft couldn't manage to get this right, so you'd get a bizarre
error on Windows. Fix by forcing the results to be NaN explicitly,
rather than relying on the division operator to give that or the
snprintf function to print it correctly. I have some doubts that
this is really the most useful definition, but it seems better to
remain backward-compatible with those platforms for which the
behavior wasn't completely broken. Back-patch to 8.2, since the
code is like that in all current releases.
http://git.postgresql.org/pg/commitdiff/af7d181298fbcd4eb225 ee349598edd4611=

- Add a regression test for pgstattuple. This is mainly to prove that
the NaN fix actually works cross-platform.
http://git.postgresql.org/pg/commitdiff/bd165757f4e0914efb80 8927482c46f719a=

- Add "%option warn" to all flex input files that lacked it. This is
recommended in the flex manual, and there seems no good reason not
to use it everywhere.
http://git.postgresql.org/pg/commitdiff/2e95f1f002bc3f0504df fa6d9ffed0dc914=

- Fix psql lexer to avoid use of backtracking. Per previous
experimentation, backtracking slows down lexing performance
significantly (by about a third). It's usually pretty easy to
avoid, just need to have rules that accept an incomplete construct
and do whatever the lexer would have done otherwise. The
backtracking was introduced by the patch that added quoted variable
substitution. Back-patch to 9.0 where that was added.
http://git.postgresql.org/pg/commitdiff/77ce50a40364a3605f77 5d3f0efca2e1caa=

- Add makefile rules to check for backtracking in backend and psql
lexers. Per discussion, we should enforce the policy of "no
backtracking" in these performance-sensitive scanners.
http://git.postgresql.org/pg/commitdiff/ecf248737a4c0705bf7d 79fdd52b5271618=

- Support non-ASCII letters in psql variable names. As in the
backend, the implementation actually accepts any non-ASCII
character, but we only document that you can use letters.
http://git.postgresql.org/pg/commitdiff/e86fdb0ab224eaa73d90 7ab16a2dd0e0058=

- Clean up weird corner cases in lexing of psql meta-command
arguments. These changes allow backtick command evaluation and psql
variable interpolation to happen on substrings of a single
meta-command argument. Formerly, no such evaluations happened at
all if the backtick or colon wasn't the first character of the
argument, and we considered an argument completed as soon as we'd
processed one backtick, variable reference, or quoted substring. A
string like 'FOO'BAR was thus taken as two arguments not one, not
exactly what one would expect. In the new coding, an argument is
considered terminated only by unquoted whitespace or backslash.
Also, clean up a bunch of omissions, infelicities and outright
errors in the psql documentation of variables and metacommand
argument syntax.
http://git.postgresql.org/pg/commitdiff/928311a463d480ca566e 2905a369ac6aa0c=

- Fix potential memory clobber in tsvector_concat().
tsvector_concat() allocated its result workspace using the
"conservative" estimate of the sum of the two input tsvectors'
sizes. Unfortunately that wasn't so conservative as all that,
because it supposed that the number of pad bytes required could not
grow. Which it can, as per test case from Jesper Krogh, if there's
a mix of lexemes with positions and lexemes without them in the
input data. The fix is to assume that we might add a
not-previously-present pad byte for each and every lexeme in the two
inputs; which really is conservative, but it doesn't seem worthwhile
to try to be more precise. This is an aboriginal bug in
tsvector_concat, so back-patch to all versions containing it.
http://git.postgresql.org/pg/commitdiff/00eb036c111b8f72a34c a729efccd785761=

- Improve comments describing tsvector data structure.
http://git.postgresql.org/pg/commitdiff/40271811cb9c4906041a fc21a3b2c2f31f5=

- Ensure we discard unread/unsent data when abandoning a connection
attempt. There are assorted situations wherein PQconnectPoll() will
abandon a connection attempt and try again with different parameters
(eg, SSL versus not SSL). However, the code forgot to discard any
pending data in libpq's I/O buffers when doing this. In at least
one case (server returns E message during SSL negotiation), there is
unread input data which bollixes the next connection attempt. I
have not checked to see whether this is possible in the other cases
where we close the socket and retry, but it seems like a matter of
good defensive programming to add explicit buffer-flushing code to
all of them. This is one of several issues exposed by Daniel
Farina's report of misbehavior after a server-side fork failure.
This has been wrong since forever, so back-patch to all supported
http://git.postgresql.org/pg/commitdiff/724e30c9f886efd852f7 14d47c56336ffa6=

- Don't assume that "E" response to NEGOTIATE_SSL_CODE means pre-7.0
server. These days, such a response is far more likely to signify a
server-side problem, such as fork failure. Reporting "server does
not support SSL" (in sslmode=3Drequire) could be quite misleading.
But the results could be even worse in sslmode=3Dprefer: if the
problem was transient and the next connection attempt succeeds,
we'll have silently fallen back to protocol version 2.0, possibly
disabling features the user needs. Hence, it seems best to just
eliminate the assumption that backing off to non-SSL/2.0 protocol is
the way to recover from an "E" response, and instead treat the
server error the same as we would in non-SSL cases. I tested this
change against a pre-7.0 server, and found that there was a second
logic bug in the "prefer" path: the test to decide whether to make a
fallback connection attempt assumed that we must have opened
conn->ssl, which in fact does not happen given an "E" response.
After fixing that, the code does indeed connect successfully to
pre-7.0, as long as you didn't set sslmode=3Drequire. (If you did,
you get "Unsupported frontend protocol", which isn't completely off
base given the server certainly doesn't support SSL.) Since there
seems no reason to believe that pre-7.0 servers exist anymore in the
wild, back-patch to all supported branches.
http://git.postgresql.org/pg/commitdiff/a49fbaaf8d461ff91912 c30b3563d546494=

- Include $cc_string in the info reported by a configure run. Without
this, it's not very easy to tell which compiler version a buildfarm
animal is actually using at the moment.
http://git.postgresql.org/pg/commitdiff/2c5d6f1fb570db1a2875 32d3291d284710e=

- Be more user-friendly about unsupported cases for parallel
pg_restore. If we are unable to do a parallel restore because the
input file is stdin or is otherwise unseekable, we should complain
and fail immediately, not after having done some of the restore.
Complaining once per thread isn't so cool either, and the messages
should be worded to make it clear this is an unsupported case not
some weird race-condition bug. Per complaint from Lonni Friedman.
Back-patch to 8.4, where parallel restore was introduced.
http://git.postgresql.org/pg/commitdiff/d6e7abe45a64378113c1 c717a831b7aac9c=

- Actually, all of parallel restore's limitations should be tested
earlier. On closer inspection, whining in restore_toc_entries_parallel
is really much too late for any user-facing error case. The right
place to do it is at the start of RestoreArchive(), before we've
done anything interesting (such as trying to DROP all the targets
...) Back-patch to 8.4, where parallel restore was introduced.
http://git.postgresql.org/pg/commitdiff/6e1f1fee97839599cf59 f37f7051786a09f=

Bruce Momjian pushed:

- Simplify errno generating in thread testing program.
http://git.postgresql.org/pg/commitdiff/e319ec4b7378e047e6bb 92bd1bb7ff7d515=

- Properly call strerror() in thread test; add comments.
http://git.postgresql.org/pg/commitdiff/5473f283f501ff9f5e38 e89d3a2e89f738a=

- Mark cpluspluscheck as excutable in git.
http://git.postgresql.org/pg/commitdiff/034dda61ddf83a2f9762 71ecb0cc5ee0151=

- Add missing include so include file compiles cleanly on its own.
http://git.postgresql.org/pg/commitdiff/2ab15afcdd28ce3d52a9 b01d41f67687ac7=

- In pg_upgrade, limit schema name filter to include toast tables.
Bug introduced recently when trying to filter out temp tables.
Backpatch to 9.0 and 9.1.
http://git.postgresql.org/pg/commitdiff/eb013ede590dc62ca5b5 2144ff41e7fd6e4=

- In pgrminclude, make skipped include names constent and skip files
with #if/#ifdefs.
http://git.postgresql.org/pg/commitdiff/4399e817492222623c5e 7541ca8488ae460=

- In pgrminclude, add code to skip includes with a marker comment.
http://git.postgresql.org/pg/commitdiff/6f9afc351b81a46ce9dc 0f48c8a4c0af3de=

- Fix pgrminclude regex pattern.
http://git.postgresql.org/pg/commitdiff/910725b49ddf5c827658 717f458fb14d004=

- do include files first
http://git.postgresql.org/pg/commitdiff/987214b4d5118a6adf51 945d6e266bb464c=

- Modify pgrminclude to include all code, even in #if blocks. Process
.h include files before .c files. Mark some includes as needed to
be ignored by pgrminclude.
http://git.postgresql.org/pg/commitdiff/f8e41abd8a11d562c3ed 97427d6dec9b383=

- Cleanup of script.
http://git.postgresql.org/pg/commitdiff/c6e9da17a1820bde0c51 c8a23b24dff6b8b=

- Add another marker.
http://git.postgresql.org/pg/commitdiff/455d08b2855af0e7748b bc7602605ceb484=

- Fix #if blocks.
http://git.postgresql.org/pg/commitdiff/ac5f11e0ec23e70a5749 c5caee890f0b9ad=

- Add markers.
http://git.postgresql.org/pg/commitdiff/48423d949f6bf8f21505 a00571aa2f95599=

- Add markers for skips.
http://git.postgresql.org/pg/commitdiff/f8fc37b337982fb97de9 504f00381d1a545=

- Add missing includes after pgrminclude run.
http://git.postgresql.org/pg/commitdiff/f261deb4b41e73f61270 5c0f852fdb132d7=

- Fix missing pgdefine detection in pgrminclude.
http://git.postgresql.org/pg/commitdiff/8b0f0822fd5111118a7d 147344ee1253acb=

- Add postgres.h to *.c files for pg_upgrade, ltree, and btree_gist,
and remove from local *.h files. Per suggestion from Alvaro
http://git.postgresql.org/pg/commitdiff/f1312b5ed32630ae479e 61e2a58cfac56ae=

- Change references of CVS to .git.
http://git.postgresql.org/pg/commitdiff/e7088713cd2bb6ce2ce6 30d07feb45b0113=

- Add another pgdefine path check, and a cvs-git change.
http://git.postgresql.org/pg/commitdiff/68c019a5383bae89794b 8a37001a7b8801e=

- Add support for #elif to pgrminclude.
http://git.postgresql.org/pg/commitdiff/d010391ac8f706e17998 671534ca1230f68=

- Allow more include files to be compiled in their own by adding
missing include dependencies. Modify pgcompinclude to skip a common
fcinfo error.
http://git.postgresql.org/pg/commitdiff/4bd7333b14786a2d7571 95e907709d2aee1=

- Modify pgrminclude -v to report include files that can't be compiled
on their own. Avoid compile problems with defines being redefined
after the removal of the #if blocks. Change script to use shell
functions for simplicity.
http://git.postgresql.org/pg/commitdiff/94db6664e2238c4f3879 be67bcded085d5a=

Robert Haas pushed:

- Typo fix.
http://git.postgresql.org/pg/commitdiff/7488936478cbe2de19a9 4cb9fbde78e6cd6=

- Adjust CREATE DOMAIN example for standard_conforming_strings=3Don.
Noted by Hitoshi Harada.
http://git.postgresql.org/pg/commitdiff/6fc726adac3001f8e1f3 215ad4c874f58cd=

- Tweak postgresql.conf.sample's comments on listen_addresess. This
makes it slightly more clear that '*' is not part of the default
value, in case that wasn't obvious. As requested by Dougal
http://git.postgresql.org/pg/commitdiff/48bc57657dc9a6e1091e e0dc837caccfb32=

- Properly quote SQL/MED generic options in pg_dump output. Shigeru
http://git.postgresql.org/pg/commitdiff/0a803d65e4ae1f6817dc c196f7e59f36e43=

- Change format of SQL/MED generic options in psql backslash commands.
Rather than dumping out the raw array as PostgreSQL represents it
internally, we now print it out in a format similar to the one in
which the user input it, which seems a lot more user friendly.
Shigeru Hanada
http://git.postgresql.org/pg/commitdiff/0371d4d0632221957a60 d4cdb70a898caf7=

Heikki Linnakangas pushed:

- Add recovery.conf to the index in the user manual. Fujii Masao
http://git.postgresql.org/pg/commitdiff/6c6a4153338c2b2e3320 3bfb02a26ff8e3d=

Peter Eisentraut pushed:

- Use consistent format for reporting GetLastError(). Use something
like "error code %lu" for reporting GetLastError() values on
Windows. Previously, a mix of different wordings and formats were
in use.
http://git.postgresql.org/pg/commitdiff/1af55e2751cdf3bf3bf2 5993c34be1fa9ad=

- Build src/ before contrib/ in make world. This fixes failures under
parallel make when contrib modules use a generated backend header
file (such as errcodes.h).
http://git.postgresql.org/pg/commitdiff/4803de6f8932e2f2b96b b1243ba07a05cd2=

- Spelling improvement
http://git.postgresql.org/pg/commitdiff/3104cc89be65614ef3d0 748e1cc19fb5394=

- Implement the information schema with_hierarchy column. In
PostgreSQL, this is included in the SELECT privilege, so show YES or
NO depending on whether SELECT is granted.
http://git.postgresql.org/pg/commitdiff/fd5b397ca4963bf91a54 678be51207bf827=

- Document minimum required version of DocBook XSL stylesheets
http://git.postgresql.org/pg/commitdiff/f44d275b6df71281a8a4 068aa8f468f4d2b=

Alvaro Herrera pushed:

- Update FK alternative test output to new whitespace rules. With
these changes, the isolation tests pass again on isolation levels
serializable and repeatable read. Author: Kevin Grittner
http://git.postgresql.org/pg/commitdiff/f18795e7b74c3c67fb65 f253562f241f26f=

- Add expected isolationtester output when prepared xacts are
disabled. This was deemed unnecessary initially but in later
discussion it was agreed otherwise. Original file from Kevin
Grittner, allegedly from Dan Ports. I had to clean up whitespace a
bit per changes from Heikki Linnakangas.
http://git.postgresql.org/pg/commitdiff/28190bacfd4657954c2c d594cc1c3e6b691=

Andrew Dunstan pushed:

- Port backup check on psql lexer to MSVC.
http://git.postgresql.org/pg/commitdiff/7327cb6420106d60d3a1 a817648b145d602=

- Unbreak MSVC build broken by my port of flex check. flex puts
lex.backup in the current working directory regardless of where the
input and output are.
http://git.postgresql.org/pg/commitdiff/6a56a38f017718f23b00 1050e3a69662b83=

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Pavan Deolasse sent in another revisions of the patch to track the
vacuum generation number in the line pointer itself.

Alexander Korotkov and Heikki Linnakangas traded patches to speed up
GiST index builds.

David Gould and Robert Haas traded patches to diagnose and fix an
issue with locking.

Pavel Stehule sent in a patch for PL/pgsql to plan SQL statements

Sushant Sinha sent in a patch to limit the number of words generated
in a headline in text search.

Jeff Davis sent in another revision of the patch to implement range

Steve Singer sent in a patch to allow skipping WAL on COPY.

Andrew Dunstan sent in a patch to implement a --no-table-date option
for pg_dump. This is useful when only some tables' data is relevant
to a dump.

Andrew Dunstan sent in patches to allow pg_dump and pg_restore to
omit or use exclusively post-data items.

Dougal Sutherland sent in a patch to clarify postgresql.conf.sample.

KaiGai Kohei sent in another revision of the patch intended to fix
some leaks in views.

Tomas Vondra sent in a patch to enable regular logging of checkpoint

KaiGai Kohei sent in another revision of the patch to allow access to
the userspace access vector cache.

Bruce Momjian sent in a patch intended to remove redundant #include

Dean Rasheed sent in two patches intended to allow relative timestamps
as input, e.g. 'tomorrow 10:30'. 'Christmas plus three fortnights'
will need to wait for a later patch.

Greg Smith sent in two more revisions of a patch to allow
finer-grained tracking of vacuums.

YAMAMOTO Takashi sent in a patch to remove tab characters from a

KaiGai Kohei sent in a patch to enable object access hooks with

Andreas 'ads' Scherbaum
Deutsche PostgreSQL User Group: http://www.pgug.de/
DPWN: http://andreas.scherbaum.la/blog/categories/18-PWN

Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.org)
To make changes to your subscription: