== PostgreSQL Weekly News - January 31 2010 ==

== 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(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot)

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