== PostgreSQL Weekly News - May 31 2010 ==

== PostgreSQL Weekly News - May 31 2010 ==

PostgreSQL EU Board election results are in.
http://blog.hagander.net/archives/169-PostgreSQL-Europe-election-results.html

== PostgreSQL Jobs for May ==

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

== PostgreSQL Local ==

Registration for Open Source Bridge is open and talks are announced!
June 1-4, 2010 in Portland, Oregon.
http://opensourcebridge.org/events/2010/proposals/

Michael Renner will be speaking at AMOOCON, which goes from June 4-6, 2010.
http://www.amoocon.de/speakers/214

SouthEast LinuxFest 2010 will take place in Spartanburg, SC USA on
June 11-13.  PostgreSQL gurus Joshua Drake and Andrew Dunstan will be
giving some must-see PostgreSQL-related presentations.
http://southeastlinuxfest.org/

Michael Renner will be speaking at Netways OSDC in Nuremberg, Germany,
which will be June 23-24, 2010.
http://www.netways.de/osdc/y2010/programm/

CHAR(10), the PostgreSQL Conference on Clustering, High Availability
and Replication is now open for online registration and bookings.
July 2-3 2010, Oriel College, Oxford University, UK
http://www.char10.org/

PDXPUG Day will be July 18, 2010 at the Oregon Convention Center in
Portland, OR. For more information, see:
http://wiki.postgresql.org/wiki/PDXPUGDay2010

OSCON will take place in Portland, Oregon July 19-23, 2010.
http://www.oscon.com/oscon2010

== 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) Spanish language
to pwn(at)arpug(dot)com(dot)ar(dot)

== Applied Patches ==

Bruce Momjian committed:

- In pgsql/contrib/pg_upgrade/option.c, split apart pg_upgrade user
lookup and root check so '--help' shows proper default username.

- In pgsql/doc/src/sgml/pgupgrade.sgml, update pg_upgrade docs to show
options in alphabetical order, and improve 8.3 doc limitations
paragraph.

- In pgsql/contrib/pg_upgrade/info.c, in pg_upgrade, test for
datallowconn instead of hardcoding template0.

- In pgsql/contrib/pg_upgrade/option.c, re-order pg_upgrade 'help'
options to be alphabetical.

- In pgsql/contrib/pg_upgrade/IMPLEMENTATION.jp, add Japanese
Implementation file to CVS.

- In /doc/src/sgml/pgupgrade.sgml, add pg_upgrade docs about binary
compatibility, per Robert Haas.

- Modify pg_standby, pgbench, and pg_upgrade manual pages to be
consistent in their display of command-line options with other
client applications.

- In pgsql/contrib/pg_upgrade/IMPLEMENTATION, update pg_upgrade
IMPLEMENTATION doc file to match current 9.0 behavior.

- Remove IMPLEMENTATION.jp file from pg_upgrade docs; still in
pgFoundry for pg_migrator, per suggestion from Magnus Hagander.

- In pgsql/doc/src/sgml/maintenance.sgml, document pgFouine and
check_postgres as log analysis options.

- In pgsql/doc/src/sgml/maintenance.sgml, fix SGML markup.

- In pgsql/doc/src/sgml/installation.sgml, document use of VPATH
builds. David Fetter.

- In pgsql/src/backend/executor/execUtils.c, add C comment that we
will have to remove an exclusion constraint check if we ever
implement '<>' index opclasses. Jeff Davis.

- In pgsql/doc/src/sgml/ref/create_domain.sgml, document that NOT NULL
domain constraints are not always honored.

- Clarify the meaning of "trusted language" in the documentation.

Michael Meskes committed:

- Added a configure test for "long long" datatypes. So far this is
only used in ecpg and replaces the old test that was kind of
hackish.

- Replace self written 'long long int' configure test by standard
'AC_TYPE_LONG_LONG_INT' macro call.

Tom Lane committed:

- In pgsql/src/backend/optimizer/plan/createplan.c, fix oversight in
construction of sort/unique plans for UniquePaths. If the original
IN operator is cross-type, for example int8 = int4, we need to use
int4 < int4 to sort the inner data and int4 = int4 to unique-ify it.
We got the first part of that right, but tried to use the original
IN operator for the equality checks. Per bug #5472 from Vlad
Romascanu. Backpatch to 8.4, where the bug was introduced by the
patch that unified SortClause and GroupClause. I was able to take
out a whole lot of on-the-fly calls of get_equality_op_for_ordering_op(),
but failed to realize that I needed to put one back in right here :-(

- In pgsql/src/interfaces/libpq/fe-secure.c, add missing newlines to
some SSL-related error messages. Noted while testing.

- In pgsql/src/backend/libpq/be-secure.c, tell openssl to include the
names of the root certs the server trusts in requests for client
certs. This lets a client with a keystore select the appropriate
client certificate to send. In particular, this is necessary to get
Java clients to work in all but the most trivial configurations.
Per discussion of bug #5468. Craig Ringer

- In pgsql/src/backend/libpq/be-secure.c, minor editorialization for
be-secure.c: fix comments and some formatting infelicities.

- In pgsql/src/backend/libpq/hba.c, the message style police pay a
visit to hba.c.

- In pgsql/src/backend/libpq/auth.c, fix bogus error message for
SSL-cert authentication, due to lack of a uaCert entry in
auth_failed(). Put the switch entries into a sane order, namely the
one the enum is declared in.

- Rearrange libpq's SSL initialization to simplify it and make it
handle some additional cases correctly. The original coding failed
to load additional (chain) certificates from the client cert file,
meaning that indirectly signed client certificates didn't work
unless one hacked the server's root.crt file to include intermediate
CAs (not the desired approach). Another problem was that everything
got loaded into the shared SSL_context object, which meant that
concurrent connections trying to use different sslcert settings
could well fail due to conflicting over the single available slot
for a keyed certificate. To fix, get rid of the use of
SSL_CTX_set_client_cert_cb(), which is deprecated anyway in the
OpenSSL documentation, and instead just unconditionally load the
client cert and private key during connection initialization. This
lets us use SSL_CTX_use_certificate_chain_file(), which does the
right thing with additional certs, and is lots simpler than the
previous hacking about with BIO-level access. A small disadvantage
is that we have to load the primary client cert a second time with
SSL_use_certificate_file, so that that one ends up in the correct
slot within the connection's SSL object where it can get paired with
the key. Given the other overhead of making an SSL connection, that
doesn't seem worth worrying about. Per discussion ensuing from bug
#5468.

- Improve SSL-related documentation. Explain how to deal with
certificate chains, do assorted wordsmithing.

- In pgsql/src/backend/commands/indexcmds.c, make CREATE INDEX run
expression preprocessing on a proposed index expression before it
checks whether the expression is immutable. This covers two cases
that were previously handled poorly: 1. SQL function inlining could
reduce the apparent volatility of the expression, allowing an
expression to be accepted where it previously would not have been.
As an example, polymorphic functions must be marked with the
worst-case volatility they have for any argument type, but for
specific argument types they might not be so volatile, so indexing
could be allowed. (Since the planner will refuse to inline
functions in cases where the apparent volatility of the expression
would increase, this won't break any cases that were accepted
before.) 2. A nominally immutable function could have default
arguments that are volatile expressions. In such a case insertion
of the defaults will increase both the apparent and actual
volatility of the expression, so it is *necessary* to check this
before allowing the expression to be indexed. Back-patch to 8.4,
where default arguments were introduced.

- In pgsql/src/include/catalog/pg_proc.h, fix the volatility marking
of textanycat() and anytextcat(): they were marked immutable, but
that is wrong in general because the cast from the polymorphic
argument to text could be stable or even volatile. Mark them
volatile for safety. In the typical case where the cast isn't
volatile, the planner will deduce the correct expression volatility
after inlining the function, so performance is not lost. The
just-committed fix in CREATE INDEX also ensures this won't break any
indexing cases that ought to be allowed. Per discussion, I'm not
bumping catversion for this change, as it doesn't seem critical
enough to force an initdb on beta testers.

- In pgsql/src/backend/utils/misc/ps_status.c, change ps_status.c to
explicitly track the current logical length of ps_buffer. This
saves cycles in get_ps_display() on many popular platforms, and more
importantly ensures that get_ps_display() will correctly return an
empty string if init_ps_display() hasn't been called yet. Per
trouble report from Ray Stell, in which log_line_prefix %i produced
junk early in backend startup. Back-patch to 8.0. 7.4 doesn't have
%i and its version of get_ps_display() makes no pretense of avoiding
pad junk anyhow.

- In pgsql/src/backend/executor/nodeMergejoin.c, rejigger mergejoin
logic so that a tuple with a null in the first merge column is
treated like end-of-input, if nulls sort last in that column and we
are not doing outer-join filling for that input. In such a case,
the tuple cannot join to anything from the other input (because we
assume mergejoinable operators are strict), and neither can any
tuple following it in the sort order. If we're not interested in
doing outer-join filling we can just pretend the tuple and its
successors aren't there at all. This can save a great deal of time
in situations where there are many nulls in the join column, as in a
recent example from Scott Marlowe. Also, since the planner tends to
not count nulls in its mergejoin scan selectivity estimates, this is
an important fix to make the runtime behavior more like the
estimate. I regard this as an omission in the patch I wrote years
ago to teach mergejoin that tuples containing nulls aren't joinable,
so I'm back-patching it. But only to 8.3 --- in older versions, we
didn't have a solid notion of whether nulls sort high or low, so
attempting to apply this optimization could break things.

- Rewrite LIKE's %-followed-by-_ optimization so it really works (this
time for sure ;-)). It now also optimizes more cases, such as %_%_.
Improve comments too. Per bug #5478. In passing, also rename the
TCHAR macro to GETCHAR, because pgindent is messing with the
formatting of the former (apparently it now thinks TCHAR is a
typedef name). Back-patch to 8.3, where the bug was introduced.

- In pgsql/src/backend/utils/adt/like_match.c, fix oversight in the
previous patch that made LIKE throw error for \ at the end of the
pattern: the code path that handles \ just after % should throw
error too. As in the previous patch, not back-patching for fear of
breaking apps that worked before.

- In pgsql/src/bin/psql/common.c, abort a FETCH_COUNT-controlled query
if we observe any I/O error on the output stream. This typically
indicates that the user quit out of $PAGER, or that we are writing
to a file and ran out of disk space. In either case we shouldn't
bother to continue fetching data. Stephen Frost.

- In pgsql/doc/src/sgml/perform.sgml, add text to "Populating a
Database" pointing out that bulk data load into a table with foreign
key constraints eats memory. Per off-line discussion of bug #5480
with its reporter. Also do some minor wordsmithing elsewhere in the
same section.

- Change the notation for calling functions with named parameters from
"val AS name" to "name := val", as per recent discussion. This
patch catches everything in the original named-parameters patch, but
I'm not certain that no other dependencies snuck in later (grepping
the source tree for all uses of AS soon proved unworkable). In
passing I note that we've dropped the ball at least once on keeping
ecpg's lexer (as opposed to parser) in sync with the backend. It
would be a good idea to go through all of pgc.l and see if it's in
sync now. I didn't attempt that at the moment.

- In pgsql/src/backend/tsearch/ts_typanalyze.c, fix misuse of Lossy
Counting (LC) algorithm in compute_tsvector_stats(). We must filter
out hashtable entries with frequencies less than those specified by
the algorithm, else we risk emitting junk entries whose actual
frequency is much less than other lexemes that did not get
tabulated. This is bad enough by itself, but even worse is that
tsquerysel() believes that the minimum frequency seen in
pg_statistic is a hard upper bound for lexemes not included, and was
thus underestimating the frequency of non-MCEs. Also, set the
threshold frequency to something with a little bit of theory behind
it, to wit assume that the input distribution is approximately
Zipfian. This might need adjustment in future, but some preliminary
experiments suggest that it's not too unreasonable. Back-patch to
8.4, where this code was introduced. Jan Urbanski, with some
editorialization by Tom Lane.

Robert Haas committed:

- In pgsql/src/backend/postmaster/postmaster.c, more fixes for
shutdown during recovery. 1. If we receive a fast shutdown request
while in the PM_STARTUP state, process it just as we would in
PM_RECOVERY, PM_HOT_STANDBY, or PM_RUN. Without this change, an
early fast shutdown followed by Hot Standby causes the database to
get stuck in a state where a shutdown is pending (so no new
connections are allowed) but the shutdown request is never processed
unless we end Hot Standby and enter normal running. 2. Avoid
removing the backup label file when a smart or fast shutdown occurs
during recovery. It makes sense to do this once we've reached
normal running, since we must be taking a backup which now won't be
valid. But during recovery we must be recovering from a previously
taken backup, and any backup label file is needed to restart
recovery from the right place. Fujii Masao and Robert Haas

- In pgsql/src/bin/psql/help.c, fix psql help: \da+ is same as \da,
but \daS is not. Noted by Stephen Frost.

- In pgsql/src/backend/postmaster/postmaster.c, avoid starting
walreceiver in states where it shouldn't be running. In particular,
it's bad to start walreceiver when in state PM_WAIT_BACKENDS,
because we have no provision to kill walreceiver when in that state.
Fujii Masao.

Simon Riggs committed:

- Hot Standby Defer buffer pin deadlock check until deadlock_timeout
has expired. During Hot Standby we need to check for buffer pin
deadlocks when the Startup process begins to wait, in case it never
wakes up again. We previously made the deadlock check immediately
on the basis it was cheap, though clearer thinking and prima facie
evidence shows that was too simple. Refactor existing code to make
it easy to add in deferral of deadlock check until deadlock_timeout
allowing a good reduction in deadlock checks since far few buffer
pins are held for that duration. It's worth doing anyway, though
major goal is to prevent further reports of context switching with
high numbers of users on occasional tests.

Heikki Linnakangas committed:

- In pgsql/src/backend/replication/walsender.c, in walsender, don't
sleep if there's outstanding WAL waiting to be sent, otherwise we
effectively rate-limit the streaming as pointed out by Simon Riggs.
Also, send the WAL in smaller chunks, to respond to signals more
promptly.

- In pgsql/src/backend/replication/walsender.c, thinko in previous
commit: ensure that MAX_SEND_SIZE is always greater than
XLOG_BLCKSZ, by defining it as 16 * XLOG_BLCKSZ rather than directly
as 128k bytes.

- In pgsql/src/backend/access/transam/xlog.c, adjust comment to
reflect that we now have Hot Standby. Pointed out by Robert Haas.

- In pgsql/doc/src/sgml/ref/savepoint.sgml, make it more clear that
you need to release savepoint with RELEASE SAVEPOINT to make an
older savepoint with the same name accessible. It's also possible
to implicitly release the savepoint by rolling back to an earlier
savepoint, but mentioning that too would make the note just more
verbose and confusing. Florian Pflug.

- In pgsql/doc/src/sgml/high-availability.sgml, update High
Availability docs. Clarify terms master/primary standby/slave,
move two paragraphs that apply to log shipping in general from the
"Alternative method for log shipping" section to the earlier
sections. Add varname tags where missing. Some small wording
changes.

- In pgsql/doc/src/sgml/high-availability.sgml, fix typos in recent
doc patch. Fujii Masao.

ITAGAKI Takahiro committed:

- Mark PG_MODULE_MAGIC and PG_FUNCTION_INFO_V1 with PGDLLEXPORT
independently from BUILDING_DLL. It is always __declspec(dllexport).

- In pgsql/src/include/port/cygwin.h and pgsql/src/include/port/win32.h
PGDLLEXPORT is __declspec (dllexport) only on MSVC, but is
__declspec (dllimport) on other compilers because cygwin and mingw
don't like dllexport.

Peter Eisentraut committed:

- In pgsql/doc/src/sgml/syntax.sgml, small correction/clarification in
discussion of Unicode literals.

== Rejected Patches (for now) ==

Stephen Frost's in a patch to move a "long long" check to c.h. The
actual problem, if it needs fixing, needs to be fixed in autoconf.

== Pending Patches ==

KaiGai Kohei sent in six revisions of a patch to rework Robert Haas's
patch to add an ExecutorCheckPerms() hook.

Fujii Masao sent in two more revisions of the patch to fix smart
shutdown during recovery.

Craig Ringer sent in two revisions of a patch to fix an issue with SSL
certificates.

Simon Riggs sent in a patch to fix the sporadically reduced speed and
context switch storm issues in Hot Standby.

Mike Fowler sent in three revisions of a patch to add XMLEXISTS from
the SQL/XML section of the SQL standard.

Jonathan Leto and Josh Tolley traded patches to document the PL
template handler better.

Stephen Frost sent in a patch to do regression tests on psql's
backslash commands.

ITAGAKI Takahiro sent in a patch to fix an issue where fillfactor in
TOAST tables is set to 0, which is not always right.

Robert Haas sent in a patch to add a get_whatever_oid() function.
This factors out much code duplication.

Simon Riggs sent in another revision of the patch to fix keepalive
issues in max_standby_delay.

Peter Eisentraut sent in a patch for 9.1 that adds functions
pg_stat_get_backend_client_addr and pg_stat_get_backend_client_port.