Les nouveautés des produits dérivés

  • pgmetrics, un outil en ligne de commande pour extraire et afficher les métriques d'un serveur PostgreSQL, publié en open source : https://pgmetrics.io/

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/20180312050436.GA7526@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

Magnus Hagander pushed:

Peter Eisentraut pushed:

Fujii Masao pushed:

Robert Haas pushed:

Tom Lane pushed:

  • Add infrastructure to support server-version-dependent tab completion. Up to now we've not worried about whether psql's tab completion queries would work against prior server versions. But since we support older server versions in describe.c, we really ought to do so here as well. Failing to take care of this not only leads to loss of tab-completion service when using an older server, but risks aborting a user's open transaction when we send an incompatible query to the server. The recent changes in pg_proc.prokind are one reason to take this more seriously now than before, and the proposed patch for completion after SELECT needs some such capability as well. Hence, create some infrastructure to allow selection of one of several versions of a query depending on server version. For cases where we just need to select one of several query strings, introduce VersionedQuery and COMPLETE_WITH_VERSIONED_QUERY(). Likewise extend the SchemaQuery infrastructure to allow versioning of those. I went ahead and fixed the prokind issues, to serve as an illustration of how to use versioned SchemaQuery. To have some illustration of VersionedQuery, change the support for completion of publication and subscription names so that psql will not send sure-to-fail queries to pre-v10 servers. There is much more that should be done to make tab completion more friendly to older servers, but this commit is mainly meant to get the infrastructure in place, so I stopped here. Discussion: https://postgr.es/m/24314.1520190408@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/722408bcd1bde0b007f73b41135382af11b0282d
  • Temporarily instrument postgres_fdw test to look for statistics changes. It seems fairly hard to explain recent buildfarm failures without the theory that something is doing an ANALYZE behind our backs. Probe for this directly to see if it's true. In principle the outputs of these queries should be stable, since the table in question is small enough that ANALYZE's sample will include all rows. But even if that turns out to be wrong, we can put up with some failures for a bit. I don't intend to leave this here indefinitely. Discussion: https://postgr.es/m/25502.1520277552@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/c2c537c56dc30ec3cdc12051f4ea5363aa66d73c
  • In initdb, don't bother trying max_connections = 10. The server won't actually start with that setting anymore, not since we raised the default max_wal_senders to 10. Per discussion, we don't wish to back down on that default, so instead raise the effective floor for max_connections (to 20). It's still possible to configure a smaller setting manually, but initdb won't set it that way. Since that change happened in v10, back-patch to v10. Kyotaro Horiguchi Discussion: https://postgr.es/m/20180209.170823.42008365.horiguchi.kyotaro@lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/6a0b30f0401a76b3a972e366ba30aa7e49ffbf87
  • Fix cross-checking of ReservedBackends/max_wal_senders/MaxConnections. We were independently checking ReservedBackends < MaxConnections and max_wal_senders < MaxConnections, but because walsenders aren't allowed to use superuser-reserved connections, that's really the wrong thing. Correct behavior is to insist on ReservedBackends + max_wal_senders being less than MaxConnections. Fix the code and associated documentation. This has been wrong for a long time, but since the situation probably hardly ever arises in the field (especially pre-v10, when the default for max_wal_senders was zero), no back-patch. Discussion: https://postgr.es/m/28271.1520195491@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/4e0c743c18bf5435a4850510c5c74b3521c3e1e5
  • Revert "Temporarily instrument postgres_fdw test to look for statistics changes.". This reverts commit c2c537c56dc30ec3cdc12051f4ea5363aa66d73c. It's now clear that whatever is going on there, it can't be blamed on unexpected ANALYZE runs, because the statistics are the same just before the failing query as they were at the start of the test. https://git.postgresql.org/pg/commitdiff/04e7ecadf64d18f67ca1d0632d8ab71f120ca5e3
  • Add test scaffolding for exercising optimizer's predicate-proof logic. The predicate-proof code in predtest.c is a bit hard to test as-is: you have to construct a query whose plan changes depending on the success of a test, and in tests that have been around for awhile, it's always possible that the plan shape is now being determined by some other factor. Our existing regression tests aren't doing real well at providing full code coverage of predtest.c, either. So, let's add a small test module that allows directly inspecting the results of predicate_implied_by() and predicate_refuted_by() for arbitrary boolean expressions. I chose the set of tests committed here in order to get reasonably complete code coverage of predtest.c just from running this test module, and to cover some cases called out as being interesting in the existing comments. We might want to add more later. But this set already shows a few cases where perhaps things could be improved. Indeed, this exercise proves that predicate_refuted_by() is buggy for the case of clause_is_check = true, though fortunately we aren't using that case anywhere yet. I'll look into doing something about that in a separate commit. For now, just memorialize the current behavior. Discussion: https://postgr.es/m/5983.1520487191@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/44468f49bbe7e47e5a27a1ccbf13549ff85149f2
  • Fix test_predtest's idea of what weak refutation means. I'd initially supposed that predicate_refuted_by(..., true) ought to say that "A refutes B" means "non-falsity of A implies non-truth of B". But it seems better to define it as "truth of A implies non-truth of B". This is more useful in the current system, slightly easier to prove, and in closer correspondence to the existing code behavior. With this change, test_predtest no longer claims that any existing test cases show false proof reports, though there still are cases where we could prove something and fail to. Discussion: https://postgr.es/m/5983.1520487191@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/a63c3274a68832182f84ca5d2b8cc5a7462bdacb
  • Improve predtest.c's internal docs, and enhance its functionality a bit. Commit b08df9cab left things rather poorly documented as far as the exact semantics of "clause_is_check" mode went. Also, that mode did not really work correctly for predicate_refuted_by; although given the lack of specification as to what it should do, as well as the lack of any actual use-case, that's perhaps not surprising. Rename "clause_is_check" to "weak" proof mode, and provide specifications for what it should do. I defined weak refutation as meaning "truth of A implies non-truth of B", which makes it possible to use the mode in the part of relation_excluded_by_constraints that checks for mutually contradictory WHERE clauses. Fix up several places that did things wrong for that definition. (As far as I can see, these errors would only lead to failure-to-prove, not incorrect claims of proof, making them not serious bugs even aside from the fact that v10 contains no use of this mode. So there seems no need for back-patching.) In addition, teach predicate_refuted_by_recurse that it can use predicate_implied_by_recurse after all when processing a strong NOT-clause, so long as it asks for the correct proof strength. This is an optimization that could have been included in commit b08df9cab, but wasn't. Also, simplify and generalize the logic that checks for whether nullness of the argument of IS [NOT] NULL would force overall nullness of the predicate or clause. (This results in a change in the partition_prune test's output, as it is now able to prune an all-nulls partition that it did not recognize before.) In passing, in PartConstraintImpliedByRelConstraint, remove bogus conversion of the constraint list to explicit-AND form and then right back again; that accomplished nothing except forcing a useless extra level of recursion inside predicate_implied_by. Discussion: https://postgr.es/m/5983.1520487191@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/5748f3a0aa7cf78ac6979010273bd9d50869bb8e
  • In psql, restore old behavior of Query_for_list_of_functions. Historically, tab completion for functions has offered the names of aggregates as well. This is essential in at least one context, namely GRANT/REVOKE, because there is no GRANT ON AGGREGATE syntax. There are other cases where a command that nominally is for functions will allow aggregates as well, though not all do. Commit fd1a421fe changed this query to disallow aggregates, but that doesn't seem to have been thought through very carefully. Change it to allow aggregates (but still ignore procedures). We might at some point tighten this up, but it'd require sorting through all the uses of this query to see which ones should offer aggregate names and which shouldn't. Given the lack of field complaints about the historical laxity here, that's work I'm not eager to do right now. Discussion: https://postgr.es/m/14268.1520283126@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/b6e132ddc8601c99ac5466792f6128ee859f4128
  • Fix improper uses of canonicalize_qual(). One of the things canonicalize_qual() does is to remove constant-NULL subexpressions of top-level AND/OR clauses. It does that on the assumption that what it's given is a top-level WHERE clause, so that NULL can be treated like FALSE. Although this is documented down inside a subroutine of canonicalize_qual(), it wasn't mentioned in the documentation of that function itself, and some callers hadn't gotten that memo. Notably, commit d007a9505 caused get_relation_constraints() to apply canonicalize_qual() to CHECK constraints. That allowed constraint exclusion to misoptimize situations in which a CHECK constraint had a provably-NULL subclause, as seen in the regression test case added here, in which a child table that should be scanned is not. (Although this thinko is ancient, the test case doesn't fail before 9.2, for reasons I've not bothered to track down in detail. There may be related cases that do fail before that.) More recently, commit f0e44751d added an independent bug by applying canonicalize_qual() to index expressions, which is even sillier since those might not even be boolean. If they are, though, I think this could lead to making incorrect index entries for affected index expressions in v10. I haven't attempted to prove that though. To fix, add an "is_check" parameter to canonicalize_qual() to specify whether it should assume WHERE or CHECK semantics, and make it perform NULL-elimination accordingly. Adjust the callers to apply the right semantics, or remove the call entirely in cases where it's not known that the expression has one or the other semantics. I also removed the call in some cases involving partition expressions, where it should be a no-op because such expressions should be canonical already ... and was a no-op, independently of whether it could in principle have done something, because it was being handed the qual in implicit-AND format which isn't what it expects. In HEAD, add an Assert to catch that type of mistake in future. This represents an API break for external callers of canonicalize_qual(). While that's intentional in HEAD to make such callers think about which case applies to them, it seems like something we probably wouldn't be thanked for in released branches. Hence, in released branches, the extra parameter is added to a new function canonicalize_qual_ext(), and canonicalize_qual() is a wrapper that retains its old behavior. Patch by me with suggestions from Dean Rasheed. Back-patch to all supported branches. Discussion: https://postgr.es/m/24475.1520635069@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/4a4e2442a7f7c1434e86dd290cdb3704cfebb24c

Andres Freund pushed:

Álvaro Herrera pushed:

  • Clone extended stats in CREATE TABLE (LIKE INCLUDING ALL). The LIKE INCLUDING ALL clause to CREATE TABLE intuitively indicates cloning of extended statistics on the source table, but it failed to do so. Patch it up so that it does. Also include an INCLUDING STATISTICS option to the LIKE clause, so that the behavior can be requested individually, or excluded individually. While at it, reorder the INCLUDING options, both in code and in docs, in alphabetical order which makes more sense than feature-implementation order that was previously used. Backpatch this to Postgres 10, where extended statistics were introduced, because this is seen as an oversight in a fresh feature which is better to get consistent from the get-go instead of changing only in pg11. In pg11, comments on statistics objects are cloned too. In pg10 they are not, because I (Álvaro) was too coward to change the parse node as required to support it. Also, in pg10 I chose not to renumber the parser symbols for the various INCLUDING options in LIKE, for the same reason. Any corresponding user-visible changes (docs) are backpatched, though. Reported-by: Stephen Froehlich Author: David Rowley Reviewed-by: Álvaro Herrera, Tomas Vondra Discussion: https://postgr.es/m/CY1PR0601MB1927315B45667A1B679D0FD5E5EF0@CY1PR0601MB1927.namprd06.prod.outlook.com https://git.postgresql.org/pg/commitdiff/5564c11815486bdfe87eb46ebc7c070293fa6956
  • Fix bogus Name assignment in CreateStatistics. Apparently, it doesn't work to use a plain cstring as a Name datum: you may end up having random bytes because of failing to zero the bytes after the terminating \0, as indicated by valgrind. I introduced this bug in 5564c1181548, so backpatch this fix to REL_10_STABLE, like that commit. While at it, fix a slightly misleading comment, pointed out by David Rowley. https://git.postgresql.org/pg/commitdiff/1ffb63a2a1767c3dd0c7611bed6383bd37bfbce6
  • Refrain from duplicating data in reorderbuffers. If a walsender exits leaving data in reorderbuffers, the next walsender that tries to decode the same transaction would append its decoded data in the same spill files without truncating it first, which effectively duplicate the data. Avoid that by removing any leftover reorderbuffer spill files when a walsender starts. Backpatch to 9.4; this bug has been there from the very beginning of logical decoding. Author: Craig Ringer, revised by me Reviewed by: Álvaro Herrera, Petr Jelínek, Masahiko Sawada https://git.postgresql.org/pg/commitdiff/8aa75e1384b1c62e302db02e6006fc6a2885afeb
  • Fix typo. Author: Kyotaro HORIGUCHI Discussion: https://postgr.es/m/20180307.163428.209919771.horiguchi.kyotaro@lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/f4a2842ac37f6fffe793ac7a30f8141bf42faae8
  • Add missing debug lines during bootstrap. Noticed while playing with changes that mess with the bootstrap sequence; the operations patched here failed to emit anything, leading the developer to think that the bug was in the previous operation that did emit a message. https://git.postgresql.org/pg/commitdiff/f9d34ce4e798ca768e28aea3703464db3179adcf

Stephen Frost pushed:

Correctifs en attente

Amit Langote sent in a patch to fix an infelicity between partitions and WCO quals on parent tables.

Thomas Munro sent in a patch to fix a typo in src/backend/access/hash/README.

Rajkumar Raghuwanshi sent in another revision of a patch to implement partition-wise JOIN, accounting for default partitions.

Fabien COELHO sent in another revision of a patch to add a --random-seed option to pgbench.

Fabien COELHO sent in another revision of a patch to add a pgbench "progress" test.

Michaël Paquier sent in a patch to fix a particle before SQL.

Claudio Freire sent in another revision of a patch to update the FSM more frequently in VACUUM.

Andrey Borodin sent in a patch to fill in missing pieces of the SLRU checksum patch.

Nikhil Sontakke sent in another revision of a patch to implement logical decoding of two-phase transactions.

Heikki Linnakangas sent in another revision of a patch to speed up crc32 on ARM64.

Satyanarayana Narlapuram sent in another revision of a patch to add client connection redirection support to PostgreSQL.

Michael Banck and Álvaro Herrera traded patches to allow parallel pg_dump to /dev/nul

Daniel Gustafsson sent in another revision of a patch to refactor backend signalling code and use the new infrastructure to support an optional message in backend cancel/terminate.

Peter Eisentraut sent in two more revisions of a patch to allow INOUT parameters in PROCEDUREs.

Jeevan Chalke, Robert Haas, and Ashutosh Bapat traded patches to implement partition-wise aggregation/grouping.

David Steele sent in three more revisions of a patch to make it possible to allow group read access on the filesystem.

Michael Banck sent in two more revisions of a patch to verify checksums during basebackups.

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

Pavan Deolasee sent in three more revisions of a patch to implement MERGE.

David Steele sent in a patch to add regression tests for reinit.c.

Dean Rasheed sent in two more revisions of a patch to improve MCV lists for highly skewed distributions.

David Rowley sent in two more revisions of a patch to make array_agg and string_agg parallelizable.

Etsuro Fujita and Amit Langote traded patches to fix an issue where inserts into a partitioned table could cause a crash.

Michaël Paquier extend lookup routines for FDW and foreign server with NULL handling, refactor routines for subscription and publication lookups, and eliminate user-visible cache lookup errors for objaddr SQL functions.

Aleksandr Parfenov sent in another revision of a patch to add a flexible configuration for full-text search.

Dmitry Dolgov sent in another revision of a patch to implement generic type subscripting.

Craig Ringer sent in another revision of a patch to clean up reorder buffer files when starting logical decoding.

Peter Eisentraut sent in a patch to fix test counting in SSL tests.

Stephen Frost sent in another revision of a patch to rewrite the pg_dump TAP tests.

Artur Zakirov sent in another revision of a patch to implement shared ISpell dictionaries.

Nikita Glukhov sent in two more revisions of a patch to implement JSONPATH.

Nikita Glukhov sent in two more revisions of a patch to implement SQL/JSON functions.

Nikita Glukhov sent in two more revisions of a patch to implement JSON_TABLE.

Stephen Frost sent in two more revisions of a patch to add a default role 'pg_access_server_files'.

Edmund Horner sent in two more revisions of a patch to add tab completion for SELECT in psql.

Sergei Kornilov sent in three more revisions of a patch to skip NOT NULL checks in ALTER TABLE when appropriate CHECKs already enforce them.

Michail Nikolaev sent in two more revisions of a patch to use indexes for OFFSETs and GROUP BY where possible.

David Rowley sent in a patch to disable physical tlists in ALTER TABLE ... ADD COLUMN ... DEFAULT.

Ashutosh Bapat add a missing break statement after transformCallStmt in transformStmt.

Andrey Borodin sent in another revision of a patch to implement GiST VACUUM.

Kyotaro HORIGUCHI sent in a patch to ensure that cursor position is not added when hide_stmt is set.

Álvaro Herrera sent in another revision of a patch to implement foreign key arrays.

Ildar Musin sent in another revision of a patch to add a general purpose hashing function to pgbench.

Nikolay Shaplov sent in another revision of a patch to add an enum relation option type.

Fabrízio de Royes Mello sent in a patch to fix missing spaces in the docs.

Robert Haas and Rajkumar Raghuwanshi traded patches to fix an infelicity between parallel append and a simple UNION ALL.

Kyotaro HORIGUCHI sent in two more revisions of a patch to remove catcache entries that haven't been used for a certain time.

Daniel Vérité sent in another revision of a patch to implement a csv table format for psql.

Álvaro Herrera sent in four more revisions of a patch to implement FOR EACH ROW triggers on partitioned tables.

Peter Geoghegan sent in a patch to report query text in parallel CREATE INDEX workers.

Masahiko Sawada sent in another revision of a patch to improve messaging during logical replication worker.

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

Pavel Stěhule sent in another revision of a patch to implement schema variables.

Peter Eisentraut sent in another revision of a patch to support GNUTLS as an SSL option.

Julian Markwort sent in another revision of a patch to add plans to pg_stat_statements.

Tomas Vondra sent in a patch to use a sort-based approach to speed up XidInMVCCSnapshot.

Amit Khandekar sent in a patch to fix a concurrency bug in UPDATEs of partition key.

Kyotaro HORIGUCHI sent in another revision of a patch to remove dynamic_shared_memroy_type=none.

Dilip Kumar sent in a patch to fix fpwupdate.

John Naylor sent in another revision of a patch to rationalize the handling of bootstrap data.

Peter Eisentraut sent in another revision of a patch to track statistics for streaming spilling.

Masahiko Sawada sent in another revision of a patch to report autovac workitem request failure.

Amul Sul sent in another revision of a patch to restrict concurrent update/delete with UPDATE of partition key.

Alexander Korotkov and Masahiko Sawada traded patches to add a GUC for cleanup indexes threshold.

Takayuki Tsunakawa and Michaël Paquier traded patches to fix a bug where pg_rewind creates corrupt WAL files, and the standby cannot catch up the primary.

Michaël Paquier sent in a patch to fix some missing schema qualifications.

Yura Sokolov sent in another revision of a patch to make a hash table for xip in XidInMVCCSnapshot.

Tom Lane sent in a patch to fix bogus use of canonicalize_qual.

Amit Kapila sent in two more revisions of a patch to implement Predicate Locking in hash index.

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

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

Peter Eisentraut sent in a patch to set libpq sslcompression to off by default.

Peter Geoghegan sent in a patch to fix corruption of backend REINDEX processing state.

Álvaro Herrera sent in another revision of a patch to allow foreign keys and partitioned tables.