== PostgreSQL Weekly News - January 24 2010 ==

== PostgreSQL Weekly News - January 24 2010 ==

"PostgreSQL: Das Praxisbuch" by Thomas Pfeiffer and Andreas Wenk published.
http://www.pg-praxisbuch.de

== PostgreSQL 8.5 Feature of the Week ==

The DROP IF EXISTS syntax now works on columns and constraints.

== PostgreSQL Jobs for January ==

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

== PostgreSQL Local ==

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

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/

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

Hitoshi Harada reviewed Magnus Hagander's patch to add RADIUS
authentication.

Hitoshi Harada reviewed the Typed Table patch. It needs work.

Gregory Stark reviewed the patch to speed up CREATE DATABASE.

Brad T. Sliger reviewed the patch to pretty-print trigger functions.
The first patch no longer applied cleanly, but the second one did.

Kevin Grittner reviewed the patch to allow substring and replace to
get and set bit values.

Jeff Davis reviewed Joachim Wieland's patch to fix listen and notify
behavior when the queue is full.

Michael Meskes reviewed Zoltan Boszormenyi's patch to add out-of-scope
cursor support in ECPG native mode. There were some issues with the
header files. Zoltan Boszormenyi fixed this in a later patch.

Jaime Casanova reviewed Zoltan Boszormenyi's lock_timeout GUC patch
and pronounced it "Ready for Committer."

Pavel Stehule reviewed Hitoshi Harada's new patch to add more frame
options to windowing functions and pronounced it "Ready for
Committer."

Andreas Freund reviewed Gregory Stark's patch to speed up CREATE
DATABASE.

David Wheeler reviewed Pavel Stehule's patch to add a listagg()
aggregate.
== Applied Patches ==

Teodor Sigaev committed:

- In pgsql/src/backend/access/gin/ginscan.c, fix incorrect comparison
of scan key in GIN. Per report from Vyacheslav Kalinin.

Tom Lane committed:

- In pgsql/src/backend/optimizer/plan/subselect.c, fix an oversight in
convert_EXISTS_sublink_to_join: we can't convert an EXISTS that
contains a WITH clause. This would usually lead to a "could not
find CTE" error later in planning, because the WITH wouldn't get
processed at all. Noted while playing with an example from Ken
Marshall.

- Add "USING expressions" option to plpgsql's OPEN cursor FOR EXECUTE.
This is the last EXECUTE-like plpgsql statement that was missing the
capability of inserting parameter values via USING. Pavel Stehule,
reviewed by Itagaki Takahiro.

- Add pg_table_size() and pg_indexes_size() to provide more
user-friendly wrappers around the pg_relation_size() function.
Bernd Helmle, reviewed by Greg Smith.

- In pgsql/src/backend/optimizer/util/clauses.c, fix thinko in my
recent change to put an explicit argisrow field in NullTest: when
the planner splits apart a ROW(...) IS NULL test, the argisrow
values of the component tests have to be determined from the
component field types, not copied from the original NullTest (in
which argisrow is surely true).

- When doing a parallel restore, we must guard against out-of-range
dependency dump IDs, because the array we're using is sized
according to the highest dump ID actually defined in the archive
file. In a partial dump there could be references to higher dump
IDs that weren't dumped. Treat these the same as references to
in-range IDs that weren't dumped. (The whole thing is a bit scary
because the missing objects might have been part of dependency
chains, which we won't know about. Not much we can do though ---
throwing an error is probably overreaction.) Also, reject parallel
restore with pre-1.8 archive version (made by pre-8.0 pg_dump). In
these old versions the dependency entries are OIDs, not dump IDs,
and we don't have enough information to interpret them. Per bug
#5288 from Jon Erdman.

- In pgsql/src/backend/Makefile, well, the systemtap guys moved the
goalposts again: with the latest version, we *must* generate
probes.o or the dtrace probes don't work. Revert our workaround for
their previous bug. Details at
https://bugzilla.redhat.com/show_bug.cgi?id=557266

- In pgsql/src/backend/utils/adt/dbsize.c, insert CHECK_FOR_INTERRUPTS
calls into loops in dbsize.c, to ensure that the various
disk-size-reporting functions will respond to query cancel
reasonably promptly even in very large databases. Per report from
Kevin Grittner.

- Fix assorted core dumps and Assert failures that could occur during
AbortTransaction or AbortSubTransaction, when trying to clean up
after an error that prevented (sub)transaction start from
completing: 1. access to TopTransactionResourceOwner that might not
exist 2. assert failure in AtEOXact_GUC, if AtStart_GUC not called
yet 3. assert failure or core dump in AfterTriggerEndSubXact, if
AfterTriggerBeginSubXact not called yet. Per testing by injecting
elog(ERROR) at successive steps in StartTransaction and
StartSubTransaction. It's not clear whether all of these cases
could really occur in the field, but at least one of them is easily
exposed by simple stress testing, as per my accidental discovery
yesterday.

- Apply Tcl_Init() to the "hold" interpreter created by pltcl. You
might think this is unnecessary since that interpreter is never used
to run code --- but it turns out that's wrong. As of Tcl 8.5, the
"clock" command (alone among builtin Tcl commands) is partially
implemented by loaded-on-demand Tcl code, which means that it fails
if there's not unknown-command support, and also that it's
impossible to run it directly in a safe interpreter. The way they
get around the latter is that Tcl_CreateSlave() automatically sets
up an alias command that forwards any execution of "clock" in a safe
slave interpreter to its parent interpreter. Thus, when attempting
to execute "clock" in trusted pltcl, the command actually executes
in the "hold" interpreter, where it will fail if unknown-command
support hasn't been introduced by sourcing the standard init.tcl
script, which is done by Tcl_Init(). (This is a pretty dubious
design decision on the Tcl boys' part, if you ask me ... but they
didn't.) Back-patch all the way. It's not clear that anyone would
try to use ancient versions of pltcl with a recent Tcl, but it's not
clear they wouldn't, either. Also add a regression test using
"clock", in branches that have regression test support for pltcl.
Per recent trouble report from Kyle Bateman.

Peter Eisentraut committed:

- In pgsql/src/backend/nodes/read.c, cast slightly abused enum to int,
so that GCC 4.5 won't warn about switch cases that are not part of
the enum's definition.

- In pgsql/src/backend/commands/typecmds.c, before attempting to
create a composite type, check whether a type of that name already
exists, so we'd get an error message about a "type" instead of about
a "relation", because the composite type code shares code with
relation creation.

- Fix several oversights in previous commit - attribute options patch.
I failed to 'cvs add' the new files and also neglected to bump
catversion.

- Fix mismatched tags introduced by attribute options patch. Thanks
to Devrim GUNDUZ for the report.

- Add user-specific .pg_service.conf file. This extends the existing
pg_service.conf facility to first look for a service definition file
in the user's home directory.

- PL/Python DO handler. Also cleaned up some redundancies between the
primary error messages and the error context in PL/Python. Hannu
Valtonen.

Magnus Hagander committed:

- Add pg_stat_reset_shared('bgwriter') to reset the cluster-wide
shared statistics of the bgwriter. Greg Smith.

- In pgsql/doc/src/sgml/backup.sgml, add missing closing tag.

Robert Haas committed:

- In pgsql/doc/src/sgml/libpq.sgml, reformat documentation of libpq
escaping functions. Modify the "Escaping Strings for Inclusion in
SQL Commands" section to use a <variablelist> as the preceding and
following sections do, and merge the "Escaping Binary Strings for
Inclusion in SQL Commands" section into it. This changes only the
formatting of these sections, not the content. It is intended to
lay the groundwork for a follow-on patch to add some new escaping
functions, but it makes sense to commit this first, for clarity.

- In pgsql/doc/src/sgml/plperl.sgml, fix doc build, which was broken
by PL/perl utility functions patch.

- Add new escaping functions PQescapeLiteral and PQescapeIdentifier.
PQescapeLiteral is similar to PQescapeStringConn, but it relieves
the caller of the need to know how large the output buffer should
be, and it provides the appropriate quoting (in addition to escaping
special characers within the string). PQescapeIdentifier provides
similar functionality for escaping identifiers. Per recent
discussion with Tom Lane.

- In pgsql/src/interfaces/libpq/fe-exec.c, fix unsafe loop test, and
declare as_ident as bool rather than int.

- Replace ALTER TABLE ... SET STATISTICS DISTINCT with a more general
mechanism. Attributes can now have options, just as relations and
tablespaces do, and the reloptions code is used to parse, validate,
and store them. For simplicity and because these options are not
performance critical, we store them in a separate cache rather than
the main relcache. Thanks to Alex Hunsaker for the review.

Andrew Dunstan committed:

- Add utility functions to PLPerl: quote_literal, quote_nullable,
quote_ident, encode_bytea, decode_bytea, looks_like_number,
encode_array_literal, encode_array_constructor. Split SPI.xs into
two - SPI.xs now contains only SPI functions. Remainder are in new
Util.xs. Some more code and documentation cleanup along the way, as
well as adding some CVS markers to files missing them. Original
patch from Tim Bunce, with a little editing from me.

- In pgsql/src/tools/msvc/Mkvcbuild.pm, build plperl's new Util.xs

- In pgsql/src/tools/msvc/Mkvcbuild.pm, fix typo in Mkvcbuild.pl

Heikki Linnakangas committed:

- Rethink the way walreceiver is linked into the backend. Instead than
shoving walreceiver as whole into a dynamically loaded module, split
the libpq-specific parts of it into dynamically loaded module and
keep the rest in the main backend binary. Although Tom fixed the
Windows compilation problems with the old walreceiver module
already, this is a cleaner division of labour and makes the code
more readable. There's also the prospect of adding new transport
methods as pluggable modules in the future, which this patch makes
easier, though for now the API between libpqwalreceiver and
walreceiver process should be considered private. The
libpq-specific module is now in
src/backend/replication/libpqwalreceiver, and the part linked with
postgres binary is in src/backend/replication/walreceiver.c.

- In pgsql/src/tools/msvc/Mkvcbuild.pm, adjust MSVC build script too,
now that the walreceiver dynamic module has been renamed.

- Adjust windows makefiles too, now that the walreceiver dynamic
module has been renamed.

- In pgsql/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c,
add missing "!= NULL", for the sake of consistency. Fujii Masao

- Now that much of walreceiver has been pulled back into the postgres
binary, revert PGDLLIMPORT decoration of global variables. I'm not
sure if there's any real harm from unnecessary PGDLLIMPORTs, but
these are all internal variables that external modules really
shouldn't be messing with. ThisTimeLineID still needs PGDLLIMPORT.

- Write a WAL record whenever we perform an operation without
WAL-logging that would've been WAL-logged if archiving was enabled.
If we encounter such records in archive recovery anyway, we know
that some data is missing from the log. A WARNING is emitted in that
case. Original patch by Fujii Masao, with changes by me.

- In pgsql/src/backend/replication/libpqwalreceiver/Makefile, fix
bogus subdir setting. Again. I must've unfixed it by accident while
moving files around.

- In pgsql/src/backend/replication/walsender.c, fix bogus comments.

Simon Riggs committed:

- In pgsql/src/backend/storage/ipc/procarray.c, better internal
documentation of locking for Hot Standby conflict resolution.
Discuss the reasons for the lock type we hold on ProcArrayLock while
deriving the conflict list. Cover the idea of false positive
conflicts and seemingly strange effects on snapshot derivation.

- In pgsql/src/backend/tcop/postgres.c, add missing flag reset to
ensure subsequent manual cancelation gives correct reason.

- In Hot Standby, Startup process sets SIGALRM when waiting for buffer
pin. If woken by alarm we send SIGUSR1 to all backends requesting
that they check to see if they are blocking Startup process. If so,
they throw ERROR/FATAL as for other conflict resolutions. Deadlock
stop gap removed. max_standby_delay = -1 option removed to prevent
deadlock. Review by Andres Freund and Hiroyuki Yamada.

- In pgsql/src/backend/tcop/postgres.c, add explanatory detail to Hot
Standby cancelation error messages with errdetail(). Add errhint()
to suggest retry in certain cases.

ITAGAKI Takahiro committed:

- Adjust psql to use pg_get_triggerdef(pretty=true) to remove extra
()'s from description of triggers with WHEN clause. Thanks to Brad
T. Sliger for the review.

Michael Meskes committed:

- Applied patch by Zoltan Boszormenyi to fix problem in auto-prepare
mode if the connection is closed and re-opened and the previously
prepared query is issued again.

- In pgsql/src/interfaces/ecpg/ecpglib/prepare.c, make argument const
char * as per ITAGAKI Takahiro's suggestion.

Bruce Momjian committed:

- In pgsql/doc/src/sgml/docguide.sgml, document that the HTML
documentation must be built before the PDF documentation.

- Revert mention that HTML documentation has to be built first. Now
require gmake 3.79.1 or later to build source/sgml.

Joe Conway committed:

- In pgsql/contrib/dblink/dblink.c, rewrite dblink_record_internal()
and dblink_fetch() to use a tuplestore (SFRM_Materialize mode) to
return tuples. Since we don't return from the dblink function in
tuplestore mode, release the PGresult with a PG_CATCH block on
error. Also rearrange to share the same code to materialize the
tuplestore. Patch by Takahiro Itagaki.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

ITAGAKI Takahiro sent in another revision of the SQL syntax patch for
partitioning.

Per review, Leonardo Francalanci sent in another revision of the patch
to allow substring() and replace() to get and set bit values.

Robert Haas sent in two more revisions of the patch to add quoting
behavior to psql variables.

ITAGAKI Takahiro sent in another revision of the pretty-printed
trigger patch for psql per feedback from Brad T. Sliger.

Arnaud Betremieux sent in a patch atop the listen/notify patch for
"full queue" which adds a send_notify SQL function that calls the
Async_Notify C function.

Zoltan Boszormenyi sent in another revision of the ECPG patch to add
out-of-scope cursor support in native mode per review by Michael
Meskes.

ITAGAKI Takahiro sent in another revision of the patch to fix
auto-prepare. Zoltan Boszormenyi sent another patch in response.

Hitoshi Harada sent in another revision of the patch to make ROWS and
RANGE more capable in windowing functions.

David Christensen sent in two revisions of a patch for psql which
pulls up context-specific help when people type in certain
mysql-centric commands.

Gregory Stark sent in another revision of the patch to speed up
CREATE DATABASE.

Simon Riggs sent in two revisions of a patch to fix a problem where a
hot standby node can freeze when the startup process calls
LockBufferForCleanup(), the second per review by Hiroyuki Yamada.

Magnus Hagander sent in a patch to sync the git repo with CVS.

Zoltan Boszormenyi sent in another revision of the lock_timeout GUC
patch.

Heikki Linnakangas sent in another streaming replication patch.

Leonardo Francalanci sent in another revision of the patch to let
substring/replace get and set bit values. Kevin Grittner reviewed and
sent in an updated patch.

Kevin Grittner sent in another revision of the SIREAD locks patch for
truly serializable transaction isolation.

Pavel Stehule sent in two more revisions of the psql variable quoting
patch.

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

Heikki Linnakangas sent in a patch intended to fix streaming
replication on Win32.

Fujii Masao sent in a patch to help streaming replication deal with a
"disk full" condition on the primary.

Peter Eisentraut sent in a patch intended to make the error message
for duplicate keys more specific in the primary key case.

Joachim Wieland sent in another revision of the patch to fix the case
when queue is full on LISTEN/NOTIFY.

Alex Hunsaker sent in a patch to fix some minor items atop Tim Bunce's
patch to fix PL/Perl.

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

David Christensen sent in a patch to add a regschema OID type. This
would be castable.

Bernd Helmle sent in another revision of the patch to fix multiply
inherited column names in ALTER TABLE...RENAME.

Hitoshi Harada sent in another revision of the patch to expand
windowing functions along the lines of ROWS and RANGE.

Robert Haas sent in a patch to add hash details to EXPLAIN.

Teodor Sigaev sent in another revision of the patch to add red-black
trees to GIN.

Magnus Hagander sent in another revision of the patch to add RADIUS
authentication.

KaiGai Kohei sent in two patches to refactor the ALTER TABLE privilege
checks.

Magnus Hagander sent in two revisions of a patch to add functions
which can reset a single statistics counter.