== PostgreSQL Weekly News - January 17 2010 ==

== PostgreSQL Weekly News - January 17 2010 ==

The final scheduled commitfest for the upcoming release--could it be
9.0?--has begun.  It's time to review all those patches including
Streaming Replication.  Rapid reviews mean alpha can come sooner, and
alpha gates beta, and beta gates release.  If you haven't reviewed a
patch before, here's your chance!
http://wiki.postgresql.org/wiki/Reviewing_a_Patch
http://commitfest.postgresql.org

The German PostgreSQL User Group has a stand at Chemnitz Linuxdays
2010 on March 13 and 14, 2010 in Chemnitz, Germany.
http://andreas.scherbaum.la/blog/archives/652-PostgreSQL-stand-at-Chemnitz-Linux-Days-2010.html

Linuxfest Northwest 2010 will take place in Bellingham, Washington,
USA on April 24 and 25.  Session, Booth and Sponsorship are available.
http://linuxfestnorthwest.org/

== PostgreSQL Product News ==

boxinfo version 1.1.7
http://bucardo.org/wiki/Boxinfo

tail_n_mail 1.5.9, a log monitor, released.
http://bucardo.org/wiki/Tail_n_mail

== PostgreSQL 8.5 Feature of the Week ==

VACUUM FULL has been changed to now generate all-new files for the
vacuumed table and indexes.  This is based on the old CLUSTER command,
and is both more efficient and more effective.  The old functionality
of VACUUM FULL can still be accessed via VACUUM FULL INPLACE, but will
be incompatible with Hot Standby.

== PostgreSQL Tip of the Week ==

By turning on logging of temporary files using the
log_temp_files option, you can track whether you may need to increase
work_mem or maintenance_work_mem for your databases.

== PostgreSQL Jobs for January ==

http://archives.postgresql.org/pgsql-jobs/2010-01/threads.php

== PostgreSQL Local ==

PostgreSQL will have a booth at LinuxConf.AU Open Day in Wellington,
NZ.  Josh Berkus and Selena Deckelman will be at LCA all week.
http://www.lca2010.org.nz/programme/open_day

The Call for Papers for East is open until January 30, 2010.  Details at:
http://www.postgresqlconference.org/

PGDay-Cuba 2010 will be held 4-6 February 2010, in La Havana at the
University of Science Informatic.  Attendees from Cuba, the Dominican
Republic and Venezuela are invited.

FOSDEM 2010 will be in Brussels, Belgium on February 6-7, 2010.
http://www.postgresql.eu/events/schedule/fosdem2010/

The German Speaking PostgreSQL User Group has a stand at the Project
Lounge in hall 2 at Cebit 2010 from March 02 to March 06, 2010 in
Hannover, Germany.
http://andreas.scherbaum.la/blog/archives/649-PostgreSQL-stand-at-Cebit-2010.html

Chemnitzer Linuxtage will be in Chemnitz, Germany on March 13-14, 2010.
http://chemnitzer.linux-tage.de/

Andreas (ads) Scherbaum will be teaching a "PostgreSQL for Corporate
Use" course at the adult education center in Magdeburg, Germany May
3-7, 2010.  Details below:
http://andreas.scherbaum.la/blog/archives/650-PostgreSQL-Schulung-als-Bildungsurlaub-03.-07.05.2010-in-Magdeburg.html

PgCon 2010 will be held May 20-21 2010 in Ottawa with tutorials before
on the 18th and 19th.  The RfP is open!
http://www.pgcon.org/2010/papers.php

The Call for Proposals for OSCON is open.  OSCON will take place in
Portland, Oregon July 19-23, 2010.
http://post.oreilly.com/f2f/9z1zqmm5lhkab0uogt3avlvc4u59bro6f917re423d8

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

== Reviews ==

ITAGAKI Takahiro reviewed Pavel Stehule's patch to add USING to
OPEN...FOR EXECUTE in PL/pgsql and marked it "Ready for Committer."

Pavel Stehule reviewed Hitoshi Harada's patch to add more ROWS and
RANGE options to windowing functions.

Andreas Freund reviewed Simon Riggs's patch to fix query-cancel in hot
standby.

Erik Rijkers reviewed Hitoshi Harada's windowing function patch and
found some regression test irregularities.

Robert Haas reviewed Pavel Stehule's new patch for psql which enables
quoting and said it was excessively simplified.

Kevin Grittner reviewed Leonardo F's patch to add substring and
replace() to get and set bit values.  It no longer applies.

Robert Haas reviewed ITAGAKI Takahiro's patch to add partitioning
syntax and suggested some improvements to the documentation of LIST
and RANGE partitions.

Magnus Hagander reviewed Greg Smith's revision of the patch to allow
clearing global statistics.

Jan Urbanski reviewed Arie Bikker's patch to improve xpath handling.

Gregory Stark reviewed the Bloom index patch and noted some
infelicities with rand() and srand(), i.e. they shouldn't be there.
== Applied Patches ==

Andrew Dunstan committed:

- In pgsql/src/tools/msvc/Mkvcbuild.pm, remove use of temp file for
  perlchunks.h, as in recent change to plperl GNUmakefile

Tom Lane committed:

- In pgsql/src/backend/executor/execQual.c, improve ExecEvalVar's
  handling of whole-row variables in cases where the rowtype contains
  dropped columns.  Sometimes the input tuple will be formed from a
  select targetlist in which dropped columns are filled with a NULL of
  an arbitrary type (the planner typically uses INT4, since it can't
  tell what type the dropped column really was).  So we need to relax
  the rowtype compatibility check to not insist on physical
  compatibility if the actual column value is NULL.  In principle we
  might need to do this for functions returning composite types, too
  (see tupledesc_match()).  In practice there doesn't seem to be a bug
  there, probably because the function will be using the same cached
  rowtype descriptor as the caller.  Fixing that code path would
  require significant rearrangement, so I left it alone for now.  Per
  complaint from Filip Rembialkowski.

- Add some simple support and documentation for using process-specific
  oom_adj settings to prevent the postmaster from being OOM-killed on
  Linux systems.  Alex Hunsaker and Tom Lane.

- In pgsql/src/backend/utils/adt/acl.c, fix aclexplode to not explode
  on a zero-entry ACL array.  (An Assert is no substitute for thinking
  clearly :-() Minor style kibitzing too.  Per report from Robert
  Treat.

- In pgsql/src/backend/utils/cache/relcache.c, fix relcache reload
  mechanism to be more robust in the face of errors occurring during a
  reload, such as query-cancel.  Instead of zeroing out an existing
  relcache entry and rebuilding it in place, build a new relcache
  entry, then swap its contents with the old one, then free the new
  entry.  This avoids problems with code believing that a previously
  obtained pointer to a cache entry must still reference a valid
  entry, as seen in recent failures on buildfarm member jaguar.
  (jaguar is using CLOBBER_CACHE_ALWAYS which raises the probability
  of failure substantially, but the problem could occur in the field
  without that.)  The previous design was okay when it was made, but
  subtransactions and the ResourceOwner mechanism make it unsafe now.
  Also, make more use of the already existing rd_isvalid flag, so that
  we remember that the entry requires rebuilding even if the first
  attempt fails.  Back-patch as far as 8.2.  Prior versions have
  enough issues around relcache reload anyway (due to inadequate
  locking) that fixing this one doesn't seem worthwhile.

- In pgsql/src/backend/parser/parse_param.c, make fixed_paramref_hook
  behave properly when there are 'unused' slots in the parameter
  array.  Noted while experimenting with an example from Pavel
  Stehule.  This wouldn't come up in normal use, but it ought to honor
  the specification that a parameter array can have unused slots.

- In pgsql/src/backend/utils/cache/plancache.c, fix bug #5269:
  ResetPlanCache mustn't invalidate cached utility statements,
  especially not ROLLBACK.  ROLLBACK might need to be executed in an
  already aborted transaction, when there is no safe way to revalidate
  the plan.  But in general there's no point in marking utility
  statements invalid, since they have no plans in the normal sense of
  the word; so we might as well work a bit harder here to avoid future
  revalidation cycles.  Back-patch to 8.4, where the bug was
  introduced.

- In pgsql/src/backend/utils/cache/relcache.c, when loading critical
  system indexes into the relcache, ensure we lock the underlying
  catalog not only the index itself.  Otherwise, if the cache load
  process touches the catalog (which will happen for many though not
  all of these indexes), we are locking index before parent table,
  which can result in a deadlock against processes that are trying to
  lock them in the normal order.  Per today's failure on buildfarm
  member gothic_moth; it's surprising the problem hadn't been
  identified before.  Back-patch to 8.2.  Earlier releases didn't have
  the issue because they didn't try to lock these indexes during load
  (instead assuming that they couldn't change schema at all during
  multiuser operation).

- In pgsql/src/port/exec.c, simplify validate_exec() by using
  access(2) to check file permissions, rather than trying to implement
  the equivalent logic by hand.  The motivation for the original
  coding appears to have been to check with the effective uid's
  permissions not the real uid's; but there is no longer any
  difference, because we don't run the postmaster setuid (indeed,
  main.c enforces that they're the same).  Using access() means we
  will get it right in situations the original coding failed to
  handle, such as ACL-based permissions.  Besides it's a lot shorter,
  cleaner, and more thread-safe.  Per bug #5275 from James Bellinger.

- In pgsql/src/backend/replication/walreceiver/Makefile, fix bogus
  subdir setting ... wonder just what that affects ...

- In pgsql/src/backend/replication/walreceiver/Makefile, actually,
  I'll bet the mingw problem is lack of $(BE_DLLLIBS) ...

- In pgsql/src/backend/replication/walreceiver/Makefile, no, scratch
  that, it was getting added twice.

- Second try: walreceiver should not be built with -DBUILDING_DLL

- Do parse analysis of an EXPLAIN's contained statement during the
  normal parse analysis phase, rather than at execution time.  This
  makes parameter handling work the same as it does in ordinary
  plannable queries, and in particular fixes the incompatibility that
  Pavel pointed out with plpgsql's new handling of variable
  references.  plancache.c gets a little bit grottier, but the
  alternatives seem worse.

- PGDLLIMPORT-ize the remaining variables needed by walreceiver.

- In pgsql/src/include/libpq/pqsignal.h, huh, apparently on cygwin we
  HAVE_SIGPROCMASK, so both variants of the BlockSig/UnBlockSig
  declaration have to be PGDLLIMPORT'ified.  Per buildfarm results.

- In pgsql/src/backend/storage/ipc/procarray.c, fix bogus
  initialization of KnownAssignedXids shared memory state --- didn't
  work in EXEC_BACKEND case.

- In pgsql/src/backend/parser/scan.l, fix unportable use of isxdigit()
  with char (rather than unsigned char) argument, per warnings from
  buildfarm member pika.  Also clean up code formatting a trifle.

- Re-order configure tests to reflect the fact that the code generated
  for posix_fadvise and other file-related functions can depend on
  _LARGEFILE_SOURCE and/or _FILE_OFFSET_BITS.  Per report from Robert
  Treat.  Back-patch to 8.4.  This has been wrong all along, but we
  weren't really using posix_fadvise in anger before, and
  AC_FUNC_FSEEKO seems to mask the issue well enough for that
  function.

- In pgsql/src/backend/tcop/postgres.c, remove unnecessary,
  inconsistent flag resets in ProcessInterrupts.

- Improve the handling of SET CONSTRAINTS commands by having them
  search pg_constraint before searching pg_trigger.  This allows saner
  handling of corner cases; in particular we now say "constraint is
  not deferrable" rather than "constraint does not exist" when the
  command is applied to a constraint that's inherently non-deferrable.
  Per a gripe several months ago from hubert depesz lubaczewski.  To
  make this work without breaking user-defined constraint triggers, we
  have to add entries for them to pg_constraint.  However, in return
  we can remove the pgconstrname column from pg_constraint, which
  represents a fairly sizable space savings.  I also replaced the
  tgisconstraint column with tgisinternal; the old meaning of
  tgisconstraint can now be had by testing for nonzero tgconstraint,
  while there is no other way to get the old meaning of nonzero
  tgconstraint, namely that the trigger was internally generated
  rather than being user-created.  In passing, fix an old misstatement
  in the docs and comments, namely that pg_trigger.tgdeferrable is
  exactly redundant with pg_constraint.condeferrable.  Actually, we
  mark RI action triggers as nondeferrable even when they belong to a
  nominally deferrable FK constraint.  The SET CONSTRAINTS code now
  relies on that instead of hard-coding a list of exception OIDs.

- In pgsql/doc/src/sgml/ref/set_constraints.sgml, update SET
  CONSTRAINTS reference page --- it failed to mention EXCLUDE
  constraints and was lacking a couple of other interesting details.

- In pgsql/src/backend/utils/mmgr/portalmem.c, fix portalmem.c to
  avoid keeping a dangling pointer to a cached plan list after it's
  released its reference count for the cached plan.  There are code
  paths that might try to examine the plan list before noticing that
  the portal is already in aborted state.  Report and diagnosis by
  Tatsuo Ishii, though this isn't exactly his proposed patch.

Bruce Momjian committed:

- In pgsql/doc/src/sgml/backup.sgml, remove tabs in SGML file.

- Place tablespace directories in their own subdirectory so
  pg_migrator can upgrade clusters without renaming the tablespace
  directories.  New directory structure format is, e.g.:
  $PGDATA/pg_tblspc/20981/PG_8.5_201001061/719849/83292814

- In src/backend/replication/walreceiver/walreceiver.c, add #include
  <sys/time.h> for struct timeval definition on BSD/OS.

Michael Meskes committed:

- In pgsql/src/interfaces/ecpg/ecpglib/typename.c, applied Zoltan
  Boszormenyi's patch to make char the default sqlda type.  Given that
  undefined types are handled as character strings anyway the type
  translation function can simply return the correcponding ECPGt_char
  type.

- Applied patch by Zoltan Boszormenyi to add DESCRIBE [OUTPUT]
  statement to ecpg.

- In pgsql/src/interfaces/ecpg/ecpglib/descriptor.c, added correct
  error handling in DESCRIBE statement processing by Zoltan
  Boszormenyi.

Modified Files:
--------------

- Removed ecpg Changelog file that was only there for historical
  reasons and isn't actively used anymore.

- Fix SQL3 type return value.  For non-SQL3 types ecpg used to return
  -Oid.  This will break if there are enough Oids to fill the
  namespace.  Therefore we play it safe and return 0 if there is no
  Oid->SQL3 tyoe mapping available.

Magnus Hagander committed:

- In pgsql/doc/src/sgml/install-win32.sgml, update MSVC build
  instructions.  Updated to reflect building with Visual Studio 2008,
  with just the Platform SDK, and for 64-bit Windows.

- In pgsql/src/tools/msvc/Mkvcbuild.pm, build the walreceiver library
  on MSVC as well.

- In pgsql/doc/src/sgml/installation.sgml, fix spelling error, noticed
  by Thomas Shinnick.

- In pgsql/contrib/xml2/xpath.c, silence compiler warning, noted by
  Stefan Kaltenbrunner

- In pgsql/src/tools/msvc/Mkvcbuild.pm, add include directory for
  dblink to find fmgroids.h on VS2008 builds.

Simon Riggs committed:

- First part of refactoring of code for ResolveRecoveryConflict.
  Purposes of this are to centralise the conflict code to allow
  further change, as well as to allow passing through the full reason
  for the conflict through to the conflicting backends.  Backend state
  alters how we can handle different types of conflict so this is now
  required.  As originally suggested by Heikki, no longer optional.

- Teach standby conflict resolution to use SIGUSR1.  Conflict reason
  is passed through directly to the backend, so we can take decisions
  about the effect of the conflict based upon the local state.  No
  specific changes, as yet, though this prepares for later work.
  CancelVirtualTransaction() sends signals while holding
  ProcArrayLock.  Introduce errdetail_abort() to give message detail
  explaining that the abort was caused by conflict processing.  Remove
  CONFLICT_MODE states in favour of using PROCSIG_RECOVERY_CONFLICT
  states directly, for clarity.

- In pgsql/src/backend/storage/ipc/standby.c, message mentions msec
  when it should be seconds, so use s instead of ms.  Noticed by
  Andres Freund.

- In pgsql/src/backend/commands/dbcommands.c, lock database while
  running drop database in Hot Standby to protect against concurrent
  reconnection. Failure during testing showed issue was possible, even
  though earlier analysis seemed to indicate it would not be required.
  Use LockSharedObjectForSession() before
  ResolveRecoveryConflictWithDatabase() and hold lock until end of
  processing for that WAL record. Simple approach to avoid introducing
  further bugs at this stage of development on an improbable issue.

Teodor Sigaev committed:

- Add point_ops opclass for GiST.

Heikki Linnakangas committed:

- Introduce Streaming Replication.  This includes two new kinds of
  postmaster processes, walsenders and walreceiver.  Walreceiver is
  responsible for connecting to the primary server and streaming WAL
  to disk, while walsender runs in the primary server and streams WAL
  from disk to the client.  Documentation still needs work, but the
  basics are there.  We will probably pull the replication section to
  a new chapter later on, as well as the sections describing
  file-based replication.  But let's do that as a separate patch, so
  that it's easier to see what has been added/changed.  This patch
  also adds a new section to the chapter about FE/BE protocol,
  documenting the protocol used by walsender/walreceivxer.  Bump
  catalog version because of two new functions,
  pg_last_xlog_receive_location() and pg_last_xlog_replay_location(),
  for monitoring the progress of replication.  Fujii Masao, with
  additional hacking by Heikki Linnakangas.

- In pgsql/src/backend/replication/walsender.c, remove unused (in
  non-assertion-enabled build) variable.

- In pgsql/src/Makefile, move build of src/backend/replication/walreceiver/
  later in the build process, after src/interfaces, because it depends
  on libpq.  Also add missing lines for clean, etc. targets.  Report
  from Zoltan Boszormenyi.

Peter Eisentraut committed:

- Improved printing of Python exceptions in PL/Python.  Mimic the
  Python interpreter's own logic for printing exceptions instead of
  just using the straight str() call, so that you get plpy.SPIError
  instead of <class 'plpy.SPIError'> and for built-in exceptions
  merely, UnicodeEncodeError.  Besides looking better this cuts down
  on the endless version differences in the regression test expected
  files.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Pavel Stehule sent in two more revisions of the patch to introduce
quoting behavior to psql variables.

Zoltan Boszormenyi sent in four revisions of the lock_timeout GUC
patch.  Jaime Casanova one after Zoltan's first.

ITAGAKI Takahiro sent in a patch to fix a memory leak in dblink when
a tuple-returning query gets cancelled.

Andreas Freund sent in a WIP patch to address an issue where hot
standby doesn't play well with cancelling queries.

KaiGai Kohei sent in a patch clarifying comments in
src/backend/rewrite/rewriteDefine.c.

Marko (johto) Tiikkaja sent in another revision of the writeable CTE
patch.

Teodor Sigaev sent in several patches as WIP to implement
K-Nearest-Neighbor searches.

Kevin Grittner sent in a .gitignore file.

ITAGAKI Takahiro sent in a patch to correct an error in execMain.c

when there's a NOT NULL violation.

Peter Eisentraut sent in a patch to change the order in which pg_dump
dumps functions.

Pavel Stehule sent in a patch to make psql remove redundant
parentheses in trigger definitions.

Hans-Juergen Schoenig sent in a patch to add SQL tracing and tracking
to ECPG.

Pavel Stehule sent in a patch against PL/pgsql which adds an optional
USING clause to OPEN FOR EXECUTE.

Hitoshi Harada sent in two more revisions of the patch to extend ROWS
and RANGE in windowing context, one per Pavel Stehule's review.

Teodor Sigaev sent in another WIP patch to add Bloom indexes as a
contrib module.

Simon Riggs sent in three revisions of a patch to fix the query-cancel
issue in hot standby.

Kevin Grittner sent in another WIP patch for serializable isolation.

Tim Bunce sent in a patch for PL/Perl which adds utility functions,
names stored procedures as $name__$oid, cleans up whitespace in
warnings, does some minor optimizations, and properly uses the
recently updated ppport.h.

KaiGai Kohei sent in another revision of the patch to fix ALTER
TABLE...RENAME TO behavior on multiply inherited columns.

Tim Bunce sent in a patch against PL/Perl which enables "require,"
(works for preloaded modules), "use strict" (and there was much
rejoicing), some 5.10 features, named stored procedure subroutines,
simplified and generalized subroutine creation code, some other
optimizations, and disallows the buggy Safe.pm 2.20.

Markus Wanner (ne Schiltknecht) sent in a tool for testing in
concurrent sessions called dtester.

ITAGAKI Takahiro sent in another revision of the PARTITION syntax
patch.

Simon Riggs sent in a patch to fix a bug with GetConflictingVirtualXIDs.

Greg Smith sent in a patch to clear global statistics.

Bernd Helmle sent in a patch to enable seeing the total table size
including TOAST.

Tim Bunce sent in a patch which adds on_perl_init and proper
destruction to PL/Perl.

Tim Bunce sent in a patch for PL/Perl which adds plperl.on_trusted_init
and plperl.on_untrusted_init GUCs, improves state management in
select_perl_context(), and simplifies the utf8fix code.

Tim Bunce sent in a patch for PL/Perl which moves the internal
functions from the main:: namespace to PostgreSQL::InServer and
PostgreSQL::InServer::safe and restructures the Safe compartment setup
code.

Peter Eisentraut sent in a patch to add a per-user pg_service.conf.

Alex Hunsaker sent in a review and patch atop Robert Haas's attoptions
patch.

Heikki Linnakangas sent in another version of the patch to add a new
XLOG record indicating WAL-skipping.

Guillaume Lelarge sent in another revision of the application name
patch for libpq.

Heikki Linnakangas sent in a patch which pulls walreceiver into the
backend executable and adds some interface functions to it.

Hitoshi Harada sent in a patch to fix the windowing functions patch
per feedback from Erik Rijkers.

Peter Eisentraut sent in a patch to remove a gcc dependency from
the definition of inline functions.

Peter Eisentraut sent in a patch to rearrange the parallel regression
tests so they output the names before they all start instead of after
they all finish.

Tatsuo Ishii sent in a patch which sets portal->stmts to NULL in
src/backend/utils/mmgr/portalmem.c just after
PortalReleaseCachedPlan(portal) in order to fix an issue he found
earlier.

Fujii Masao sent in a patch which adjust streaming replication for
some later changes.