== 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@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.

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