== PostgreSQL Weekly News - March 04 2012 ==

== PostgreSQL Weekly News - March 04 2012 ==

PostgreSQL Security Releases 9.1.3, 9.0.7, 8.4.11 and 8.3.18 released.
Upgrade immediately if the vulnerabilities they fix could affect you,
or at the next scheduled opportunity if they do not.
http://www.postgresql.org/about/news/1377/

== PostgreSQL Product News ==

pgAdmin III v1.14.2, a GUI administration tool for PostgreSQL, released.
http://www.pgadmin.org/development/changelog.php

== PostgreSQL Jobs for March ==

http://archives.postgresql.org/pgsql-jobs/2012-03/threads.php

== PostgreSQL Local ==

PGDay Austin 2012 will be held March 28.
http://www.postgresql.org/about/event/1379/

PGDay DC 2012 will be held on March 30.
http://pgday.bwpug.org

PGDay NYC will be held April 2, 2012 at Lighthouse International in
New York City.
http://pgday.nycpug.org

PGCon 2012 will be held 17-18 May 2012, in Ottawa at the University of
Ottawa. It will be preceded by two days of tutorials on 15-16 May 2012.
http://www.pgcon.org/2012/

PGDay France will be in Lyon on June 7, 2012.
http://www.pgday.fr

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

== Reviews ==

== Applied Patches ==

Tom Lane pushed:

- Remove arbitrary limitation on length of common name in SSL
certificates. Both libpq and the backend would truncate a common
name extracted from a certificate at 32 bytes. Replace that
fixed-size buffer with dynamically allocated string so that there is
no hard limit. While at it, remove the code for extracting peer_dn,
which we weren't using for anything; and don't bother to store
peer_cn longer than we need it in libpq. This limit was not so
terribly unreasonable when the code was written, because we weren't
using the result for anything critical, just logging it. But now
that there are options for checking the common name against the
server host name (in libpq) or using it as the user's name (in the
server), this could result in undesirable failures. In the worst
case it even seems possible to spoof a server name or user name, if
the correct name is exactly 32 bytes and the attacker can persuade a
trusted CA to issue a certificate in which that string is a prefix
of the certificate's common name. (To exploit this for a server
name, he'd also have to send the connection astray via phony DNS
data or some such.) The case that this is a realistic security
threat is a bit thin, but nonetheless we'll treat it as one.
Back-patch to 8.4. Older releases contain the faulty code, but it's
not a security problem because the common name wasn't used for
anything interesting. Reported and patched by Heikki Linnakangas
Security: CVE-2012-0867
http://git.postgresql.org/pg/commitdiff/077711c2e3e86384d19d833233bd35e05b921cfc

- Convert newlines to spaces in names written in pg_dump comments.
pg_dump was incautious about sanitizing object names that are
emitted within SQL comments in its output script. A name containing
a newline would at least render the script syntactically incorrect.
Maliciously crafted object names could present a SQL injection risk
when the script is reloaded. Reported by Heikki Linnakangas, patch
by Robert Haas Security: CVE-2012-0868
http://git.postgresql.org/pg/commitdiff/89e0bac86dbca40dfc321926205f2a90d3da5437

- Require execute permission on the trigger function for CREATE
TRIGGER. This check was overlooked when we added function execute
permissions to the system years ago. For an ordinary trigger
function it's not a big deal, since trigger functions execute with
the permissions of the table owner, so they couldn't do anything the
user issuing the CREATE TRIGGER couldn't have done anyway. However,
if a trigger function is SECURITY DEFINER, that is not the case.
The lack of checking would allow another user to install it on his
own table and then invoke it with, essentially, forged input data;
which the trigger function is unlikely to realize, so it might do
something undesirable, for instance insert false entries in an audit
log table. Reported by Dinesh Kumar, patch by Robert Haas Security:
CVE-2012-0866
http://git.postgresql.org/pg/commitdiff/891e6e7bfd9bb72687522af08c18689f795cb60a

- Last-minute release note updates. Security: CVE-2012-0866,
CVE-2012-0867, CVE-2012-0868
http://git.postgresql.org/pg/commitdiff/b2ce60703ab431a1d6c10f50587ea5f5e984af2e

- Fix thinko in new match_join_clauses_to_index() logic. We don't
need to constrain the other side of an indexable join clause to not
be below an outer join; an example here is SELECT FROM t1 LEFT JOIN
t2 ON t1.a = t2.b LEFT JOIN t3 ON t2.c = t3.d; We can consider an
inner indexscan on t3.d using c = d as indexqual, even though t2.c
is potentially nulled by a previous outer join. The comparable
logic in orindxpath.c has always worked that way, but I was being
overly cautious here.
http://git.postgresql.org/pg/commitdiff/0140a11b9ba5b22e1e4807e178bca770d46c3e28

- Move CRC tables to libpgport, and provide them in a separate include
file. This makes it much more convenient to build tools for
Postgres that are separately compiled and require a matching CRC
implementation. To prevent multiple copies of the CRC polynomial
tables being introduced into the postgres binaries, they are now
included in the static library libpgport that is mainly meant for
replacement system functions. That seems like a bit of a kludge,
but there's no better place. This cleans up building of the tools
pg_controldata and pg_resetxlog, which previously had to build their
own copies of pg_crc.o. In the future, external programs that need
access to the CRC tables can include the tables directly from the
new header file pg_crc_tables.h. Daniel Farina, reviewed by Abhijit
Menon-Sen and Tom Lane
http://git.postgresql.org/pg/commitdiff/5c02a00d440b90ead12658ce6ec9f4eee95dd0a3

- Fix MSVC builds for previous patch's addition of a src/port file.
(And why in the world is this OBJS list not being scraped from the
corresponding Makefile?)
http://git.postgresql.org/pg/commitdiff/8cae5810ebaaabb54171d9953bdd9cc802f0d135

- Simplify references to backslash-doubling in func.sgml. Several
places were still written as though standard_conforming_strings
didn't exist, much less be the default. Now that it is on by
default, we can simplify the text and just insert occasional notes
suggesting that you might have to think harder if it's turned off.
Per discussion of a suggestion from Hannes Frederic Sowa.
Back-patch to 9.1 where standard_conforming_strings was made the
default.
http://git.postgresql.org/pg/commitdiff/a5c1a1969dd838189e5cc936c15cb40e13fb6d68

- Allow child-relation entries to be made in ec_has_const
EquivalenceClasses. This fixes an oversight in commit
11cad29c91524aac1d0b61e0ea0357398ab79bf8, which introduced
MergeAppend plans. Before that happened, we never particularly
cared about the sort ordering of scans of inheritance child
relations, since appending their outputs together would destroy any
ordering anyway. But now it's important to be able to match child
relation sort orderings to those of the surrounding query. The
original coding of add_child_rel_equivalences skipped ec_has_const
EquivalenceClasses, on the originally-correct grounds that adding
child expressions to them was useless. The effect of this is that
when a parent variable is equated to a constant, we can't recognize
that index columns on the equivalent child variables are not
sort-significant; that is, we can't recognize that a child index on,
say, (x, y) is able to generate output in "ORDER BY y" order when
there is a clause "WHERE x = constant". Adding child expressions to
the (x, constant) EquivalenceClass fixes this, without any downside
that I can see other than a few more planner cycles expended on such
queries. Per recent gripe from Robert McGehee. Back-patch to 9.1
where MergeAppend was introduced.
http://git.postgresql.org/pg/commitdiff/44634e474fcb9dcd92b16fe3a0fb1d8a91e69353

- Collect and use element-frequency statistics for arrays. This patch
improves selectivity estimation for the array <@, &&, and @>
(containment and overlaps) operators. It enables collection of
statistics about individual array element values by ANALYZE, and
introduces operator-specific estimators that use these stats. In
addition, ScalarArrayOpExpr constructs of the forms "const = ANY/ALL
(array_column)" and "const <> ANY/ALL (array_column)" are estimated
by treating them as variants of the containment operators. Since we
still collect scalar-style stats about the array values as a whole,
the pg_stats view is expanded to show both these stats and the
array-style stats in separate columns. This creates an incompatible
change in how stats for tsvector columns are displayed in pg_stats:
the stats about lexemes are now displayed in the array-related
columns instead of the original scalar-related columns. There are a
few loose ends here, notably that it'd be nice to be able to
suppress either the scalar-style stats or the array-element stats
for columns for which they're not useful. But the patch is in good
enough shape to commit for wider testing. Alexander Korotkov,
reviewed by Noah Misch and Nathan Boley
http://git.postgresql.org/pg/commitdiff/0e5e167aaea4ceb355a6e20eec96c4f7d05527ab

- Improve histogram-filling loop in new compute_array_stats() code.
Do "frac" arithmetic in int64 to prevent overflow with large
statistics targets, and improve the comments so people have some
chance of understanding how it works. Alexander Korotkov and Tom
Lane
http://git.postgresql.org/pg/commitdiff/4fb694aebc524f2085152d8c98a85e01ef6136f4

- Remove useless "rough estimate" path from mcelem_array_contained_selec.
The code in this function that tried to cope with a missing count
histogram was quite ineffective for anything except a perfectly flat
distribution. Furthermore, since we were already punting for
missing MCELEM slot, it's rather useless to sweat over missing
DECHIST: there are no cases where ANALYZE will create the first but
not the second. So just simplify the code by punting rather than
pretending we can do something useful.
http://git.postgresql.org/pg/commitdiff/e2eed7891008cbf2b7d3868b3d77751b33ed09ad

- Rewrite GiST support code for rangetypes. This patch installs
significantly smarter penalty and picksplit functions for ranges,
making GiST indexes for them smaller and faster to search. There is
no on-disk format change, so no catversion bump, but you'd need to
REINDEX to get the benefits for any existing index. Alexander
Korotkov, reviewed by Jeff Davis
http://git.postgresql.org/pg/commitdiff/80da9e68fdd70b796b3a7de3821589513596c0f7

Peter Eisentraut pushed:

- Call check_keywords.pl in maintainer-check. For that purpose, have
check_keywords.pl print errors to stderr and return a useful exit
status.
http://git.postgresql.org/pg/commitdiff/9bf8603c7a9153cada7e32eb0cf7ac1feb1d3b56

- Add const qualifiers where they are accidentally cast away. This
only produces warnings under -Wcast-qual, but it's more correct and
consistent in any case.
http://git.postgresql.org/pg/commitdiff/973e9fb294dc05a384ecae7623923ae53cb81806

- psql: Improve error display for psql -f -. Running "psql -f -" used
to print psql:<stdin>:1: ERROR: blah but that got broken between
8.4 and 9.0 (commit b291c0fba83a1e93868e2f69c03be195d620f30c), and
now it printed psql:-:1: ERROR: blah This reverts to the old
behavior and cleans up some code that was left dead or useless by
the mentioned commit.
http://git.postgresql.org/pg/commitdiff/89c2f573a392e3995fffc619d4faed23f8649269

- Don't link pg_isolation_regress with libpq. It's not necessary and
can only create confusion about which libpq installation should be
used. Also remove some dead code from the makefile that was
apparently copied from elsewhere.
http://git.postgresql.org/pg/commitdiff/36a1a8c33d0d400b246dec8395990725b98801b7

- Small possible clarification in pg_basebackup reference page. The
<literal> markup is not visible as distinct on man pages, which
creates a bit of confusion when looking at the documentation of the
pg_basebackup -l option. Rather than reinventing the entire font
system for man pages to remedy this, just put some quotes around
this particular case, which should also help in other output
formats.
http://git.postgresql.org/pg/commitdiff/bc8765e91c743d87f5658387b41e3a61cde54116

- ecpg: Clean up some const usage
http://git.postgresql.org/pg/commitdiff/d41f510c807ce8b12c572196e2ae8f3817ac253a

- Add COLLATION FOR expression. reviewed by Jaime Casanova
http://git.postgresql.org/pg/commitdiff/6688d2878e516314418274ee95c5c30412351933

- Re-add "make check" target in src/test/isolation/Makefile. This
effectively reverts 7886cc73ad12fb9b5a729b6c8152f11a309f5d65, which
was done under the impression that isolationtester needs libpq,
which it no longer does (and never really did).
http://git.postgresql.org/pg/commitdiff/8e5f4300fdcadac1bfd72a7d1a0225030226c800

- Fix incorrect uses of gzFile. gzFile is already a pointer, so code
like: gzFile *handle = gzopen(...) is wrong. This used to pass
silently because gzFile used to be defined as void*, and you can
assign a void* to a void**. But somewhere between zlib versions
1.2.3.4 and 1.2.6, the definition of gzFile was changed to struct
gzFile_s *, and with that new definition this usage causes compiler
warnings. So remove all those extra pointer decorations. There is
a related issue in pg_backup_archiver.h, where FILE *FH;
/* General purpose file handle */ is used throughout pg_dump as
sometimes a real FILE* and sometimes a gzFile handle, which also
causes warnings now. This is not yet fixed here, because it might
need more code restructuring.
http://git.postgresql.org/pg/commitdiff/d923125b77c5d698bb8107a533a21627582baa43

- Allow CREATE TABLE (LIKE ...) from composite type. The only reason
this didn't work before was that parserOpenTable() rejects composite
types. So use relation_openrv() directly and manually do the
errposition() setup that parserOpenTable() does.
http://git.postgresql.org/pg/commitdiff/b59ca98209d45f5689fe9de22a7429d4cf09d40c

Alvaro Herrera pushed:

- ALTER TABLE: skip FK validation when it's safe to do so. We already
skip rewriting the table in these cases, but we still force a whole
table scan to validate the data. This can be skipped, and thus we
can make the whole ALTER TABLE operation just do some catalog
touches instead of scanning the table, when these two conditions
hold: (a) Old and new pg_constraint.conpfeqop match exactly. This
is actually stronger than needed; we could loosen things by way of
operator families, but it'd require a lot more effort. (b) The
functions, if any, implementing a cast from the foreign type to the
primary opcintype are the same. For this purpose, we can consider a
binary coercion equivalent to an exact type match. When the
opcintype is polymorphic, require that the old and new foreign types
match exactly. (Since ri_triggers.c does use the executor, the
stronger check for polymorphic types is no mere future-proofing.
However, no core type exercises its necessity.) Author: Noah Misch
Committer's note: catalog version bumped due to change of the
Constraint node. I can't actually find any way to have such a node
in a stored rule, but given that we have "out" support for them,
better be safe.
http://git.postgresql.org/pg/commitdiff/cb3a7c2b95a28e57c56562d48d2a3aa5eeb7fa29

- psql: when tab-completing, use quotes on file names that need them.
psql backslash commands that deal with file or directory names
require quotes around those that have spaces, single quotes, or
backslashes. However, tab-completing such names does not provide
said quotes, and is thus almost useless with them. This patch fixes
the problem by having a wrapper function around
rl_filename_completion_function that dequotes on input and quotes on
output. This eases dealing with such names. Author: Noah Misch
http://git.postgresql.org/pg/commitdiff/41e3c94cac0e68257126b2d264dc5e877e892490

- Fix typo in comment. Haifeng Liu
http://git.postgresql.org/pg/commitdiff/58e9f974dcfae7c4c445631afad47d80deb83160

- Remove TOAST table from pg_database. The only toastable column now
is datacl, but we don't really support long ACLs anyway. The TOAST
table should have been removed when the pg_db_role_setting catalog
was introduced in commit 2eda8dfb52ed9962920282d8384da8bb4c22514d,
but I forgot to do that. Per -hackers discussion on March 2011.
http://git.postgresql.org/pg/commitdiff/3433c6ba002f711a60352c3518f30cda73d06087

Heikki Linnakangas pushed:

- Correctly detect SSI conflicts of prepared transactions after crash.
A prepared transaction can get new conflicts in and out after
preparing, so we cannot rely on the in- and out-flags stored in the
statefile at prepare- time. As a quick fix, make the conservative
assumption that after a restart, all prepared transactions are
considered to have both in- and out-conflicts. That can lead to
unnecessary rollbacks after a crash, but that shouldn't be a big
problem in practice; you don't want prepared transactions to hang
around for a long time anyway. Dan Ports
http://git.postgresql.org/pg/commitdiff/d6a7271958e61fe8029087a34483437292f41f6f

- When a GiST page is split during index build, it might not have a
buffer. Previously it was thought that it's impossible as the code
stands, because insertions create buffers as tuples are cascaded
downwards, and index split also creaters buffers eagerly for all
halves. But the example from Jay Levitt demonstrates that it can
happen, when the root page is split. It's in fact OK if the buffer
doesn't exist, so we just need to remove the sanity check. In fact,
we've been discussing the possibility of destroying empty buffers to
conserve memory, which would render the sanity check completely
useless anyway. Fix by Alexander Korotkov
http://git.postgresql.org/pg/commitdiff/2502f45979fca76a6b19a07c98d7a41737a3dc7b

Magnus Hagander pushed:

- Add a rule to optionally build docs with the stylesheet from the
website. For those of us who prefer the formatting of the docs
using the website stylesheets. Use "make STYLE=website draft" (for
example) to use. The stylesheet itself is referenced directly to
the website, so there is currently no copy of it stored in the
source repository. Thus, docs built with it will only look correct
if the browser can access the website when viewing them.
http://git.postgresql.org/pg/commitdiff/8efb0bc57eb350bd991fd32c96e38a13bfe7f120

- Add function pg_xlog_location_diff to help comparisons. Comparing
two xlog locations are useful for example when calculating
replication lag. Euler Taveira de Oliveira, reviewed by Fujii
Masao, and some cleanups from me
http://git.postgresql.org/pg/commitdiff/bc5ac3686580079bd4ea26bf027178786d77a9ee

- More carefully validate xlog location string inputs. Now that we
have validate_xlog_location, call it from the previously existing
functions taking xlog locatoins as a string input. Suggested by
Fujii Masao
http://git.postgresql.org/pg/commitdiff/141b89826ddb82b3afa7cf5e048d28a3d8e1c45c

Andrew Dunstan pushed:

- Provide environment overrides for psql file locations. PSQL_HISTORY
provides an alternative for the command history file, and PSQLRC
provides an alternative location for the .psqlrc file. Review by
David Wheeler.
http://git.postgresql.org/pg/commitdiff/34c978442c55dd13a3a8c6b90fd4380dad02f3da

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Dimitri Fontaine, Andres Freund and Thom Brown sent in parts of the
grand unified patch to implement command triggers.

Marti Raudsepp sent in a patch to make heap_open()/heap_close()
consistently, which kicked off a short discussion about an implicit
behavior of heap_open() that things depend on, including third-party
things.

Josh Kupershmidt sent in a patch to fix a misleading error message
from connectMaintenanceDatabase().

Kyotaro HORIGUCHI sent in another revision of the patch to create in
libpq and use in dblink, a more space-efficient tuple storage
mechanism.

Robert Haas sent in a WIP patch to set a new buffer flag
BM_BGWRITER_CLEANED to every buffer the background writer cleans.

Bruce Momjian sent in two revisions of a patch to clarify pg_upgrade
--logfile along with its documentation.

Alexander Shulgin sent in another revision of the patch to add URI
connection string support to libpq.

Alvaro Herrera and Pavel Stehule traded revisions of the patch to add
CHECK FUNCTION functionality to PostgreSQL.

Peter Geoghegan sent in another revision of the patch to normalize
pg_stat_statements based in part on a review by Daniel Farina.

Simon Riggs sent in four more revisions of the patch to add some hints
to COPY for large bulk loads.

Shigeru HANADA sent in another revision of the patch to add a
PostgreSQL FDW.

Marti Raudsepp sent in another revision of the patch to enable caching
the results of stable expressions with constant arguments.

Gilles Darold sent in two patches which embody two different
approaches to implement a pg_is_in_exclusive_backup() function.

Robert Haas sent in a patch to add sortsupport to TEXT types.

Yeb Havinga sent in another revision of the patch to add a GUC named
sepgsql.client_label.

Simon Riggs sent in three more revisions of a patch to make TRUNCATE
more MVCC-safe.

Daniele Varazzo sent in a patch to improves the array selectivity
estimation for = ANY and <> ALL, hence for the IN/NOT IN operators.

Jeff Janes sent in a patch to fix some cases of how sort memory grows.