== Postgres Weekly News - January 13 2008 ==

== Postgres Weekly News - January 13 2008 ==

Security releases 8.3 RC1, 8.2.6, 8.1.11, 8.0.15, 7.4.19 and 7.3.21
are out. Upgrade ASAP!

ASQL, a Data Warehouse engine atop Postgres, has been announced.
http://www.analyticsql.org/index.html

Devrim GUNDUZ and Darcy Buskermolen have created the PostgreSQL RPM
Buildfarm, which will provide native binaries for many RPM-based
distributions.

Devrim GUNDUZ and Darcy Buskermolen have created a yum repository for
Postgres.
http://yum.pgsqlrpms.org

7.3.21 is the final release in the community 7.3 series. Upgrade from
7.3 as soon as you can.

== Postgres Product News ==

Open Technology Group now has PostGIS and UMN Mapserver Training.
http://www.otg-nc.com

PGCluster 1.7.0rc8 released.
http://pgfoundry.org/projects/pgcluster/

pg_proboscis and pg_typical 0.9.2 released.
http://pgfoundry.org/projects/python/

PL/Lua 0.2 released.
http://pgfoundry.org/projects/pllua/

PL/Proxy 2.0.4 released.
http://pgfoundry.org/projects/plproxy/

== Postgres Jobs for January ==

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

== Postgres Local ==

What's new in PostgreSQL 8.3 - Breakfast with Bruce Momjian
24th January 2008 - London. Email below to get in.
info.emea <AT> enterprisedb.com

PGCon 2008 will be May 20-23 in Ottawa - now accepting proposals.
http://www.pgcon.org/2008/papers.php

The BSD and PostgreSQL teams share a developer room at FOSDEM 2008 in
Brussels February 23-34, 2008. If you want to give a talk or help
managing the dev room, contact fosdem(at)pgug(dot)eu(dot) For more information,
see:
http://fosdem.org/2008/schedule/devroom/bsdpostgresql

PostgreSQL Conference East '08 talks are March 29 and 30 at the
University of Maryland, College Park. The Call for Papers is open.
http://www.postgresqlconference.org/

== Postgres in the News ==

Planet PostgreSQL: http://www.planetpostgresql.org/

General Bits, Archives and occasional new articles:
http://www.varlena.com/GeneralBits/

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

Michael Meskes committed:

- In ecpg, fixed lexer to parse C quotes correctly.

- In ecpg, changed prototype for ECPGdo because some compilers don't
like int/enum aliasing in there.

Magnus Hagander committed:

- In pgsql/src/include/port/win32.h, don't enforce 32-bit time_t for
FRONTEND apps. Fixes standalone builds of libpq in both 32 and
64-bit. Per gripe from Hiroshi Saito.

- In pgsql/src/interfaces/libpq/win32.mak, generate and include
manifest in standalone libpq build on Windows. Hiroshi Saito

Alvaro Herrera committed:

- In pgsql/doc/src/sgml/protocol.sgml, add index entry for
frontend-backend protocol.

Bruce Momjian committed:

- In pgsql/src/tools/RELEASE_CHANGES, mention use of
src/tools/major_release_split for creating back-branch release
notes.

- Add URLs to two excellent web pages about SSL API and certificate
usage.

- In pgsql/doc/src/sgml/info.sgml, remove mention of /contrib README
files from documentation because files are moved to SGML.

- In pgsql/src/backend/utils/misc/postgresql.conf.sample,
vacuum_cost_limit has a minimum value of 1, not zero; update
postgresql.conf comment to match.

- Add to TODO: "Add ability to trigger on TRUNCATE."

- In pgsql/doc/src/sgml/datatype.sgml, update documentation for
tsvector duplicate elimination.

Neil Conway committed:

- In pgsql/src/backend/nodes/outfuncs.c, fix a minor bug in outfuncs
support for SetOp: dupOperators is an array of Oid, and therefore
should use the "%u" escape sequence rather than "%d".

- In pgsql/src/backend/nodes/outfuncs.c, fix an omission in the
outfuncs.c support for Agg nodes: the grpColIdx and grpOperators
fields were not emitted by _outAgg().

- In pgsql/src/backend/utils/adt/xml.c, minor perf tweak for
_SPI_strdup(): if we're going to call strlen() anyway, it is faster
to memcpy() than to strcpy().

- Fix two places in xml.c that neglected to check the return values of
SPI_prepare() and SPI_cursor_open(), to silence a Coverity warning.

Tom Lane committed:

- Fix assorted security-grade bugs in the regex engine. All of these
problems are shared with Tcl, since it's their code to begin with,
and the patches have been copied from Tcl 8.5.0. Problems:
CVE-2007-4769: Inadequate check on the range of backref numbers
allows crash due to out-of-bounds read. CVE-2007-4772: Infinite
loop in regex optimizer for pattern '($|^)*'. CVE-2007-6067: Very
slow optimizer cleanup for regex with a large NFA representation, as
well as crash if we encounter an out-of-memory condition during NFA
construction. Part of the response to CVE-2007-6067 is to put a
limit on the number of states in the NFA representation of a regex.
This seems needed even though the within-the-code problems have been
corrected, since otherwise the code could try to use very large
amounts of memory for a suitably-crafted regex, leading to potential
DOS by driving the system into swap, activating a kernel OOM killer,
etc. Although there are certainly plenty of ways to drive the
system into effective DOS with poorly-written SQL queries, these
problems seem worth treating as security issues because many
applications might accept regex search patterns from untrustworthy
sources. Thanks to Will Drewry of Google for reporting these
problems. Patches by Will Drewry and Tom Lane. Security:
CVE-2007-4769, CVE-2007-4772, CVE-2007-6067

- Make standard maintenance operations (including VACUUM, ANALYZE,
REINDEX, and CLUSTER) execute as the table owner rather than the
calling user, using the same privilege-switching mechanism already
used for SECURITY DEFINER functions. The purpose of this change is
to ensure that user-defined functions used in index definitions
cannot acquire the privileges of a superuser account that is
performing routine maintenance. While a function used in an index
is supposed to be IMMUTABLE and thus not able to do anything very
interesting, there are several easy ways around that restriction;
and even if we could plug them all, there would remain a risk of
reading sensitive information and broadcasting it through a covert
channel such as CPU usage. To prevent bypassing this security
measure, execution of SET SESSION AUTHORIZATION and SET ROLE is now
forbidden within a SECURITY DEFINER context. Thanks to Itagaki
Takahiro for reporting this vulnerability. Security: CVE-2007-6600

- The original patch to disallow non-passworded connections to
non-superusers failed to cover all the ways in which a connection
can be initiated in dblink. Plug the remaining holes. Also,
disallow transient connections in functions for which that feature
makes no sense (because they are only sensible as part of a sequence
of operations on the same connection). Joe Conway Security:
CVE-2007-6601

- Update release notes for security releases. Security:
CVE-2007-4769, CVE-2007-4772, CVE-2007-6067, CVE-2007-6600,
CVE-2007-6601

- Stamp release 8.3RC1. Security: CVE-2007-4769, CVE-2007-4772,
CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 8.2.6. Security: CVE-2007-4769, CVE-2007-4772,
CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 8.1.11. Security: CVE-2007-4769, CVE-2007-4772,
CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 8.0.15. Security: CVE-2007-4769, CVE-2007-4772,
CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 7.4.19. Security: CVE-2007-4769, CVE-2007-4772,
CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- Stamp release 7.3.21. Security: CVE-2007-4769, CVE-2007-4772,
CVE-2007-6067, CVE-2007-6600, CVE-2007-6601

- In pgsql/src/backend/utils/adt/tsquery.c, remove unnecessary comma
in enum definition ... some C compilers don't like that. Per report
from J6M.

- informix.c was violating the coding rule about not including any
system headers before c.h. Per report from J6M.

- lmgr.c:DescribeLockTag was never taught about virtual xids, per Greg
Stark. Also a couple of minor tweaks to try to future-proof the
code a bit better against future locktag additions.

- Fix some planner issues found while investigating Kevin Grittner's
report of poorer planning in 8.3 than 8.2: 1. After pushing a
constant across an outer join --- ie, given "a LEFT JOIN b ON (a.x =
b.y) WHERE a.x = 42", we can deduce that b.y is sort of equal to 42,
in the sense that we needn't fetch any b rows where it isn't 42 ---
loop to see if any additional deductions can be made. Previous
releases did that by recursing, but I had mistakenly thought that
this was no longer necessary given the EquivalenceClass machinery.
2. Allow pushing constants across outer join conditions even if the
condition is outerjoin_delayed due to a lower outer join. This is
safe as long as the condition is strict and we re-test it at the
upper join. 3. Keep the outer-join clause even if we successfully
push a constant across it. This is *necessary* in the
outerjoin_delayed case, but even in the simple case, it seems better
to do this to ensure that the join search order heuristics will
consider the join as reasonable to make. Mark such a clause as
having selectivity 1.0, though, since it's not going to eliminate
very many rows after application of the constant condition. 4.
Tweak have_relevant_eclass_joinclause to report that two relations
are joinable when they have vars that are equated to the same
constant. We won't actually generate any joinclause from such an
EquivalenceClass, but again it seems that in such a case it's a good
idea to consider the join as worth costing out. 5. Fix a bug in
select_mergejoin_clauses that was exposed by these changes: we have
to reject candidate mergejoin clauses if either side was equated to
a constant, because we can't construct a canonical pathkey list for
such a clause. This is an implementation restriction that might be
worth fixing someday, but it doesn't seem critical to get it done
for 8.3.

- Fix a bug in 8.2.x that was exposed while investigating Kevin
Grittner's report of poor planning in 8.3: it's unsafe to push a
constant across an outer join when the outer-join condition is
delayed by lower outer joins, unless we recheck the outer-join
condition at the upper outer join. 8.2.x doesn't really have the
ability to tell whether this is the case or not, but fortunately it
doesn't matter --- it seems most desirable to keep the join
condition whether it's entirely redundant or not. However, it's
usually mostly redundant, so force its selectivity to 1.0. It might
be a good idea to back-patch this into 8.1 as well, but I'll refrain
until/unless there's evidence that 8.1 actually fails on any cases
that this would fix.

- Fix CREATE INDEX CONCURRENTLY to not deadlock against an automatic
or manual VACUUM that is blocked waiting to get lock on the table
being indexed. Per report and fix suggestion from Greg Stark.

- In pgsql/src/backend/utils/mb/mbutils.c, remove incorrect (and
ill-advised anyway) pfree's in pg_convert_from and pg_convert_to.
Per bug #3866 from Andrew Gilligan.

- Restructure the shutdown procedure for the archiver process to allow
it to finish archiving everything (when there's no error), and to
eliminate various hazards as best we can. This fixes a previous 8.3
patch that caused the postmaster to kill and then restart the
archiver during shutdown (!?). The new behavior is that the
archiver is allowed to run unmolested until the bgwriter has exited;
then it is sent SIGUSR2 to tell it to do a final archiving cycle and
quit. We only SIGQUIT the archiver if we want a panic stop; this is
important since SIGQUIT will also be sent to any active
archive_command. The postmaster also now doesn't SIGQUIT the stats
collector until the bgwriter is done, since the bgwriter can send
stats messages in 8.3. The postmaster will not exit until both the
archiver and stats collector are gone; this provides some defense
(not too bulletproof) against conflicting archiver or stats
collector processes being started by a new postmaster instance. We
continue the prior practice that the archiver will check for
postmaster death immediately before issuing any archive_command;
that gives some additional protection against conflicting archivers.
Also, modify the archiver process to notice SIGTERM and refuse to
issue any more archive commands if it gets it. The postmaster
doesn't ever send it SIGTERM; we assume that any such signal came
from init and is a notice of impending whole-system shutdown. In
this situation it seems imprudent to try to start new archive
commands --- if they aren't extremely quick they're likely to get
SIGKILL'd by init. All per discussion.

- Fix a conceptual error in my patch of 2007-10-26 that avoided
considering clauseless joins of relations that have unexploited join
clauses. Rather than looking at every other base relation in the
query, the correct thing is to examine the other relations in the
"initial_rels" list of the current make_rel_from_joinlist()
invocation, because those are what we actually have the ability to
join against. This might be a subset of the whole query in cases
where join_collapse_limit or from_collapse_limit or full joins have
prevented merging the whole query into a single join problem. This
is a bit untidy because we have to pass those rels down through a
new PlannerInfo field, but it's necessary. Per bug #3865 from Oleg
Kharin.

- Fix an old error in clause_selectivity: the default selectivity
estimate for unhandled clause types ought to be 0.5, not 1.0. I
fear I introduced this silliness due to misreading the intent of the
very-poorly-structured code that was there when we inherited the
file from Berkeley. The lack of sanity in this behavior was exposed
by an example from Sim Zacks. (Arguably this is a bug fix and
should be back-patched, but I'm a bit hesitant to introduce a
possible planner behavior change in the back branches; it might
detune queries that worked acceptably in the past.) While at it,
make estimation for DistinctExpr do something marginally realistic,
rather than just defaulting.

- The original implementation of polymorphic aggregates didn't really
get the checking of argument compatibility right; although the
problem is only exposed with multiple-input aggregates in which some
arguments are polymorphic and some are not. Per bug #3852 from
Sokolov Yura.

- Fix logical errors in constraint exclusion: we cannot assume that a
CHECK constraint yields TRUE for every row of its table, only that
it does not yield FALSE (a NULL result isn't disallowed). This
breaks a couple of implications that would be true in two-valued
logic. I had put in one such mistake in an 8.2.5 patch: foo IS NULL
doesn't refute a strict operator on foo. But there was another in
the original 8.2 release: NOT foo doesn't refute an expression whose
truth would imply the truth of foo. Per report from Rajesh Kumar
Mallah. To preserve the ability to do constraint exclusion with one
partition holding NULL values, extend
relation_excluded_by_constraints() to check for attnotnull flags,
and add col IS NOT NULL expressions to the set of constraints we
hope to refute.

- In pgsql/src/backend/utils/adt/xml.c, it turns out the
LIBXML_TEST_VERSION macro calls xmlInitParser(). Therefore we must
xmlCleanupParser(), or we risk leaving behind dangling pointers to
whatever memory context is current when xml_init() is called. This
seems to fix bug #3860, though we might still want the more invasive
solution being worked on by Alvaro.

- In pgsql/doc/src/sgml/ddl.sgml, add note pointing out that read-only
commands no longer consume command IDs.

- Fix a regression test that fails if default_text_search_config isn't
'english'.

- Fix CREATE INDEX CONCURRENTLY so that it won't use synchronized scan
for its second pass over the table. It has to start at block zero,
else the "merge join" logic for detecting which TIDs are already in
the index doesn't work. Hence, extend heapam.c's API so that
callers can enable or disable syncscan. (I put in an option to
disable buffer access strategy, too, just in case somebody needs
it.) Per report from Hannes Dorbath.

- Use an indexscan not a heapscan to search pg_index in
get_pkey_attnames. Noted while looking for heapscans that might
need to start from block zero.

- Fix pgstat_heap() to not be broken by syncscans starting from a
block higher than zero. Same problem as just detected in CREATE
INDEX CONCURRENTLY.

== Rejected Patches (for now) ==

Erik Rijkers's doc patch for textsearch.sgml on grounds of not
including the tables referred to in the patch.

Alvaro Herrera's patch to fix BUG #3860. The fix should use the
palloc infrastructure instead.

== Pending Patches ==

Tom Lane sent in 3 revisions of a patch intended to fix archiver
shutdown behavior.