Google hangout le 17 avril à 8h UTC intitulé "Postgres Performance Diagnostics: Busting that slow running SQL" (diagnostics de performance sous PostgreSQL : débusquer cet SQL paresseux). RSVP: https://plus.google.com/events/cm0roo9chi2s6p3afp679lhv1bk

Postgres Open 2014 aura lieu à Chicago, Illinois - USA, du 17 au 19 septembre. L'appel à conférenciers est lancé : http://postgresopen.org/2014/callforpapers/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en avril

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.

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.

(lien vers l'article original)

Correctifs appliqués

Heikki Linnakangas a poussé :

Robert Haas a poussé :

Tom Lane a poussé :

  • Add an in-core GiST index opclass for inet/cidr types. This operator class can accelerate subnet/supernet tests as well as btree-equivalent ordered comparisons. It also handles a new network operator inet && inet (overlaps, a/k/a "is supernet or subnet of"), which is expected to be useful in exclusion constraints. Ideally this opclass would be the default for GiST with inet/cidr data, but we can't mark it that way until we figure out how to do a more or less graceful transition from the current situation, in which the really-completely-bogus inet/cidr opclasses in contrib/btree_gist are marked as default. Having the opclass in core and not default is better than not having it at all, though. While at it, add new documentation sections to allow us to officially document GiST/GIN/SP-GiST opclasses, something there was never a clear place to do before. I filled these in with some simple tables listing the existing opclasses and the operators they support, but there's certainly scope to put more information there. Emre Hasegeli, reviewed by Andreas Karlsson, further hacking by me http://git.postgresql.org/pg/commitdiff/f23a5630ebc797219b62797f566dec9f65090e03
  • Create infrastructure for moving-aggregate optimization. Until now, when executing an aggregate function as a window function within a window with moving frame start (that is, any frame start mode except UNBOUNDED PRECEDING), we had to recalculate the aggregate from scratch each time the frame head moved. This patch allows an aggregate definition to include an alternate "moving aggregate" implementation that includes an inverse transition function for removing rows from the aggregate's running state. As long as this can be done successfully, runtime is proportional to the total number of input rows, rather than to the number of input rows times the average frame length. This commit includes the core infrastructure, documentation, and regression tests using user-defined aggregates. Follow-on commits will update some of the built-in aggregates to use this feature. David Rowley and Florian Pflug, reviewed by Dean Rasheed; additional hacking by me http://git.postgresql.org/pg/commitdiff/a9d9acbf219b9e96585779cd5f99d674d4ccba74
  • Provide moving-aggregate support for a bunch of numerical aggregates. First installment of the promised moving-aggregate support in built-in aggregates: count(), sum(), avg(), stddev() and variance() for assorted datatypes, though not for float4/float8. In passing, remove a 2001-vintage kluge in interval_accum(): interval array elements have been properly aligned since around 2003, but nobody remembered to take out this workaround. Also, fix a thinko in the opr_sanity tests for moving-aggregate catalog entries. David Rowley and Florian Pflug, reviewed by Dean Rasheed http://git.postgresql.org/pg/commitdiff/9d229f399e87d2ae7132c2e8feef317ce1479728
  • Provide moving-aggregate support for boolean aggregates. David Rowley and Florian Pflug, reviewed by Dean Rasheed http://git.postgresql.org/pg/commitdiff/d95425c8b9d3ea1681bd91b76ce73be95ca5ee21
  • Suppress compiler warning in new contrib/pg_trgm code. MSVC doesn't seem to like it when a constant initializer loses precision upon being assigned. David Rowley http://git.postgresql.org/pg/commitdiff/46a60abfe9fa13087dbbe15953c20df35f006968
  • Improve some O(N^2) behavior in window function evaluation. Repositioning the tuplestore seek pointer in window_gettupleslot() turns out to be a very significant expense when the window frame is sizable and the frame end can move. To fix, introduce a tuplestore function for skipping an arbitrary number of tuples in one call, parallel to the one we introduced for tuplesort objects in commit 8d65da1f. This reduces the cost of window_gettupleslot() to O(1) if the tuplestore has not spilled to disk. As in the previous commit, I didn't try to do any real optimization of tuplestore_skiptuples for the case where the tuplestore has spilled to disk. There is probably no practical way to get the cost to less than O(N) anyway, but perhaps someone can think of something later. Also fix PersistHoldablePortal() to make use of this API now that we have it. Based on a suggestion by Dean Rasheed, though this turns out not to look much like his patch. http://git.postgresql.org/pg/commitdiff/e0c91a7ff015fab0ccbb0f75b6819f29ae00295e

Michael Meskes a poussé :

Bruce Momjian a poussé :

Stephen Frost a poussé :

  • Make security barrier views automatically updatable. Views which are marked as security_barrier must have their quals applied before any user-defined quals are called, to prevent user-defined functions from being able to see rows which the security barrier view is intended to prevent them from seeing. Remove the restriction on security barrier views being automatically updatable by adding a new securityQuals list to the RTE structure which keeps track of the quals from security barrier views at each level, independently of the user-supplied quals. When RTEs are later discovered which have securityQuals populated, they are turned into subquery RTEs which are marked as security_barrier to prevent any user-supplied quals being pushed down (modulo LEAKPROOF quals). Dean Rasheed, reviewed by Craig Ringer, Simon Riggs, KaiGai Kohei http://git.postgresql.org/pg/commitdiff/842faa714c0454d67e523f5a0b6df6500e9bc1a5
  • Make a dedicated AlterTblSpcStmt production. Given that ALTER TABLESPACE has moved on from just existing for general purpose rename/owner changes, it deserves its own top-level production in the grammar. This also cleans up the RenameStmt to only ever be used for actual RENAMEs again- it really wasn't appropriate to hide non-RENAME productions under there. Noted by Alvaro Herrera. http://git.postgresql.org/pg/commitdiff/5f508b6dea19b66961c645bf5e5c427ac3af8359
  • Add ANALYZE into regression tests. Looks like we can end up with different plans happening on the buildfarm, which breaks the regression tests when we include EXPLAIN output (which is done in the regression tests for updatable security views, to ensure that the user-defined function isn't pushed down to a level where it could view the rows before the security quals are applied). This adds in ANALYZE to hopefully make the plans consistent. The ANALYZE ends up changing the original plan too, so the update looks bigger than it really is. The new plan looks perfectly valid, of course. http://git.postgresql.org/pg/commitdiff/b3e6593716efef901fcc847f33256c6b49958898

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Michael Paquier sent in two more revisions of a patch to include replication slot data in base backups.
  • Ian Lawrence Barwick sent in three revisions of a patch to add psql tab completion for event triggers.
  • Vaishnavi Prabakaran sent in a patch to add a new option in pg_basebackup to exclude pg_log files during base backup.
  • Rajeev Rastogi sent in two revisions of a patch to add autonomous transactions.
  • Etsuro Fujita sent in two revisions of a patch to improve the ALTER TABLE documentation.
  • Bruce Momjian sent in another revision of a patch to fix a socket issue on Windows.
  • Simon Riggs sent in a patch to implement ALTER TABLE ... SET reloptions.
  • Amit Kapila sent in a patch to fix a dsm invalid errcode issue.
  • Bruce Momjian sent in another revision of a patch to fix the oid display in psql's \d+.
  • Kyotaro HORIGUCHI sent in another revision of a patch to get more from indexes.
  • Sergey Muraviov and Gregory Stark traded patches to improve the display of wide tables in psql.
  • MauMau sent in another revision of a patch to fix an issue where pg_ctl always uses the same event source.
  • Andres Freund sent in a patch to add pinning_backends column to the pg_buffercache extension.
  • David Rowley sent in a patch to add a window function optimization which allows pushdowns of items matching PARTITION BY clauses.
  • Jan Wieck sent in two revisions of a patch to ensure that a snapshot's txids are unique.