== PostgreSQL Weekly News - November 16 2008 ==

== PostgreSQL Weekly News - November 16 2008 ==

FOSDEM is soon in Brussels. See below for details.

== PostgreSQL Product News ==

MicroOLAP Database Designer 1.2.6 for PostgreSQL released.
http://microolap.com/products/database/postgresql-designer/

PGUnit released.
http://en.dklab.ru/lib/dklab_pgunit/

PyReplica 1.0.5 beta released.
http://pgfoundry.org/projects/pyreplica/

== PostgreSQL Jobs for November ==

http://archives.postgresql.org/pgsql-jobs/2008-11/threads.php

== PostgreSQL Local ==

PgDay Rio de la Plata is looking for sponsors. Interested parties
should contact admin AT postgres-arg DOT org.

Dickson Guedes is looking for volunteers to help with a PgMeeting in
Florianópolis. Write to guediz AT gmail DOT com if you want to help.

David Fetter will be speaking at PGDay Rio de la Plata Buenos Aires
November 22.
http://pgday.postgres-arg.org/

David Fetter will be speaking at OSDC Sydney December 3-5.
http://www.osdc.com.au/2008/index.html

FOSDEM 2009 will take place february 7-8 2009 in Brussels, Belgium.
PostgreSQL will share a booth with the BSD groups and have a developer
room. Submit your 20-minute, 45 minute or lightning talks to
fosdem (at) postgresql (dot) org.

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

== Applied Patches ==

Tom Lane committed:

- Fix old bug in contrib/sslinfo: X509_NAME_to_text freed the
BIO_s_mem buffer it was using too soon. In a situation where
pg_do_encoding_conversion is a no-op, this led to garbage data
returned. In HEAD, also modify the code that's ensuring null
termination to make it a tad more obvious what's happening.

- pg_do_encoding_conversion cannot return NULL (at least not unless
the input is NULL), so remove some useless tests for the case.

- In pgsql/src/backend/utils/adt/xml.c, fix bugs in sqlchar_to_unicode
and unicode_to_sqlchar: both were measuring the length of a UTF8
character with pg_mblen (wrong if DB encoding isn't UTF8), and the
latter was blithely assuming that a static buffer would somehow
revert to all zeroes for each use.

- Add support for input and output of interval values formatted per
ISO 8601; specifically, we can input either the "format with
designators" or the "alternative format", and we can output the
former when IntervalStyle is set to iso_8601. Ron Mayer.

- In pgsql/src/backend/utils/mb/mbutils.c, add an explicit caution
about how to use pg_do_encoding_conversion with non-null-terminated
input. Per discussion with ITAGAKI Takahiro.

- Fix sloppy omission of now-required #include's.

- Get rid of adjust_appendrel_attr_needed(), which has been broken
ever since we extended the appendrel mechanism to support UNION ALL
optimization. The reason nobody noticed was that we are not
actually using attr_needed data for appendrel children; hence it
seems more reasonable to rip it out than fix it. Back-patch to 8.2
because an Assert failure is possible in corner cases. Per
examination of an example from Jim Nasby. In HEAD, also get rid of
AppendRelInfo.col_mappings, which is quite inadequate to represent
UNION ALL situations; depend entirely on translated_vars instead.

- In pgsql/src/backend/optimizer/prep/prepjointree.c, ensure that the
phrels sets of PlaceHolderVars appearing in an AppendRelInfo's
translated_vars list get updated when pulling up an appendrel
member. It's not clear that this really matters at present, since
relatively little gets done with the outputs of an appendrel child
relation; but it probably will come back to bite us sometime if we
leave them with the wrong values.

- Clean up the ancient decision to show only two fractional-seconds
digits in "postgres_verbose" intervalstyle, and the equally
arbitrary decision to show at least two fractional-seconds digits in
most other datetime display styles. This results in some minor
changes in the expected regression test outputs. Also, coalesce a
lot of repetitive code in datetime.c into subroutines, for clarity
and ease of maintenance. In particular this roughly halves the
number of #ifdef HAVE_INT64_TIMESTAMP segments. Ron Mayer, with
some additional kibitzing from Tom Lane

- In pgsql/src/include/catalog/pg_proc.h, if we're going to use a SQL
function for this, at least make it schema-proof.

- In pgsql/src/backend/optimizer/util/predtest.c, install a limit on
the number of branches we will process in AND, OR, or equivalent
clauses: if there are too many (more than 100) just exit without
proving anything. This ensures that we don't spend O(N^2) time
trying (and most likely failing) to prove anything about very long
IN lists and similar cases. Also, install a couple of
CHECK_FOR_INTERRUPTS calls to ensure that a long proof attempt can
be interrupted. Per gripe from Sergey Konoplev. Back-patch the
whole patch to 8.2 and just the CHECK_FOR_INTERRUPTS addition to
8.1. (The rest of the patch doesn't apply cleanly, and since 8.1
doesn't show the complained-of behavior anyway, it doesn't seem
necessary to work

- In pgsql/src/backend/optimizer/util/predtest.c, arrange to cache the
results of looking up a btree predicate proof comparison operator.
The result depends only on the two input operators and the proof
direction (imply or refute), so it's easy to cache. This provides a
very large savings in cases such as Sergey Konoplev's long
NOT-IN-list example, where predtest spends all its time repeatedly
figuring out that the same pair of operators cannot be used to prove
anything. (But of course the O(N^2) behavior still catches up with
you eventually.) I'm not convinced it buys a whole lot when
constraint_exclusion isn't turned on, but it's not a lot of added
code so we might as well cache all the time.

- In pgsql/src/include/utils/pg_crc.h, PGDLLIMPORT-ize the global
variables referenced in pg_crc.h. I think this will fix current
mingw buildfarm failures for pg_trgm.

- Prevent synchronous scan during GIN index build, because GIN is
optimized for inserting tuples in increasing TID order. It's not
clear whether this fully explains Ivan Sergio Borgonovo's complaint,
but simple testing confirms that a scan that doesn't start at block
0 can slow GIN build by a factor of three or four. Backpatch to
8.3. Sync scan didn't exist before that.

- In pgsql/src/timezone/pgtz.c, suppress leap-second-aware timezones
in the output of pg_tzenumerate_next, and thereby in the
pg_timezone_names view. Although we allow such zones to be used in
certain limited contexts like AT TIME ZONE, we don't allow them in
SET TIME ZONE, and bug #4528 shows that they're more likely to
confuse users than do anything useful. So hide 'em. (Note that we
don't even generate these zones when installing our own timezone
database. But they are likely to be present when using a
system-provided database.)

- In pgsql/doc/src/sgml/func.sgml, marginal editorial improvements for
array_agg patch documentation.

- In pgsql/src/backend/utils/adt/array_userfuncs.c, minor code clarity
improvements in array_agg functions, and add a comment about how
this is playing fast and loose with the type system.

- Implement the basic form of UNNEST, ie unnest(anyarray) returns
setof anyelement. This lacks the WITH ORDINALITY option, as well as
the multiple input arrays option added in the most recent SQL specs.
But it's still a pretty useful subset of the spec's functionality,
and it is enough to allow obsoleting contrib/intagg.

- Actually, instead of whining about how type internal might not
safely store a pointer, why don't we just fix that. Every known use
of "internal" really means a pointer anyway.

- In pgsql/src/interfaces/ecpg/preproc/Makefile, improve comment about
when to clean generated files.

- In pgsql/src/backend/commands/conversioncmds.c, make CREATE
CONVERSION verify that a putative encoding conversion function
returns VOID. This is the last of the easy fixes I recommended in
11870(dot)1218838360(at)sss(dot)pgh(dot)pa(dot)us --- the others got done awhile ago
but I forgot about this one.

- In pgsql/src/interfaces/ecpg/preproc/preproc.y, ecpg's preproc.y is
now generated as needed, so remove from CVS.

- In CREATE AGGREGATE, allow the transition datatype to be "internal",
but only if the user is superuser. This makes available to
extension modules the same sort of trick being practiced by
array_agg(). The reason for the superuser restriction is that you
could crash the system by connecting up an incompatible pair of
internal-using functions as an aggregate. It shouldn't interfere
with any legitimate use, since you'd have to be superuser to create
the internal-using transition and final functions anyway.

- Reduce contrib/intagg to a thin wrapper around the new core
functions array_agg() and unnest(). We could drop it entirely in
the future, but let's keep it for a release or two as a
compatibility assist.

- In pgsql/src/include/utils/pg_crc.h, second try at fixing DLLIMPORT
problem for pg_crc.h on Cygwin.

- In pgsql/src/bin/pg_resetxlog/pg_resetxlog.c, one more hack to see
if we can get the cygwin machines building again. This continues
the saga of trying to get PGDLLIMPORT to work in pg_crc.h in both
backend and frontend environments.

- In pgsql/doc/src/sgml/libpq.sgml, document a gotcha that happens on
Windows when using libpq's new event procedure support: it's
possible to get confused because exported procedures have two
different addresses. Per Andrew Chernow.

- Make SELECT FOR UPDATE/SHARE work on inheritance trees, by having
the plan return the tableoid as well as the ctid for any FOR UPDATE
targets that have child tables. All child tables are listed in the
ExecRowMark list, but the executor just skips the ones that didn't
produce the current row. Curiously, this longstanding restriction
doesn't seem to have been documented anywhere; so no doc changes.

- In pgsql/src/interfaces/ecpg/preproc/Makefile, add missing
dependencies to preproc.y build rule.

- Modify UPDATE/DELETE WHERE CURRENT OF to use the FOR UPDATE
infrastructure to locate the target row, if the cursor was declared
with FOR UPDATE or FOR SHARE. This approach is more flexible and
reliable than digging through the plan tree; for instance it can
cope with join cursors. But we still provide the old code for use
with non-FOR-UPDATE cursors. Per gripe from Robert Haas.

Alvaro Herrera committed:

- Add a --locale switch to createdb, to ease the creation of databases
with different locales. This is just syntactical sweetener over
--lc-collate and --lc-ctype. Per discussion. While at it, properly
document --lc-ctype and --lc-collate in SGML docs, which apparently
were forgotten (or purposefully ommited?) when they were created.

- Fix a case of string building in text search.

- In pgsql/src/include/utils/pg_crc.h, update URL to Ross Williams'
CRC paper. Per note from Devrim GUNDUZ.

- Replace the usage of heap_addheader to create pg_attribute tuples
with regular heap_form_tuple. Since this removes the last remaining
caller of heap_addheader, remove it. Extracted from the column
privileges patch from Stephen Frost, with further code cleanups by
me.

Heikki Linnakangas committed:

- In pgsql/src/backend/utils/adt/formatting.c, fix 'Q' format char
parsing in the new to_timestamp() code. Used to crash.

- Change error messages to print the physical path, like
"base/11517/3767_fsm", instead of symbolic names like
"1663/11517/3767/1", per Alvaro's suggestion. I didn't change the
messages in the higher-level index, heap and FSM routines, though,
where the fork is implicit.

- In pgsql/src/backend/postmaster/autovacuum.c, fix off-by-one error
in autovacuum shmem struct sizing. This could lead to autovacuum
worker sending SIGUSR1 signal to wrong process, per Zou Yong's
report. Backpatch to 8.3.

- In pgsql/src/backend/storage/smgr/md.c, fix oversight in previous
error-reporting patch; mustn't pfree path string before passing it
to elog.

Michael Meskes committed:

- Replaced manually synced preproc.y by the one created by the new
script. Adapted regression test files accordingly.

- In pgsql/src/backend/parser/gram.y, do not use ICONST/SCONST in
rules other than Iconst/Sconst.

- In pgsql/src/backend/parser/gram.y, removed two non-terminals:
FloatOnly, only used by NumericOnly, instead put the FloatOnly
production into NumericOnly, and IntegerOnly, only used by
NumericOnly and one ALTER TABLE rule, replacement SignedIconst is
already used in several other places

- Updated parser file to the one generated by the latest version of
parse.[awk|pl] from the latest version of gram.y. Some small
corrections to test suite.

- Add pgsql/src/interfaces/ecpg/preproc/parse.pl script that generates
preproc.y from gram.y to CVS.

- Added files containing changes between gram.y and preproc.y.

- In ecpg, fixed test for output_filename == stdout.

- In ecpg, enable script to generate preproc.y in build process.

Magnus Hagander committed:

- In pgsql/src/bin/psql/command.c, make psql report "SSL connection
(unknown cipher)" when libpq has set up a SSL connection, but psql
is compiled without support for it. Not a really realistic
use-case, but the patch also cuts down on the number of places with
#ifdef's...

- In pgsql/doc/src/sgml/monitoring.sgml, mention the tup_fetched
column in pg_stat_database. Greg Sabino Mullane.

- Fix libpq certificate validation for SSL connections. Add config
parameter "sslverify" to control the verification. Default is to do
full verification. Clean up some old SSL code that never really
worked.

- In pgsql/src/tools/msvc/Mkvcbuild.pm, exclude contrib/intagg from
the list of MSVC project files to be generated, since it's now just
a SQL module and no C code.

Andrew Dunstan committed:

- Detect and error out on inability to get proper linkage information
required for plperl, usually due to absence of perl ExtUtils::Embed
module. Backpatch as far as 8.1.

- In pgsql/src/interfaces/ecpg/preproc/ecpg.trailer, add missing
semicolon, per gripe from Alex Hunsaker.

Peter Eisentraut committed:

- array_length() function, and for SQL compatibility also
cardinality() function as a special case. This version still has
the suspicious behavior of returning null for an empty array (rather
than zero), but this may need a wholesale revision of empty array
behavior, currently under discussion. Jim Nasby, Robert Haas, Peter
Eisentraut

- In pgsql/doc/src/sgml/dblink.sgml, add refentrytitle elements to
refentry pages. Their absence confused the man page tools somewhat.

- Remove unnecessary whitespace in refname elements. This confuses
the man page tools.

- In pgsql/doc/src/sgml/Makefile, support for man page links, if a man
page has more than one refname. We don't actually use this
anywhere, but it might come in handy for dealing with
SELECT/WITH/TABLE. It works with both the old and the new man page
target (for some value of "works").

- In pgsql/doc/src/sgml/func.sgml, since the xmlagg section was moved,
"below" is no longer accurate.

- array_agg aggregate function, as per SQL:2008, but without ORDER BY
clausea. Rearrange the documentation a bit now that array_agg and
xmlagg have similar semantics and issues. Best of Robert Haas, Jeff
Davis, Peter Eisentraut

- Set SQL man pages to be section 7 by default, and only transform
them to another section if required by the platform (instead of the
old way of building them in section "l" and always transforming them
to the platform-specific section). This speeds up the installation
on common platforms, and it avoids some funny business with the man
page tools and build process.

- In pgsql/doc/src/Makefile, since doc/src/sgml already builds the
HTML docs twice to resolve index entries, we don't have to do two
builds here as well.

- Fix crash of xmlconcat(NULL). Also backpatched to 8.3.

Teodor Sigaev committed:

- Support of multibyte encoding for pg_trgm.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

KaiGai Kohei sent in another revision of his SE-PostgreSQL patches.

ITAGAKI Takahiro sent in another revision of his auto-explain contrib
module.

Hitoshi Harada sent in two more revisions of his windowing functions
patch.

Tom Lane sent in a revised version of Simon Riggs' patch to reduce
some DDL locks to ShareLock.

KaiGai Kohei sent in another revision of his SE-PostgreSQL patches.

Hiroshi Saito sent in another revision of his locale patch.

Fujii Masao sent in another revision of his synchronous replication
patch.

Alvaro Herrera sent in a patch to update pg_filedump for CVS HEAD.

Pavel Stehule sent in a patch to add FOUND and GET DIAGNOSTICS support
to RETURN QUERY in PL/PgSQL.

Pavel Stehule sent in another revision of his GROUPING SETS patch.

Teodor Sigaev sent in another revision of his B-Tree emulation for
GIN patch, now with more data types.

Magnus Hagander sent in another revision of his usermap regex support
patch.

Heikki Linnakangas sent in another revision of his visibility
map/partial vacuum patch.

ITAGAKI Takahiro sent in another revision of his pg_stat_statements
patch.

Jeff Davis sent in another revision of the auto_explain contrib
module.

Magnus Hagander and Alex Hunsaker sent in a flock of patches to extend
and fix the SSL implementation.