Skip to content

Wöchentlicher PostgreSQL Newsletter - 30. Oktober 2011

Der Originalartikel befindet sich unter:

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

Der Call for Papers ist offen für PostgreSQL Session #3,
welche am 2. Februar 2012 in Paris stattfindet. Der Einsendeschluß
ist der 30. November 2011, die ausgewählten Sprecher werden bis
zum 14. Dezember 2011 informiert. Vorträge (in Französisch oder
Englisch) sollen an call-for-paper AT postgresql-sessions DOT org
geschickt werden. Mehr Informationen unter:

== PostgreSQL Produkt Neuigkeiten ==

Benetl 3.8, ein ETL Werkzeug für Dateien, die PostgreSQL
nutzen, ist erschienen.

PL/Proxy 2.3.0, ein schreib-skalierendes Clusteringsystem für
PostgreSQL, ist erschienen.

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

== PostgreSQL Jobs im Oktober ==

== PostgreSQL Lokal ==

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. Das Vortragsprogramm
ist jetzt verfügbar und die Registrierung 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 ==

Magnus Hagander pushed:

- Make TABLE tab complation in psql include all relations.  Not just
  tables, since views also work fine with the TABLE command.

- Support configurable eventlog application names on Windows.  This
  allows different instances to use the eventlog with different
  identifiers, by setting the event_source GUC, similar to how
  syslog_ident works.  Original patch by MauMau, heavily modified by
  Magnus Hagander

- Remove argument decoration that appears unsupported on mingw

- Make event_source visible on all platforms.  On non-windows
  platform, we just ignore any value set there.  Noted by Jaime

- Fix typo

- MingW doesn't support wcstombs_s()...

- Implement streaming xlog for backup tools.  Add option for parallel
  streaming of the transaction log while a base backup is running, to
  get the logfiles before the server has removed them.  Also add a
  tool called pg_receivexlog, which streams the transaction log into
  files, creating a log archive without having to wait for segments to
  complete, thus decreasing the window of data loss without having to
  waste space using archive_timeout. This works best in combination
  with archive_command - suggested usage docs etc coming later.

Alvaro Herrera pushed:

- Add debugging aid in isolationtester

Tom Lane pushed:

- Change FK trigger creation order to better support self-referential
  FKs.  When a foreign-key constraint references another column of the
  same table, row updates will queue both the PK's ON UPDATE action
  and the FK's CHECK action in the same event.  The ON UPDATE action
  must execute first, else the CHECK will check a non-final state of
  the row and possibly throw an inappropriate error, as seen in bug
  #6268 from Roman Lytovchenko.  Now, the firing order of multiple
  triggers for the same event is determined by the sort order of their
  pg_trigger.tgnames, and the auto-generated names we use for FK
  triggers are "RI_ConstraintTrigger_NNNN" where NNNN is the trigger
  OID.  So most of the time the firing order is the same as creation
  order, and so rearranging the creation order fixes it.  This patch
  will fail to fix the problem if the OID counter wraps around or adds
  a decimal digit (eg, from 99999 to 100000) while we are creating the
  triggers for an FK constraint.  Given the small odds of that, and
  the low usage of self-referential FKs, we'll live with that solution
  in the back branches.  A better fix is to change the auto-generated
  names for FK triggers, but it seems unwise to do that in stable
  branches because there may be client code that depends on the naming
  convention.  We'll fix it that way in HEAD in a separate patch.
  Back-patch to all supported branches, since this bug has existed for
  a long time.

- Change FK trigger naming convention to fix self-referential FKs.
  Use names like "RI_ConstraintTrigger_a_NNNN" for FK action triggers
  and "RI_ConstraintTrigger_c_NNNN" for FK check triggers.  This
  ensures the action trigger fires first in self-referential cases
  where the very same row update fires both an action and a check
  trigger.  This change provides a non-probabilistic solution for bug
  #6268, at the risk that it could break client code that is making
  assumptions about the exact names assigned to auto-generated FK
  triggers.  Hence, change this in HEAD only.  No need for forced
  initdb since old triggers continue to work fine.

- Improve planner's ability to recognize cases where an IN's RHS is
  unique.  If the right-hand side of a semijoin is unique, then we can
  treat it like a normal join (or another way to say that is: we don't
  need to explicitly unique-ify the data before doing it as a normal
  join).  We were recognizing such cases when the RHS was a sub-query
  with appropriate DISTINCT or GROUP BY decoration, but there's
  another way: if the RHS is a plain relation with unique indexes, we
  can check if any of the indexes prove the output is unique.  Most of
  the infrastructure for that was there already in the join removal
  code, though I had to rearrange it a bit.  Per reflection about a
  recent example in pgsql-performance.

- Typo fixes.  expect -> except, noted by Andrew Dunstan.  Also,
  "cannot" seems more readable here than "can not", per David Wheeler.

- Add simple script to check for right recursion in Bison grammars.
  We should generally use left-recursion not right-recursion to parse
  lists.  Bison hasn't got any built-in way to check for this type of
  inefficiency, and I didn't find anything on the net in a quick
  search, so I wrote a little Perl script to do it.  Add to src/tools/
  so we don't have to re-invent this wheel next time we wonder if
  we're doing anything stupid.  Currently, the only place that seems
  to need fixing is plpgsql's stmt_else production, so the problem
  doesn't appear to be common enough to warrant trying to include such
  a test in our standard build process.  If we did want to do that,
  we'd need a way to ignore some false positives, such as a_expr :=
  '-' a_expr

- Avoid recursion while processing ELSIF lists in plpgsql.  The
  original implementation of ELSIF in plpgsql converted the construct
  into nested simple IF statements.  This was prone to stack overflow
  with long ELSIF lists, in two different ways.  First, it's difficult
  to generate the parsetree without using right-recursion in the bison
  grammar, and that's prone to parser stack overflow since nothing can
  be reduced until the whole list has been read.  Second, we'd recurse
  during execution, thus creating an unnecessary risk of
  execution-time stack overflow.  Rewrite so that the ELSIF list is
  represented as a flat list, scanned via iteration not recursion, and
  generated through left-recursion in the grammar.  Per a gripe from
  Håvard Kongsgård.

- Update docs to point to the timezone library's new home at IANA.
  The recent unpleasantness with copyrights has accelerated a move
  that was already in planning.

- De-parallelize ecpg build some more.  Make sure ecpg/include/ is
  rebuilt before the other subdirectories, so that ecpg_config.h is up
  to date.  This is not likely to matter during production builds,
  only development, so no back-patch.

- Fix assorted bogosities in cash_in() and cash_out().  cash_out
  failed to handle multiple-byte thousands separators, as per bug
  #6277 from Alexander Law.  In addition, cash_in didn't handle that
  either, nor could it handle multiple-byte positive_sign.  Both
  routines failed to support multiple-byte mon_decimal_point, which I
  did not think was worth changing, but at least now they check for
  the possibility and fall back to using '.' rather than emitting
  invalid output.  Also, make cash_in handle trailing negative signs,
  which formerly it would reject.  Since cash_out generates trailing
  negative signs whenever the locale tells it to, this last omission
  represents a fail-to-reload-dumped-data bug.  IMO that justifies
  patching this all the way back.

- Further improvement of make_greater_string.  Make sure that it
  considers all the possibilities that the old code did, instead of
  trying only one possibility per character position.  To keep the
  runtime in bounds, instead tweak the character incrementers to not
  try every possible multibyte character code.  Remove unnecessary
  logic to restore the old character value on failure.  Additional
  comment and formatting cleanup.

- Support more locale-specific formatting options in cash_out().  The
  POSIX spec defines locale fields for controlling the ordering of the
  value, sign, and currency symbol in monetary output, but cash_out
  only supported a small subset of these options.  Fully implement
  p/n_sign_posn, p/n_cs_precedes, and p/n_sep_by_space per spec.  Fix
  up cash_in so that it will accept all these format variants.  Also,
  make sure that thousands_sep is only inserted to the left of the
  decimal point, as required by spec.  Per bug #6144 from Eduard
  Kracmar and discussion of bug #6277.  This patch includes some ideas
  from Alexander Lakhin's proposed patch, though it is very different
  in detail.

Bruce Momjian pushed:

- Fix pg_bsd_indent bug where newlines were not being trimmed from
  typedef lines.  Update pg_bsd_indent required version to 1.1 (and
  update ftp site).  Problem reported by Magnus Hagander.

- Update pg_upgrade testing instructions.

- Clarify pg_upgrade error message that the 'postgres' database must
  exist in the old cluster.

- Remove pg_upgrade dependency on the 'postgres' database existing in
  the new cluster.   vacuumdb, used by pg_upgrade, still has this

Heikki Linnakangas pushed:

- Fix the number of lwlocks needed by the "fast path" lock patch. It
  needs one lock per backend or auxiliary process - the need for a
  lock for each aux processes was not accounted for in NumLWLocks().
  No-one noticed, because the three locks needed for the three aux
  processes fit into the few extra lwlocks we allocate for 3rd party
  modules that don't call RequestAddinLWLocks()
  (NUM_USER_DEFINED_LWLOCKS, 4 by default).

Robert Haas pushed:

- Change "and and" to "and".  Report by Vik Reykja, patch by Kevin

- Clarify that ORDER BY/FOR UPDATE can't malfunction at higher iso
  levels.  Kevin Grittner

- Remove hard-coded "\connect postgres" from pg_dumpall.  This doesn't
  appear to accompish anything useful, and does make the restore fail
  if the postgres database happens to have been dropped.

- Demote some sanity checks in BufferIsValid() to assertions.  Testing
  reveals that this macro is a hot-spot for index-only-scans.  Per
  discussion with Tom Lane.

- Allow hint bits to be set sooner for temporary and unlogged tables.
  We need not wait until the commit record is durably on disk, because
  in the event of a crash the page we're updating with hint bits will
  be gone anyway.  Per off-list report from Heikki Linnakangas, this
  can significantly degrade the performance of unlogged tables; I was
  able to show a 2x speedup from this patch on a pgbench run with
  scale factor 15.  In practice, this will mostly help small, heavily
  updated tables, because on larger tables you're unlikely to run into
  the same row again before the commit record makes it out to disk.

- Improve make_greater_string() with encoding-specific incrementers.
  This infrastructure doesn't in any way guarantee that the character
  we produce will sort before the one we incremented; but it does at
  least make it much more likely that we'll end up with something that
  is a valid character, which improves our chances.  Kyotaro
  Horiguchi, with various adjustments by me.

- Update visibilitymap.c header comments.  Recent work on index-only
  scans left this somewhat out of date.

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Fujii Masao and Jun Ishiduka traded revisions of the patch to allow
taking a base backup from a hot standby.

Shigeru HANADA sent in two revisions of patches for a PostgreSQL FDW,
along with some generic helper functions and new documentation on how
to write FDWs.

Heikki Linnakangas sent in another revision of the patch to add
multiple tuples at once in COPY.

Simon Riggs sent in two revisions of a patch to fix an issue where hot
backup fails at rsync fails at pg_clog when under load.

Kerem Kat sent in another revision of the patch to add CORRESPONDING
TO set operations.

Pavel Stehule sent in another revision of the patch that allows
PL/pgsql to make arrays of any %TYPE declared.

Alexander Korotkov sent in another revision of the patch to collect
statistics for array columns.

Robert Haas sent in a couple of patches he was using to analyze the
slowness of COUNT(*) in the index-only scan case.

Simon Riggs sent in two revisions of a patch to speed up hot standbys
in the subtransaction case.

Robert Haas sent in a patch to speed up unlogged tables.

Robert Haas sent in a patch that initializes each PGPROC's myProcLocks
just once at postmaster startup rather than every time the PGPROC is
handed out to a backend.


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