Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

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/20170320021208.GB30492@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:

  • Remove dead code in nodeGatherMerge.c. Coverity noted that the last line of gather_merge_getnext() was unreachable, since each arm of the preceding "if" ends in a "return". Drop it as an oversight. In passing, improve some nearby comments. http://git.postgresql.org/pg/commitdiff/5d3f7c57ab9c9e2f074ad29d619056570fc5c51e
  • Fix typo in initdb's SCRAM password processing. Noted by Coverity (a rather impressive catch). Michael Paquier http://git.postgresql.org/pg/commitdiff/835cc1136745e8cf02d3d0231b5b7c7a543df5df
  • Add "break"s to make it clearer what will happen in a nested switch. This could only matter if the guessed_type variable had a value that wasn't a member of the PasswordType enum; but just in case, let's be sure that control falls out to reach the elog(ERROR) at the end of the function. Per gripe from Coverity. http://git.postgresql.org/pg/commitdiff/766f7fd613adbceaf1b40803793e10dc487f8596
  • Remove unnecessary dependency on statement_timeout in prepared_xacts test. Rather than waiting around for statement_timeout to expire, we can just try to take the table's lock in nowait mode. This saves some fraction under 4 seconds when running this test with prepared xacts available, and it guards against timeout-expired-anyway failures on very slow machines when prepared xacts are not available, as seen in a recent failure on axolotl for instance. This approach could fail if autovacuum were to take an exclusive lock on the test table concurrently, but there's no reason for it to do so. Since the main point here is to improve stability in the buildfarm, back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/1c7a66a8e9378aeb092d7ed26890134d17fdd691
  • Add a "void *" passthrough pointer for psqlscan.l's callback functions. The immediate motivation for this is to provide clean infrastructure for the proposed \if...\endif patch for psql; but it seems like a good thing to have even if that patch doesn't get in. Previously the callback functions could only make use of application-global state, which is a pretty severe handicap. For the moment, the pointer is only passed through to the get_variable callback function. I considered also passing it to the write_error callback, but for now let's not. Neither psql nor pgbench has a use for that, and in the case of psql we'd have to invent a separate wrapper function because we would certainly not want to change the signature of psql_error(). Discussion: https://postgr.es/m/10108.1489418309@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/895e36bb3f36fdb7ec8e573be1a20d104fac820b
  • Fix busted markup. Oversight in commit 9ca5c8721. Per buildfarm. http://git.postgresql.org/pg/commitdiff/0c87cd003d9966fcb19d6998ccf90d3276b08e0c
  • Make logging about multixact wraparound protection less chatty. The original messaging design, introduced in commit 068cfadf9, seems too chatty now that some time has elapsed since the bug fix; most installations will be in good shape and don't really need a reminder about this on every postmaster start. Hence, arrange to suppress the "wraparound protections are now enabled" message during startup (specifically, during the TrimMultiXact() call). The message will still appear if protection becomes effective at some later point. Discussion: https://postgr.es/m/17211.1489189214@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/5ed6fff6b729c3cce55d4abc8f695da93aa40a0d
  • Include port number when logging successful binding to a TCP port. Per suggestion from Andres Freund. Discussion: https://postgr.es/m/20170314033842.st7gifec55yigz2h@alap3.anarazel.de http://git.postgresql.org/pg/commitdiff/2b32ac2a59df18246c3b79e96a209bfdb39bb918
  • Rewrite async-connection loop in libpqwalreceiver.c, once again. The original coding in commit 1e8a85009 didn't use PQconnectPoll per spec, and while the rewrite in e434ad39a is closer, it still doesn't guarantee to wait until the socket is read-ready or write-ready (as appropriate) before calling PQconnectPoll. It's not clear whether that omission is causing the continuing failures on buildfarm member bowerbird; but given the lack of other explanations meeting the available facts, let's tighten that up and see what happens. An independent issue in the same loop was that it had a race condition whereby it could clear the process's latch without having serviced an interrupt request, causing failure to respond to a cancel while waiting for connection (the very problem 1e8a85009 was meant to fix). Discussion: https://postgr.es/m/7295.1489596949@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/b5dd50f2c0ad8edcc3145aabe18788d448bf940d
  • Fix WaitEventSetWait() to handle write-ready waits properly on Windows. Windows apparently will not detect socket write-ready events unless a preceding send attempt returned WSAEWOULDBLOCK. In many usage patterns that's satisfied by the caller of WaitEvenSetWait(), but not always. Apply the same solution that we already had in pgwin32_select(), namely to perform a dummy WSASend() call with len=0. This will return WSAEWOULDBLOCK if there's no buffer space (even though it could legitimately do nothing and report success, which makes me a bit nervous about this solution; but since it's been working fine in libpq, let's roll with it). In passing, improve the comments about this in pgwin32_select(), and remove duplicated code there. Back-patch to 9.6 where WaitEventSetWait() was introduced. We might need to back-patch something similar into predecessor code. But given the lack of complaints so far, it's not clear that the case ever gets exercised in the back branches, so I'm not going to expend effort on it right now. This should resolve recurring failures on buildfarm member bowerbird, which has been failing since 1e8a85009 went in. Diagnosis and patch by Petr Jelinek, cosmetic adjustments by me. Discussion: https://postgr.es/m/5b6a6d6d-fb45-0afb-2e95-5600063c3dbd@2ndquadrant.com http://git.postgresql.org/pg/commitdiff/f7819baa618c528f60e266874051563ecfe08207
  • Fix REFRESH MATERIALIZED VIEW to report activity to the stats collector. The non-concurrent code path for REFRESH MATERIALIZED VIEW failed to report its updates to the stats collector. This is bad since it means auto-analyze doesn't know there's any work to be done. Adjust it to report the refresh as a table truncate followed by insertion of an appropriate number of rows. Since a matview could contain more than INT_MAX rows, change the signature of pgstat_count_heap_insert() to accept an int64 rowcount. (The accumulator it's adding into is already int64, but existing callers could not insert more than a small number of rows at once, so the argument had been declared just "int n".) This is surely a bug fix, but changing pgstat_count_heap_insert()'s API seems too risky for the back branches. Given the lack of previous complaints, I'm not sure it's a big enough problem to justify a kluge solution that would avoid that. So, no back-patch, at least for now. Jim Mlodgenski, adjusted a bit by me Discussion: https://postgr.es/m/CAB_5SRchSz7-WmdO5szdiknG8Oj_GGqJytrk1KRd11yhcMs1KQ@mail.gmail.com http://git.postgresql.org/pg/commitdiff/17f8ffa1e331cd0d95a3c4ccec66ea83d8b893c5
  • Avoid use of already-closed relcache entry. Oversight in commit 17f8ffa1e. Per buildfarm member prion. http://git.postgresql.org/pg/commitdiff/e3044f6184beac395e88b4e1230e6c9d449db7f2

Noah Misch pushed:

  • Assume deconstruct_array() outputs are untoasted. In functions that issue a deconstruct_array() call, consistently use plain VARSIZE()/VARDATA() on the array elements. Prior practice was divided between those and VARSIZE_ANY_EXHDR()/VARDATA_ANY(). http://git.postgresql.org/pg/commitdiff/2fd26b23b662dcb0cf649e983a58581cb911fc4b
  • Fix comment about length of text, bytea, etc. When commit 3e23b68dac006e8deb0afa327e855258df8de064 introduced single-byte varlena headers, it rendered this comment incomplete. http://git.postgresql.org/pg/commitdiff/9e0926468a1c41a31c09785787a737311dcd92c1
  • Recommend wrappers of PG_DETOAST_DATUM_PACKED(). When commit 3e23b68dac006e8deb0afa327e855258df8de064 introduced single-byte varlena headers, its fmgr.h changes presented PG_GETARG_TEXT_PP() and PG_GETARG_TEXT_P() as equals. Its postgres.h changes presented PG_DETOAST_DATUM_PACKED() and VARDATA_ANY() as the exceptional case. Now, instead, firmly recommend PG_GETARG_TEXT_PP() over PG_GETARG_TEXT_P(); likewise for other ...PP() macros. This shaves cycles and invites consistency of style. http://git.postgresql.org/pg/commitdiff/9d7726c2ba06b932f791f2d0cc5acf73cc0b4dca
  • Use wrappers of PG_DETOAST_DATUM_PACKED() more. This makes almost all core code follow the policy introduced in the previous commit. Specific decisions: * Text search support functions with char* and length arguments, such as prsstart and lexize, may receive unaligned strings. I doubt maintainers of non-core text search code will notice. * Use plain VARDATA() on values detoasted or synthesized earlier in the same function. Use VARDATA_ANY() on varlenas sourced outside the function, even if they happen to always have four-byte headers. As an exception, retain the universal practice of using VARDATA() on return values of SendFunctionCall(). * Retain PG_GETARG_BYTEA_P() in pageinspect. (Page images are too large for a one-byte header, so this misses no optimization.) Sites that do not call get_page_from_raw() typically need the four-byte alignment. * For now, do not change btree_gist. Its use of four-byte headers in memory is partly entangled with storage of 4-byte headers inside GBT_VARKEY, on disk. * For now, do not change gtrgm_consistent() or gtrgm_distance(). They incorporate the varlena header into a cache, and there are multiple credible implementation strategies to consider. http://git.postgresql.org/pg/commitdiff/3a0d473192b2045cbaf997df8437e7762d34f3ba
  • Fix pg_file_write() error handling. Detect fclose() failures; given "ln -s /dev/full $PGDATA/devfull", "pg_file_write('devfull', 'x', true)" now fails as it should. Don't leak a stream when fwrite() fails. Remove a born-ineffective test that aimed to skip zero-length writes. Back-patch to 9.2 (all supported versions). http://git.postgresql.org/pg/commitdiff/944a026b4ec252667f275768ba4dcd53ae3bb07e

Magnus Hagander pushed:

Peter Eisentraut pushed:

Heikki Linnakangas pushed:

Michael Meskes pushed:

Ãlvaro Herrera pushed:

Robert Haas pushed:

Andres Freund pushed:

Stephen Frost pushed:

  • Add support for EUI-64 MAC addresses as macaddr8. This adds in support for EUI-64 MAC addresses by adding a new data type called 'macaddr8' (using our usual convention of indicating the number of bytes stored). This was largely a copy-and-paste from the macaddr data type, with appropriate adjustments for having 8 bytes instead of 6 and adding support for converting a provided EUI-48 (6 byte format) to the EUI-64 format. Conversion from EUI-48 to EUI-64 inserts FFFE as the 4th and 5th bytes but does not perform the IPv6 modified EUI-64 action of flipping the 7th bit, but we add a function to perform that specific action for the user as it may be commonly done by users who wish to calculate their IPv6 address based on their network prefix and 48-bit MAC address. Author: Haribabu Kommi, with a good bit of rework of macaddr8_in by me. Reviewed by: Vitaly Burovoy, Kuntal Ghosh Discussion: https://postgr.es/m/CAJrrPGcUi8ZH+KkK+=TctNQ+EfkeCEHtMU_yo1mvX8hsk_ghNQ@mail.gmail.com http://git.postgresql.org/pg/commitdiff/c7a9fa399d557c6366222e90b35db31e45d25678
  • Bump catversion for MACADDR8. Pointed out by Robert. http://git.postgresql.org/pg/commitdiff/c5832346625af4193b1242e57e7d13e66a220b38
  • Clean up overly paranoid checks in mac8.c. Andres' compiler points out, quite correctly, that there's no need for some of the overly paranoid checks which were put into mac8.c. Remove those, as they're useless, add some comments and make a few other minor improvements- reduce the size of hexlookup by making it a char array instead of an int array, and pass in the ptr location directly instead of making hex2_to_uchar re-calculate the location based off the offset every time. http://git.postgresql.org/pg/commitdiff/7821f7229c6e149046ee0dd8cab57928c4f86a37
  • Be more careful about signed vs. unsigned char. The buildfarm has reminded me that not all systems consider char to be signed and we need to be explicit. Adjust the various bits of mac8.c for what we intend, mostly using casts to unsigned char as suggested by Tom, and adjust the tests for valid input accordingly. Explicitly make the hexlookup table signed as it's useful to use -1 there to indicate an invalid value. http://git.postgresql.org/pg/commitdiff/cccbddeb1483b85f1853a29dc3b6464647b91eee
  • Improve pg_dump regression tests and code coverage. These improvements bring the lines-of-code coverage of pg_dump.c up to 87.7% (at least using LCOV 1.12, 1.11 seems to differ slightly). Nearly every function is covered, three of the four which aren't are only called when talking to older PG instances. There is more which can, and should, be done here to improve the coverage but it's past time to see what the buildfarm thinks of this. What has been added: * Coverage for many more command-line options * Use command_fails_like instead of command_exit_is * Operator classes, operator families * Text search configuration, templates, parsers, dictionaries * FDWs, servers, foreign tables * Materialized views * Improved Publications / Subscriptions test (though this needs work, see PG10 open items and tests marked with XXX in 002_pg_dump.pl) * Unlogged tables * Partitioned tables * Additional ACL testing for various object types There is room for improvement, specifically: * Various type-based option (alignment, storage, etc) * Composite type collation * Extra Procedural language functions (inline, validator) * Different function options (SRF, Transform, config, security definer, cost, leakproof) * OpClass options (default, storage, order by, recheck) * OpFamily options (order by, recheck) * Aggregate functions (combinefunc, serialfunc, deserialfunc, etc) * Text Search parser 'headline' * Text Search template 'init' * FDW options (handler, validator, options) * Server options (type, version, options) * User mapping options * Default ACLs for sequences, types * Security labels * View circular dependencies (last function that needs coverage) * Toast table autovacuum options * Replica identity options * Independent indexes (plus marking them as clustered on) * Deferrable / initially deferred constraints * Independent domain constraints There's bits of extension pg_dump'ing also not covered, but those will need to go into test_pg_dump (such as having a filter for config tables). Last, but not least, this approximately halves the number of tests run with 'ok()' by removing the ok()-based checking of if all runs are covered by each test. Instead, 002_pg_dump.pl will just exit out in such a case (with a message in the log file). In general, when adding tests, cover all runs unless there is a very good reason not to (such as adding a 'catch-all' case). With these changes, the resulting output and number of "tests" run is actually reduced. http://git.postgresql.org/pg/commitdiff/31a8b77a9244a0883e1968adcff9b6038e575d77
  • pg_dump: Remove "option requires an argument -- j" test. This is really testing getopt more than pg_dump, and what getopt returns exactly appears to differ based on platform, so remove this test. Per buildfarm. http://git.postgresql.org/pg/commitdiff/5abda5a3e9e7f2a472ccbb1b8389d9166d4a9eca
  • Adjust number of tests for pg_dump 001_basic.pl. When removing a test, need to make sure the count of tests is adjusted when it isn't calculated. http://git.postgresql.org/pg/commitdiff/805e8bc7029b6eb19f0ca3a68051cfda5bd07ef3
  • pg_dump: Skip COLLATION-related regression tests. Not every platform supports non-default collations, as pointed out by the buildfarm, so skip collation-related regression tests in pg_dump when they aren't supported. http://git.postgresql.org/pg/commitdiff/de34123834c3fa465b97f65ded171888edbfbccf

Andrew Gierth pushed:

  • Avoid having vacuum set reltuples to 0 on non-empty relations in the presence of page pins, which leads to serious estimation errors in the planner. This particularly affects small heavily-accessed tables, especially where locking (e.g. from FK constraints) forces frequent vacuums for mxid cleanup. Fix by keeping separate track of pages whose live tuples were actually counted vs. pages that were only scanned for freezing purposes. Thus, reltuples can only be set to 0 if all pages of the relation were actually counted. Backpatch to all supported versions. Per bug #14057 from Nicolas Baccelli, analyzed by me. Discussion: https://postgr.es/m/20160331103739.8956.94469@wrigleys.postgresql.org http://git.postgresql.org/pg/commitdiff/1914c5ea7daaaaba4420f65c991256af5d4a9813
  • Repair test for vacuum reltuples fix. Concurrent auto-analyze could be holding a snapshot, affecting the removal of deleted row versions. Remove the deletion to avoid this happening. Per buildfarm. In passing, make the test independent of assumptions of physical row order, just out of sheer paranoia. http://git.postgresql.org/pg/commitdiff/64ae420b275a82534732aafd9d550b9982ca0a5d

Correctifs en attente

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

Amit Langote sent in a patch to fix a bug in get_partition_for_tuple.

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

Masahiko Sawada sent in another revision of a patch to support 2PC for FDWs.

Pavel Stěhule sent in a patch to fix an xpath bug.

Craig Ringer and Petr Jelínek traded patches to add an option to control snapshot export to CREATE_REPLICATION_SLOT and add logical replication support for initial data copy.

Craig Ringer sent in two more revisions of a patch to add a pg_recvlogical wrapper to PostgresNode, follow timeline switches in logical decoding, and enable logical decoding on standby.

Mithun Cy sent in another revision of a patch to implement auto_prewarm.

Thomas Munro sent in a patch to fix a bug in psql's tab completion for UPDATE.

Jeff Janes sent in a patch to silence a perl warning in the check-world make target.

Peter Geoghegan sent in a patch to avoid copying within tuplesort_gettupleslot().

Thomas Munro sent in a patch to update the "tool sets" documentation for modern FreeBSD.

David Rowley sent in two more revisions of a patch to improve performance for joins where outer side is unique.

Anastasia Lubennikova and Ashutosh Bapat traded patches to add IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING statements.

Rafia Sabih sent in another revision of a patch to add parallelism support for queries coming from SQL or other PL functions.

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

Vaishnavi Prabakaran sent in a patch to fix a bug in libpq's single row mode.

Michaël Paquier sent in another revision of a patch to change references of password encryption to hashing.

Amit Langote sent in four more revisions of a patch to avoid creating scan nodes for partitioned tables and not allocate storage for partitioned tables.

David Steele sent in two more revisions of a patch to make pg_stop_backup() archive wait optional.

Andres Freund, Heikki Linnakangas, and Andreas Karlsson traded patches to process expressions faster.

Aleksander Alekseev sent in another revision of a patch to suppress clang 3.9 warnings.

Beena Emerson sent in another revision of a patch to allow increasing the default WAL segment size.

Amit Kapila sent in another revision of a patch to enable parallel queries containing initplans.

Michaël Paquier sent in another revision of a patch to fix backend crash on non-exclusive backup cancel.

Ãlvaro Herrera and David Rowley traded patches to add extended (multivariate) statistics.

Ashutosh Sharma sent in six more revisions of a patch to add microvacuum support for Hash Index.

Surafel Temesgen and Pavel Stěhule traded patches to add CORRESPONDING.

Ashutosh Bapat and Rafia Sabih traded patches to support partition-wise join for join between (declaratively) partitioned tables.

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

Masahiko Sawada sent in another revision of a patch to report the number of skipped frozen pages by manual VACUUM.

Kuntal Ghosh sent in a patch to show extParams and allParams in explain.

Andrew Gierth sent in another revision of a patch to add hash support for GROUPING SETS.

Haribabu Kommi sent in another revision of a patch to mark utility commands benefiting from parallel plan.

Haribabu Kommi sent in another revision of a patch to add pg_stat_wal_write statistics view.

Vaishnavi Prabakaran sent in another revision of a patch to add pipelining batch support for libpq code and tests for same.

Dilip Kumar sent in three revisions of a patch to fix a bug in parallel bitmap scans.

Nikhil Sontakke sent in another revision of a patch to speed up 2PC transactions.

Kuntal Ghosh sent in two more revisions of a patch to add infra to expose all backend processes in pg_stat_get_activity, expose stats for all backends, and add backend_type column in pg_stat_get_activity.

Heikki Linnakangas sent in a patch to allow SCRAM authentication, when pg_hba.conf says 'md5'.

Michaël Paquier sent in another revision of a patch to use base64-based encoding for stored and server keys in SCRAM verifiers, refactor frontend-side random number generation, move routine to build SCRAM verifier into src/common/, and extend PQencryptPassword with a hashing method.

Simon Riggs and David Rowley traded patches to improve the performance of replay of AccessExclusiveLock.

Peter Eisentraut sent in another revision of a patch to refine rules for altering publication owner, change logical replication pg_hba.conf use, add USAGE privilege for publications, add subscription apply worker privilege checks, and add CREATE SUBSCRIPTION privilege on databases.

Rushabh Lathia sent in two more revisions of a patch to add wait events for disk I/O.

Peter Eisentraut sent in another revision of a patch to implement ICU integration.

Thomas Munro sent in two revisions of a patch to change ancient use of Size to the now-common size_t.

Michaël Paquier sent in a patch to add clause PASSWORD (val USING protocol) to CREATE/ALTER ROLE.

Takayuki Tsunakawa sent in a patch to fix an issue where pgwin32_is_service was not checking whether SECURITY_SERVICE_SID was disabled.

Michael Banck sent in three revisions of a patch to reorder tablespaces for non-WAL streaming basebackups.

Amit Khandekar sent in another revision of a patch to implement parallel append.

Michaël Paquier sent in another revision of a patch to rename pg_subtrans to pg_subxact.

Corey Huinker sent in two more revisions of a patch to add \if and friends to psql.

Kyotaro HORIGUCHI sent in another revision of a patch to implement asynchronous execution.

Dave Page sent in another revision of a patch to add monitoring roles.

Emre Hasegeli sent in another revision of a patch to add new BRIN cost estimates.

Yugo Nagata sent in another revision of a patch to implement hash partitioning.

Robert Haas sent in a patch to put make a single point of truth for adjust_relid_set while keeping it static.

Petr Jelínek sent in a patch to fix wait for writable socket on Windows.

Pritam Baral sent in a patch to enable using indexes for element-contained-by-const-range clauses.

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.

Peter Eisentraut sent in a patch to pageinspect to add bt_page_items function with a bytea argument.

Elvis Pranskevichus sent in a patch to add and report the new "in_hot_standby" GUC pseudo-variable.

Vinayak Pokale sent in another revision of a patch to add an ANALYZE command progress checker.

Petr Jelínek sent in another revision of a patch to add an option to modify sync commit per subscription in logical replication.

Pavel Stěhule sent in another revision of a patch to psql to add \gload for loading binary formats.

Mithun Cy sent in another revision of a patch to expand hash buckets efficiently.

Jan Michálek sent in another revision of a patch to add markdown and rst output formats to psql.

Michael Banck sent in two revisions of a patch to create replication slot in pg_basebackup if requested and not yet present.

Peter Eisentraut sent in a patch to remove createlang and droplang.

Peter Geoghegan sent in another revision of a patch to add parallel B-tree index build sorting and add temporary testing tools.