Les nouveautés des produits dérivés

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/20171224210038.GA28780@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:

Robert Haas pushed:

Fujii Masao pushed:

  • Fix bug in cancellation of non-exclusive backup to avoid assertion failure. Previously an assertion failure occurred when pg_stop_backup() for non-exclusive backup was aborted while it's waiting for WAL files to be archived. This assertion failure happened in do_pg_abort_backup() which was called when a non-exclusive backup was canceled. do_pg_abort_backup() assumes that there is at least one non-exclusive backup running when it's called. But pg_stop_backup() can be canceled even after it marks the end of non-exclusive backup (e.g., during waiting for WAL archiving). This broke the assumption that do_pg_abort_backup() relies on, and which caused an assertion failure. This commit changes do_pg_abort_backup() so that it does nothing when non-exclusive backup has been already marked as completed. That is, the asssumption is also changed, and do_pg_abort_backup() now can handle even the case where it's called when there is no running backup. Backpatch to 9.6 where SQL-callable non-exclusive backup was added. Author: Masahiko Sawada and Michael Paquier Reviewed-By: Robert Haas and Fujii Masao Discussion: https://www.postgresql.org/message-id/CAD21AoD2L1Fu2c==gnVASMyFAAaq3y-AQ2uEVj-zTCGFFjvmDg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/56a95ee5118bf6d46e261b8d352f7dafac10587d

Peter Eisentraut pushed:

  • doc: Fix figures in example description. oversight in 244c8b466a743d1ec18a7d841bf42669699b3b56 Reported-by: Blaz Merela <blaz@merela.org> https://git.postgresql.org/pg/commitdiff/53cba77b53f98255bfbba9d2612d1a5685feec52
  • Move SCRAM-related name definitions to scram-common.h. Mechanism names for SCRAM and channel binding names have been included in scram.h by the libpq frontend code, and this header references a set of routines which are only used by the backend. scram-common.h is on the contrary usable by both the backend and libpq, so getting those names from there seems more reasonable. Author: Michael Paquier <michael.paquier@gmail.com> https://git.postgresql.org/pg/commitdiff/25d532698d74f4adb34f013f1a287a0029e31fb1
  • Add libpq connection parameter "scram_channel_binding". This parameter can be used to enforce the channel binding type used during a SCRAM authentication. This can be useful to check code paths where an invalid channel binding type is used by a client and will be even more useful to allow testing other channel binding types when they are added. The default value is tls-unique, which is what RFC 5802 specifies. Clients can optionally specify an empty value, which has as effect to not use channel binding and use SCRAM-SHA-256 as chosen SASL mechanism. More tests for SCRAM and channel binding are added to the SSL test suite. Author: Author: Michael Paquier <michael.paquier@gmail.com> https://git.postgresql.org/pg/commitdiff/4bbf110d2fb4f74b9385bd5a521f824dfa5f15ec

Andres Freund pushed:

  • Add shared tuplestores. SharedTuplestore allows multiple participants to write into it and then read the tuples back from it in parallel. Each reader receives partial results. For now it always uses disk files, but other buffering policies and other kinds of scans (ie each reader receives complete results) may be useful in future. The upcoming parallel hash join feature will use this facility. Author: Thomas Munro Reviewed-By: Peter Geoghegan, Andres Freund, Robert Haas Discussion: https://postgr.es/m/CAEepm=2W=cOkiZxcg6qiFQP-dHUe09aqTrEMM7yJDrHMhDv_RA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ab9e0e718acb9ded7e4c4b5cedc1d410690ea6ba
  • Add parallel-aware hash joins. Introduce parallel-aware hash joins that appear in EXPLAIN plans as Parallel Hash Join with Parallel Hash. While hash joins could already appear in parallel queries, they were previously always parallel-oblivious and had a partial subplan only on the outer side, meaning that the work of the inner subplan was duplicated in every worker. After this commit, the planner will consider using a partial subplan on the inner side too, using the Parallel Hash node to divide the work over the available CPU cores and combine its results in shared memory. If the join needs to be split into multiple batches in order to respect work_mem, then workers process different batches as much as possible and then work together on the remaining batches. The advantages of a parallel-aware hash join over a parallel-oblivious hash join used in a parallel query are that it: * avoids wasting memory on duplicated hash tables * avoids wasting disk space on duplicated batch files * divides the work of building the hash table over the CPUs One disadvantage is that there is some communication between the participating CPUs which might outweigh the benefits of parallelism in the case of small hash tables. This is avoided by the planner's existing reluctance to supply partial plans for small scans, but it may be necessary to estimate synchronization costs in future if that situation changes. Another is that outer batch 0 must be written to disk if multiple batches are required. A potential future advantage of parallel-aware hash joins is that right and full outer joins could be supported, since there is a single set of matched bits for each hashtable, but that is not yet implemented. A new GUC enable_parallel_hash is defined to control the feature, defaulting to on. Author: Thomas Munro Reviewed-By: Andres Freund, Robert Haas Tested-By: Rafia Sabih, Prabhat Sahu Discussion: https://postgr.es/m/CAEepm=2W=cOkiZxcg6qiFQP-dHUe09aqTrEMM7yJDrHMhDv_RA@mail.gmail.com https://postgr.es/m/CAEepm=37HKyJ4U6XOLi=JgfSHM3o6B-GaeO-6hkOmneTDkH+Uw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/1804284042e659e7d16904e7bbb0ad546394b6a3
  • Fix assert with side effects in the new PHJ code. Instead of asserting the assert just set the value to what it was supposed to test... Per coverity. https://git.postgresql.org/pg/commitdiff/4e2970f8807f1ccfc8029979a70dc80ee102ce48

Tom Lane pushed:

  • Avoid putting build-location-dependent strings into generated files. Various Perl scripts we use to generate files were in the habit of printing things like "generated by $0" into their output files. That looks like a fine idea at first glance, but it results in non-reproducible output, because in VPATH builds $0 won't be just the name of the script file, but a full path for it. We'd prefer that you get identical results whether using VPATH or not, so this is a bad thing. Some of these places also printed their input file name(s), causing an additional hazard of the same type. Hence, establish a policy that thou shalt not print $0, nor input file pathnames, into output files (they're still allowed in error messages, though). Instead just write the script name verbatim. While we are at it, we can make these annotations more useful by giving the script's full relative path name within the PG source tree, eg instead of Gen_fmgrtab.pl let's print src/backend/utils/Gen_fmgrtab.pl. Not all of the changes made here actually affect any files shipped in finished tarballs today, but it seems best to apply the policy everyplace so that nobody copies unsafe code into places where it could matter. Christoph Berg and Tom Lane Discussion: https://postgr.es/m/20171215102223.GB31812@msg.df7cb.de https://git.postgresql.org/pg/commitdiff/c98c35cd084a25c6cf9b08c76de8b89facd75fe7
  • Rearrange execution of PARAM_EXTERN Params for plpgsql's benefit. This patch does three interrelated things: * Create a new expression execution step type EEOP_PARAM_CALLBACK and add the infrastructure needed for add-on modules to generate that. As discussed, the best control mechanism for that seems to be to add another hook function to ParamListInfo, which will be called by ExecInitExpr if it's supplied and a PARAM_EXTERN Param is found. For stand-alone expressions, we add a new entry point to allow the ParamListInfo to be specified directly, since it can't be retrieved from the parent plan node's EState. * Redesign the API for the ParamListInfo paramFetch hook so that the ParamExternData array can be entirely virtual. This also lets us get rid of ParamListInfo.paramMask, instead leaving it to the paramFetch hook to decide which param IDs should be accessible or not. plpgsql_param_fetch was already doing the identical masking check, so having callers do it too seemed redundant. While I was at it, I added a "speculative" flag to paramFetch that the planner can specify as TRUE to avoid unwanted failures. This solves an ancient problem for plpgsql that it couldn't provide values of non-DTYPE_VAR variables to the planner for fear of triggering premature "record not assigned yet" or "field not found" errors during planning. * Rework plpgsql to get rid of the need for "unshared" parameter lists, by dint of turning the single ParamListInfo per estate into a nearly read-only data structure that doesn't instantiate any per-variable data. Instead, the paramFetch hook controls access to per-variable data and can make the right decisions on the fly, replacing the cases that we used to need multiple ParamListInfos for. This might perhaps have been a performance loss on its own, but by using a paramCompile hook we can bypass plpgsql_param_fetch entirely during normal query execution. (It's now only called when, eg, we copy the ParamListInfo into a cursor portal. copyParamList() or SerializeParamList() effectively instantiate the virtual parameter array as a simple physical array without a paramFetch hook, which is what we want in those cases.) This allows reverting most of commit 6c82d8d1f, though I kept the cosmetic code-consolidation aspects of that (eg the assign_simple_var function). Performance testing shows this to be at worst a break-even change, and it can provide wins ranging up to 20% in test cases involving accesses to fields of "record" variables. The fact that values of such variables can now be exposed to the planner might produce wins in some situations, too, but I've not pursued that angle. In passing, remove the "parent" pointer from the arguments to ExecInitExprRec and related functions, instead storing that pointer in a transient field in ExprState. The ParamListInfo pointer for a stand-alone expression is handled the same way; we'd otherwise have had to add yet another recursively-passed-down argument in expression compilation. Discussion: https://postgr.es/m/32589.1513706441@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/6719b238e8f0ea83c39d2b1728c7670cdaa34e06
  • Fix UNION/INTERSECT/EXCEPT over no columns. Since 9.4, we've allowed the syntax "select union select" and variants of that. However, the planner wasn't expecting a no-column set operation and ended up treating the set operation as if it were UNION ALL. Turns out it's trivial to fix in v10 and later; we just need to be careful about not generating a Sort node with no sort keys. However, since a weird corner case like this is never going to be exercised by developers, we'd better have thorough regression tests if we want to consider it supported. Per report from Victor Yegorov. Discussion: https://postgr.es/m/CAGnEbojGJrRSOgJwNGM7JSJZpVAf8xXcVPbVrGdhbVEHZ-BUMw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c4c2885cbb1803f772e58f6db4c8951d8cd672cd

�lvaro Herrera pushed:

Teodor Sigaev pushed:

Correctifs en attente

Peter Eisentraut sent in another revision of a patch to add support for static assertions in C++.

Anastasia Lubennikova sent in a patch to add a mechanism for tracking page changes. This is infrastructure for, among other things, incremental backups.

Nathan Bossart sent in another revision of a patch to fix an issue which caused vacuum to crash, adding an optional NOWAIT parameter to VACUUM and ANALYZE in the process.

Huong Dangminh sent in six more revisions of a patch to fix the slot_store error callback, which had broken logical replication in certain cases where user-defined types existed.

Amit Langote sent in a patch to delay allocation of per-partition objects until tuple is actually routed to a given partition. This helps with non-bulk inserts and doesn't appear to hurt bulk inserts.

Andrey Borodin sent in another revision of a patch to delete pages during GiST VACUUM.

Etsuro Fujita sent in another revision of a patch to add support for tuple routing to foreign partitions.

Marina Polyakova sent in two more revisions of a patch to deal better with pgbench errors and serialization/deadlock retries.

Peter Eisentraut sent in a patch to update the portal-related memory context names and API and remove PortalGetQueryDesc(), which is now useless.

Amit Kapila sent in a patch to make ParallelWorkerNumber available to extensions.

Robert Haas sent in a PoC patch to add a buffer pin wait to pg_isolation_test_session_is_blocked().

Aleksandr Parfenov sent in a patch to make full-text search configuration more flexible.

Magnus Hagander sent in two revisions of a patch to ensure that base backups do not show up as idle.

Nikhil Sontakke sent in two more revisions of a patch to implement logical decoding of two-phase commits.

Craig Ringer sent in two revisions of a patch to add pg_diag_backend(pid) to print memory context info for a running backend.

Beena Emerson and David Rowley traded patches to implement runtime partition pruning.

Feike Steenbergen sent in two revisions of a patch to add hint about replication slots for wraparound.

Tomas Vondra sent in another revision of a patch to implement BRIN multi-range indexes.

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

Peter Eisentraut sent in another revision of a patch to use stdbool.h.

David Fetter sent in a patch to schema-qualify function calls in information_schema.

Peter Eisentraut sent in another revision of a patch to replace GrantObjectType and AclObjectKind with ObjectType.

Tom Lane sent in a patch to let PL/pgsql use the newly added expression evaluation infrastructure.

Amit Langote sent in three more revisions of a patch to prune partitions faster.

Amit Khandekar sent in two more revisions of a patch to enable UPDATEs on partition keys that would have the effect of moving a tuple to another partition.

�lvaro Herrera sent in four revisions of a patch to allow unique indexes on partitioned tables.

Ildar Musin sent in a patch to add some new hashing functions to pgbench.

Christoph Berg sent in another revision of a patch to make builds reproducible by eliminating usages of $0.

Konstantin Knizhnik sent in two more revisions of a patch to implement AS OF queries.

Haribabu Kommi sent in two revisions of a patch to enhance the pg_stat_wal_receiver view to display the connected host.

Ra�l Mar�n Rodr�guez sent in two more revisions of a patch to add pow() to pgbench.

Beena Emerson sent in another revision of a patch to implement runtime partition pruning.

Feike Steenbergen sent in two revisions of a patch to fix the permissions check on pg_stat_get_wal_senders.

Masahiko Sawada sent in a patch to fix a typo in autoprewarm.c.

Micha�l Paquier sent in two more revisions of a patch to implement channel binding tls-server-end-point for SCRAM and refactor channel binding code to fetch cbind_data only when necessary.

Rajkumar Raghuwanshi sent in another revision of a patch to implement partition-wise join with default partitions.

Teodor Sigaev sent in another revision of a patch to add more operators and functions to pgbench.

Thomas Munro sent in a patch to fix a suspicious call of initial_cost_hashjoin().

Dilip Kumar sent in a patch to remove an assert in rewritequery in order to fix a bug which could cause a server crash once after dropping a RULE.

Kyotaro HORIGUCHI sent in another revision of a patch to restrict maximum keep segments by repslots.

Oliver Ford sent in another revision of a patch to add RANGE and GROUPS to window functions.

Antonin Houska sent in another revision of a patch to implement aggregate pushdown.

�lvaro Herrera sent in a patch to protect against hypothetical memory leaks in RelationGetPartitionKey.

�lvaro Herrera sent in a patch to allow indexes on partitioned tables to be unique.

Maksim Milyutin sent in a patch to add a custom signal handler for extensions.

Robert Haas sent in a patch to build towards a point where parallel append will work with UNION ALL.

Tomas Vondra sent in two revisions of a patch to define a memory limit for the reorderbuffer (responsible for building the logically decoded transactions), and add the ability to stream large in-progress transactions (exceeding the memory limit).