Skip to content

Wöchentlicher PostgreSQL Newsletter - 09. Mai 2009

Der Originalartikel befindet sich unter:

== Wöchentlicher PostgreSQL Newsletter - 09. Mai 2009 ==

Neue Umfrage: Welches 8.4 Feature findest du am faszinierendsten?

Die PostgreSQL Webseite benötigt eine Generalüberholung, 2000
Dollar liegen auf dem Tisch dafür. Erhalte Ruhm (und ein kleines)
Vermögen mit deinen Fähigkeiten.

PGDay Junin / Buenos Aires Unnoba wird am 6. Juni 2009 stattfinden.

PGDay.EU 2009 wird an der Telecom ParisTech Universität in Paris,
Frankreich, am 6. und 7. Nivember 2009 stattfinden.

== PostgreSQL Produkt Neuigkeiten ==

check_postgres 2.8.0, ein Nagios Plugin zum Monitoren von PostgreSQL.
ist erschienen.

Jopr 2.2, eine Server Management und Monitoring Applikation,
ist erschienen.

Das PostgreSQL RPM Building Project hat eine neue Version der
PostgreSQL Live CD veröffentlicht.

Npgsql2 2.0.5, ein .NET Data Provider für PostgreSQL, ist erschienen.

pgAdmin 1.10 Beta 3, ein GUI Management Tool für PostgreSQL,
ist erschienen.

pgpool-II 2.2.2, ein Connection Pooler und mehr, ist erschienen.

Die United States PostgreSQL Association hat nun Non-Profit-Status
nach 501(c)(3). Glückwünsche!

PostGIS 1.3.6, ein Geospatial System für PostgreSQL, ist erschienen.

Slony 1.2.16 und 2.0.2, ein kaskadierendes Master-Slave
Replikationssystem für PostgreSQL, ist erschienen.

== PostgreSQL 8.4 Feature der Woche ==

Windowing Funktionen. Diese Reporting Anfragen wurden gerade viel, viel
einfacher. PARTITION BY ist sehr hilfreich in diesem Bereich.

== PostgreSQL Tipp der Woche ==

Die referenzierte ("Child") Seite eines Foreign-Key Feldes sollte
generell indiziert sein, außer der referenzierte Wert ist von sehr
niedriger Kardinalität.
PostgreSQL erledigt dies nicht automatisch.

== PostgreSQL Jobs im Mai ==

== PostgreSQL Lokal ==

Gabriele Bartolini von 2ndQuadrant Italien wird am 20. Mai in Pisa
an der Veranstaltung "Open Source: Unternehmen treffen die Studenten",
organisiert von den Toskana Open Source Software Werkstätten,
teilnehmen. Mehr Informationen (aut italienisch):

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.

PgDay Florianopolis wird am 22. Mai stattfinden. Kontaktiere Dickson S.
Guedes (guedes AT guedesoft DOT net) um teilzunehmen oder einen Vortrag

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 und Josh Berkus.

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

== Angewandte Patches ==

Peter Eisentraut committed:

- Replace a couple of references to files that no longer exist in the
  source tree with references to the appropriate URLs.  Robert Haas

- Disable the use of Unicode escapes in string constants (U&'') when
  standard_conforming_strings is not on, for security reasons.

Magnus Hagander committed:

- In pgsql/src/backend/port/win32_shmem.c, call SetLastError(0) before
  calling the file mapping functions to make sure that the error code
  is reset, as a precaution in case the API doesn't properly reset it
  on success. This could be necessary, since we check the error value
  even if the function doesn't fail for specific success cases.

- In pgsql/src/port/dirmod.c, update no longer current comments in

- In pgsql/src/backend/port/win32_shmem.c, make the win32 shared
  memory code try 10 times instead of one if it fails because the
  shared memory segment already exists. This means it can take up to
  10 seconds before it reports the error if it does exist, but
  hopefully it will make the system capable of restarting even when
  the server is under high load.

Heikki Linnakangas committed:

- Fix the query used for \d against 8.2 and 8.3 servers.

- Request XLOG switch before writing checkpoint in pg_start_backup().
  Otherwise you can end up with an unrecoverable backup if you start a
  new base backup right after finishing archive recovery. In that
  scenario, the redo pointer of the checkpoint that pg_start_backup()
  writes points to the XLOG segment where the timeline-changing
  end-of-archive-recovery checkpoint is. The beginning of that segment
  contains pages with the old timeline ID, and we don't accept that in
  recovery unless we find a history file covering the old timeline ID.
  If you omit pg_xlog from the base backup and clear the archive
  directory before starting the backup, there will be no such history
  file available.  The bug is present in all versions since PITR was
  introduced in 8.0, but I'm back-patching only back to 8.2. Earlier
  versions didn't have XLOG switch records, making this fix
  unfeasible. Given the lack of reports until now, it doesn't seem
  worthwhile to spend more effort to fix 8.0 and 8.1.  Per report and
  suggestion by Mikael Krantz

- Add alternative expected output files for cs_CZ locale for
  btree_gist and tsearch2 tests.  This should make 'comet_moth'
  buildfarm member pass contrib check.  Zdenek Kotala.

Tom Lane committed:

- In pgsql/src/bin/psql/describe.c, code review for \df rewrite: fix
  assorted bugs, make type and volatility columns localizable.

- In pgsql/src/backend/commands/analyze.c, avoid integer overflow in
  the loop that extracts histogram entries from ANALYZE's total
  sample.  The original coding is at risk of overflow for statistics
  targets exceeding about 2675; this was not a problem before 8.4 but
  it is now.  Per bug #4793 from Dennis Noordsij.

- In pgsql/src/backend/access/nbtree/nbtpage.c, update comment for

- Insert CHECK_FOR_INTERRUPTS() calls into btree and hash index scans
  at the points where we step right or left to the next page.  This
  should ensure reasonable response time to a query cancel request
  during an unsuccessful index scan, as seen in recent gripe from Marc
  Cousin.  It's a bit trickier than it might seem at first glance,
  because CHECK_FOR_INTERRUPTS() is a no-op if executed while holding
  a buffer lock.  So we have to do it just at the point where we've
  dropped one page lock and not yet acquired the next.  Remove
  CHECK_FOR_INTERRUPTS calls at the top level of btgetbitmap and
  hashgetbitmap, since they're pointless given the added checks.  I
  think that GIST is okay already --- at least, there's a
  CHECK_FOR_INTERRUPTS at a plausible-looking place in gistnext().  I
  don't claim to know GIN well enough to try to poke it for this, if
  indeed it has a problem at all.  This is a pre-existing issue, but
  in view of the lack of prior complaints I'm not going to risk

- Install a "dead man switch" to allow the postmaster to detect cases
  where a backend has done exit(0) or exit(1) without having
  disengaged itself from shared memory.  We are at risk for this
  whenever third-party code is loaded into a backend, since such code
  might not know it's supposed to go through proc_exit() instead.
  Also, it is reported that under Windows there are ways to externally
  kill a process that cause the status code returned to the postmaster
  to be indistinguishable from a voluntary exit (thank you,
  Microsoft).  If this does happen then the system is probably hosed
  --- for instance, the dead session might still be holding locks.  So
  the best recovery method is to treat this like a backend crash.  The
  dead man switch is armed for a particular child process when it
  acquires a regular PGPROC, and disarmed when the PGPROC is released;
  these should be the first and last touches of shared memory
  resources in a backend, or close enough anyway.  This choice means
  there is no coverage for auxiliary processes, but I doubt we need
  that, since they shouldn't be executing any user-provided code
  anyway.  This patch also improves the management of the EXEC_BACKEND
  ShmemBackendArray array a bit, by reducing search costs.  Although
  this problem is of long standing, the lack of field complaints seems
  to mean it's not critical enough to risk back-patching; at least not
  till we get some more testing of this mechanism.

- In pgsql/src/backend/storage/ipc/ipc.c, install an atexit(2)
  callback that ensures that proc_exit's cleanup processing will still
  be performed if something in a backend process calls exit()
  directly, instead of going through proc_exit() as we prefer.  This
  is a second response to the issue that we might load third-party
  code that doesn't know it should not call exit().  Such a call will
  now cause a reasonably graceful backend shutdown, if possible.  (Of
  course, if the reason for the exit() call is out-of-memory or some
  such, we might not be able to recover, but at least we will try.)

- Make new complaint about unsafe Unicode literals include an error
  location.  Every other ereport in scan.l has one, this should too.

- In pgsql/src/backend/port/win32_shmem.c, minor improvement: avoid
  assuming that GetLastError value cannot be affected by CloseHandle()
  or Sleep().

- In pgsql/src/backend/commands/dbcommands.c, tweak a comment to agree
  a bit better with the new dispensation that locales are
  database-wide, not server-wide.

- Fix ecpg tests for change that disallowed Unicode literals unless
  standard_conforming_strings is on.

- Modify CREATE DATABASE to enforce that the source database's
  encoding setting must be used for the new database, except when
  copying from template0.  This is the same rule that we now enforce
  for locale settings, and it has the same motivation: databases other
  than template0 might contain data that would be invalid according to
  a different setting.  This represents another step in a continuing
  process of locking down ways in which encoding violations could
  occur inside the backend.  Per discussion of a few days ago.  In
  passing, fix pre-existing breakage of, and fix up a
  couple of ereport() calls in dbcommands.c that failed to specify
  sqlstate codes.

- In pgsql/src/backend/optimizer/plan/initsplan.c, tweak
  distribute_qual_to_rels so that when we decide a pseudoconstant qual
  can be pushed to the top of the join tree, we update both the relids
  and qualscope variables to keep them in sync.  This prevents a
  possible later failure of an Assert clause, and affects nothing else
  since qualscope isn't used later except for that Assert.  At the
  moment the Assert shouldn't be reachable when we've pushed the qual
  up; but this is cheap insurance, and it's more sensible anyway in
  terms of the overall logic of the routine.  Per analysis of a bug
  report from Stefan Huehner.  I'm not back-patching this since it's
  just future-proofing; but if anyone gets tempted to change
  check_outerjoin_delay again in the back branches, this might be

- In pgsql/src/backend/optimizer/plan/initsplan.c, ooops ...
  make_outerjoininfo wasn't actually enforcing the join order
  restrictions specified for semijoins in optimizer/README, to wit
  that you can't reassociate outer joins into or out of the RHS of a
  semijoin.  Per report from Heikki Linnakangas.

- Change pgbench to use the table names pgbench_accounts,
  pgbench_branches, pgbench_history, and pgbench_tellers, rather than
  just accounts, branches, history, and tellers.  This is to prevent
  accidental conflicts with real application tables, as has been
  reported to happen at least once.  Also remove the automatic "SET
  search_path = public" that it did at startup, as this seems to
  restrict testing flexibility without actually buying much.  Per
  proposal by Joshua Drake and ensuing discussion.  Joshua Drake and
  Tom Lane.

- Add an option to AlterTableCreateToastTable() to allow its caller to
  force a toast table to be built, even if the sum-of-column-widths
  calculation indicates one isn't needed.  This is needed by
  pg_migrator because if the old table has a toast table, we have to
  migrate over the toast table since it might contain some live data,
  even though subsequent column drops could mean that no
  recently-added rows could require toasting.

Bruce Momjian committed:

- In pgsql/src/include/access/transam.h, 'PGDLLIMPORT'
  ShmemVariableCache, needed for function linkage on
  Win32.  Tested by Hiroshi Saito.

- In pgsql/src/tools/fsync/test_fsync.c, add missing third argument to

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Robert Haas sent in another revision of the ALTER TABLE ... ALTER

Andres Freund sent in two revisions of a patch which implements ALTER

Dickson S. Guedes sent in a patch to add a way to display both client
and server versions in the psql prompt.

Seth Robertson sent in a patch to automate selection of client
certificates in libpq.

Andrew (RhodiumToad) Gierth sent in a patch to change
src/backend/libpq/be-secure.c from calling
SSL_CTX_use_certificate_file to SSL_CTX_use_certificate_chain_file.

Joshua Drake sent in a patch to make strict-clean.

Khee Chin sent in a patch to make psql's \di show the type of index

David Fetter sent in a patch to make all the perl programs


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