== PostgreSQL Weekly News - February 18 2007 == - Mailing list pgsql-announce

== PostgreSQL Weekly News - February 18 2007 ==

Version 3.4 of the open source SYMBOL Australian accounting program
gets enhanced scalability from PostgreSQL:
http://www.pcworld.idg.com.au/index.php/id;1504289875

IGN, the French National Geographical Institute, has chosen PostgreSQL
and PostGIS for its scalability, speed and transactional integrity.
http://postgis.refractions.net/documentation/casestudies/ign/

== PostgreSQL Product News ==

pgmemcache 1.1 released.  Ongoing development sponsored by Opten
Technology Group, Inc.
http://pgfoundry.org/projects/pgmemcache/

pgchem::tigress 7.0 released.
http://pgfoundry.org/projects/pgchem/

pgpool-II 1.0.2 released.
http://pgfoundry.org/projects/pgpool/

PL/PgPSM 0.2.0 released, now with full SQL/PSM support.  It is
installable like any other contrib package via PGXS.
http://pgfoundry.org/projects/plpsm/

== PostgreSQL Jobs for February ==

http://archives.postgresql.org/pgsql-jobs/2007-02/threads.php

== PostgreSQL Local ==

There will be a PostgreSQL booth at CLT on March 3 and 4 in Chemnitz,
Germany.  Some of the usual suspects from the German-speaking PostgreSQL
communities will be there.  Contact info@pgug.de to participate.
http://chemnitzer.linux-tage.de/2007/info/

There will be a PostgreSQL booth at FOSDEM on February 24 and 25 in
Brussels, Belgium.  Many of the usual suspects from the EU PostgreSQL
communities will be there.  Contact de@postgresql.org to participate.
http://www.fosdem.org/2007/

The Italian PostgreSQL community is looking for sponsors for its
PostgreSQL day in Prato, Italy this summer.  Check the link below
to participate.
http://www.pgday.it

== PostgreSQL in the News ==

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

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

PostgreSQL Weekly News is brought to you this week by David Fetter,
Jean-Paul Argudo and Josh Berkus.

To get your submission into the upcoming issue, get it to
david@fetter.org by Sunday at 3:00pm Pacific Time.

== Applied Patches ==

Alvaro Herrera committed:

- Install a more correct fix in the timestamp and timestamptz
  regression tests: remove duplicated tests in timestamp, and complete
  timestamptz with the tests that were missing to more closely mirror
  timestamp.

- Fix the timestamptz test problem, by moving the tests that use the
  timestamp_tbl table into the timestamp test.  Also, restore a test
  that used to exist as a valid test in the timestamptz test.

- Remove useless database name from bootstrap argument processing
  (including startup and bgwriter processes), and the -y flag.  It's
  not used anywhere.

- Restructure autovacuum in two processes: a dummy process, which runs
  continuously, and requests vacuum runs of "autovacuum workers" to
  postmaster.  The workers do the actual vacuum work.  This allows for
  future improvements, like allowing multiple autovacuum jobs running
  in parallel.  For now, the code keeps the original behavior of
  having a single autovac process at any time by sleeping until the
  previous worker has finished.

Peter Eisentraut committed:

- Better fix for determining minimum and maximum int64 values that
  doesn't require stdint.h and works for "busted" int64.

- Remove naughty // comment from pgsql/src/backend/utils/adt/formatting.c

- Add stdint.h header to pgsql/src/backend/utils/adt/xml.c

- Add functions for mapping table data and table schemas to XML
  (a.k.a. XML export)

- Fix typo in pgsql/doc/src/sgml/func.sgml.

Neil Conway committed:

- Fix capitalization and punctuation of two more GUC description
  strings.

Magnus Hagander committed:

- Make it possible to build with integer datetimes in msvc, and enable
  by default.

- Un-break build on ANSI compilers (like msvc) by moving Assert to
  position after variable declarations.

- Add script to run regression tests under vc++ without mingw.  Update
  clean script to properly clean up the result of it.

- One more fix for makefile := to : change.

- Properly parse Makefile after change from := to =.

Bruce Momjian committed:

- In pgsql/doc/src/sgml/spi.sgml, document that when sending multiple
  commands in a single string to SPI_execute(), the later commands
  cannot depend on the creation of objects earlier in the string.

- Update wording in TODO with some commas.

- Add to TODO: "Allow SQL-language functions to reference parameters
  by parameter name." currently SQL-language functions can only refer
  to parameters via $1, etc

- Update "Conflicting lock modes" to show as conflict, add
  current/requested headings, add link to table from text.

- Remove rint() for to_char MS and US output.  We can't us rint()
  because we can't overflow to the next higher units, and we might
  print the lower units for MS.

- Add to TODO: "Allow holdable cursors in SPI."

- Cleanup of Brendan Jurd's to_char() patch.

- Add to TODO: "Allow row and record variables to be set to NULL
  constants, and allow NULL tests on such variables Because a row is
  not scalar, do not allow assignment from NULL-valued scalars.

- Remove installation mention that integer timestamps is less stable
  that floating point.

- Mark Kirkwood's patch to create AVG() aggregates for int8 and
  NUMERIC which do not compute X^2, as a performance enhancement.

- Add to TODO: "Consider reducing on-disk varlena length from four to
  two because a heap row cannot be more than 64k in length

- Reduce the amount of memory "clobbered" for every process title
  change, on platforms that need this.  This is done by only writing
  past the previously stored message, if it was longer.

- In pgsql/doc/src/sgml/ecpg.sgml, remove extra character erroneously
  added.

- Remove tabs from SGML files to help tag alingment and improve
  detection of tabs are added in the future.

- Brendan Jurd's patch which adds two new format fields for use with
  to_char(), to_date() and to_timestamp() "ID" for day-of-week and
  "IDDD" for day-of-year This makes it possible to convert ISO week
  dates to and from text fully represented in either week
  ('IYYY-IW-ID') or day-of-year ('IYYY-IDDD') format.  It also adds an
  'isoyear' field for use with extract / date_part.

- Victor B. Wagner's SSL improvements which allow reading a global SSL
  configuration file, add GUC "ssl_ciphers" to control allowed
  ciphers, and add libpq environment variable PGSSLKEY to control SSL
  hardware keys

- Add to TODO: "Use LC_TIME for localized weekday/month names, rather
  than LC_MESSAGES."

- Fix to_date()/to_timestamp() 'D' field for day of week, was off by
  one.  Converting from char using 'D' doesn't make lots of sense, of
  course.  Report from Brendan Jurd.

- Move fsync method macro defines into /include/access/xlogdefs.h so
  they can be used by src/tools/fsync/test_fsync.c.

- Add to TODO: "Clean up casting in /contrib/isn."

- Add to TODO: "Improve logging of prepared statements recovered
  during startup."

- In TODO, add URL for, "Allow SQL-language functions to return
  results from RETURNING queries."

- In TODO, add URL for, "Allow accurate statistics to be collected on
  indexes with more than one column or expression indexes, perhaps
  using per-index statistics."

- In TODO, add URL for, "Update Bonjour to work with newer
  cross-platform SDK"

- Add to TODO: "Improve failure message when DROP DATABASE is used on
  a database that has prepared transactions."

- In TODO, add URL for, "Update Bonjour to work with newer
  cross-platform SDK."

- In clusterdb and reindexdb, add code so database scans are done in
  an order consistent with pg_dumpall.

- Update /contrib/fuzzystrmatch error message to mention bytes, not
  just 'length', which can be characters.

- Add ORDER BY to vacummdb so databases are scaned in the same order
  as pg_dumpall.

- Add to TODO: "Update our code to handle 64-bit timezone files to
  match the zic source code, which now uses them"

- In pgsql/src/port/open.c, add comment to explain why O_EXCL and
  O_TRUNC can be ignored in openFlagsToCreateFileFlags() in certain
  cases.

- In pgsql/src/backend/utils/adt/formatting.c, add comment that
  to_char() for broken glibc pt_BR might cause a problem.

Tom Lane committed:

- Improve plpgsql's error message when a datatype declaration is
  omitted.  Per example from Jeff Ross.

- Put function expressions and values lists into FunctionScan and
  ValuesScan plan nodes, so that the executor does not need to get
  these items from the range table at runtime.  This will avoid
  needing to include these fields in the compact range table I'm
  expecting to make the executor use.

- Fix portal management code to support non-default command completion
  tags for portals using PORTAL_UTIL_SELECT strategy.  This is
  currently significant only for FETCH queries, which are supposed to
  include a count in the tag.  Seems it's been broken since 7.4, but
  nobody noticed before Knut Lehre.

- Add code so that when COPY_PARSE_PLAN_TREES is defined, the copy and
  equal functions are checked for raw parse trees as well as
  post-analysis trees.  This was never very important before, but the
  upcoming plan cache control module will need to be able to do
  copyObject() on raw parse trees.

- Teach find_nonnullable_rels to handle OR cases: if every arm of an
  OR forces a particular relation nonnullable, then we can say that
  the OR does.  This is worth a little extra trouble since it may
  allow reduction of outer joins to plain joins.

- Fix new RI operator selection code to do the right thing when
  working with an opclass for a generic type such as ANYARRAY.  The
  original coding failed to check that PK and FK columns were of the
  same array type.  Per discussion with Tom Dunstan.  Also, make the
  code a shade more readable by not trying to economize on variables.

- Adjust the definition of is_pushed_down so that it's always true for
  INNER JOIN quals, just like WHERE quals, even if they reference
  every one of the join's relations.  Now that we can reorder outer
  and inner joins, it's possible for such a qual to end up being
  assigned to an outer join plan node, and we mustn't have it treated
  as a join qual rather than a filter qual for the node.  (If it were,
  the join could produce null-extended rows that it shouldn't.)  Per
  bug report from Pelle Johansson.

- Code review for SSLKEY patch.

- Fix markup, spelling, grammar, and explanations for SSLKEY patch.

- Fix another problem in 8.2 changes that allowed "one-time" qual
  conditions to be checked at plan levels below the top; namely, we
  have to allow for Result nodes inserted just above a nestloop inner
  indexscan.  Should think about using the general Param mechanism to
  pass down outer-relation variables, but for the moment we need a
  back-patchable solution.  Per report from Phil Frost.

- Restructure code that is responsible for ensuring that clauseless
  joins are considered when it is necessary to do so because of a
  join-order restriction (that is, an outer-join or IN-subselect
  construct).  The former coding was a bit ad-hoc and inconsistent,
  and it missed some cases, as exposed by Mario Weilguni's recent bug
  report.  His specific problem was that an IN could be turned into a
  "clauseless" join due to constant-propagation removing the IN's
  joinclause, and if the IN's subselect involved more than one
  relation and there was more than one such IN linking to the same
  upper relation, then the only valid join orders involve "bushy"
  plans but we would fail to consider the specific paths needed to get
  there.  (See the example case added to the join regression test.)
  On examining the code I wonder if there weren't some other problem
  cases too; in particular it seems that GEQO was defending against a
  different set of corner cases than the main planner was.  There was
  also an efficiency problem, in that when we did realize we needed a
  clauseless join because of an IN, we'd consider clauseless joins
  against every other relation whether this was sensible or not.  It
  seems a better design is to use the outer-join and in-clause lists
  as a backup heuristic, just as the rule of joining only where there
  are joinclauses is a heuristic: we'll join two relations if they
  have a usable joinclause *or* this might be necessary to satisfy an
  outer-join or IN-clause join order restriction.  I refactored the
  code to have just one place considering this instead of three, and
  made sure that it covered all the cases that any of them had been
  considering.  Backpatch as far as 8.1 (which has only the IN-clause
  form of the disease).  By rights 8.0 and 7.4 should have the bug
  too, but they accidentally fail to fail, because the joininfo
  structure used in those releases preserves some memory of there
  having once been a joinclause between the inner and outer sides of
  an IN, and so it leads the code in the right direction anyway.  I'll
  be conservative and not touch them.

- Add ORDER BY to a query on information_schema.views, to avoid
  possible platform-specific result ordering.  Per buildfarm results.

- Repair oversight in 8.2 change that improved the handling of
  "pseudoconstant" WHERE clauses.  createplan.c is now willing to
  stick a gating Result node almost anywhere in the plan tree, and in
  particular one can wind up directly underneath a MergeJoin node.
  This means it had better be willing to handle Mark/Restore.
  Fortunately, that's trivial in such cases, since we can just pass
  off the call to the input node (which the planner has previously
  ensured can handle Mark/Restore).  Per report from Phil Frost.

- Add some discussion of sort ordering to indices.sgml, which
  curiously had never touched the subject before.

- Heikki Linnakangas's patch which throws an error if you try to
  COMMIT/ROLLBACK PREPARED from a database other than the one where
  the transaction was originally prepared.

- Minor editorialization on operator-family documentation: put some
  copied-and-pasted text in a more useful location.

- Fix up foreign-key mechanism so that there is a sound semantic basis
  for the equality checks it applies, instead of a random dependence
  on whatever operators might be named "=".  The equality operators
  will now be selected from the opfamily of the unique index that the
  FK constraint depends on to enforce uniqueness of the referenced
  columns; therefore they are certain to be consistent with that
  index's notion of equality.  Among other things this should fix the
  problem noted awhile back that pg_dump may fail for foreign-key
  constraints on user-defined types when the required operators aren't
  in the search path.  This also means that the former warning
  condition about "foreign key constraint will require costly
  sequential scans" is gone: if the comparison condition isn't
  indexable then we'll reject the constraint entirely. All per past
  discussions.  Along the way, make the RI triggers look into
  pg_constraint for their information, instead of using
  pg_trigger.tgargs; and get rid of the always error-prone fixed-size
  string buffers in ri_triggers.c in favor of building up the RI
  queries in StringInfo buffers.  This forces initdb due to columns
  added to pg_constraint and pg_trigger.

- Disallow committing a prepared transaction unless we are in the same
  database it was executed in.  Someday it might be nice to allow
  cross-DB commits, but work would be needed in NOTIFY and perhaps
  other places.  Per Heikki Linnakangas.

- Improve postmaster's behavior if an accept() call fails.  Because
  the server socket is still read-ready, the code was a tight loop,
  wasting lots of CPU.  We can't do anything to clear the failure,
  other than wait, but we should give other processes more chance to
  finish and release FDs; so insert a small sleep.  Also, avoid bogus
  "close(-1)" in this case.  Per report from Jim Nasby.

- Repair bug in 8.2's new logic for planning outer joins: we have to
  allow joins that overlap an outer join's min_righthand but aren't
  fully contained in it, to support joining within the RHS after
  having performed an outer join that can commute with this one.
  Aside from the direct fix in make_join_rel(), fix
  has_join_restriction() and GEQO's desirable_join() to consider this
  possibility.  Per report from Ian Harding.

- Avoid infinite recursion when dumping new planner EquivalenceClass
  trees.

Teodor Sigaev committed:

- Fix backend crash in parsing incorrect tsquery.  Per report from Jon
  Rosebaugh.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Pavel Stehule sent in a patch which implements TABLE functions per
SQL:2003.

Greg Smith sent in a patch which adds two command-line switches to
pgbench: -x, which generates extended detail in the latency log, and
-X, which does extra cleanup work after the run.

Pavan Deolasee sent in another version of the HOT-update patch.

Gregory Stark sent in a patch to replace the VARATT_SIZEP with
SET_VARLENA_LEN.  This is part of the implementation of the
variable-length varlena type per discussion on -hackers.

Tom Dunstan sent in an updated version of his enums patch.

Magnus Hagander sent in a patch to support MOVE for cursors in
PL/PgSQL.

Arul Shaji sent in a patch to implement the TODO item, "Allow server
log information to be output as INSERT statements
This would allow server log information to be easily loaded into a
database for analysis."  Tom Lane suggested that he refactor this as
COPY statements.

Alvaro Herrera sent in two more patches to clean up the bootstrap
code.

Chad Wagner sent in a patch which allows psql's \pset to take an
argument for boolean variables.  The old toggling behavior still works
when no argument is passed.

Bruce Momjian sent in a patch implementing isodow in date_part.