PostgreSQL Weekly News | Nouvelles neuves de Postgres

Par N Bougain le 02/11/2007 - 02:28

Nouvelles hebdomadaires de Postgres - 28 Octobre 2007

Grâce à vos retours, une bêta 2 de la version 8.3 sortira sous peu. Soyez prêts à tester un peu plus encore !

Les nouveautés des produits dérivés

• MicroOLAP Database Designer 1.2.2 pour PostgreSQL a été publié :
  http://microolap.com/products/database/postgresql-designer/
• Npgsql 1.0.1 est sorti :
  http://pgfoundry.org/projects/npgsql/
• Debian a maintenant des paquets orafce :
  http://pgfoundry.org/projects/orafce/
• PgBouncer 1.1.1 (bugfix) est disponible :
  http://pgfoundry.org/projects/pgbouncer/
• pgpool-II 1.3 est sorti :
  http://pgfoundry.org/projects/pgpool/

Offres d'emplois autour de Postgres en octobre

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

Postgres Local

• La première réunion du PUG néo-zélandais aura lieu à Wellington, le 6 novembre 2007. Plus de détails à l'adresse :
  http://pgug.org.nz
• Jetez un œil sur le nouveau forum Postgres concernant les emplois germanophones :
  http://www.pg-forum.de/jobboerse-marktplatz/

Postgres dans les média

• Planet PostgreSQL:
  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 par l'équipe PostgreSQLFr.

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

Correctifs appliqués

Peter Eisentraut a commité :

• Translation updates.

Bruce Momjian a commité :

• Update release notes to current CVS.
• Add plpgsql doc example of RETURN NEXT. Ulrich Kroener
• Set Japanese FAQ character code to UTF-8. Jun Kuwamura
• Remove use of "Postgres" aliases in the FAQ in second-in-paragraph cases.
• Remove second-in-paragraph usage of "Postgres" from FAQ_DEV.

Alvaro Herrera a commité :

• Minor changes to autovacuum worker: change error handling so that it continues with the next table on schedule instead of exiting, in all cases instead of just on query cancel. Add a errcontext() line indicating the activity of the worker to the error message when it is cancelled. Change the WorkerInfo struct to contain a pointer to the worker's PGPROC instead of just the PID. Add forgotten post-auth delays, per Simon Riggs. Also to autovac launcher.
• In pgsql/src/bin/initdb/initdb.c, change Danish_Danmark to Danish_Denmark.
• Rearrange vacuum-related bits in PGPROC as a bitmask, to better support having several of them. Add two more flags: whether the process is executing an ANALYZE, and whether a vacuum is for Xid wraparound (which is obviously only set by autovacuum). Sneakily move the worker's recently-acquired PostAuthDelay to a more useful place.
• In pgsql/src/port/chklocale.c, add new encoding aliases for Solaris, per Zdenek Kotala.
• Use more real-world examples in the text search parser documentation (pgsql/doc/src/sgml/textsearch.sgml).
• In pgsql/src/backend/postmaster/autovacuum.c, extract catalog info for error reporting before an error actually happens. Also, remove redundant reset of for-wraparound PGPROC flag. Thanks to Tom Lane for noticing both bogosities.
• Allow an autovacuum worker to be interrupted automatically when it is found to be locking another process (except when it's working to prevent Xid wraparound problems). Simon Riggs

Magnus Hagander a commité :

• Use snprintf instead of wsprintf, and use getenv("APPDATA") instead of SHGetFolderPath. This removes the direct dependency on shell32.dll and user32.dll, which eats a lot of "desktop heap" for each backend that's started. The desktop heap is a very limited resource, causing backends to no longer start once it's been exhausted. We still have indirect depdendencies on user32.dll through third party libraries, but those can't easily be removed. Patch by Dave Page with editorializing by Tom Lane.
• Change win32 child-death tracking code to use a threadpool to wait for childprocess deaths instead of using one thread per child. This drastastically reduces the address space usage and should allow for more backends running. Also change the win32_waitpid functionality to use an IO Completion Port for queueing child death notices instead of using a fixed-size array.

Tom Lane a commité :

• Adjust ts_debug's output as per my proposal of yesterday: show the active dictionary and its output lexemes as separate columns, instead of smashing them into one text column, and lowercase the column names. Also, define the output rowtype using OUT parameters instead of a composite type, to be consistent with the other built-in functions.
• Clarify example of planner cost computation, per a suggestion from James Shaw. Also update a couple of examples to reflect 8.3's improved plan-printing code.
• Fix several bugs in tsvectorin, including crash due to uninitialized field and miscomputation of required palloc size. The crash could only occur if the input contained lexemes both with and without positions, which is probably not common in practice. The miscomputation would definitely result in wasted space. Also fix some inconsistent coding around alignment of strings and positions in a tsvector value; these errors could also lead to crashes given mixed with/without position data and a machine that's picky about alignment. And be more careful about checking for overflow of string offsets. Patch is only against HEAD --- I have not looked to see if same bugs are in back-branch contrib/tsearch2 code.
• Fix two-argument form of ts_rewrite() so it actually works for cases where a later rewrite rule should change a subtree modified by an earlier one. Per my gripe of a few days ago.
• Rename and slightly redefine the default text search parser's "word" categories, as per discussion. asciiword (formerly lword) is still ASCII-letters-only, and numword (formerly word) is still the most general mixed-alpha-and-digits case. But word (formerly nlword) is now any-group-of-letters-with-at-least-one-non-ASCII, rather than all-non-ASCII as before. This is no worse than before for parsing mixed Russian/English text, which seems to have been the design center for the original coding; and it should simplify matters for parsing most European languages. In particular it will not be necessary for any language to accept strings containing digits as being regular "words". The hyphenated-word categories are adjusted similarly.
• Make configure probe for the location of the <uuid.h> header file. Needed to accommodate different layout on some platforms (Debian for one). Heikki Linnakangas
• Remove the aggregate form of ts_rewrite(), since it doesn't work as desired if there are zero rows to aggregate over, and the API seems both conceptually and notationally ugly anyway. We should look for something that improves on the tsquery-and-text-SELECT version (which is also pretty ugly but at least it works...), but it seems that will take query infrastructure that doesn't exist today. (Hm, I wonder if there's anything in or near SQL2003 window functions that would help?) Per discussion.
• Set read_only = TRUE while evaluating input queries for ts_rewrite() and ts_stat(), per my recent suggestion. Also add a possibly-not-needed- but-can't-hurt check for NULL SPI_tuptable, before we try to dereference same.
• In pgsql/src/backend/access/heap/pruneheap.c, keep heap_page_prune from marking the buffer dirty when it didn't really change anything. Per report from Itagaki Takahiro. Fix by Pavan Deolasee.
• Fix UPDATE/DELETE WHERE CURRENT OF to support repeated update and update- then-delete on the current cursor row. The basic fix is that nodeTidscan.c has to apply heap_get_latest_tid() to the current-scan-TID obtained from the cursor query; this ensures we get the latest row version to work with. However, since that only works if the query plan is a TID scan, we also have to hack the planner to make sure only that type of plan will be selected. (Formerly, the planner might decide to apply a seqscan if the table is very small. This change is probably a Good Thing anyway, since it's hard to see how a seqscan could really win.) That means the execQual.c code to support CurrentOfExpr as a regular expression type is dead code, so replace it with just an elog(). Also, add regression tests covering these cases. Note that the added tests expose the fact that re-fetching an updated row misbehaves if the cursor used FOR UPDATE. That's an independent bug that should be fixed later. Per report from Dharmendra Goyal.
• Fix an error in make_outerjoininfo introduced by my patch of 30-Aug: the code neglected to test whether an outer join's join-condition actually refers to the lower outer join it is looking at. (The comment correctly described what was supposed to happen, but the code didn't do it...) This often resulted in adding an unnecessary constraint on the join order of the two outer joins, which was bad enough. However, it also seems to expose a performance problem in an older patch (from 15-Feb): once we've decided that there is a join ordering constraint, we will start trying clauseless joins between every combination of rels within the constraint, which pointlessly eats up lots of time and space if there are numerous rels below the outer join. That probably needs to be revisited :-(. Per gripe from Jakub Ouhrabka.
• In pgsql/doc/src/sgml/ref/fetch.sgml, remove obsolete statement that you can't update through a cursor.
• Disallow scrolling of FOR UPDATE/FOR SHARE cursors, so as to avoid problems in corner cases such as re-fetching a just-deleted row. We may be able to relax this someday, but let's find out how many people really care before we invest a lot of work in it. Per report from Heikki and subsequent discussion. While in the neighborhood, make the combination of INSENSITIVE and FOR UPDATE throw an error, since they are semantically incompatible. (Up to now we've accepted but just ignored the INSENSITIVE option of DECLARE CURSOR.)
• Tweak new error messages to match the actual syntax of DECLARE CURSOR. (Last night I copied-and-pasted from the WITH HOLD case, but that's wrong because of the bizarrely irregular syntax specified by the standard.)
• Fix ALTER SEQUENCE so that it does not affect the value of currval() for the sequence. Also, make setval() with is_called = false not affect the currval state, either. Per report from Kris Jurka that an implicit ALTER SEQUENCE OWNED BY unexpectedly caused currval() to become valid. Since this isn't 100% backwards compatible, it will go into HEAD only; I'll put a more limited patch into 8.2.
• In pgsql/src/backend/postmaster/autovacuum.c, fix memory management for new variables -- they must actually survive transaction end, in case we decide to do a vacuum analyze (which is done in two xacts).
• Ugly patch to make ALTER SEQUENCE OWNED BY not affect the currval() state of the sequence. Since OWNED BY never existed before 8.2, this seems unlikely to create any compatibility issues. Other forms of ALTER SEQUENCE continue to do what they did before, namely update currval to match the sequence's actual last_val. That seems wrong on consideration, but we'll not change it in a minor release --- 8.3 will make that fix.
• Make initdb's selection of default text search configuration depend only on the 'language' part of the locale name, ignoring the country code. We may need to be smarter later when there are more built-in configurations, but for now this is good enough and avoids having to bloat the table.
• Avoid including any backend-only stuff in the zic utility program. Per gripe from Zdenek Kotala, though not exactly his patch.
• Avoid having backend-only code compiled into ecpg. Per Zdenek Kotala.
• Change have_join_order_restriction() so that we do not force a clauseless join if either of the input relations can legally be joined to any other rels using join clauses. This avoids uselessly (and expensively) considering a lot of really stupid join paths when there is a join restriction with a large footprint, that is, lots of relations inside its LHS or RHS. My patch of 15-Feb-2007 had been causing the code to consider joining *every* combination of rels inside such a group, which is exponentially bad :-(. With this behavior, clauseless bushy joins will be done if necessary, but they'll be put off as long as possible. Per report from Jakub Ouhrabka. Backpatch to 8.2. We might someday want to backpatch to 8.1 as well, but 8.1 does not have the problem for OUTER JOIN nests, only for IN-clauses, so it's not clear anyone's very likely to hit it in practice; and the current patch doesn't apply cleanly to 8.1.
• In pgsql/doc/src/sgml/textsearch.sgml, minor wording improvements per suggestion from Jeff Davis. Also tweak hyphenated-word parser examples per earlier discussion with Alvaro.
• Stamp 8.3beta2.
• Avoid considering both sort directions as equally useful for merging. This doubles the planning workload for mergejoins while not actually accomplishing much. The only useful case is where one of the directions matches the query's ORDER BY request; therefore, put a thumb on the scales in that direction, and otherwise arbitrarily consider only the ASC direction. (This is a lot easier now than it would've been before 8.3, since we have more semantic knowledge embedded in PathKeys now.)
• In pgsql/src/backend/tsearch/wparser_def.c, add some rudimentary tracing code to the default text search parser, to help in debugging its state-machine rules. Const-ify all the constant tables. Minor other code cleanup, including using "token" rather than "lexeme" to describe the output strings.
• Change text search parsing rules for hyphenated words so that digit strings containing decimal points aren't considered part of a hyphenated word. Sync the hyphenated-word lookahead states with the subsequent part-by-part reparsing states so that we don't get different answers about how much text is part of the hyphenated word. Per my gripe of a few days ago.
• Fix a couple of issues with pg_dump's handling of inheritance child tables that have default expressions different from their parent. First, if the parent table's default expression has to be split out as a separate ALTER TABLE command, we need a dependency constraint to ensure that the child's command is given second. This is because the ALTER TABLE on the parent will propagate to the child. (We can't prevent that by using ONLY on the parent's command, since it's possible that other children exist that should receive the inherited default.) Second, if the child has a NULL default where the parent does not, we have to explicitly say DEFAULT NULL on the child in order for this state to be preserved after reload. (The latter actually doesn't work right because of a backend bug, but that is a separate issue.) Backpatch as far as 8.0. 7.x pg_dump has enough issues with altered tables (due to lack of dependency analysis) that trying to fix this one doesn't seem very productive.
• Make pg_dump and friends consistently report both the filename and the errno string when complaining of fopen failures. Per gripe from Bob Pawley, it's not always instantly obvious to the user which name we tried to open.
• Update FAQ_IRIX to reflect current realities about IRIX compilers.
• Ooops, fat-fingered last commit message. Should have mentioned updating FAQ_IRIX for information about buggy MIPSPro compiler version, now confirmed by Herve Boulouis.

Correctifs rejetés (à ce jour)

• Pas de déception cette semaine :-)

Correctifs en attente

• ITAGAKI Takahiro sent in a patch to fix locking in pgstattuple.
• Pavel Stehule sent in a patch to implement EXECUTE USING, which lets people send parameters to dynamically created SQL in a fashion analogous to PREPARE/EXECUTE in PL/PgSQL.
• Gokulakannan Somasun sent in two revisions of a patch which lets people add snapshot information to indexes.
• Heikki Linnakangas sent in a patch which adds some autoconf magic to fix uuid.h on Debian.
• Alvaro Herrera sent in two revisions of a patch which turns a few boolean vacuum flags in PGPROC into a bitmask.
• Zdenek Kotala sent in a patch intended to clean up some headers.
• Joe Conway sent in three revisions of a patch to fix crosstab in contrib/ for NULL-handling.
• Kenneth Marshall sent in a patch to implement the 2006 version of the hash function by Bob Jenkins.
  http://burtleburtle.net/bob/hash/doobs.html