PostgreSQL 10 Beta 4 disponible. À vos tests ! https://www.postgresql.org/about/news/1776/

PostgreSQL 9.6.5, 9.5.9, 9.4.14, 9.3.19 et 9.2.23 ont été publiées. Mettez à jour dès que possible : https://www.postgresql.org/about/news/1777/

Session PostgreSQL le 17 novembre 2017 à Paris. L'appel à conférenciers est lancé jusqu'au 30 septembre à l'adresse call-for-paper AT postgresql-sessions POINT org. Merci d'inclure ce qui serait pertinent parmi : nom, prénom, pseudo twitter, nom de l'entreprise, courte biographie (contributions à la communauté PostgreSQL), titre et résumé de la présentation, et tout besoin particulier.

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en septembre

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/20170904023717.GA31900@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

Peter Eisentraut pushed:

Tom Lane pushed:

  • Fix over-aggressive sanity check in misc_sanity.sql. Fix thinko in commit 8be8510cf: it's okay to have dbid == 0 in normal (non-pin) entries in pg_shdepend, because global objects such as databases are entered that way. The test would pass so long as it was run in a cluster containing no databases/tablespaces owned by, or granted to, roles other than the bootstrap superuser. That's the expected situation for "make check", but for "make installcheck", not so much. Reported by Ryan Murphy. Discussion: https://postgr.es/m/CAHeEsBc6EQe0mxGBKDXAwJbntgfvoAd5MQC-5362SmC3Tng_6g@mail.gmail.com https://git.postgresql.org/pg/commitdiff/95e28b7f0c9e30cb636f58b5aa351e5f8ae3a473
  • Doc: adjust release-note credit for parallel pg_restore fix. Discussion: https://postgr.es/m/CAFcNs+pJ6_Ud-zg3vY_Y0mzfESdM34Humt8avKrAKq_H+v18Cg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/dc4f356b4470bcdcaf1d0fb68444683206eeba22
  • Release notes for 9.6.5, 9.5.9, 9.4.14, 9.3.19, 9.2.23. https://git.postgresql.org/pg/commitdiff/f97c55c708a6fbcb675be6fbb94af3038733dfa4
  • Improve docs about numeric formatting patterns (to_char/to_number). The explanation about "0" versus "9" format characters was confusing and arguably wrong; the discussion of sign handling wasn't very good either. Notably, while it's accurate to say that "FM" strips leading zeroes in date/time values, what it really does with numeric values is to strip *trailing* zeroes, and then only if you wrote "9" rather than "0". Per gripes from Erwin Brandstetter. Discussion: https://postgr.es/m/CAGHENJ7jgRbTn6nf48xNZ=FHgL2WQ4X8mYsUAU57f-vq8PubEw@mail.gmail.com Discussion: https://postgr.es/m/CAGHENJ45ymd=GOCu1vwV9u7GmCR80_5tW0fP9C_gJKbruGMHvQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ce5dcf54b942a469194ae390730f803b3f3fb928
  • Teach libpq to detect integer overflow in the row count of a PGresult. Adding more than 1 billion rows to a PGresult would overflow its ntups and tupArrSize fields, leading to client crashes. It'd be desirable to use wider fields on 64-bit machines, but because all of libpq's external APIs use plain "int" for row counters, that's going to be hard to accomplish without an ABI break. Given the lack of complaints so far, and the general pain that would be involved in using such huge PGresults, let's settle for just preventing the overflow and reporting a useful error message if it does happen. Also, for a couple more lines of code we can increase the threshold of trouble from INT_MAX/2 to INT_MAX rows. To do that, refactor pqAddTuple() to allow returning an error message that replaces the default assumption that it failed because of out-of-memory. Along the way, fix PQsetvalue() so that it reports all failures via pqInternalNotice(). It already did so in the case of bad field number, but neglected to report anything for other error causes. Because of the potential for crashes, this seems like a back-patchable bug fix, despite the lack of field reports. Michael Paquier, per a complaint from Igor Korot. Discussion: https://postgr.es/m/CA+FnnTxyLWyjY1goewmJNxC==HQCCF4fKkoCTa9qR36oRAHDPw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2e70d6b5e99b7e7b53336b1838f869bbea1b5024
  • Doc: document libpq's restriction to INT_MAX rows in a PGresult. As long as PQntuples, PQgetvalue, etc, use "int" for row numbers, we're pretty much stuck with this limitation. The documentation formerly stated that the result of PQntuples "might overflow on 32-bit operating systems", which is just nonsense: that's not where the overflow would happen, and if you did reach an overflow it would not be on a 32-bit machine, because you'd have OOM'd long since. Discussion: https://postgr.es/m/CA+FnnTxyLWyjY1goewmJNxC==HQCCF4fKkoCTa9qR36oRAHDPw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6cbee65eee20c144bb4de169c6802f20e76785b0
  • Force rescanning of parallel-aware scan nodes below a Gather[Merge]. The ExecReScan machinery contains various optimizations for postponing or skipping rescans of plan subtrees; for example a HashAgg node may conclude that it can re-use the table it built before, instead of re-reading its input subtree. But that is wrong if the input contains a parallel-aware table scan node, since the portion of the table scanned by the leader process is likely to vary from one rescan to the next. This explains the timing-dependent buildfarm failures we saw after commit a2b70c89c. The established mechanism for showing that a plan node's output is potentially variable is to mark it as depending on some runtime Param. Hence, to fix this, invent a dummy Param (one that has a PARAM_EXEC parameter number, but carries no actual value) associated with each Gather or GatherMerge node, mark parallel-aware nodes below that node as dependent on that Param, and arrange for ExecReScanGather[Merge] to flag that Param as changed whenever the Gather[Merge] node is rescanned. This solution breaks an undocumented assumption made by the parallel executor logic, namely that all rescans of nodes below a Gather[Merge] will happen synchronously during the ReScan of the top node itself. But that's fundamentally contrary to the design of the ExecReScan code, and so was doomed to fail someday anyway (even if you want to argue that the bug being fixed here wasn't a failure of that assumption). A follow-on patch will address that issue. In the meantime, the worst that's expected to happen is that given very bad timing luck, the leader might have to do all the work during a rescan, because workers think they have nothing to do, if they are able to start up before the eventual ReScan of the leader's parallel-aware table scan node has reset the shared scan state. Although this problem exists in 9.6, there does not seem to be any way for it to manifest there. Without GatherMerge, it seems that a plan tree that has a rescan-short-circuiting node below Gather will always also have one above it that will short-circuit in the same cases, preventing the Gather from being rescanned. Hence we won't take the risk of back-patching this change into 9.6. But v10 needs it. Discussion: https://postgr.es/m/CAA4eK1JkByysFJNh9M349u_nNjqETuEnY_y1VUc_kJiU0bxtaQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/7df2c1f8daeb361133ac8bdeaf59ceb0484e315a
  • Restore test case from a2b70c89ca1a5fcf6181d3c777d82e7b83d2de1b. Revert the reversion commits a20aac890 and 9b644745c. In the wake of commit 7df2c1f8d, we should get stable buildfarm results from this test; if not, I'd like to know sooner not later. Discussion: https://postgr.es/m/CAA4eK1JkByysFJNh9M349u_nNjqETuEnY_y1VUc_kJiU0bxtaQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6c2c5bea3cec4c874d1ee225bb6e222055c03d75
  • Separate reinitialization of shared parallel-scan state from ExecReScan. Previously, the parallel executor logic did reinitialization of shared state within the ExecReScan code for parallel-aware scan nodes. This is problematic, because it means that the ExecReScan call has to occur synchronously (ie, during the parent Gather node's ReScan call). That is swimming very much against the tide so far as the ExecReScan machinery is concerned; the fact that it works at all today depends on a lot of fragile assumptions, such as that no plan node between Gather and a parallel-aware scan node is parameterized. Another objection is that because ExecReScan might be called in workers as well as the leader, hacky extra tests are needed in some places to prevent unwanted shared-state resets. Hence, let's separate this code into two functions, a ReInitializeDSM call and the ReScan call proper. ReInitializeDSM is called only in the leader and is guaranteed to run before we start new workers. ReScan is returned to its traditional function of resetting only local state, which means that ExecReScan's usual habits of delaying or eliminating child rescan calls are safe again. As with the preceding commit 7df2c1f8d, it doesn't seem to be necessary to make these changes in 9.6, which is a good thing because the FDW and CustomScan APIs are impacted. Discussion: https://postgr.es/m/CAA4eK1JkByysFJNh9M349u_nNjqETuEnY_y1VUc_kJiU0bxtaQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/41b0dd987d44089dc48e9c70024277e253b396b7
  • Code review for nodeGatherMerge.c. Comment the fields of GatherMergeState, and organize them a bit more sensibly. Comment GMReaderTupleBuffer more usefully too. Improve assorted other comments that were obsolete or just not very good English. Get rid of the use of a GMReaderTupleBuffer for the leader process; that was confusing, since only the "done" field was used, and that in a way redundant with need_to_scan_locally. In gather_merge_init, avoid calling load_tuple_array for already-known-exhausted workers. I'm not sure if there's a live bug there, but the case is unlikely to be well tested due to timing considerations. Remove some useless code, such as duplicating the tts_isempty test done by TupIsNull. Remove useless initialization of ps.qual, replacing that with an assertion that we have no qual to check. (If we did, the code would fail to check it.) Avoid applying heap_copytuple to a null tuple. While that fails to crash, it's confusing and it makes the code less legible not more so IMO. Propagate a couple of these changes into nodeGather.c, as well. Back-patch to v10, partly because of the possibility that the gather_merge_init change is fixing a live bug, but mostly to keep the branches in sync to ease future bug fixes. https://git.postgresql.org/pg/commitdiff/04e9678614ec64ad9043174ac99a25b1dc45233a
  • Improve code coverage of select_parallel test. Make sure that rescans of parallel indexscans are tested. Per code coverage report. https://git.postgresql.org/pg/commitdiff/4b1dd62a257a469f92fef4f4cce37beab6c0b98b
  • Clean up shm_mq cleanup. The logic around shm_mq_detach was a few bricks shy of a load, because (contrary to the comments for shm_mq_attach) all it did was update the shared shm_mq state. That left us leaking a bit of process-local memory, but much worse, the on_dsm_detach callback for shm_mq_detach was still armed. That means that whenever we ultimately detach from the DSM segment, we'd run shm_mq_detach again for already-detached, possibly long-dead queues. This accidentally fails to fail today, because we only ever re-use a shm_mq's memory for another shm_mq, and multiple detach attempts on the last such shm_mq are fairly harmless. But it's gonna bite us someday, so let's clean it up. To do that, change shm_mq_detach's API so it takes a shm_mq_handle not the underlying shm_mq. This makes the callers simpler in most cases anyway. Also fix a few places in parallel.c that were just pfree'ing the handle structs rather than doing proper cleanup. Back-patch to v10 because of the risk that the revenant shm_mq_detach callbacks would cause a live bug sometime. Since this is an API change, it's too late to do it in 9.6. (We could make a variant patch that preserves API, but I'm not excited enough to do that.) Discussion: https://postgr.es/m/8670.1504192177@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/6708e447efb5046c95bdcf900b6da97f56f97ae8
  • Avoid memory leaks when a GatherMerge node is rescanned. Rescanning a GatherMerge led to leaking some memory in the executor's query-lifespan context, because most of the node's working data structures were simply abandoned and rebuilt from scratch. In practice, this might never amount to much, given the cost of relaunching worker processes --- but it's still pretty messy, so let's fix it. We can rearrange things so that the tuple arrays are simply cleared and reused, and we don't need to rebuild the TupleTableSlots either, just clear them. One small complication is that because we might get a different number of workers on each iteration, we can't keep the old convention that the leader's gm_slots[] entry is the last one; the leader might clobber a TupleTableSlot that we need for a worker in a future iteration. Hence, adjust the logic so that the leader has slot 0 always, while the active workers have slots 1..n. Back-patch to v10 to keep all the existing versions of nodeGatherMerge.c in sync --- because of the renumbering of the slots, there would otherwise be a very large risk that any future backpatches in this module would introduce bugs. Discussion: https://postgr.es/m/8670.1504192177@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/2d44c58c79aeef2d376be0141057afbb9ec6b5bc
  • Ensure SIZE_MAX can be used throughout our code. Pre-C99 platforms may lack <stdint.h> and thereby SIZE_MAX. We have a couple of places using the hack "(size_t) -1" as a fallback, but it wasn't universally available; which means the code added in commit 2e70d6b5e fails to compile everywhere. Move that hack to c.h so that we can rely on having SIZE_MAX everywhere. Per discussion, it'd be a good idea to make the macro's value safe for use in #if-tests, but that will take a bit more work. This is just a quick expedient to get the buildfarm green again. Back-patch to all supported branches, like the previous commit. Discussion: https://postgr.es/m/15883.1504278595@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/b79d69b087561eb6687373031a5098b0694f9ec6
  • Make [U]INT64CONST safe for use in #if conditions. Instead of using a cast to force the constant to be the right width, assume we can plaster on an L, UL, LL, or ULL suffix as appropriate. The old approach to this is very hoary, dating from before we were willing to require compilers to have working int64 types. This fix makes the PG_INT64_MIN, PG_INT64_MAX, and PG_UINT64_MAX constants safe to use in preprocessor conditions, where a cast doesn't work. Other symbolic constants that might be defined using [U]INT64CONST are likewise safer than before. Also fix the SIZE_MAX macro to be similarly safe, if we are forced to provide a definition for that. The test added in commit 2e70d6b5e happens to do what we want even with the hack "(size_t) -1" definition, but we could easily get burnt on other tests in future. Back-patch to all supported branches, like the previous commits. Discussion: https://postgr.es/m/15883.1504278595@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/9d6b160d7db76809f0c696d9073f6955dd5a973a
  • Improve division of labor between execParallel.c and nodeGather[Merge].c. Move the responsibility for creating/destroying TupleQueueReaders into execParallel.c, to avoid duplicative coding in nodeGather.c and nodeGatherMerge.c. Also, instead of having DestroyTupleQueueReader do shm_mq_detach, do it in the caller (which is now only ExecParallelFinish). This means execParallel.c does both the attaching and detaching of the tuple-queue-reader shm_mqs, which seems less weird than the previous arrangement. These changes also eliminate a vestigial memory leak (of the pei->tqueue array). It's now demonstrable that rescans of Gather or GatherMerge don't leak memory. Discussion: https://postgr.es/m/8670.1504192177@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/51daa7bdb39e1bdc31eb99fd3f54f61743ebb7ae
  • Fix macro-redefinition warning on MSVC. In commit 9d6b160d7, I tweaked pg_config.h.win32 to use "#define HAVE_LONG_LONG_INT_64 1" rather than defining it as empty, for consistency with what happens in an autoconf'd build. But Solution.pm injects another definition of that macro into ecpg_config.h, leading to justifiable (though harmless) compiler whining. Make that one consistent too. Back-patch, like the previous patch. Discussion: https://postgr.es/m/CAEepm=1dWsXROuSbRg8PbKLh0S=8Ou-V8sr05DxmJOF5chBxqQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e451901804bd96a6b0fe3875b5c90aa0555c6a05
  • Suppress compiler warnings in dshash.c. Some compilers complain, not unreasonably, about left-shifting an int32 "1" and then assigning the result to an int64. In practice I sure hope that this data structure never gets large enough that an overflow would actually occur; but let's cast the constant to the right type to avoid the hazard. In passing, fix a typo in dshash.h. Amit Kapila, adjusted as per comment from Thomas Munro. Discussion: https://postgr.es/m/CAA4eK1+5vfVMYtjK_NX8O3-42yM3o80qdqWnQzGquPrbq6mb+A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4faa1dc2eb02ba67303110e025d44abb40b12725

Robert Haas pushed:

Simon Riggs pushed:

�lvaro Herrera pushed:

Correctifs en attente

Kyotaro HORIGUCHI sent in another revision of a patch to fix a race condition between SELECT and ALTER TABLE NO INHERIT.

Kyotaro HORIGUCHI sent in another revision of a patch to retard restart LSN of a slot when a segment starts with a contrecord and add some debugging code for same.

Aaron Patters sent in a patch to custom allocators to libpq by adding PQunescapeByteaConn for unescaping bytes given a connection and using the configured malloc / free from the connection.

Kyotaro HORIGUCHI sent in a patch to show "aggressive" or not in vacuum messages.

Craig Ringer sent in two revisions of a patch to show sockdir/hostname in pg_regress startup output.

Michael Malis sent in two revisions of a patch to improve correlated partial index cost estimates.

Masahiko Sawada sent in five more revisions of a patch to enable pgbench custom initialization.

Rajkumar Raghuwanshi and Ashutosh Bapat traded patches to create an advanced partition matching algorithm for partition-wise join.

Kyotaro HORIGUCHI sent in a patch to clean up the negative cache of pg_statistic when dropping a relation and clean up the negative cache of pg_class when dropping a schema.

Kyotaro HORIGUCHI sent in another revision of a patch to restrict maximum keep segments by repslots.

Amit Langote, Robert Haas, and Ashutosh Bapat traded patches to expand single inheritance child and expand inheritance by bound order.

Andrey Borodin sent in another revision of a patch to add hooks to watch for changed pages.

Simon Riggs sent in a patch to add a "toast_tuple_target" table-level GUC which specifies the threshold of tuple length at which PostgreSQL tries to move long column values into TOAST tables.

Mithun Cy sent in another revision of a patch to cache data in GetSnapshotData() for reuse.

Yangjie sent in another revision of a patch to implement hash partitioning.

Micha�l Paquier sent in another revision of a patch to refactor error message handling in pqAddTuple() and improve overflow checks of pqAddTuple() in libpq.

Dmitry Dolgov sent in another revision of a patch to implement generic type subscripting.

Etsuro Fujita sent in another revision of a patch to add epqpath for foreign joins.

Peter Geoghegan sent in another revision of a patch to add a bloom filter data structure implementation and use same to build amcheck verification of indexes against the heap.

Micha�l Paquier sent in a patch to ensure that int64 is used in pg_rewind logs.

Dilip Kumar sent in another revision of a patch to improve bitmap costing for lossy pages.

David Steele sent in two more revisions of a patch to update the low-level backup documentation to match its actual behavior.

Nathan Bossart sent in five more revisions of a patch to enable users to specify multiple tables in VACUUM commands.

David Steele sent in a patch to refactor OpenFile() permissions handling.

Etsuro Fujita sent in another revision of a patch to skip the CheckValidResultRel checks for a target table if it's a foreign partition to perform tuple-routing for.

Alexander Korotkov sent in another revision of a patch to change ALTER INDEX ... SET STATISTICS ... to set or reset the behavior of a given expression in the index.

Amit Kapila sent in another revision of his "group update clog" patch.

Peter Eisentraut sent in a patch to document and use SPI_result_code_string().

Peter Eisentraut sent in a patch to clean up places where global string variables are initialized as empty strings ("") and then checked later with strcmp(), instead of just using NULL.

Jing Wang sent in another revision of a patch to support to COMMENT ON DATABASE CURRENT_DATABASE.

Haribabu Kommi sent in another revision of a patch to parallelize queries containing initplans.

Amit Kapila sent in another revision of a patch to implement parallel append.

Jeff Davis sent in another revision of a patch to implement RANGE JOIN.

Beena Emerson sent in another revision of a patch to enable increasing the default WAL segment size.

Peter Eisentraut sent in a patch to implement generated columns.

Kyotaro HORIGUCHI sent in a patch to allow multiple targets for VACUUM.

Andreas Karlsson sent in another revision of a patch to implement REINDEX CONCURRENTLY.

Peter Geoghegan sent in a patch to remove replacement selection sort.

Peter Eisentraut sent in a patch to sync process names between ps and pg_stat_activity.

Peter Eisentraut sent in another revision of a patch to support static assertions in C++.

Peter Eisentraut sent in another revision of a patch to add background worker type.

Andrey Borodin sent in a patch to add hooks to track changed pages for backup purposes.

Tsutomu Yamada sent in another revision of a patch to add a CLUSTER command progress monitor.

Jacob Champion sent in a patch to assert that the correct locks are held when calling PageGetLSN().

Magnus Hagander sent in a patch to simplify the log_destination GUC by always starting the logging collector.

Thomas Munro sent in another revision of a patch to implement parallel hash.

Andreas Karlsson sent in a patch to add GnuTLS support for SSL.

Magnus Hagander sent in another revision of a patch to add a function to move the position of a replication slot.

Amit Langote sent in another revision of a patch to teach pg_inherits.c a bit about partitioning, allow locking only partitioned children in partition tree, defer opening and locking partitions to set_append_rel_size, change the interfaces for partition_bound_{cmp/bsearch}, addition to partition.c interface for partition-pruning, and make some planner-side changes for partition-pruning.

Haribabu Kommi sent in another revision of a patch to implement pluggable storage.

Micha�l Paquier sent in another revision of a patch to enable creating backup history files for backups taken from standbys.

Thomas Munro sent in another revision of a patch to allow SERIALIZABLE isolation level with parallel query.

Haribabu Kommi sent in another revision of a patch to make parallel eligible for utility commands underneath queries.

Konstantin Knizhnik sent in another revision of a patch to implement surjective functional indexes.

Etsuro Fujita sent in another revision of a patch to update comment in ExecPartitionCheck.

Thomas Munro sent in another revision of a patch to remove obsolete SLRU wrapping and warnings from predicate.c.

Tanay Varma sent in a patch to enable Visual Studio 2017 Build Support.

Antonin Houska sent in another revision of a patch to add some tools to help with partition-wise join for join between (declaratively) partitioned tables.

Antonin Houska sent in another revision of a patch to implement partition-wise join for join between (declaratively) partitioned tables.

Jeff Janes sent in a patch to ensure that pg_basebackup throttling throttles as promised.

Andres Freund sent in another revision of a patch to make JIT compiling expressions/deform + inlining available.

Simon Riggs and Tom Lane traded patches to fix a savepoint bug.

Micha�l Paquier sent in another revision of a patch to change detection of corrupted 2PC files to be FATAL and minimize the window between history file and end-of-recovery.

Konstantin Knizhnik sent in another revision of a patch to optimize secondary indexes.

Emre Hasegeli sent in another revision of a patch to improve geometric types.

David Steele sent in a patch to exclude pg_internal.init from base backup.

Alik Khilazhev sent in another revision of a patch to add a Zipfian distribution in pgbench.

Nikolay Shaplov sent in another revision of a patch to move all am-related reloption code into src/backend/access/[am-name] and get rid of relopt_kind for custom AM.

Thomas Munro sent in another revision of a patch to add an optional message to user when terminating/cancelling backend.

Amit Kapila and Thomas Munro traded patches to fix warnings and typo in dshash.