La conférence PGDay UK aura lieu le 7 juillet 2015 – elle vise les membres de la communauté PostgreSQL anglaise. L'appel à conférenciers expire le 13 avril : http://www.postgresqlusergroup.org.uk

L'appel à conférenciers pour le PostgresOpen 2015, programmé à Dallas (Texas) du 16 au 18 septembre, a été lancé : http://2015.postgresopen.org/callforpapers/

[ndt: 4e rendez-vous du PLUG (Lyon) le 15 avril, avec une présentation de PoWA et des techniques de détection d'index manquants : http://www.meetup.com/PostgreSQL-User-Group-Lyon/events/221188759/]

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

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

Tom Lane a poussé :

  • Replace insertion sort in contrib/intarray with qsort(). It's all very well to claim that a simplistic sort is fast in easy cases, but O(N^2) in the worst case is not good ... especially if the worst case is as easy to hit as "descending order input". Replace that bit with our standard qsort. Per bug #12866 from Maksym Boguk. Back-patch to all active branches. http://git.postgresql.org/pg/commitdiff/8d1f239003d0245dda636dfa6cf0add13bee69d6
  • Allow foreign tables to participate in inheritance. Foreign tables can now be inheritance children, or parents. Much of the system was already ready for this, but we had to fix a few things of course, mostly in the area of planner and executor handling of row locks. As side effects of this, allow foreign tables to have NOT VALID CHECK constraints (and hence to accept ALTER ... VALIDATE CONSTRAINT), and to accept ALTER SET STORAGE and ALTER SET WITH/WITHOUT OIDS. Continuing to disallow these things would've required bizarre and inconsistent special cases in inheritance behavior. Since foreign tables don't enforce CHECK constraints anyway, a NOT VALID one is a complete no-op, but that doesn't mean we shouldn't allow it. And it's possible that some FDWs might have use for SET STORAGE or SET WITH OIDS, though doubtless they will be no-ops for most. An additional change in support of this is that when a ModifyTable node has multiple target tables, they will all now be explicitly identified in EXPLAIN output, for example: Update on pt1 (cost=0.00..321.05 rows=3541 width=46) Update on pt1 Foreign Update on ft1 Foreign Update on ft2 Update on child3 -> Seq Scan on pt1 (cost=0.00..0.00 rows=1 width=46) -> Foreign Scan on ft1 (cost=100.00..148.03 rows=1170 width=46) -> Foreign Scan on ft2 (cost=100.00..148.03 rows=1170 width=46) -> Seq Scan on child3 (cost=0.00..25.00 rows=1200 width=46) This was done mainly to provide an unambiguous place to attach "Remote SQL" fields, but it is useful for inherited updates even when no foreign tables are involved. Shigeru Hanada and Etsuro Fujita, reviewed by Ashutosh Bapat and Kyotaro Horiguchi, some additional hacking by me http://git.postgresql.org/pg/commitdiff/cb1ca4d800621dcae67ca6c799006de99fa4f0a5

Álvaro Herrera a poussé :

  • Fix out-of-array-bounds compiler warning. Since the array length check is using a post-increment operator, the compiler complains that there's a potential write to one element beyond the end of the array. This is not possible currently: the only path to this function is through pg_get_object_address(), which already verifies that the input array is no more than two elements in length. Still, a bug is a bug. No idea why my compiler doesn't complain about this ... Pointed out by Dead Rasheed and Peter Eisentraut http://git.postgresql.org/pg/commitdiff/a190738457353ddb60743e45972f6fe50a75ee77
  • Support opfamily members in get_object_address. In the spirit of 890192e99af and 4464303405f: have get_object_address understand individual pg_amop and pg_amproc objects. There is no way to refer to such objects directly in the grammar -- rather, they are almost always considered an integral part of the opfamily that contains them. (The only case that deals with them individually is ALTER OPERATOR FAMILY ADD/DROP, which carries the opfamily address separately and thus does not need it to be part of each added/dropped element's address.) In event triggers it becomes possible to become involved with individual amop/amproc elements, and this commit enables pg_get_object_address to do so as well. To make the overall coding simpler, this commit also slightly changes the get_object_address representation for opclasses and opfamilies: instead of having the AM name in the objargs array, I moved it as the first element of the objnames array. This enables the new code to use objargs for the type names used by pg_amop and pg_amproc. Reviewed by: Stephen Frost http://git.postgresql.org/pg/commitdiff/a61fd5334eb1040d0dcec0368702398a5b49152c
  • Rationalize vacuuming options and parameters. We were involving the parser too much in setting up initial vacuuming parameters. This patch moves that responsibility elsewhere to simplify code, and also to make future additions easier. To do this, create a new struct VacuumParams which is filled just prior to vacuum execution, instead of at parse time; for user-invoked vacuuming this is set up in a new function ExecVacuum, while autovacuum sets it up by itself. While at it, add a new member VACOPT_SKIPTOAST to enum VacuumOption, only set by autovacuum, which is used to disable vacuuming of the toast table instead of the old do_toast parameter; this relieves the argument list of vacuum() and some callees a bit. This partially makes up for having added more arguments in an effort to avoid having autovacuum from constructing a VacuumStmt parse node. Author: Michael Paquier. Some tweaks by Álvaro Reviewed by: Robert Haas, Stephen Frost, Álvaro Herrera http://git.postgresql.org/pg/commitdiff/0d831389749a3baaced7b984205b9894a82444b9
  • Setup cursor position for schema-qualified elements. This makes any errors thrown while looking up such schemas report the position of the error. Author: Ryan Kelly Reviewed by: Jeevan Chalke, Tom Lane http://git.postgresql.org/pg/commitdiff/b8d226b4f9691c7afb986dbaaf3f6ff7b203d1b5
  • Install shared libraries to bin/ in Windows under MSVC. Since commit cb4a3b04 we were already doing this for the Cygwin/mingw toolchains, but MSVC had not been updated to do it. At Install.pm time, the Makefile (or GNUmakefile) is inspected, and if a line matching SO_MAJOR_VERSION is found (indicating a shared library is being built), then files with the .dll extension are set to be installed in bin/ rather than lib/, while files with .lib extension are installed in lib/. This makes the MSVC toolchain up to date with cygwin/mingw. This removes ad-hoc hacks that were copying files into bin/ or lib/ manually (libpq.dll in particular was already being copied into bin). So while this is a rather ugly kludge, it's still cleaner than what was there before. Author: Michael Paquier Reviewed by: Asif Naeem http://git.postgresql.org/pg/commitdiff/f9dead5624c63b009fc04229c1e5f660436b747b
  • array_offset() and array_offsets(). These functions return the offset position or positions of a value in an array. Author: Pavel Stěhule Reviewed by: Jim Nasby http://git.postgresql.org/pg/commitdiff/13dbc7a824b3f905904cab51840d37f31a07a9ef

Andres Freund a poussé :

  • Remove docs missed in 51c11a7025. Somehow I misresolved a merge conflict when forward porting Petr's patch leading to a section of the docs remaining... Thankfully Fujii spotted my mistake. http://git.postgresql.org/pg/commitdiff/4559167c6b75be334fabad70d7cc03a38a08d494
  • Use 128-bit math to accelerate some aggregation functions. On platforms where we support 128bit integers, use them to implement faster transition functions for sum(int8), avg(int8), var_*(int2/int4),stdev_*(int2/int4). Where not supported continue to use numeric as a transition type. In some synthetic benchmarks this has been shown to provide significant speedups. Bumps catversion. Discussion: 544BB5F1.50709@proxel.se Author: Andreas Karlsson Reviewed-By: Peter Geoghegan, Petr Jelinek, Andres Freund, Oskari Saarenmaa, David Rowley http://git.postgresql.org/pg/commitdiff/959277a4f579da5243968c750069570a58e92b38
  • Add, optional, support for 128bit integers. We will, for the foreseeable future, not expose 128 bit datatypes to SQL. But being able to use 128bit math will allow us, in a later patch, to use 128bit accumulators for some aggregates; leading to noticeable speedups over using numeric. So far we only detect a gcc/clang extension that supports 128bit math, but no 128bit literals, and no *printf support. We might want to expand this in the future to further compilers; if there are any that that provide similar support. Discussion: 544BB5F1.50709@proxel.se Author: Andreas Karlsson, with significant editorializing by me Reviewed-By: Peter Geoghegan, Oskari Saarenmaa http://git.postgresql.org/pg/commitdiff/8122e1437e332e156d971a0274879b0ee76e488a
  • Fix minor copy & pasto in the int128 accumulator patch. It's unlikely that using PG_GETARG_INT16 instead of PG_GETARG_INT32 in this pace can cause actual problems, but this still should be fixed. http://git.postgresql.org/pg/commitdiff/59b0a98af07cf8decfe94739f92bf18ebb34ffc6

Bruce Momjian a poussé :

Robert Haas a poussé :

  • Fix status reporting for terminated bgworkers that were never started. Previously, GetBackgroundWorkerPid() would return BGWH_NOT_YET_STARTED if the slot used for the worker registration had not been reused by unrelated activity, and BGWH_STOPPED if it had. Either way, a process that had requested notification when the state of one of its background workers changed did not receive such notifications. Fix things so that GetBackgroundWorkerPid() always returns BGWH_STOPPED in this situation, so that we do not erroneously give waiters the impression that the worker will eventually be started; and send notifications just as we would if the process terminated after having been started, so that it's possible to wait for the postmaster to process a worker termination request without polling. Discovered by Amit Kapila during testing of parallel sequential scan. Analysis and fix by me. Back-patch to 9.4; there may not be anyone relying on this interface yet, but if anyone is, the new behavior is a clear improvement. http://git.postgresql.org/pg/commitdiff/bf740ce9e5d82612889d131f34c079215973ca00
  • Add flags argument to dsm_create. Right now, there's only one flag, DSM_CREATE_NULL_IF_MAXSEGMENTS, which suppresses the error that would normally be thrown when the maximum number of segments already exists, instead returning NULL. It might be useful to add more flags in the future, such as one to ignore allocation errors, but I haven't done that here. http://git.postgresql.org/pg/commitdiff/12968cf4085409c50f70c6643d92befdb34008f6

Stephen Frost a poussé :

  • GetUserId() changes to has_privs_of_role(). The pg_stat and pg_signal-related functions have been using GetUserId() instead of has_privs_of_role() for checking if the current user should be able to see details in pg_stat_activity or signal other processes, requiring a user to do 'SET ROLE' for inheirited roles for a permissions check, unlike other permissions checks. This patch changes that behavior to, instead, act like most other permission checks and use has_privs_of_role(), removing the 'SET ROLE' need. Documentation and error messages updated accordingly. Per discussion with Alvaro, Peter, Adam (though not using Adam's patch), and Robert. Reviewed by Jeevan Chalke. http://git.postgresql.org/pg/commitdiff/bf038899965263dbc4aef2b43c8fdfe6f49b788f

Peter Eisentraut a poussé :

Heikki Linnakangas a poussé :

  • Make pg_xlogdump MSVC build work more like others. Instead of copying xlogreader.c and *desc.c files into the source directory, build them where they are. That's what we do for other binaries that need to compile and link in files from elsewhere in the source tree. The commit history suggests that it was done this way because of issues with older versions of MSVC. I think this should work, but we'll see if the buildfarm complains. http://git.postgresql.org/pg/commitdiff/1933a5bbc883fd2697c42d82ae12f2d585559b23

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Kyotaro HORIGUCHI sent in another revision of a patch to reduce pinning in btree indexes.
  • Kaigai Kouhei sent in two more revisions of a patch to add custom/foreign join APIs.
  • David Rowley sent in another revision of a patch to allow removing inner JOINs under certain conditions.
  • Dean Rasheed sent in another revision of a patch to improve RLS qual pushdown.
  • Álvaro Herrera sent in another revision of a flock of patches to add deparsing utility commands.
  • Adam Brightwell sent in a patch to fix a regression test for sepgsql.
  • Michael Paquier sent in another revision of a patch to add in-core regression tests for recovery.
  • Álvaro Herrera sent in another revision of a patch to add a missing type_schema hint.
  • Álvaro Herrera sent in another revision of a patch to move freeze parameters of VacuumStmt into a separate code path.
  • Robert Haas sent in three more revisions of a patch to add parallel mode and parallel contexts.
  • Julien Tachoires and Andreas Karlsson traded patches to allow toast tables to be moved to a different tablespace.
  • Bruce Momjian and Tom Lane traded patches to prevent post-commit interrupts.
  • Robert Haas sent in another revision of a patch to allow testing parallel safety.
  • Amit Kapila sent in another revision of a patch to implement parallel sequential scan.
  • Michael Paquier sent in another revision of a patch to implement table-level log_autovacuum_min_duration.
  • Michael Paquier sent in a patch to fix an example of a variable referencing itself in example of pgbench.sgml.
  • David Christensen sent in two revisions of a patch to add a two-arg current_setting() with fallback.
  • Max Filippov sent in a patch to compare the linker and compiler outputs in the case of pthread with their default output.
  • Bruce Momjian sent in a patch to document the fact that repeatable read and serializable transactions see data committed after transaction start.
  • Kyotaro HORIGUCHI sent in another revision of a patch to add shared infrastructure and functional dependencies for the upcoming multivariate statistics feature.
  • Bruce Momjian sent in another revision of a patch to change pg_ctl's default shutdown mode from "smart" to "fast."
  • Bruce Momjian sent in four revisions of a patch to simplify examples of dynamic SQL.
  • Bruce Momjian sent in a patch to remove an extra VACUUM in initdb.
  • Bruce Momjian sent in two more revisions of a patch to enable asciidoc as a psql output format.
  • David Wheeler sent in another revision of a patch to add launchd support.
  • Peter Geoghegan sent in two more revisions of a patch to add sort support for numerics.
  • Bruce Momjian sent in a patch to document more clearly the limitations of NUMERIC.
  • David Rowley sent in another revision of a patch to improve performance for joins where outer side is unique.
  • Peter Eisentraut sent in another revision of a patch to add TRANSFORMs.
  • Tomas Vondra sent in a patch to add two new log_line_prefix escape sequences - %T and %M, doing the same thing as %t and %m, but formatting the value as a number.
  • Andrew (RhodiumToad) Gierth sent in two more revisions of a patch to standardize INT64_MIN INT64_MAX UINT64_MAX.
  • Fabien COELHO sent in a patch to fix pgbench --progress report under (very) low rate.
  • Amit Khandekar sent in a patch to reset background worker crash state.
  • Dmitry Voronin sent in a patch to add some new functions to th SSL contrib module.
  • Pavel Stehule sent in another revision of a patch to add an ASSERT statement to SQL.