== PostgreSQL Weekly News - July 26 2009 ==

== PostgreSQL Weekly News - July 26 2009 ==

If you're running a Win32 platform, please help test a patch intended
to fix the infamous, "failed to re-attach to shared memory" problem.
Http://blog.hagander.net/archives/149-Help-us-test-a-patch-for-the-Win32-shared-memory-issue.html

== PostgreSQL Product News ==

psqlODBC 08.04.0100 released.
http://psqlodbc.projects.postgresql.org/release.html

Open Source Database Magazine kicks off with an article by Robert
Treat.
http://www.osdbzine.net

Open Source Database Podcast has started.
http://www.osdbzine.net/podcast/osdbpodcast.xml

PostGIS 1.4.0, a Geographic Information System for PostgreSQL, released.
http://postgis.refractions.net/

== PostgreSQL Jobs for July ==

http://archives.postgresql.org/pgsql-jobs/2009-07/threads.php

== PostgreSQL Local ==

OpenSQL Camp will take place August 22-23, 2009 in St. Augustin,
Germany, close to Bonn and Cologne. Deadline for the CfP is July 19,
2009. Get your proposals in!
http://opensqlcamp.org/Events/2009/Call_for_Participation

The German PostgreSQL User Group will have a dev-room at FrOSCon on
Sunday, August 23, 2009. The Call for Papers is open:
http://andreas.scherbaum.la/blog/archives/573-Call-for-Papers-fuer-den-PostgreSQL-Devroom-auf-der-FrOSCon-2009.html

The CfP for PyCon Argentina is open until June 29th, 2009 23:59 ART.
The conference itself will be in Beunos Aires on September 4-5 2009.
http://ar.pycon.org/2009/rfp/

Andreas (ads) Scherbaum will be teaching a "PostgreSQL for Corporate
Use" course at the adult education center in Magdeburg, Germany on
September 7-11, 2009. Details below:
http://andreas.scherbaum.la/blog/archives/574-PostgreSQL-als-Bildungsurlaub.html

There will be a conference in Athens, Georgia, USA on September 19,
2009. The CfP is open.
http://www.postgresqlconference.org/2009/pgday/athens

There will be a conference in Seattle, Washington, USA October 16-18,
2009. The CfP is open.
http://www.postgresqlconference.org/2009/west

PGCon Brazil will be take place October 23-24 2009 at Unicamp in
Campinas, Sao Paulo state. The CfP is open!
http://pgcon.postgresql.org.br/2009/chamadas.en.php

PGDay.EU 2009 will be at Telecom ParisTech in Paris, France on
November 6-7, 2009. The CfP is out. Submit!
http://www.pgday.eu/

JPUG 10th Anniversary Conference has started its Request for
Proposals. The conference is November 20-21, 2009 in Tokyo, Japan.
http://archives.postgresql.org/pgsql-announce/2009-05/msg00018.php

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

Peter Eisentraut committed:

- Use errcontext mechanism in PL/Python. Error messages from
PL/Python now always mention the function name in the CONTEXT:
field. This also obsoletes the few places that tried to do the same
manually. Regression test files are updated to work with Python
2.4-2.6. I don't have access to older versions right now.

- In pgsql/doc/src/sgml/Makefile, small additional clean from the
rearrangement of the HTML index build rules

- In pgsql/src/tools/editors/emacs.samples, add a further
customization to the SGML Emacs mode to prevent the use of tabs in
the documentation source.

- Remove translated FAQs. The English FAQ has been moved to the wiki,
so the translated versions should have been removed at that point as
well. The FAQ_MINGW.html should have been removed when the platform
FAQs were integrated into the documentation (or earlier). Applied
to both 8.4 and 8.5

- Change pg_listener attribute number constants to match the usual
pattern. It appears that, for no particularly good reason,
pg_listener.h deviates from the usual convention for declaring
attribute number constants. Normally, it's
#define Anum_{catalog-name}_{column-name} {attribute-number}
pg_listener.h, however substitutes a different string that is
similar, but not the same as, the column name. This change fixes
that. Author: Robert Haas

- In pgsql/doc/src/sgml/func.sgml, clarify NULLIF example. Found by
Vesa-Matti J Kari.

- Document \dg+ and \du+. The fact that \dg and \du take the + option
was missing in the documentation. Backpatched to 8.4. Author:
Andreas Wenk.

- In pgsql/src/backend/access/gist/gistsplit.c, fix the fix for the
gist error message

Alvaro Herrera committed:

- In pgsql/src/include/Makefile, install src/include/utils/fmgroids.h
on VPATH builds too. The original coding was not dealing specially
with this file being a symlink, with the end result that it was not
installed in VPATH builds. Oddly enough, the clean target does know
about it ...

Bruce Momjian committed:

- In pgsql/src/bin/pg_dump/pg_dump.c, properly restore
pg_largeobject.relfozenxid in binary upgrade mode. Backpatch to
8.4.X.

Tom Lane committed:

- Fix another semijoin-ordering bug. We already knew that we couldn't
reorder a semijoin into or out of the righthand side of another
semijoin, but actually it doesn't work to reorder it into or out of
the righthand side of a left or antijoin, either. Per bug #4906
from Mathieu Fenniak. This was sloppy thinking on my part. This
identity does work:
( A left join B on (Pab) ) semijoin C on (Pac)
==
( A semijoin C on (Pac) ) left join B on (Pab)
but I failed to see that that doesn't mean this does:
( A left join B on (Pab) ) semijoin C on (Pbc)
!=
A left join ( B semijoin C on (Pbc) ) on (Pab)

- In pgsql/src/backend/utils/mmgr/aset.c, speed up AllocSetFreeIndex,
which is a significant cost in palloc and pfree, by using a lookup
table instead of a naive shift-and-count loop. Based on code
originally posted by Sean Eron Anderson at
http://graphics.stanford.edu/%7eseander/bithacks.html. Greg Stark
did the research and benchmarking to show that this is what we
should use. Jeremy Kerr first noticed that this is a hotspot that
could be optimized, though we ended up not using his suggestion of
platform-specific bit-searching code.

- Make pg_dump/pg_restore --clean options drop large objects too. In
passing, make invocations of lo_xxx functions a bit more
schema-safe. Itagaki Takahiro

- Tweak TOAST code so that columns marked with MAIN storage strategy
are not forced out-of-line unless that is necessary to make the row
fit on a page. Previously, they were forced out-of-line if needed
to get the row down to the default target size (1/4th page). Kevin
Grittner

- Change do_tup_output() to take Datum/isnull arrays instead of a char *
array, so it doesn't go through BuildTupleFromCStrings. This is
more or less a wash for current uses, but will avoid inefficiency
for planned changes to EXPLAIN. Robert Haas.

- In pgsql/src/backend/optimizer/path/joinrels.c, fix another thinko
in join_is_legal's handling of semijoins: we have to test for the
case that the semijoin was implemented within either input by
unique-ifying its RHS before we test to see if it appears to match
the current join situation. The previous coding would select
semijoin logic in situations where we'd already unique-ified the RHS
and joined it to some unrelated relation(s), and then came to join
it to the semijoin's LHS. That still gave the right answer as far
as the semijoin itself was concerned, but would lead to incorrectly
examining only an arbitrary one of the matchable rows from the
unrelated relation(s). The cause of this thinko was incorrect
unification of the pre-8.4 logic for IN joins and OUTER joins ---
the comparable case for outer joins can be handled after making the
match test, but that's because there is nothing like the
unique-ification escape hatch for outer joins. Per bug #4934 from
Benjamin Reed.

- In a non-hashed Agg node, reset the "aggcontext" at group
boundaries, instead of individually pfree'ing pass-by-reference
transition values. This should be at least as fast as the prior
coding, and it has the major advantage of clearing out any working
data an aggregate function may have stored in or underneath the
aggcontext. This avoids memory leakage when an aggregate such as
array_agg() is used in GROUP BY mode. Per report from Chris Spotts.
Back-patch to 8.4. In principle the problem could arise in prior
versions, but since they didn't have array_agg the issue seems not
critical.

- In pgsql/src/backend/executor/execTuples.c, save a few cycles in
EXPLAIN and related commands by not bothering to form a physical
tuple in do_tup_output(). A virtual tuple is easier to set up and
also easier for most tuple receivers to process. Per my comment on
Robert Haas' recent patch in this code.

- In pgsql/src/bin/pg_dump/pg_dump.c, teach pg_dump to dump comments
attached to the columns of a composite type. Taro (Higepon) Minowa.

- Add commentary about Cygwin's broken erand48, per report from Andrew
Dunstan.

- Avoid extra system calls to block SIGPIPE if the platform provides
either sockopt(SO_NOSIGPIPE) or the MSG_NOSIGNAL flag to send(). We
assume these features are available if (1) the symbol is defined at
compile time and (2) the kernel doesn't reject the call at runtime.
It might turn out that there are some platforms where (1) and (2)
are true and yet the signal isn't really blocked, in which case
applications would die on server crash. If that sort of thing gets
reported, then we'll have to add additional defenses of some kind.
Jeremy Kerr.

- Assorted minor refactoring in EXPLAIN. This is believed to not
change the output at all, with one known exception: "Subquery Scan
foo" becomes "Subquery Scan on foo". (We can fix that if anyone
complains, but it would be a wart, because the old code was clearly
inconsistent.) The main intention is to remove duplicate coding and
provide a cleaner base for subsequent EXPLAIN patching. Robert
Haas.

- Code review for FORCE QUOTE * patch: fix error checking to consider
FORCE QUOTE * as a variety of FORCE QUOTE, and update psql
documentation to include the option. (The actual psql code doesn't
seem to need any changes.)

- Extend EXPLAIN to allow generic options to be specified. The
original syntax made it difficult to add options without making them
into reserved words. This change parenthesizes the options to avoid
that problem, and makes provision for an explicit (and perhaps
non-Boolean) value for each option. The original syntax is still
supported, but only for the two original options ANALYZE and
VERBOSE. As a test case, add a COSTS option that can suppress the
planner cost estimates. This may be useful for including EXPLAIN
output in the regression tests, which are otherwise unable to cope
with cross-platform variations in cost estimates. Robert Haas

- Experiment with using EXPLAIN COSTS OFF in regression tests. This
is a simple test to see whether COSTS OFF will help much with
getting EXPLAIN output that's sufficiently platform-independent for
use in the regression tests. The planner does have some freedom of
choice in these examples (plain via bitmap indexscan), so I'm not
sure what will happen.

- In pgsql/contrib/pg_stat_statements/pg_stat_statements.c, fix
pg_stat_statements for EXEC_BACKEND case. We should not try to load
old statistics when re-attaching to existing shared memory. Per bug
#4941. Itagaki Takahiro

- In pgsql/contrib/pg_stat_statements/pg_stat_statements.c, improve
comment, per gripe from Alvaro Herrera.

- In pgsql/src/include/storage/s_lock.h, add s_lock support for SuperH
architecture. After a patch originally submitted by Nobuhiro
Iwamatsu, but corrected (I think) to match our guidelines for safe
use of asm fragments. This should be considered untested ...

Joe Conway committed:

- Replace PLpgSQL_dstring by StringInfo. Replace redundant
PLpgSQL_dstring functionality with StringInfo. Patch by Pavel
Stehule. Review by Joe Conway.

Magnus Hagander committed:

- In pgsql/src/interfaces/ecpg/ecpglib/prepare.c, fix mismatch in
const:ness of parameters.

- Reserve the shared memory region during backend startup on Windows,
so that memory allocated by starting third party DLLs doesn't end up
conflicting with it. Hopefully this solves the long-time issue with
"could not reattach to shared memory" errors on Win32. Patch from
Tsutomu Yamada and me, based on idea from Trevor Talbot.

- Reserve the shared memory region during backend startup on Windows,
so that memory allocated by starting third party DLLs doesn't end up
conflicting with it. Hopefully this solves the long-time issue with
"could not reattach to shared memory" errors on Win32. Patch from
Tsutomu Yamada and me, based on idea from Trevor Talbot.

Andrew Dunstan committed:

- In pgsql/doc/src/sgml/install-win32.sgml, update location to
download flex for building on Windows.

- Force use of our erand48 on Cygwin.

- Allow * as parameter for FORCE QUOTE for COPY CSV. Itagaki
Takahiro.

- In pgsql/src/backend/commands/copy.c, small stylistic improvement in
recent FORCE QUOTE * code - use a bool instead of a magic value.

== Rejected Patches (for now) ==

Dean Rasheed's in revision of the deferrable unique constraints patch.
Needs work.

== Pending Patches ==

James Pye sent in a WIP patch to make PL/Python work with Python 3,
with several stylistic and functional improvements included.

Alexey Klyukin sent in two revisions of a patch to add errcontext
support to PL/Perl.

Pavel Stehule sent in two revisions of a patch intended to fix mixed
and named notation support in PL/PgSQL.

Alan Li sent in two revisions of a patch to optimize MIN()/MAX()
operations on partitioned tables.

Josh Tolley and Petr (PJMODOS) Jelinek traded patches to implement
DefaultACLs.

ITAGAKI Takahiro sent in another revision of the patch to make pgbench
multi-threaded.

Fujii Masao sent in another revision of a patch to add a non-blocking
communication between an front-end and a back-end.

Robert Haas sent in another revision of his patch to auto-generate
headers.

Pavel Stehule sent in another revision of his patch to enable
machine-readable EXPLAIN output.

Brendan Jurd sent in an updated version of the EEEE formatting patch
for to_char().

ITAGAKI Takahiro sent in another revision of his patch to mkae a more
specific error when a key is duplicated.

Bernd Helmle sent in an updated version of the patch to handle byteas
more efficiently in pg_dump.

Jaime Casanova sent in an updated patch to help infer client_encoding
from client locale.

Robert Haas sent in another revision of the machine-readable EXPLAIN
output patch.