Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en janvier

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/20170109204928.GA13529@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:

  • In pgbench logging, avoid assuming that instr_times match Unix timestamps. For aggregated logging, pg_bench supposed that printing the integer part of INSTR_TIME_GET_DOUBLE() would produce a Unix timestamp. That was already broken on Windows, and it's about to get broken on most other platforms as well. As in commit 74baa1e3b, we can remove the entanglement at the price of one extra syscall per transaction; though here it seems more convenient to use time(NULL) instead of gettimeofday(), since we only need integral-second precision. I took the time to do some wordsmithing on the documentation about pgbench's logging features, too. Discussion: https://postgr.es/m/8837.1483216839@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/67a875355e4a6db294e9652af5a883876ddeb4a5
  • Use clock_gettime(), if available, in instr_time measurements. The advantage of clock_gettime() is that the API allows the result to be precise to nanoseconds, not just microseconds as in gettimeofday(). Now that it's routinely possible to do tens of plan node executions in 1us, we really need more precision than gettimeofday() can offer for EXPLAIN ANALYZE to accumulate statistics with. Some research shows that clock_gettime() is available on pretty nearly every modern Unix-ish platform, and as far as I have been able to test, it has about the same execution time as gettimeofday(), so there's no loss in switching over. (By the same token, this doesn't do anything to fix the fact that we really wish clock readings were faster. But there's enough win here to justify changing anyway.) A small side benefit is that on most platforms, we can use CLOCK_MONOTONIC instead of CLOCK_REALTIME and thereby render EXPLAIN impervious to concurrent resets of the system clock. (This means that code must not assume that the contents of struct instr_time have any well-defined interpretation as timestamps, but really that was true before.) Some platforms offer nonstandard clock IDs that might be of interest. This patch knows we should use CLOCK_MONOTONIC_RAW on macOS, because it provides more precision and is faster to read than their CLOCK_MONOTONIC. If there turn out to be many more cases where we need special rules, it might be appropriate to handle the selection of clock ID in configure, but for the moment that doesn't seem worth the trouble. Discussion: https://postgr.es/m/31856.1400021891@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/1d63f7d2d180c8708bc12710254eb7b45823440f
  • Allow SSL configuration to be updated at SIGHUP. It is no longer necessary to restart the server to enable, disable, or reconfigure SSL. Instead, we just create a new SSL_CTX struct (by re-reading all relevant files) whenever we get SIGHUP. Testing shows that this is fast enough that it shouldn't be a problem. In conjunction with that, downgrade the logic that complains about pg_hba.conf "hostssl" lines when SSL isn't active: now that's just a warning condition not an error. An issue that still needs to be addressed is what shall we do with passphrase-protected server keys? As this stands, the server would demand the passphrase again on every SIGHUP, which is certainly impractical. But the case was only barely supported before, so that does not seem a sufficient reason to hold up committing this patch. Andreas Karlsson, reviewed by Michael Banck and Michael Paquier Discussion: https://postgr.es/m/556A6E8A.9030400@proxel.se http://git.postgresql.org/pg/commitdiff/de41869b64d57160f58852eab20a27f248188135
  • Disable prompting for passphrase while (re)loading SSL config files. OpenSSL's default behavior when loading a passphrase-protected key file is to open /dev/tty and demand the password from there. It was kinda sorta okay to allow that to happen at server start, but really that was never workable in standard daemon environments. And it was a complete fail on Windows, where the same thing would happen at every backend launch. Yesterday's commit de41869b6 put the final nail in the coffin by causing that to happen at every SIGHUP; even if you've still got a terminal acting as the server's TTY, having the postmaster freeze until you enter the passphrase again isn't acceptable. Hence, override the default behavior with a callback that returns an empty string, ensuring failure. Change the documentation to say that you can't have a passphrase-protected server key, period. If we can think of a production-grade way of collecting a passphrase from somewhere, we might do that once at server startup and use this callback to feed it to OpenSSL, but it's far from clear that anyone cares enough to invest that much work in the feature. The lack of complaints about the existing fractionally-baked behavior suggests nobody's using it anyway. Discussion: https://postgr.es/m/29982.1483412575@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/1e942c7474d7b5e4bfa04918d2f68d95902f17b3
  • Re-allow SSL passphrase prompt at server start, but not thereafter. Leave OpenSSL's default passphrase collection callback in place during the first call of secure_initialize() in server startup. Although that doesn't work terribly well in daemon contexts, some people feel we should not break it for anyone who was successfully using it before. We still block passphrase demands during SIGHUP, meaning that you can't adjust SSL configuration on-the-fly if you used a passphrase, but this is no worse than what it was before commit de41869b6. And we block passphrase demands during EXEC_BACKEND reloads; that behavior wasn't useful either, but at least now it's documented. Tweak some related log messages for more readability, and avoid issuing essentially duplicate messages about reload failure caused by a passphrase. Discussion: https://postgr.es/m/29982.1483412575@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/6667d9a6d77b9a6eac89638ac363b6d03da253c1
  • Prefer int-wide pg_atomic_flag over char-wide when using gcc intrinsics. configure can only probe the existence of gcc intrinsics, not how well they're implemented, and unfortunately the answer is sometimes "badly". In particular we've found that multiple compilers fail to implement char-width __sync_lock_test_and_set() correctly on PPC; and even a correct implementation would necessarily be pretty inefficient, since that hardware has only a word-wide primitive to work with. Given the knowledge we've accumulated in s_lock.h, it appears that it's best to rely on int-width TAS operations on most non-Intel architectures. Hence, pick int not char when both are nominally available to us in generic-gcc.h (note that that code is not used for x86[_64]). Back-patch to fix regression test failures on FreeBSD/PPC. Ordinarily back-patching a change like this would be verboten because of ABI breakage. But since pg_atomic_flag is not yet used in any Postgres data structure, there's no ABI to break. It seems safer to back-patch to avoid possible gotchas, if someday we do back-patch something that uses pg_atomic_flag. Discussion: https://postgr.es/m/25414.1483076673@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/698127a4a9bc3c74659bf0e5383b1ed99aeb1570
  • Handle OID column inheritance correctly in ALTER TABLE ... INHERIT. Inheritance operations must treat the OID column, if any, much like regular user columns. But MergeAttributesIntoExisting() neglected to do that, leading to weird results after a table with OIDs is associated to a parent with OIDs via ALTER TABLE ... INHERIT. Report and patch by Amit Langote, reviewed by Ashutosh Bapat, some adjustments by me. It's been broken all along, so back-patch to all supported branches. Discussion: https://postgr.es/m/cb13cfe7-a48c-5720-c383-bb843ab28298@lab.ntt.co.jp http://git.postgresql.org/pg/commitdiff/d86f40009b6b019f794819a9af9038cff0cac6f3
  • Fix handling of empty arrays in array_fill(). array_fill(..., array[0]) produced an empty array, which is probably what users expect, but it was a one-dimensional zero-length array which is not our standard representation of empty arrays. Also, for no very good reason, it rejected empty input arrays; that case should be allowed and produce an empty output array. In passing, remove the restriction that the input array(s) have lower bound 1. That seems rather pointless, and it would have needed extra complexity to make the check deal with empty input arrays. Per bug #14487 from Andrew Gierth. It's been broken all along, so back-patch to all supported branches. Discussion: https://postgr.es/m/20170105152156.10135.64195@wrigleys.postgresql.org http://git.postgresql.org/pg/commitdiff/82f8107b92c9104ec9d9465f3f6a4c6dab4c124a
  • Invalidate cached plans on FDW option changes. This fixes problems where a plan must change but fails to do so, as seen in a bug report from Rajkumar Raghuwanshi. For ALTER FOREIGN TABLE OPTIONS, do this through the standard method of forcing a relcache flush on the table. For ALTER FOREIGN DATA WRAPPER and ALTER SERVER, just flush the whole plan cache on any change in pg_foreign_data_wrapper or pg_foreign_server. That matches the way we handle some other low-probability cases such as opclass changes, and it's unclear that the case arises often enough to be worth working harder. Besides, that gives a patch that is simple enough to back-patch with confidence. Back-patch to 9.3. In principle we could apply the code change to 9.2 as well, but (a) we lack postgres_fdw to test it with, (b) it's doubtful that anyone is doing anything exciting enough with FDWs that far back to need this desperately, and (c) the patch doesn't apply cleanly. Patch originally by Amit Langote, reviewed by Etsuro Fujita and Ashutosh Bapat, who each contributed substantial changes as well. Discussion: https://postgr.es/m/CAKcux6m5cA6rRPTKkqVdJ-R=KKDfe35Q_ZuUqxDSV_4hwga=og@mail.gmail.com http://git.postgresql.org/pg/commitdiff/c52d37c8b3674f1ca9ead297480ade0ac9c54174
  • Merge two copies of tuple-building code in pltcl.c. Make pltcl_trigger_handler() construct modified tuples using pltcl_build_tuple_result(), rather than its own copy of essentially the same logic. This results in slightly different message wording for the error cases, and in one case a different SQLSTATE, but it seems unlikely that any existing applications are depending on any of those details. While at it, fix a typo in commit 26abb50c4: pltcl_build_tuple_result was applying encoding conversion in the wrong direction. That would be a back-patchable bug fix, except the code hasn't shipped yet. Jim Nasby, reviewed by me Discussion: https://postgr.es/m/d2c6425a-d9e0-f034-f774-4a872c234d89@BlueTreble.com http://git.postgresql.org/pg/commitdiff/de5fed0d0c704e3d1a928bf420663319d898cee4
  • Get rid of ParseState.p_value_substitute; use a columnref hook instead. I noticed that p_value_substitute, which is a single-purpose kluge I added in 2002 (commit b0422b215), could be replaced by having domainAddConstraint install a parser hook that looks for the name "value". The parser hook code only dates back to 2009, so it's not surprising that we had to kluge this in 2002, but we can do it more cleanly now. http://git.postgresql.org/pg/commitdiff/7c3abe3c92fd3a14a70bc2f888f936cd6fe28c0f
  • Improve documentation of struct ParseState. I got annoyed about how some fields of ParseState were documented in the struct's block comment and some weren't; not all of the latter are trivial. Fix that. Also reorder a couple of fields that seem to have been placed rather randomly, or maybe with an idea of avoiding padding space; but there are never so many ParseStates in existence at one time that we ought to value pad space over readability. http://git.postgresql.org/pg/commitdiff/3c40594e6eeabb3a8ad22aee93de3a19c41efdc2

Heikki Linnakangas pushed:

Peter Eisentraut pushed:

Bruce Momjian pushed:

Magnus Hagander pushed:

  • Make wal streaming the default mode for pg_basebackup. Since streaming is now supported for all output formats, make this the default as this is what most people want. To get the old behavior, the parameter -X none can be specified to turn it off. This also removes the parameter -x for fetch, now requiring -X fetch to be specified to use that. Reviewed by Vladimir Rusinov, Michael Paquier and Simon Riggs http://git.postgresql.org/pg/commitdiff/9a4d51077c96c10322582211781bb969b51822ff
  • Attempt to handle pending-delete files on Windows. These files are deleted but not yet gone from the filesystem. Operations on them will return ERROR_DELETE_PENDING. With this we start treating that as ENOENT, meaning files does not exist (which is the state it will soon reach). This should be safe in every case except when we try to recreate a file with exactly the same name. This is an operation that PostgreSQL does very seldom, so hopefully that won't happen much -- and even if it does, this treatment should be no worse than treating it as an unhandled error. We've been un able to reproduce the bug reliably, so pushing this to master to get buildfarm coverage and other testing. Once it's proven to be stable, it should be considered for backpatching. Discussion: https://postgr.es/m/20160712083220.1426.58667%40wrigleys.postgresql.org Patch by me and Michael Paquier http://git.postgresql.org/pg/commitdiff/9951741bbeb3ec37ca50e9ce3df1808c931ff6a6

Simon Riggs pushed:

Robert Haas pushed:

Stephen Frost pushed:

  • Protect against NULL-dereference in pg_dump. findTableByOid() is allowed to return NULL and we should therefore be checking for that case. getOwnedSeqs() and dumpSequence() shouldn't ever actually see this happen, but given odd circumstances it might and commit f9e439b1 probably shouldn't have removed that check. Pointed out by Coverity. Initial patch from Michael Paquier. Back-patch to 9.6, where that commit had removed the check. http://git.postgresql.org/pg/commitdiff/d74ecbc8d85eb7a2aa1d5516c5c38d6ab0cbbd82
  • Add basic pg_dumpall/pg_restore TAP tests. For reasons unknown, pg_dumpall and pg_restore managed to escape the basic set of TAP tests that were added for pg_dump in 6bd356c3, so let's get them added now. A few minor adjustments are also made to the dump/restore tests to improve code coverage for pg_restore/pg_dumpall. http://git.postgresql.org/pg/commitdiff/9b815a8ff227e62442259e0fbabc5cf37e433df9

Correctifs en attente

Thomas Munro sent in a patch to add an isolation test for SERIALIZABLE READ ONLY DEFERRABLE.

Ashutosh Bapat sent in another revision of a patch to support partition-wise join between multi-level partitioned tables.

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

Pavan Deolasee sent in two more revisions of a patch to implement WARM.

Ashutosh Bapat sent in a patch to cast nodes more safely.

Thomas Munro sent in another revision of a patch to help measure replay lag.

KaiGai Kohei sent in another revision of a patch to add PassDownLimitBound for ForeignScan/CustomScan.

Mithun Cy sent in two more revisions of a patch to cache hash index meta page.

Amit Kapila sent in another revision of a patch to parallelize queries containing subplans.

Peter Geoghegan sent in another revision of a patch to implement parallel tuplesort.

Tom Lane sent in a patch to expand the existing API to allow the AM to return either a heap or index tuple.

Tomas Vondra sent in another revision of a patch to implement multivariate statistics.

Heikki Linnakangas sent in a patch to replace isMD5() with a more future-proof way to check if password is encrypted and use "plain:" prefix for plaintext passwords stored in pg_authid.

Dilip Kumar sent in another revision of a patch to implement parallel bitmap heap scan.

Craig Ringer sent in two more revisions of a patch to implement logical decoding on standbys.

Peter Eisentraut sent in two revisions of a patch to fix some infelicities in the logical replication protocol and output plugin.

Peter Eisentraut sent in another revision of a patch to add logical replication workers.

Peter Eisentraut and Petr Jelínek traded patches to add PUBLICATION catalogs and DDL.

Ãlvaro Herrera sent in three revisions of a patch to fix and error in ALTER TABLE ... ALTER TYPE.

Haribabu Kommi sent in another revision of a patch to implement a pg_hba_file_settings view and add TAP tests for same.

David Fetter sent in two more revisions of a patch to disable simple UPDATEs and DELETEs which lack any WHERE clause.

Peter Eisentraut sent in another revision of a patch to fix some infelicities between pg_basebackups and slots.

Dilip Kumar sent in another revision of a patch to implement parallel merge join.

Kuntal Ghosh sent in another revision of a patch to implement a WAL consistency check facility.

Craig Ringer sent in another revision of a patch to add PostgresNode methods to wait for node catchup and expand streaming replication tests to cover hot standby feedback and physical replication slots.

Michaël Paquier sent in another revision of a patch to bring the TAP test docs for PostgresNode up to date.

Peter Eisentraut sent in another revision of a patch to generate fmgr prototypes automatically.

Masahiko Sawada sent in a patch to add a GUC for cleanup indexes threshold.

Ashutosh Bapat sent in a patch to add an option to EXPLAIN: SUMMARY option which behaves as: SUMMARY when ON prints planning time, and prints planning time in EXPLAIN EXECUTE output.

Takayuki Tsunakawa sent in two more revisions of a patch to support huge pages on Windows.

Haribabu Kommi sent in another revision of a patch to add macaddr 64 bit (EUI-64) datatype support.

Ashutosh Sharma sent in two more revisions of a patch to add microvacuum support for hash indexes.

Jesper Pedersen sent in another revision of a patch to add support for hash index in pageinspect contrib module.

Tatsuo Ishii sent in a patch to fix a mistaken tag in the documentation for user name maps.

Vitaly Burovoy sent in another revision of a patch to add check for overflow to 'interval' functions.

Andrew Gierth sent in a patch to add hash support for grouping sets.

Jim Nasby sent in another revision of a patch to implement faster methods for getting SPI results in PL/PythonU.

Vladimir Rusinov sent in another revision of a patch to rename things xlog to things wal.

Jonathon Nelson sent in a patch to guc-ify the formerly hard-coded MAX_SEND_SIZE to max_wal_send.

Takeshi Ideriha sent in another revision of a patch to add DECLARE STATEMENT setting up a connection in ECPG.

Amit Langote sent in a patch to fix up some infelicities in declarative partitioning.

Ashutosh Sharma sent in another revision of a patch to add pgstathashindex() to get hash index table statistics.

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

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

Peter Geoghegan sent in a patch to fix a subtle bug in "Simplify tape block format" commit.

Masahiko Sawada sent in another revision of a patch to enable block level parallel vacuum.

Michaël Paquier sent in another revision of a patch to add compression levels to pg_receivexlog.

Amul Sul sent in two more revisions of a patch to add a pg_background contrib module.

Pavel Stěhule sent in a patch to add pragmas to PL/pgsql.

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

Tom Lane sent in a patch to disallow output columns of type UNKNOWN.

Ryan Murphy sent in a patch to show type name and constraints for errors in inherited tables.

Jim Nasby sent in two more revisions of a patch to add more test coverage for PL/Tcl.

Victor Wagner sent in two revisions of a patch to add explicit subtransactions to PL/Tcl.

Haribabu Kommi sent in a patch to fix a bug in the patch to add a pg_hba_settings view.