== Wöchentlicher PostgreSQL Newsletter - 06. März 2011 ==

== Wöchentlicher PostgreSQL Newsletter - 06. März 2011 ==

am 09.03.2011 18:09:39 von adsmail

Der Originalartikel befindet sich unter:


== Wöchentlicher PostgreSQL Newsletter - 06. März 2011 ==

Die Liste der Sprecher für PGCon 2011 ist veröffentlicht.

Selena Deckelmann organisiert einen PL Developer Summit auf
der PgCon. Hilf mit!
http://www.chesnok.com/daily/2011/02/28/pl-developer-summit- at-pgcon-may-21/

Es gibt jetzt eine Version in spanischer Sprache von Planet PostgreSQL.

Ein Webcast in Spanisch über PostgreSQL 9.0 wird am 9. März statt=

Es wird eine umfangreiche PostgreSQL Präsenz auf der MySQL Konferenz
und Expo am am 11-14. April 2011 in Santa Clara, Kalifornien, geben.
http://en.oreilly.com/mysql2011/public/schedule/tag/postgres ql

== PostgreSQL Jobs im März ==

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

== PostgreSQL Lokal ==

Gabriele Bartolini von der ITPUG wird einen Vortrag über
PostgreSQL 9.0 auf der Codemotion in Rom am Samstag dem 5. März 2011
um 16:10 Uhr halten. Mehr Informationen unter:

PostgreSQL Conference East 2011: New York,
vom 22. bis 25. März

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=

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.

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

Peter Eisentraut pushed:

- PL/Python custom SPI exceptions. This provides a separate exception
class for each error code that the backend defines, as well as the
ability to get the SQLSTATE from the exception object. Jan
Urbański, reviewed by Steve Singer
http://git.postgresql.org/pg/commitdiff/474a42473adf9b184172 42f1fc0691a857e=

- Fix regression tests after PL/Python custom SPI exceptions patch.
http://git.postgresql.org/pg/commitdiff/4b853c879d3c298b7ea7 a09fcb74314bc54=

- Dump the COLLATABLE attribute in CREATE TYPE This was previously
omitted by accident.
http://git.postgresql.org/pg/commitdiff/16143d64513e4dc3c72b ad7ae98d3df0b5a=

- Additional PL/Python regression test expected file.
plpython_subtransaction test needs a separate expected file
specifically for Python 2.5.
http://git.postgresql.org/pg/commitdiff/2f363590c17c0e02186e fab5fa0b197b82a=

- Support for DragonFly BSD. Mapped to NetBSD, the closest existing
match. (Even though DragonFly BSD is derived from FreeBSD, the
shared library version numbering matches NetBSD, and the rest is
mostly the same among all BSD variants.) per "Rumko"
http://git.postgresql.org/pg/commitdiff/6094c242d1ee40a08f31 38811425d7540e8=

- Add collations to information_schema.usage_privileges. This is
faked information like for domains.
http://git.postgresql.org/pg/commitdiff/091bda0188250c9802ce bca066b4ca9e049=

- When creating a collation, check that the locales can be loaded.
This is the same check that would happen later when the collation is
used, but it's friendlier to check the collation already when it is
http://git.postgresql.org/pg/commitdiff/4442e1975d3c4c96a0b5 73b7abd864b0cbe=

- Don't allow CREATE TABLE AS to create a column with invalid
collation. It is possible that an expression ends up with a
collatable type but without a collation. CREATE TABLE AS could then
create a table based on that. But such a column cannot be dumped
with valid SQL syntax, so we disallow creating such a column. per
test report from Noah Misch.
http://git.postgresql.org/pg/commitdiff/b9cff97fdf486eca7e56 3a9696a73910488=

- Update of SQL feature conformance.
http://git.postgresql.org/pg/commitdiff/9650364b7b2fc5d676f8 7cca7b5f9ff991c=

Robert Haas pushed:

- Rename pg_stat_replication.apply_location to replay_location. For
consistency with pg_last_xlog_replay_location. Per discussion.
http://git.postgresql.org/pg/commitdiff/92c30fd2edee6da6e018 f41a1d67b4ac2f8=

- Avoid excessive Hot Standby feedback messages. Without this patch,
when wal_receiver_status_interval=3D0, indicating that no status
messages should be sent, Hot Standby feedback messages are instead
sent extremely frequently. Fujii Masao, with documentation changes
by me.
http://git.postgresql.org/pg/commitdiff/59d6a7594288ebc909a2 4f3fc9e502f097b=

- Refactor seclabel.c to use the new check_object_ownership function.
This avoids duplicate (and not-quite-matching) code, and makes the
http://git.postgresql.org/pg/commitdiff/efa415da8c0fb8c1c0bc 50faebac430f8b9=

- Remove emphasis from 9.1alpha3 items. In preparation for 9.1alpha4
release notes, where only the 9.1alpha4 features will be emphasized.
http://git.postgresql.org/pg/commitdiff/a4406da967093a9793db 0fbceb6e226e318=

- First cut at 9.1alpha4 release notes.
http://git.postgresql.org/pg/commitdiff/5c44f38d3b63c5a11f58 fe558adfe4d3cf4=

- Add missing word.
http://git.postgresql.org/pg/commitdiff/f31e532492aa950c62c3 ded32d07d1e4201=

- Remove one copy of duplicated alpha4 release note. Noted by Andy
http://git.postgresql.org/pg/commitdiff/c0f2b2e256a3e98c2a06 6cee8623b7ab156=

Andrew Dunstan pushed:

- Unbreak vpath builds broken by commit=20
http://git.postgresql.org/pg/commitdiff/57e9bda5ec6a032e1e6d 51dad5e534a1166=

- Fix plpython breakage detected on certain Fedora machines on
buildfarm. Patch from Jan Urbański.
http://git.postgresql.org/pg/commitdiff/4c966d920fb75a5d0366 b887c2ef28e6d87=

- Add a comment explaining the recent fix for plpython breakage in
commit 4c966d9. Mostly text supplied by Jan Urbański.
http://git.postgresql.org/pg/commitdiff/12bf602f3fe963a9d155 778b9385186c3a0=

- Add PL extension files to MSVC Install procedure.
http://git.postgresql.org/pg/commitdiff/a956b160261579ae2422 b539ccf73d75684=

Tom Lane pushed:

- Rearrange snapshot handling to make rule expansion more consistent.
With this patch, portals, SQL functions, and SPI all agree that
there should be only a CommandCounterIncrement between the queries
that are generated from a single SQL command by rule expansion.
Fetching a whole new snapshot now happens only between original
queries. This is equivalent to the existing behavior of EXPLAIN
ANALYZE, and it was judged to be the best choice since it eliminates
one source of concurrency hazards for rules. The patch should also
make things marginally faster by reducing the number of snapshot
push/pop operations. The patch removes pg_parse_and_rewrite(),
which is no longer used anywhere. There was considerable discussion
about more aggressive refactoring of the query-processing functions
exported by postgres.c, but for the moment nothing more has been
done there. I also took the opportunity to refactor snapmgr.c's API
slightly: the former PushUpdatedSnapshot() has been split into two
functions. Marko Tiikkaja, reviewed by Steve Singer and Tom Lane.
http://git.postgresql.org/pg/commitdiff/c0b00760365c74308e9e 0719c993eadfbcd=

- Update discussion of EXPLAIN to reflect existence of ModifyTable
nodes. Back-patch to 9.0, since this was changed then.
http://git.postgresql.org/pg/commitdiff/09b49a8439fde63826f9 3f55430868cf258=

- Include the target table in EXPLAIN output for ModifyTable nodes.
Per discussion, this seems important for plans involving writable
CTEs, since there can now be more than one ModifyTable node in the
plan. To retain the same formatting as for target tables of scan
nodes, we show only one target table, which will be the parent table
in case of an UPDATE or DELETE on an inheritance tree. Individual
child tables can be determined by inspecting the child plan trees if
http://git.postgresql.org/pg/commitdiff/97c4ee94adf15f7a0a39 cbb5549159e2aa0=

- Correct mistaken claims about EXPLAIN ANALYZE's handling of
triggers. Time spent executing AFTER triggers is not included in
the runtime of the associated ModifyTable node; in my patch of
yesterday I confused queuing of these triggers with their actual
execution. Spotted by Marko Tiikkaja.
http://git.postgresql.org/pg/commitdiff/7e49330cad885a764412 53da74b88e7d2f2=

- Fix erroneous documentation of the syntax of CREATE CONSTRAINT
TRIGGER. The grammar requires a specific ordering of the clauses,
but the documentation showed a different order. This error was
introduced in commit b47953f9c69d48a9261bd643e3170017b93f6337, which
merged the CREATE CONSTRAINT TRIGGER documentation into the CREATE
TRIGGER page. There is no code bug AFAICS.
http://git.postgresql.org/pg/commitdiff/2f6c8453cf3f38a70adb cb59489630cd5be=

- Add KNNGIST support to contrib/btree_gist. This extends GiST's
support for nearest-neighbor searches to many of the standard data
types. Teodor Sigaev
http://git.postgresql.org/pg/commitdiff/8436489c81c23af63769 6ac69cdaafddcc9=

- Add 'collatable' to the Parameters section of CREATE TYPE.
http://git.postgresql.org/pg/commitdiff/43bdf3583a9a5912e180 0ab140b8ca495ae=

- Mark operator implementation functions as such in their comments.
Historically, we've not had separate comments for built-in
pg_operator entries, but relied on the comments for the underlying
functions. The trouble with this approach is that there isn't much
of anything to suggest to users that they'd be better off using the
operators instead. So, move all the relevant comments into
pg_operator, and give each underlying function a comment that just
says "implementation of XXX operator". There are only about half a
dozen cases where it seems reasonable to use the underlying function
interchangeably with the operator; in these cases I left the same
comment in place on the function as on the operator. While at it,
establish a policy that every built-in function and operator entry
should have a comment: there are now queries in the opr_sanity
regression test that will complain if one doesn't. This only
required adding a dozen or two more entries than would have been
there anyway. I also spent some time trying to eliminate gratuitous
inconsistencies in the style of the comments, though it's hopeless
to suppose that more won't creep in soon enough. Per my proposal of
http://git.postgresql.org/pg/commitdiff/94133a935414407920a4 7d06a6e22734c97=

- Add -lm to SHLIB_LINK for contrib/btree_gist. Now that btree_gist
contains a reference to isinf(), this is necessary at least on some
platforms. Per buildfarm.
http://git.postgresql.org/pg/commitdiff/8c4164540b30434845be 02f84973663bfeb=

- Run a portal's cleanup hook immediately when pushing it to DONE
state. This works around the problem noted by Yamamoto Takashi in
bug #5906, that there were code paths whereby we could reach
AtCleanup_Portals with a portal's cleanup hook still unexecuted.
The changes I made a few days ago were intended to prevent that from
happening, and I think that on balance it's still a good thing to
avoid, so I don't want to remove the Assert in AtCleanup_Portals.
Hence do this instead.
http://git.postgresql.org/pg/commitdiff/6252c4f9e201f619e5ee bda12fa867acd4e=

- Fix citext's upgrade-from-unpackaged script to set its collation
correctly. Although there remains some debate about how CREATE TYPE
should represent the collation property, this doesn't really affect
what we need to do in citext's script, so go ahead and fix that.
http://git.postgresql.org/pg/commitdiff/94be9e3f0ca9e7ced661 68397eb586565bc=

- Further refine patch for commenting operator implementation
functions. Instead of manually maintaining the "implementation of
XXX operator" comments in pg_proc.h, delete all those entries and
let initdb create them via a join. To let initdb figure out which
name to use when there is a conflict, change the comments for
deprecated operators to say they are deprecated --- which seems like
a good thing to do anyway.
http://git.postgresql.org/pg/commitdiff/908ab80286401bb20a51 9fa7dc7a837631f=

- In initialize_SSL, don't fail unnecessarily when home dir is
unavailable. Instead, just act as though the certificate file(s)
are not present. There is only one case where this need be a hard
failure condition: when sslmode is verify-ca or verify-full, not
having a root cert file is an error. Change the logic so that we
complain only in that case, and otherwise fall through cleanly.
This is how it used to behave pre-9.0, but my patch
4ed4b6c54e5fab24ab2624d80e26f7546edc88ad of 2010-05-26 broke the
case. Per report from Christian Kastner.
http://git.postgresql.org/pg/commitdiff/bd58d9d883111ee84de4 af480ebb018fe9b=

- Allow non-superusers to create (some) extensions. Remove the
unconditional superuser permissions check in CREATE EXTENSION, and
instead define a "superuser" extension property, which when false
(not the default) skips the superuser permissions check. In this
case the calling user only needs enough permissions to execute the
commands in the extension's installation script. The superuser
property is also enforced in the same way for ALTER EXTENSION UPDATE
cases. In other ALTER EXTENSION cases and DROP EXTENSION, test
ownership of the extension rather than superuserness. ALTER
EXTENSION ADD/DROP needs to insist on ownership of the target object
as well; to do that without duplicating code, refactor comment.c's
big switch for permissions checks into a separate function in
objectaddress.c. I also removed the superuserness checks in
pg_available_extensions and related functions; there's no strong
reason why everybody shouldn't be able to see that info. Also
invent an IF NOT EXISTS variant of CREATE EXTENSION, and use that in
pg_dump, so that dumps won't fail for installed-by-default
extensions. We don't have any of those yet, but we will soon. This
is all per discussion of wrapping the standard procedural languages
into extensions. I'll make those changes in a separate commit; this
is just putting the core infrastructure in place.
http://git.postgresql.org/pg/commitdiff/8d3b421f5f7b955e7ac7 d156aa74ee6a6fe=

- Create extension infrastructure for the core procedural languages.
This mostly just involves creating control, install, and
update-from-unpackaged scripts for them. However, I had to adjust
plperl and plpython to not share the same support functions between
variants, because we can't put the same function into multiple
extensions. catversion bump forced due to new contents of
pg_pltemplate, and because initdb now installs plpgsql as an
extension not a bare language. Add support for regression testing
these as extensions not bare languages. Fix a couple of other
issues that popped up while testing this: my initial hack at pg_dump
binary-upgrade support didn't work right, and we don't want an extra
schema permissions test after all. Documentation changes still to
come, but I'm committing now to see whether the MSVC build scripts
need work (likely they do).
http://git.postgresql.org/pg/commitdiff/63b656b7bf39beb5b609 5ab2b5e1e4e9d8c=

- Update documentation to reflect that standard PLs are now
extensions. Recommend use of CREATE EXTENSION rather than plain
CREATE LANGUAGE where relevant. Encourage PL authors to provide
extension wrappers for their PLs.
http://git.postgresql.org/pg/commitdiff/c2903fb3d2aeb0a72069 45478ad28bc47bd=

- Convert createlang/droplang to use CREATE/DROP EXTENSION. In
createlang this is a one-line change. In droplang there's a whole
lot of cruft that can be discarded since the extension mechanism now
manages removal of the language's support functions. Also, add
deprecation notices to these two programs' reference pages, since
per discussion we may toss them overboard altogether in a release or
http://git.postgresql.org/pg/commitdiff/ba0c93a0f46ca70cae47 a8817067c1e9848=

- Make plpythonu language use plpython2 shared library directly. The
original scheme for this was to symlink plpython.$DLSUFFIX to
plpython2.$DLSUFFIX, but that doesn't work on Windows, and only
accidentally failed to fail because of the way that CREATE LANGUAGE
created or didn't create new C functions. My changes of yesterday
exposed the weakness of that approach. To fix, get rid of the
symlink and make pg_pltemplate show what's really going on.
http://git.postgresql.org/pg/commitdiff/bfd7f8cbb2ddf8c48ac3 10a80d33c951087=

- Adjust documentation about pg_pltemplate to reflect latest thinking.
It's more likely that pg_pltemplate will go away in the future than
that we'll add additional specialized infrastructure for it.
http://git.postgresql.org/pg/commitdiff/0b5d7345925dd0212d45 09195a337f19127=

- Fix incorrect access to pg_index.indcollation. Since this field is
after a variable-length field, it can't simply be accessed via the C
struct for pg_index. Fortunately, the relcache already did the
dirty work of pulling the information out to where it can be
accessed easily, so this is a one-line fix. Andres Freund
http://git.postgresql.org/pg/commitdiff/149b2673c244b92b5941 1dd2292d6ddcfb0=

- Fix pg_dump's dump order for collations versus extensions. Mixing
them together alphabetically won't be nice. Per my gripe of

- Add missing "static" marker to internal_ping(). Per testing with a
compiler that doesn't like that.
http://git.postgresql.org/pg/commitdiff/775464e8450677602833 b1d11b919838e7b=

- Fix a couple more missing "static" markers.
http://git.postgresql.org/pg/commitdiff/dfe18f18d262df731378 cbf38a4136f08ca=

- Suppress some "variable might be clobbered by longjmp" warnings.
Seen with an older gcc version. I'm not sure these represent any
real risk factor, but still a bit scary. Anyway we have lots of
other volatile-marked variables in this code, so a couple more won't
http://git.postgresql.org/pg/commitdiff/4172bd88300524efd172 8c247fbc1eff3c1=

Heikki Linnakangas pushed:

- Fix bugs in Serializable Snapshot Isolation. Change the way UPDATEs
are handled. Instead of maintaining a chain of tuple-level locks in
shared memory, copy any existing locks on the old tuple to the new
tuple at UPDATE. Any existing page-level lock needs to be duplicated
too, as a lock on the new tuple. That was neglected previously.
Store xmin on tuple-level predicate locks, to distinguish a lock on
an old already-recycled tuple from a new tuple at the same physical
location. Failure to distinguish them caused loops in the
tuple-lock chains, as reported by YAMAMOTO Takashi. Although we
don't use the chain representation of UPDATEs anymore, it seems like
a good idea to store the xmin to avoid some false positives if no
other reason. CheckSingleTargetForConflictsIn now correctly handles
the case where a lock that's being held is not reflected in the
local lock table. That happens if another backend acquires a lock on
our behalf due to an UPDATE or a page split.
PredicateLockPageCombine now retains locks for the page that is
being removed, rather than removing them. This prevents a
potentially dangerous false-positive inconsistency where the local
lock table believes that a lock is held, but it is actually not.
Dan Ports and Kevin Grittner
http://git.postgresql.org/pg/commitdiff/47ad79122bc099c1f0ea 8a7ae413fcd8d45=

- Change pg_last_xlog_receive_location() not to move backwards. That
makes it a lot more useful for determining which standby is most
up-to-date, for example. There was long discussions on whether
overwriting existing existing WAL makes sense to begin with, and
whether we should do some more extensive variable renaming, but this
change nevertheless seems quite uncontroversial. Fujii Masao,
reviewed by Jeff Janes, Robert Haas, Stephen Frost.
http://git.postgresql.org/pg/commitdiff/6eba5a7c57d1d61f46b6 966026bf7bc07f8=

- Add tab-completion for table name after JOIN. Andrey Popp
http://git.postgresql.org/pg/commitdiff/8e2d8b1497ac64142bb7 df05e39a58ebf3e=

- You must hold a lock on the heap page when you call
CheckForSerializableConflictOut(), because it can set hint bits.
http://git.postgresql.org/pg/commitdiff/ee3838b1d35cffec21ca 40dbfc409ec40dd=

Michael Meskes pushed:

- Added new version of ecpg's parser generator script. This one was
written by Andy Colson.
http://git.postgresql.org/pg/commitdiff/32fce70564999a90d48a 27c4279a8908e90=

Bruce Momjian pushed:

- Fix parallel gmake for extension directory addition in PL languages.
http://git.postgresql.org/pg/commitdiff/c15c1f1c15fc19d20c06 99aeb312664f552=

- Print clearer failure message when pg_upgrade fails due to a fatal
http://git.postgresql.org/pg/commitdiff/a3375becfaebde56f8e6 260a3f77c467425=

- Fix pg_upgrade to print the proper database name for file transfer
http://git.postgresql.org/pg/commitdiff/f7b70dfc7649772cf350 35754ae7e77f253=

- Restructure pg_upgrade checks because pre-8.4 Postgres did not
rename toast file names to match the new relfilenode.
http://git.postgresql.org/pg/commitdiff/9e5bed2df1693a46dfae d862d7462ba2379=

- Update new pg_upgrade comment about pre-8.4 TOAST tables.
http://git.postgresql.org/pg/commitdiff/8f87dcd863c2fe294012 9ea58343bc0e56c=

- Improve pg_upgrade relation name check logic for pre-8.4 servers.
http://git.postgresql.org/pg/commitdiff/a54ba23c087a23222a7c 76b33c61cddad52=

- Tighten pg_upgrade check for pre-8.4 toast table name matching.
http://git.postgresql.org/pg/commitdiff/05d93c38a791836eecea f8edb0ea8cb19cd=

Simon Riggs pushed:

- Efficient transaction-controlled synchronous replication. If a
standby is broadcasting reply messages and we have named one or more
standbys in synchronous_standby_names then allow users who set
synchronous_replication to wait for commit, which then provides
strict data integrity guarantees. Design avoids sending and
receiving transaction state information so minimises bookkeeping
overheads. We synchronize with the highest priority standby that is
connected and ready to synchronize. Other standbys can be defined to
takeover in case of standby failure. This version has very strict
behaviour; more relaxed options may be added at a later date. Simon
Riggs and Fujii Masao, with reviews by Yeb Havinga, Jaime Casanova,
Heikki Linnakangas and Robert Haas, plus the assistance of many
other design reviewers.
http://git.postgresql.org/pg/commitdiff/a8a8a3e0965201df88bd fdff08f50e5c06c=

- Add new files for syncrep missed in previous commit.
http://git.postgresql.org/pg/commitdiff/966fb05b588ab33a6c99 c6a26308941e1b3=

- Catversion increment for pg_stat_replication changes for syncrep.
http://git.postgresql.org/pg/commitdiff/dcfe3f60c12880c52fd3 cb9b5d51ff44c94=

- Dynamic array required within pg_stat_replication.
http://git.postgresql.org/pg/commitdiff/cae4974e3d4c0cb12375 68b55a2cee4482d=

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Fujii Masao sent in another revision of the replication server timeout

Dan Ports sent in a patch to fix a bug in SSI.

Jan Urbanski sent in a patch to fix PL/PythonU tracebacks.

Heikki Linnakangas and Merlin Moncure traded patches to fix an issue
where the PD_ALL_VISIBLE flag was set incorrectly.

Andy Colson sent in a cleaned-up version of check_rules.pl to conform
with Perl 5.12.

Jan Urbanski sent in another revision of the patch to add tracebacks
to PL/PythonU.

Fujii Masao sent in another revision of the patch to add replication
server timeout.

Kevin Grittner sent in a documentation patch to explain SET
TRANSACTION...DEFERRABLE better in the context of SSI.

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: