Features Download
From: David Fetter <david <at> fetter.org>
Subject: == PostgreSQL Weekly News - January 29 2012 ==
Newsgroups: gmane.comp.db.postgresql.announce
Date: Monday 30th January 2012 07:08:12 UTC (over 6 years ago)
== PostgreSQL Weekly News - January 29 2012 ==

There will be a PostgreSQL track at FOSDEM, February 4-5 in Brussels,
Belgium, along with speakers from the PostgreSQL community on the main

The PGCon CfP has been extended through January 31, 2012.

PGDay NYC will be held April 2, 2012 at Lighthouse International in
New York City.

PGDay France will be in Lyon on June 7, 2012.

== PostgreSQL Jobs for January ==


== PostgreSQL Local ==

The fifth annual "Prague PostgreSQL Developers Day" conference,
organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be
held on February 9, 2012 in Prague.

PGCon 2012 will be held 17-18 May 2012, in Ottawa at the University of
Ottawa.  It will be preceded by two days of tutorials on 15-16 May 2012.

== 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 [email protected], German language
to [email protected], Italian language to [email protected]  Spanish language
to [email protected]

== Reviews ==

== Applied Patches ==

Simon Riggs pushed:

- ALTER  [IF EXISTS] ... allows silent DDL if required, e.g.

- Resolve timing issue with logging locks for Hot Standby.  We log
  AccessExclusiveLocks for replay onto standby nodes, but because of
  timing issues on ProcArray it is possible to log a lock that is
  still held by a just committed transaction that is very soon to be
  removed. To avoid any timing issue we avoid applying locks made by
  transactions with InvalidXid.  Simon Riggs, bug report Tom Lane,
  diagnosis Pavan Deolasee

- Add new replication mode synchronous_commit = 'write'.  Replication
  occurs only to memory on standby, not to disk, so provides
  additional performance if user wishes to reduce durability level
  slightly. Adds concept of multiple independent sync rep queues.
  Fujii Masao and Simon Riggs

- Allow pg_basebackup from standby node with safety checking.  Base
  backup follows recommended procedure, plus goes to great lengths to
  ensure that partial page writes are avoided.  Jun Ishizuka and Fujii
  Masao, with minor modifications

Robert Haas pushed:

- Adjustments to regression tests for security_barrier views.  Drop
  the role we create, so regression tests pass even when run more than
  once against the same cluster, a problem noted by Tom Lane and Jeff
  Janes.  Also, rename the temporary role so that it starts with
  "regress_", to make it unlikely that we'll collide with an existing
  role name while running "make installcheck", per further gripe from
  Tom Lane.

- Make CheckIndexCompatible simpler and more bullet-proof.  This gives
  up the "don't rewrite the index" behavior in a couple of relatively
  unimportant cases, such as changing between an array type and an
  unconstrained domain over that array type, in return for making this
  code more future-proof.  Noah Misch

- Instrument index-only scans to count heap fetches performed.  Patch
  by me; review by Tom Lane, Jeff Davis, and Peter Geoghegan.

- Damage control for yesterday's CheckIndexCompatible changes.  Rip
  out a regression test that doesn't play well with settings put in
  place by the build farm, and rewrite the code in
  CheckIndexCompatible in a hopefully more transparent style.

- Classify DROP operations by whether or not they are user-initiated.
  This doesn't do anything useful just yet, but is intended as
  supporting infrastructure for allowing sepgsql to sensibly check
  DROP permissions.  KaiGai Kohei and Robert Haas

- Add missing #include, to suppress compiler warning.

- Be more clear when a new column name collides with a system column
  name.  We now use the same error message for ALTER TABLE .. ADD
  The old message was accurate, but might be confusing to users not
  aware of our system columns.  Vik Reykja, with some changes by me,
  and further proofreading by Tom Lane

- Adjust tuplesort.c based on the fact that we never use the OS's
  qsort().  Our own qsort_arg() implementation doesn't have the defect
  previously observed to affect only QNX 4, so it seems sufficiently
  to assert that it isn't broken rather than retesting.  Also, update
  a few comments to clarify why it's valuable to retain a tie-break
  rule based on CTID during index builds.  Peter Geoghegan, with
  slight tweaks by me.

- pg_dump: Move connection-setup code to a separate function.
  Parallel dump will need to repeat these steps for each new
  connection, so it's better to have this logic in its own function.
  Extracted (with some changes) from a much larger patch by Joachim

- Document that analyzing an empty table doesn't update the
  statistics.  Per a suggestion from Sergey Konoplev

- Document that COUNT(*) might not need a seq scan any more.  Noted by
  Josh Kupershmidt.

Bruce Momjian pushed:

- Reduce tab outdent of "error handling" GUC comments in
  postgresql.conf, to match surrounding outdenting.

- Fix new SQL tablespace location function usage in pg_upgrade to
  properly check cluster version numbers, and fix missing table alias.

- In pg_upgrade, when checking for the plpython library, we must check
  for "plpython2" when upgrading from pre-PG 9.1.  Patch to head and
  9.1.  Per report from Peter Eisentraut.

- Improve plpython fix comment in pg_upgrade.

- Now that the shared library name can be adjusted in the library
  test, have pg_upgrade allocate a maximum fixed size buffer for
  testing the library file name, rather than base the allocation on
  the library name.  Backpatch to 9.1.

- Fix pg_upgrade --help text for --user to not use a plural cluster name.

Tom Lane pushed:

- Suppress possibly-uninitialized-variable warning seen with older gcc

- Suppress variable-clobbered-by-longjmp warning seen with older gcc

- Use parameterized paths to generate inner indexscans more flexibly.
  This patch fixes the planner so that it can generate nestloop-with-
  inner-indexscan plans even with one or more levels of joining
  between the indexscan and the nestloop join that is supplying the
  parameter.  The executor was fixed to handle such cases some time
  ago, but the planner was not ready.  This should improve our plans
  in many situations where join ordering restrictions formerly forced
  complete table scans.  There is probably a fair amount of tuning
  work yet to be done, because of various heuristics that have been
  added to limit the number of parameterized paths considered.
  However, we are not going to find out what needs to be adjusted
  until the code gets some real-world use, so it's time to get it in
  there where it can be tested easily.  Note API change for index AM
  amcostestimate functions.  I'm not aware of any non-core index AMs,
  but if there are any, they will need minor adjustments.

- Undo 8.4-era lobotomization of subquery pullup rules.  After the
  planner was fixed to convert some IN/EXISTS subqueries into
  semijoins or antijoins, we had to prevent it from doing that in some
  cases where the plans risked getting much worse.  The reason the
  plans got worse was that in the unoptimized implementation,
  subqueries could reference parameters from the outer query at any
  join level, and so full table scans could be avoided even if they
  were one or more levels of join below where the semi/anti join would
  be.  Now that we have sufficient mechanism in the planner to handle
  such cases properly, it should no longer be necessary to play dumb
  here.  This reverts commits 07b9936a0f10d746e5076239813a5e938f2f16be
  and cd1f0d04bf06938c0ee5728fc8424d62bcf2eef3.  The latter was a
  stopgap fix that wasn't really sufficiently analyzed at the time.
  Rather than just restricting ourselves to cases where the new join
  can be stacked on the right-hand input, we should also consider
  whether it can be stacked on the left-hand input.

- Fix error detection in contrib/pgcrypto's encrypt_iv() and
  decrypt_iv().  Due to oversights, the encrypt_iv() and decrypt_iv()
  functions failed to report certain types of invalid-input errors,
  and would instead return random garbage values.  Marko Kreen, per
  report from Stefan Kaltenbrunner

- Fix handling of data-modifying CTE subplans in EvalPlanQual.  We
  can't just skip initializing such subplans, because the referencing
  CTE node will expect to find the subplan available when it
  initializes.  That in turn means that ExecInitModifyTable must allow
  the case (which actually it needed to do anyway, since there's no
  guarantee that ModifyTable is exactly at the top of the CTE plan
  tree).  So move the complaint about not being allowed in
  EvalPlanQual mode to execution instead of initialization.  Testing
  turned up yet another problem, which is that we'd try to
  re-initialize the result relation's index list, leading to leaks and
  dangling pointers.  Per report from Phil Sorber.  Back-patch to 9.1
  where data-modifying CTEs were introduced.

- Add simple tests of EvalPlanQual using the isolationtester
  infrastructure.  Much more could be done here, but at least now we
  have *some* automated test coverage of that mechanism.  In
  particular this tests the writable-CTE case reported by Phil Sorber.
  In passing, remove isolationtester's arbitrary restriction on the
  number of steps in a permutation list.  I used this so that a single
  spec file could be used to run several related test scenarios, but
  there are other possible reasons to want a step series that's not
  exactly a permutation.  Improve documentation and fix a couple other
  nits as well.

- Fix handling of init_plans list in inheritance_planner().  Formerly
  we passed an empty list to each per-child-table invocation of
  grouping_planner, and then merged the results into the global list.
  However, that fails if there's a CTE attached to the statement,
  because create_ctescan_plan uses the list to find the plan
  referenced by a CTE reference; so it was unable to find any CTEs
  attached to the outer UPDATE or DELETE.  But there's no real reason
  not to use the same list throughout the process, and doing so is
  simpler and faster anyway.  Per report from Josh Berkus of "could
  not find plan for CTE" failures.  Back-patch to 9.1 where we added
  support for WITH attached to UPDATE or DELETE.  Add some regression
  test cases, too.

- Update statement about sorting of character-string data.  The sort
  order is no longer fixed at database creation time, but can be
  controlled via COLLATE.  Noted by Thomas Kellerer.

- Add caution about multiple unique indexes breaking plpgsql upsert
  example.  Per Phil Sorber, though I didn't use his wording exactly.

- Fix pushing of index-expression qualifications through UNION ALL.
  In commit 57664ed25e5dea117158a2e663c29e60b3546e1c, I made the
  planner wrap non-simple-variable outputs of appendrel children (IOW,
  child SELECTs of UNION ALL subqueries) inside PlaceHolderVars, in
  order to solve some issues with EquivalenceClass processing.
  However, this means that any upper-level WHERE clauses mentioning
  such outputs will now contain PlaceHolderVars after they're pushed
  down into the appendrel child, and that prevents indxpath.c from
  recognizing that they could be matched to index expressions.  To
  fix, add explicit stripping of PlaceHolderVars from index operands,
  same as we have long done for RelabelType nodes.  Add a regression
  test covering both this and the plain-UNION case (which is a totally
  different code path, but should also be able to do it).  Per bug
  #6416 from Matteo Beccati.  Back-patch to 9.1, same as the previous

- Tweak index costing for problems with partial indexes.
  btcostestimate() makes an estimate of the number of index tuples
  that will be visited based on knowledge of which index clauses can
  actually bound the scan within nbtree.  However, it forgot to
  account for partial indexes in this calculation, with the result
  that the cost of the index scan could be significantly overestimated
  for a partial index.  Fix that by merging the predicate with the
  abbreviated indexclause list, in the same way as we do with the full
  list to estimate how many heap tuples will be visited.  Also,
  slightly increase the "fudge factor" that's meant to give preference
  to smaller indexes over larger ones.  While this is applied to all
  indexes, it's most important for partial indexes since it can be the
  only factor that makes a partial index look cheaper than a similar
  full index.  Experimentation shows that the existing value is so
  small as to easily get swamped by noise such as
  page-boundary-roundoff behavior.  I'm tempted to kick it up more
  than this, but will refrain for now.  Per report from Ruben Blanco.
  These are long-standing issues, but given the lack of prior
  complaints I'm not going to risk changing planner behavior in back
  branches by back-patching.

- Fix typo in comment.  Peter Geoghegan

- Assorted comment fixes, mostly just typos, but some obsolete
  statements.  YAMAMOTO Takashi

Peter Eisentraut pushed:

- Remove quotes around format_type_be() output.  format_type_be()
  takes care of any needed quoting itself.

- Do not access indclass through Form_pg_index.  Normally, accessing
  variable-length members of catalog structures past the first one
  doesn't work at all.  Here, it happened to work because indnatts was
  checked to be 1, and so the defined FormData_pg_index layout, using
  int2vector[1] and oidvector[1] for variable-length arrays, happened
  to match the actual memory layout.  But it's a very fragile
  assumption, and it's not in a performance-critical path, so code it
  properly using heap_getattr() instead.  bug analysis by Tom Lane

- Hide most variable-length fields from Form_pg_* structs.  Those
  fields only appear in the structs so that genbki.pl can create the
  BKI bootstrap files for the catalogs.  But they are not actually
  usable from C.  So hiding them can prevent coding mistakes, saves
  stack space, and can help the compiler.  In certain catalogs, the
  first variable-length field has been kept visible after manual
  inspection.  These exceptions are noted in C comments.  reviewed by
  Tom Lane

- Disallow ALTER DOMAIN on non-domain type everywhere.  This has been
  the behavior already in most cases, but through omission, ALTER
  DOMAIN / OWNER TO and ALTER DOMAIN / SET SCHEMA would silently work
  on non-domain types as well.

- Revert unfortunate whitespace change.  In
  e5e2fc842c418432756d8b5825ff107c6c5fc4c3, blank lines were removed
  after a comment block, which now looks as though the comment refers
  to the immediately following code, but it actually refers to the
  preceding code.  So put the blank lines back.

- Show default privileges in information schema.  Hitherto, the
  information schema only showed explicitly granted privileges that
  were visible in the *acl catalog columns.  If no privileges had been
  granted, the implicit privileges were not shown.  To fix that, add
  an SQL-accessible version of the acldefault() function, and use that
  inside the aclexplode() calls to substitute the catalog-specific
  default privilege set for null values.  reviewed by Abhijit

Alvaro Herrera pushed:

- Add pg_trigger_depth() function.  This reports the depth level of
  triggers currently in execution, or zero if not called from inside a
  trigger.  No catversion bump in this patch, but you have to initdb
  if you want access to the new function.  Author: Kevin Grittner

- Have \copy go through SendQuery.  This enables a bunch of features,
  notably ON_ERROR_ROLLBACK.  It also makes COPY failure (either in
  the server or psql) as a whole behave more sanely in psql.
  Additionally, having more commands in the same command line as COPY
  works better (though since psql splits lines at semicolons, this
  doesn't matter much unless you're using -c).  Also tighten a couple
  of switches on PQresultStatus() to add PGRES_COPY_BOTH support and
  stop assuming that unknown statuses received are errors; have those
  print diagnostics where warranted.  Author: Noah Misch

Magnus Hagander pushed:

- Track temporary file count and size in pg_stat_database.  Add
  counters for number and size of temporary files used for
  spill-to-disk queries for each database to the pg_stat_database
  view.  Tomas Vondra, review by Magnus Hagander

- Add deadlock counter to pg_stat_database.  Adds a counter that
  tracks number of deadlocks that occurred in each database to
  pg_stat_database.  Magnus Hagander, reviewed by Jaime Casanova

- Fix a copy/pasted typo in several comments

- Fix wording, per Peter Geoghegan

- Prevent logging "failed to stat file: success" for temp files.  This
  was broken in commit bc3347484a7bf9eddb98e4352d84599cae9a31c6, the
  addition of statistics counters for temp files.  Reported by Thom

Heikki Linnakangas pushed:

- Make bgwriter sleep longer when it has no work to do, to save
  electricity.  To make it wake up promptly when activity starts
  again, backends nudge it by setting a latch in MarkBufferDirty().
  The latch is kept set while bgwriter is active, so there is very
  little overhead from that when the system is busy. It is only armed
  before going into longer sleep.  Peter Geoghegan, with some changes
  by me.

- Fix sentence in docs: checkpoints are not done by bgwriter anymore.

- Mention in the docs that bgwriter now sleeps longer when it has no
  work to do.

- Fix copy-paste error in docs.  Dean Rasheed

- Initialize the new bgwriterLatch field properly.  Peter Geoghegan

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Noah Misch sent in another revision of the patch to collect statistics
for arrays.

Jim Mlodgensky and Heikki Linnakangas traded patches to add a new GUC
for welcome messages.

Matthew Draper sent in another revision of the patch to enable SQL
language functions to reference parameters by name.

Simon Riggs sent in another revision of the patch to have a WAL
restore process during recovery.

Simon Riggs sent in another revision of the freelist_wait_stats patch.

Alexander Korotkov and Jeff Davis traded patches for GiST indexing on
range types.

Alvaro Herrera sent in two more revisions of the FOREIGN KEY LOCK

Jaime Casanova sent in another revision of the relation_free_space

Thomas Ogrisegg sent in a patch intended to fix a bug in
pg_basebackup, which exits with 0 even if it had errors while writing
the backup to disk when the backup file is to be sent to stdout.

Fujii Masao sent an add-on patch to the one which adds a "write"
synchrounous replication mode to modify postgresql.conf appropriately.

Noah Misch sent in another revision of the patch to prevent unneeded
table rewrites in certain types of ALTER TABLE...ALTER TYPE

Heikki Linnakangas sent in two more revisions of the GROUP COMMIT
patch, reviews by Robert Haas, Jeff Janes and Jesper Krogh.

Dimitri Fontaine sent in another revision of the patch to add triggers
to commands in general.

Abhijit Menon-Sen and Pavel Stehule traded revisions of a patch to
allow better debugging of overloaded functions.

Abhijit Menon-Sen sent in a patch atop Andrew Dunstan's patch to add
{query,array,row}-to-json functionality.

KaiGai Kohei sent in another revision of the patch to plug certain
types of information leaks in views.

Abhijit Menon-Sen sent in a patch to fix some infelicities in Peter
Eisentraut's patch to enable using \0 as a field or record separator.

Jaime Casanova sent in another revision of the patch to add a
pg_stats_recovery view.

Abhijit Menon-Sen sent in a fix to Daniel Farina's patch which factors
out the various crc32 implementations in the code base.

Kyotaro HORIGUCHI sent in another revision of the patch to add a new
libpq tuple storage and use same to speed up dblink.  Reviews by Marko
Kreen and Merlin Moncure.

Peter Geoghegan sent in another revision of the fast path sort patch.

Marti Raudsepp sent in another revision of the patch to cache stable
expressions with constant arguments.

Luben Karavelov sent in a patch to enable limited cover density
ranking in text search.  Reviews by Oleg Bartunov and Sushant Sinha.

Dan Scales sent in another WIP patch implementing double-write with
the suggested double-write buffers per feedback from Heikki
Linnakangas and Simon Riggs.

Jeff Janes sent in a patch to allow simulating log contention in

Dean Rasheed sent in a patch to fix a performance regression in
index-only scans.

Petr (PJMODOS) Jelinek sent in another revision of the patch to enable

Simon Riggs sent in another revision of the DROP INDEX CONCURRENTLY

Simon Riggs sent in another revision of the patch intended to reduce
CLOG contention.

---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:

               [email protected]
CD: 4ms