Les nouveautés des produits dérivés

  • padnag 1.2.1, un système permettant de synchroniser les rôles PostgreSQL avec les utilisateurs et groupes Active Directory : http://padnag.io/

Offres d'emplois autour de PostgreSQL en décembre

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/20171203193107.GB26891@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:

Simon Riggs pushed:

Tom Lane pushed:

  • Fix creation of resjunk tlist entries for inherited mixed UPDATE/DELETE. rewriteTargetListUD's processing is dependent on the relkind of the query's target table. That was fine at the time it was made to act that way, even for queries on inheritance trees, because all tables in an inheritance tree would necessarily be plain tables. However, the 9.5 feature addition allowing some members of an inheritance tree to be foreign tables broke the assumption that rewriteTargetListUD's output tlist could be applied to all child tables with nothing more than column-number mapping. This led to visible failures if foreign child tables had row-level triggers, and would also break in cases where child tables belonged to FDWs that used methods other than CTID for row identification. To fix, delay running rewriteTargetListUD until after the planner has expanded inheritance, so that it is applied separately to the (already mapped) tlist for each child table. We can conveniently call it from preprocess_targetlist. Refactor associated code slightly to avoid the need to heap_open the target relation multiple times during preprocess_targetlist. (The APIs remain a bit ugly, particularly around the point of which steps scribble on parse->targetList and which don't. But avoiding such scribbling would require a change in FDW callback APIs, which is more pain than it's worth.) Also fix ExecModifyTable to ensure that "tupleid" is reset to NULL when we transition from rows providing a CTID to rows that don't. (That's really an independent bug, but it manifests in much the same cases.) Add a regression test checking one manifestation of this problem, which was that row-level triggers on a foreign child table did not work right. Back-patch to 9.5 where the problem was introduced. Etsuro Fujita, reviewed by Ildus Kurbangaliev and Ashutosh Bapat Discussion: https://postgr.es/m/20170514150525.0346ba72@postgrespro.ru https://git.postgresql.org/pg/commitdiff/9a785ad573176b88a93563209980fbe80cd72163
  • Fix assorted syscache lookup sloppiness in partition-related code. heap_drop_with_catalog and ATExecDetachPartition neglected to check for SearchSysCache failures, as noted in bugs #14927 and #14928 from Pan Bian. Such failures are pretty unlikely, since we should already have some sort of lock on the rel at these points, but it's neither a good idea nor per project style to omit a check for failure. Also, StorePartitionKey contained a syscache lookup that it never did anything with, including never releasing the result. Presumably the reason why we don't see refcount-leak complaints is that the lookup always fails; but in any case it's pretty useless, so remove it. All of these errors were evidently introduced by the relation partitioning feature. Back-patch to v10 where that came in. Amit Langote and Tom Lane Discussion: https://postgr.es/m/20171127090105.1463.3962@wrigleys.postgresql.org Discussion: https://postgr.es/m/20171127091341.1468.72696@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/cb03fa33aeaea4775b9f3437a2240de4ac9cb630
  • Mark some more functions as pg_attribute_noreturn(). Doing this suppresses Coverity warnings and might allow improved code in some cases. The prospects of that are not so bright as to warrant back-patching, though. Michael Paquier, per Coverity https://git.postgresql.org/pg/commitdiff/0772c152b9bd02baeca6920c3371fce95e8f13dc
  • Clarify old comment about qual_is_pushdown_safe's handling of subplans. This comment glossed over the difference between initplans and subplans, but they are indeed different for our purposes here. https://git.postgresql.org/pg/commitdiff/801386af62eac84c13feec5a643c120cf0ce33bd
  • Fix neqjoinsel's behavior for semi/anti join cases. Previously, this function estimated the selectivity as 1 minus eqjoinsel() for the negator equality operator, regardless of join type (I think there was an expectation that eqjoinsel would handle the join type). But actually this is completely wrong for semijoin cases: the fraction of the LHS that has a non-matching row is not one minus the fraction of the LHS that has a matching row. In reality a semijoin with <> will nearly always succeed: it can only fail when the RHS is empty, or it contains a single distinct value that is equal to the particular LHS value, or the LHS value is null. The only one of those things we should have much confidence in estimating is the fraction of LHS values that are null, so let's just take the selectivity as 1 minus outer nullfrac. Per coding convention, antijoin should be estimated the same as semijoin. Arguably this is a bug fix, but in view of the lack of field complaints and the risk of destabilizing plans, no back-patch. Thomas Munro, reviewed by Ashutosh Bapat Discussion: https://postgr.es/m/CAEepm=270ze2hVxWkJw-5eKzc3AB4C9KpH3L2kih75R5pdSogg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/7ca25b7de6aefa5537e0dbe56541bc41c0464f97
  • Fix uninitialized-variable compiler warning induced by commit e4128ee76. I'm a little bit astonished that anyone's compiler would have failed to complain about this. The compiler surely does not know that is_procedure means the function return value will be ignored. https://git.postgresql.org/pg/commitdiff/a852cfe96752b25c2deaa2653cffd60c0ec82ead

Robert Haas pushed:

Peter Eisentraut pushed:

  • PL/Python: Fix potential NULL pointer dereference. After d0aa965c0a0ac2ff7906ae1b1dad50a7952efa56, one error path in PLy_spi_execute_fetch_result() could result in the variable "result" being dereferenced after being set to NULL. To fix that, just clear the resources right there and return early. Also add another SPI_freetuptable() call so that that is cleared in all error paths. discovered by John Naylor <jcnaylor@gmail.com> via scan-build https://git.postgresql.org/pg/commitdiff/e42e2f38907681c48c43f49c5ec9f9f41a9aa9a5
  • Revert "PL/Python: Fix potential NULL pointer dereference". This reverts commit e42e2f38907681c48c43f49c5ec9f9f41a9aa9a5. It's not safe to return in the middle of a PG_TRY block, so this will have to be done differently. https://git.postgresql.org/pg/commitdiff/62546b4357f2aec46bb896fdbddfc0904a2d7920
  • Add compiler hints to PLy_elog(). Decorate PLy_elog() in a similar way as elog(), to give compilers and static analyzers hints in which cases it does not return. Reviewed-by: John Naylor <jcnaylor@gmail.com> https://git.postgresql.org/pg/commitdiff/cdddd5d40b8a8b37db18adda3912e029756d1e36
  • PL/Python: Fix remaining scan-build warnings. Apparently, scan-build thinks that proc->is_setof can change during PLy_exec_function(). To make it clearer, save the value in a local variable. Also add an assertion to clear another warning. Reviewed-by: John Naylor <jcnaylor@gmail.com> https://git.postgresql.org/pg/commitdiff/c7f5c58e1c6bb250ff7c24970a05e033201be409
  • SQL procedures. This adds a new object type "procedure" that is similar to a function but does not have a return type and is invoked by the new CALL statement instead of SELECT or similar. This implementation is aligned with the SQL standard and compatible with or similar to other SQL implementations. This commit adds new commands CALL, CREATE/ALTER/DROP PROCEDURE, as well as ALTER/DROP ROUTINE that can refer to either a function or a procedure (or an aggregate function, as an extension to SQL). There is also support for procedures in various utility commands such as COMMENT and GRANT, as well as support in pg_dump and psql. Support for defining procedures is available in all the languages supplied by the core distribution. While this commit is mainly syntax sugar around existing functionality, future features will rely on having procedures as a separate object type. Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com> https://git.postgresql.org/pg/commitdiff/e4128ee767df3c8c715eb08f8977647ae49dfb59
  • pg_basebackup: Fix progress messages when writing to a file. The progress messages print out \r to keep overwriting the same line on the screen. But this does not yield useful results when writing the output to a file. So in that case, print out \n instead. Author: Martín Marqués <martin@2ndquadrant.com> Reviewed-by: Arthur Zakirov <a.zakirov@postgrespro.ru> https://git.postgresql.org/pg/commitdiff/143b54d21d37804707c27edebdbd4410891da133
  • Check channel binding flag at end of SCRAM exchange. We need to check whether the channel-binding flag encoded in the client-final-message is the same one sent in the client-first-message. Reviewed-by: Michael Paquier <michael.paquier@gmail.com> https://git.postgresql.org/pg/commitdiff/86ab28fbd19a6a0742a7f66e69a595b61eb13d00

Álvaro Herrera pushed:

Andres Freund pushed:

Noah Misch pushed:

Correctifs en attente

Andres Freund sent in a patch to implement expression based aggregate transition / combine function invocation, part of the JIT infrastructure.

Oliver Ford, Erik Rijkers, and David Fetter traded patches to add RANGE with values and exclusions clauses to the window functions.

Andreas Karlsson sent in another revision of a patch to add support for GnuTLS.

Alexander Korotkov sent in two revisions of a patch to implement atomic pgrename on Windows.

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

Jing Wang sent in another revision of a patch to implement COMMENT ON DATABASE CURRENT_DATABASE.

Daniel Gustafsson sent in two more revisions of a patch to refactor identifier checks to consistently use strcmp.

Masahiko Sawada sent in three more revisions of a patch to move relation extension locks out of the heavyweight lock manager.

Anthony Bykov sent in another revision of a patch to implement transforms for JSON for PL/Perl.

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

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

Yura Sokolov sent in a patch to create a header for customized qsort.

Feike Steenbergen sent in two revisions of a patch to skip index cleanup if autovacuum did not do any work.

Doug Rady sent in another revision of a patch to pgbench to add an option to build using ppoll() for larger connection counts.

Victor Drobny sent in two more revisions of a patch to add a new function for tsquery creation.

Amul Sul sent in another revision of a patch to implement parallel append.

Sergei Kornilov sent in two revisions of a patch to use an index or check if present in ALTER TABLE SET NOT NULL.

Amit Kapila sent in two revisions of a patch to fix the instrumentation for parallel workers.

Thomas Munro sent in a patch to ensure that TupleDescCopy clears atthasdef, attnotnull, and attidentity.

Andres Freund sent in a patch to implement expression-based grouping equality, part of the infrastructure for JIT.

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

Alexander Korotkov sent in another revision of a patch to fix contrib/cube's KNN.

Petr Jelínek sent in a patch to fix walsender timeouts when decoding large transactions.

Shubham Barai sent in another revision of a patch to implement predicate locking in GiST indexes.

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.

Emre Hasegeli sent in another revision of a patch to improve the geometric types.

Amit Khandekar sent in another revision of a patch to see to it that UPDATEs of a partition key which would cause a tuple to move to another partition do so.

Stas Kelvich and Petr Jelínek traded patches to make XactLockTableWait work for transactions that are not yet self locked.

Beena Emerson sent in two more revisions of a patch to implement runtime partition pruning.

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

Andrey Borodin sent in a patch to update the patch for covering + unique indexes to account for the new amcheck.

Pavel Stěhule sent in another revision of a patch to add extra checks to PL/pgsql.

David Rowley sent in another revision of a patch to see to it that LEFT JOIN is removed in appropriate cases where DISTINCT also appears.

Alexey Chernyshov sent in a patch to make ASCII NUL a valid character.

Robert Haas sent in another revision of a patch to remove the IndexTupleDSize macro.

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

Aleksey Kondratov sent in another revision of a patch to allow COPY to handle certain types of errors more gracefully.

Michaël Paquier and Amit Langote traded patches to fix a use of uninitialized variables in ExecFindPartition() for a parent partition without leaves.

Michaël Paquier sent in two more revisions of a patch to allow SSL connection from a v11 client to v10 server with SCRAM channel binding.

Michaël Paquier sent in another revision of a patch to move SCRAM-related name definitions to scram-common.h, add a "scramchannelbinding" connection parameter, and implement channel binding tls-server-end-point for SCRAM.

Fabien COELHO sent in another revision of a patch to add more functions and operators to pgbench.

Robert Haas sent in another revision of a patch to fix the superuser checks in the PostgreSQL FDW.

Rajkumar Raghuwanshi sent in another revision of a patch to implement partition-wise join for join between (declaratively) partitioned tables.

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

Nathan Bossart sent in two more revisions of a patch to add logging for VACUUM and ANALYZE.

Thomas Munro and Justin Pryzby traded patches to clarify the documentation on huge pages.

Raúl Marín Rodríguez sent in another revision of a patch to add pow() support for pgbench.

Chen Huajun sent in another revision of a patch to make pg_rewind to not copy useless WAL files.

Ashutosh Bapat sent in another revision of a patch to implement an advanced partition matching algorithm for partition-wise join.