La PGCon 2018 se tiendra à Ottawa du 29 mai au 1er juin 2018. L'appel à conférenciers sera lancé prochainement : https://www.pgcon.org/2018/

Prague PostgreSQL Developer Day 2018 (P2D2 2018) est une série de conférences sur deux jours qui aura lieu les 14 & 15 février 2018 à Prague (République Tchèque). L'appel à conférenciers est lancé jusqu'au 5 janvier 2018 à l'adresse https://p2d2.cz/callforpapers : http://www.p2d2.cz/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en novembre

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/20171105203248.GA14597@fetter.org

Submit news and announcements by Sunday at 3:00pm EST5EDT. Please send English language ones to david@fetter.org, German language to pwn@pgug.de, Italian language to pwn@itpug.org.

Correctifs appliqués

Magnus Hagander pushed:

Álvaro Herrera pushed:

Tom Lane pushed:

  • Doc: call out UPDATE syntax change as a v10 compatibility issue. The change made by commit 906bfcad7 means that if you're writing a parenthesized column list in UPDATE ... SET, but that column list is only one column, you now need to write ROW(expression) on the righthand side, not just a parenthesized expression. This was an intentional change for spec compatibility and potential future expansion of the possibilities for the RHS, but I'd neglected to document it as a compatibility issue, figuring that hardly anyone would bother with parenthesized syntax for a single target column. I was wrong, as shown by questions from Justin Pryzby, Adam Brusselback, and others. Move the release note item into the compatibility section and point out the behavior change for a single target column. Discussion: https://postgr.es/m/CAMjNa7cDLzPcs0xnRpkvqmJ6Vb6G3EH8CYGp9ZBjXdpFfTz6dg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/86182b18957b8f9e8045d55b137aeef7c9af9916
  • Fix underqualified cast-target type names in pg_dump and psql queries. Queries running with some non-pg_catalog schema frontmost in their search path need to be careful to schema-qualify type names that should be sought in pg_catalog. Vitaly Burovoy reported an oversight of this sort in pg_dump's dumpSequence, and grepping detected another one in psql's describeOneTableDetails, both introduced by sequence-related changes in v10. In pg_dump, we can fix things by removing the cast altogether, since it doesn't really matter what data types are reported for these query result columns. Likewise in psql, the query seemed to be working unduly hard to get a result that's guaranteed to be exactly 'bigint'. I also changed a couple of occurrences of "::char" similarly. These are not bugs, since "char" is a typename keyword and not subject to search_path rules, but it seems better to use uniform style. Vitaly Burovoy and Tom Lane Discussion: https://postgr.es/m/CAKOSWN=ds66zLw2SqkLTM8wbXFgDbc_OdkmT3dJfPT2mE5kipA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/080351466c5a669bf35a323bdec9e296330a5dbb
  • Fix ALTER TABLE code to update domain constraints when needed. It's possible for dropping a column, or altering its type, to require changes in domain CHECK constraint expressions; but the code was previously only expecting to find dependent table CHECK constraints. Make the necessary adjustments. This is a fairly old oversight, but it's a lot easier to encounter the problem in the context of domains over composite types than it was before. Given the lack of field complaints, I'm not going to bother with a back-patch, though I'd be willing to reconsider that decision if someone does complain. Patch by me, reviewed by Michael Paquier Discussion: https://postgr.es/m/30656.1509128130@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/af20e2d728eb508bb169e7294e4e210a3459833a
  • Allow bitmap scans to operate as index-only scans when possible. If we don't have to return any columns from heap tuples, and there's no need to recheck qual conditions, and the heap page is all-visible, then we can skip fetching the heap page altogether. Skip prefetching pages too, when possible, on the assumption that the recheck flag will remain the same from one page to the next. While that assumption is hardly bulletproof, it seems like a good bet most of the time, and better than prefetching pages we don't need. This commit installs the executor infrastructure, but doesn't change any planner cost estimates, thus possibly causing bitmap scans to not be chosen in cases where this change renders them the best choice. I (tgl) am not entirely convinced that we need to account for this behavior in the planner, because I think typically the bitmap scan would get chosen anyway if it's the best bet. In any case the submitted patch took way too many shortcuts, resulting in too many clearly-bad choices, to be committable. Alexander Kuzmenkov, reviewed by Alexey Chernyshov, and whacked around rather heavily by me. Discussion: https://postgr.es/m/239a8955-c0fc-f506-026d-c837e86c827b@postgrespro.ru https://git.postgresql.org/pg/commitdiff/7c70996ebf0949b142a99c9445061c3c83ce62b3
  • Doc: update URL for check_postgres. Reported by Dan Vianello. Discussion: https://postgr.es/m/e6e12f18f70e46848c058084d42fb651@KSTLMEXGP001.CORP.CHARTERCOM.com https://git.postgresql.org/pg/commitdiff/c0e2062d3214f6230a0e1eee9236b202bda9221f
  • Teach planner to account for HAVING quals in aggregation plan nodes. For some reason, we have never accounted for either the evaluation cost or the selectivity of filter conditions attached to Agg and Group nodes (which, in practice, are always conditions from a HAVING clause). Applying our regular selectivity logic to post-grouping conditions is a bit bogus, but it's surely better than taking the selectivity as 1.0. Perhaps someday the extended-statistics mechanism can be taught to provide statistics that would help us in getting non-default estimates here. Per a gripe from Benjamin Coutu. This is surely a bug fix, but I'm hesitant to back-patch because of the prospect of destabilizing existing plan choices. Given that it took us this long to notice the bug, it's probably not hurting too many people in the field. Discussion: https://postgr.es/m/20968.1509486337@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/7b6c07547190f056b0464098bb5a2247129d7aa2
  • Fix corner-case errors in brin_doupdate(). In some cases the BRIN code releases lock on an index page, and later re-acquires lock and tries to check that the tuple it was working on is still there. That check was a couple bricks shy of a load. It didn't consider that the page might have turned into a "revmap" page. (The samepage code path doesn't call brin_getinsertbuffer(), so it isn't protected by the checks for revmap status there.) It also didn't check whether the tuple offset was now off the end of the linepointer array. Since commit 24992c6db the latter case is pretty common, but at least in principle it could have occurred before that. The net result is that concurrent updates of a BRIN index could fail with errors like "invalid index offnum" or "inconsistent range map". Per report from Tomas Vondra. Back-patch to 9.5, since this code is substantially the same in all versions containing BRIN. Discussion: https://postgr.es/m/10d2b9f9-f427-03b8-8ad9-6af4ecacbee9@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/62a16572d5714bfb19e2a273e61218be6682d3df
  • Set the metapage's pd_lower correctly in brin, gin, and spgist indexes. Previously, these index types left the pd_lower field set to the default SizeOfPageHeaderData, which is really a lie because it ought to point past whatever space is being used for metadata. The coding accidentally failed to fail because we never told xlog.c that the metapage is of standard format --- but that's not very good, because it impedes WAL consistency checking, and in some cases prevents compression of full-page images. To fix, ensure that we set pd_lower correctly, not only when creating a metapage but whenever we write it out (these apparently redundant steps are needed to cope with pg_upgrade'd indexes that don't yet contain the right value). This allows telling xlog.c that the page is of standard format. The WAL consistency check mask functions are made to mask only if pd_lower appears valid, which I think is likely unnecessary complication, since any metapage appearing in a v11 WAL stream should contain valid pd_lower. But it doesn't cost much to be paranoid. Amit Langote, reviewed by Michael Paquier and Amit Kapila Discussion: https://postgr.es/m/0d273805-0e9e-ec1a-cb84-d4da400b8f85@lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/81e334ce4e6d687d548e60ad8954b7dfd9e568a2
  • pgbench: replace run-time string comparisons with an enum identifier. Minor refactoring that should yield some performance benefit. Fabien Coelho, reviewed by Aleksandr Parfenov Discussion: https://postgr.es/m/alpine.DEB.2.20.1709230538130.4999@lancre https://git.postgresql.org/pg/commitdiff/f987f83de20afe3ba78be1e15db5dffe7488faa7
  • Flag index metapages as standard-format in xlog.c calls. btree, hash, and bloom indexes all set up their metapages in standard format (that is, with pd_lower and pd_upper correctly delimiting the unused area); but they mostly didn't inform the xlog routines of this. When calling log_newpage[_buffer], this is bad because it loses the opportunity to compress unused data out of the WAL record. When calling XLogRegisterBuffer, it's not such a performance problem because all of these call sites also use REGBUF_WILL_INIT, preventing an FPI image from being written. But it's still a good idea to provide the flag when relevant, because that aids WAL consistency checking. This completes the project of getting all the in-core index AMs to handle their metapage WAL operations similarly. Amit Kapila, reviewed by Michael Paquier Discussion: https://postgr.es/m/0d273805-0e9e-ec1a-cb84-d4da400b8f85@lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/4c11d2c559e76892156fd08d6a3cf5e1848a017f
  • Avoid looping through line pointers twice in PageRepairFragmentation(). There doesn't seem to be any good reason to do the filling of the itemidbase[] array separately from the first traversal of the pointers. It's certainly not a win if there are any line pointers with storage, and even if there aren't, this change doesn't insert code into the part of the first loop that will be traversed in that case. So let's just merge the two loops. Yura Sokolov, reviewed by Claudio Freire Discussion: https://postgr.es/m/e49befcc6f1d7099834c6fdf5c675a60@postgrespro.ru https://git.postgresql.org/pg/commitdiff/a9169f0200fc57e01cbd216bbd41c9ea3a79a7b0
  • First-draft release notes for 10.1. As usual, the release notes for other branches will be made by cutting these down, but put them up for community review first. Note that a fair percentage of the entries apply only to prior branches because their issue was already fixed in 10.0. https://git.postgresql.org/pg/commitdiff/42de8a0255c2509bf179205e94b9d65f9d6f3cf9
  • Release notes for 10.1, 9.6.6, 9.5.10, 9.4.15, 9.3.20, 9.2.24. In the v10 branch, also back-patch the effects of 1ff01b390 and c29c57890 on these files, to reduce future maintenance issues. (I'd do it further back, except that the 9.X branches differ anyway due to xlog-to-wal link tag renaming.) https://git.postgresql.org/pg/commitdiff/b35b185bf705c4dbaf21198c81b3d85f4a96804a

Robert Haas pushed:

Stephen Frost pushed:

Michael Meskes pushed:

Peter Eisentraut pushed:

Noah Misch pushed:

Correctifs en attente

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.

Alexander Kuzmenkov sent in another revision of a patch to implement full merge join on comparison clause.

Alexander Korotkov sent in another revision of a patch to fix the cube contrib extension's ORDER BY issue and KNN operations.

Christoph Dreis sent in a patch to remove instances of "the the" in the code base.

Dagfinn Ilmari Mannsåker sent in a patch to fix a typo in a get_collation_name() comment.

Tom Lane sent in a patch to rewrite PL/PythonU's typeio code.

Tomas Vondra sent in another revision of a patch to implement BRIN multi-range min-max indexes and BRIN bloom indexes.

Andres Freund sent in another revision of a patch to speed up overflow checks for int and float types.

Kyotaro HORIGUCHI sent in a patch to add several pieces of vacuum information to the pg_stat_*_tables.

Chris Travers sent in two revisions of a patch to restrict pg_rewind to whitelisted directories.

Lætitia Avrot and Amit Langote traded patches to add several missing options to the ALTER TABLE synopsis.

Amul Sul sent in two more revisions of a patch to implement hash partitioning.

Thomas Munro sent in two more revisions of a patch to implement parallel hash.

Amit Langote sent in three more revisions of a patch to prune partitions faster.

Amit Kapila sent in two more revisions of a patch to parallelize queries containing initplans.

Raúl Marín Rodríguez sent in two revisions of a patch to add support for pow() in pgbench.

Alexander Korotkov sent in a patch to add VP trees as an SP-GiST extension.

Anastasia Lubennikova sent in another revision of a patch to implement covering + unique indexes.

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

Kyotaro HORIGUCHI sent in another revision of a patch to protect syscache from bloating with negative cache entries.

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

David Rowley sent in a patch to remove [Merge]Append nodes which contain a single subpath.

Peter Eisentraut sent in a patch to consistently catch errors from Python _New() functions.

Simon Riggs sent in another revision of a patch to remove the secondary checkpoint.

Vitaly Burovoy sent in two revisions of a patch to fix dumping pre-10 DBs by pg_dump10 if table "name" exists.

Peter Eisentraut sent in a patch to implement SQL procedures, which can span transactions.

Peter Eisentraut sent in a patch to implement transaction control in PL/PythonU procedures.

Shubham Barai sent in another revision of a patch to implement predicate locking in GiST indexes.

Takayuki Tsunakawa sent in another revision of a patch to implement statement-level rollback.

Peter Eisentraut sent in a patch to add support for INSERT OVERRIDING to the PostgreSQL FDW.

Peter Eisentraut sent in a patch to allow returning dynamic result sets from procedures.

Peter Eisentraut sent in two revisions of a patch to add const decorations to many char * arguments in functions.

Oleg Ivanov sent in a patch to implement generic WAL compression.

David Rowley sent in a patch to support removing LEFT JOINs with DISTINCT/GROUP BY.

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

Pavel Stěhule sent in another revision of a patch to enable specifying sort order for psql's describe commands when size is present.

Petr Jelínek sent in another revision of a patch to fix walsender timeouts when decoding large transactions.

Masahiko Sawada sent in another revision of a patch to add an explicit relation name in VACUUM VERBOSE log.

Ildus Kurbangaliev sent in another revision of a patch to add custom compression methods.

Tom Lane sent in a patch to fix how reltuples are counted in VACUUM.

Andreas Karlsson sent in another revision of a patch to add GnuTLS support.

Fabien COELHO sent in another revision of a patch to pgbench to use enum for meta commands.

David Rowley sent in a patch to implement ALists (ArrayLists), these being like a List, only using an array as the data structure rather than a linked list.

Piotr Stefaniak and Nikita Glukhov traded patches to implement SQL-JSON.

Thomas Munro sent in a patch to fix some LDAP URI decoding bugs.

Antonin Houska sent in another revision of a patch to implement aggregate pushdown.

David Fetter sent in a patch to skip an unneeded temp file in 'make html'.

Thomas Munro sent in two revisions of a patch to add ldaps support for ldap authentication.

Andres Freund sent in a patch to fix pruning of locked and updated tuples.

Fabrízio de Royes Mello sent in two more revisions of a patch to implement hooks for session_start and session_end.

Tom Lane sent in two more revisions of a patch to improve compactify_tuples.

Fabien COELHO sent in another revision of a patch to pgbench to enable storing select results into variables.

Robert Haas sent in two more revisions of a patch to make Gather nodes faster.

Amit Kapila sent in another revision of a patch to ensure that parallel paths include tlist cost.

Lucas (lucas75 AT gmail DOT com) sent in a patch to add a --lock-early option to pg_dump.

Emre Hasegeli sent in another revision of a patch to refactor the geometric functions and operators code, provide a header file for built-in float datatypes, use the built-in float datatype to implement geometric types, fix obvious problems around the line datatype, check for float -0 after multiplications and divisions, and improve test coverage of geometric types.

David Christensen sent in a patch to add a two-argument form of current_setting(NAME, FALLBACK), which avoids erroring out on, for example, non-existence of the setting to be set.

Noah Misch sent in a patch to fix some encoding issues encountered with libxml2 functions in UTF-8-encoded databases.