[ndt: Meetup à Toulouse le 2 mars :https://www.meetup.com/fr-FR/PostgreSQL-User-Group-Toulouse/]

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/20170116044539.GA14726@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:

Tom Lane pushed:

  • Expand the regression tests for PL/Tcl. This raises the test coverage (by line count) in pltcl.c from about 70% to 86%. Karl Lehenbauer and Jim Nasby Discussion: https://postgr.es/m/92a1670d-21b6-8f03-9c13-e4fb2207ab7b@BlueTreble.com http://git.postgresql.org/pg/commitdiff/961bed0208912a929a47c5a30190ff76748f3a03
  • Fix error handling in pltcl_returnnext. We can't throw elog(ERROR) out of a Tcl command procedure; we have to catch the error and return TCL_ERROR to the Tcl interpreter. pltcl_returnnext failed to meet this requirement, so that errors detected by pltcl_build_tuple_result or other functions called here led to longjmp'ing out of the Tcl interpreter and thereby leaving it in a bad state. Use the existing subtransaction support to prevent that. Oversight in commit 26abb50c4, found more or less accidentally by the buildfarm thanks to the tests added in 961bed020. Report: https://postgr.es/m/30647.1483989734@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/8c5722948e831c1862a39da2bb5d793a6f2aabab
  • Fix field order in struct catcache. Somebody failed to grasp the point of having the #ifdef CATCACHE_STATS fields at the end of the struct. Put that back the way it should be, and add a comment making it more explicit why it should be that way. http://git.postgresql.org/pg/commitdiff/e2117e4ab0d7fcb76f20cbf2e52541998d639d9e
  • In PL/Tcl tests, don't choke if optional error fields are missing. This fixes a portability issue introduced by commit 961bed020: with a compiler that doesn't support PG_FUNCNAME_MACRO, the "funcname" field of errorCode won't be provided, leading to a failure of the unset command. I added -nocomplain to the unset commands for filename and lineno too, just in case, though I know of no platform that wouldn't populate those fields. (BTW, -nocomplain is new in Tcl 8.4, but fortunately we dropped support for pre-8.4 Tcl some time ago.) Per buildfarm member pademelon. http://git.postgresql.org/pg/commitdiff/5b29e6b688d1e783a098aa24f9e795f8de798a87
  • Fix some more regression test row-order-instability issues. Commit 0563a3a8b just introduced another instance of the same unsafe testing methodology that appeared in 2ac3ef7a0, which I corrected in 257d81572. Robert/Amit, please stop doing that. Also look through the rest of f0e44751d's test cases, and correct some other queries with underdetermined ordering of results from the system catalogs. These haven't failed in the buildfarm yet, but I don't have any confidence in that staying true. Per multiple buildfarm members. http://git.postgresql.org/pg/commitdiff/5ad966ab1c50e829462f2b3e3ffa59e2d95479e6
  • Throw suitable error for COPY TO STDOUT/FROM STDIN in a SQL function. A client copy can't work inside a function because the FE/BE wire protocol doesn't support nesting of a COPY operation within query results. (Maybe it could, but the protocol spec doesn't suggest that clients should support this, and libpq for one certainly doesn't.) In most PLs, this prohibition is enforced by spi.c, but SQL functions don't use SPI. A comparison of _SPI_execute_plan() and init_execution_state() shows that rejecting client COPY is the only discrepancy in what they allow, so there's no other similar bugs. This is an astonishingly ancient oversight, so back-patch to all supported branches. Report: https://postgr.es/m/BY2PR05MB2309EABA3DEFA0143F50F0D593780@BY2PR05MB2309.namprd05.prod.outlook.com http://git.postgresql.org/pg/commitdiff/75abb955dfef064f2fbc5c043f37fff8d0262ffe
  • Change representation of statement lists, and add statement location info. This patch makes several changes that improve the consistency of representation of lists of statements. It's always been the case that the output of parse analysis is a list of Query nodes, whatever the types of the individual statements in the list. This patch brings similar consistency to the outputs of raw parsing and planning steps: * The output of raw parsing is now always a list of RawStmt nodes; the statement-type-dependent nodes are one level down from that. * The output of pg_plan_queries() is now always a list of PlannedStmt nodes, even for utility statements. In the case of a utility statement, "planning" just consists of wrapping a CMD_UTILITY PlannedStmt around the utility node. This list representation is now used in Portal and CachedPlan plan lists, replacing the former convention of intermixing PlannedStmts with bare utility-statement nodes. Now, every list of statements has a consistent head-node type depending on how far along it is in processing. This allows changing many places that formerly used generic "Node *" pointers to use a more specific pointer type, thus reducing the number of IsA() tests and casts needed, as well as improving code clarity. Also, the post-parse-analysis representation of DECLARE CURSOR is changed so that it looks more like EXPLAIN, PREPARE, etc. That is, the contained SELECT remains a child of the DeclareCursorStmt rather than getting flipped around to be the other way. It's now true for both Query and PlannedStmt that utilityStmt is non-null if and only if commandType is CMD_UTILITY. That allows simplifying a lot of places that were testing both fields. (I think some of those were just defensive programming, but in many places, it was actually necessary to avoid confusing DECLARE CURSOR with SELECT.) Because PlannedStmt carries a canSetTag field, we're also able to get rid of some ad-hoc rules about how to reconstruct canSetTag for a bare utility statement; specifically, the assumption that a utility is canSetTag if and only if it's the only one in its list. While I see no near-term need for relaxing that restriction, it's nice to get rid of the ad-hocery. The API of ProcessUtility() is changed so that what it's passed is the wrapper PlannedStmt not just the bare utility statement. This will affect all users of ProcessUtility_hook, but the changes are pretty trivial; see the affected contrib modules for examples of the minimum change needed. (Most compilers should give pointer-type-mismatch warnings for uncorrected code.) There's also a change in the API of ExplainOneQuery_hook, to pass through cursorOptions instead of expecting hook functions to know what to pick. This is needed because of the DECLARE CURSOR changes, but really should have been done in 9.6; it's unlikely that any extant hook functions know about using CURSOR_OPT_PARALLEL_OK. Finally, teach gram.y to save statement boundary locations in RawStmt nodes, and pass those through to Query and PlannedStmt nodes. This allows more intelligent handling of cases where a source query string contains multiple statements. This patch doesn't actually do anything with the information, but a follow-on patch will. (Passing this information through cleanly is the true motivation for these changes; while I think this is all good cleanup, it's unlikely we'd have bothered without this end goal.) catversion bump because addition of location fields to struct Query affects stored rules. This patch is by me, but it owes a good deal to Fabien Coelho who did a lot of preliminary work on the problem, and also reviewed the patch. Discussion: https://postgr.es/m/alpine.DEB.2.20.1612200926310.29821@lancre http://git.postgresql.org/pg/commitdiff/ab1f0c8225714aaa18d2f9ca4f80cd009f145421
  • Teach contrib/pg_stat_statements to handle multi-statement commands better. Make use of the statement boundary info added by commit ab1f0c822 to let pg_stat_statements behave more sanely when multiple SQL queries are jammed into one query string. It now records just the relevant part of the source string, not the whole thing, for each individual query. Even when no multi-statement strings are involved, users may notice small changes in the output: leading and trailing whitespace and semicolons will be stripped from statements, which did not happen before. Also, significantly expand pg_stat_statements' regression test script. Fabien Coelho, reviewed by Craig Ringer and Kyotaro Horiguchi, some mods by me Discussion: https://postgr.es/m/alpine.DEB.2.20.1612200926310.29821@lancre http://git.postgresql.org/pg/commitdiff/83f2061dd037477ec8479ee160367840e203a722
  • Fix matching of boolean index columns to sort ordering. Normally, if we have a WHERE clause like "indexcol = constant", the planner will figure out that that index column can be ignored when determining whether the index has a desired sort ordering. But this failed to work for boolean index columns, because a condition like "boolcol = true" is canonicalized to just "boolcol" which does not give rise to an EquivalenceClass. Add a check to allow the same type of deduction to be made in this case too. Per a complaint from Dima Pavlov. Arguably this is a bug, but given the limited impact and the small number of complaints so far, I won't risk destabilizing plans in stable branches by back-patching. Patch by me, reviewed by Michael Paquier Discussion: https://postgr.es/m/1788.1481605684@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/0777f7a2e8e0a51f0f60cfe164d538bb459bf9f2

Ãlvaro Herrera pushed:

  • BRIN revmap pages are not standard pages, and therefore we ought not to tell XLogRegisterBuffer the opposite, when writing XLog for a brin update that moves the index tuple to a different page. Otherwise, xlog insertion would try to "compress the hole" when producing a full-page image for it; but since we don't update pd_lower/upper, the hole covers the whole page. On WAL replay, the revmap page becomes empty and so the entire portion of the index is useless and needs to be recomputed. This is low-probability: a BRIN update only moves an index tuple to a different page when the summary tuple is larger than the existing one, which doesn't happen with fixed-width datatypes. Also, the revmap page must be first after a checkpoint. Report and patch: Kuntal Ghosh Bug is alleged to have detected by a WAL-consistency-checking tool. Discussion: https://postgr.es/m/CAGz5QCJ=00UQjScSEFbV=0qO5ShTZB9WWz_Fm7+Wd83zPs9Geg@mail.gmail.com I posted a test case demonstrating the problem, but I'm refraining from adding it to the test suite; if the WAL consistency tool makes it in, that will be a better way to catch this from regressing. (We should definitely have someting that causes not-same-page updates, though.) http://git.postgresql.org/pg/commitdiff/7403561c0f6a8c62b79b6ddf0364ae6c01719068
  • Fix ALTER TABLE / SET TYPE for irregular inheritance. If inherited tables don't have exactly the same schema, the USING clause in an ALTER TABLE / SET DATA TYPE misbehaves when applied to the children tables since commit 9550e8348b79. Starting with that commit, the attribute numbers in the USING expression are fixed during parse analysis. This can lead to bogus errors being reported during execution, such as: ERROR: attribute 2 has wrong type DETAIL: Table has type smallint, but query expects integer. Since it wouldn't do to revert to the original coding, we now apply a transformation to map the attribute numbers to the correct ones for each child. Reported by Justin Pryzby Analysis by Tom Lane; patch by me. Discussion: https://postgr.es/m/20170102225618.GA10071@telsasoft.com http://git.postgresql.org/pg/commitdiff/3957b58b8885441c8d03bc1cfc00e47cf8cd7975
  • Fix overflow check in StringInfo; add missing casts. A few thinkos I introduced in fa2fa9955280. Also, amend a similarly broken comment. Report by Daniel Vérité. Authors: Daniel Vérité, Ãlvaro Herrera Discussion: https://postgr.es/m/1706e85e-60d2-494e-8a64-9af1e1b2186e@manitou-mail.org http://git.postgresql.org/pg/commitdiff/42f50cb8fa9848bbbc6776bcea03293a6b28b2d4

Stephen Frost pushed:

  • Fix invalid-parallel-jobs error message. Including the program name twice is not helpful: -> pg_dump -j -1 pg_dump: pg_dump: invalid number of parallel jobs Correct by removing the progname from the exit_horribly() call used when validating the number of parallel jobs. Noticed while testing various pg_dump error cases. Back-patch to 9.3 where parallel pg_dump was added. http://git.postgresql.org/pg/commitdiff/2ef6fe9cbae9fe7789a35cbc5fa1bbf78c163d42
  • pg_dump: Strict names with no matching schema. When using pg_dump --strict-names and a schema pattern which doesn't match any schemas (eg: --schema='nonexistant*'), we were incorrectly throwing an error claiming no tables were found when, really, there were no schemas found: -> pg_dump --strict-names --schema='nonexistant*' pg_dump: no matching tables were found for pattern "nonexistant*" Fix that by changing the error message to say 'schemas' instead, since that is what we are actually complaining about. Noticed while testing pg_dump error cases. Back-patch to 9.6 where --strict-names and this error message were introduced. http://git.postgresql.org/pg/commitdiff/abfd0095c1e1a2e3fad2696516b64871895334ec
  • pg_restore: Don't allow non-positive number of jobs. pg_restore will currently accept invalid values for the number of parallel jobs to run (eg: -1), unlike pg_dump which does check that the value provided is reasonable. Worse, '-1' is actually a valid, independent, parameter (as an alias for --single-transaction), leading to potentially completely unexpected results from a command line such as: -> pg_restore -j -1 Where a user would get neither parallel jobs nor a single-transaction. Add in validity checking of the parallel jobs option, as we already have in pg_dump, before we try to open up the archive. Also move the check that we haven't been asked to run more parallel jobs than possible on Windows to the same place, so we do all the option validity checking before opening the archive. Back-patch all the way, though for 9.2 we're adding the Windows-specific check against MAXIMUM_WAIT_OBJECTS as that check wasn't back-patched originally. Discussion: https://www.postgresql.org/message-id/20170110044815.GC18360%40tamriel.snowman.net http://git.postgresql.org/pg/commitdiff/e72059f3757594c5530ce321acdbe67f0da5da13

Robert Haas pushed:

  • Improve coding in _hash_addovflpage. Instead of relying on the page contents to know whether we have advanced from the primary bucket page to an overflow page, track that explicitly. Amit Kapila, per a complaint by me. http://git.postgresql.org/pg/commitdiff/e898437460f55b49623d1aea435cd92e0011d54d
  • Fix incorrect function name in comment. Amit Langote http://git.postgresql.org/pg/commitdiff/76568d37865c5c21ae154008b2c681e3e32ac880
  • Fix cardinality estimates for parallel joins. For a partial path, the cardinality estimate needs to reflect the number of rows we think each worker will see, rather than the total number of rows; otherwise, costing will go wrong. The previous coding got this completely wrong for parallel joins. Unfortunately, this change may destabilize plans for users of 9.6 who have enabled parallel query, but since 9.6 is still fairly new I'm hoping expectations won't be too settled yet. Also, this is really a brown-paper-bag bug, so leaving it unfixed for the entire lifetime of 9.6 seems unwise. Related reports (whose import I initially failed to recognize) by Tomas Vondra and Tom Lane. Discussion: http://postgr.es/m/CA+TgmoaDxZ5z5Kw_oCQoymNxNoVaTCXzPaODcOuao=CzK8dMZw@mail.gmail.com http://git.postgresql.org/pg/commitdiff/0c2070cefa0e5d097b715c9a3b9b5499470019aa
  • Fix a bug in how we generate partition constraints. Move the code for doing parent attnos to child attnos mapping for Vars in partition constraint expressions to a separate function map_partition_varattnos() and call it from the appropriate places. Doing it in get_qual_from_partbound(), as is now, would produce wrong result in certain multi-level partitioning cases, because it only considers the current pair of parent-child relations. In certain multi-level partitioning cases, attnums for the same key attribute(s) might differ between various levels causing the same attribute to be numbered differently in different instances of the Var corresponding to a given attribute. With this commit, in generate_partition_qual(), we first generate the the whole partition constraint (considering all levels of partitioning) and then do the mapping, so that Vars in the final expression are numbered according the leaf relation (to which it is supposed to apply). Amit Langote, reviewed by me. http://git.postgresql.org/pg/commitdiff/0563a3a8b59150bf3cc8b2b7077f684e0eaf8aff

Bruce Momjian pushed:

Peter Eisentraut pushed:

Correctifs en attente

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

Dilip Kumar sent in four more revisions of a patch to implement parallel bitmap heap scan.

Euler Taveira de Oliveira sent in another revision of a patch to move collation import to the backend.

Anastasia Lubennikova and Erik Rijkers traded patches to implement covering + unique indexes.

Takayuki Tsunakawa sent in another revision of a patch to support huge pages on Windows.

Jon Nelson sent in another revision of a patch to guc-ify the formerly hard-coded MAX_SEND_SIZE to max_wal_send.

Ãlvaro Herrera sent in another revision of a patch to help fix pg_dump and COPY bugs around big lines.

David Fetter sent in three more revisions of a patch to add an extension that disallows simple UPDATEs and DELETEs that lack any WHERE clause.

Ãlvaro Herrera sent in a patch to remove floats from the bootstrap scanner and parser.

Antonin Houska sent in two revisions of a patch to implement grouped base relation, which is infrastructure for distributed aggregation.

Vladimir Rusinov sent in four more revisions of a patch to rename things *xlog* to things *wal*.

Keith Fiske and Amit Langote traded patches to fix up declarative partitioning.

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

Michaël Paquier sent in two more revisions of a patch to add compression-level adjustment to pg_receivelog.

Kyotaro HORIGUCHI sent in another revision of a patch to implement radix trees for character conversion.

Ashutosh Sharma sent in another revision of a patch to add microvacuum support for hash indexes.

Pavel Stěhule sent in another revision of a patch to implement \gstore and friends in psql.

Matheus de Oliveira sent in another revision of a patch to add ALTER DEFAULT PRIVILEGES with GRANT/REVOKE ON SCHEMAS.

Rafia Sabih sent in two revisions of a patch to enable passing query string to workers.

Jesper Pedersen and Ashutosh Sharma traded patches to add hash index support to the pageinspect contrib extension.

Nikita Glukhovs sent in another revision of a patch to add a recursive version of json_populate_record().

David Rowley sent in a patch to make connection_limit ignore bgworkers.

Peter Eisentraut sent in a patch to factor out the many copies of atooid.

Robert Haas sent in another revision of a patch to implement gather merge.

Etsuro Fujita sent in a patch to rearrange some function declarations for correctness.

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

Fabien COELHO and Rafia Sabih traded patches to give pgbench a way to use backslash as a continuation character.

Ãlvaro Herrera and Pavel StÄ›hule traded patches to implement xmltable().

Amit Kapila sent in another revision of a patch to add WAL logging for hash indexes.

Mithun Cy sent in another revision of a patch to cache hash index meta pages.

Etsuro Fujita sent in another revision of a patch to push more FULL JOINs down to FDWs.

Ashutosh Bapat sent in a patch to remove an unused member root in foreign_glob_cxt.

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

Gilles Darold and Karl O. Pinc traded patches to implement pg_current_logfile().

Rafia Sabih sent in another revision of a patch to add parallel index-only scan.

Mithun Cy sent in a patch to fix a typo in a comment in hashsearch.c.

Ants Aasma sent in another revision of a patch to send hot standby feedback on first connect.

Anastasia Lubennikova sent in a patch to implement IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING statements.

Amit Khandekar sent in a patch to prevent useless VACUUMs.

Peter Moser sent in another revision of a patch to implement temporal query processing with range types.

Etsuro Fujita sent in another revision of a patch to fix a bug in the Postgres FDW.

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

Petr Jelínek sent in two more revisions of a patch to implement logical replication.