== Postgres Weekly News - September 30 2007 ==

== Postgres Weekly News - September 30 2007 ==

Watch for Postgres 8.3 beta 1 this week!

== Postgres Product News ==

Bucardo 3.0.6 asynchronous multi-master system released.
http://bucardo.org/

PostgreSQL Maestro 7.9 released.
http://www.sqlmaestro.com/products/postgresql/maestro/

Pg::Snapshot and Materialized Views version 0.3.0a released.
http://pgfoundry.org/projects/snapshot/

PgPool-II 1.2.1 released.
http://pgfoundry.org/projects/pgpool/

== Postgres Jobs for September ==

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

== Postgres Local ==

PostgreSQL Fall Conference will be October 20, 2007 in Portland.
http://www.postgresqlconference.org/

Simon Riggs will be giving a talk at Databases and the Web on October
16 in London.
http://www.ukuug.org/events/seminars/databases/booking/

== Postgres in the News ==

Planet Postgres: 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

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to david@fetter.org, German to
pwn@pgug.de.

== Applied Patches ==

Magnus Hagander committed:

- In pgsql/src/tools/msvc/Mkvcbuild.pm, add chklocale.c to pgport
  files for msvc builds, per buildfarm failures.

- Properly pass -c parameter to ecpg when building regression tests.
  Per Michael.

Michael Meskes committed:

- Applied another patch by ITAGAKI Takahiro to get memory allocation
  thread-safe.  He also did some cleaning up.

- Applied patch by ITAGAKI Takahiro to get prepare thread-safe in
  ecpg.

Teodor Sigaev committed:

- Fix crash of to_tsvector() function on huge input: compareWORD()
  function didn't return the correct result for word position greater
  than limit.  Per report from Stuart Bishop.

Bruce Momjian committed:

- In the release checklist, mention packagers will see the minor
  upgrade numbering for additional functions.

- In pgsql/src/pl/tcl/pltcl.c, update TCL comment to read, "We can
  only fix this with Tcl >= 8.4, when Tcl_SetNotifier()."

- Remove from TODO: "SQL*Net listener that makes PostgreSQL appear as
  an Oracle database to clients"

- Rename "PostgreSQL" to "Postgres" in 3 places in FAQ_DEV.

- Rename "PostgreSQL" to "Postgres" in 7 places in the FAQ.

- Add to TODO: "Implement Boyer-Moore searching in strpos()"

- Document that libpq's PQntuples() might overflow.

Peter Eisentraut committed:

- Small string tweaks.

Alvaro Herrera committed:

- Adjust the new memory limit in the lazy vacuum code to use
  MaxHeapTuplesPerPage tuples per page instead of fixed 200, to better
  cope with systems that use a different block size.

- Avoid having autovacuum read pgstats data too many times in quick
  succession.  This is problematic for the autovac launcher when there
  are many databases, so we keep data for a full second before reading
  it again.

- Reduce the size of memory allocations by lazy vacuum when processing
  a small table, by allocating just enough for a hardcoded number of
  dead tuples per page.  The current estimate is 200 dead tuples per
  page.  Per reports from Jeff Amiel, Erik Jones and Marko Kreen, and
  subsequent discussion.

Tom Lane committed:

- Add an extra header byte to TOAST-pointer datums to represent their
  size explicitly.  This means a TOAST pointer takes 18 bytes instead
  of 17 --- still smaller than in 8.2 --- which seems a good tradeoff
  to ensure we won't have painted ourselves into a corner if we want
  to support multiple types of TOAST pointer later on.  Per discussion
  with Greg Stark.

- In hopes of un-breaking the buildfarm, add missing file
  pgsql/src/interfaces/ecpg/test/thread/alloc.pgc from ITAGAKI
  Takahiro's patch.

- In pgsql/src/backend/access/transam/xlog.c, adjust recovery PS
  display as agreed with Simon: 'waiting for XXX' while the
  restore_command does its thing, then 'recovering XXX' while
  processing the segment file.  These operations are heavyweight
  enough that an extra PS display set shouldn't bother anyone.

- In pgsql/contrib/isn/isn.sql.in, properly mark mergeable/hashable
  equality operators (found by opr_sanity testing).  Combine the
  formerly independent opclasses for the various ISN types into
  opfamilies.  The latter causes some extra bleating from opr_sanity,
  since the module doesn't provide complete sets of cross-type
  operators, but it's still a good idea because it will give the
  planner more information to work with.  The missing cross-type
  operators no longer pose a risk of unexpected planner errors in 8.3,
  so there's no need to insist on filling them in (and I gather it
  wouldn't be very sound semantically to add them all).

- In pgsql/contrib/seg/seg.sql.in, support functions for index
  opclasses should be immutable.  Found by running opr_sanity on
  contrib modules.

- In pgsql/contrib/pg_trgm/pg_trgm.sql.in, support functions for index
  opclasses should be immutable.  Found by running opr_sanity on
  contrib modules.

- In pgsql/contrib/ltree/ltree.sql.in, support functions for index
  opclasses should be immutable.  Found by running opr_sanity on
  contrib modules.

- In pgsql/contrib/intarray/_int.sql.in, support functions for index
  opclasses should be immutable.  Found by running opr_sanity on
  contrib modules.

- In pgsql/contrib/hstore/hstore.sql.in, support functions for index
  opclasses should be immutable.  Found by running opr_sanity on
  contrib modules.

- In pgsql/contrib/cube/cube.sql.in, support functions for index
  opclasses should be immutable.  Found by running opr_sanity on
  contrib modules.

- In pgsql/contrib/chkpass, remove bogus commutator marking --- the
  module doesn't actually supply any commutator operator for
  =(chkpass,text), so this was creating a shell operator that would
  fail on use.  Found by opr_sanity testing.

- In pgsql/contrib/btree_gist/btree_gist.sql.in, support functions for
  index opclasses should be immutable.  Found by running opr_sanity on
  contrib modules.

- In pgsql/src/backend/access/transam/xlog.c, make recovery show the
  current input WAL segment name in the startup process' PS display.
  After a suggestion by Simon (not exactly his patch though).

- Disallow CLUSTER using an invalid index (that is, one left over from
  a failed CREATE INDEX CONCURRENTLY).  Such an index might not have
  entries for every heap row and thus clustering with it would result
  in silent data loss.  The scenario requires a pretty foolish DBA,
  but still ...

- Improve consistency of the error messages generated when you try to
  use ALTER TABLE on a composite type or ALTER TYPE on a table's
  rowtype.  We already rejected these cases, but the error messages
  were a bit random and didn't always provide a HINT to use the other
  command type.

- Defend against openssl libraries that fail on keys longer than 128
  bits; which is the case at least on some Solaris versions.  Marko
  Kreen

- Defend against openssl libraries that fail on keys longer than 128
  bits; which is the case at least on some Solaris versions.  Marko
  Kreen.  Back-patched to 8.2.

- Make archive recovery always start a new timeline, rather than only
  when a recovery stop time was used.  This avoids a corner-case risk
  of trying to overwrite an existing archived copy of the last WAL
  segment, and seems simpler and cleaner all around than the original
  definition.  Per example from Jon Colverson and subsequent analysis
  by Simon.  Back-patched to 8.0

- Make use of additional chklocale.c entries to reject locales that we
  cannot support.

- Teach chklocale.c about a few names for frontend-only encodings,
  since this will allow initdb to reject attempts to initdb in a
  locale that uses such an encoding.  We'll probably find out more
  such names during beta ...

- In pgsql/src/port/chklocale.c on OS X, assume that an empty-string
  result for nl_langinfo(CODESET) means UTF-8.  Per examination of
  /usr/share/locale in 10.4.10.

- Change initdb and CREATE DATABASE to actively reject attempts to
  create databases with encodings that are incompatible with the
  server's LC_CTYPE locale, when we can determine that (which we can
  on most modern platforms, I believe).  C/POSIX locale is compatible
  with all encodings, of course, so there is still some usefulness to
  CREATE DATABASE's ENCODING option, but this will insulate us against
  all sorts of recurring complaints caused by mismatched settings.  I
  moved initdb's existing LC_CTYPE-to-encoding mapping knowledge into
  a new src/port/ file so it could be shared by CREATE DATABASE.

- Tweak initdb's text search configuration selection code so it can
  cope with LANG settings like 'es_ES@euro'.

- Fix typos in two comments.  Spotted by Brendan Jurd

- Tweak pgbench.c to remove the hidden assumption that a WIN32 machine
  couldn't possibly HAVE_GETOPT.  I believe this is the most
  appropriate form of the patch submitted 2007-08-07 by Hiroshi Saito,
  though not having a Windows build environment I won't know for sure
  till I see the buildfarm results.

- Define the FRONTEND symbol in postgres_fe.h, which allows us to
  eliminate duplicative -DFRONTEND flags from many Makefiles.  We
  still need Makefile control of the symbol in a few places that
  compile frontend-or-backend src/port/ files, but it's a lot cleaner
  than before.  Hiroshi Saito

- Add virtual transaction IDs to CSVLOG output, so that messages
  coming from the same transaction can be identified even when no
  regular XID was assigned.  This seems essential after addition of
  the lazy-XID patch.  Also some minor code cleanup in write_csvlog().

- Fix Assert failure in ExpandColumnRefStar --- what I thought was a
  can't happen condition can happen given incorrect input.  The real
  problem is that gram.y should try harder to distinguish * from "*"
  --- the latter is a legal column name per spec, and someday we ought
  to treat it that way.  However fixing that is too invasive for a
  back-patch, and it's too late for the 8.3 cycle too.  So just reduce
  the Assert to a plain elog for now.  Per report from NikhilS.

- Some small tuptoaster improvements from Greg Stark.  Avoid
  unnecessary decompression of an already-compressed external value
  when we have to copy it; save a few cycles when a value is too short
  for compression; and annotate various lines that are currently
  unreachable.

- Minor improvements in backup and recovery.

- Create a function variable "join_search_hook" to let plugins
  override the join search order portion of the planner; this is
  specifically intended to simplify developing a replacement for GEQO
  planning.  Patch by Julius Stroffek, editorialized on by me.  I
  renamed make_one_rel_by_joins to standard_join_search and
  make_rels_by_joins to join_search_one_level to better reflect their
  place within this scheme.

- In the integer-datetimes case, date2timestamp and date2timestamptz
  need to check for overflow because the legal range of type date is
  actually wider than timestamp's.  Problem found by Neil Conway.

- Use SYSV semaphores rather than POSIX on Darwin >= 6.0 (i.e., OS X
  10.2 and up), per Chris Marcellino.  This avoids consuming O(N^2)
  file descriptors to support N backends.  Tests suggest it's about a
  wash for small installations, but large ones would have a problem.

- Change on-disk representation of NUMERIC datatype so that the
  sign_dscale word comes before the weight instead of after.  This
  will allow future binary-compatible extension of the representation
  to support compact formats, as discussed on pgsql-hackers around
  2007/06/18.  The reason to do it now is that we've already pretty
  well broken any chance of simple in-place upgrade from 8.2 to 8.3,
  but it's possible that 8.3 to 8.4 (or whenever we get around to
  squeezing NUMERIC) could otherwise be data-compatible.

- Dept. of second thoughts: fix loop in BgBufferSync so that the exit
  when bgwriter_lru_maxpages is exceeded leaves the loop variables in
  the expected state.  In the original coding, we'd fail to advance
  next_to_clean, causing that buffer to be probably-uselessly
  rechecked next time, and also have an off-by-one idea of the number
  of buffers scanned.

- Just-in-time background writing strategy.  This code avoids
  re-scanning buffers that cannot possibly need to be cleaned, and
  estimates how many buffers it should try to clean based on moving
  averages of recent allocation requests and density of reusable
  buffers.  The patch also adds a couple more columns to
  pg_stat_bgwriter to help measure the effectiveness of the bgwriter.
  Greg Smith, building on his own work and ideas from several other
  people, in particular a much older patch from Itagaki Takahiro.

- Simplify and rename some GUC variables, per various recent
  discussions.

Andrew Dunstan committed:

- Exclude contrib/tsearch2 from regression tests.

- Turn build and vcregress .bat files into pure one line wrappers for
  the perl scripts. Remove the now superfluous getregress.pl.

- Use proper search for contrib makefiles in vcregress.pl

- Fix typo in vcregress.pl

- In pgsql/src/backend/utils/mb/mbutils.c, add comments re text <->
  bytea internal equivalence in convert routines.

- In pgsql/src/backend/utils/mb/mbutils.c, use correct PG_GETARG macro
  in pg_convert.

- Remove "convert 'blah' using conversion_name" facility, because if
  it produces text it is an encoding hole and if not it's incompatible
  with the spec, whatever the spec means (which we're not sure about
  anyway).

== Rejected Patches (for now) ==

Petr (PJMODOS) Jelinek's patch which chklocale.c to pgportfiles in
Mkvcbuild the perl module.  Magnus Hagander had already applied a
similar one.

== Pending Patches ==

Petr (PJMODOS) Jelinek sent in two versions of a patch which implments
ALTER TYPE RENAME.

Hannes Eder sent in a patch to add __FUNCTION__ support for MS VC7.

Dave Page sent in three versions of a patch which adds BIO OpenSSL
where needed.

ITAGAKI Takahiro sent in two patches to clean up ecpg.

Jaime Casanova sent in a patch which merges relation_open_nowait with
relation_open.

Tom Lane sent a patch to undo the removal of dead-space tracking.

Pavel Stehule sent in another version of his PL/PSM patch.

Andrew Dunstan sent in a patch which implements a one-value pattern
cache for the multi-byte encoding case for ILIKE.

Brendan Jurd sent in a patch which adds a quote_qualified_identifier
along with regression tests.

Tom Raney sent in a patch to speed up creation of hash indexes.