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/20170122232424.GA6120@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

Fujii Masao pushed:

  • Fix typos in comments. Masahiko Sawada http://git.postgresql.org/pg/commitdiff/8fa6019b405f9d39539a77c6f5f11fe640ddf955
  • Fix an assertion failure related to an exclusive backup. Previously multiple sessions could execute pg_start_backup() and pg_stop_backup() to start and stop an exclusive backup at the same time. This could trigger the assertion failure of "FailedAssertion("!(XLogCtl->Insert.exclusiveBackup)". This happend because, even while pg_start_backup() was starting an exclusive backup, other session could run pg_stop_backup() concurrently and mark the backup as not-in-progress unconditionally. This patch introduces ExclusiveBackupState indicating the state of an exclusive backup. This state is used to ensure that there is only one session running pg_start_backup() or pg_stop_backup() at the same time, to avoid the assertion failure. Back-patch to all supported versions. Author: Michael Paquier Reviewed-By: Kyotaro Horiguchi and me Reported-By: Andreas Seltenreich Discussion: <87mvktojme.fsf@credativ.de> http://git.postgresql.org/pg/commitdiff/974ece58bbb3c0ef185a9d44b1cedae51cd56b04
  • Add description of temporary column into pg_replication_slots doc. Ayumi Ishii http://git.postgresql.org/pg/commitdiff/954737095061e5b5f1d87fb8cc43f7f8afff64c6

Magnus Hagander pushed:

Tom Lane pushed:

  • Fix NULL pointer dereference in tuplesort.c. Oversight in commit e94568ecc. This could cause a crash when an external datum tuplesort of a pass-by-value type required multiple passes. Per report from Mithun Cy. Peter Geoghegan Discussion: https://postgr.es/m/CAD__OujuhfWFULGFSt1fyHqUb8N-XafjJhudwt88V0Qs2o84qg@mail.gmail.com http://git.postgresql.org/pg/commitdiff/4e46c97fde42fa8ca57d29b9b47f2ebd11ab8105
  • Fix check_srf_call_placement() to handle VALUES cases correctly. INSERT ... VALUES with a single VALUES row is implemented quite differently from the general VALUES case. A user-visible implication of that is that we accept SRFs in the single-row case, but not in the multi-row case. That's a historical artifact no doubt, but in view of the lack of field complaints, I'm not excited about fixing it right now. However, check_srf_call_placement() needs to know about this, first because it should throw an error in the unsupported case, and second because it should set p_hasTargetSRFs in the single-row case (because we treat that like a SELECT tlist). That's an oversight in commit a4c35ea1c. To fix, split EXPR_KIND_VALUES into two values. So far as I can see, this is the only place where we need to distinguish the two cases at present; but there might be more later. Patch by me, per report from Andres Freund. Discussion: https://postgr.es/m/20170116081548.zg63zltblwimpfgp@alap3.anarazel.de http://git.postgresql.org/pg/commitdiff/d43a619c60405ecda275ca9e3ac9ead242e20ecb
  • Improve RLS planning by marking individual quals with security levels. In an RLS query, we must ensure that security filter quals are evaluated before ordinary query quals, in case the latter contain "leaky" functions that could expose the contents of sensitive rows. The original implementation of RLS planning ensured this by pushing the scan of a secured table into a sub-query that it marked as a security-barrier view. Unfortunately this results in very inefficient plans in many cases, because the sub-query cannot be flattened and gets planned independently of the rest of the query. To fix, drop the use of sub-queries to enforce RLS qual order, and instead mark each qual (RestrictInfo) with a security_level field establishing its priority for evaluation. Quals must be evaluated in security_level order, except that "leakproof" quals can be allowed to go ahead of quals of lower security_level, if it's helpful to do so. This has to be enforced within the ordering of any one list of quals to be evaluated at a table scan node, and we also have to ensure that quals are not chosen for early evaluation (i.e., use as an index qual or TID scan qual) if they're not allowed to go ahead of other quals at the scan node. This is sufficient to fix the problem for RLS quals, since we only support RLS policies on simple tables and thus RLS quals will always exist at the table scan level only. Eventually these qual ordering rules should be enforced for join quals as well, which would permit improving planning for explicit security-barrier views; but that's a task for another patch. Note that FDWs would need to be aware of these rules --- and not, for example, send an insecure qual for remote execution --- but since we do not yet allow RLS policies on foreign tables, the case doesn't arise. This will need to be addressed before we can allow such policies. Patch by me, reviewed by Stephen Frost and Dean Rasheed. Discussion: https://postgr.es/m/8185.1477432701@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/215b43cdc8d6b4a1700886a39df1ee735cb0274d
  • Avoid conflicts with collation aliases generated by stripping. This resulted in failures depending on the order of "locale -a" output. The original coding in initdb sorted the results, but that should be unnecessary as long as "locale -a" doesn't print duplicate names. The original entries will then all be non-dups, and while we might generate duplicate aliases by stripping, they should be for different encodings and thus not conflict. Even if the latter assumption fails somehow, it won't be fatal because we're using if_not_exists mode for the aliases. Discussion: https://postgr.es/m/26116.1484751196%40sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/0333a7340054c3356940759b1ab2391eed572171
  • Disable transforms that replaced AT TIME ZONE with RelabelType. These resulted in wrong answers if the relabeled argument could be matched to an index column, as shown in bug #14504 from Evgeniy Kozlov. We might be able to resurrect these optimizations by adjusting the planner's treatment of RelabelType, or by adjusting btree's rules for selecting comparison functions, but either solution will take careful analysis and does not sound like a fit candidate for backpatching. I left the catalog infrastructure in place and just reduced the transform functions to always-return-NULL. This would be necessary anyway in the back branches, and it doesn't seem important to be more invasive in HEAD. Bug introduced by commit b8a18ad48. Back-patch to 9.5 where that came in. Report: https://postgr.es/m/20170118144828.1432.52823@wrigleys.postgresql.org Discussion: https://postgr.es/m/18771.1484759439@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/c22ecc6562aac895f0f0529707d7bdb460fd2a49
  • Reset the proper GUC in create_index test. Thinko in commit a4523c5aa. It doesn't really affect anything at present, but it would be a problem if any tests added later in this file ought to get index-only-scan plans. Back-patch, like the previous commit, just to avoid surprises in case we add such a test and then back-patch it. Nikita Glukhov Discussion: https://postgr.es/m/8b70135d-ad38-bdd8-ac92-71e2b3c273cf@postgrespro.ru http://git.postgresql.org/pg/commitdiff/1586317c3f57e619e0cde674c6da406f9d73aaff
  • Doc: improve documentation of new SRF-in-tlist behavior. Correct a misstatement about how things used to work: we did allow nested SRFs before, as long as no function had more than one set-returning input. Also, attempt to document the fact that the new implementation changes the behavior for SRFs within conditional constructs (eg CASE): the conditional construct no longer gates whether the SRF is run, and thus cannot affect the number of rows emitted. We might want to change this behavior, but first it behooves us to see if we can explain it. Minor other wordsmithing on what I wrote yesterday, too. Discussion: https://postgr.es/m/20170118214702.54b2mdbxce5piwv5@alap3.anarazel.de http://git.postgresql.org/pg/commitdiff/f13a1277aa2df301583c6db9c2989d2e9d7f6483
  • Fix Assert failure induced by commit 215b43cdc. I'd somehow talked myself into believing that set_append_rel_size doesn't need to worry about getting back an AND clause when it applies eval_const_expressions to the result of adjust_appendrel_attrs (that is, transposing the appendrel parent's restriction clauses for one child). But that is nonsense, and Andreas Seltenreich's fuzz tester soon turned up a counterexample. Put back the make_ands_implicit step that was there before, and add a regression test covering the case. Report: https://postgr.es/m/878tq6vja6.fsf@ansel.ydns.eu http://git.postgresql.org/pg/commitdiff/d479e37e3d20efad8b178e0f1e468c086a7519a8
  • Avoid core dump for empty prepared statement in an aborted transaction. Brown-paper-bag bug in commit ab1f0c822: the old code here coped with null CachedPlanSource.raw_parse_tree, the new code not so much. Per report from Dave Cramer. No regression test, because our core testing infrastructure doesn't provide any easy way to exercise this path. Fortunately, the JDBC crew test it regularly. Discussion: https://postgr.es/m/CADK3HH+Ug3xCysKqw_dZOnaNnytZ1Rh5yP05hjO-e4NoyRxVvA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/ba61a04bc7fefeee03416d9911eb825c4897c223
  • Allow backslash line continuations in pgbench's meta commands. A pgbench meta command can now be continued onto additional line(s) of a script file by writing backslash-return. The continuation marker is equivalent to white space in that it separates tokens. Eventually it'd be nice to have the same thing in psql, but that will be a much larger project. Fabien Coelho, reviewed by Rafia Sabih Discussion: https://postgr.es/m/alpine.DEB.2.20.1610031049310.19411@lancre http://git.postgresql.org/pg/commitdiff/cdc2a70470bdbe3663dc464deb753d6d931bba61
  • Try to fix non-MSVC Windows builds in the wake of logical replication. pgoutput evidently needs to be built without -DBUILDING_DLL. (It seems like a pretty bad idea that these makefiles need to know exactly where all the shlibs are in the tree, or maybe what's bad is putting them under src/backend/. But right now is not the time to redesign that.) Also, remove "override CPPFLAGS" in pgoutput's Makefile. I don't think that that actually has any bad consequences, but it's certainly useless in a directory that has no .h files, and it might be contributing to the failure somehow. Per buildfarm. http://git.postgresql.org/pg/commitdiff/0502e854640bd024f349c0df46b7dd6812b8c05c
  • Fix cross-shlib linking in temporary installs on HPUX 10. Turns out this has been broken for years and we'd not noticed. The one case that was getting exercised in the buildfarm, or probably anywhere else, was postgres_fdw.sl's reference to libpq.sl; and it turns out that that was always going to libpq.sl in the actual installation directory not the temporary install. We'd not noticed because the buildfarm script does "make install" before it tests contrib. However, the recent addition of a logical-replication test to the core regression scripts resulted in trying to use libpqwalreceiver.sl before "make install" happens, and that failed for lack of finding libpq.sl, as shown by failures on buildfarm members gaur and pademelon. There are two changes needed to fix it: the magic environment variable to specify shlib search path at runtime is SHLIB_PATH not LD_LIBRARY_PATH, and the shlib link command needs to specify the +s switch else the library will not honor SHLIB_PATH. I'm not quite sure why buildfarm members anole and gharial (HPUX 11) didn't show the same failure. Consulting man pages on the web says that HPUX 11 honors both LD_LIBRARY_PATH and SHLIB_PATH, which would explain half of it, and the rather confusing wording I've been able to find suggests that +s might effectively be the default in HPUX 11. But it seems at least as likely that there's just a libpq.so installed in /usr/lib on that machine; as long as it's not too ancient, that would satisfy the test. In any case I do not think this patch will break HPUX 11. At the moment I don't see a need to back-patch this, since it only matters for testing purposes, not to mention that HPUX 10 is probably dead in the real world anyway. http://git.postgresql.org/pg/commitdiff/d2ab1176160e30543da1e48f7e0d17564852b693
  • Remove no-longer-needed loop in ExecGather(). Coverity complained quite properly that commit ea15e1867 had introduced unreachable code into ExecGather(); to wit, it was no longer possible to iterate the final for-loop more or less than once. So remove the for(). In passing, clean up a couple of comments, and make better use of a local variable. http://git.postgresql.org/pg/commitdiff/0a8b9d3b2c57028f7100078cd711370f396d5a81
  • Relocate static function declarations to be after typedefs in jsonfuncs.c. Project style is to put things in this order, for the good and sufficient reason that you often need the typedefs in the function declarations. There already was one function declaration that needed a typedef, which was randomly placed away from all the other static function declarations in consequence. And the submitted patch for better json_populate_record functionality jumped through even more hoops in order to preserve this bad idea. This patch only moves lines from point A to point B, no other changes. http://git.postgresql.org/pg/commitdiff/90992e0e2f9fc4aa0f6402f0327604e5fef4630c

Peter Eisentraut pushed:

Álvaro Herrera pushed:

Robert Haas pushed:

Andres Freund pushed:

  • Move targetlist SRF handling from expression evaluation to new executor node. Evaluation of set returning functions (SRFs_ in the targetlist (like SELECT generate_series(1,5)) so far was done in the expression evaluation (i.e. ExecEvalExpr()) and projection (i.e. ExecProject/ExecTargetList) code. This meant that most executor nodes performing projection, and most expression evaluation functions, had to deal with the possibility that an evaluated expression could return a set of return values. That's bad because it leads to repeated code in a lot of places. It also, and that's my (Andres's) motivation, made it a lot harder to implement a more efficient way of doing expression evaluation. To fix this, introduce a new executor node (ProjectSet) that can evaluate targetlists containing one or more SRFs. To avoid the complexity of the old way of handling nested expressions returning sets (e.g. having to pass up ExprDoneCond, and dealing with arguments to functions returning sets etc.), those SRFs can only be at the top level of the node's targetlist. The planner makes sure (via split_pathtarget_at_srfs()) that SRF evaluation is only necessary in ProjectSet nodes and that SRFs are only present at the top level of the node's targetlist. If there are nested SRFs the planner creates multiple stacked ProjectSet nodes. The ProjectSet nodes always get input from an underlying node. We also discussed and prototyped evaluating targetlist SRFs using ROWS FROM(), but that turned out to be more complicated than we'd hoped. While moving SRF evaluation to ProjectSet would allow to retain the old "least common multiple" behavior when multiple SRFs are present in one targetlist (i.e. continue returning rows until all SRFs are at the end of their input at the same time), we decided to instead only return rows till all SRFs are exhausted, returning NULL for already exhausted ones. We deemed the previous behavior to be too confusing, unexpected and actually not particularly useful. As a side effect, the previously prohibited case of multiple set returning arguments to a function, is now allowed. Not because it's particularly desirable, but because it ends up working and there seems to be no argument for adding code to prohibit it. Currently the behavior for COALESCE and CASE containing SRFs has changed, returning multiple rows from the expression, even when the SRF containing "arm" of the expression is not evaluated. That's because the SRFs are evaluated in a separate ProjectSet node. As that's quite confusing, we're likely to instead prohibit SRFs in those places. But that's still being discussed, and the code would reside in places not touched here, so that's a task for later. There's a lot of, now superfluous, code dealing with set return expressions around. But as the changes to get rid of those are verbose largely boring, it seems better for readability to keep the cleanup as a separate commit. Author: Tom Lane and Andres Freund Discussion: https://postgr.es/m/20160822214023.aaxz5l4igypowyri@alap3.anarazel.de http://git.postgresql.org/pg/commitdiff/69f4b9c85f168ae006929eec44fc44d569e846b9
  • Adapt python regression tests to 69f4b9c85f16. Hopefully this'll unbreak the buildfarm. http://git.postgresql.org/pg/commitdiff/8b07aee8c5d803801c00103f0d61e32356aaf29c
  • Remove obsoleted code relating to targetlist SRF evaluation. Since 69f4b9c plain expression evaluation (and thus normal projection) can't return sets of tuples anymore. Thus remove code dealing with that possibility. This will require adjustments in external code using ExecEvalExpr()/ExecProject() - that should neither be hard nor very common. Author: Andres Freund and Tom Lane Discussion: https://postgr.es/m/20160822214023.aaxz5l4igypowyri@alap3.anarazel.de http://git.postgresql.org/pg/commitdiff/ea15e18677fc2eff3135023e27f69ed8821554ed
  • Fix platform dependant regression output triggered by 69f4b9c85f16. Due to the changed costing in that commit hash-aggregates started to be used, which results in big-endian vs. little-endian output differences. Disable hash-aggs for those tests. Author: Andres Freund, with input from Tom Lane Discussion: https://postgr.es/m/22891.1484791792@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/182200531a92204b0547d337f50b665d222af168

Stephen Frost pushed:

  • Dump sequence data based on the TableDataInfo flag. When considering a sequence's Data entry in dumpSequenceData, we were actually looking at the sequence definition's dump flag to decide if we should dump the data or not. That's generally fine, except for when the sequence data entry was created by processExtensionTables() because it's a config sequence. In that case, the sequence itself won't be marked as dumping data because it's part of an extension, leading to the need for processExtensionTables() to create the sequence data entry. This leads to extension config sequence data not being included in the dump when it should be. Fix this by looking at the sequence data's dump flag instead, just as dumpTableData() was doing for tables (which is why config tables were correctly being handled), and add a regression test to make sure we don't break it moving forward. All of this is a bit round-about since we can now represent which components of a given dump item should be dumped out through the dump flag. A future improvement might be to change checkExtensionMembership() to check for config sequences/tables and set the dump flag based on that directly, possibly removing the need for processExtensionTables(). Bug found by Daniele Varrazzo. Discussion: https://postgr.es/m/CA+mi_8ZmxQM7+nZ7pJ8uyfxc9V3o=UAG14dVqvftdmvw8OJ3gQ@mail.gmail.com Patch by Michael Paquier, with some tweaking of the regression tests by me. Back-patch to 9.6 where the bug was introduced. http://git.postgresql.org/pg/commitdiff/bec96c82f8ff4fcf7ef0f070f6f7447edf106de3

Correctifs en attente

Michaël Paquier and Karl O. Pinc traded patches to implement pg_current_logfile().

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

Masahiko Sawada sent in two more revisions of a patch to support FDW transactions.

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

Haribabu Kommi sent in four more revisions of a patch to implement a pg_hba_file_settings view.

Beena Emerson and Robert Haas traded patches to enable increasing WAL segment size.

Amit Kapila and Robert Haas traded patches to enable parallel index scans.

Michaël Paquier sent in a flock of back-patches for an issue in backups on standbys uncovered by sqlsmith.

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

Nikita Glukhov sent in a patch to implement K-nearest-neighbor for B-Tree indexes.

Dilip Kumar sent in another revision of a patch to implement parallel bitmap heap scan.

Magnus Hagander and Michaël Paquier traded patches to implement a version of jsonb_delete which takes arrays as input.

Haribabu Kommi sent in another revision of a patch to implement a pg_stat_sql view.

Etsuro Fujita sent in another revision of a patch to fix a bug in foreign joins.

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

Haribabu Kommi sent in another revision of a patch to implement a 64-bit macaddr type.

Ashutosh Sharma sent in another revision of a patch to add pgstathashindex to the pgstattuple extension.

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

Petr Jelínek sent in two more revisions of a patch to enable logical replication support for the initial data copy.

Petr Jelínek sent in another revision of a patch to add RENAME support for PUBLICATIONs and SUBSCRIPTIONs.

Petr Jelínek sent in a flock of patches to fix issues in logical replication.

Rafia Sabih sent in another revision of a patch to implement parallel index-only scans.

Pavan Deolasee sent in another revision of a patch to implement WARM.

Dmitry Fedin sent in a patch to change a misleading comment in pgtime.h.

Yugo Nagata sent in a patch to fix a comment in freelist.c.

Erik Rijkers sent in a patch to improve the logical replication documentation.

Jim Nasby sent in a patch to protect syscache from bloating with negative cache entries.

Petr Jelínek sent in a patch to fix AFTER trigger execution in logical replication.

Tom Lane sent in another revision of a patch to prevent the possibility of UNKNOWN output columns.