== PostgreSQL Weekly News - August 21 2011 ==

== PostgreSQL Weekly News - August 21 2011 ==

== PostgreSQL Product News ==

MyJSQLView 3.30, a GUI tool that can be used with PostgreSQL, released.
http://dandymadeproductions.com/projects/MyJSQLView/

pgpool-II 3.1.0 beta1, a connection pooler and more, released.
http://pgfoundry.org/projects/pgpool/

A German language tutorial for PostgreSQL 9.0 has been released.
http://workshop-postgresql.de

pgwatch 1.0beta2, a monitoring tool for PostgreSQL, released.
http://www.cybertec.at/en/pgwatch/

== PostgreSQL Jobs for August ==

http://archives.postgresql.org/pgsql-jobs/2011-08/threads.php

== PostgreSQL Local ==

Postgres Open 2011, a conference focused on disruption of the database
industry through PostgreSQL, will take place September 14-16, 2011 in
Chicago, Illinois at the Westin Michigan Avenue hotel.
http://postgresopen.org

PG-Day Denver 2011 will be held on Friday, October 21st, 2011 at
the Auraria Campus near downtown Denver, Colorado.
http://pgday.consistentstate.com/

PostgreSQL Conference West (#PgWest) will be held September 27th-30th,
2011 at the San Jose Convention center in San Jose, California, USA.
http://www.postgresqlconference.org

PostgreSQL Conference Europe 2011 will be held on October 18-21 in
Amsterdam.
http://2011.pgconf.eu/

pgbr will be in Sao Paulo, Brazil November 3-4, 2011.
http://pgbr.postgresql.org.br/

PGConf.DE 2011 is the German-speaking PostgreSQL Conference and will
take place on November 11th in the Rheinisches Industriemuseum in
Oberhausen, Germany.  Call for Papers is open.
http://2011.pgconf.de/

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to david@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.  Spanish language
to pwn@arpug.com.ar.

== Reviews ==

== Applied Patches ==

Tom Lane pushed:

- Fix unsafe order of operations in foreign-table DDL commands.  When
  updating or deleting a system catalog tuple, it's necessary to
  acquire RowExclusiveLock on the catalog before looking up the tuple;
  otherwise a concurrent VACUUM FULL on the catalog might move the
  tuple to a different TID before we can apply the update.  Coding
  patterns that find the tuple via a table scan aren't at risk here,
  but when obtaining the tuple from a catalog cache, correct ordering
  is important; and several routines in foreigncmds.c got it wrong.
  Noted while running the regression tests in parallel with VACUUM
  FULL of assorted system catalogs.  For consistency I moved all the
  heap_open calls to the starts of their functions, including a couple
  for which there was no actual bug.  Back-patch to 8.4 where
  foreigncmds.c was added.
  http://git.postgresql.org/pg/commitdiff/52994e9e5686b10a92bc93ec0f4e15c7fbc18242

- Fix race condition in relcache init file invalidation.  The previous
  code tried to synchronize by unlinking the init file twice, but that
  doesn't actually work: it leaves a window wherein a third process
  could read the already-stale init file but miss the SI messages that
  would tell it the data is stale.  The result would be bizarre
  failures in catalog accesses, typically "could not read block 0 in
  file ..." later during startup.  Instead, hold RelCacheInitLock
  across both the unlink and the sending of the SI messages.  This is
  more straightforward, and might even be a bit faster since only one
  unlink call is needed.  This has been wrong since it was put in (in
  2002!), so back-patch to all supported releases.
  http://git.postgresql.org/pg/commitdiff/2ada6779c5d3fcc31568ba263f8a0cc9bb8318c1

- Preserve toast value OIDs in toast-swap-by-content for
  CLUSTER/VACUUM FULL.  This works around the problem that a catalog
  cache entry might contain a toast pointer that we try to dereference
  just as a VACUUM FULL completes on that catalog.  We will see the
  sinval message on the cache entry when we acquire lock on the toast
  table, but by that point we've already told tuptoaster.c "here's the
  pointer to fetch", so it's difficult from a code structural
  standpoint to update the pointer before we use it.  Much less
  painful to ensure that toast pointers are not invalidated in the
  first place.  We have to add a bit of code to deal with the case
  that a value that previously wasn't toasted becomes so; but that
  should be a seldom-exercised corner case, so the inefficiency
  shouldn't be significant.  Back-patch to 9.0.  In prior versions, we
  didn't allow CLUSTER on system catalogs, and VACUUM FULL didn't
  result in reassignment of toast OIDs, so there was no problem.
  http://git.postgresql.org/pg/commitdiff/7b0d0e9356963d5c3e4d329a917f5fbb82a2ef05

- Fix incorrect order of operations during sinval reset processing.
  We have to be sure that we have revalidated each nailed-in-cache
  relcache entry before we try to use it to load data for some other
  relcache entry.  The introduction of "mapped relations" in 9.0 broke
  this, because although we updated the state kept in relmapper.c
  early enough, we failed to propagate that information into relcache
  entries soon enough; in particular, we could try to fetch pg_class
  rows out of pg_class before we'd updated its relcache entry's
  rd_node.relNode value from the map.  This bug accounts for Dave
  Gould's report of failures after "vacuum full pg_class", and I
  believe that there is risk for other system catalogs as well.  The
  core part of the fix is to copy relmapper data into the relcache
  entries during "phase 1" in RelationCacheInvalidate(), before
  they'll be used in "phase 2".  To try to future-proof the code
  against other similar bugs, I also rearranged the order in which
  nailed relations are visited during phase 2: now it's pg_class
  first, then pg_class_oid_index, then other nailed relations.  This
  should ensure that RelationClearRelation can apply
  RelationReloadIndexInfo to all nailed indexes without risking use of
  not-yet-revalidated relcache entries.  Back-patch to 9.0 where the
  relation mapper was introduced.
  http://git.postgresql.org/pg/commitdiff/f4d7f1adbae831a37686d28cc5f89f0fcff48a54

- Forget about targeting catalog cache invalidations by tuple TID.
  The TID isn't stable enough: we might queue an sinval event before a
  VACUUM FULL, and then process it afterwards, when the target tuple
  no longer has the same TID.  So we must invalidate entries on the
  basis of hash value only.  The old coding can be shown to result in
  various bizarre, hard-to-reproduce errors in the presence of
  concurrent VACUUM FULLs on system catalogs, and could easily result
  in permanent catalog corruption, up to and including complete loss
  of tables.  This commit is just a minimal fix that removes the
  unsafe comparison.  We should remove transmission of the tuple TID
  from sinval messages altogether, and then arrange to suppress the
  extra message in the common case of a heap_update that doesn't
  change the key hashvalue.  But that's going to be much more
  invasive, and will only produce a probably-marginal performance
  gain, so it doesn't seem like material for a back-patch.  Back-patch
  to 9.0.  Before that, VACUUM FULL refused to do any tuple moving if
  it found any INSERT_IN_PROGRESS or DELETE_IN_PROGRESS tuples (and
  CLUSTER would give up altogether), so there was no risk of moving a
  tuple that might be the subject of an unsent sinval message.
  http://git.postgresql.org/pg/commitdiff/632ae6829f7abda34e15082c91d9dfb3fc0f298b

- Revise sinval code to remove no-longer-used tuple TID from inval
  messages.  This requires adjusting the API for syscache callback
  functions: they now get a hash value, not a TID, to identify the
  target tuple.  Most of them weren't paying any attention to that
  argument anyway, but plancache did require a small amount of fixing.
  Also, improve performance a trifle by avoiding sending duplicate
  inval messages when a heap_update isn't changing the catcache lookup
  columns.
  http://git.postgresql.org/pg/commitdiff/b5282aa893e565b7844f8237462cb843438cdd5e

- Fix two issues in plpython's handling of composite results.  Dropped
  columns within a composite type were not handled correctly.  Also,
  we did not check for whether a composite result type had changed
  since we cached the information about it.  Jan Urbański, per a bug
  report from Jean-Baptiste Quenot
  http://git.postgresql.org/pg/commitdiff/2dada0cc85ee36f4e2b32a0463cb75ad9466589a

- Update 9.1 release notes to reflect commits through today.  Also do
  another pass of copy-editing.
  http://git.postgresql.org/pg/commitdiff/1a998474a7d88e8b340b179130b8368b734a3c30

- Explain max_prepared_transactions requirement in isolation tests'
  README.  Now that we have a test that requires nondefault settings
  to pass, it seems like we'd better mention that detail in the
  directions about how to run the tests.  Also do some very minor
  copy-editing.
  http://git.postgresql.org/pg/commitdiff/11c88e59a60b2096ab42fa7cf9bff542717ec1e0

- Tag 9.1rc1.
  http://git.postgresql.org/pg/commitdiff/d89b8daf5ee05f9c6fa63695e88d2315a224bf2d

- Fix performance problem when building a lossy tidbitmap.  As pointed
  out by Sergey Koposov, repeated invocations of tbm_lossify can make
  building a large tidbitmap into an O(N^2) operation.  To fix, make
  sure we remove more than the minimum amount of information per call,
  and add a fallback path to behave sanely if we're unable to fit the
  bitmap within the requested amount of memory.  This has been wrong
  since the tidbitmap code was written, so back-patch to all supported
  branches.
  http://git.postgresql.org/pg/commitdiff/08e1eedf247e04a9652d997a74ceb46d889124ba

Peter Eisentraut pushed:

- Add "Reason code" prefix to internal SSI error messages.  This makes
  it clearer that the error message is perhaps not supposed to be
  understood by users, and it also makes it somewhat clearer that it
  was not accidentally omitted from translation.  Idea from Heikki
  Linnakangas, except that we don't mark "Reason code" for translation
  at this point, because that would make the implementation too
  cumbersome.
  http://git.postgresql.org/pg/commitdiff/e5475a80d2fd1b99a7939a81a44b746821287a06

- Adjust regression tests for error message change
  http://git.postgresql.org/pg/commitdiff/5845f42721c359dd1f6940995381be4000767117

- Use less cryptic variable names
  http://git.postgresql.org/pg/commitdiff/005e5c30d162447da81d5d5e118a5ea4613dc944

- Make pg_basebackup progress report translatable.  Also fix a
  potential portability bug, because INT64_FORMAT is only guaranteed
  to be available with snprintf, not fprintf.
  http://git.postgresql.org/pg/commitdiff/3b3f09351b48f3081021ce60964c92cec42b7c3d

- MacOS -> Mac OS.  Josh Kupershmidt
  http://git.postgresql.org/pg/commitdiff/1368409034f3d5280d7af6a0f59ab8bb96b07edc

- Move \r out of translatable strings.  The translation tools are very
  unhappy about seeing \r in translatable strings, so move it to a
  separate fprintf call.
  http://git.postgresql.org/pg/commitdiff/7f699804b12e480a07727472b9b8cf3886dbb897

- Translation updates
  http://git.postgresql.org/pg/commitdiff/1bf80041e316392b2b602ed5c90904035ca3ac10

- Improve detection of Python 3.2 installations.  Because of ABI
  tagging, the library version number might no longer be exactly the
  Python version number, so do extra lookups.  This affects
  installations without a shared library, such as ActiveState's
  installer.  Also update the way to detect the location of the
  'config' directory, which can also be versioned.  Ashesh Vashi
  http://git.postgresql.org/pg/commitdiff/f8c2029ef0bf83ceb2bb10ee28362305cfa9cdf2

- Change PyInit_plpy to external linkage.  Module initialization
  functions in Python 3 must have external linkage, because
  PyMODINIT_FUNC does dllexport on Windows-like platforms.  Without
  this change, the build with Python 3 fails on Windows.
  http://git.postgresql.org/pg/commitdiff/e1f9aa4eaecd8107230df1a1b698e8caf0fba4f8

- Hide unused variable warnings under Python 3
  http://git.postgresql.org/pg/commitdiff/f684bcb5238c13c8ba5a3770f4d0856677f99b35

Bruce Momjian pushed:

- In pg_upgrade, avoid dumping orphaned temporary tables.  This makes
  the pg_upgrade schema matching pattern match pg_dump/pg_dumpall.
  Fix for 9.0, 9.1, and 9.2.  Report and proposed bug fix by David
  Byrne.
  http://git.postgresql.org/pg/commitdiff/2411fbdac448045a23eebf4f0dbfd5790ebad720

- In pg_upgrade, don't copy visibility map files from clusters that
  did not have crash-safe visibility maps to clusters that expect
  crash-safety.  Request from Robert Haas.
  http://git.postgresql.org/pg/commitdiff/00a7c9014a8fbb7388a807daeba3e0a85b49a747

- Implement src/tools/copyright as a Perl program, so anyone can run
  it.  David Fetter
  http://git.postgresql.org/pg/commitdiff/739e3f10506c0639eaf3a552936fb82b9628c735

- Add executable bit to file.
  http://git.postgresql.org/pg/commitdiff/da64fb93e5ac3f9521490414369d39c98ac54067

- Remove use of 'tie' in perl for copyright.pl;  instead use normal
  file open/close.
  http://git.postgresql.org/pg/commitdiff/272c289a46d90d7d23947be9b6ffc5fb3ae8df83

- Fix problem with regex in copyright test.  Report and fix by Kris
  Jurka
  http://git.postgresql.org/pg/commitdiff/6dfcadafd3458221aff39b784b5456ac2002653a

- Fix copyright.pl to properly us 'tie' function.  Kris Jurka
  http://git.postgresql.org/pg/commitdiff/ee639d277787a75183d3763728f02da0d0a6ae52

- Have thread_test create its test files in the current directory,
  rather than /tmp.  Also cleanup C defines and add comments.  Per
  report by Alex Soto
  http://git.postgresql.org/pg/commitdiff/6cc08e703bb078ee1e8d183caf4596d62bf12bf7

Heikki Linnakangas pushed:

- Fix bogus comment that claimed that the new BACKUP METHOD line in
  backup_label was new in 9.0.  Spotted by Fujii Masao.
  http://git.postgresql.org/pg/commitdiff/2877c67bc24510c30bca477c876f5de427c85588

- If backup-end record is not seen, and we reach end of recovery from
  a streamed backup, throw an error and refuse to start up. The
  restore has not finished correctly in that case and the data
  directory is possibly corrupt.  We already errored out in case of
  archive recovery, but could not during crash recovery because we
  couldn't distinguish between the case that pg_start_backup() was
  called and the database then crashed (must not error, data is OK),
  and the case that we're restoring from a backup and not all the
  needed WAL was replayed (data can be corrupt).  To distinguish those
  cases, add a line to backup_label to indicate whether the backup was
  taken with pg_start/stop_backup(), or by streaming (ie.
  pg_basebackup).  This is a different implementation than what I
  committed to 9.2 a week ago.  That implementation was not
  back-patchable because it required re-initdb.  Fujii Masao
  http://git.postgresql.org/pg/commitdiff/59dd4790e24b2fdffe85b8d3664a8374303cca24

- Fix comment about which version had BACKUP METHOD line in
  backup_lable, again.  It was invalidated again by Fujii's patch to
  9.1.
  http://git.postgresql.org/pg/commitdiff/1d0392b2457e0cb845ea498849541f7eb94616d7

- Teach pg_controldata and pg_resetxlog about the new
  backupEndRequired field in control file.
  http://git.postgresql.org/pg/commitdiff/a1a847d31f37b8063d8478b56e07856d53132b05

- Strip whitespace from SQL blocks in the isolation test suite. This
  is purely cosmetic, it removes a lot of IMHO ugly whitespace from
  the expected output.
  http://git.postgresql.org/pg/commitdiff/62fd1afc55368a2d4a15b60cda80bb5b1c594abc

- Add an SSI regression test that tests all interesting permutations
  in the order of begin, prepare, and commit of three concurrent
  transactions that have conflicts between them.  The test runs for a
  quite long time, and the expected output file is huge, but this test
  caught some serious bugs during development, so seems worthwhile to
  keep. The test uses prepared transactions, so it fails if the server
  has max_prepared_transactions=0. Because of that, it's marked as
  "ignore" in the schedule file.  Dan Ports
  http://git.postgresql.org/pg/commitdiff/af35737313f92fcbe227baf51bb69b8650acc41a

Magnus Hagander pushed:

- Adjust total size in pg_basebackup progress report when reality
  changes.  When streaming including WAL, the size estimate will
  always be incorrect, since we don't know how much WAL is included.
  To make sure the output doesn't look completely unreasonable, this
  patch increases the total size whenever we go past the estimate, to
  make sure we never go above 100%.
  http://git.postgresql.org/pg/commitdiff/1bb69245ab234634013c543927e6fa76009622fa

- Adjust wording now that estimated size can increase.  Per comment
  form Fujii Masao.
  http://git.postgresql.org/pg/commitdiff/e5cb7563612b7056d9f97e65d4f4bef3930219dc

Andrew Dunstan pushed:

- Properly handle empty arrays returned from plperl functions.  Bug
  reported by David Wheeler, fix by Alex Hunsaker.
  http://git.postgresql.org/pg/commitdiff/68c903a66ce6f766a8c99d07034374109859624f

Robert Haas pushed:

- Remove obsolete README file.  Perhaps we ought to add some other
  kind of documentation here instead, but for now let's get rid of
  this woefully obsolete description of the sinval machinery.
  http://git.postgresql.org/pg/commitdiff/24bf1552f6fa9dbecbd264d1208a100bf9d68940

- Make lazy_vacuum_rel call pg_rusage_init only if needed.
  do_analyze_rel already does it this way.  Euler Taveira de Oliveira
  http://git.postgresql.org/pg/commitdiff/0f7acbeddfba502587065a5aa6da5b0d1fac4f38

- Typo fix.
  http://git.postgresql.org/pg/commitdiff/ffaf9eca4c27d0a63639ffb3022035a297df9900

- Allow sepgsql regression tests to be run from a user homedir.
  KaiGai Kohei, with some changes by me.
  http://git.postgresql.org/pg/commitdiff/a64bdf5f657f4dd58f1b2df7b69f890d84952597

- Fix contrib/sepgsql and contrib/xml2 to always link required
  libraries.  contrib/xml2 can get by without libxslt; the relevant
  features just won't work.  But if doesn't have libxml2, or if
  sepgsql doesn't have libselinux, the link succeeds but the module
  then fails to work at load time.  To avoid that, link the require
  libraries unconditionally, so that it will be clear at link-time
  that there is a problem.  Per discussion with Tom Lane and KaiGai
  Kohei.
  http://git.postgresql.org/pg/commitdiff/10c378f2350a8321ea76fc02d821a9e7707c7563

- Clean up 'chkselinuxenv' script.  Eliminate dependencies on "which",
  as we don't really need that to be installed for proper testing.
  Don't number the tests, as that increases the footprint of every
  patch that wants to add or remove tests.  Make the test output more
  informative, so that it's a bit easier to see what went right (or
  wrong).  Spelling and grammar improvements.
  http://git.postgresql.org/pg/commitdiff/a4b3feebc44dcb872fc7a765c4d48627c97421ed

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Joachim Wieland sent in another revision of the patch to provide
facilities for exporting and using snapshots.

Magnus Hagander sent in a patch intended to address some infelicities
in the representation of timestamptzs in replication.

KaiGai Kohei sent in three patches to unify DROP into a single
mechanism.

Heikki Linnakangas and Alexander Korotkov traded new revisions of the
patch to speed up GiST index builds.

Fujii Masao sent in two revisions of a patch to fix some issues in
cascading replication.

Jeevan Chalke sent in a patch to allow the same cursor names in nested
levels.

Magnus Hagander sent in another revision of the patch to implement
pg_receivexlog.

Josh Kupershmidt sent in a patch to fix up the pg_comments view.

Greg Smith sent in a patch that tracks and displays the accumulated
cost when autovacuum is running.  Code by Noah Misch and Greg Smith.

Josh Kupershmidt sent in a patch to fix some infelicities in
mb_regress.sh.

Shigeru HANADA sent in two more revisions of the patch which gives the
format of FDW options.

KaiGai Kohei sent in two more revisions of the patch to allow access
to the userspace access vector cache.

Wojciech Muła sent in a patch to fix some infelicities in PL/pgsql's
handling of %TYPE in arrays.