== Postgres Weekly News - March 23 2008 == - Mailing list pgsql-announce

== Postgres Weekly News - March 23 2008 ==

PostgreSQL 8.3.1 and 8.2.7 are out.  Upgrade ASAP!

Summer of PostgreSQL: Google Summer of Code starts accepting
applications for students working on PostgreSQL projects on March
25th.  Prepare those projects now!

== Postgres Product News ==

MyJSQLView 2.86 released.
http://myjsqlview.sourceforge.net/

Registration for PGCon 2008 is open.
http://www.pgcon.org/2008/registration.php

ptop 3.6.2-beta2 released.
http://ptop.projects.postgresql.org/

phpPgAdmin 4.2-Beta-2 released.  Please test!
http://phppgadmin.sourceforge.net/?page=download

Pagila 0.10.1 released
http://pgfoundry.org/frs/?group_id=1000150&release_id=998

check_postgres 1.3.0 released.
http://bucardo.org/nagios/

== Postgres Jobs for March ==

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

== Postgres Local ==

LAPUG will be meeting March 28, 7:00pm in the City of Garden Grove
Training Room.
http://pugs.postgresql.org/lapug

PostgreSQL Conference East '08 talks are March 29 and 30 at the
University of Maryland, College Park.  Registration closes March 26th.
http://www.postgresqlconference.org/

PG UK day will be April 2 in Birmingham.
http://www.postgresql.org.uk/

FISL 9.0 will be April 17-19 at PUCRS in Porto Alegre, RS, Brazil.
https://fisl.softwarelivre.org/9.0/

PGCon 2008 will be May 20-23 in Ottawa.
http://www.pgcon.org/2008/

Utah Open Source Conference 2008's CfP is open through June 1.
This 2nd annual conference is August 28-30, 2008 in Salt Lake City, UT
http://2008.utosc.com/

== Postgres in the News ==

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

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

Postgres Weekly News is brought to you this week by David Fetter and
Josh Berkus.

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:

- When creating a large hash index, pre-sort the index entries by
  estimated bucket number, so as to ensure locality of access to the
  index during the insertion step.  Without this, building an index
  significantly larger than available RAM takes a very long time
  because of thrashing.  On the other hand, sorting is just useless
  overhead when the index does fit in RAM.  We choose to sort when the
  initial index size exceeds effective_cache_size.  This is a revised
  version of work by Tom Raney and Shreya Bhargava.

- In pgsql/doc/src/sgml/ref/create_index.sgml, add a note to the
  CREATE INDEX reference page about the impact of maintenance_work_mem
  and effective_cache_size on index creation speed.

- Fix TransactionIdIsCurrentTransactionId() to use binary search
  instead of linear search when checking child-transaction XIDs.  This
  makes for an important speedup in transactions that have large
  numbers of children, as in a recent example from Craig Ringer.  We
  can also get rid of an ugly kluge that represented lists of
  TransactionIds as lists of OIDs.  Heikki Linnakangas

- In pgsql/src/backend/utils/sort/tuplesort.c, grab some low-hanging
  fruit in the new hash index build code.  oprofile shows that a
  nontrivial amount of time is being spent in repeated calls to
  index_getprocinfo, which really only needs to be called once.  So do
  that, and inline _hash_datum2hashkey to make it work.

- In pgsql/src/backend/utils/adt/selfuncs.c, revert thinko introduced
  into prefix_selectivity() by my recent patch: make_greater_string
  needs the < procedure not the >= one.  Spotted by Peter Eisentraut.

- In pgsql/src/port/snprintf.c, fix our printf implementation to
  follow spec: if a star parameter value for a precision is negative,
  act as though precision weren't specified at all, that is the whole
  .* part of the format spec should be ignored.  Our previous coding
  took it as .0 which is certainly wrong.  Per report from Kris Jurka
  and local testing.  Possibly this should be back-patched, but it
  would be good to get some more testing first; in any case there are
  no known cases where there's really a problem on the backend side.

- In pgsql/src/backend/executor/nodeIndexscan.c, advance multiple
  array keys rightmost-first instead of leftmost-first during a bitmap
  index scan.  This cannot affect the query results (since we're just
  dumping the TIDs into a bitmap) but it might offer some advantage in
  locality of access to the index.  Per Greg Stark.

- Arrange to "inline" SQL functions that appear in a query's FROM
  clause, are declared to return set, and consist of just a single
  SELECT.  We can replace the FROM-item with a sub-SELECT and then
  optimize much as if we were dealing with a view.  Patch from Richard
  Rowell, cleaned up by me.

- In pgsql/src/backend/utils/adt/regexp.c, fix regexp substring
  matching (substring(string from pattern)) for the corner case where
  there is a match to the pattern overall but the user has specified a
  parenthesized subexpression and that subexpression hasn't got a
  match.  An example is substring('foo' from 'foo(bar)?').  This
  should return NULL, since (bar) isn't matched, but it was mistakenly
  returning the whole-pattern match instead (ie, 'foo').  Per bug
  #4044 from Rui Martins.  This has been broken since the beginning;
  patch in all supported versions.  The old behavior was sufficiently
  inconsistent that it's impossible to believe anyone is depending on
  it.

- Support ALTER TYPE RENAME.  Petr Jelinek

- Support a --no-tablespaces option in pg_dump/pg_dumpall/pg_restore,
  so that dumps can be loaded into databases without the same
  tablespaces that the source had.  The option acts by suppressing all
  "SET default_tablespace" commands, and also CREATE TABLESPACE
  commands in pg_dumpall's case.  Gavin Roy, with documentation and
  minor fixes by me.

- In pgsql/src/bin/pg_dump/pg_dumpall.c, dept of second thoughts:
  --no-tablespaces had better also prevent pg_dumpall from attaching
  TABLESPACE options to CREATE DATABASE commands.

- Arrange for an explicit cast applied to an ARRAY[] constructor to be
  applied directly to all the member expressions, instead of the
  previous implementation where the ARRAY[] constructor would infer a
  common element type and then we'd coerce the finished array after
  the fact.  This has a number of benefits, one being that we can
  allow an empty ARRAY[] construct so long as its element type is
  specified by such a cast.  Brendan Jurd, minor fixes by me.

- Get rid of a bunch of #ifdef HAVE_INT64_TIMESTAMP conditionals by
  inventing a new typedef TimeOffset to represent an intermediate time
  value.  It's either int64 or double as appropriate, and in most
  usages will be measured in microseconds or seconds the same as
  Timestamp.  We don't call it Timestamp, though, since the value
  doesn't necessarily represent an absolute time instant.  Warren
  Turkal

- Adjust pgstatindex() to give correct answers for indexes larger than
  2^31 blocks.  Also fix pg_relpages() for the same case.  Tatsuhito
  Kasahara.

- Report the current queries of all backends involved in a deadlock
  (if they'd be visible to the current user in pg_stat_activity).
  This might look like it's subject to race conditions, but it's
  actually pretty safe because at the time DeadLockReport() is
  constructing the report, we haven't yet aborted our transaction and
  so we can expect that everyone else involved in the deadlock is
  still blocked on some lock.  (There are corner cases where that
  might not be true, such as a statement timeout triggering in another
  backend before we finish reporting; but at worst we'd report a
  misleading activity string, so it seems acceptable considering the
  usefulness of reporting the queries.) Original patch by Itagaki
  Takahiro, heavily modified by me.

- In pgsql/src/backend/parser/parse_utilcmd.c, give an explicit error
  for serial[], rather than silently ignoring the array decoration as
  the code had been doing.

- Remove TypeName struct's timezone flag, which has been write-only
  storage for a very long time --- in current usage it's entirely
  redundant with the name field.

- In pgsql/src/backend/utils/adt/formatting.c, refactor
  to_char/to_date formatting code; primarily, replace DCH_processor
  with two new functions DCH_to_char and DCH_from_char that have less
  confusing APIs.  Brendan Jurd.

- Create a function quote_nullable(), which works the same as
  quote_literal() except that it returns the string 'NULL', rather
  than a SQL null, when called with a null argument.  This is often a
  much more useful behavior for constructing dynamic queries.  Add
  more discussion to the documentation about how to use these
  functions.  Brendan Jurd

- In pgsql/src/backend/executor/nodeMaterial.c, avoid a useless tuple
  copy within nodeMaterial.  Neil Conway.

Bruce Momjian committed:

- Mark TODO as done: "During index creation, pre-sort the tuples to
  improve build speed."

- Add to TODO: "Allow Kerberos to disable stripping of realms so we
  can check the username@realm against multiple realms."

- Add URL for TODO: "Consider increasing the number of default
  statistics target, and reduce statistics target overhead."

- Adjust TODO spacing.

- Add to TODO: "Fix server restart problem when the server was
  shutdown during a PITR backup."

- Add to TODO: "Improve text search error messages" and "Fix
  /contrib/ltree operator."

- Add URL for TODO: "Improve text search error messages."

- Add to TODO: "Remove pre-7.3 pg_dump code that assumes pg_depend
  does not exit."

- Add URL for TODO: "Add SQL:2003 WITH RECURSIVE (hierarchical)
  queries to SELECT."

- Add to TODO: "Consider if CommandCounterIncrement() can avoid its
  AcceptInvalidationMessages() call."

- Add to TODO: "Reduce file system activity overhead of statistics
  file pgstat.stat."

- Add to TODO: "Reduce BIT data type overhead using short varlena
  headers."

- Add to TODO: "Allow SSL key file permission checks to be optionally
  disabled when sharing SSL keys with other applications."

- Add URL for TODO: "Allow COPY to report error lines and continue."

- Add to TODO: "Consider Cartesian joins when both relations are
  needed to form an indexscan qualification for a third relation."

- Add to TODO: "Recreate pg_xlog/archive_status/ if it doesn't exist
  after restoring from a PITR backup."

- Add URLs for TODO: "Speed WAL recovery by allowing more than one
  page to be prefetched."

- Add URLs for TODO: "Allow UPDATE tab SET ROW (col, ...) = (SELECT...)"

- Add to TODO: "Consider not storing a NULL bitmap on disk if all the
  NULLs are trailing."

- In pgsql/src/tools/find_typedef, add Linux support to find_typedefs,
  with help from Alvaro.

- In pgsql/src/tools/find_typedef, add find_typedef comment.

- In pgsql/src/tools/find_typedef, add find_typedef comments for
  Linux.

- Update TODO: "Speed WAL recovery by allowing more than one page to
  be prefetched."

- Split TODO entries into: "Experiment with multi-threaded backend
  better I/O utilization" and "Experiment with multi-threaded backend
  better CPU utilization" per suggestion by Heikki Linnakangas.

- Make source code READMEs more consistent.  Add CVS tags to all
  README files.

- More README src cleanups.

- In  pgsql/doc/src/sgml/runtime.sgml, document that soft-mounting NFS
  is not recommended.

- Add to TODO: "Prevent SSL from sending network packets to avoid
  interference with Win32 signal emulation."

- Add to TODO: "Fix inconsistent precedence of =, >, and < compared to
  <>, >=, and <=."

- Add to TODO: "Convert single quotes to apostrophes in the PDF
  documentation."

- Add URLs for TODO: "Simplify ability to create partitioned tables."

- Add to TODO: "Improve performance of shared invalidation queue for
  multiple CPUs."

- Add URLs for TODO: "Simplify ability to create partitioned tables."

- Add to TODO: "Add checks to prevent a CREATE RULE views on inherited
  tables."

- Add URL for TODO: "Add checks to prevent a CREATE RULE views on
  inherited tables."

- Add to TODO: "Avoid tuple some tuple copying in sort routines."

- Add to Win32 TODO: "Support pgxs."

- Add to TODO: "Improve WAL concurrency by increasing lock
  granularity."

- Add to Win32 TODO: "Fix MSVC NLS support, like for to_char()."

- Add to pg_dump TODO: "Allow pre/data/post files when dumping a
  single object, for performance reasons."

- Mark TODO as done: "Avoid tuple some tuple copying in sort
  routines."

- Add URL for TODO: "Do async I/O for faster random read-ahead of
  data."

- Add to TODO: "Sort large UPDATE/DELETEs so it is done in heap
  order."

- Re-add to TODO: "Avoid tuple some tuple copying in sort routines."

- Add URL for TODO: "Avoid tuple some tuple copying in sort routines."

- Update Win32 TODO to read: "Support pgxs when using MSVC."

Alvaro Herrera committed:

- Move ProcState definition into sinvaladt.c from sinvaladt.h, since
  it's not needed anywhere after my previous patch.  Noticed by Tom
  Lane.  Also, remove #include <signal.h> from sinval.c.

- In pgsql/src/backend/storage/ipc/sinvaladt.c, move elog(DEBUG4) call
  outside the locked area, per suggestion from Tom Lane.

- In pgsql/src/backend/commands/vacuum.c, we no longer need a snapshot
  set after opening the finishing transaction: this is redundant
  because autovacuum now always analyzes a single table per
  transaction.

- In pgsql/src/backend/postmaster/autovacuum.c, remove another useless
  snapshot creation.

- Add a couple of missing FreeQueryDesc calls.  Noticed while testing
  a framework to keep track of snapshots in use.

Magnus Hagander committed:

- Fix postgres --describe-config for guc enums, breakage noted by
  Alvaro Herrera.  While at it, rename option lookup functions to make
  names clearer, per discussion with Tom Lane.

- In pgsql/doc/src/sgml/cvs.sgml, wiki page about cvs now lives in the
  main wiki, the one on developer.postgresql.org is going away.

- In pgsql/doc/src/sgml/acronyms.sgml, cvsweb lives on
  anoncvs.postgresql.org these days.

Peter Eisentraut committed:

- In pgsql/src/backend/common.mk, we need to rebuild objfiles.txt when
  one of the subdirectories' objfiles.txt changed in case a new file
  got added.

- Enable probes to work with Mac OS X Leopard and other OSes that will
  support DTrace in the future.  Switch from using DTRACE_PROBEn
  macros to the dynamically generated macros.  Use "dtrace -h" to
  create a header file that contains the dynamically generated macros
  to be used in the source code instead of the DTRACE_PROBEn macros.
  A dummy header file is generated for builds without DTrace support.
  Robert Lor.

- Catch all errors in for and while loops in makefiles.  Don't ignore
  any errors in any commands, including in various clean targets that
  have so far been handled inconsistently.  make -i is available to
  ignore all errors in a consistent and official way.

- In pgsql/src/interfaces/ecpg/preproc/Makefile, don't need -Wno-error
  anymore, because flex is no longer producing warnings.

Tatsuo Ishii committed:

- In pgsql/contrib/pgbench/pgbench.c, fix tps calculation when -C
  supplied.  Per Yoshiyuki Asaba.  Change Copyright owner from mine to
  PostgreSQL Global Development Group Fix minor message typo.

- Add libpq new API lo_import_with_oid() which is similar to
  lo_import() except that lob's oid can be specified.

- Add -M (query mode) option to pgbench per ITAGAKI Takahiro.

- Add server side lo_import(filename, oid) function.

Heikki Linnakangas committed:

- In pgsql/src/backend/utils/mb/conversion_procs/cyrillic_and_mic/cyrillic_and_mic.c,
  add the missing cyrillic "Yo" characters ('e' and 'E' with two dots)
  to the ISO_8859-5 <-> MULE_INTERNAL conversion tables.  This was
  discovered when trying to convert a string containing those
  characters from ISO_8859-5 to Windows-1251, because we use
  MULE_INTERNAL/KOI8R as an intermediate encoding between those two.
  While the missing "Yo" was just an omission in the conversion
  tables, there are a few other characters like the "Numero" sign
  ("No" as a single character) that exists in all the other cyrillic
  encodings (win1251, ISO_8859-5 and cp866), but not in KOI8R. Added
  comments about that.  Patch by Sergey Burladyan. Back-patch to 7.4.

Michael Meskes committed:

- In ecpg, changed statement escaping to not escape continuation line
  markers.

- In HEAD's ecpg, added ECPGget_PGconn() function to ecpglib, courtesy
  of Mike Aubury.  Removed one include file from connect-test1.

- In 8.3's ecpg, added ECPGget_PGconn() function to ecpglib, courtesy
  of Mike Aubury.  Bumped library version to 6.1.

- In pgsql/src/interfaces/ecpg/ecpglib/Makefile, corrected version
  number.

Andrew Dunstan committed:

- In pgsql/src/tools/msvc/Solution.pm, generate dummy probes.h for
  MSVC builds.

== Rejected Patches (for now) ==

Bruce Momjian's patch to pg_dump -i wording.  The wording got less
scary rather than the intended more scary.

== Pending Patches ==

ITAGAKI Takahiro sent another revision of his patch to suppress
compiler warnings on mingw.

Kohei KaiGai sent in a set of four patches intended to push parts of
SE-PostgreSQL into 8.4.

ITAGAKI Takahiro sent in another revision of his patch to add query
modes to pgbench to measure the performance of the simple protocol,
the extended protocol and prepared statements with it.

Alvaro Herrera sent in a patch which adds a new module to the snapshot
code which stashes used snapshots and refcounts them.

Simon Riggs sent in another revision of his WIP patch for tuning bulk
inserts.

Martin Pihlak sent in a patch which enables tracking function calls
through the stats subsystem per discussion below:
http://archives.postgresql.org/pgsql-hackers/2007-07/msg00377.php