La première réunion de PostgreSQL Israël aura lieu à Tel Aviv le 7 avril 2016 : http://www.meetup.com/PostgreSQL-Israel/events/229430729/

L'appel à conférenciers du PG Day UK 2016 cours jusqu'au 5 avril 2016. La conférence aura lieu le 5 juillet 2016. Merci de soumettre vos propositions à Simon.Riggs@2ndquadrant.com <Simon AT 2ndquadrant DOT com> ou via le site web : http://www.pgconf.uk/papers

Les conférences pour le PGCon à Ottawa ont été sélectionnés : http://www.pgcon.org/2016/ugly-list-of-great-talks.txt

[ndt: meetup à Nantes le jeudi 17 mars : http://www.meetup.com/fr-FR/PostgreSQL-User-Group-Nantes/]

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

PostgreSQL Local

  • La première conférence PostgreSQL pan-asiatique se tiendra les 17 et 19 mars 2016 à Singapour. Les inscriptions sont ouvertes : http://2016.pgday.asia/
  • Le PGDay nordique, une série de conférences sur une seule journée, aura lieu à Helsinki (Finlande) le 17 mars 2016. Les inscriptions sont encore ouvertes : http://2016.nordicpgday.org/
  • Les inscriptions pour le PGDay Paris 2016, prévu pour le 31 mars, sont ouvertes : http://www.pgday.paris/registration/
  • La 8ème Session PostgreSQL aura lieu le 6 avril 2016 à Lyon (France).
  • La PGConf US 2016 aura lieu les 18, 19 et 20 avril à New-York City. Les inscriptions sont ouvertes : http://www.pgconf.us/2016/
  • La LinuxFest Northwest aura lieu les 23 et 24 avril 2016 au Collège Technique de Bellingham (Washington, USA). L'appel à conférenciers est maintenant lancé : http://www.linuxfestnorthwest.org/2016/present
  • FOSS4G NA (Free and Open Source Software for Geospatial - North America) se tiendra à Raleigh, en Caroline du Nord, du 2 au 5 mai 2016. Les candidatures de conférenciers sont encore acceptées : https://2016.foss4g-na.org/cfp
  • La PGCon 2016 se tiendra du 17 au 21 mai 2016 à Ottawa : http://www.pgcon.org/
  • Le PGDay suisse sera, cette année, tenue à l'Université des Sciences Appliquées (HSR) de Rapperswil le 24 juin 2016. L'appel à conférenciers est lancé : http://www.pgday.ch/
  • "5432 ... Meet us!" aura lieu à Milan (Italie) les 28 & 29 juin 2016. L'appel à conférenciers court jusqu'au 14 mars : http://5432meet.us/
  • La PgConf Silicon Valley 2016 aura lieu du 14 au 16 novembre 2016 : http://www.pgconfsv.com/

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/20160314064234.GA25336@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

Tom Lane pushed:

  • Mop-up for setting minimum Tcl version to 8.4.

Commit e2609323e set the minimum Tcl version we support to 8.4, but I forgot to adjust the documentation to say the same. Some nosing around for other consequences found that the configure script could be simplified slightly as well.

Branch ------ master

Details ------- http://git.postgresql.org/pg/commitdiff/9da70efcbe09954b1006f878d39885a4acf1c534

  • Fix unportable usage of <ctype.h> functions. isdigit(), isspace(), etc are likely to give surprising results if passed a signed char. We should always cast the argument to unsigned char to avoid that. Error in commit d78a7d9c7fa3e9cd, found by buildfarm member gaur. http://git.postgresql.org/pg/commitdiff/cb0ca0c9953aa0614e7b143bd2440a7582a27233
  • Fix not-terribly-safe coding in NIImportOOAffixes() and NIImportAffixes(). There were two places in spell.c that supposed that they could search for a location in a string produced by lowerstr() and then transpose the offset into the original string. But this fails completely if lowerstr() transforms any characters into characters of different byte length, as can happen in Turkish UTF8 for instance. We'd added some comments about this coding in commit 51e78ab4ff328296, but failed to realize that it was not merely confusing but wrong. Coverity complained about this code years ago, but in such an opaque fashion that nobody understood what it was on about. I'm not entirely sure that this issue *is* what it's on about, actually, but perhaps this patch will shut it up -- and in any case the problem is clear. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/b3e05097e58051a7816ed734074fd76345687e0c
  • Fix broken definition for function name in pgbench's exprscan.l. As written, this would accept e.g. 123e9 as a function name. Aside from being mildly astonishing, that would come back to haunt us if we ever try to add float constants to the expression syntax. Insist that function names start with letters (or at least non-digits). In passing reset yyline as well as yycol when starting a new expression. This variable is useless since it's used nowhere, but if we're going to have it we should have it act sanely. http://git.postgresql.org/pg/commitdiff/3899caf772c8dec5c79e553c91f8fc248ca686c9
  • Re-fix broken definition for function name in pgbench's exprscan.l. Wups, my first try wasn't quite right either. Too focused on fixing the existing bug, not enough on not introducing new ones. http://git.postgresql.org/pg/commitdiff/94f1adccd36df3ad75d2c257c9ae1ca448f3e4ac
  • Fix backwards test for Windows service-ness in pg_ctl. A thinko in a96761391 caused pg_ctl to get it exactly backwards when deciding whether to report problems to the Windows eventlog or to stderr. Per bug #14001 from Manuel Mathar, who also identified the fix. Like the previous patch, back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/b642e50aea1b966f3b78c49e806b4a2c5497a861
  • Make the upper part of the planner work by generating and comparing Paths. I've been saying we needed to do this for more than five years, and here it finally is. This patch removes the ever-growing tangle of spaghetti logic that grouping_planner() used to use to try to identify the best plan for post-scan/join query steps. Now, there is (nearly) independent consideration of each execution step, and entirely separate construction of Paths to represent each of the possible ways to do that step. We choose the best Path or set of Paths using the same add_path() logic that's been used inside query_planner() for years. In addition, this patch removes the old restriction that subquery_planner() could return only a single Plan. It now returns a RelOptInfo containing a set of Paths, just as query_planner() does, and the parent query level can use each of those Paths as the basis of a SubqueryScanPath at its level. This allows finding some optimizations that we missed before, wherein a subquery was capable of returning presorted data and thereby avoiding a sort in the parent level, making the overall cost cheaper even though delivering sorted output was not the cheapest plan for the subquery in isolation. (A couple of regression test outputs change in consequence of that. However, there is very little change in visible planner behavior overall, because the point of this patch is not to get immediate planning benefits but to create the infrastructure for future improvements.) There is a great deal left to do here. This patch unblocks a lot of planner work that was basically impractical in the old code structure, such as allowing FDWs to implement remote aggregation, or rewriting plan_set_operations() to allow consideration of multiple implementation orders for set operations. (The latter will likely require a full rewrite of plan_set_operations(); what I've done here is only to fix it to return Paths not Plans.) I have also left unfinished some localized refactoring in createplan.c and planner.c, because it was not necessary to get this patch to a working state. Thanks to Robert Haas, David Rowley, and Amit Kapila for review. http://git.postgresql.org/pg/commitdiff/3fc6e2d7f5b652b417fa6937c34de2438d60fa9f
  • Spell "parallel" correctly. Per David Rowley. http://git.postgresql.org/pg/commitdiff/cf8e7b16a5f3e63fe692d042fefc0c9f09a23ebc
  • Fix minor typo in logical-decoding docs. David Rowley http://git.postgresql.org/pg/commitdiff/a93aec4e0f061ad43034d5324b8407a824e54395
  • Finish refactoring make_foo() functions in createplan.c. This patch removes some redundant cost calculations that I left for later cleanup in commit 3fc6e2d7f5b652b4. There's now a uniform policy that the make_foo() convenience functions don't do any cost calculations. Most of their callers copy costs from the source Path node, and for those that don't, the calculation in the make_foo() function wasn't necessarily right anyhow. (make_result() was particularly a mess, as it was serving multiple callers using cost calcs designed for only the first one or two that had ever existed.) Aside from saving a few cycles, this ensures that what EXPLAIN prints matches the costs we used for planning purposes. It does not change any planner decisions, since the decisions are already made. http://git.postgresql.org/pg/commitdiff/8c314b9853c2fbb85c041d4761426f25a9d63972
  • Fix minor thinko in pathification code. I passed the wrong "root" struct to create_pathtarget in build_minmax_path. Since the subroot is a clone of the outer root, this would not cause any serious problems, but it would waste some cycles because set_pathtarget_cost_width would not have access to Var width estimates set up while running query_planner on the subroot. http://git.postgresql.org/pg/commitdiff/61fd218930db53079e5f001dd4ea2fd53afd1b95
  • Improve handling of group-column indexes in GroupingSetsPath. Instead of having planner.c compute a groupColIdx array and store it in GroupingSetsPaths, make create_groupingsets_plan() find the grouping columns by searching in the child plan node's tlist. Although that's probably a bit slower for create_groupingsets_plan(), it's more like the way every other plan node type does this, and it provides positive confirmation that we know which child output columns we're supposed to be grouping on. (Indeed, looking at this now, I'm not at all sure that it wasn't broken before, because create_groupingsets_plan() isn't demanding an exact tlist match from its child node.) Also, this allows substantial simplification in planner.c, because it no longer needs to compute the groupColIdx array at all; no other cases were using it. I'd intended to put off this refactoring until later (like 9.7), but in view of the likely bug fix and the need to rationalize planner.c's tlist handling so we can do something sane with Konstantin Knizhnik's function-evaluation-postponement patch, I think it can't wait. http://git.postgresql.org/pg/commitdiff/9e8b99420fe5f80495ada8dc50aeb7b954b33093
  • Improve handling of pathtargets in planner.c. Refactor so that the internal APIs in planner.c deal in PathTargets not targetlists, and establish a more regular structure for deriving the targets needed for successive steps. There is more that could be done here; calculating the eval costs of each successive target independently is both inefficient and wrong in detail, since we won't actually recompute values available from the input node's tlist. But it's no worse than what happened before the pathification rewrite. In any case this seems like a good starting point for considering how to handle Konstantin Knizhnik's function-evaluation-postponement patch. http://git.postgresql.org/pg/commitdiff/51c0f63e4d76a86b44e87876a6addcfffb01ec28
  • Fix copy-and-pasteo in comment. Wensheng Zhang http://git.postgresql.org/pg/commitdiff/d31f20e2b5a246f276c73134b610ac7a2f34e274
  • Fix incorrect tlist generation in create_gather_plan(). This function is written as though Gather doesn't project; but it does. Even if it did not project, though, we must use build_path_tlist to ensure that the output columns receive correct sortgroupref labeling. Per report from Amit Kapila. http://git.postgresql.org/pg/commitdiff/8776c15c85322612b9bf79daf50f74be71c12e05
  • Fix incorrect handling of NULL index entries in indexed ROW() comparisons. An index search using a row comparison such as ROW(a, b) > ROW('x', 'y') would stop upon reaching a NULL entry in the "b" column, ignoring the fact that there might be non-NULL "b" values associated with later values of "a". This happens because _bt_mark_scankey_required() marks the subsidiary scankey for "b" as required, which is just wrong: it's for a column after the one with the first inequality key (namely "a"), and thus can't be considered a required match. This bit of brain fade dates back to the very beginnings of our support for indexed ROW() comparisons, in 2006. Kind of astonishing that no one came across it before Glen Takahashi, in bug #14010. Back-patch to all supported versions. Note: the given test case doesn't actually fail in unpatched 9.1, evidently because the fix for bug #6278 (i.e., stopping at nulls in either scan direction) is required to make it fail. I'm sure I could devise a case that fails in 9.1 as well, perhaps with something involving making a cursor back up; but it doesn't seem worth the trouble. http://git.postgresql.org/pg/commitdiff/a298a1e06fb0574c898a07761f9f86c2a323919e
  • Remove a couple of useless pstrdup() calls. There's no point in pstrdup'ing the result of TextDatumGetCString, since that's necessarily already a freshly-palloc'd C string. These particular calls are unlikely to be of any consequence performance-wise, but still they're a bad precedent that can confuse future patch authors. Noted by Chapman Flack. http://git.postgresql.org/pg/commitdiff/cc402116ca156babcd3ef941317f462a96277e3a
  • Refactor pull_var_clause's API to make it less tedious to extend. In commit 1d97c19a0f748e94 and later c1d9579dd8bf3c92, we extended pull_var_clause's API by adding enum-type arguments. That's sort of a pain to maintain, though, because it means every time we add a new behavior we must touch every last one of the call sites, even if there's a reasonable default behavior that most of them could use. Let's switch over to using a bitmask of flags, instead; that seems more maintainable and might save a nanosecond or two as well. This commit changes no behavior in itself, though I'm going to follow it up with one that does add a new behavior. In passing, remove flatten_tlist(), which has not been used since 9.1 and would otherwise need the same API changes. Removing these enums means that optimizer/tlist.h no longer needs to depend on optimizer/var.h. Changing that caused a number of C files to need addition of #include "optimizer/var.h" (probably we can thank old runs of pgrminclude for that); but on balance it seems like a good change anyway. http://git.postgresql.org/pg/commitdiff/364a9f47ab363250f62dd2c381c4da435283725a
  • Give pull_var_clause() reject/recurse/return behavior for WindowFuncs too. All along, this function should have treated WindowFuncs in a manner similar to Aggrefs, ie with an option whether or not to recurse into them. By not considering the case, it was always recursing, which is OK for most callers (although I suspect that the case in prepare_sort_from_pathkeys might represent a bug). But now we need return-without-recursing behavior as well. There are also more than a few callers that should never see a WindowFunc, and now we'll get some error checking on that. http://git.postgresql.org/pg/commitdiff/c82c92b111b7b636e80f8a432de10c62011b35b6
  • Minor additional refactoring of planner.c's PathTarget handling. Teach make_group_input_target() and make_window_input_target() to work entirely with the PathTarget representation of tlists, rather than constructing a tlist and immediately deconstructing it into PathTarget format. In itself this only saves a few palloc's; the bigger picture is that it opens the door for sharing cost_qual_eval work across all of planner.c's constructions of PathTargets. I'll come back to that later. In support of this, flesh out tlist.c's infrastructure for PathTargets a bit more. http://git.postgresql.org/pg/commitdiff/49635d7b3e86c0088eadd80db1563a210bc89efd
  • When appropriate, postpone SELECT output expressions till after ORDER BY. It is frequently useful for volatile, set-returning, or expensive functions in a SELECT's targetlist to be postponed till after ORDER BY and LIMIT are done. Otherwise, the functions might be executed for every row of the table despite the presence of LIMIT, and/or be executed in an unexpected order. For example, in SELECT x, nextval('seq') FROM tab ORDER BY x LIMIT 10; it's probably desirable that the nextval() values are ordered the same as x, and that nextval() is not run more than 10 times. In the past, Postgres was inconsistent in this area: you would get the desirable behavior if the ordering were performed via an indexscan, but not if it had to be done by an explicit sort step. Getting the desired behavior reliably required contortions like SELECT x, nextval('seq') FROM (SELECT x FROM tab ORDER BY x) ss LIMIT 10; This patch conditionally postpones evaluation of pure-output target expressions (that is, those that are not used as DISTINCT, ORDER BY, or GROUP BY columns) so that they effectively occur after sorting, even if an explicit sort step is necessary. Volatile expressions and set-returning expressions are always postponed, so as to provide consistent semantics. Expensive expressions (costing more than 10 times typical operator cost, which by default would include any user-defined function) are postponed if there is a LIMIT or if there are expressions that must be postponed. We could be more aggressive and postpone any nontrivial expression, but there are costs associated with doing so: it requires an extra Result plan node which adds some overhead, and postponement changes the volume of data going through the sort step, perhaps for the worse. Since we tend not to have very good estimates of the output width of nontrivial expressions, it's hard to have much confidence in our ability to predict whether postponement would increase or decrease the cost of the sort; therefore this patch doesn't attempt to make decisions conditionally on that. Between these factors and a general desire not to change query behavior when there's not a demonstrable benefit, it seems best to be conservative about applying postponement. We might tweak the decision rules in the future, though. Konstantin Knizhnik, heavily rewritten by me http://git.postgresql.org/pg/commitdiff/9118d03a8cca3d97327c56bf89a72e328e454e63
  • Re-export a few of createplan.c's make_xxx() functions. CitusDB is using these and don't wish to redesign their code right now. I am not on board with this being a good idea, or a good precedent, but I lack the energy to fight about it. http://git.postgresql.org/pg/commitdiff/570be1f73f385abb557bda15b718d7aac616cc15
  • Get rid of scribbling on a const variable in psql's print.c. Commit a2dabf0e1dda93c8 had the bright idea that it could modify a "const" global variable if it merely casted away const from a pointer. This does not work on platforms where the compiler puts "const" variables into read-only storage. Depressingly, we evidently have no such platforms in our buildfarm ... an oversight I have now remedied. (The one platform that is known to catch this is recent OS X with -fno-common.) Per report from Chris Ruprecht. Back-patch to 9.5 where the bogus code was introduced. http://git.postgresql.org/pg/commitdiff/fc7a9dfddb073a55a226778acd6a9b3f5ea2e626
  • Widen query numbers-of-tuples-processed counters to uint64. This patch widens SPI_processed, EState's es_processed field, PortalData's portalPos field, FuncCallContext's call_cntr and max_calls fields, ExecutorRun's count argument, PortalRunFetch's result, and the max number of rows in a SPITupleTable to uint64, and deals with (I hope) all the ensuing fallout. Some of these values were declared uint32 before, and others "long". I also removed PortalData's posOverflow field, since that logic seems pretty useless given that portalPos is now always 64 bits. The user-visible results are that command tags for SELECT etc will correctly report tuple counts larger than 4G, as will plpgsql's GET GET DIAGNOSTICS ... ROW_COUNT command. Queries processing more tuples than that are still not exactly the norm, but they're becoming more common. Most values associated with FETCH/MOVE distances, such as PortalRun's count argument and the count argument of most SPI functions that have one, remain declared as "long". It's not clear whether it would be worth promoting those to int64; but it would definitely be a large dollop of additional API churn on top of this, and it would only help 32-bit platforms which seem relatively less likely to see any benefit. Andreas Scherbaum, reviewed by Christian Ullrich, additional hacking by me http://git.postgresql.org/pg/commitdiff/23a27b039d94ba359286694831eafe03cd970eef
  • Fix Windows portability issue in 23a27b039d94ba35. _strtoui64() is available in MSVC builds, but apparently not with other Windows toolchains. Thanks to Petr Jelinek for the diagnosis. http://git.postgresql.org/pg/commitdiff/ab737f6ba9fc0a26d32a95b115d5cd0e24a63191
  • Report memory context stats upon out-of-memory in repalloc[_huge]. This longstanding functionality evidently got lost in commit 3d6d1b585524aab6. Noted while studying an OOM report from Jaime Casanova. Backpatch to 9.5 where the bug was introduced. http://git.postgresql.org/pg/commitdiff/4b980167cb5a489346c5e53afb86280a7d59ebc7
  • Fix memory leak in repeated GIN index searches. Commit d88976cfa1302e8d removed this code from ginFreeScanKeys(): if (entry->list) pfree(entry->list); evidently in the belief that that ItemPointer array is allocated in the keyCtx and so would be reclaimed by the following MemoryContextReset. Unfortunately, it isn't and it won't. It'd likely be a good idea for that to become so, but as a simple and back-patchable fix in the meantime, restore this code to ginFreeScanKeys(). Also, add a similar pfree to where startScanEntry() is about to zero out entry->list. I am not sure if there are any code paths where this change prevents a leak today, but it seems like cheap future-proofing. In passing, make the initial allocation of so->entries[] use palloc not palloc0. The code doesn't depend on unused entries being zero; if it did, the array-enlargement code in ginFillScanEntry() would be wrong. So using palloc0 initially can only serve to confuse readers about what the invariant is. Per report from Felipe de Jesús Molina Bravo, via Jaime Casanova in <CAJGNTeMR1ndMU2Thpr8GPDUfiHTV7idELJRFusA5UXUGY1y-eA@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/ab4ff2889d0bccc32467e681546aabdb87de4958

Andres Freund pushed:

  • Fix wrong allocation size in c8f621c43. In c8f621c43 I forgot to account for MAXALIGN when allocating a new tuplebuf in ReorderBufferGetTupleBuf(). That happens to currently not cause active problems on a number of platforms because the affected pointer is already aligned, but others, like ppc and hppa, trigger this in the regression test, due to a debug memset clearing memory. Fix that. Backpatch: 9.4, like the previous commit. http://git.postgresql.org/pg/commitdiff/fd45d16f6212df15821684b231a44448389fb002
  • Further improvements to c8f621c43. Coverity and inspection for the issue addressed in fd45d16f found some questionable code. Specifically coverity noticed that the wrong length was added in ReorderBufferSerializeChange() - without immediate negative consequences as the variable isn't used afterwards. During code-review and testing I noticed that a bit of space was wasted when allocating tuple bufs in several places. Thirdly, the debug memset()s in ReorderBufferGetTupleBuf() reduce the error checking valgrind can do. Backpatch: 9.4, like c8f621c43. http://git.postgresql.org/pg/commitdiff/b63bea5fd3bba4d7a61c3beaba51a06f24b38da6
  • plperl: Correctly handle empty arrays in plperl_ref_from_pg_array. plperl_ref_from_pg_array() didn't consider the case that postgrs arrays can have 0 dimensions (when they're empty) and accessed the first dimension without a check. Fix that by special casing the empty array case. Author: Alex Hunsaker Reported-By: Andres Freund / valgrind / buildfarm animal skink Discussion: 20160308063240.usnzg6bsbjrne667@alap3.anarazel.de Backpatch: 9.1- http://git.postgresql.org/pg/commitdiff/e66197fa2efa8ae0cab1eed6b2257ab4e2134b1e
  • ltree: Zero padding bytes when allocating memory for externally visible data. ltree/ltree_gist/ltxtquery's headers stores data at MAXALIGN alignment, requiring some padding bytes. So far we left these uninitialized. Zero those by using palloc0. Author: Andres Freund Reported-By: Andres Freund / valgrind / buildarm animal skink Backpatch: 9.1- http://git.postgresql.org/pg/commitdiff/7a1d4a2448c34ed4669d67ae4f24c594545f10b5
  • Add valgrind suppressions for bootstrap related code. Author: Andres Freund Backpatch: 9.4, where we started to maintain valgrind suppressions http://git.postgresql.org/pg/commitdiff/5e43bee8307f1f6f87894c9a4bd9f9045f45c064
  • Add valgrind suppressions for python code. Python's allocator does some low-level tricks for efficiency; unfortunately they trigger valgrind errors. Those tricks can be disabled making instrumentation easier; but few people testing postgres will have such a build of python. So add broad suppressions of the resulting errors. See also https://svn.python.org/projects/python/trunk/Misc/README.valgrind This possibly will suppress valid errors, but without it it's basically impossible to use valgrind with plpython code. Author: Andres Freund Backpatch: 9.4, where we started to maintain valgrind suppressions http://git.postgresql.org/pg/commitdiff/2f1f4439306d2793492e49366d5911e48aa2c4b1
  • Introduce durable_rename() and durable_link_or_rename(). Renaming a file using rename(2) is not guaranteed to be durable in face of crashes; especially on filesystems like xfs and ext4 when mounted with data=writeback. To be certain that a rename() atomically replaces the previous file contents in the face of crashes and different filesystems, one has to fsync the old filename, rename the file, fsync the new filename, fsync the containing directory. This sequence is not generally adhered to currently; which exposes us to data loss risks. To avoid having to repeat this arduous sequence, introduce durable_rename(), which wraps all that. Also add durable_link_or_rename(). Several places use link() (with a fallback to rename()) to rename a file, trying to avoid replacing the target file out of paranoia. Some of those rename sequences need to be durable as well. There seems little reason extend several copies of the same logic, so centralize the link() callers. This commit does not yet make use of the new functions; they're used in a followup commit. Author: Michael Paquier, Andres Freund Discussion: 56583BDD.9060302@2ndquadrant.com Backpatch: All supported branches http://git.postgresql.org/pg/commitdiff/606e0f9841b820d826f837bf741a3e5e9cc62fa1
  • Avoid unlikely data-loss scenarios due to rename() without fsync. Renaming a file using rename(2) is not guaranteed to be durable in face of crashes. Use the previously added durable_rename()/durable_link_or_rename() in various places where we previously just renamed files. Most of the changed call sites are arguably not critical, but it seems better to err on the side of too much durability. The most prominent known case where the previously missing fsyncs could cause data loss is crashes at the end of a checkpoint. After the actual checkpoint has been performed, old WAL files are recycled. When they're filled, their contents are fdatasynced, but we did not fsync the containing directory. An OS/hardware crash in an unfortunate moment could then end up leaving that file with its old name, but new content; WAL replay would thus not replay it. Reported-By: Tomas Vondra Author: Michael Paquier, Tomas Vondra, Andres Freund Discussion: 56583BDD.9060302@2ndquadrant.com Backpatch: All supported branches http://git.postgresql.org/pg/commitdiff/1d4a0ab19a7e45aa8b94d7f720d1d9cefb81ec40
  • Blindly try to fix dtrace enabled builds, broken in 9cd00c45. Reported-By: Peter Eisentraut Discussion: 56E2239E.1050607@gmx.net http://git.postgresql.org/pg/commitdiff/c94f0c29cecc7944a14aa645c8a97a7250bf146b
  • Checkpoint sorting and balancing. Up to now checkpoints were written in the order they're in the BufferDescriptors. That's nearly random in a lot of cases, which performs badly on rotating media, but even on SSDs it causes slowdowns. To avoid that, sort checkpoints before writing them out. We currently sort by tablespace, relfilenode, fork and block number. One of the major reasons that previously wasn't done, was fear of imbalance between tablespaces. To address that balance writes between tablespaces. The other prime concern was that the relatively large allocation to sort the buffers in might fail, preventing checkpoints from happening. Thus pre-allocate the required memory in shared memory, at server startup. This particularly makes it more efficient to have checkpoint flushing enabled, because that'll often result in a lot of writes that can be coalesced into one flush. Discussion: alpine.DEB.2.10.1506011320000.28433@sto Author: Fabien Coelho and Andres Freund http://git.postgresql.org/pg/commitdiff/9cd00c457e6a1ebb984167ac556a9961812a683c
  • Allow to trigger kernel writeback after a configurable number of writes. Currently writes to the main data files of postgres all go through the OS page cache. This means that some operating systems can end up collecting a large number of dirty buffers in their respective page caches. When these dirty buffers are flushed to storage rapidly, be it because of fsync(), timeouts, or dirty ratios, latency for other reads and writes can increase massively. This is the primary reason for regular massive stalls observed in real world scenarios and artificial benchmarks; on rotating disks stalls on the order of hundreds of seconds have been observed. On linux it is possible to control this by reducing the global dirty limits significantly, reducing the above problem. But global configuration is rather problematic because it'll affect other applications; also PostgreSQL itself doesn't always generally want this behavior, e.g. for temporary files it's undesirable. Several operating systems allow some control over the kernel page cache. Linux has sync_file_range(2), several posix systems have msync(2) and posix_fadvise(2). sync_file_range(2) is preferable because it requires no special setup, whereas msync() requires the to-be-flushed range to be mmap'ed. For the purpose of flushing dirty data posix_fadvise(2) is the worst alternative, as flushing dirty data is just a side-effect of POSIX_FADV_DONTNEED, which also removes the pages from the page cache. Thus the feature is enabled by default only on linux, but can be enabled on all systems that have any of the above APIs. While desirable and likely possible this patch does not contain an implementation for windows. With the infrastructure added, writes made via checkpointer, bgwriter and normal user backends can be flushed after a configurable number of writes. Each of these sources of writes controlled by a separate GUC, checkpointer_flush_after, bgwriter_flush_after and backend_flush_after respectively; they're separate because the number of flushes that are good are separate, and because the performance considerations of controlled flushing for each of these are different. A later patch will add checkpoint sorting - after that flushes from the ckeckpoint will almost always be desirable. Bgwriter flushes are most of the time going to be random, which are slow on lots of storage hardware. Flushing in backends works well if the storage and bgwriter can keep up, but if not it can have negative consequences. This patch is likely to have negative performance consequences without checkpoint sorting, but unfortunately so has sorting without flush control. Discussion: alpine.DEB.2.10.1506011320000.28433@sto Author: Fabien Coelho and Andres Freund http://git.postgresql.org/pg/commitdiff/428b1d6b29ca599c5700d4bc4f4ce4c5880369bf
  • Include portability/mem.h into fd.c for MAP_FAILED. Buildfarm members gaur and pademelon are old enough not to know about MAP_FAILED; which is used in 428b1d6. Include portability/mem.h to fix; as already done in a bunch of other places. http://git.postgresql.org/pg/commitdiff/e01157500f26342bf4f067a4eb1e45ab9a3cd410

Joe Conway pushed:

Peter Eisentraut pushed:

Robert Haas pushed:

Ãlvaro Herrera pushed:

Simon Riggs pushed:

Magnus Hagander pushed:

  • Avoid crash on old Windows with AVX2-capable CPU for VS2013 builds. The Visual Studio 2013 CRT generates invalid code when it makes a 64-bit build that is later used on a CPU that supports AVX2 instructions using a version of Windows before 7SP1/2008R2SP1. Detect this combination, and in those cases turn off the generation of FMA3, per recommendation from the Visual Studio team. The bug is actually in the CRT shipping with Visual Studio 2013, but Microsoft have stated they're only fixing it in newer major versions. The fix is therefor conditioned specifically on being built with this version of Visual Studio, and not previous or later versions. Author: Christian Ullrich http://git.postgresql.org/pg/commitdiff/9d90388247e093cd9b3ead79954df2ac18bfeb66
  • Refactor receivelog.c parameters. Much cruft had accumulated over time with a large number of parameters passed down between functions very deep. With this refactoring, instead introduce a StreamCtl structure that holds the parameters, and pass around a pointer to this structure instead. This makes it much easier to add or remove fields that are needed deeper down in the implementation without having to modify every function header in the file. Patch by me after much nagging from Andres Reviewed by Craig Ringer and Daniel Gustafsson http://git.postgresql.org/pg/commitdiff/38c83c9b7569378d864d8915e291716b8bec15f2
  • Allow setting sample ratio for auto_explain. New configuration parameter auto_explain.sample_ratio makes it possible to log just a fraction of the queries meeting the configured threshold, to reduce the amount of logging. Author: Craig Ringer and Julien Rouhaud Review: Petr Jelinek http://git.postgresql.org/pg/commitdiff/92f03fe76fe6be683a8b7497579158b8a82b2c25
  • Fix order of MemSet arguments. Noted by Tomas Vondra http://git.postgresql.org/pg/commitdiff/a1aa8b7ea0558620106e25c27d0a70ee4ac9d6a8
  • Rename auto_explain.sample_ratio to sample_rate. Per suggestion from Tomas Vondra Author: Julien Rouhaud http://git.postgresql.org/pg/commitdiff/7a8d8748362d4d8505e320c3eaab4a2c2463e3a6

Teodor Sigaev pushed:

Correctifs rejetés (à ce jour)

No one was disappointed this week :-)

Correctifs en attente

Amit Langote sent in eight more revisions of a patch to implement a vacuum progress checker.

David Rowley and Haribabu Kommi traded patches to implement parallel aggregation.

Peter Geoghegan sent in a patch to avoid incorrectly indicating exclusion constraint wait.

Michaël Paquier and Petr Jelínek traded patches to add support for MSVC 2015.

Fabien COELHO sent in four more revisions of a patch to extend pgbench expressions with functions.

Peter Geoghegan sent in another revision of a patch to use quicksort for every external sort run.

Michaël Paquier sent in four more revisions of a patch to fix a recovery delay.

Robert Haas sent in another revision of a patch to add an Idle In Transaction Session timeout GUC.

Robbie Harwood sent in another revision of a patch to add GSSAPI encryption support.

Ashutosh Bapat sent in another revision of a patch to allow pushing sorted joins down to the FDW.

Pavan Deolasee sent in two more revisions of a patch to reduce the amount of WAL in the 2PC case.

David Rowley sent in two more revisions of a patch to make outer joins faster when the outer side is unique.

Tomas Vondra sent in five more revisions of a patch to implement multivariate statistics.

Dilip Kumar sent in three more revisions of a patch to scale up relation extension.

Thomas Munro sent in four more revisions of a patch to implement "causal reads."

Kyotaro HORIGUCHI sent in another revision of a patch to allow index-only scans with partial indexes.

Fabien COELHO sent in another revision of a patch to fix pgbench duration under rate.

Michaël Paquier sent in another revision of a patch to delay status update to the end of recovery.

David G. Johnston sent in a patch to continue using \pset titles during \watch iterations after the first in psql.

Etsuro Fujita sent in another revision of a patch to make writes faster with the PostgreSQL FDW.

Gilles Darold sent in two revisions of a patch to add a pg_current_logfile() function.

Alexander Korotkov sent in two more revisions of a patch for access method extendability.

Mithun Cy and Amit Kapila traded patches to fix an issue where Explain [Analyze] produces a parallel scan for SELECT INTO TABLE statements. As writes have not yet been parallelized, this is a problem.

Kaigai Kouhei sent in two more revisions of a patch to rework CustomScan [de]serialization.

Alexander Kuleshov sent in a patch to simplify search of end of argv in save_ps_display_args().

Stas Kelvich sent in a patch to add 2PC support to pg_logical.

Pavel Stěhule sent in another revision of a patch to add parse_ident().

Corey Huinker sent in two more revisions of a patch to add generate_series(date, date[, integer]).

Peter Eisentraut sent in a patch to clear OpenSSL error queue before OpenSSL calls.

Alexey Grishchenko sent in two revisions of a patch to fix an endless loop calling PL/Python set returning functions.

Grzegorz Sampolski and Haribabu Kommi traded patches to add rhost to PAM auth.

Thomas Munro sent in three more revisions of a patch to detect SSI conflicts before reporting constraint violations.

Kyotaro HORIGUCHI sent in another revision of a patch to fix a WAL logging issue.

David Steele sent in another revision of a patch to add client log output filtering.

Sherrylyn Branchaw sent in a patch to change error code for hstore syntax error.

Alexander Kuleshov sent in a patch to use MemoryContextAlloc() in the MemoryContextAllocZero() and MemoryContextAllocZeroAligned().

Daniel Verité sent in another revision of a patch to add \crosstabview to psql.

Alexander Korotkov sent in another revision of a patch to implement partial sort.

Dagfinn Ilmari Mannsåker sent in a patch to fix obsolete wording in PL/Perl hv_store_string comment.

Jim Nasby sent in a patch to improve error handling in pltcl.

Jim Nasby sent in another revision of a patch to ensure that configure checks for a valid [1-65535] port.

Alexander Korotkov sent in another revision of a patch to move PinBuffer and UnpinBuffer to atomics.

Jim Nasby sent in another revision of a patch to implement \gexec in psql.

Thomas Munro sent in a patch to make pg_stat_get_progress_info() strict.

David Rowley sent in two more revisions of a patch to help implement combining aggregates.

Noah Misch and Tomas Vondra traded patches to split stats file per database.