Skip to content

Wöchentlicher PostgreSQL Newsletter - 01. März 2009

Der Originalartikel befindet sich unter:

== Wöchentlicher PostgreSQL Newsletter - 01. März 2009 ==

Die Denver PUG trifft sich am 27. März. Details folgen.

Michael Renner wird einen Workshop über PostgreSQL Replikation auf
der Netways OSDC 2009 in Nürnbern, Deutschland geben.

PostgreSQLFr wird einen Stand auf der Solutions Linux 2009 in Paris
haben. Anmelden unter sas AT postgresql DOT fr oder im
französischen Wiki.

== PostgreSQL Produkt Neuigkeiten ==

Npgsql2 2.0.3, a .NET Data Provider für PostgreSQL, erschienen.

pgpool-II 2.2, ein Connection Pooler und mehr, und pgpoolAdmin 2.2, ein
GUI Frontend für pgpool-II, sind erschienen.

Simpycity, ein Python Mapping System für PostgreSQL, ist erschienen.

== PostgreSQL 8.4 Feature der Woche ==

pg_stat_statements. Dieses Contrib-Modul von Itagaki Takahiro gibt dir
- wie bei pg_fouine - die "Top 25 Anfragen" in der Datenbank, fast in

== PostgreSQL Tipp der Woche ==

Autovacuum sollte für die meisten Datenbankanwendungen aktiviert sein,
allerdings sollte es für Imports großer Datenmengen wie z. B. ETL oder
beim Wiederherstellen einer Datenbank abgeschaltet werden.

== PostgreSQL Jobs im März ==

== PostgreSQL Lokal ==

Die Deutsche PostgreSQL User Gruppe wird einen Stand, 2 Vorträge und
einen Workshop auf den Chemnitzer Linuxtagen am 14. und 15. März 2009
haben. Mehr Informationen auf Deutsch hier:

PostgreSQL Conference U.S. wird einen PgDay auf dem Linuxfest Nordwest
am 25. und 26. April veranstalten. Der Call for Papers ist unter:

PGCon 2009 wird vom 21. bis 22. Mai 2009 in Ottawa an der Universität
von Ottawa stattfinden. Dem gehen zwei Tage mit Tutorials vom 19. bis
20. Mai 2009 voraus.

Termin vormerken: pgDay San Jose, am Sonntag dem 19. Juli unmittelbar
vor der OSCON. CfP und mehr Informationen bald.

PGCon Brasilien wird am 23.-24. Oktober 2009 auf dem Unicamp in
Campinas, Sao Paulo, stattfinden.

== PostgreSQL in den News ==

Planet PostgreSQL:

Dieser wöchentliche PostgreSQL Newsletter wurde erstellt von David
Fetter, Josh Berkus und Devrim GUNDUZ.

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

== Angewandte Patches ==

Heikki Linnakangas committed:

- Change the signaling of end-of-recovery. Startup process now
  indicates end of recovery by exiting with exit code 0, like in
  previous releases.  Per Tom's suggestion.

- In pgsql/src/backend/postmaster/postmaster.c, put back a "continue"
  that went missing in the changes to start background writer in WAL

- In pgsql/src/pl/plpgsql/src/pl_exec.c, set isnull for errm and
  sqlstate local variables when they're free'd. Because they are out
  of scope for any code after that anyway, leaving isnull true should
  be harmless. However, PL/pgSQL Debugger doesn't seem to care about
  the scoping and crashed, per report by Robert Walker (bug #4635).
  And it's good to be tidy for debugging purposes too.  Fix in 8.3,
  8.2 and 8.1 branches, CVS HEAD was fixed earlier already.  Analysis
  and fix by Ashesh Vashi and Dave Page.

- In pgsql/src/backend/commands/conversioncmds.c, in CREATE
  CONVERSION, test that the given function is a valid conversion
  function for the specified source and destination encodings.  We do
  that by calling the function with an empty string.  If it can't
  perform the requested conversion, it will throw an error.  Backport
  to 7.4 - 8.3. Per bug report #4680 by Denis Afonin.

Peter Eisentraut committed:

- In pgsql/src/pl/plpgsql/src/pl_exec.c, add quotes to message.

- Add the possibility to specify an explicit validator function for
  foreign-data wrappers (similar to procedural languages).  This way
  we don't need to retain the nearly empty libraries, and we are more
  free in how to implement the wrapper API in the future.

- Don't append epoch to log_filename if no format specifier is given.
  Robert Haas

- Sort the output of --help mostly alphabetical, make it align better,
  make help of pg_dump and pg_dumpall more similar.

- Remove feof(stdin) calls related to when to prompt for a password,
  leftovers from when the password was read from stdin.

- In pgsql/contrib/vacuumlo/vacuumlo.c, clean up help (-?) output.

- In pgsql/src/bin/pg_config/pg_config.c, use the same style in the
  help synopsis that other programs are using.

- Add a -w/--no-password option that prevents all password prompts to
  all programs that have a -W/--password option.  In passing, remove
  the ancient PSQL_ALWAYS_GET_PASSWORDS compile option.

- Final removal of -q options, which haven't done anything since 8.3
  and were marked for removal in 8.4.

- Fix the inconsistent and wrong claims that the option value in

- Equip the programs installed by contrib with proper --help and
  --version options and normally formatted help output.

- In pgsql/src/bin/psql/help.c, add the long options to the psql
  --help display, where they were curiously missing.  Since this
  touches most lines of the help output, also change the mix of puts
  and printf calls to printf everywhere, for easier code editing and

Tom Lane committed:

- In pgsql/src/bin/psql/describe.c, fix psql's \dD to show only one
  row per domain, even when the domain has multiple check constraints.

- Repair a longstanding bug in CLUSTER and the rewriting variants of
  ALTER TABLE: if the command is executed by someone other than the
  table owner (eg, a superuser) and the table has a toast table, the
  toast table's pg_type row ends up with the wrong typowner, ie, the
  command issuer not the table owner.  This is quite harmless for most
  purposes, since no interesting permissions checks consult the
  pg_type row.  However, it could lead to unexpected failures if one
  later tries to drop the role that issued the command (in 8.1 or
  8.2), or strange warnings from pg_dump afterwards (in 8.3 and up,
  which will allow the DROP ROLE because we don't create a "redundant"
  owner dependency for table rowtypes).  Problem identified by Cott
  Lang.  Back-patch to 8.1.  The problem is actually far older --- the
  CLUSTER variant can be demonstrated in 7.0 --- but it's mostly
  cosmetic before 8.1 because we didn't track ownership dependencies
  before 8.1.  Also, fixing it before 8.1 would require changing the
  call signature of heap_create_with_catalog(), which seems to carry a
  nontrivial risk of breaking add-on modules.

- Get rid of the rather fuzzily defined FlattenedSubLink node type in
  favor of making pull_up_sublinks() construct a full-blown JoinExpr
  tree representation of IN/EXISTS SubLinks that it is able to convert
  to semi or anti joins.  This makes pull_up_sublinks() a shade more
  complex, but the gain in semantic clarity is worth it.  I still have
  more to do in this area to address the previously-discussed
  problems, but this commit in itself fixes at least one bug in HEAD,
  as shown by added regression test case.

- In pgsql/src/backend/utils/adt/ruleutils.c, fix an old problem in
  decompilation of CASE constructs: the ruleutils.c code looks for a
  CaseTestExpr to figure out what the parser did, but it failed to
  consider the possibility that an implicit coercion might be inserted
  above the CaseTestExpr.  This could result in an Assert failure in
  some cases (but correct results if Asserts weren't enabled), or an
  "unexpected CASE WHEN clause" error in other cases.  Per report from
  Alan Li.  Back-patch to 8.1; problem doesn't exist before that
  because CASE was implemented differently.

- Remove outdated join_1.out regression test comparison file.  This
  has been broken for more than a month, so evidently it's not needed,
  at least not for any configuration in the buildfarm.  We can correct
  it and replace it later if we find something that still needs it.

- In pgsql/src/backend/optimizer/util/pathnode.c, improve
  create_unique_path to not be fooled by unrelated clauses that happen
  to be syntactically part of a semijoin clause.  For example given
  WHERE EXISTS(SELECT ... WHERE upper.var = lower.var AND
  some-condition) where some-condition is just a restriction on the
  lower relation, we can use unique-ification on lower.var after
  having applied some-condition within the scan on lower.

- Tighten up join ordering rules to account for recent more-careful
  analysis of the associativity of antijoins.  Also improve
  optimizer/README discussion of outer join ordering rules.

- In pgsql/src/backend/optimizer/prep/prepjointree.c, temporarily (I
  hope) disable flattening of IN/EXISTS sublinks that are within the
  ON clause of an outer join.  Doing so is semantically correct but
  results in de-optimizing queries that were structured to take
  advantage of the sublink style of execution, as seen in recent
  complaint from Kevin Grittner.  Since the user can get the other
  behavior by reorganizing his query, having the flattening happen
  automatically is just a convenience, and that doesn't justify
  breaking existing applications.  Eventually it would be nice to
  re-enable this, but that seems to require a significantly different
  approach to outer joins in the executor.

- Reduce the maximum value of vacuum_cost_delay and
autovacuum_vacuum_cost_delay to 100ms (from 1000).  This still seems to
be comfortably larger than the useful range of the parameter, and it
should help discourage people from picking uselessly large values.
Tweak the documentation to recommend small values, too.  Per discussion
of a couple weeks ago.

- In pgsql/src/backend/optimizer/path/pathkeys.c, shave a few cycles
  in compare_pathkeys() by checking for pointer-identical input lists
  before we grovel through the lists.  This doesn't save much, but
  testing shows that the case of both inputs NIL is common enough that
  it saves something.  And this is used enough to be a hotspot.

- Fix buffer allocations in encoding conversion routines so that they
  won't fail on zero-length inputs.  This isn't an issue in normal use
  because the conversion infrastructure skips calling the converters
  for empty strings.  However a problem was created by yesterday's
  patch to check whether the right conversion function is supplied in
  CREATE CONVERSION.  The most future-proof fix seems to be to make
  the converters safe for this corner case.

Magnus Hagander committed:

- In pgsql/src/tools/msvc/, remove references to foreign
  data wrapper libraries, since they have been removed. This should
  unbreak the msvc build again.

Andrew Dunstan committed:

- In pgsql/src/backend/utils/adt/xml.c, don't mangle xml and xpath
  unless xml is not in fact a well-formed document, in xpath(). If
  mangling xpath, make a saner attempt where xpath expression does not
  begin with '/'.

== Abgelehnte Patches (bis jetzt) ==

James Pye's patch which changes the behavior of xpath processing to
account for fragments.  Returned for review as it changes a
functionality in a non-bug-fixing way.

Sushint Sinhas's patch to add space when FragmentDelimiter is
specified in tsearch2.  Functionality is available via less invasive

Pavel Stehule's patch to support a mix of named parameters and default
values for functions.  Too much ambiguity in interpretation.

== Eingesandte Patches ==

KaiGai Kohei sent in two more revisions of his SE-PostgreSQL patches.

Alan Li sent in a patch to fix a back-end assertion failure which can
be triggered by defining a VIEW with CASE on a table.

Martin Pihlak sent in a patch to have the \d commands treat
information schema as system objects, making them invisible by
default.  They are still visible via the \d*S commands.

Damien Clochard sent in a patch to psql which shows tables which inherit
a given table when invoking \d table.

Heikki Linnakangas sent in a WIP patch for Hot Standby showing how
transaction tracking could work using "unobserved xids," now actually
all master transactions, alone.

Robert Lor sent in a doc patch for the recently added DTrace probes.

Dave Page sent in a patch from Ashesh Vashi which fixes a PL/PgSQL bug
in 8.3 where after setting state_var->value and errm_var->value to
null, state_var->isnull and errm_var->isnull are not set

Peter Eisentraut sent in a patch to add SQL:2008 error codes for LIMIT
and OFFSET values.

Peter Eisentraut sent in a patch which prevents PostgreSQL from
violating the system limit of maximum open files.


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