Skip to content

Wöchentlicher PostgreSQL Newsletter - 28. August 2011

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 erschienen.

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

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

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:

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

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

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

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

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

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

- Fix to always add  --dbname=$(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 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

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

- Add a regression test for pgstattuple.  This is mainly to prove that
  the NaN fix actually works cross-platform.

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

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

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

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

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

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

- Improve comments describing tsvector data structure.

- 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

- 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=require) could be quite misleading.
  But the results could be even worse in sslmode=prefer: 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=require.  (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.

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

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

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

Bruce Momjian pushed:

- Simplify errno generating in thread testing program.

- Properly call strerror() in thread test;  add comments.

- Mark cpluspluscheck as excutable in git.

- Add missing include so include file compiles cleanly on its own.

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

- In pgrminclude, make skipped include names constent and skip files
  with #if/#ifdefs.

- In pgrminclude, add code to skip includes with a marker comment.

- Fix pgrminclude regex pattern.

- do include files first

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

- Cleanup of script.

- Add another marker.

- Fix #if blocks.

- Add markers.

- Add markers for skips.

- Add missing includes after pgrminclude run.

- Fix missing pgdefine detection in pgrminclude.

- Add postgres.h to *.c files for pg_upgrade, ltree, and btree_gist,
  and remove from local *.h files.  Per suggestion from Alvaro

- Change references of CVS to .git.

- Add another pgdefine path check, and a cvs-git change.

- Add support for #elif to pgrminclude.

- Allow more include files to be compiled in their own by adding
  missing include dependencies.  Modify pgcompinclude to skip a common
  fcinfo error.

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

Robert Haas pushed:

- Typo fix.

- Adjust CREATE DOMAIN example for standard_conforming_strings=on.
  Noted by Hitoshi Harada.

- 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

- Properly quote SQL/MED generic options in pg_dump output.  Shigeru

- 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

Heikki Linnakangas pushed:

- Add recovery.conf to the index in the user manual.  Fujii Masao

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.

- 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).

- Spelling improvement

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

- Document minimum required version of DocBook XSL stylesheets

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

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

Andrew Dunstan pushed:

- Port backup check on psql lexer to MSVC.

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

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


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