[ndt: MeetUp à Lyon le 13 décembre : https://www.meetup.com/PostgreSQL-Lyon-User-Group/]

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/20171119212623.GA18632@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

Noah Misch pushed:

Stephen Frost pushed:

Álvaro Herrera pushed:

Robert Haas pushed:

Tom Lane pushed:

  • Allow running just selected steps of pgbench's initialization sequence. This feature caters to specialized use-cases such as running the normal pgbench scenario with nonstandard indexes, or inserting other actions between steps of the initialization sequence. The normal sequence of initialization actions is broken down into half a dozen steps which can be executed in a user-specified order, to the extent to which that's sensible. The actions themselves aren't changed, except to make them more robust against nonstandard uses: * all four tables are now dropped in one DROP command, to reduce assumptions about what foreign key relationships exist; * all four tables are now truncated at the start of the data load step, for consistency; * the foreign key creation commands now specify constraint names, to prevent accidentally creating duplicate constraints by executing the 'f' step twice. Make some cosmetic adjustments in the messages emitted by pgbench so that it's clear which steps are getting run, and so that the messages agree with the documented names of the steps. In passing, fix failure to enforce that the -v option is used only in benchmarking mode. Masahiko Sawada, reviewed by Fabien Coelho, editorialized a bit by me Discussion: https://postgr.es/m/CAD21AoCsz0ZzfCFcxYZ+PUdpkDd5VsCSG0Pre_-K1EgokCDFYA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/591c504fad0de88b559bf28e929d23672179a857
  • Document changes in large-object privilege checking. Commit 5ecc0d738 removed the hard-wired superuser checks in lo_import and lo_export in favor of protecting them with SQL permissions, but failed to adjust the documentation to match. Fix that, and add a <caution> paragraph pointing out the nontrivial security hazards involved with actually granting such permissions. (It's still better than ALLOW_DANGEROUS_LO_FUNCTIONS, though.) Also, commit ae20b23a9 caused large object read/write privilege to be checked during lo_open() rather than in the actual read or write calls. Document that. Discussion: https://postgr.es/m/CAB7nPqRHmNOYbETnc_2EjsuzSM00Z+BWKv9sy6tnvSd5gWT_JA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6d776522d243d38faca6924d9b3c7cfaf0c4860d
  • Rearrange c.h to create a "compiler characteristics" section. Generalize section 1 to handle stuff that is principally about the compiler (not libraries), such as attributes, and collect stuff there that had been dropped into various other parts of c.h. Also, push all the gettext macros into section 8, so that section 0 is really just inclusions rather than inclusions and random other stuff. The primary goal here is to get pg_attribute_aligned() defined before section 3, so that we can use it with int128. But this seems like good cleanup anyway. This patch just moves macro definitions around, and shouldn't result in any changes in generated code. But I'll push it out separately to see if the buildfarm agrees. Discussion: https://postgr.es/m/20171110185747.31519.28038@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/91aec93e6089a5ba49cce0aca3bf7f7022d62ea4
  • Prevent int128 from requiring more than MAXALIGN alignment. Our initial work with int128 neglected alignment considerations, an oversight that came back to bite us in bug #14897 from Vincent Lachenal. It is unsurprising that int128 might have a 16-byte alignment requirement; what's slightly more surprising is that even notoriously lax Intel chips sometimes enforce that. Raising MAXALIGN seems out of the question: the costs in wasted disk and memory space would be significant, and there would also be an on-disk compatibility break. Nor does it seem very practical to try to allow some data structures to have more-than-MAXALIGN alignment requirement, as we'd have to push knowledge of that throughout various code that copies data structures around. The only way out of the box is to make type int128 conform to the system's alignment assumptions. Fortunately, gcc supports that via its __attribute__(aligned()) pragma; and since we don't currently support int128 on non-gcc-workalike compilers, we shouldn't be losing any platform support this way. Although we could have just done pg_attribute_aligned(MAXIMUM_ALIGNOF) and called it a day, I did a little bit of extra work to make the code more portable than that: it will also support int128 on compilers without __attribute__(aligned()), if the native alignment of their 128-bit-int type is no more than that of int64. Add a regression test case that exercises the one known instance of the problem, in parallel aggregation over a bigint column. This will need to be back-patched, along with the preparatory commit 91aec93e6. But let's see what the buildfarm makes of it first. Discussion: https://postgr.es/m/20171110185747.31519.28038@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/7518049980be1d90264addab003476ae105f70d4
  • Further refactoring of c.h and nearby files. This continues the work of commit 91aec93e6 by getting rid of a lot of Windows-specific funny business in "section 0". Instead of including pg_config_os.h in different places depending on platform, let's standardize on putting it before the system headers, and in consequence reduce win32.h to just what has to appear before the system headers or the body of c.h (the latter category seems to include only PGDLLIMPORT and PGDLLEXPORT). The rest of what was in win32.h is moved to a new sub-include of port.h, win32_port.h. Some of what was in port.h seems to better belong there too. It's possible that I missed some declaration ordering dependency that needs to be preserved, but hopefully the buildfarm will find that out in short order. Unlike the previous commit, no back-patch, since this is just cleanup not a prerequisite for a bug fix. Discussion: https://postgr.es/m/29650.1510761080@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ed9b3606dadb461aac57e41ac509f3892095a394
  • Fix bogus logic for checking data dirs' versions within pg_upgrade. Commit 9be95ef15 failed to cure all of the redundancy here: we were actually calling get_major_server_version() three times for each of the old and new data directories. While that's not enormously expensive, it's still sloppy. A. Akenteva Discussion: https://postgr.es/m/f9266a85d918a3cf3a386b5148aee666@postgrespro.ru https://git.postgresql.org/pg/commitdiff/164d6338785b0b6c5a1ac30ee3e4b63bd77441ba
  • Define _WINSOCK_DEPRECATED_NO_WARNINGS in all MSVC builds. Commit 0fb54de9a thought that this was only needed in VS2015 and later, but buildfarm member woodlouse shows that at least VS2013 whines as well. Let's just define it regardless of MSVC version; it should be harmless enough in older releases. Also, in the wake of ed9b3606d, it seems better to put it in win32_port.h where <winsock2.h> is included. Since this is only suppressing a pedantic compiler warning, I don't feel a need for a back-patch. Discussion: https://postgr.es/m/20124.1510850225@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ff2d4356f8b18f5489e5d7b1f8b4b5357d088c8c
  • Make PL/Python handle domain-type conversions correctly. Fix PL/Python so that it can handle domains over composite, and so that it enforces domain constraints correctly in other cases that were not always done properly before. Notably, it didn't do arrays of domains right (oversight in commit c12d570fa), and it failed to enforce domain constraints when returning a composite type containing a domain field, and if a transform function is being used for a domain's base type then it failed to enforce domain constraints on the result. Also, in many places it missed checking domain constraints on null values, because the plpy_typeio code simply wasn't called for Py_None. Rather than try to band-aid these problems, I made a significant refactoring of the plpy_typeio logic. The existing design of recursing for array and composite members is extended to also treat domains as containers requiring recursion, and the APIs for the module are cleaned up and simplified. The patch also modifies plpy_typeio to rely on the typcache more than it did before (which was pretty much not at all). This reduces the need for repetitive lookups, and lets us get rid of an ad-hoc scheme for detecting changes in composite types. I added a couple of small features to typcache to help with that. Although some of this is fixing bugs that long predate v11, I don't think we should risk a back-patch: it's a significant amount of code churn, and there've been no complaints from the field about the bugs. Tom Lane, reviewed by Anthony Bykov Discussion: https://postgr.es/m/24449.1509393613@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/687f096ea9da82d267f1809a5f3fdfa027092045
  • Clean up warnings in MinGW builds. Experimentation with modern MinGW (specifically the 5.0.2 version packaged for Fedora 26) shows that its version of sys/stat.h *does* provide S_IRGRP and friends, contrary to the expectation of win32_port.h. This results in an astonishing number of compiler warnings, and perhaps in incorrect code --- I'm not sure if the nonzero values supplied by MinGW's header actually do anything. Hence, adjust win32_port.h to only define these macros if <sys/stat.h> doesn't. This might be worth back-patching, but given the lack of complaints so far, I'm not too excited about it. https://git.postgresql.org/pg/commitdiff/09a777447a858a01ac4d547d678ba295d9542c3b
  • Prevent to_number() from losing data when template doesn't match exactly. Non-data template patterns would consume characters whether or not those characters were what the pattern expected, for example SELECT TO_NUMBER('1234', '9,999'); produced 134 because the '2' got eaten by the comma pattern. This seems undesirable, not least because it doesn't happen in Oracle. For the ',' and 'G' template patterns, we can fix this by consuming characters only if they match what the pattern would output. For non-data patterns such as 'L' and 'TH', it seems impractical to tighten things up to the point of consuming only exact matches to what the pattern would output; but we can improve matters quite a lot by redefining the behavior as "consume only characters that aren't digits, signs, decimal point, or comma". Also, fix it so that the behavior is to consume the number of *characters* the pattern would output, not the number of *bytes*. The old coding would do surprising things with non-ASCII currency symbols, for example. (It would be good to apply that rule for literal text as well, but this commit only fixes it for non-data patterns.) Oliver Ford, reviewed by Thomas Munro and Nathan Wagner, and whacked around a bit more by me Discussion: https://postgr.es/m/CAGMVOdvpbMqPf9XWNzOwBpzJfErkydr_fEGhmuDGa015z97mwg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e87d4965bd39e4d0d56346c1bbe9361d3eb9ff0a
  • Provide modern examples of how to auto-start Postgres on macOS. The scripts in contrib/start-scripts/osx don't work at all on macOS 10.10 (Yosemite) or later, because they depend on SystemStarter which Apple deprecated long ago and removed in 10.10. Add a new subdirectory contrib/start-scripts/macos with scripts that use the newer launchd infrastructure. Since this problem is independent of which Postgres version you're using, back-patch to all supported branches. Discussion: https://postgr.es/m/31338.1510763554@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ac3b9626812b1dd1482ec201711f26af733800f9
  • Remove contrib/start-scripts/osx/. Since those scripts haven't worked at all in macOS releases of 2014 and later, and aren't the recommended way to do it on any release since 2005, there seems little point carrying them into the future. It's very unlikely that anyone would be installing PG >= 11 on a macOS release where they couldn't use contrib/start-scripts/macos/. Discussion: https://postgr.es/m/31338.1510763554@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/527878635030489e464d965b3b64f6caf178f641
  • Fix quoted-substring handling in format parsing for to_char/to_number/etc. This code evidently intended to treat backslash as an escape character within double-quoted substrings, but it was sufficiently confused that cases like ..."foo\\"... did not work right: the second backslash managed to quote the double-quote after it, despite being quoted itself. Rewrite to get that right, while preserving the existing behavior outside double-quoted substrings, which is that backslash isn't special except in the combination \". Comparing to Oracle, it seems that their version of to_char() for timestamps allows literal alphanumerics only within double quotes, while non-alphanumerics are allowed outside quotes; backslashes aren't special anywhere; there is no way at all to emit a literal double quote. (Bizarrely, their to_char() for numbers is different; it doesn't allow literal text at all AFAICT.) The fact that they don't treat backslash as special justifies our existing behavior for backslash outside double quotes. I considered making backslash inside double quotes act the same way (ie, special only if before "), which in a green field would be a more consistent behavior. But that would likely break more existing SQL code than what this patch does. Add some test cases illustrating this behavior. (Only the last new case actually changes behavior in this commit.) Little of this behavior was documented, either, so fix that. Discussion: https://postgr.es/m/3626.1510949486@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/63ca86318dc3d6a768eed78efbc6ca014a0622a8
  • Improve to_date/to_number/to_timestamp behavior with multibyte characters. The documentation says that these functions skip one input character per literal (non-pattern) format character. Actually, though, they skipped one input *byte* per literal *byte*, which could be hugely confusing if either data or format contained multibyte characters. To fix, adjust the FormatNode representation and parse_format() so that multibyte format characters are stored as one FormatNode not several, and adjust the data-skipping bits to advance by pg_mblen() not necessarily one byte. There's no user-visible behavior change on the to_char() side, although the internal representation changes. Commit e87d4965b had already fixed most places where we skip characters on the basis of non-literal format patterns to advance by characters not bytes, but this gets one more place, the SKIP_THth macro. I think everything in formatting.c gets that right now. It'd be nice to have some regression test cases covering this behavior; but of course there's no way to do so in an encoding-agnostic way, and many of the interesting aspects would also require unportable locale selections. So I've not bothered here. Discussion: https://postgr.es/m/28186.1510957703@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/976a1a48fc35cde3c750982be64f872c4de4d343
  • Merge near-duplicate code in RI triggers. Merge ri_restrict_del and ri_restrict_upd into one function ri_restrict. Create a function ri_setnull that is the common implementation of RI_FKey_setnull_del and RI_FKey_setnull_upd. Likewise create a function ri_setdefault that is the common implementation of RI_FKey_setdefault_del and RI_FKey_setdefault_upd. All of these pairs of functions were identical except for needing to check for no-actual-key-change in the UPDATE cases; the one extra if-test is a small price to pay for saving so much code. Aside from removing about 400 lines of essentially duplicate code, this allows us to recognize that we were uselessly caching two identical plans whenever there were pairs of triggers using these duplicated functions (which is likely very common). Ildar Musin, reviewed by Ildus Kurbangaliev Discussion: https://postgr.es/m/ca7064a7-6adc-6f22-ca47-8615ba9425a5@postgrespro.ru https://git.postgresql.org/pg/commitdiff/4797f9b519995ceca5d6b8550b5caa2ff6d19347
  • Fix compiler warning in rangetypes_spgist.c. On gcc 7.2.0, comparing pointer to (Datum) 0 produces a warning. Treat it as a simple pointer to avoid that; this is more consistent with comparable code elsewhere, anyway. Tomas Vondra Discussion: https://postgr.es/m/99410021-61ef-9a9a-9bc8-f733ece637ee@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/52f63bd916184b5f07130c293475d0cf4f202a86

Peter Eisentraut pushed:

  • Remove TRUE and FALSE. Code should be using true and false. Existing code can be changed to those in a backward compatible way. The definitions in the ecpg header files are left around to avoid upsetting those users unnecessarily. Reviewed-by: Michael Paquier <michael.paquier@gmail.com> https://git.postgresql.org/pg/commitdiff/6337865f36da34e9c89aaa292f976bde6df0b065
  • Refactor routine to test connection to SSL server. Move the sub-routines wrappers to check if a connection to a server is fine or not into the test main module. This is useful for other tests willing to check connectivity into a server. Author: Michael Paquier <michael@paquier.xyz> https://git.postgresql.org/pg/commitdiff/642bafa0c5f9f08d106a14f31429e0e0c718b603
  • Support channel binding 'tls-unique' in SCRAM. This is the basic feature set using OpenSSL to support the feature. In order to allow the frontend and the backend to fetch the sent and expected TLS Finished messages, a PG-like API is added to be able to make the interface pluggable for other SSL implementations. This commit also adds a infrastructure to facilitate the addition of future channel binding types as well as libpq parameters to control the SASL mechanism names and channel binding names. Those will be added by upcoming commits. Some tests are added to the SSL test suite to test SCRAM authentication with channel binding. Author: Michael Paquier <michael@paquier.xyz> Reviewed-by: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> https://git.postgresql.org/pg/commitdiff/9288d62bb4b6f302bf13bb2fed3783b61385f315
  • Consistently catch errors from Python _New() functions. Python Py*_New() functions can fail and return NULL in out-of-memory conditions. The previous code handled that inconsistently or not at all. This change organizes that better. If we are in a function that is called from Python, we just check for failure and return NULL ourselves, which will cause any exception information to be passed up. If we are called from PostgreSQL, we consistently create an "out of memory" error. Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> https://git.postgresql.org/pg/commitdiff/d0aa965c0a0ac2ff7906ae1b1dad50a7952efa56

Andrew Dunstan pushed:

Andres Freund pushed:

Correctifs en attente

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

Noah Misch sent in a patch to fix building PL/Perl with ActiveState Perl 5.22 and MSVC.

Mark Rofail sent in another revision of a patch to implement foriegn key arrays.

Anthony Bykov sent in another revision of a patch to add a PL/PythonU TRANSFORM for JSONB.

Dean Rasheed sent in another revision of a patch to add a RLS summary table to the documentation.

Fabien COELHO sent in another revision of a patch to fix a pgbench regression test failure.

Konstantin Knizhnik sent in a patch to the PostgrSQL FDW which enables concurrent execution of aggregates by all remote servers.

Álvaro Herrera sent in three more revisions of a patch to implement indexes for partitioned tables.

Oliver Ford sent in another revision of a patch to fix number skipping in to_number().

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

Masahiko Sawada sent in two revisions of a patch to fix a bug where ginInsertCleanup called from vacuum could still miss tuples to be deleted.

Andres Freund sent in two more revisions of a patch to fix freezing of a dead HOT-updated tuple.

Moon Insung sent in a patch to add a buffer state column to pg_buffercache and add a function to decode buffer state.

Andrew Dunstan sent in another revision of a patch to add a toast_tuple_target parameter to tables.

Peter Eisentraut sent in another revision of a patch to implement SQL procedures.

Anthony Bykov and Pavel Stěhule traded patches to add PL/Perl TRANSFORMs for JSONB.

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

Rushabh Lathia sent in another revision of a patch to implement parallel B-tree index build sorting.

Beena Emerson sent in another revision of a patch to implement runtime partition pruning.

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

David Rowley sent in another revision of a patch to implement bms_add_range().

Nikita Glukhov sent in another revision of a patch to implement SQL/JSON.

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

Robert Haas sent in another revision of a patch to implement wire protocol version negotiation.

Masahiko Sawada sent in a patch to fix a bug that manifested as a failure with user-defined data types in logical replication.

Michaël Paquier and Masahiko Sawada traded patches to fix an assertion failure when the non-exclusive pg_stop_backup aborted.

Jeevan Chalke sent in another revision of a patch to implement partition-wise aggregation/grouping.

Peter Eisentraut sent in another revision of a patch to implement transaction control in PL procedures.

Michaël Paquier sent in another revision of a patch to add timeline ID in backup_label file.

Stas Kelvich sent in a patch to create a flag in pgproc indicating that hw lock is acquired and include in RunningXacts only transaction with this flag set.

Brian Cloutier sent in a patch to add PGDLLIMPORT lines to some variables for use on Windows.

Kyotaro HORIGUCHI sent in another revision of a patch to add columns pg_stat_all_tables.last_vacuum_index_scans, pg_stat_all_tables.vacuum_required, and pg_stat_all_tables.last_vacuum_status/autovacuum_fail_count.

Kyotaro HORIGUCHI sent in another revision of a patch to fix walsender timeouts when decoding a large transaction.

Masahiko Sawada sent in a doc patch to add a missing wal_receiver_status_interval to the subscribers section.

Fabrízio de Royes Mello sent in another revision of a patch to add hooks for session start and session end.

Amit Langote sent in another revision of a patch to prune partitions faster.

Robert Haas sent in a patch to fix multiple problems with satisfies_hash_partition.

Amit Langote sent in a patch to fix a bug in default range partition and constraint exclusion.

Amit Kapila sent in another revision of a patch to pass eflags to workers.

Tomas Vondra sent in another revision of a patch to implement multivariate histograms and MCV lists.

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

GaoZengqi (高增琦) sent in another revision of a patch to add a dependency between client executes and static libraries.

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