Skip to content

Wöchentlicher PostgreSQL Newsletter - 02. Oktober 2011

Der Originalartikel befindet sich unter:

== Wöchentlicher PostgreSQL Newsletter - 02. Oktober 2011 ==

Frühbucherregistrierung (Billig! Billig!) ist verfügbar für PGDay.IT

Die Liste der Sprecher für PGBR2011 ist verfügbar:

== PostgreSQL Produkt Neuigkeiten ==

check_postgres 2.18.0, ein Skript zum Monitoren von
PostgreSQL, ist erschienen.

tail_n_mail 1.20.3, ein Programm zum Überwachen von
Logdateien, ist erschienen.

Pyrseas 0.4.0, ein Versionskontrollwerkzeug für PostgreSQL,
ist auf PGXN erschienen.

xlogdump 0.5.0, ein Werkzeug um Daten aus WAL Segmentdateien zu
extrahieren, ist erschienen.

== PostgreSQL Lokal ==

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

PG-Day Denver 2011 findet am Freitag, dem 21. Oktober 2011 auf dem
Auraria Campus in der Nähe von Downtown Denver, Colorado 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.

Die fünfte Edition des Italienischen PostgreSQL
Tags (PGDay.IT 2011) findet am 25. November in
Prato, Italien statt.

Der Call for Papers für die FLOSS UK ist offen, die Veranstaltung
findet in Edinburgh vom 20. bis 22. März 2012 statt. Der
Einsendeschluß für Einreichungen ist der 18. November 2011,
ausgewählte Sprecher werden bis zum 25. November benachrichtigt.
Einreichungen sollen an postgresql2012 AT flossuk DOT org gehen.
Mehr Informationen:

== PostgreSQL in den News ==

Planet PostgreSQL:

Dieser wöchentliche PostgreSQL Newsletter wurde erstellt von David Fetter.

Sende Neuigkeiten und Ankündigungen bis Sonntag, 15 Uhr Pazifischer
Zeit. Bitte sende englische Beiträge an, deutsche an, italienische an, spanische an

== Reviews ==

== Angewandte Patches ==

Tom Lane pushed:

- Use a fresh copy of query_list when making a second plan in
  GetCachedPlan.  The code path that tried a generic plan, didn't like
  it, and then made a custom plan was mistakenly passing the same copy
  of the query_list to the planner both times.  This doesn't work too
  well for nontrivial queries, since the planner tends to scribble on
  its input.  Diagnosis and fix by Yamamoto Takashi.

- Speed up array element assignment in plpgsql by caching type
  information.  Cache assorted data in the PLpgSQL_arrayelem struct to
  avoid repetitive catalog lookups over multiple executions of the
  same statement.  Pavel Stehule

- Allow snapshot references to still work during transaction abort.
  In REPEATABLE READ (nee SERIALIZABLE) mode, an attempt to do
  GetTransactionSnapshot() between AbortTransaction and
  CleanupTransaction failed, because GetTransactionSnapshot would
  recompute the transaction snapshot (which is already wrong, given
  the isolation mode) and then re-register it in the
  TopTransactionResourceOwner, leading to an Assert because the
  TopTransactionResourceOwner should be empty of resources after
  AbortTransaction.  This is the root cause of bug #6218 from Yamamoto
  Takashi.  While changing plancache.c to avoid requesting a snapshot
  when handling a ROLLBACK masks the problem, I think this is really a
  snapmgr.c bug: it's lower-level than the resource manager mechanism
  and should not be shutting itself down before we unwind resource
  manager resources.  However, just postponing the release of the
  transaction snapshot until cleanup time didn't work because of the
  circular dependency with TopTransactionResourceOwner.  Fix by
  managing the internal reference to that snapshot manually instead of
  depending on TopTransactionResourceOwner.  This saves a few cycles
  as well as making the module layering more straightforward.
  predicate.c's dependencies on TopTransactionResourceOwner go away
  too.  I think this is a longstanding bug, but there's no evidence
  that it's more than a latent bug, so it doesn't seem worth any risk
  of back-patching.

- Fix window functions that sort by expressions involving aggregates.
  In commit c1d9579dd8bf3c921ca6bc2b62c40da6d25372e5, I changed things
  so that the output of the Agg node that feeds the window functions
  would not list any ungrouped Vars directly.  Formerly, for example,
  the Agg tlist might have included both "x" and "sum(x)", which is
  not really valid if "x" isn't a grouping column.  If we then had a
  window function ordering on something like "sum(x) + 1",
  prepare_sort_from_pathkeys would find no exact match for this in the
  Agg tlist, and would conclude that it must recompute the expression.
  But it would break the expression down to just the Var "x", which it
  would find in the tlist, and then rebuild the ORDER BY expression
  using a reference to the subplan's "x" output.  Now, after the
  above-referenced changes, "x" isn't in the Agg tlist if it's not a
  grouping column, so that prepare_sort_from_pathkeys fails with
  "could not find pathkey item to sort", as reported by Bricklen
  Anderson.  The fix is to not break down Aggrefs into their component
  parts, but just treat them as irreducible expressions to be sought
  in the subplan tlist.  This is definitely OK for the use with
  respect to window functions in grouping_planner, since it just built
  the tlist being used on the same basis.  AFAICT it is safe for other
  uses too; most of the other call sites couldn't encounter Aggrefs

- Take sepgsql regression tests out of the regular regression test
  mechanism.  Because these tests require root privileges, not to
  mention invasive changes to the security configuration of the host
  system, it's not reasonable for them to be invoked by a regular
  "make check" or "make installcheck".  Instead, dike out the
  Makefile's knowledge of the tests, and change chkselinuxenv (now
  renamed "test_sepgsql") into a script that verifies the environment
  is workable and then runs the tests.  It's expected that
  test_sepgsql will only be run manually.  While at it, do some
  cleanup in the error checking in the script, and do some
  wordsmithing in the documentation.

- Update and extend the EXPLAIN-related documentation.  I've made a
  significant effort at filling in the "Using EXPLAIN" section to be
  reasonably complete about mentioning everything that EXPLAIN can
  output, including the "Rows Removed" outputs that were added by
  Marko Tiikkaja's recent documentation-free patch.  I also updated
  the examples to be consistent with current behavior; several of them
  were not close to what the current code will do.  No doubt there's
  more that can be done here, but I'm out of patience for today.

- Fix index matching for operators with mixed collatable/noncollatable
  inputs.  If an indexable operator for a non-collatable indexed
  datatype has a collatable right-hand input type, any OpExpr for it
  will be marked with a nonzero inputcollid (since having one
  collatable input is sufficient to make that happen).  However, an
  index on a non-collatable column certainly doesn't have any
  collation.  This caused us to fail to match such operators to their
  indexes, because indxpath.c required an exact match of index
  collation and clause collation.  It seems correct to allow a match
  when the index is collation-less regardless of the clause's
  inputcollid: an operator with both noncollatable and collatable
  inputs could perhaps depend on the collation of the collatable
  input, but it could hardly expect the index for the noncollatable
  input to have that same collation.  Per bug #6232 from Pierre
  Ducroquet.  His example is specifically about "hstore ? text" but
  the problem seems quite generic.

- Fix recursion into previously planned sub-query in
  examine_simple_variable.  This code was looking at the sub-Query
  tree as seen in the parent query's RangeTblEntry; but that's the
  pristine parser output, and what we need to look at is the tree as
  it stands at the completion of planning.  Otherwise we might pick up
  a Var that references a subquery that got flattened and hence has no
  RelOptInfo in the subroot.  Per report from Peter Geoghegan.

- Support GiST index support functions that want to cache data across
  calls.  pg_trgm was already doing this unofficially, but the
  implementation hadn't been thought through very well and leaked
  memory.  Restructure the core GiST code so that it actually works,
  and document it.  Ordinarily this would have required an extra
  memory context creation/destruction for each GiST index search, but
  I was able to avoid that in the normal case of a non-rescanned
  search by finessing the handling of the RBTree.  It used to have its
  own context always, but now shares a context with the scan-lifespan
  data structures, unless there is more than one rescan call.  This
  should make the added overhead unnoticeable in typical cases.

- Cache the result of makesign() across calls of gtrgm_penalty().
  Since gtrgm_penalty() is usually called many times in a row with the
  same "newval" (to determine which item on an index page newval fits
  into best), the makesign() calculation is repetitious.  It's
  expensive enough to make it worth caching the result, so do so.  On
  my machine this is good for more than a 40% savings in the time
  needed to build a trigram index on /usr/share/dict/words.  This is
  all per a suggestion of Heikki's.  In passing, make some
  mostly-cosmetic improvements in the caching logic in the other
  functions in this file that rely on caching info in fn_extra.

- Improve generated column names for cases involving sub-SELECTs.
  We'll now use "exists" for EXISTS(SELECT ...), "array" for
  ARRAY(SELECT ...), or the sub-select's own result column name for a
  simple expression sub-select.  Previously, you usually got
  "?column?" in such cases.  Marti Raudsepp, reviewed by Kyotaro

- Restructure error handling in reading of postgresql.conf.  This
  patch has two distinct purposes: to report multiple problems in
  postgresql.conf rather than always bailing out after the first one,
  and to change the policy for whether changes are applied when there
  are unrelated errors in postgresql.conf.  Formerly the policy was to
  apply no changes if any errors could be detected, but that had a
  significant consistency problem, because in some cases specific
  values might be seen as valid by some processes but invalid by
  others.  This meant that the latter processes would fail to adopt
  changes in other parameters even though the former processes had
  done so.  The new policy is that during SIGHUP, the file is rejected
  as a whole if there are any errors in the "name = value" syntax, or
  if any lines attempt to set nonexistent built-in parameters, or if
  any lines attempt to set custom parameters whose prefix is not
  listed in (the new value of) custom_variable_classes.  These tests
  should always give the same results in all processes, and provide
  what seems a reasonably robust defense against loading values from
  badly corrupted config files.  If these tests pass, all processes
  will apply all settings that they individually see as good, ignoring
  (but logging) any they don't.  In addition, the postmaster does not
  abandon reading a configuration file after the first syntax error,
  but continues to read the file and report syntax errors (up to a
  maximum of 100 syntax errors per file).  The postmaster will still
  refuse to start up if the configuration file contains any errors at
  startup time, but these changes allow multiple errors to be detected
  and reported before quitting.  Alexey Klyukin, reviewed by Andy
  Colson and av (Alexander ?) with some additional hacking by Tom Lane

Robert Haas pushed:

- Update obsolete comments.  This was partially fixed by
  57fdb2b0d835fe201434fc28bf5dabf83ada26d1, back in 2005, but it
  missed a couple of spots.  YAMAMOTO Takashi

- heap_update() must recheck tuple after unlocking and relocking
  buffer.  Bug found by Alvaro Herrera, fix suggested by Heikki
  Linnakangas and reviewed by Tom Lane.

- sepgsql uavc comment improvements.  Robert Haas and KaiGai Kohei

- Update comments related to the crash-safety of the visibility map.
  In hio.c, document how we avoid deadlock with respect to visibility
  map buffer locks.  In visibilitymap.c, update the LOCKING section of
  the file header comment.  Both oversights noted by Heikki

Alvaro Herrera pushed:

- Fix typo

- Remove dependency on error ordering in isolation tests.  We now
  report errors reported by the just-unblocked and unblocking
  transactions identically; this should fix relatively common
  buildfarm failures reported by animals that are failing the "wrong"

Bruce Momjian pushed:

- In pg_upgrade, because toast table names can be mismatched with the
  heap oid on 8.4, modify the toast name comparison test to only apply
  to old 9.0+ servers.  (The test was previously 8.4+.) Backpatch to

- Fix pg_upgrade for EXEC_BACKEND builds (e.g. Windows) by properly
  passing the -b/binary-upgrade flag.  Backpatch to 9.1.X.

- In pg_upgrade, remove unnecessary local variable.

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Peter Geoghegan sent in another revision of the patch to inline
comparison operators.

Shigeru HANADA sent in another revision of the patch to display
accumulated autovacuum cost.

Noah Misch and Alvaro Herrera traded patches to test for isolation

Fujii Masao sent in two more revisions of the patch to enable making a
base backup from a hot standby.

Bruce Momjian sent in two more revisions of a patch to fix testing for

Andreas Karlsson sent in a patch to allow for EXECUTE tab completion in

Tom Lane sent in a WIP patch to break a circular dependency in
snapshot management.

Marti Raudsepp sent in a patch to log crashed backends.

Brar Piening sent in another revision of the patch to support VS2010.

Joachim Wieland sent in another revision of the patch to enable
exporting and synchronizing snapshots.

KaiGai Kohei sent in another revision of the patch to rework DROP into
a single framework.

KaiGai Kohei sent in another revision of the patch to fix certain
leaks in VIEWs.

Bruce Momjian sent in another revision of a patch to fix pg_upgrade.

Gurjeet Singh sent in a patch to remove savepointLevel from

Alvaro Herrera sent in a patch to make SLRU's truncate use callbacks.

Fujii Masao sent in a patch which prevents the creation of
restartpoints by using rm_safe_restartpoint callback when a consistent
state is not yet reached and the invalid-page table is not empty.

Kyotaro HORIGUCHI sent in another revision of the patch to add

KaiGai Kohei sent in another revision of the patch to add object
access hooks with argument support.

Jeff Davis sent in two more revisions of the patch to add range types.

Bruce Momjian sent in a patch which makes an empty string the default
for external_pid_file in postgresql.conf to make it consistent with
other defaults there.

Bruce Momjian sent in a patch to add a configuration directory setting
for pg_upgrade.

Simon Riggs sent in another revision of a patch to separate the
background writer process from the checkpointer.

Tom Lane sent in a WIP patch to remove custom variable classes for
GUCs.   There hadn't been a way to validate them anyhow, so now
arbitrary GUCs are allowed.


No Trackbacks


Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options