Skip to content

Wöchentlicher PostgreSQL Newsletter - 13. Februar 2011

Der Originalartikel befindet sich unter:

== Wöchentlicher PostgreSQL Newsletter - 13. Februar 2011 ==

Selena Deckelmann gibt einen Onlinekurs bei CodeLesson:

== PostgreSQL Jobs im Februar ==

== PostgreSQL Lokal ==

NYPUG trifft sich am 15. Februar 2011 um 18:15 Uhr im Yodle Office,
50 W 23rd St, New York, NY. Mehr Informationen und Anmeldung unter:

PGDay findet dieses Jahr während der südkalifornischen Linux Ausstellung
(SCALE) im LAX Hilton Hotel in Los Angeles, Kalifornien am Freitag dem
25. Februar 2011 statt.

PostgreSQL Conference East 2011: New York,
vom 22. bis 25. März

Das Open Database Camp findet vom 7. bis 9. Mai 2011 in Sardinien,
Italien statt.

PGCon findet am 19. und 20. Mai 2011 an der Universität
von Ottawa statt, vorher gibt es am 17. und 18. Mai
zwei Tage mit Trainings.

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

Erik Rijkers reviewed the patch to add range types.

== Angewandte Patches ==

Bruce Momjian pushed:

- remove tags.;a=commitdiff;h=ad762426333aac5bd8e1ceac753bebe4a6411c28

- Remove tabs in SGML file.

- Remove more SGML tabs.

- Fix pg_get_encoding_from_locale() function call parameters to match
  prototype for cases where there is no multi-language support.

- Fix "variable not used" warnings when USE_WIDE_UPPER_LOWER is not

- Properly handle Win32 paths of 'E:abc', which can be either absolute
  or relative, by creating a function path_is_relative_and_below_cwd()
  to check for specific requirements.  It is unclear if this fixes a
  security problem or not but the new code is more robust.

- Fix reverse 'if' test in path_is_relative_and_below_cwd(), per Tom

- Back out libpq doc change;  not ready yet.

ITAGAKI Takahiro pushed:

- Fix error messages for FreeFile in COPY command.  They are extracted
  from COPY API patch.  suggested by Noah Misch;a=commitdiff;h=fb7355e0cedfadd437c30c73cfb71af7ff8933b1

- Fix a comment for MergeAttributes.  We forgot to adjust it when we
  changed relistemp to relpersistence.;a=commitdiff;h=c18f51da17d8cf01d62218e0404e18ba246bde54

- Fix typo in the documentation.  By Kevin Grittner.

Andrew Dunstan pushed:

- Supply now required HeUTF8 macro for plperl where it's missing, per
  buildfarm results.;a=commitdiff;h=c852e95b0bcbe708ba64b9499e50b9cc98bd7b71

- Use correct name and URL for MinGW-w64, error noticed by Gurjeet Singh.

Heikki Linnakangas pushed:

- Implement genuine serializable isolation level.  Until now, our
  Serializable mode has in fact been what's called Snapshot Isolation,
  which allows some anomalies that could not occur in any serialized
  ordering of the transactions. This patch fixes that using a method
  called Serializable Snapshot Isolation, based on research papers by
  Michael J. Cahill (see README-Steve Singer for full references). In
  Serializable Snapshot Isolation, transactions run like they do in
  Snapshot Isolation, but a predicate lock manager observes the reads
  and writes performed and aborts transactions if it detects that an
  anomaly might occur. This method produces some false positives, ie.
  it sometimes aborts transactions even though there is no anomaly.
  To track reads we implement predicate locking, see
  storage/lmgr/predicate.c.  Whenever a tuple is read, a predicate
  lock is acquired on the tuple.  Shared memory is finite, so when a
  transaction takes many tuple-level locks on a page, the locks are
  promoted to a single page-level lock, and further to a single
  relation level lock if necessary. To lock key values with no
  matching tuple, a sequential scan always takes a relation-level
  lock, and an index scan acquires a page-level lock that covers the
  search key, whether or not there are any matching keys at the
  moment.  A predicate lock doesn't conflict with any regular locks or
  with another predicate locks in the normal sense. They're only used
  by the predicate lock manager to detect the danger of anomalies.
  Only serializable transactions participate in predicate locking, so
  there should be no extra overhead for for other transactions.
  Predicate locks can't be released at commit, but must be remembered
  until all the transactions that overlapped with it have completed.
  That means that we need to remember an unbounded amount of predicate
  locks, so we apply a lossy but conservative method of tracking locks
  for committed transactions.  If we run short of shared memory, we
  overflow to a new "pg_serial" SLRU pool.  We don't currently allow
  Serializable transactions in Hot Standby mode.  That would be hard,
  because even read-only transactions can cause anomalies that
  wouldn't otherwise occur.  Serializable isolation mode now means the
  new fully serializable level.  Repeatable Read gives you the old
  Snapshot Isolation level that we have always had.  Kevin Grittner
  and Dan Ports, reviewed by Jeff Davis, Heikki Linnakangas and Anssi

- Oops, forgot to bump catversion in the Serializable Snapshot
  Isolation patch.  I thought we didn't need that, but then I
  remembered that it added a new SLRU subdirectory, pg_serial. While
  we're at it, document what pg_serial is.;a=commitdiff;h=47082fa875179ae629edb26807ab3f38a775280b

- Fix copy-pasto in description of pg_serial, and silence compiler
  warning about uninitialized field you get on some compilers.;a=commitdiff;h=7202ad7b8dd07864092be70287fe971ec72a3fbc

- UINT64_MAX isn't defined on MSVC.;a=commitdiff;h=f9f9d696a9c51cb26c24bb28760e6bb1edd0e995

- Fix typo, by Kevin Grittner.;a=commitdiff;h=1703f0e8da2e8e3eccb6e12879c011ba106f8a62

- Fix allocation of RW-conflict pool in the new predicate lock
  manager, and also take the RW-conflict pool into account in the
  PredicateLockShmemSize() estimate.

- Allocate all entries in the serializable xid hash up-front, so that
  you don't run out of shared memory when you try to assign an xid to
  a transaction.  Kevin Grittner.

- Send status updates back from standby server to master, indicating
  how far the standby has written, flushed, and applied the WAL. At
  the moment, this is for informational purposes only, the values are
  only shown in pg_stat_replication system view, but in the future
  they will also be needed for synchronous replication.  Extracted
  from Simon riggs' synchronous replication patch by Robert Haas, with
  some tweaking by me.

Robert Haas pushed:

- Avoid having autovacuum workers wait for relation locks.  Waiting
  for relation locks can lead to starvation - it pins down an
  autovacuum worker for as long as the lock is held.  But if we're
  doing an anti-wraparound vacuum, then we still wait; maintenance can
  no longer be put off.  To assist with troubleshooting, if
  log_autovacuum_min_duration >= 0, we log whenever an autovacuum or
  autoanalyze is skipped for this reason.  Per a gripe by Josh Berkus,
  and ensuing discussion.;a=commitdiff;h=32896c40ca766146312b28a5a0eb3f66ca0300ed

- Tweak find_composite_type_dependencies API a bit more.  Per
  discussion with Noah Misch, the previous coding, introduced by my
  commit 65377e0b9c0e0397b1598b38b6a7fb8b6f740d39 on 2011-02-06, was
  really an abuse of RELKIND_COMPOSITE_TYPE, since the caller in
  typecmds.c is actually passing the name of a domain.  So go back
  having a type name argument, but make the first argument a Relation
  rather than just a string so we can tell whether it's a table or a
  foreign table and emit the proper error message.

- Typo fixes.  receivedUpto should be capitalized consistently.

- Allow tab-completion of :variable even as first word on a line.
  Christoph Berg

- Teach ALTER TABLE .. SET DATA TYPE to avoid some table rewrites.
  When the old type is binary coercible to the new type and the using
  clause does not change the column contents, we can avoid a full
  table rewrite, though any indexes on the affected columns will still
  need to be rebuilt.  This applies, for example, when changing a
  varchar column to be of type text.  The prior coding assumed that
  the set of operations that force a rewrite is identical to the set
  of operations that must be propagated to tables making use of the
  affected table's rowtype.  This is no longer true: even though the
  tuples in those tables wouldn't need to be modified, the data type
  change invalidate indexes built using those composite type columns.
  Indexes on the table we're actually modifying can be invalidated
  too, of course, but the existing machinery is sufficient to handle
  that case.  Along the way, add some debugging messages that make it
  possible to understand what operations ALTER TABLE is actually
  performing in these cases.  Noah Misch and Robert Haas

Simon Riggs pushed:

- Extend ALTER TABLE to allow Foreign Keys to be added without initial
  validation.  FK constraints that are marked NOT VALID may later be
  VALIDATED, which uses an ShareUpdateExclusiveLock on constraint
  table and RowShareLock on referenced table. Significantly reduces
  lock strength and duration when adding FKs.  New state visible from
  psql.  Simon Riggs, with reviews from Marko Tiikkaja and Robert Haas;a=commitdiff;h=722bf7017bbe796decc79c1fde03e7a83dae9ada

- Remove rare corner case for data loss when triggering standby
  server.  If the standby was streaming when trigger file arrives,
  check also in the archive for additional WAL files. This is a corner
  case since it is unlikely that we would trigger a failover while the
  master is still available and sending data to standby, while at the
  same time running in archive mode and also while the streaming
  standby has fallen behind archive.  Someone would eventually be
  unlucky; we must plug all gaps however small.  Fujii Masao;a=commitdiff;h=faa0550572583f51dba25611ab0f1d1c31de559b

- Basic Recovery Control functions for use in Hot Standby. Pause,
  Resume, Status check functions only. Also, new recovery.conf
  parameter to pause_at_recovery_target, default on.  Simon Riggs,
  reviewed by Fujii Masao;a=commitdiff;h=8c6e3adbf792c2bba448e88cbf2c8e03fb802e73

- Named restore points in recovery.  Users can record named points,
  then new recovery.conf parameter recovery_target_name allows PITR to
  specify named points as recovery targets.  Jaime Casanova, reviewed
  by Euler Taveira de Oliveira, plus minor edits;a=commitdiff;h=c016ce728139be95bb0dc7c4e5640507334c2339

- Continue long tradition of bumping the catalog version a little

Peter Eisentraut pushed:

- Per-column collation support.  This adds collation support for
  columns and domains, a COLLATE clause to override it per expression,
  and B-tree index support.  Peter Eisentraut reviewed by Pavel
  Stehule, Itagaki Takahiro, Robert Haas, Noah Misch;a=commitdiff;h=414c5a2ea65cbd38d79ffdf9b1fde7cc75c134e0

- Information schema views for collation support.  Add the views
  character_sets, collations, and collation_character_set_applicability.

- Update comment.  It was still claiming that the keyword list is in
  keywords.c, when it is now in kwlist.h.

- DDL support for collations: collowner field, CREATE COLLATION, ALTER
  extensions, pg_dump support for the above, dependency management,
  psql tab completion, psql \dO command

Tom Lane pushed:

- Fix merge conflict.;a=commitdiff;h=0bc0bd07d41169d6de513967615ad9cb3d0f322e

- Core support for "extensions", which are packages of SQL objects.
  This patch adds the server infrastructure to support extensions.
  There is still one significant loose end, namely how to make it play
  nice with pg_upgrade, so I am not yet committing the changes that
  would make all the contrib modules depend on this feature.  In
  passing, fix a disturbingly large amount of breakage in
  AlterObjectNamespace() and callers.  Dimitri Fontaine, reviewed by
  Anssi Kääriäinen, Itagaki Takahiro, Tom Lane, and numerous others;a=commitdiff;h=d9572c4e3b474031060189050e14ef384b94e001

- Suppress some compiler warnings in recent commits.  Older versions
  of gcc tend to throw "variable might be clobbered by `longjmp' or
  `vfork'" warnings whenever a variable is assigned in more than one
  place and then used after the end of a PG_TRY block.  That's
  reasonably easy to work around in execute_extension_script, and the
  overhead of unconditionally saving/restoring the GUC variables seems
  unlikely to be a serious concern.  Also clean up logic in
  ATExecValidateConstraint to make it easier to read and less likely
  to provoke "variable might be used uninitialized in this function"

- Implement "ALTER EXTENSION ADD object".  This is an essential
  component of making the extension feature usable; first because it's
  needed in the process of converting an existing installation
  containing "loose" objects of an old contrib module into the
  extension-based world, and second because we'll have to use it in
  pg_dump --binary-upgrade, as per recent discussion.  Loosely based
  on part of Dimitri Fontaine's ALTER EXTENSION UPGRADE patch.

- Rethink order of operations for dumping extension member objects.
  My original idea of doing extension member identification during
  getDependencies() didn't work correctly: we have to mark member
  tables as not-to-be-dumped rather earlier than that, else their
  subsidiary objects like indexes get dumped anyway.  Rearrange code
  to mark them early enough.

- Fix pg_upgrade to handle extensions.  This follows my proposal of
  yesterday, namely that we try to recreate the previous state of the
  extension exactly, instead of allowing CREATE EXTENSION to run a SQL
  script that might create some entirely-incompatible on-disk state.
  In --binary-upgrade mode, pg_dump won't issue CREATE EXTENSION at
  all, but instead uses a kluge function provided by
  pg_upgrade_support to recreate the pg_extension row (and
  extension-level pg_depend entries) without creating any member
  objects.  The member objects are then restored in the same way as if
  they weren't members, in particular using pg_upgrade's normal hacks
  to preserve OIDs that need to be preserved.  Then, for each member
  object, ALTER EXTENSION ADD is issued to recreate the pg_depend
  entry that marks it as an extension member.  In passing, fix
  breakage in pg_upgrade's enum-type support: somebody didn't fix it
  when the noise word VALUE got added to ALTER TYPE ADD.  Also,
  rationalize parsetree representation of COMMENT ON DOMAIN and fix
  get_object_address() to allow OBJECT_DOMAIN.

- Fix improper matching of resjunk column names for FOR UPDATE in
  subselect.  Flattening of subquery range tables during setrefs.c
  could lead to the rangetable indexes in PlanRowMark nodes not
  matching up with the column names previously assigned to the
  corresponding resjunk ctid (resp.  tableoid or wholerow) columns.
  Typical symptom would be either a "cannot extract system attribute
  from virtual tuple" error or an Assert failure.  This wasn't a
  problem before 9.0 because we didn't support FOR UPDATE below the
  top query level, and so the final flattening could never renumber an
  RTE that was relevant to FOR UPDATE.  Fix by using a plan-tree-wide
  unique number for each PlanRowMark to label the associated resjunk
  columns, so that the number need not change during flattening.  Per
  report from David Johnston (though I'm darned if I can see how this
  got past initial testing of the relevant code).  Back-patch to 9.0.

- Extend "ALTER EXTENSION ADD object" to permit "DROP object" as well.
  Per discussion, this is something we should have sooner rather than
  later, and it doesn't take much additional code to support it.

- Add support for multiple versions of an extension and ALTER
  EXTENSION UPDATE.  This follows recent discussions, so it's quite a
  bit different from Dimitri's original.  There will probably be more
  changes once we get a bit of experience with it, but let's get it in
  and start playing with it.  This is still just core code.  I'll
  start converting contrib modules shortly.  Dimitri Fontaine and Tom

- Clean up installation directory choices for extensions.  Arrange for
  the control files to be in $SHAREDIR/extension not
  $SHAREDIR/contrib, since we're generally trying to deprecate the
  term "contrib" and this is a once-in-many-moons opportunity to get
  rid of it in install paths.  Fix PGXS to install the $EXTENSION file
  into that directory no matter what MODULEDIR is set to; a nondefault
  MODULEDIR should only affect the script and secondary extension
  files.  Fix the control file directory parameter to be interpreted
  relative to $SHAREDIR, to avoid a surprising disconnect between how
  you specify that and what you set MODULEDIR to.  Per discussion with
  David Wheeler.

- Refactor ALTER EXTENSION UPDATE to have cleaner multi-step
  semantics.  This change causes a multi-step update sequence to
  behave exactly as if the updates had been commanded one at a time,
  including updating the "requires" dependencies afresh at each step.
  The initial implementation took the shortcut of examining only the
  final target version's "requires" and changing the catalog entry but
  once.  But on reflection that's a bad idea, since it could lead to
  executing old update scripts under conditions different than they
  were designed/tested for.  Better to expend a few extra cycles and
  avoid any surprises.  In the same spirit, if a CREATE EXTENSION FROM
  operation involves applying a series of update files, it will act as
  though the CREATE had first been done using the initial script's
  target version and then the additional scripts were invoked with
  ALTER EXTENSION UPDATE.  I also removed the restriction about not
  changing encoding in secondary control files.  The new rule is that
  a script is assumed to be in whatever encoding the control file(s)
  specify for its target version.  Since this reimplementation causes
  us to read each intermediate version's control file, there's no
  longer any uncertainty about which encoding setting would get

Magnus Hagander pushed:

- Implement NOWAIT option for BASE_BACKUP command.  Specifying this
  option makes the server not wait for the xlog to be archived, or
  emit a warning that it can't, instead leaving the responsibility
  with the client.  This is useful when the log is being streamed
  using the streaming protocol in parallel with the backup, without
  having log archiving enabled.

- Use NOWAIT when including WAL in base backup.  Avoids warning and
  waiting for the last segment to be archived, which isn't necessary
  when we're including the required WAL in the backup itself.

- Track last time for statistics reset on databases and bgwriter.
  Tracks one counter for each database, which is reset whenever the
  statistics for any individual object inside the database is reset,
  and one counter for the background writer.  Tomas Vondra, reviewed
  by Greg Smith.

Alvaro Herrera pushed:

- Fix the isolation tester compilation on VPATH builds

- Fix isolation tester Makefile so that it runs in a VPATH build

- Fix comment recently obsoleted

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Greg Smith sent in another revision of the patch to spread out
checkpoint syncs.

ITAGAKI Takahiro sent in three more revisions of the patch to export
some of the COPY API, which can be used for, among other things,

Shigeru HANADA sent in two more revisions of the patch to add a
file-based foreign data wrapper for SQL/MED.

Shigeru HANADA sent in another revision of the patch to create a
foreign data wrapper API for SQL/MED.

Radoslaw Smogura sent in a patch to improve the performance of
textsend, used in COPY BINARY.

Andrew Dunstan sent in a patch to fix the regression tests which broke
as a result of the COPY API changes.

Steve Singer and Jan Urbanski traded patches to add subtransactions to
PL/PythonU in support of exception handling.

Ibrary Ahmed sent in another revision of the patch to determine
client_encoding from client locale.

Robert Haas and Thom Brown traded patches for synchronous replication.

Shigeru HANADA sent in another revision of the patch to add foreign
scans in SQL/MED.

Shigeru HANADA sent in a patch to avoid catalog lookups in foreign

Tom Lane sent in another revision of the patch to add support for
EXTENSIONs to pg_dump.

Andrew Dunstan sent in a patch implementing "jagged row" CSV text
arrays atop SQL/MED's file foreign data wrapper feature.

Thom Brown sent in another revision of the patch to fix some boundary
case behavior in generate_series().

Greg Smith sent in a patch to allow pg_archivecleanup to ignore
extensions.  This is especially useful for compressed WALs, which
pg_archivecleanup would otherwise have ignored.

Heikki Linnakangas sent in another revision of that patch to add a
PostgreSQL foreign data wrapper.

Jeff Davis sent in another revision of the patch to add range types.

Greg Smith sent in another revision of the patch to expand the maximum
size of pgbench tests.

Alex Hunsaker and Alexey Klyukin traded patches to convert between
PostgreSQL and Perl arrays for PL/Perl.

Stephen Frost and Kevin Grittner traded some more patches to add
support for logging the current role.

Noah Misch sent in another revision of the patch to add FOR KEY LOCK.

Fujii Masao sent in a patch to fix some infelicities in the new
ability to name restore points.

Per review from ITAGAKI Takahiro, Marko (johto) Tiikkaja sent in
another revision of the patch to add transaction-scope advisory locks.

Robert Haas sent in two more revisions of the patch to enable
synchronous replication.

Heikki Linnakangas sent in patches updating the FDW API and the
PostgreSQL FDW, both for SQL/MED.

Jan Urbanski sent in another flock of patches to improve PL/PythonU.

Stephen Frost sent in three more revisions of the patch to add FOR

Alex Hunsaker sent in a patch to fix an infelicity in PL/Perl's
conversion to and from UTF8.

Per review from YAMAMOTO Takashi, Kevin Grittner sent in a patch to
fix some infelicities in SSI.

Daniel Farina sent in three revisions of a patch to fix some
infelicities in replication server timeout.

Gurjeet Singh sent in a patch to fix some infelicities in re: the
actual existence of hypothetical indexes.

Tom Lane sent in a patch to fix some mismatches between EXTENSIONs and

Daniel Farina sent in another pieces of the synchronous replication
patch, this time for feeding back the xmin of the hot standby.

Peter Eisentraut sent in a patch to add a "make check" target for PLs.

Peter Eisentraut sent in a patch to see to it that psql -l processes
the .psqlrc.


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