PostgreSQL La base de donnees la plus sophistiquee au monde.

Nouvelles hebdomadaires de PostgreSQL - 27 novembre 2011

FOSDEM 2012 - PostgreSQL Devroom: l'appel à conférenciers est lancé (date limite de dépôt : 20 décembre 2011) :

Offres d'emplois autour de PostgreSQL en novembre

PostgreSQL Local

  • L'appel à conférenciers est lancé pour la PostgreSQL Session #3, programmée le 2 février 2012 à Paris. La date limite de dépôt est le 30 novembre 2011 et les conférenciers sélectionnés seront contactés avant le 14 décembre. Les propositions (en français ou anglais) doivent être envoyées à call-for-paper AT postgresql-sessions DOT org. Plus d'informations sur :
  • FOSDEM 2012 - PostgreSQL Devroom: l'appel à conférenciers est lancé (date limite de dépôt : 20 décembre 2011) :
  • La cinquième conférence annuelle "Prague PostgreSQL Developers Day", organisée pas le CSPUG (PUG Tchèque & Slovaque), aura lieu le 9 février 2012 à Prague. L'appel à conférenciers est lancé. Merci d'envoyer vos propositions, incluant le sujet, une estimation de la durée et vos coordonnées à l'adresse info CHEZ p2d2 POINT cz.
  • L'appel à conférenciers a été lancé pour le FLOSS UK, programmé du 20 au 22 mars 2012 à Edimbourg. La date limite de dépôt des candidatures est fixée au 18 novembre 2011 et les conférenciers sélectionnés seront informés avant le 25 novembre. Les propositions sont à envoyer à postgresql2012 AT flossuk POINT org. Plus d'informations via le lien suivant :

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA.

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

(lien vers l'article original)

Revues de code

Correctifs appliqués

Tom Lane a poussé :

  • Fix citext upgrade script to update derived copies of pg_type.typcollation. If the existing citext type has not merely been created, but used in any tables, then the upgrade script wasn't doing enough. We have to update attcollation for each citext table column, and indcollation for each citext index column, as well. Per report from Rudolf van der Leeden.
  • More code review for rangetypes patch. Fix up some infelicitous coding in DefineRange, and add some missing error checks. Rearrange operator strategy number assignments for GiST anyrange opclass so that they don't make such a mess of opr_sanity's table of operator names associated with different strategy numbers. Assign hopefully-temporary selectivity estimators to range operators that didn't have one --- poor as the estimates are, they're still a lot better than the default 0.5 estimate, and they'll shut up the opr_sanity test that wants to see selectivity estimators on all built-in operators.
  • Still more review for range-types patch. Per discussion, relax the range input/construction rules so that the only hard error is lower bound > upper bound. Cases where the lower bound is <= upper bound, but the range nonetheless normalizes to empty, are now permitted. Fix core dump in range_adjacent when bounds are infinite. Marginal cleanup of regression test cases, some more code commenting.
  • Improve implementation of range-contains-element tests. Implement these tests directly instead of constructing a singleton range and then applying range-contains. This saves a range serialize/deserialize cycle as well as a couple of redundant bound-comparison steps, and adds very little code on net. Remove elem_contained_by_range from the GiST opclass: it doesn't belong there because there is no way to use it in an index clause (where the indexed column would have to be on the left). Its commutator is in the opclass, and that's what counts.
  • Remove zero- and one-argument range constructor functions. Per discussion, the zero-argument forms aren't really worth the catalog space (just write 'empty' instead). The one-argument forms have some use, but they also have a serious problem with looking too much like functional cast notation; to the point where in many real use-cases, the parser would misinterpret what was wanted. Committing this as a separate patch, with the thought that we might want to revert part or all of it if we can think of some way around the cast ambiguity.
  • Remove user-selectable ANALYZE option for range types. It's not clear that a per-datatype typanalyze function would be any more useful than a generic typanalyze for ranges. What *is* clear is that letting unprivileged users select typanalyze functions is a crash risk or worse. So remove the option from CREATE TYPE AS RANGE, and instead put in a generic typanalyze function for ranges. The generic function does nothing as yet, but hopefully we'll improve that before 9.2 release.
  • Creator of a range type must have permission to call support functions. Since range types can be created by non-superusers, we need to consider their permissions. Ideally we'd check this when the type is used, not when it's created, but that seems like much more trouble than it's worth. The existing restriction that the support functions be immutable already prevents most cases where an unauthorized call to a function might be thought a security issue, and the fact that the user has no access to the results of the system's calls to subtype_diff closes off the other plausible reason for concern. So this check is basically pro-forma, but let's make it anyway.
  • Adjust range_adjacent to support different canonicalization rules. The original coding would not work for discrete ranges in which the canonicalization rule is to produce symmetric boundaries (either [] or () style), as noted by Jeff Davis. Florian Pflug pointed out that we could fix that by invoking the canonicalization function to see if the range "between" the two given ranges normalizes to empty. This implementation of Florian's idea is a tad slower than the original code, but only in the case where there actually is a canonicalization function --- if not, it's essentially the same logic as before.
  • Some more editing of the range-types documentation. Be more thorough about specifying the expectations for canonical and subtype_diff functions, and move that info to the same place.
  • Fix unsupported options in CREATE TABLE ... AS EXECUTE. The WITH [NO] DATA option was not supported, nor the ability to specify replacement column names; the former limitation wasn't even documented, as per recent complaint from Naoya Anzai. Fix by moving the responsibility for supporting these options into the executor. It actually takes less code this way ... catversion bump due to change in representation of IntoClause, which might affect stored rules.
  • Fix erroneous replay of GIN_UPDATE_META_PAGE WAL records. A simple thinko in ginRedoUpdateMetapage, namely failing to increment a loop counter, led to inserting records into the last pending-list page in the wrong order (the opposite of that intended). So far as I can tell, this would not upset the code that eventually flushes pending items into the main part of the GIN index. But it did break the code that searched the pending list for matches, resulting in transient failure to find matching entries during index lookups, as illustrated in bug #6307 from Maksym Boguk. Back-patch to 8.4 where the incorrect code was introduced.
  • Fix overly-aggressive and inconsistent quoting in OS X start script. Sidar Lopez, per bug #6310, with some additional improvements by me. Back-patch to 9.0, where the issue was introduced.
  • Make GiST index searches smarter about queries against empty ranges. In the cases where the result of the called proc is negated, we should explicitly test both inputs for empty, to ensure we'll never return "true" for an unsatisfiable query. In other cases we can rely on the called proc to say the right thing.
  • Use the proper macro to convert a bool to a Datum. The original coding was var->value = (Datum) state; which is bogus, and then in commit 2f0f7b4bce13e68394543728801ef011fd82fac6 it was "corrected" to var->value = PointerGetDatum(state); which is a faithful translation but still wrong. This seems purely cosmetic, though, so no need for a back-patch. Pavel Stehule
  • Improve GiST range-contained-by searches by adding a flag for empty ranges. In the original implementation, a range-contained-by search had to scan the entire index because an empty range could be lurking anywhere. Improve that by adding a flag to upper GiST entries that says whether the represented subtree contains any empty ranges. Also, make a simple mod to the penalty function to discourage empty ranges from getting pushed into subtrees without any. This needs more work, and the picksplit function should be taught about it too, but that code can be improved without causing an on-disk compatibility break; so we'll leave it for another day. Since we're breaking on-disk compatibility of range values anyway, I took the opportunity to reorganize the range flags bits; the unused RANGE_xB_NULL bits are now adjacent, which might open the door for using them in some other way later. In passing, remove the GiST range opclass entry for <>, which doesn't seem like it can really be indexed usefully. Alexander Korotkov, with some editorializing by Tom Lane.
  • Use IEEE infinity, not 1e10, for null-and-not-null case in gistpenalty(). Use of a randomly chosen large value was never exactly graceful, and now that there are penalty functions that are intentionally using infinity, it doesn't seem like a good idea for null-vs-not-null to be using something less.
  • Ensure that whole-row junk Vars are always of composite type. The EvalPlanQual machinery assumes that whole-row Vars generated for the outputs of non-table RTEs will be of composite types. However, for the case where the RTE is a function call returning a scalar type, we were doing the wrong thing, as a result of sharing code with a parser case where the function's scalar output is wanted. (Or at least, that's what that case has done historically; it does seem a bit inconsistent.) To fix, extend makeWholeRowVar's API so that it can support both use-cases. This fixes Belinda Cussen's report of crashes during concurrent execution of UPDATEs involving joins to the result of UNNEST() --- in READ COMMITTED mode, we'd run the EvalPlanQual machinery after a conflicting row update commits, and it was expecting to get a HeapTuple not a scalar datum from the "wholerowN" variable referencing the function RTE. Back-patch to 9.0 where the current EvalPlanQual implementation appeared. In 9.1 and up, this patch also fixes failure to attach the correct collation to the Var generated for a scalar-result case. An example: regression=# select upper(x.*) from textcat('ab', 'cd') x; ERROR: could not determine which collation to use for upper() function

Simon Riggs a poussé :

Peter Eisentraut a poussé :

Robert Haas a poussé :

  • Check for INSERT privileges in SELECT INTO / CREATE TABLE AS. In the normal course of events, this matters only if ALTER DEFAULT PRIVILEGES has been used to revoke default INSERT permission. Whether or not the new behavior is more or less likely to be what the user wants when dealing only with the built-in privilege facilities is arguable, but it's clearly better when using a loadable module such as sepgsql that may use the hook in ExecCheckRTPerms to enforce additional permissions checks. KaiGai Kohei, reviewed by Laurenz Albe
  • Move "hot" members of PGPROC into a separate PGXACT array. This speeds up snapshot-taking and reduces ProcArrayLock contention. Also, the PGPROC (and PGXACT) structures used by two-phase commit are now allocated as part of the main array, rather than in a separate array, and we keep ProcArray sorted in pointer order. These changes are intended to minimize the number of cache lines that must be pulled in to take a snapshot, and testing shows a substantial increase in performance on both read and write workloads at high concurrencies. Pavan Deolasee, Heikki Linnakangas, Robert Haas

Bruce Momjian a poussé :

Heikki Linnakangas a poussé :

Alvaro Herrera a poussé :

Andrew Dunstan a poussé :

Correctifs rejetés (à ce jour)

  • Pas de déception cette semaine :-)

Correctifs en attente

  • Mark Kirkwood sent in two revisions of a patch to allow renaming a database which has backends connected to it.
  • Peter Geoghegan sent in four more revisions of a patch to inline comparators as a performance optimization.
  • Alexander Korotkov sent in a WIP patch to allow index support for regex operators.
  • Jan Urbanski sent in another revision of the patch to add cursor support to PL/PythonU.
  • Pavel Stehule sent in a WIP patch to enable better support for debugging overloaded functions in PL/pgsql.
  • Lars Kanis sent in two revisions of a patch to fix some infelicities in certain versions of MSVC.
  • Pavel Stehule sent in a PoC patch to use errcontext for custom exceptions in PL/pgsql.
  • Andrew Dunstan sent in another revision of a patch to add a \setenv command to psql.
  • Dimitri Fontaine sent in another revision of the patch to add command triggers.
  • Peter Eisentraut sent in a patch to fix error reports in vpath builds.
  • Pavel Stehule sent in another revision of the patch to add CHECK FUNCTION and CHECK TRIGGER functionality.
  • Andres Freund and Pavan Deolasee traded patches to avoid unneeded computation of snapshots.
  • Peter Eisentraut sent in a patch to allow psql to report the line number on which an error occurred when reading from stdin.
  • Ants Aasma sent in a patch to implement timing of shared buffer fills and per relation stats collection of said timings. Buffer flushes are timed as well but aren't exposed per table because of difficulty of correctly attributing them.

pgwn/27_novembre_2011.txt · Dernière modification: 2011/12/04 18:54 par buggy