== PostgreSQL Weekly News - September 06 2009 ==

== PostgreSQL Weekly News - September 06 2009 ==

New Survey:  Which 8.5 Alpha1 feature do you find the most
interesting?
http://www.postgresql.org/community

Registration for PostgreSQL Conference West is now open:
http://www.postgresql.us/purchase

PostgreSQL Live CD Project started a mailing list to build a live CD
with comments from community:
http://www.pglivecd.org/community.php

== PostgreSQL Product News ==

GTpB portalBase, a web plaform based on PostgreSQL, is now available
under the GPL.
http://www.gtportalbase.com/

PostgreSQL RPM Building Project another version of The PostgreSQL
Live CD, this time based on PostgreSQL 8.4.0 and CentOS 5.3:
http://yum.pgsqlrpms.org/livecd.php

AnySQL Maestro 9.8, a GUI for administration and development on
Windows, released.
http://www.sqlmaestro.com/products/anysql/maestro/

pg51g, a data diff toolkit for PostgreSQL, released.
http://pgdba.net/pg51g/

pgDesigner 1.2.11, a graphical schema design program for PostgreSQL,
released.
http://pgdesigner.sourceforge.net/en/index.html

== PostgreSQL Jobs for September ==

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

== PostgreSQL Local ==

SFPUG presents: Nathan Boley on Statistics and Postgres.
September 8, 2009.  Details including live webcast info below.
http://postgresql.meetup.com/1/calendar/11030245/

There will be a conference in Seattle, Washington, USA October 16-18,
2009.  The CfP is still 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/

OpenSQL Camp in Portland is looking for sponsors.  Make your travel plans now! :)
http://www.chesnok.com/daily/2009/07/29/opensql-camp-comes-to-portland-november-14-15-2009/

JPUG 10th Anniversary Conference is November 20-21, 2009 in Tokyo, Japan.
http://archives.postgresql.org/pgsql-announce/2009-05/msg00018.php

FOSDEM 2010 will be in Brussels on February 6-7, 2010.
http://www.fosdem.org/

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter,
Josh Berkus, and Devrim GUNDUZ.

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

Tom Lane committed:

- Track the current XID wrap limit (or more accurately, the oldest
  unfrozen XID) in checkpoint records.  This eliminates the need to
  recompute the value from scratch during database startup, which is
  one of the two remaining reasons for the flatfile code to exist.  It
  should also simplify life for hot-standby operation.  To avoid
  bloating the checkpoint records unreasonably, I switched from
  tracking the oldest database by name to tracking it by OID.  This
  turns out to save cycles in general (everywhere but the
  warning-generating paths, which we hardly care about) and also helps
  us deal with the case that the oldest database got dropped instead
  of being vacuumed.  The prior coding might go for a long time
  without updating the wrap limit in that case, which is bad because
  it might result in a lot of useless autovacuum activity.

- Change the autovacuum launcher to read pg_database directly, rather
  than via the "flat files" facility.  This requires making it enough
  like a backend to be able to run transactions; it's no longer an
  "auxiliary process" but more like the autovacuum worker processes.
  Also, its signal handling has to be brought into line with
  backends/workers.  In particular, since it now has to handle
  procsignal.c processing, the special autovac-launcher-only signal
  conditions are moved to SIGUSR2.  Alvaro Herrera, with some cleanup
  from Tom Lane.

- Move processing of startup-packet switches and GUC settings into
  InitPostgres, to fix the problem that SetClientEncoding needs to be
  done before InitializeClientEncoding, as reported by Zdenek Kotala.
  We get at least the small consolation of being able to remove the
  bizarre API detail that had InitPostgres returning whether user is a
  superuser.

- Bump catversion for flat-file-ectomy.  Also remove a missed dead
  extern declaration.

- In pgsql/src/backend/access/transam/twophase.c, actually, we need to
  bump the format identifier on twophase files because of readjustment
  of 2PC rmgr IDs for flatfile removal.

- Force VACUUM to recalculate oldestXmin even when we haven't changed
  our own database's datfrozenxid, if the current value is old enough
  to be forcing autovacuums or warning messages.  This ensures that a
  bogus value is replaced as soon as possible.  Per a comment from
  Heikki.

- Fix pg_ctl's readfile() to not go into infinite loop on an empty
  file (could happen if either postgresql.conf or postmaster.opts is
  empty).  It's been broken since the C version was written for 8.0,
  so patch all the way back.  initdb's copy of the function is broken
  in the same way, but it's less important there since the input files
  should never be empty.  Patch that in HEAD only, and also fix some
  cosmetic differences that crept into that copy of the function.  Per
  report from Corry Haines and Jeff Davis.

- Fix subquery pullup to wrap a PlaceHolderVar around the entire
  RowExpr that's generated for a whole-row Var referencing the
  subquery, when the subquery is in the nullable side of an outer
  join.  The previous coding instead put PlaceHolderVars around the
  elements of the RowExpr.  The effect was that when the outer join
  made the subquery outputs go to null, the whole-row Var produced
  ROW(NULL,NULL,...) rather than just NULL.  There are arguments afoot
  about whether those things ought to be semantically
  indistinguishable, but for the moment they are not entirely so, and
  the planner needs to take care that its machinations preserve the
  difference.  Per bug #5025.  Making this feasible required
  refactoring ResolveNew() to allow more caller control over what is
  substituted for a Var.  I chose to make ResolveNew() a wrapper
  around a new general-purpose function replace_rte_variables().  I
  also fixed the ancient bogosity that ResolveNew might fail to set a
  query's hasSubLinks field after inserting a SubLink in it.  Although
  all current callers make sure that happens anyway, we've had bugs of
  that sort before, and it seemed like a good time to install a proper
  solution.  Back-patch to 8.4.  The problem can be demonstrated clear
  back to 8.0, but the fix would be too invasive in earlier branches;
  not to mention that people may be depending on the subtly-incorrect
  behavior.  The 8.4 series is new enough that fixing this probably
  won't cause complaints, but it might in older branches.  Also, 8.4
  shows the incorrect behavior in more cases than older branches do,
  because it is able to flatten subqueries in more cases.

- In pgsql/src/bin/initdb/initdb.c, remove initdb's rather gratuitous
  check to see if the backend created a flat password file, because it
  never will anymore.  We had managed to miss this during the recent
  flat-file-ectomy because it only happens if --pwfile or --pwprompt
  is specified to initdb.  Apparently, few hackers use those.
  Reported by Erik Rijkers.

- Update time zone data files to tzdata release 2009l: DST law changes
  in Egypt, Mauritius, Bangladesh.

- Install a workaround for a longstanding gcc bug that allows SIGFPE
  traps to occur for division by zero, even though the code is
  carefully avoiding that.  All available evidence is that the only
  functions affected are int24div, int48div, and int28div, so patch
  just those three functions to include a "return" after the ereport()
  call.  Backpatch to 8.4 so that the fix can be tested in production
  builds.  For older branches our recommendation will continue to be
  to use -O1 on affected platforms (which are mostly non-mainstream
  anyway).

- Final updates of release notes for 8.4.1, 8.3.8, 8.2.14, 8.1.18,
  8.0.22, 7.4.26.

- In pgsql/src/backend/postmaster/pgstat.c, remove pgstat's
  discrimination against MsgVacuum and MsgAnalyze messages.  Formerly,
  these message types would be discarded unless there was already a
  stats hash table entry for the target table.  However, the intent of
  saving hash table space for unused tables was subverted by the fact
  that the physical I/O done by the vacuum or analyze would result in
  an immediately following tabstat message, which would create the
  hash table entry anyway.  All that we had left was surprising loss
  of statistical data, as in a recent complaint from Jaime Casanova.
  It seems unlikely that a real database would have many tables that
  go totally untouched over the long haul, so the consensus is that
  this "optimization" serves little purpose anyhow.  Remove it, and
  just create the hash table entry on demand in all cases.

- In pgsql/src/backend/Makefile, revert ill-considered restriction of
  dtrace support to Solaris only.

- In pgsql/src/backend/Makefile, put back "ifeq ($(PORTNAME),
  solaris)", this time with some documentation of why it's not as
  broken as it appears on first glance.

- In pgsql/doc/src/sgml/ref/copy.sgml, add a note warning that COPY
  BINARY is very datatype-specific.  Per a complaint from Gordon
  Shannon.

- Update the tznames reference files, and add IDT (Israel Daylight
  Time) to the Default timezone abbreviation set.  Back-port the the
  current file set to all branches that contain tznames.  This
  includes adding SGT to the Default set in pre-8.4 releases.  Joachim
  Wieland.

Alvaro Herrera committed:

- This patch removes flatfiles.c for good.  It doesn't change the
  keeping of locks in dbcommands.c and user.c, because at least some
  of them are still required.  Regarding sync commits that previously
  happen and now won't, I think the only case worth worrying about is
  the one in vacuum.c.  Do we need a ForceSyncCommit() in there?  I'm
  not sure if vacuum itself already forces sync commit.

- Remove flatfiles.c, which is now obsolete.  Recent commits have
  removed the various uses it was supporting.  It was a performance
  bottleneck, according to bug report #4919 by Lauris Ulmanis; seems
  it slowed down user creation after a billion users.

Peter Eisentraut committed:

- In pgsql/src/backend/access/gist/gist.c, improve picksplit debug
  message.  Missed this earlier because the translation site was
  broken for the 7.4 branch.

- Translation updates

Magnus Hagander committed:

- In pgsql/src/interfaces/ecpg/preproc/Makefile, revert Makefile
  modification that broke the MSVC build.

Michael Meskes committed:

- In pgsql/src/interfaces/ecpg/ecpglib/misc.c, do not set connection
  values if no connection is open.

- In ECPG, removed some variables no longer needed.

- In pgsql/src/interfaces/ecpg/ecpglib/execute.c, fixed incorrect
  memory management.

Marc Fournier committed:

- Tag 8.4.1, 8.3.8, 8.2.14, 8.1.18, 8.0.22, and 7.4.26.

Heikki Linnakangas committed:

- In pgsql/src/backend/utils/adt/xml.c, fix encoding handling in xml
  binary input function. If the XML header didn't specify an encoding
  explicitly, we used to treat it as being in database encoding when
  we parsed it, but then perform a UTF-8 -> database encoding
  conversion on it, which was completely bogus. It's now consistently
  treated as UTF-8.

- Tighten binary receive functions so that they reject values that the
  text input functions don't accept either.  While the backend can
  handle such values fine, they can cause trouble in clients and in
  pg_dump/restore.  This is followup to the original issue on time
  datatype reported by Andrew McNamara a while ago.  Like that one,
  none of these seem worth back-patching.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Heikki Linnakangas sent in a patch to make some of the binary recv
functions stricter.

KaiGai Kohei sent in another revision of the patch to add access
controls for large objects.

Greg Sabino Mullane sent in another revision of the patch to add YAML
as a formatting option for EXPLAIN output.

Kevin Grittner sent in two more revisions of the patch to add a LSB
conformant init script to contrib/start-scripts.

Peter Eisentraut sent in another patch to help with PL/PythonU data
type conversion improvements.

Jeff Janes sent in a patch to rearrange pgbench's handling of
#define'd constants.

Sam Mason sent in a patch which throws an error when anything but a
one-byte char is sent to the "char" type.

ITAGAKI Takahiro sent in a patch to add column-level triggers.

KaiGai Kohei sent in another revision of the patch to refactor ACL
facilities in PostgreSQL.

Zoltan Boszormenyi sent in another revision of the patch to implement
SELECT...FOR UPDATE [WAIT integer | NOWAIT].

Zoltan Boszormenyi sent in new revisions of the ECPG patches for
dynamic cursor name, sqlda, describe, and fixes for cursor scope error
handling in Informix compatibility mode.

Alvaro Herrera sent in a doc patch for logging at high loads.

Simon Riggs sent in a patch to skip SignalAutoVacuumWorkers(SIGTERM)
during recovery.

Zoltan Boszormenyi sent in another revision of the cursor-handling
patch for ECPG.

Robert Haas sent in another revision of the join removal patch.

Marko (johto) Tiikkaja sent in another revision of the patch to make
DML nodes in support of writeable CTEs.

KaiGai Kohei sent in another revision of the patch to add access
controls to large objects.