== Wöchentlicher PostgreSQL Newsletter - 24. April 2011 ==

Der Originalartikel befindet sich unter:

http://www.postgresql.org/community/weeklynews/pwn20110424



== Wöchentlicher PostgreSQL Newsletter - 24. April 2011 ==

Neue Umfrage: Hast du pg_upgrade schon mal verwendet?
http://www.postgresql.org/community

== PostgreSQL Produkt Neuigkeiten ==

MicroOLAP Database Designer 1.8.0 Beta 2 für PostgreSQL
ist erschienen.
http://microolap.com/products/database/postgresql-designer/

== PostgreSQL Jobs im April ==

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

== PostgreSQL Lokal ==

Die Türkische PostgreSQL User Group organisiert eine eintägige
Veranstaltung am 30. April 2011. Folge @PgDayTR für Details,
auf Türkisch.
http://pgday.postgresql.org.tr/2011/

Das Open Database Camp findet vom 7. bis 9. Mai 2011 in Sardinien,
Italien statt.
http://datacharmer.blogspot.com/2011/01/announcing-open-data base-camp-sardi=
nia.html

PGCon findet am 19. und 20. Mai 2011 an der Universität
von Ottawa statt, vorher gibt es am 17. und 18. Mai
zwei Tage mit Trainings.
http://www.pgcon.org/2011/

PG Session 2 über PostGIS findet am 23. Juni in Paris statt.
Der Call for Papers ist jetzt offen.
http://www.postgresql-sessions.org/en/2/

pgbr findet in Sao Paulo, Brazilien, am 3. und 4. November 2011 statt.
http://pgbr.postgresql.org.br/

== PostgreSQL in den News ==

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

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

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

Robert Haas pushed:

- recoveryStopsHere() must check the resource manager ID. Before
commit c016ce728139be95bb0dc7c4e5640507334c2339, this wasn't needed,
but now that multiple resource manager IDs can percolate down
through here, we have to make sure we know which one we've got.
Otherwise, we can confuse (for example) an XLOG_XACT_COMMIT record
with an XLOG_CHECKPOINT_SHUTDOWN record. Review by Jaime Casanova
=20
http://git.postgresql.org/pg/commitdiff/aea1f24c2c25f0154043 5ded6ba61101639=
2ef06

- Only allow typed tables to hang off composite types, not e.g.
tables. This also ensures that we take a relation lock on the
composite type when creating a typed table, which is necessary to
prevent the composite type and the typed table from getting out of
step in the face of concurrent DDL. Noah Misch, with some changes.
=20
http://git.postgresql.org/pg/commitdiff/04db0fdbfa9382730bb6 5f94bca2cd8063a=
3456a

- Allow ALTER TABLE name {OF type | NOT OF}. This syntax allows a
standalone table to be made into a typed table, or a typed table to
be made standalone. This is possibly a mildly useful feature in its
own right, but the real motivation for this change is that we need
it to make pg_upgrade work with typed tables. This doesn't actually
fix that problem, but it's necessary infrastructure. Noah Misch.
=20
http://git.postgresql.org/pg/commitdiff/68739ba856c52e6721d6 cffec21f1bf0327=
a9a7b

- Typo fix.
=20
http://git.postgresql.org/pg/commitdiff/0babcdf6cfdfb2a82805 6afc3172ec524f0=
90fd5

- Fix use of incorrect constant RemoveRoleFromObjectACL. This could
cause failures when DROP OWNED BY attempt to remove default
privileges on sequences. Back-patching to 9.0. Shigeru Hanada
=20
http://git.postgresql.org/pg/commitdiff/8ede427938e9676d0e49 7406c213f098303=
a93f3

- Allow ALTER TYPE .. ADD ATTRIBUTE .. CASCADE to recurse to
descendants. Without this, adding an attribute to a typed table
with an inheritance child fails, which is surprising. Noah Misch,
with minor changes by me.
=20
http://git.postgresql.org/pg/commitdiff/a0e8df527ec24e8dba98 f295c0e2ab6ccf3=
e5d2c

Andrew Dunstan pushed:

- Attempt to remedy buildfarm breakage caused by commit f536d4194.
=20
http://git.postgresql.org/pg/commitdiff/b7b86924c6da46c774e1 ab5d524a6bc4f72=
627ab

- Silence compiler warning about casting HANDLE to long on WIN64.
=20
http://git.postgresql.org/pg/commitdiff/ca5a75fbaed63f41c6e5 2e5d4b354700803=
56bdf

- Silence a few compiler warnings from gcc on MinGW. Most of these
cast DWORD to int or unsigned int for printf type handling. This is
safe even on 64 bit architectures because a DWORD is always 32 bits.
In one case a variable is initialised to keep the compiler happy.
=20
http://git.postgresql.org/pg/commitdiff/d98711dfef6ade6a26aa 0f4c0a775087ed1=
3e060

Tom Lane pushed:

- Improve findoidjoins to cover more cases. Teach the program and
script to deal with OID-array referencing columns, which we now have
several of. Also, modify the recommended usage process to specify
that the program should be run against the regression database
rather than template1. This lets it find numerous joins that cannot
be found in template1 because the relevant catalogs are entirely
empty. Together these changes add seventeen formerly-missed cases
to the oidjoins regression test.
=20
http://git.postgresql.org/pg/commitdiff/795c382e8caf27f9db2f b09d12384b81832=
80fee

- Improve cost estimation for aggregates and window functions. The
previous coding failed to account properly for the costs of
evaluating the input expressions of aggregates and window functions,
as seen in a recent gripe from Claudio Freire. (I said at the time
that it wasn't counting these costs at all; but on closer
inspection, it was effectively charging these costs once per output
tuple. That is completely wrong for aggregates, and not exactly
right for window functions either.) There was also a hard-wired
assumption that aggregates and window functions had procost 1.0,
which is now fixed to respect the actual cataloged costs. The
costing of WindowAgg is still pretty bogus, since it doesn't try to
estimate the effects of spilling data to disk, but that seems like a
separate issue.
=20
http://git.postgresql.org/pg/commitdiff/e6a30a8c3c81a7a2949f 852379d34a19dfc=
26a0d

- Update oidjoins regression test for 9.1 catalog schema additions.
=20
http://git.postgresql.org/pg/commitdiff/970d8a39736fd67e3ebf 406ed8129eed076=
7d15d

- Fix handling of collations in multi-row VALUES constructs. Per spec
we ought to apply select_common_collation() across the expressions
in each column of the VALUES table. The original coding was just
taking the first row and assuming it was representative. This patch
adds a field to struct RangeTblEntry to carry the resolved
collations, so initdb is forced for changes in stored rule
representation.
=20
http://git.postgresql.org/pg/commitdiff/918854cc08868d569aad 3bdf2529fc61c66=
ecde5

- Refrain from canonicalizing a client_encoding setting of "UNICODE".
While "UTF8" is the correct name for this encoding, existing JDBC
drivers expect that if they send "UNICODE" it will read back the
same way; they fail with an opaque "Protocol error" complaint if
not. This will be fixed in the 9.1 drivers, but until older drivers
are no longer in use in the wild, we'd better leave "UNICODE" alone.
Continue to canonicalize all other inputs. Per report from Steve
Singer and subsequent discussion.
=20
http://git.postgresql.org/pg/commitdiff/390cf3209b718382c0ec 9793b714422189e=
9b68b

- Revert "Prevent incorrect updates of pg_index while reindexing
pg_index itself." This reverts commit
4b6106ccfea21e86943f881edcf3cfc03661a415 of 2011-04-15. There's a
better way to do it, which will follow shortly.
=20
http://git.postgresql.org/pg/commitdiff/c096d19b74a637443109 e528000342e896b=
150f3

- Avoid changing an index's indcheckxmin horizon during REINDEX.
There can never be a need to push the indcheckxmin horizon forward,
since any HOT chains that are actually broken with respect to the
index must pre-date its original creation. So we can just avoid
changing pg_index altogether during a REINDEX operation. This
offers a cleaner solution than my previous patch for the problem
found a few days ago that we mustn't try to update pg_index while we
are reindexing it. System catalog indexes will always be created
with indcheckxmin =3D false during initdb, and with this modified code
we should never try to change their pg_index entries. This avoids
special-casing system catalogs as the former patch did, and should
provide a performance benefit for many cases where REINDEX formerly
caused an index to be considered unusable for a short time.
Back-patch to 8.3 to cover all versions containing HOT. Note that
this patch changes the API for index_build(), but I believe it is
unlikely that any add-on code is calling that directly.
=20
http://git.postgresql.org/pg/commitdiff/8c19977e9c515cc29af4 49a7ab6c25e496f=
539f3

- Make plan_cluster_use_sort cope with no IndexOptInfo for the target
index. The original coding assumed that such a case represents
caller error, but actually get_relation_info will omit generating an
IndexOptInfo for any index it thinks is unsafe to use. Therefore,
handle this case by returning "true" to indicate that a
seqscan-and-sort is the preferred way to implement the CLUSTER
operation. New bug in 9.1, no backpatch needed. Per bug #5985 from
Daniel Grace.
=20
http://git.postgresql.org/pg/commitdiff/5b8e442953da0bf4950b 86c7cb4a6117842=
aedf7

- Set indcheckxmin true when REINDEX fixes an invalid or not-ready
index. Per comment from Greg Stark, it's less clear that HOT chains
don't conflict with the index than it would be for a valid index.
So, let's preserve the former behavior that indcheckxmin does get
set when there are potentially-broken HOT chains in this case. This
change does not cause any pg_index update that wouldn't have
happened anyway, so we're not re-introducing the previous bug with
pg_index updates, and surely the case is not significant from a
performance standpoint; so let's be as conservative as possible.
=20
http://git.postgresql.org/pg/commitdiff/9ad7e15507ffa14f51d8 0d6ae3ed942ea19=
1826d

- Fix bugs in indexing of in-doubt HOT-updated tuples. If we find a
DELETE_IN_PROGRESS HOT-updated tuple, it is impossible to know
whether to index it or not except by waiting to see if the deleting
transaction commits. If it doesn't, the tuple might again be LIVE,
meaning we have to index it. So wait and recheck in that case.
Also, we must not rely on ii_BrokenHotChain to decide that it's
possible to omit tuples from the index. That could result in
omitting tuples that we need, particularly in view of yesterday's
fixes to not necessarily set indcheckxmin (but it's broken even
without that, as per my analysis today). Since this is just an
extremely marginal performance optimization, dropping the test
shouldn't hurt. These cases are only expected to happen in system
catalogs (they're possible there due to early release of
RowExclusiveLock in most catalog-update code paths). Since
reindexing of a system catalog isn't a particularly
performance-critical operation anyway, there's no real need to be
concerned about possible performance degradation from these changes.
The worst aspects of this bug were introduced in 9.0 --- 8.x will
always wait out a DELETE_IN_PROGRESS tuple. But I think dropping
index entries on the strength of ii_BrokenHotChain is dangerous even
without that, so back-patch removal of that optimization to 8.3 and
8.4.
=20
http://git.postgresql.org/pg/commitdiff/520bcd9c9bb4d0662705 4e1c567bac1feb2=
da879

- Avoid possible divide-by-zero in gincostestimate. Per report from
Jeff Janes.
=20
http://git.postgresql.org/pg/commitdiff/92647fc4b9cd7406afb2 ee240a20082ba60=
97177

- Make a code-cleanup pass over the collations patch. This patch is
almost entirely cosmetic --- mostly cleaning up a lot of neglected
comments, and fixing code layout problems in places where the patch
made lines too long and then pgindent did weird things with that. I
did find a bug-of-omission in equalTupleDescs().
=20
http://git.postgresql.org/pg/commitdiff/9e9b9ac7d1860fbb98eb 4db17a94ff25524=
b6447

- De-kludge contrib/btree_gin for collations. Using
DEFAULT_COLLATION_OID in the comparePartial functions was not only a
lame hack, but outright wrong, because the compare functions for
collation-aware types were already responding to the declared index
collation. So comparePartial would have the wrong expectation about
the index's sort order, possibly leading to missing matches for
prefix searches.
=20
http://git.postgresql.org/pg/commitdiff/474ff212e5c2e89a9955 cc2355cb96b2fe4=
0398e

- Make GIN and GIST pass the index collation to all their support
functions. Experimentation with contrib/btree_gist shows that the
majority of the GIST support functions potentially need collation
information. Safest policy seems to be to pass it to all of them,
instead of making assumptions about which ones could possibly need
it.
=20
http://git.postgresql.org/pg/commitdiff/ae20bf1740c53494e15f adfd8c2c6444032=
a3441

- Fix contrib/btree_gist to handle collations properly. Make use of
the collation attached to the index column, instead of hard-wiring
DEFAULT_COLLATION_OID. (Note: in theory this could require
reindexing btree_gist indexes on textual columns, but I rather doubt
anyone has one with a non-default declared collation as yet.)
=20
http://git.postgresql.org/pg/commitdiff/bb850306307d3d6ebb61 1c4039ae127236e=
b1699

- Fix char2wchar/wchar2char to support collations properly. These
functions should take a pg_locale_t, not a collation OID, and should
call mbstowcs_l/wcstombs_l where available. Where those functions
are not available, temporarily select the correct locale with
uselocale(). This change removes the bogus assumption that all
locales selectable in a given database have the same wide-character
conversion method; in particular, the collate.linux.utf8 regression
test now passes with LC_CTYPE=3DC, so long as the database encoding is
UTF8. I decided to move the char2wchar/wchar2char functions out of
mbutils.c and into pg_locale.c, because they work on wchar_t not
pg_wchar_t and thus don't really belong with the mbutils.c
functions. Keeping them where they were would have required
importing pg_locale_t into pg_wchar.h somehow, which did not seem
like a good plan.
=20
http://git.postgresql.org/pg/commitdiff/2ab0796d7a3a7116a79b 65531fd33f15485=
14b52

- Adjust comments about collate.linux.utf8 regression test. This test
should now work in any database with UTF8 encoding, regardless of
the database's default locale. The former restriction was really
"doesn't work if default locale is C", and that was because of not
handling mbstowcs/wcstombs correctly.
=20
http://git.postgresql.org/pg/commitdiff/1abd146dddc1dc5efff5 ccac065c460108a=
cbaa9

- Hash indexes had better pass the index collation to support
functions, too. Per experimentation with contrib/citext, whose hash
function assumes that it'll be passed a collation.
=20
http://git.postgresql.org/pg/commitdiff/a0b75a41a907e1582acd b8aa6ebb9cacca3=
9d7d8

Heikki Linnakangas pushed:

- Silence compiler warning about unused variable on Windows.
=20
http://git.postgresql.org/pg/commitdiff/a7cb69a5a345fe9ba481 a035559d77abd07=
791d9

- Quotes in strings injected into bki file need to escaped. In
particular, "People's Republic of China" locale on Windows was
causing initdb to fail. This fixes bug #5818 reported by yulei. On
master, this makes the mapping of "People's Republic of China" to
just "China" obsolete. In 9.0 and 8.4, just fix the escaping.
Earlier versions didn't have locale names in bki file.
=20
http://git.postgresql.org/pg/commitdiff/2b919118c2511c7741c2 1f325d2ca4f270a=
a3aba

Peter Eisentraut pushed:

- Avoid unused variable warnings for certain configurations.
=20
http://git.postgresql.org/pg/commitdiff/001cbb145f3250b0d69d 6be3d5fa0236e1a=
261dc

- Add gitignore entries for Windows MSVC builds
=20
http://git.postgresql.org/pg/commitdiff/63e9c5b71b3b8afa772a 5f4e5ee7179f77f=
87050

- Fix typo
=20
http://git.postgresql.org/pg/commitdiff/908eb1f98bd9f81613cf 4c14d6ab5877815=
c7b50

- Treat config.pl as optional in vcregress.pl. This is how build.pl
treats it and how it's documented.
=20
http://git.postgresql.org/pg/commitdiff/2e8d9544752a7d68cb46 f028a4f16ab0eb7=
6c26e

- Refix the unaccent regression test on MSVC properly ... for some
value of "properly". Instead of overriding REGRESS_OPTS, set the
variables ENCODING and NO_LOCALE, which is more expressive and
allows overriding by the user. Fix vcregress.pl to handle that.
=20
http://git.postgresql.org/pg/commitdiff/385942f46ce526000d23 1c51c76360a807c=
2f809

- Fix PL/Python traceback for error in separate file. It assumed that
the lineno from the traceback always refers to the PL/Python
function. If you created a PL/Python function that imports some
code, runs it, and that code raises an exception, PLy_traceback
would get utterly confused. Now we look at the file name reported
with the traceback and only print the source line if it came from
the PL/Python function. Jan Urbański
=20
http://git.postgresql.org/pg/commitdiff/395fcac29906d22615ba 68bd1dfa31daf69=
1979e

- Add fill-column setting to emacs example configurations. This
matches the maximum line length that pgindent uses.
=20
http://git.postgresql.org/pg/commitdiff/415f5e12592d13591954 9a5eb21893fda04=
17ebf

- Small update to emacs example configuration. Since both tarballs
and git now result in a "postgresql" directory rather than a "pgsql"
directory, adjust the example configuration to look for the former.
=20
http://git.postgresql.org/pg/commitdiff/78e7e20afe768d9c5f6b 4fbf30a2d7100d4=
6bb2f

- Normalize whitespace in the arguments of . Strip leading
and trailing whitespace and replace interior whitespace by a single
space. This avoids problems with the index generator producing
duplicate index entries for terms that differ only in whitespace.
Commit dca30da3433c40b5f92f1704c496cda052decef9 actually fixed all
the indexterm elements that would cause this problem at the moment,
but in case it sneaks in again, we're set.
=20
http://git.postgresql.org/pg/commitdiff/9412606265c2774712e3 f805798896734b3=
2c7fd

Bruce Momjian pushed:

- Add C comment about why we throw an error if the pg_upgrade old/new
database counts don't match.
=20
http://git.postgresql.org/pg/commitdiff/034194470647b3de206f b42464d49a43885=
3fa5e

- Throw error for mismatched pg_upgrade clusters. If someone removes
the 'postgres' database from the old cluster and the new cluster has
a 'postgres' database, the number of databases will not match. We
actually could upgrade such a setup, but it would violate the 1-to-1
mapping of database counts, so we throw an error instead.
Previously they got an error during the upgrade, and not at the
check stage; Peter Geoghegan 9.0.4 does the same.
=20
http://git.postgresql.org/pg/commitdiff/7228d02989afd3858ce6 eb4de845c56f4c0=
188a6

- pg_upgrade C comment addition. Document why we do the missing new
database check during the check phase.
=20
http://git.postgresql.org/pg/commitdiff/0262251c337ca066d1b1 698684784254849=
b5676

- Improve doc wording for SQL syntax of LIMIT/OFFSET.
=20
http://git.postgresql.org/pg/commitdiff/0cfdc1c657b7c2aa1e45 24f495e84005f75=
0ec02

- In pg_upgrade, only compile copy_file() on non-Win32 systems. Per
report from Andrew Dunstan.
=20
http://git.postgresql.org/pg/commitdiff/f6322b31918c5c57eeea 80c26a088af44d5=
73095

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Shigeru HANADA sent in another revision of the patch to fix foreign
table privileges.

Robert Haas sent in a patch for 9.2 to allow for time-delayed
standbys.

Noah Misch sent in another revision of the patch to resolve an
incompatibility between pg_dump --binary-upgrade and ALTER TYPE ...
DROP ATTRIBUTE.

Bruce Momjian sent in three revisions of a patch to make pg_upgrade
disable autovacuum.

Dan Ports sent in a patch to fix the UPDATE performance under SSI.

Leonardo Francalanci sent in a patch to allow switching tables from
UNLOGGED to LOGGED state.

Alex Hunsaker sent in two revisions of a patch to enable the new perl
5.14 to work with the build system.

Gianni Ciolli sent in a patch to fix a performance issue in NOTIFY.

Andrew Dunstan sent in a patch to do a consolidate cleanup on win32.

Peter Eisentraut sent in a patch to allow make check to work in
contrib.

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




--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein