Les inscriptions pour le PGDay Paris 2016, prévu pour le 31 mars, sont ouvertes : http://www.pgday.paris/registration/

Les détails pour les inscriptions et le mécénat des pgDay Asia ont été mis à jour : http://2016.pgday.asia/sponsorship.html http://2016.pgday.asia/index.html#registration

[ndt: Meetup à Toulouse ce mardi 23 à midi : http://www.meetup.com/fr-FR/PostgreSQL-User-Group-Toulouse/events/228604600/]

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en février

PostgreSQL Local

  • Le PGday annuel indien se tiendra à Bangalore (État du Karnataka en Inde) le 26 février 2016 : http://pgday.in
  • 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/
  • La 8ème Session PostgreSQL aura lieu le 6 avril 2016 à Lyon (France). L'appel à conférenciers court jusqu'au 29 février à l'adresse call-for-paper AT postgresql-sessions POINT org.
  • 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 28 février : http://5432meet.us/

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/20160222002955.GC10490@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

Noah Misch pushed:

Magnus Hagander pushed:

Fujii Masao pushed:

  • Make concurrent refresh check early that there is a unique index on matview. In REFRESH MATERIALIZED VIEW command, CONCURRENTLY option is only allowed if there is at least one unique index with no WHERE clause on one or more columns of the matview. Previously, concurrent refresh checked the existence of a unique index on the matview after filling the data to new snapshot, i.e., after calling refresh_matview_datafill(). So, when there was no unique index, we could need to wait a long time before we detected that and got the error. It was a waste of time. To eliminate such wasting time, this commit changes concurrent refresh so that it checks the existence of a unique index at the beginning of the refresh operation, i.e., before starting any time-consuming jobs. If CONCURRENTLY option is not allowed due to lack of a unique index, concurrent refresh can immediately detect it and emit an error. Author: Masahiko Sawada Reviewed-by: Michael Paquier, Fujii Masao http://git.postgresql.org/pg/commitdiff/31b6606c48edf7c008ffe91907c080404a8c8046
  • Correct the formulas for System V IPC parameters SEMMNI and SEMMNS in docs. In runtime.sgml, the old formulas for calculating the reasonable values of SEMMNI and SEMMNS were incorrect. They have forgotten to count the number of semaphores which both the checkpointer process (introduced in 9.2) and the background worker processes (introduced in 9.3) need. This commit fixes those formulas so that they count the number of semaphores which the checkpointer process and the background worker processes need. Report and patch by Kyotaro Horiguchi. Only the patch for 9.3 was modified by me. Back-patch to 9.2 where the checkpointer process was added and the number of needed semaphores was increased. Author: Kyotaro Horiguchi Reviewed-by: Fujii Masao Backpatch: 9.2 Discussion: http://www.postgresql.org/message-id/20160203.125119.66820697.horiguchi.kyotaro@lab.ntt.co.jp http://git.postgresql.org/pg/commitdiff/597f7e3a6ec393cf9ff3e11552faf69ff0ab652b

Joe Conway pushed:

Andres Freund pushed:

  • Allow SetHintBits() to succeed if the buffer's LSN is new enough. Previously we only allowed SetHintBits() to succeed if the commit LSN of the last transaction touching the page has already been flushed to disk. We can't generally change the LSN of the page, because we don't necessarily have the required locks on the page. But the required LSN interlock does not mean the commit record has to be flushed immediately, it just requires that the commit record will be flushed before the page is written out. Therefore if the buffer LSN is newer than the commit LSN, the hint bit can be safely set. In a number of scenarios (e.g. pgbench) this noticeably increases the number of hint bits are set. But more importantly it also keeps the success rate up when flushing WAL less frequently. That was the original reason for commit 4de82f7d7, which has negative performance consequences in a number of scenarios. This will allow a followup commit to reduce the flush rate. Discussion: 20160118163908.GW10941@awork2.anarazel.de http://git.postgresql.org/pg/commitdiff/db76b1efbbab2441428a9ef21f7ac9ba43c52482
  • Allow the WAL writer to flush WAL at a reduced rate. Commit 4de82f7d7 increased the WAL flush rate, mainly to increase the likelihood that hint bits can be set quickly. More quickly set hint bits can reduce contention around the clog et al. But unfortunately the increased flush rate can have a significant negative performance impact, I have measured up to a factor of ~4. The reason for this slowdown is that if there are independent writes to the underlying devices, for example because shared buffers is a lot smaller than the hot data set, or because a checkpoint is ongoing, the fdatasync() calls force cache flushes to be emitted to the storage. This is achieved by flushing WAL only if the last flush was longer than wal_writer_delay ago, or if more than wal_writer_flush_after (new GUC) unflushed blocks are pending. Based on some tests the default for wal_writer_delay is 1MB, which seems to work well both on SSD and rotational media. To avoid negative performance impact due to 4de82f7d7 an earlier commit (db76b1e) made SetHintBits() more likely to succeed; preventing performance regressions in the pgbench tests I performed. Discussion: 20160118163908.GW10941@awork2.anarazel.de http://git.postgresql.org/pg/commitdiff/7975c5e0a992ae9a45e03d145e0d37e2b5a707f5

Tom Lane pushed:

  • Suppress compiler warnings about useless comparison of unsigned to zero. Reportedly, some compilers warn about tests like "c < 0" if c is unsigned, and hence complain about the character range checks I added in commit 3bb3f42f3749d40b8d4de65871e8d828b18d4a45. This is a bit of a pain since the regex library doesn't really want to assume that chr is unsigned. However, since any such reconfiguration would involve manual edits of regcustom.h anyway, we can put it on the shoulders of whoever wants to do that to adjust this new range-checking macro correctly. Per gripes from Coverity and Andres. http://git.postgresql.org/pg/commitdiff/8c95ae81fab11b75a611b57d6aaa0ef77e8b8e41
  • Improve documentation about CREATE INDEX CONCURRENTLY. Clarify the description of which transactions will block a CREATE INDEX CONCURRENTLY command from proceeding, and mention that the index might still not be usable after CREATE INDEX completes. (This happens if the index build detected broken HOT chains, so that pg_index.indcheckxmin gets set, and there are open old transactions preventing the xmin horizon from advancing past the index's initial creation. I didn't want to explain what broken HOT chains are, though, so I omitted an explanation of exactly when old transactions prevent the index from being used.) Per discussion with Chris Travers. Back-patch to all supported branches, since the same text appears in all of them. http://git.postgresql.org/pg/commitdiff/a65313f28bfc264573a066271a11172d109dc2c4
  • Make plpython cope with funny characters in function names. A function name that's double-quoted in SQL can contain almost any characters, but we were using that name directly as part of the name generated for the Python-level function, and Python doesn't like anything that isn't pretty much a standard identifier. To fix, replace anything that isn't an ASCII letter or digit with an underscore in the generated name. This doesn't create any risk of duplicate Python function names because we were already appending the function OID to the generated name to ensure uniqueness. Per bug #13960 from Jim Nasby. Patch by Jim Nasby, modified a bit by me. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/66f503868b2ac1163aaf48a2f76d8be02af0bc81
  • Fix multiple bugs in contrib/pgstattuple's pgstatindex() function. Dead or half-dead index leaf pages were incorrectly reported as live, as a consequence of a code rearrangement I made (during a moment of severe brain fade, evidently) in commit d287818eb514d431. The index metapage was not counted in index_size, causing that result to not agree with the actual index size on-disk. Index root pages were not counted in internal_pages, which is inconsistent compared to the case of a root that's also a leaf (one-page index), where the root would be counted in leaf_pages. Aside from that inconsistency, this could lead to additional transient discrepancies between the reported page counts and index_size, since it's possible for pgstatindex's scan to see zero or multiple pages marked as BTP_ROOT, if the root moves due to a split during the scan. With these fixes, index_size will always be exactly one page more than the sum of the displayed page counts. Also, the index_size result was incorrectly documented as being measured in pages; it's always been measured in bytes. (While fixing that, I couldn't resist doing some small additional wordsmithing on the pgstattuple docs.) Including the metapage causes the reported index_size to not be zero for an empty index. To preserve the desired property that the pgstattuple regression test results are platform-independent (ie, BLCKSZ configuration independent), scale the index_size result in the regression tests. The documentation issue was reported by Otsuka Kenji, and the inconsistent root page counting by Peter Geoghegan; the other problems noted by me. Back-patch to all supported branches, because this has been broken for a long time. http://git.postgresql.org/pg/commitdiff/48e6c943e5f11f5d80cabdbcd98f79e3dbad1988
  • Add an explicit representation of the output targetlist to Paths. Up to now, there's been an assumption that all Paths for a given relation compute the same output column set (targetlist). However, there are good reasons to remove that assumption. For example, an indexscan on an expression index might be able to return the value of an expensive function "for free". While we have the ability to generate such a plan today in simple cases, we don't have a way to model that it's cheaper than a plan that computes the function from scratch, nor a way to create such a plan in join cases (where the function computation would normally happen at the topmost join node). Also, we need this so that we can have Paths representing post-scan/join steps, where the targetlist may well change from one step to the next. Therefore, invent a "struct PathTarget" representing the columns we expect a plan step to emit. It's convenient to include the output tuple width and tlist evaluation cost in this struct, and there will likely be additional fields in future. While Path nodes that actually do have custom outputs will need their own PathTargets, it will still be true that most Paths for a given relation will compute the same tlist. To reduce the overhead added by this patch, keep a "default PathTarget" in RelOptInfo, and allow Paths that compute that column set to just point to their parent RelOptInfo's reltarget. (In the patch as committed, actually every Path is like that, since we do not yet have any cases of custom PathTargets.) I took this opportunity to provide some more-honest costing of PlaceHolderVar evaluation. Up to now, the assumption that "scan/join reltargetlists have cost zero" was applied not only to Vars, where it's reasonable, but also PlaceHolderVars where it isn't. Now, we add the eval cost of a PlaceHolderVar's expression to the first plan level where it can be computed, by including it in the PathTarget cost field and adding that to the cost estimates for Paths. This isn't perfect yet but it's much better than before, and there is a way forward to improve it more. This costing change affects the join order chosen for a couple of the regression tests, changing expected row ordering. http://git.postgresql.org/pg/commitdiff/19a541143a09c067ec8cac77ec6a64eb5b1b662b
  • Cosmetic improvements in new config_info code. Coverity griped about use of unchecked strcpy() into a local variable. There's unlikely to be any actual bug there, since no caller would be passing a path longer than MAXPGPATH, but nonetheless use of strlcpy() seems preferable. While at it, get rid of unmaintainable separation between list of field names and list of field values in favor of initializing them in parallel. And we might as well declare get_configdata()'s path argument as const char *, even though no current caller needs that. http://git.postgresql.org/pg/commitdiff/c7a1c5a6b6aa4bbc2c9619edc94368fccc1c8c8e
  • Docs: make prose discussion match the ordering of Table 9-58. The "Session Information Functions" table seems to be sorted mostly alphabetically (although it's not perfect), which would be all right if it didn't lead to some related functions being described in a pretty nonintuitive order. Also, the prose discussions after the table were in an order that hardly matched the table at all. Rearrange to make things a bit easier to follow. http://git.postgresql.org/pg/commitdiff/64a169d1313d6b99b48c2d270df121ef43c03269

Ãlvaro Herrera pushed:

  • pgbench: avoid FD_ISSET on an invalid file descriptor The original code wasn't careful to test the file descriptor returned by PQsocket() for an invalid socket. If an invalid socket did turn up, that would amount to calling FD_ISSET with fd = -1, whereby undefined behavior can be invoked. To fix, test file descriptor for validity and stop further processing if that fails. Problem noticed by Coverity. There is an existing FD_ISSET callsite that does check for invalid sockets beforehand, but the error message reported by it was strerror(errno); in testing the aforementioned change, that turns out to result in "bad socket: Success" which isn't terribly helpful. Instead use PQerrorMessage() in both places which is more likely to contain an useful error message. Backpatch-through: 9.1. http://git.postgresql.org/pg/commitdiff/5df44d14ba9fd3f6149c3fa0919745c9e24bcffe

Tatsuo Ishii pushed:

Michael Meskes pushed:

Bruce Momjian pushed:

Robert Haas pushed:

Peter Eisentraut pushed:

Simon Riggs pushed:

  • Correct StartupSUBTRANS for page wraparound StartupSUBTRANS() incorrectly handled cases near the max pageid in the subtrans data structure, which in some cases could lead to errors in startup for Hot Standby. This patch wraps the pageids correctly, avoiding any such errors. Identified by exhaustive crash testing by Jeff Janes. Jeff Janes http://git.postgresql.org/pg/commitdiff/481725c0ba731b77fb32cadb12013373e378011a

Dean Rasheed pushed:

Andrew Dunstan pushed:

  • Fix two-argument jsonb_object when called with empty arrays Some over-eager copy-and-pasting on my part resulted in a nonsense result being returned in this case. I have adopted the same pattern for handling this case as is used in the one argument form of the function, i.e. we just skip over the code that adds values to the object. Diagnosis and patch from Michael Paquier, although not quite his solution. Fixes bug #13936. Backpatch to 9.5 where jsonb_object was introduced. http://git.postgresql.org/pg/commitdiff/94c745eb189e2122a3ff86c24443b11408ea2376

Correctifs rejetés (à ce jour)

No one was disappointed this week :-)

Correctifs en attente

Craig Ringer sent in two more revisions of a patch to implement failover slots.

Amit Langote sent in two more revisions of a patch to implement declarative partitioning.

Etsuro Fujita and Ashutosh Bapat traded patches to fix some breakage in foreign_join_ok.

Teodor Sigaev sent in another revision of a patch to add support for box type in SP-GiST index.

Dmitry Ivanov sent in a patch to implement ALTER ... OWNER TO ... CASCADE.

Eugene Kazakov sent in a patch to add an m4 check for the TAP perl modules.

Martin Liška sent in a patch to clean up for -sanitize=use-after-scope.

Fabien COELHO and Robert Haas traded patches to extend pgbench with expressions.

SAWADA Masahiko sent in another revision of a patch to allow N>1 synchronous standby servers.

Alexander Korotkov sent in three more revisions of a patch to implement access method extensibility.

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

Filip Rembiałkowski sent in two more revisions of a patch to make NOTIFY list de-duplication optional.

Tom Lane sent in two revisions of a patch to add a new function that reports the set of PIDs directly blocking a given PID.

Ashutosh Bapat sent in another revision of a patch to fix the docs for GetExistingLocalJoinPath().

Sehrope Sarkuni sent in a patch to add tab completion for CREATE DATABASE ... TEMPLATE ... to psql.

Julien Rouhaud sent in two more revisions of a patch to add an auto_explain sample rate.

Haribabu Kommi sent in another revision of a patch to implement aggregation in parallel.

Kyotaro HORIGUCHI sent in a patch to add an additional member in the struct ErrorData to hold a message id.

Artur Zakirov sent in two more revisions of a patch to improve Hunspell dictionary support.

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

Jim Nasby and Pavel Stěhule traded patches to add a parse_ident() function.

Pavel Stěhule sent in three more revisions of a patch to add an ereport function to PL/PythonU.

Ashutosh Bapat sent in a patch to allow pushing down sorted joins to the PostgreSQL FDW.

Michaël Paquier sent in another revision of a patch to add in-core regression tests.

Amit Kapila sent in a patch to add prepared statement support for parallel query.

Artur Zakirov sent in another revision of a patch to add fuzzy substring searching to the pg_trgm extension.

Stas Kelvich sent in another revision of a patch to add tsvector editing functions.

Constantin S. Pan sent in two more revisions of a patch to speed up building GIN indexes with parallel workers.

Etsuro Fujita sent in another revision of a patch to speed up writes to foreign tables.

Kyotaro HORIGUCHI sent in a patch to make the SQL parser part of psqlscan independent of psql, get pgbench to use same, and change the way to hold a command list from an array to a linked list.

Suraj Kharage sent in a patch to add regression tests for multisync (>1 synchronous replica) replication.

Dmitry Dolgov sent in a patch to enable inserting a new value into an array at arbitrary position in jsonb.

Anastasia Lubennikova sent in two more revisions of a patch to store duplicates more efficiently in a B-tree index.

Takashi Horikawa sent in two revisions of a patch to fix a typo in bufmgr.c that results in waste of memory.

Christoph Berg sent in three revisions of a patch to relax the permission checks on SSL keys.

Kyotaro HORIGUCHI sent in a patch to add a mult-socket version of WaitLatchOrSocket.

Corey Huinker sent in a patch to add \gexec to psql.

Peter Eisentraut sent in a patch to fix some warnings generated by GCC 6.

Andres Freund sent in another revision of a patch to allow to trigger kernel writeback after a configurable number of writes, and atop this, allow checkpoint sorting and balancing.

Amit Kapila sent in another revision of a patch to speed up clog Access by increasing CLOG buffers.

Pavel Stěhule sent in another revision of a patch to allow PL/pgsql to use %ARRAYTYPE and %ELEMENTTYPE in declarations.

Michaël Paquier sent in another revision of a patch to add hot standby checkpoints.

Tom Lane sent in a patch to get rid of lockGroupLeaderIdentifier.