== 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(at)fetter(dot)org, German to
pwn(at)pgug(dot)de(dot)

== 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(at)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.