== PostgreSQL Weekly News - January 31 2010 == - Mailing list pgsql-announce

== PostgreSQL Weekly News - January 31 2010 ==

Google Summer of Code applications are due March 9, but don't wait
until then to get in touch with Selena Deckelman at selena AT
postgresql DOT org.

The Seattle PostgreSQL Users' Group will be having their first meeting
on February 9, 2010.  Details below:
http://www.seapug.org/talk.php?talk=1

== PostgreSQL Product News ==

GNUmed EMR client 0.6.0 and GNUmed EMR server 12.0 released.
http://wiki.gnumed.de/

== PostgreSQL 8.5 Feature of the Week ==

Uniqueness constraints can now be deferred until commit time.

== PostgreSQL Jobs for January ==

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

== PostgreSQL Local ==

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

The German Language PostgreSQL User Group has a booth at Chemnitz Linuxdays
2010 on March 13 and 14, 2010 in Chemnitz, Germany.  Andreas (ads)
Scherbaum will be giving a talk on PostgreSQL 9.0 and a workshop on
tuning PostgreSQL.
http://andreas.scherbaum.la/blog/archives/652-PostgreSQL-stand-at-Chemnitz-Linux-Days-2010.html
http://chemnitzer.linux-tage.de/2010/vortraege/plan.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/

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

Jeff Davis reviewed Joachim Wieland's patch to fix the behavior of
LISTEN/NOTIFY when the queue is full.
== Applied Patches ==

Heikki Linnakangas committed:

- Add note that PREPARE TRANSACTION is for transaction managers, not
  regular applications.  Also add a comment pointing out that
  tab-completion for PREPARE TRANSACTION is missing on purpose.

- Make standby server continuously retry restoring the next WAL
  segment with restore_command, if the connection to the primary
  server is lost.  This ensures that the standby can recover
  automatically, if the connection is lost for a long time and standby
  falls behind so much that the required WAL segments have been
  archived and deleted in the master.  This also makes standby_mode
  useful without streaming replication; the server will keep retrying
  restore_command every few seconds until the trigger file is found.
  That's the same basic functionality pg_standby offers, but without
  the bells and whistles.  To implement that, refactor the
  ReadRecord/FetchRecord functions.  The FetchRecord() function
  introduced in the original streaming replication patch is removed,
  and all the retry logic is now in a new function called
  XLogReadPage().  XLogReadPage() is now responsible for executing
  restore_command, launching walreceiver, and waiting for new WAL to
  arrive from primary, as required.  This also changes the life cycle
  of walreceiver.  When launched, it now only tries to connect to the
  master once, and exits if the connection fails, or is lost during
  streaming for any reason.  The startup process detects the death,
  and re-launches walreceiver if necessary.

- Fix bug in wasender's xlogid boundary handling, reported by Erik
  Rijkers.  LogwrtRqst.Write can be set to non-existent FF log
  segment, we mustn't try to send that in XLogSend().  Also fix
  similar bug in ReadRecord(), which I just introduced in the
  ReadRecord() refactoring patch.

- Change a few remaining calls of XLogArchivingActive() to use
  XLogIsNeeded() instead, to determine if an otherwise non-logged
  operation needs to be logged in WAL for standby servers.  Fujii
  Masao.

- In pgsql/src/backend/access/transam/xlog.c, fix crashing bug at the
  end of recovery in Streaming Replication, when restore_command is
  not given. Fujii Masao.

Tom Lane committed:

- Add get_bit/set_bit functions for bit strings, paralleling those for
  bytea, and implement OVERLAY() for bit strings and bytea.  In
  passing also convert text OVERLAY() to a true built-in, instead of
  relying on a SQL function.  Leonardo Francalanci, reviewed by Kevin
  Grittner.

- In pgsql/src/bin/initdb/initdb.c, un-break initdb logic for
  commenting out IPv6 pg_hba.conf line.

- Remove the default_do_language parameter, instead making DO use a
  hardwired default of "plpgsql".  This is more reasonable than it was
  when the DO patch was written, because we have since decided that
  plpgsql should be installed by default.  Per discussion, having a
  parameter for this doesn't seem useful enough to justify the risk of
  application breakage if the value is changed unexpectedly.

- In pgsql/src/backend/regex/regcomp.c, fix some comments that got
  mangled by pgindent.

- In pgsql/src/bin/psql/print.c, fix memory leakage introduced into
  print_aligned_text by 8.4 changes (failure to free col_lineptrs[]
  array elements) and exacerbated in the current devel cycle (failure
  to free "wrap").  This resulted in moderate bloat of psql over long
  script runs.  Noted while testing bug #5302, although what the
  reporter was complaining of was backend-side leakage.

- In pgsql/src/backend/tcop/dest.c, avoid performing encoding
  conversion on command tag strings during EndCommand.  Since all
  current and foreseeable future command tags will be pure ASCII,
  there is no need to do conversion on them.  This saves a few cycles
  and also avoids polluting otherwise-pristine subtransaction memory
  contexts, which is the cause of the backend memory leak exhibited in
  bug #5302.  (Someday we'll probably want to have a better method of
  determining whether subtransaction contexts need to be kept around,
  but today is not that day.) Backpatch to 8.0.  The cycle-shaving
  aspect of this would work in 7.4 too, but without subtransactions
  the memory-leak aspect doesn't apply, so it doesn't seem worth
  touching 7.4.

- In pgsql/src/backend/libpq/auth.c, get rid of unportable use of
  socklen_t --- we have a configure test for that, so use it.

- In pgsql/src/include/port.h, parenthesize this macro, just in case.

- Fix memory leak created by deferrable-index-constraints patches.  We
  need to free the OID list returned by ExecInsertIndexTuples to avoid
  a query-lifespan memory leak.  When many rows require rechecking,
  this can be a significant leak --- it's even more than the space
  used for the queued trigger events.  Dean Rasheed.

- In pgsql/src/backend/regex/regexec.c, change regexp engine's
  ccondissect/crevdissect routines to perform DFA matching before
  recursing instead of after.  The DFA match eliminates unworkable
  midpoint choices a lot faster than the recursive check, in most
  cases, so doing it first can speed things up; particularly in
  pathological cases such as recently exhibited by Michael Glaesemann.
  In addition, apply some cosmetic changes that were applied upstream
  (in the Tcl project) at the same time, in order to sync with
  upstream version 1.15 of regexec.c.  Upstream apparently intends to
  backpatch this, so I will too.  The pathological behavior could be
  unpleasant if encountered in the field, which seems to justify any
  risk of introducing new bugs.  Tom Lane, reviewed by Donal K.
  Fellows of Tcl project

Simon Riggs committed:

- In pgsql/src/backend/access/transam/xlog.c, fix longstanding gripe
  that we check for 0000000001.history at start of archive recovery,
  even when we know it is never present.

- In pgsql/src/backend/storage/lmgr/lock.c, use malloc() in
  GetLockConflicts() when called InHotStandby to avoid repeated palloc
  calls. Current code assumed this was already true, so this is a bug
  fix.

- Filter recovery conflicts based upon dboid from relfilenode of WAL
  records for heap and btree. Minor change, mostly API changes to pass
  through the required values. This is a simple change though also
  provides the refactoring required for further enhancements to
  conflict processing using the relOid. Changes only have effect
  during Hot Standby.

- Augment WAL records for btree delete with GetOldestXmin() to reduce
  false positives during Hot Standby conflict processing. Simple patch
  to enhance conflict processing, following previous discussions.
  Controlled by parameter minimize_standby_conflicts = on | off, with
  default off allows measurement of performance impact to see whether
  it should be set on all the time.

- In pgsql/src/backend/storage/lmgr/lock.c, adjust GetLockConflicts()
  so that it uses TopMemoryContext when executed InHotStandby. Cleaner
  solution than using malloc or palloc depending upon situation, as
  proposed by Tom.

- Detect early deadlock in Hot Standby when Startup is already
  waiting.  First stage of required deadlock detection to allow
  re-enabling max_standby_delay setting of -1, which is now essential
  in the absence of improved relation- specific conflict resoluton.
  Requested by Greg Stark et al.

Peter Eisentraut committed:

- Make the default pg_hba.conf and pg_ident.conf align at 8-character
  boundaries to make it easier to use with tabs.

- Reformat the comments in pg_hba.conf and pg_ident.conf.  These files
  have apparently been edited over the years by a dozen people with as
  many different editor settings, which made the alignment of the
  paragraphs quite inconsistent and ugly.  I made a pass of M-q with
  Emacs to straighten it out.

- Type table feature.  This adds the CREATE TABLE name OF type
  command, per SQL standard.

Michael Meskes committed:

- Applied patch by Zoltan Boszormenyi to add out-of-scope cursor
  support to native mode.

- In pgsql/doc/src/sgml/ecpg.sgml, finally applied patch by Zoltan
  Boszormenyi to add the new features his patches added to the docs.

- Added test case that was part of Zoltan's ECPG patch but apparently
  wasn't part of my commit.

- Fixed a few typos in ecpg. Two were in comments, the third made a
  log output reverse yes and no.

- Changed ECPG outofscope handling to always print out statements in
  the same order so regression testing is possible.  Zoltan
  Boszormenyi.

Bruce Momjian committed:

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

Andrew Dunstan committed:

- Various small improvements and cleanups for PL/Perl.  1. Allow
  (ineffective) use of 'require' in plperl If the required module is
  not already loaded then it dies.  So "use strict;" now works in
  plperl.  2. Pre-load the feature module if perl >= 5.10.  So "use
  feature :5.10;" now works in plperl.  3. Stored procedure subs are
  now given names.  The names are not visible in ordinary use, but
  they make tools like Devel::NYTProf and Devel::Cover much more
  useful.  4. Simplified and generalized the subroutine creation code.
  Now one code path for generating sub source code, not four.  Can
  generate multiple 'use' statements with specific imports (which
  handles plperl.use_strict currently and can easily be extended to
  handle a plperl.use_feature=':5.12' in future).  5. Disallows use of
  Safe version 2.20 which is broken for PL/Perl.
  http://rt.perl.org/rt3/Ticket/Display.html?id=72068 6. Assorted
  minor optimizations by pre-growing data structures.  Patch from Tim
  Bunce, reviewed by Alex Hunsaker.

- In pgsql/doc/src/sgml/plperl.sgml, fix plperl.sgml errors from
  recent commit.

- In pgsql/src/pl/plperl/plperl.c, fix bug found by warning from
  recent gcc. patch from Tim Bunce.

- Add new make targets "world", "install-world" and
  "installcheck-world" to build, install and check just about
  everything.  In addition to everything built installed and tested by
  all, install and installcheck targets, these build HTML Docs, build
  and test contrib, and test PLs and ECPG.

- In pgsql/GNUmakefile.in, fix thinko in new installcheck-world target

- Add plperl.on_perl_init setting to provide for initializing the perl
  library on load. Also, handle END blocks in plperl.  Database access
  is disallowed during both these operations, although it might be
  allowed in END blocks in future.  Patch from Tim Bunce.

Magnus Hagander committed:

- Add support for RADIUS authentication.

- In pgsql/doc/src/sgml/client-auth.sgml, fix typo.  Noted by Thom
  Brown.

- In pgsql/src/include/port/solaris.h, define INADDR_NONE on Solaris
  when it's missing. Per a couple of buildfarm members complaining.

- Add functions to reset the statistics counter for a single
  table/index or a single function.

- In pgsql/src/backend/port/win32/signal.c, fix race condition in
  win32 signal handling.  There was a race condition where the
  receiving pipe could be closed by the child thread if the main
  thread was pre-empted before it got a chance to create a new one,
  and the dispatch thread ran to completion during that time.  One
  symptom of this is that rows in pg_listener could be dropped under
  heavy load.  Analysis and original patch by Radu Ilie, with some
  small modifications by Magnus Hagander.

- In pgsql/src/port/kill.c, remove now unnecessary loop around
  CallNamedPipe().  Radu Ilie.

- In pgsql/src/backend/postmaster/pgstat.c, make checks for invalid
  pgStatSock use PGINVALID_SOCKET.

Joe Conway committed:

- Introduce two new libpq connection functions, PQconnectdbParams and
  PQconnectStartParams. These are analogous to PQconnectdb and
  PQconnectStart respectively. They differ from the legacy functions
  in that they accept two NULL-terminated arrays, keywords and values,
  rather than conninfo strings. This avoids the need to build the
  conninfo string in cases where it might be inconvenient to do so.
  Includes documentation.  Also modify psql to utilize
  PQconnectdbParams rather than PQsetdbLogin.  This allows the new
  config parameter application_name to be set, which in turn is
  displayed in the pg_stat_activity view and included in CSV log
  entries. This will also ensure both new functions get regularly
  exercised.  Patch by Guillaume Lelarge with review and minor
  adjustments by Joe Conway.

ITAGAKI Takahiro committed:

- In pgsql/src/backend/tcop/utility.c, fix command tag for ALTER LARGE
  OBJECT.

- Add string_agg aggregate functions. The one argument version
  concatenates the input values into a string. The two argument
  version also does the same thing, but inserts delimiters between
  elements.  Original patch by Pavel Stehule, reviewed by David E.
  Wheeler and me.

Robert Haas committed:

- Allow psql variables to be interpolated with literal or identifier
  escaping.  Loosely based on a patch by Pavel Stehule.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Pavel Stehule sent in another revision of the patch to add a
string_agg() aggregate which concatenates the elements of aggregation
with an internal separator.

Pavel Stehule sent in another revision of the patch to introduce
quoting behavior to psql variables.

Selena Deckelmann sent in a patch to divide process from debugging
information in pg_standby.

Peter Eisentraut sent in another revision of the typed table patch per
review from Hitoshi Harada.

Tim Bunce sent in another revision of the patch to clean up namespaces
and Safe initialization for PL/perl.

Robert Haas sent in another revision of the patch to add red-black
trees to GIN indexes.  Mark Cave-Ayland sent another revision in
response to this.

Zoltan Boszormenyi sent in three more revisions of the patch to fix
out-of-scope cursor handling in ECPG.

David Christensen sent in a patch to add a \whoami option to psql.

Marko (johto) Tiikkaja sent in another revision of the patch to add
general DML to CTEs.

KaiGai Kohei sent in three more revisions of the patch to fix the
behavior of ALTER TABLE...RENAME TO... in the case of multiple
inheritance.

KaiGai Kohei sent in another revision of the patch to add access
controls to large objects.

Tim Bunce sent in two more revisions of the patch to add
plperl.on_[un]trusted_init.

Leonardo Francalanci sent in another revision of the patch to improve
CLUSTER's performance.

Robert Haas sent in another revision of the patch to add hash
information to EXPLAIN.

Robert Haas sent in a patch to remove contrib/xml2.  Mike Rylander
sent a smaller one to add the analogous functions to the core
distribution.

Simon Riggs sent in two revisions of a patch to add relation-specific
deferred conflict resolution.

Fujii Masao sent in a patch to extend the format of the string
returned by pg_last_xlog_receive/replay_location() to include the
timeline ID, and teaches pg_xlogfile_name[_offset]() to accept that
format, too.

Alexey Klyukin sent in a patch to improve the documentation of PL/perl
database access functions.

Zoltan Boszormenyi sent in another revision of the patch to fix NaN
and Inf behaviors in ECPG.

Simon Riggs sent in a patch to fix the behavior of VACUUM FULL in the
streaming replication case.

Tim Bunce sent in another revision of the patch to PL/Perl to move the
internal function from namespace main:: to namespaces
PostgreSQL::InServer[::safe], and restructure the Safe compartment.

Guillaume Lelarge sent in a patch to make the PostgreSQL binaries use
the new PQconnectdbParamss libpq functions.

Fujii Masao sent in a patch to shut down the server, including the
startup process and walreceiver, as soon as all read-only connections
have died.  This fixes a corner case in streaming replication.