PostgreSQL Weekly News | Nouvelles neuves de Postgres

Par N Bougain le 04/10/2007 - 01:51

Nouvelles hebdomadaires de Postgres - 30 Septembre 2007

Restez branchés ! Sortie de la bêta 1 de Postgres 8.3 cette semaine !

Les nouveautés des produits dérivés

• Le système multi-maîtres asynchrones Bucardo 3.0.6 est sorti :
  http://bucardo.org/
• PostgreSQL Maestro 7.9 est sorti :
  http://www.sqlmaestro.com/products/postgresql/maestro/
• Pg::Snapshot et Materialized Views sont disponibles en version 0.3.0a :
  http://pgfoundry.org/projects/snapshot/
• PgPool-II 1.2.1 est disponible :
  http://pgfoundry.org/projects/pgpool/

Offres d'emplois autour de Postgres en septembre

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

Postgres Local

• La PostgreSQL Conference Fall 2007 aura lieu le 20 octobre à l'Université d'État de Portland (Oregon, USA) :
  http://www.postgresqlconference.org/
• Simon Riggs donnera une conférence sur les bases de données et le Web, le 16 octobre à Londres :
  http://www.ukuug.org/events/seminars/databases/booking/

Postgres dans les média

• Planet Postgres:
  http://www.planetpostgresql.org/
• General Bits, Archives et ses manchettes occasionnelles :
  http://www.varlena.com/GeneralBits/

Postgres Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. La traduction française est effectuée religieusement par l'équipe PostgreSQLFr.

(Pour plus d'informations sur le récent changement de nom PostgreSQL / Postgres, vous pouvez consulter les derniers ajouts de la FAQ (cf correctifs de cette semaine) ou survoler la mailing-list pgsql-advocacy)

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Envoyez-les en anglais à david (a) fetter.org ou en allemand à pwn (a) pgug.de.

Correctifs appliqués

Bruce Momjian a commité :

• Renommage de "PostgreSQL" en "Postgres" à 3 emplacements dans FAQ_DEV.
• Renommage de "PostgreSQL" en "Postgres" à 7 emplacements dans la FAQ.
• 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"
• Add to TODO: "Implement Boyer-Moore searching in strpos()"
• Document that libpq's PQntuples() might overflow.

Magnus Hagander a commité :

• 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 a commité :

• 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 a commité :

• 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.

Peter Eisentraut a commité :

• Small string tweaks.

Alvaro Herrera a commité :

• 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 a commité :

• 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 a commité :

• 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).

Correctifs rejetés (à ce jour)

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

Correctifs en attente

• 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.