[ndt: Meetup à Lyon le mercredi 26 avril : https://www.meetup.com/fr-FR/PostgreSQL-Lyon-User-Group/]

Offres d'emplois autour de PostgreSQL en avril

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/20170409231442.GA4338@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

Tom Lane pushed:

  • Document psql's behavior of recalling the previously executed query. Various psql slash commands that normally act on the current query buffer will automatically recall and re-use the most recently executed SQL command instead, if the current query buffer is empty. Although this behavior is ancient (dating apparently to commit 77a472993), it was documented nowhere in the psql reference page. For that matter, we'd never bothered to define the concept of "current query buffer" explicitly. Fix that. Do some wordsmithing on relevant command descriptions to improve clarity and consistency. Discussion: https://postgr.es/m/9b4ea968-753f-4b5f-b46c-d7d3bf7c8f90@manitou-mail.org http://git.postgresql.org/pg/commitdiff/68dba97a4dea5c5c915e31978a475107c17c458d
  • Doc: clarify behavior of OT_WHOLE_LINE and OT_FILEPIPE psql slash commands. This is another bit of ancient behavior that was documented poorly (in a couple of cases) or not at all (in several others). Discussion: https://postgr.es/m/9b4ea968-753f-4b5f-b46c-d7d3bf7c8f90@manitou-mail.org http://git.postgresql.org/pg/commitdiff/ffac5998b4c18920f86d80f1bddbde9ebcf0a314
  • Remove reinvention of stringify macro. We already have CppAsString2, there's no need for the MSVC support to re-invent a macro to do that (and especially not to inject it in as ugly a way as this). Discussion: https://postgr.es/m/CADkLM=c+hm2rc0tkKgC-ZgrLttHT2KkfppE+BC-=i-xj+7V-TQ@mail.gmail.com http://git.postgresql.org/pg/commitdiff/cd6baed78150e107eb858dbd53ddf900dd429f50
  • Fix integer-overflow problems in interval comparison. When using integer timestamps, the interval-comparison functions tried to compute the overall magnitude of an interval as an int64 number of microseconds. As reported by Frazer McLean, this overflows for intervals exceeding about 296000 years, which is bad since we nominally allow intervals many times larger than that. That results in wrong comparison results, and possibly in corrupted btree indexes for columns containing such large interval values. To fix, compute the magnitude as int128 instead. Although some compilers have native support for int128 calculations, many don't, so create our own support functions that can do 128-bit addition and multiplication if the compiler support isn't there. These support functions are designed with an eye to allowing the int128 code paths in numeric.c to be rewritten for use on all platforms, although this patch doesn't do that, or even provide all the int128 primitives that will be needed for it. Back-patch as far as 9.4. Earlier releases did not guard against overflow of interval values at all (commit 146604ec4 fixed that), so it seems not very exciting to worry about overly-large intervals for them. Before 9.6, we did not assume that unreferenced "static inline" functions would not draw compiler warnings, so omit functions not directly referenced by timestamp.c, the only present consumer of int128.h. (We could have omitted these functions in HEAD too, but since they were written and debugged on the way to the present patch, and they look likely to be needed by numeric.c, let's keep them in HEAD.) I did not bother to try to prevent such warnings in a --disable-integer-datetimes build, though. Before 9.5, configure will never define HAVE_INT128, so the part of int128.h that exploits a native int128 implementation is dead code in the 9.4 branch. I didn't bother to remove it, thinking that keeping the file looking similar in different branches is more useful. In HEAD only, add a simple test harness for int128.h in src/tools/. In back branches, this does not change the float-timestamps code path. That's not subject to the same kind of overflow risk, since it computes the interval magnitude as float8. (No doubt, when this code was originally written, overflow was disregarded for exactly that reason.) There is a precision hazard instead :-(, but we'll avert our eyes from that question, since no complaints have been reported and that code's deprecated anyway. Kyotaro Horiguchi and Tom Lane Discussion: https://postgr.es/m/1490104629.422698.918452336.26FA96B7@webmail.messagingengine.com http://git.postgresql.org/pg/commitdiff/df1a699e5ba3232f373790b2c9485ddf720c4a70
  • Clean up psql/describe.c's messy query for extended stats. Remove unnecessary casts, safely schema-qualify the ones that remain, lose an unnecessary level of sub-SELECT, reformat for tidiness. http://git.postgresql.org/pg/commitdiff/20c95f27e736837b4af6bef998cb9408d1ad902e
  • Clean up after insufficiently-researched optimization of tuple conversions. tupconvert.c's functions formerly considered that an explicit tuple conversion was necessary if the input and output tupdescs contained different type OIDs. The point of that was to make sure that a composite datum resulting from the conversion would contain the destination rowtype OID in its composite-datum header. However, commit 3838074f8 entirely misunderstood what that check was for, thinking that it had something to do with presence or absence of an OID column within the tuple. Removal of the check broke the no-op conversion path in ExecEvalConvertRowtype, as reported by Ashutosh Bapat. It turns out that of the dozen or so call sites for tupconvert.c functions, ExecEvalConvertRowtype is the only one that cares about the composite-datum header fields in the output tuple. In all the rest, we'd much rather avoid an unnecessary conversion whenever the tuples are physically compatible. Moreover, the comments in tupconvert.c only promise physical compatibility not a metadata match. So, let's accept the removal of the guarantee about the output tuple's rowtype marking, recognizing that this is a API change that could conceivably break third-party callers of tupconvert.c. (So, let's remember to mention it in the v10 release notes.) However, commit 3838074f8 did have a bit of a point here, in that two tuples mustn't be considered physically compatible if one has HEAP_HASOID set and the other doesn't. (Some of the callers of tupconvert.c might not really care about that, but we can't assume it in general.) The previous check accidentally covered that issue, because no RECORD types ever have OIDs, while if two tupdescs have the same named composite type OID then, a fortiori, they have the same tdhasoid setting. If we're removing the type OID match check then we'd better include tdhasoid match as part of the physical compatibility check. Without that hack in tupconvert.c, we need ExecEvalConvertRowtype to take responsibility for inserting the correct rowtype OID label whenever tupconvert.c decides it need not do anything. This is easily done with heap_copy_tuple_as_datum, which will be considerably faster than a tuple disassembly and reassembly anyway; so from a performance standpoint this change is a win all around compared to what happened in earlier branches. It just means a couple more lines of code in ExecEvalConvertRowtype. Ashutosh Bapat and Tom Lane Discussion: https://postgr.es/m/CAFjFpRfvHABV6+oVvGcshF8rHn+1LfRUhj7Jz1CDZ4gPUwehBg@mail.gmail.com http://git.postgresql.org/pg/commitdiff/3f902354b08ac788600f0ae54fcbfc1d4e3ea765
  • Fix planner error (or assert trap) with nested set operations. As reported by Sean Johnston in bug #14614, since 9.6 the planner can fail due to trying to look up the referent of a Var with varno 0. This happens because we generate such Vars in generate_append_tlist, for lack of any better way to describe the output of a SetOp node. In typical situations nothing really cares about that, but given nested set-operation queries we will call estimate_num_groups on the output of the subquery, and that wants to know what a Var actually refers to. That logic used to look at subquery->targetList, but in commit 3fc6e2d7f I'd switched it to look at subroot->processed_tlist, ie the actual output of the subquery plan not the parser's idea of the result. It seemed like a good idea at the time :-(. As a band-aid fix, change it back. Really we ought to have an honest way of naming the outputs of SetOp steps, which suggests that it'd be a good idea for the parser to emit an RTE corresponding to each one. But that's a task for another day, and it certainly wouldn't yield a back-patchable fix. Report: https://postgr.es/m/20170407115808.25934.51866@wrigleys.postgresql.org http://git.postgresql.org/pg/commitdiff/89deca582a345b9c423bed8ebcf24b6ee81a9953
  • Ensure that ExecPrepareExprList's result is all in one memory context. Noted by Amit Langote. Discussion: https://postgr.es/m/aad31672-4983-d95d-d24e-6b42fee9b985@lab.ntt.co.jp http://git.postgresql.org/pg/commitdiff/dbb2a931478a397a2b655eb77e8be8c1ca136f63
  • Optimize joins when the inner relation can be proven unique. If there can certainly be no more than one matching inner row for a given outer row, then the executor can move on to the next outer row as soon as it's found one match; there's no need to continue scanning the inner relation for this outer row. This saves useless scanning in nestloop and hash joins. In merge joins, it offers the opportunity to skip mark/restore processing, because we know we have not advanced past the first possible match for the next outer row. Of course, the devil is in the details: the proof of uniqueness must depend only on joinquals (not otherquals), and if we want to skip mergejoin mark/restore then it must depend only on merge clauses. To avoid adding more planning overhead than absolutely necessary, the present patch errs in the conservative direction: there are cases where inner_unique or skip_mark_restore processing could be used, but it will not do so because it's not sure that the uniqueness proof depended only on "safe" clauses. This could be improved later. David Rowley, reviewed and rather heavily editorialized on by me Discussion: https://postgr.es/m/CAApHDvqF6Sw-TK98bW48TdtFJ+3a7D2mFyZ7++=D-RyPsL76gw@mail.gmail.com http://git.postgresql.org/pg/commitdiff/9c7f5229ad68d7e0e4dd149e3f80257893e404d4
  • Add newly-symlinked files to "make clean" target. Oversight in 60f11b87a. http://git.postgresql.org/pg/commitdiff/aba696d1af9a267eee85d69845c3cdeccf788525
  • Clean up bugs in clause_selectivity() cleanup. Commit ac2b09508 was not terribly carefully reviewed. Band-aid it to not fail on non-RestrictInfo input, per report from Andreas Seltenreich. Also make it do something more reasonable with variable-free clauses, and improve nearby comments. Discussion: https://postgr.es/m/87inmf5rdx.fsf@credativ.de http://git.postgresql.org/pg/commitdiff/eef8c0069e4d5eea2e52965ce3eb018b5a594fd6

Peter Eisentraut pushed:

Andrew Gierth pushed:

Robert Haas pushed:

Stephen Frost pushed:

Simon Riggs pushed:

Andrew Dunstan pushed:

Andres Freund pushed:

Kevin Grittner pushed:

  • Follow-on cleanup for the transition table patch. Commit 59702716 added transition table support to PL/pgsql so that SQL queries in trigger functions could access those transient tables. In order to provide the same level of support for PL/perl, PL/python and PL/tcl, refactor the relevant code into a new function SPI_register_trigger_data. Call the new function in the trigger handler of all four PLs, and document it as a public SPI function so that authors of out-of-tree PLs can do the same. Also get rid of a second QueryEnvironment object that was maintained by PL/pgsql. That was previously used to deal with cursors, but the same approach wasn't appropriate for PLs that are less tangled up with core code. Instead, have SPI_cursor_open install the connection's current QueryEnvironment, as already happens for SPI_execute_plan. While in the docs, remove the note that transition tables were only supported in C and PL/pgSQL triggers, and correct some ommissions. Thomas Munro with some work by Kevin Grittner (mostly docs) http://git.postgresql.org/pg/commitdiff/5ebeb579b9b281dba5f8415b2fbda86fdae7b366
  • Add isolation test for SERIALIZABLE READ ONLY DEFERRABLE. This improves code coverage and lays a foundation for testing similar issues in a distributed environment. Author: Thomas Munro <thomas.munro@enterprisedb.com> Reviewed-by: Michael Paquier <michael.paquier@gmail.com> http://git.postgresql.org/pg/commitdiff/4deb413813f619b3e859abf435b61efc09cafe09
  • Fix the RTE_NAMEDTUPLESTORE case in get_rte_attribute_is_dropped(). Problems pointed out by Andres Freund and Thomas Munro. http://git.postgresql.org/pg/commitdiff/255efa241f460ee4f4c4c98c8cdd7457807f3af9
  • Add GUCs for predicate lock promotion thresholds. Defaults match the fixed behavior of prior releases, but now DBAs have better options to tune serializable workloads. It might be nice to be able to set this per relation, but that part will need to wait for another release. Author: Dagfinn Ilmari Mannsåker http://git.postgresql.org/pg/commitdiff/c63172d60f242ad3581c83723a5b315bbe547a0e

Heikki Linnakangas pushed:

Álvaro Herrera pushed:

Joe Conway pushed:

Magnus Hagander pushed:

Correctifs en attente

Vaishnavi Prabakaran sent in two more revisions of a patch to add batch/pipelining support to libpq.

Craig Ringer sent in five more revisions of a patch to add logical decoding on standby.

Ashutosh Bapat and Amit Langote traded patches to document some intricacies of the relationship between declarative partitions and foreign tables.

Takayuki Tsunakawa sent in three more revisions of a patch to support huge pages on Windows.

Amit Khandekar sent in two more revisions of a patch to enable UPDATEs on partitioned tables which would cause rows to move among partitions to work.

Rushabh Lathia sent in a patch to add coverage tests for gather merge.

Amit Langote sent in a patch to enable ON CONFLICT DO NOTHING on partitioned tables.

Haribabu Kommi sent in another revision of a patch to refactor handling of database attributes between pg_dump and pg_dumpall.

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

Daniel Gustafsson sent in a patch to use strcmp() instead of pg_strcasecmp() for identifier matching.

Kyotaro HORIGUCHI sent in a patch to distinguish "aggressive" VACUUMs from non- in logs.

Vinayak Pokale sent in another revision of a patch to add an ANALYZE progress checker.

Tatsuo Ishii and Andres Freund traded patches to rearm statement_timeout after each executed query.

Michaël Paquier sent in two revisions of a patch to rewrite the test of pg_upgrade as a TAP test.

Kuntal Ghosh sent in two revisions of a patch to fix parallel worker counts after a crash.

Amit Langote sent in a patch to remove pg_stat_progress_vacuum from Table 28.2.

David Rowley sent in three more revisions of a patch to make clausesel smarter.

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

Kyotaro HORIGUCHI sent in another revision of a patch to implement asynchronous execution.

Maksim Milyutin sent in a patch to create a "local" index for partitioned tables.

Fabien COELHO sent in a patch to add a special variable in psql to reflect the last query status.

Ashutosh Bapat sent in two more revisions of a patch to implement partition-wise joins for declaratively partitioned tables.

Rahila Syed sent in three more revisions of a patch to add support for a default partition in declarative partitions.

Craig Ringer and Jim Nasby traded patches to add SPI_execute_callback and documentation for same, then use it in PL/PythonU to speed up SPI results.

Dmitry Dolgov sent in two more revisions of a patch to implement generic type subscripting.

Amit Khandekar sent in another revision of a patch to implement parallel append.

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

Pavan Deolasee sent in two more revisions of a patch to implement WARM.

Mark Dilger sent in a patch to create a generic PG_GETARG_*_P infrastructure.

Robert Haas sent in another revision of a patch to add pgstathashindex() to get hash index table statistics.

Etsuro Fujita sent in another revision of a patch to support parameterized foreign joins.

Fabien COELHO sent in three more revisions of a patch to enable pgbench to store select results into variables.

Jeff Davis sent in a patch to implement range merge join.

Ashutosh Bapat sent in a patch to implement constraint exclusion for partitioned tables.

Kyotaro HORIGUCHI sent in a patch to ensure that enabling subscription starts a worker.

Michaël Paquier sent in a patch to use base64-based encoding for stored and server keys in SCRAM verifiers, move the routine to build the SCRAM verifier into src/common/, refactor frontend-side random number generation, extend PQencryptPassword with a hashing method, and extend psql's \password and createuser to handle SCRAM verifier creation.

Tatsuro Yamada sent in a patch to make postgresGetForeignPlan use foreignrel instead of baserel.

Thomas Munro sent in a patch to implement hash tables in dynamic shared memory.

Thomas Munro sent in a PoC patch to enable sharing record typmods between backends.

Vitaly Burovoy sent in a patch to implement SET IDENTITY ... IF NOT EXISTS.

Kyotaro HORIGUCHI sent in a patch to fix distclean of src/backend/utils/mb/Unicode.

Yorick Peterse sent in two revisions of a patch to update the hot-standby documentation (in the high availability section) so it explicitly mentions that certain settings need to be applied to servers in a particular order.

Beena Emerson sent in a patch to update the initdb regression tests to include increasing the default WAL segment size.

Aleksander Alekseev sent in another revision of a patch to remove an unused argument in btree_xlog_split.

Tatsuo Ishii sent in a patch to document the fact that pg_export_snapshot() cannot be used during recovery (i.e. on standby servers).

Aleksander Alekseev sent in a patch to warn users about duplicate configuration parameters in postgresql.conf.

Álvaro Herrera sent in another revision of a patch to fix wal_level_minimal.

Thomas Munro sent in a patch to initialise a freed segment counter in dsa.c.

Claudio Freire sent in another revision of a patch to allow VACUUM to use more than 1GB work mem in, and make it free dead tuples array as early as possible.

Thomas Munro sent in a patch to fix and document some snapshot issues.

Thomas Munro sent in a patch to add a pg_waiting_for_safe_snapshot() function.

Tom Lane and Mark Dilger traded patches to use IsA checks for bitmapsets.

Michael Banck sent in another revision of a patch to add an option to create a replication slot in pg_basebackup if not yet present.