Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en novembre

PostgreSQL Local

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. La version originale se trouve à l'adresse suivante : http://www.postgresql.org/message-id/20151101233927.GA7054@fetter.org

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, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

Correctifs appliqués

Ãlvaro Herrera pushed:

Robert Haas pushed:

Peter Eisentraut pushed:

Tom Lane pushed:

  • Docs: add example clarifying use of nested JSON containment. Show how this can be used in practice to make queries simpler and more flexible. Also, draw an explicit contrast to the existence operator, which doesn't work that way. Peter Geoghegan and Tom Lane http://git.postgresql.org/pg/commitdiff/23937a4253490bf0c06aef0b8658270176f52de4
  • Implement lookbehind constraints in our regular-expression engine. A lookbehind constraint is like a lookahead constraint in that it consumes no text; but it checks for existence (or nonexistence) of a match *ending* at the current point in the string, rather than one *starting* at the current point. This is a long-requested feature since it exists in many other regex libraries, but Henry Spencer had never got around to implementing it in the code we use. Just making it work is actually pretty trivial; but naive copying of the logic for lookahead constraints leads to code that often spends O(N^2) time to scan an N-character string, because we have to run the match engine from string start to the current probe point each time the constraint is checked. In typical use-cases a lookbehind constraint will be written at the start of the regex and hence will need to be checked at every character --- so O(N^2) work overall. To fix that, I introduced a third copy of the core DFA matching loop, paralleling the existing longest() and shortest() loops. This version, matchuntil(), can suspend and resume matching given a couple of pointers' worth of storage space. So we need only run it across the string once, stopping at each interesting probe point and then resuming to advance to the next one. I also put in an optimization that simplifies one-character lookahead and lookbehind constraints, such as "(?=x)" or "(?<!\w)", into AHEAD and BEHIND constraints, which already existed in the engine. This avoids the overhead of the LACON machinery entirely for these rather common cases. The net result is that lookbehind constraints run a factor of three or so slower than Perl's for multi-character constraints, but faster than Perl's for one-character constraints ... and they work fine for variable-length constraints, which Perl gives up on entirely. So that's not bad from a competitive perspective, and there's room for further optimization if anyone cares. (In reality, raw scan rate across a large input string is probably not that big a deal for Postgres usage anyway; so I'm happy if it's linear.) http://git.postgresql.org/pg/commitdiff/12c9a04008870c283931d6b3b648ee21bbc2cfda

Kevin Grittner pushed:

  • Fix serialization anomalies due to race conditions on INSERT. On insert the CheckForSerializableConflictIn() test was performed before the page(s) which were going to be modified had been locked (with an exclusive buffer content lock). If another process acquired a relation SIReadLock on the heap and scanned to a page on which an insert was going to occur before the page was so locked, a rw-conflict would be missed, which could allow a serialization anomaly to be missed. The window between the check and the page lock was small, so the bug was generally not noticed unless there was high concurrency with multiple processes inserting into the same table. This was reported by Peter Bailis as bug #11732, by Sean Chittenden as bug #13667, and by others. The race condition was eliminated in heap_insert() by moving the check down below the acquisition of the buffer lock, which had been the very next statement. Because of the loop locking and unlocking multiple buffers in heap_multi_insert() a check was added after all inserts were completed. The check before the start of the inserts was left because it might avoid a large amount of work to detect a serialization anomaly before performing the all of the inserts and the related WAL logging. While investigating this bug, other SSI bugs which were even harder to hit in practice were noticed and fixed, an unnecessary check (covered by another check, so redundant) was removed from heap_update(), and comments were improved. Back-patch to all supported branches. Kevin Grittner and Thomas Munro http://git.postgresql.org/pg/commitdiff/585e2a3b1a57cd7f763a9203e77563c729d6104e

Correctifs rejetés (à ce jour)

No one was disappointed this week :-)

Correctifs en attente

Zeus Kronion sent in a patch to fix an issue with parallel workers in pg_dump.

Haribabu Kommi sent in another revision of a patch to implement multi-tenancy with RLS.

Victor Wagner sent in another revision of a patch to implement failover at the libpq connect level.

Ashutosh Bapat sent in three more revisions of a patch to get sorted data from a foreign server.

Nathan Wagner and David Fetter sent in two revisions of a patch to add fortnights as a unit of time measurement.

Robert Haas sent in a patch to modify tqueue infrastructure to support transient record types.

Valery Popov sent in a patch to add a max_recursion_depth parameter to blunt the effects of out-of-control WITH RECURSIVE queries.

Michael Paquier sent in another revision of a patch to allow showing tuple data in the pageinspect contrib extension.

David Rowley sent in a patch to make tlist_matches_tupdesc() more efficient.

Marko Tiikkaja sent in a patch to add \pset true/false to psql.

Marko Tiikkaja sent in a patch to add a new aggregate, onlyvalue, which returns the single distinct non-NULL value from the input values and raises an exception if some number other than one distinct non-NULL value exists.

Rahila Syed sent in another revision of a patch to implement a vacuum progress checker.

Pavel Stěhule sent in another revision of a patch to implement ereport() in PL/PythonU.

Stas Kelvich sent in another revision of a patch to add KNN support to the cube contrib extension.

Alexander Korotkov sent in two more revisions of a patch to move PinBuffer and UnpinBuffer to atomics.

SAWADA Masahiko sent in two more revisions of a patch to add a "frozen" bit to the visibility map.

Amit Langote sent in another revision of a patch to implement declarative partitioning.

Pavel Stěhule sent in another revision of a patch to enhance the types usable in PL/pgsql.

Fabien COELHO sent in another revision of a patch to extend pgbench expressions with functions.

Jim Nasby sent in a patch to install config/missing.

Konstantin Knizhnik sent in a patch to add an extensible transaction manager API.

Marko Tiikkaja sent in a patch to allow COPY (query) to use INSERT/UPDATE/DELETE...RETURNING in addition to SELECT, TABLE, and VALUES.

Pavel Stěhule sent in another revision of a patch to add an optional --group-command parameter to psql, which lets it execute multiple commands from the shell command line.

Peter Geoghegan sent in a patch to correct a comment in strxfrm cache.

Alexander Lebedev sent in a patch to add support for box type in SP-GiST index.

Dean Rasheed sent in a patch to add trigonometric functions that take degree measurements as inputs.