== PostgreSQL Weekly News - June 05 2011 ==

== PostgreSQL Weekly News - June 05 2011 ==

The Austin Texas PUG is meeting on Wednesday, June 8th at 6:30pm.
Pizza will be available to people who R, SVP, to
austinpug AT postgresql DOT org.  Details below.
http://pugs.postgresql.org/austinpug

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

devart dotConnect for PostgreSQL, an ADO.NET provider, released.
http://www.devart.com/dotconnect/postgresql/

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

PostgreDAC 2.6.3, a Delphi/C++ builder for PostgreSQL, released.
http://microolap.com/products/connectivity/postgresdac/download/

Slony-ctl 1.2.0, a set of scripts for creating and managing Slony
clusters, released.
http://pgfoundry.org/projects/slony1-ctl/

== PostgreSQL Jobs for June ==

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

== PostgreSQL Local ==

PG Session 2, on PostGIS, will be held on June 23rd in Paris.  The CfP
is open!
http://www.postgresql-sessions.org/en/2/

CHAR(11), the PostgreSQL Conference on Clustering, High Availability
and Replication is now open for online registration and bookings.
July 11-12 2011 in Cambridge, UK.
http://www.char11.org/

PostgreSQL Conference China 2011 will be held in Guangzhou July
15-16, 2011.
http://wiki.postgresql.org/wiki/Pgconchina2011

PDXPUG is hosting PgDay on Sunday, July 24, 2011, one day before
OSCON, in Portland, Oregon, USA.  Details here:
http://pugs.postgresql.org/node/1663

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

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/

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

Alvaro Herrera pushed:

- Make message more consistent
  http://git.postgresql.org/pg/commitdiff/5177dfefc532ea481bf70d1bb8a493f835a9c57c

- Remove usage of &PL_sv_undef in hashes and arrays.  According to
  perlguts, &PL_sv_undef is not the right thing to use in those cases
  because it doesn't behave the same way as an undef value via Perl
  code.  Seems the intuitive way to deal with undef values is subtly
  enough broken that it's hard to notice when misused.  The broken
  uses got inadvertently introduced in commit
  87bb2ade2ce646083f39d5ab3e3307490211ad04 by Alexey Klyukin, Alex
  Hunsaker and myself on 2011-02-17; no backpatch is necessary.  Per
  testing report from Greg Mullane.  Author: Alex Hunsaker
  http://git.postgresql.org/pg/commitdiff/7de38741c0438cdece0e22699de8ffd5797735fb

- Fix pg_get_constraintdef to cope with NOT VALID constraints.  This
  case was missed when NOT VALID constraints were first introduced in
  commit 722bf7017bbe796decc79c1fde03e7a83dae9ada by Simon Riggs on
  2011-02-08.  Among other things, it causes pg_dump to omit the NOT
  VALID flag when dumping such constraints, which may cause them to
  fail to load afterwards, if they contained values failing the
  constraint.  Per report from Thom Brown.
  http://git.postgresql.org/pg/commitdiff/048417511aef8d5fb2d541b17b73afc730935cd5

Heikki Linnakangas pushed:

- The row-version chaining in Serializable Snapshot Isolation was
  still wrong.  On further analysis, it turns out that it is not
  needed to duplicate predicate locks to the new row version at
  update, the lock on the version that the transaction saw as visible
  is enough.  However, there was a different bug in the code that
  checks for dangerous structures when a new rw-conflict happens.  Fix
  that bug, and remove all the row-version chaining related code.
  Kevin Grittner & Dan Ports, with some comment editorialization by
  me.
  http://git.postgresql.org/pg/commitdiff/3103f9a77d005f9d8b8ef492298bbbbf6c4b843f

- SSI comment fixes and enhancements.  Notably, document that the
  conflict-out flag actually means that the transaction has a conflict
  out to a transaction that committed before the flagged transaction.
  Kevin Grittner
  http://git.postgresql.org/pg/commitdiff/c8630919e08c2e91435807caecb4b44c7722bf9e

Magnus Hagander pushed:

- Don't include local line on platforms without support.  Since we now
  include a sample line for replication on local connections in
  pg_hba.conf, don't include it where local connections aren't
  available (such as on win32).  Also make sure we use authmethodlocal
  and not authmethod on the sample line.
  http://git.postgresql.org/pg/commitdiff/764bde0f1641cc3aafa2c221c56bd3a8129d9e3b

- Refuse "local" lines in pg_hba.conf on platforms that don't support
  it.  This makes the behavior compatible with that of hostssl, which
  also throws an error when there is no SSL support included.
  http://git.postgresql.org/pg/commitdiff/5830f69665053f78cfd31e39f40bb23ad91748a8

- Don't recommend upgrading to latest available Windows SDK.  We only
  support up to version 7.0, so don't recommend upgrading past it.
  The rest of the documentation around this was already updated, but
  one spot was missed.
  http://git.postgresql.org/pg/commitdiff/2367da886d4ab903c7bf5037b363ca10489cdf85

Peter Eisentraut pushed:

- Suppress foreign data wrappers and foreign servers in partial dumps.
  This is consistent with the behavior of other global objects such as
  languages and extensions.  Omitting foreign servers also omits the
  respective user mappings.
  http://git.postgresql.org/pg/commitdiff/3001b76308e9189ff471c54b1823621e03dc1359

- Recode non-ASCII characters in source to UTF-8.  For consistency,
  have all non-ASCII characters from contributors' names in the source
  be in UTF-8.  But remove some other more gratuitous uses of
  non-ASCII characters.
  http://git.postgresql.org/pg/commitdiff/ba4cacf0756f71e175d25bac78834715a353e64e

- Use entities to encode non-ASCII characters in SGML documentation.
  This has already been the case for the most part; just some cases
  had slipped through.
  http://git.postgresql.org/pg/commitdiff/85ffed431ae6fff0d5fbafd9a48b330542f2f4d9

- Some copy editing of the release notes
  http://git.postgresql.org/pg/commitdiff/596b0c213f2f7ffa72d0d5b68e1da91c366dc72b

- Sort COMMENT synopsis and add more examples.  Josh Kupershmidt
  http://git.postgresql.org/pg/commitdiff/c82d415acc39bc30962e692229c04ee23dae27b7

- Truncate id to <=44 characters.  This is the original DocBook SGML
  limit, but apparently most installations have changed it or ignore
  it, which is why few people have run into this problem.  pointed out
  by Brendan Jurd
  http://git.postgresql.org/pg/commitdiff/3ece3913d056245d78450793fc5ad18e229a3948

Tom Lane pushed:

- Fix VACUUM so that it always updates pg_class.reltuples/relpages.
  When we added the ability for vacuum to skip heap pages by
  consulting the visibility map, we made it just not update the
  reltuples/relpages statistics if it skipped any pages.  But this
  could leave us with extremely out-of-date stats for a table that
  contains any unchanging areas, especially for TOAST tables which
  never get processed by ANALYZE.  In particular this could result in
  autovacuum making poor decisions about when to process the table, as
  in recent report from Florian Helmberger.  And in general it's a bad
  idea to not update the stats at all.  Instead, use the previous
  values of reltuples/relpages as an estimate of the tuple density in
  unvisited pages.  This approach results in a "moving average"
  estimate of reltuples, which should converge to the correct value
  over multiple VACUUM and ANALYZE cycles even when individual
  measurements aren't very good.  This new method for updating
  reltuples is used by both VACUUM and ANALYZE, with the result that
  we no longer need the grotty interconnections that caused ANALYZE to
  not update the stats depending on what had happened in the parent
  VACUUM command.  Also, fix the logic for skipping all-visible pages
  during VACUUM so that it looks ahead rather than behind to decide
  what to do, as per a suggestion from Greg Stark.  This eliminates
  useless scanning of all-visible pages at the start of the relation
  or just after a not-all-visible page.  In particular, the first few
  pages of the relation will not be invariably included in the scanned
  pages, which seems to help in not overweighting them in the
  reltuples estimate.  Back-patch to 8.4, where the visibility map was
  introduced.
  http://git.postgresql.org/pg/commitdiff/b4b6923e03f4d29636a94f6f4cc2f5cf6298b8c8

- Fix portability bugs in use of credentials control messages for peer
  auth.  Even though our existing code for handling credentials
  control messages has been basically unchanged since 2001, it was
  fundamentally wrong: it did not ensure proper alignment of the
  supplied buffer, and it was calculating buffer sizes and message
  sizes incorrectly.  This led to failures on platforms where
  alignment padding is relevant, for instance FreeBSD on 64-bit
  platforms, as seen in a recent Debian bug report passed on by Martin
  Pitt (http://bugs.debian.org//cgi-bin/bugreport.cgi?bug=612888).
  Rewrite to do the message-whacking using the macros specified in RFC
  2292, following a suggestion from Theo de Raadt in that thread.
  Tested by me on Debian/kFreeBSD-amd64; since OpenBSD and NetBSD
  document the identical CMSG API, it should work there too.
  Back-patch to all supported branches.
  http://git.postgresql.org/pg/commitdiff/13c00ae8c73ee9635c11059925814b351dc3593c

- Replace use of credential control messages with
  getsockopt(LOCAL_PEERCRED).  It turns out the reason we hadn't found
  out about the portability issues with our credential-control-message
  code is that almost no modern platforms use that code at all; the
  ones that used to need it now offer getpeereid(), which we choose
  first.  The last holdout was NetBSD, and they added getpeereid() as
  of 5.0.  So far as I can tell, the only live platform on which that
  code was being exercised was Debian/kFreeBSD, ie, FreeBSD kernel
  with Linux userland --- since glibc doesn't provide getpeereid(), we
  fell back to the control message code.  However, the FreeBSD kernel
  provides a LOCAL_PEERCRED socket parameter that's functionally
  equivalent to Linux's SO_PEERCRED.  That is both much simpler to use
  than control messages, and superior because it doesn't require
  receiving a message from the other end at just the right time.
  Therefore, add code to use LOCAL_PEERCRED when necessary, and rip
  out all the credential-control-message code in the backend.  (libpq
  still has such code so that it can still talk to pre-9.1 servers ...
  but eventually we can get rid of it there too.)  Clean up related
  autoconf probes, too.  This means that libpq's requirepeer parameter
  now works on exactly the same platforms where the backend supports
  peer authentication, so adjust the documentation accordingly.
  http://git.postgresql.org/pg/commitdiff/be4585b1c27ac5dbdd0d61740d18f7ad9a00e268

- Protect GIST logic that assumes penalty values can't be negative.
  Apparently sane-looking penalty code might return small negative
  values, for example because of roundoff error.  This will confuse
  places like gistchoose().  Prevent problems by clamping negative
  penalty values to zero.  (Just to be really sure, I also made it
  force NaNs to zero.) Back-patch to all supported branches.
  Alexander Korotkov
  http://git.postgresql.org/pg/commitdiff/6923d699bc3c46ca2c5d8c12fe1c5c39ecfee11d

- Further improvements in pg_ctl's new wait-for-postmaster-start
  logic.  Add a postmaster_is_alive() test to the wait loop, so that
  we stop waiting if the postmaster dies without removing its pidfile.
  Unfortunately this only helps after the postmaster has created its
  pidfile, since until then we don't know which PID to check.  But if
  it never does create the pidfile, we can give up in a relatively
  short time, so this is a useful addition in practice.  Per
  suggestion from Fujii Masao, though this doesn't look very much like
  his patch.  In addition, improve pg_ctl's ability to cope with
  pre-existing pidfiles.  Such a file might or might not represent a
  live postmaster that is going to block our postmaster from starting,
  but the previous code pre-judged the situation and gave up waiting
  immediately.  Now, we will wait for up to 5 seconds to see if our
  postmaster overwrites such a file.  This issue interacts with
  Fujii's patch because we would make the wrong conclusion if we did
  the postmaster_is_alive() test with a pre-existing PID.  All of this
  could be improved if we rewrote start_postmaster() so that it could
  report the child postmaster's PID, so that we'd know a-priori the
  correct PID to test with postmaster_is_alive().  That looks like a
  bit too much change for so late in the 9.1 development cycle,
  unfortunately.
  http://git.postgresql.org/pg/commitdiff/3c485ca8e6580409284ac50623286b0fb8cd4a57

- Allow hash joins to be interrupted while searching hash table for
  match.  Per experimentation with a recent example, in which
  unreasonable amounts of time could elapse before the backend would
  respond to a query-cancel.  This might be something to back-patch,
  but the patch doesn't apply cleanly because this code was rewritten
  for 9.1.  Given the lack of field complaints I won't bother for now.
  Cédric Villemain
  http://git.postgresql.org/pg/commitdiff/0c99d41ec887051fb0cc6e35e358ecc936a13584

- Implement getpeereid() as a src/port compatibility function.  This
  unifies a bunch of ugly #ifdef's in one place.  Per discussion, we
  only need this where HAVE_UNIX_SOCKETS, so no need to cover Windows.
  Marko Kreen, some adjustment by Tom Lane
  http://git.postgresql.org/pg/commitdiff/3980f7fc6ecb75952ebe76c3d30ec6731728098d

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

- Disallow SELECT FOR UPDATE/SHARE on sequences.  We can't allow this
  because such an operation stores its transaction XID into the
  sequence tuple's xmax.  Because VACUUM doesn't process sequences
  (and we don't want it to start doing so), such an xmax value won't
  get frozen, meaning it will eventually refer to nonexistent pg_clog
  storage, and even wrap around completely.  Since the row lock is
  ignored by nextval and setval, the usefulness of the operation is
  highly debatable anyway.  Per reports of trouble with pgpool 3.0,
  which had ill-advisedly started using such commands as a form of
  locking.  In HEAD, also disallow SELECT FOR UPDATE/SHARE on toast
  tables.  Although this does work safely given the current
  implementation, there seems no good reason to allow it.  I refrained
  from changing that behavior in back branches, however.
  http://git.postgresql.org/pg/commitdiff/21538377ee6a0ee91f756726bd8b3de6d19fd20a

- Clean up after erroneous SELECT FOR UPDATE/SHARE on a sequence.  My
  previous commit disallowed this operation, but did nothing about
  cleaning up the damage if one had already been done.  With the
  operation disallowed, it's okay to just forcibly clear xmax in a
  sequence's tuple, since any value seen there could not represent a
  live transaction's lock.  So, any sequence-specific operation will
  repair the problem automatically, whether or not the user has
  already seen "could not access status of transaction" failures.
  http://git.postgresql.org/pg/commitdiff/ea6eda64a6425923463d358e2915980e81280483

- libpq needs its own copy of src/port/getpeereid ... on some
  platforms, anyway.  Per buildfarm.
  http://git.postgresql.org/pg/commitdiff/2021c5a53a9dd7bb181b67475c960ae730f3a4fc

- Looks like we can't declare getpeereid on Windows anyway ... for
  lack of the uid_t and gid_t typedefs.  Per buildfarm.
  http://git.postgresql.org/pg/commitdiff/680ea6a6df345218f655eaad2c25f98900487438

- Handle domains when checking for recursive inclusion of composite
  types.  We need this now because we allow domains over arrays, and
  we'll probably allow domains over composites pretty soon, which
  makes the problem even more obvious.  Although domains over arrays
  also exist in previous versions, this does not need to be
  back-patched, because the coding used in older versions successfully
  "looked through" domains over arrays.  The problem is exposed by not
  treating a domain as having a typelem.  Problem identified by Noah
  Misch, though I did not use his patch, since it would require
  additional work to handle domains over composites that way.  This
  approach is more future-proof.
  http://git.postgresql.org/pg/commitdiff/aff97b1f4e3630069a370be663b847c777b58319

- Need to list getpeereid.c in .gitignore, too ...
  http://git.postgresql.org/pg/commitdiff/52caa355ee6fd34670b6387e14c821d9128e5c88

- Fix failure to check whether a rowtype's component types are
  sortable.  The existence of a btree opclass accepting composite
  types caused us to assume that every composite type is sortable.
  This isn't true of course; we need to check if the column types are
  all sortable.  There was logic for this for the case of array
  comparison (ie, check that the element type is sortable), but we
  missed the point for rowtypes.  Per Teodor's report of an ANALYZE
  failure for an unsortable composite type.  Rather than just add some
  more ad-hoc logic for this, I moved knowledge of the issue into
  typcache.c.  The typcache will now only report out array_eq,
  record_cmp, and friends as usable operators if the array or
  composite type will work with those functions.  Unfortunately we
  don't have enough info to do this for anonymous RECORD types; in
  that case, just assume it will work, and take the runtime failure as
  before if it doesn't.  This patch might be a candidate for
  back-patching at some point, but given the lack of complaints from
  the field, I'd rather just test it in HEAD for now.  Note: most of
  the places touched in this patch will need further work when we get
  around to supporting hashing of record types.
  http://git.postgresql.org/pg/commitdiff/ea8e42f3a0848f506d8a1b9c74967248005291cd

- Reset reindex-in-progress state before reverifying an exclusion
  constraint.  This avoids an Assert failure when we try to use
  ordinary index fetches while checking for exclusion conflicts.  Per
  report from Noah Misch.  No need for back-patch because the Assert
  wasn't there before 9.1.
  http://git.postgresql.org/pg/commitdiff/dccfb72892acabd25568539ec882cc44c57c25bd

Robert Haas pushed:

- Avoid creating init fork for unlogged indexes when it already
  exists.  Report by Greg Sabino Mullane, diagnosis and preliminary
  patch by Andres Freund, corrections by me.
  http://git.postgresql.org/pg/commitdiff/b8be5431a2baa2ea4a5140f1a49c3360deb4d64e

- Fix vim-induced typo.
  http://git.postgresql.org/pg/commitdiff/5295fa8c0bb396bf2866205e093bc04d7a3394fe

Bruce Momjian pushed:

- Use proper SGML doc entities rather than angle-brackets.  Marco
  Nenciarini
  http://git.postgresql.org/pg/commitdiff/a20bc9c8666ad81ff3fe26cfab2efd42d6df7f34

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Magnus Hagander sent in a patch to fix an infelicity in SSL handling
in pg_hba.conf.

Florian Pflug sent in two revisions of a patch to fix a bug in XPATH()
when the expression returns a scalar value.

Heikki Linnakangas sent in a patch to fix an issue with SSI predicate
locking, changing from row to tuple.

Heikki Linnakangas sent in two revisions of a patch to fix some
infelicities in nested CASE-WHEN scoping.

Alvaro Herrera sent in four revisions of a patch to enable CHECK
constraints to be declared as NOT VALID, as FOREIGN KEY ones can now.

Radoslaw Smogura sent in a patch to create BLOBs and attendant
structures.

Mark Kirkwood sent in three revisions of a patch to add the ability to
constrain backend temporary file space.

Kevin Grittner sent in a patch to fix some comments in the SSI code.

Teodor Sigaev and Tom Lane traded patches to fix an issue with VACUUM
and composite row types.

Robert Haas sent in two revisions of a patch to clean up
InitProcLocal.

Pavel Stehule sent in another revision of the patch to enhance GET
DIAGNOSTICS.

Andrew Chernow sent in two revisions of a patch to fix an issue in
PQsetvalue.

Kevin Grittner sent in two revisions of a patch to fix an infelicity
in DDL under SSI.

Alexander Korotkov sent in a WIP patch to do a faster GiST index build.

Robert Haas sent in a WIP patch to reduce the overhead of frequent
table locks.

Gurjeet Singh sent in two more revisions of a patch to allow psql to
include files relative to the current file.

Josh Kupershmidt sent in another revision of the patch to allow \dd to
show constraint comments in psql.

Pavel Stehule sent in a WIP patch to add some new diagnostics to
errors.