== PostgreSQL Weekly News - August 17 2008 ==

== PostgreSQL Weekly News - August 17 2008 ==

== PostgreSQL Jobs for August ==

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

== PostgreSQL Local ==

The Prato Linux User Group will be having PostgreSQL talks in
September. The schedule in Italian is:
http://www.prato.linux.it/serate_a_tema_2008

PGCon Brazil 2008 will be on September 26-27 at Unicamp in Campinas.
http://pgcon.postgresql.org.br/index.en.html

PgDay.fr will be October 4 in Toulouse. The Call for Papers is open:
http://www.postgresqlfr.org/?q=node/1686
Registration:
http://www.pgday.fr/doku.php/inscription

PostgreSQL Conference West 2008 will be October 10-12 at Portland
State University in Portland, Oregon.
http://www.postgresqlconference.org/
Talk submission at:
http://www.postgresqlconference.org/west08/talk_submission/

Sponsor the European PGDay!
http://www.pgday.org/en/sponsors/campaign

The Call for Papers for European PGDay has begun.
http://www.pgday.org/en/call4papers

PGDay.(IT|EU) 2008 will be October 17 and 18 in Prato.
http://www.pgday.org/it/

== PostgreSQL in the News ==

Planet PostgreSQL: http://www.planetpostgresql.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 ==

Heikki Linnakangas committed:

- Introduce the concept of relation forks. An smgr relation can now
consist of multiple forks, and each fork can be created and grown
separately. The bulk of this patch is about changing the smgr API
to include an extra ForkNumber argument in every smgr function.
Also, smgrscheduleunlink and smgrdounlink no longer implicitly call
smgrclose, because other forks might still exist after unlinking
one. The callers of those functions have been modified to call
smgrclose instead. This patch in itself doesn't have any
user-visible effect, but provides the infrastructure needed for
upcoming patches. The additional forks envisioned are a rewritten
FSM implementation that doesn't rely on a fixed-size shared memory
block, and a visibility map to allow skipping portions of a table in
VACUUM that have no dead tuples.

- Relation forks patch requires a catversion bump due to changes in
the format of some WAL records, and two-phase state files, which I
forgot.

- pg_buffercache needs to be taught about relation forks, as Greg
Stark pointed out.

- Fix pull_up_simple_union_all to copy all rtable entries from child
subquery to parent, not only those with RangeTblRefs. We need them
in ExecCheckRTPerms. Report by Brendan O'Shea. Back-patch to 8.2,
where pull_up_simple_union_all was introduced.

Bruce Momjian committed:

- Add to TODO: "Add 'hostgss' pg_hba.conf option to allow GSS
link-level encryption."

- Add URL for TODO: "Improve ability to modify views via ALTER TABLE."

- Fix version warning bug in recently applied adjustments to psql
startup. Gregory Stark

- Add new SQL training web site to FAQ:

- Update Russian FAQ. Viktor Vislobokov.

- In pgsql/src/interfaces/libpq/bcc32.mak, synchronize Borland libpq
makefile to match MSVC. Backpatch to 8.3.X.

- Add to TODO: "Prevent query cancel packets from being replayed by an
attacker, especially when using SSL."

- Add URL for TODO: "Improve ability to modify views via ALTER TABLE."

- Update instructions on generating TODO.html

Peter Eisentraut committed:

- Remove TODO item, "Allow XML to accept more liberal DOCTYPE
specifications." Everything works correctly, per today's email to
-general.

Alvaro Herrera committed:

- Have autovacuum consider processing TOAST tables separately from
their main tables. This requires vacuum() to accept processing a
toast table standalone, so there's a user-visible change in that
it's now possible (for a superuser) to execute "VACUUM
pg_toast.pg_toast_XXX".

Tom Lane committed:

- Implement SEMI and ANTI joins in the planner and executor.
(Semijoins replace the old JOIN_IN code, but antijoins are new
functionality.) Teach the planner to convert appropriate EXISTS and
NOT EXISTS subqueries into semi and anti joins respectively. Also,
LEFT JOINs with suitable upper-level IS NULL filters are recognized
as being anti joins. Unify the InClauseInfo and OuterJoinInfo
infrastructure into "SpecialJoinInfo". With that change, it becomes
possible to associate a SpecialJoinInfo with every join attempt,
which permits some cleanup of join selectivity estimation. That
needs to be taken much further than this patch does, but the next
step is to change the API for oprjoin selectivity functions, which
seems like material for a separate patch. So for the moment the
output size estimates for semi and especially anti joins are quite
bogus.

- Performance fix for new anti-join code in nodeMergejoin.c: after
finding a match in antijoin mode, we should advance to next outer
tuple not next inner. We know we don't want to return this outer
tuple, and there is no point in advancing over matching inner tuples
now, because we'd just have to do it again if the next outer tuple
has the same merge key. This makes a noticeable difference if there
are lots of duplicate keys in both inputs. Similarly, after finding
a match in semijoin mode, arrange to advance to the next outer tuple
after returning the current match; or immediately, if it fails the
extra quals. The rationale is the same. (This is a performance bug
in existing releases; perhaps worth back-patching? The planner
tries to avoid using mergejoin with lots of duplicates, so it may
not be a big issue in practice.) Nestloop and hash got this right to
start with, but I made some cosmetic adjustments there to make the
corresponding bits of logic look more similar.

- Clean up the loose ends in selectivity estimation left by my patch
for semi and anti joins. To do this, pass the SpecialJoinInfo
struct for the current join as an additional optional argument to
operator join selectivity estimation functions. This allows the
estimator to tell not only what kind of join is being formed, but
which variable is on which side of the join; a requirement long
recognized but not dealt with till now. This also leaves the door
open for future improvements in the estimators, such as accounting
for the null-insertion effects of lower outer joins. I didn't do
anything about that in the current patch but the information is in
principle deducible from what's passed. The patch also clarifies
the definition of join selectivity for semi/anti joins: it's the
fraction of the left input that has (at least one) match in the
right input. This allows getting rid of some very fuzzy thinking
that I had committed in the original 7.4-era IN-optimization patch.
There's probably room to estimate this better than the present patch
does, but at least we know what to estimate. Since I had to touch
CREATE OPERATOR anyway to allow a variant signature for join
estimator functions, I took the opportunity to add a couple of
additional checks that were missing, per my recent message to
-hackers:
* Check that estimator functions return float8; Require execute
* permission at the time of CREATE OPERATOR on the
operator's function as well as the estimator functions;
* Require ownership of any pre-existing operator that's modified by
the command. I also moved the lookup of the functions out of
OperatorCreate() and into operatorcmds.c, since that seemed more
consistent with most of the other catalog object creation processes,
eg CREATE TYPE.

- Fix a couple of places where psql might fail to report a suitable
error if PQexec returns NULL. These don't seem significant enough
to be worth back-patching, but they ought to get fixed ...

- In pgsql/src/bin/pg_dump/pg_backup_db.c, fix pg_dump/pg_restore's
ExecuteSqlCommand() to behave suitably if PQexec returns NULL
instead of a PGresult. The former coding would fail, which is OK,
but it neglected to give you the PQerrorMessage that might tell you
why. In the oldest branches, there was another problem: it'd
sometimes report PQerrorMessage from the wrong connection.

- Improve sublink pullup code to handle ANY/EXISTS sublinks that are
at top level of a JOIN/ON clause, not only at top level of WHERE.
(However, we can't do this in an outer join's ON clause, unless the
ANY/EXISTS refers only to the nullable side of the outer join, so
that it can effectively be pushed down into the nullable side.) Per
request from Kevin Grittner. In passing, fix a bug in the initial
implementation of EXISTS pullup: it would Assert if the EXIST's
WHERE clause used a join alias variable. Since we haven't yet
flattened join aliases when this transformation happens, it's
necessary to include join relids in the computed set of RHS relids.

- In pgsql/src/backend/optimizer/plan/subselect.c, remove prohibition
against SubLinks in the WHERE clause of an EXISTS subquery that
we're considering pulling up. I hadn't wanted to think through
whether that could work during the first pass at this stuff.
However, on closer inspection it seems to be safe enough.

- In pgsql/src/backend/optimizer/path/joinrels.c, add some defenses
against constant-FALSE outer join conditions. Since
eval_const_expressions will generally throw away anything that's
ANDed with constant FALSE, what we're left with given an example
like select * from tenk1 a where (unique1,0) in (select unique2,1
from tenk1 b); is a cartesian product computation, which is really
not acceptable. This is a regression in CVS HEAD compared to
previous releases, which were able to notice the impossible join
condition in this case --- though not in some related cases that are
also improved by this patch, such as select * from tenk1 a left join
tenk1 b on (a.unique1=b.unique2 and 0=1); Fix by skipping evaluation
of the appropriate side of the outer join in cases where it's
demonstrably unnecessary.

Magnus Hagander committed:

- Make the temporary directory for pgstat files configurable by the
GUC variable stats_temp_directory, instead of requiring the admin to
mount/symlink the pg_stat_tmp directory manually. For now the
config variable is PGC_POSTMASTER. Room for further improvment that
would allow it to be changed on-the-fly. Original idea by Euler
Taveira de Oliveira.

- In pgsql/src/tools/msvc/Solution.pm, probes.h is generated from
probes.d, not pg_trace.d.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Zdenek Kotala sent in a patch to introduce a page layout footprint in
aid of his in-place upgrades work.

David Wheeler sent in another patch to clean up the citext contrib
module.

Zdenek Kotala sent in a WIP patch for his new page API in aid of his
in-place upgrades work.

ITAGAKI Takahiro sent in another revision of his "Copy storage
parameters" patch.

Gregory Stark sent in a patch to fix a bug in psql where old variables
were being used for the new database connection after \c.

ITAGAKI Takahiro sent in a patch to add duration option (-T) to
pgbench instead of number of transactions (-t). -t and -T are mutually
exclusive.

Jan Urbanski sent in two more revisions of his oprrest patch for text
search.

Dmitri Koterov sent in a patch to add three new functions to
contrib/intarray: int_array_append_aggregate(int[]), which quickly
merges arrays, _int_group_count_sort(int[], bool), a frequency-based
sort, and bidx(int[], int), a binary search in a sorted array.

Martin Pihlak sent in a patch that implements plan invalidation on
function DROP, REPLACE and ALTER.

Ryan Bradetich sent in a patch to implement unsigned integer types.

Magnus Hagander sent in a patch to do pg_hba.conf and postgresql.conf
parsing in the postmaster and matching in the backend. Per
discussion.