La PgConf Silicon Valley 2016 aura lieu du 14 au 16 novembre 2016 : http://www.pgconfsv.com/

[ndt: meetup à Nantes le jeudi 17 mars : http://www.meetup.com/fr-FR/PostgreSQL-User-Group-Nantes/]

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

PostgreSQL Local

  • La première conférence PostgreSQL pan-asiatique se tiendra les 17 et 19 mars 2016 à Singapour. Les inscriptions sont ouvertes : http://2016.pgday.asia/
  • Le PGDay nordique, une série de conférences sur une seule journée, aura lieu à Helsinki (Finlande) le 17 mars 2016. Les inscriptions sont encore ouvertes : http://2016.nordicpgday.org/
  • Les inscriptions pour le PGDay Paris 2016, prévu pour le 31 mars, sont ouvertes : http://www.pgday.paris/registration/
  • La 8ème Session PostgreSQL aura lieu le 6 avril 2016 à Lyon (France).
  • La PGConf US 2016 aura lieu les 18, 19 et 20 avril à New-York City. Les inscriptions sont ouvertes : http://www.pgconf.us/2016/
  • La LinuxFest Northwest aura lieu les 23 et 24 avril 2016 au Collège Technique de Bellingham (Washington, USA). L'appel à conférenciers est maintenant lancé : http://www.linuxfestnorthwest.org/2016/present
  • FOSS4G NA (Free and Open Source Software for Geospatial - North America) se tiendra à Raleigh, en Caroline du Nord, du 2 au 5 mai 2016. Les candidatures de conférenciers sont encore acceptées : https://2016.foss4g-na.org/cfp
  • La PGCon 2016 se tiendra du 17 au 21 mai 2016 à Ottawa : http://www.pgcon.org/
  • Le PGDay suisse sera, cette année, tenue à l'Université des Sciences Appliquées (HSR) de Rapperswil le 24 juin 2016. L'appel à conférenciers est lancé : http://www.pgday.ch/
  • "5432 ... Meet us!" aura lieu à Milan (Italie) les 28 & 29 juin 2016. L'appel à conférenciers court jusqu'au 14 mars : http://5432meet.us/

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/20160306234210.GE28543@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:

  • Avoid multiple free_struct_lconv() calls on same data. A failure partway through PGLC_localeconv() led to a situation where the next call would call free_struct_lconv() a second time, leading to free() on already-freed strings, typically leading to a core dump. Add a flag to remember whether we need to do that. Per report from Thom Brown. His example case only provokes the failure as far back as 9.4, but nonetheless this code is obviously broken, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/907e4dd2b104bdcb4af042065a92fcd73d5790ec
  • Fix build under OPTIMIZER_DEBUG. In commit 19a541143a09c067 I replaced RelOptInfo.width with RelOptInfo.reltarget.width, but I missed updating debug_print_rel() for that because it's not compiled by default. Reported by Salvador Fandino, patch by Michael Paquier. http://git.postgresql.org/pg/commitdiff/05893712cc9950b7c4c312aa9d3d444375bf15a2
  • Remove useless unary plus. It's harmless, but might confuse readers. Seems to have been introduced in 6bc8ef0b7f1f1df3. Back-patch, just to avoid cosmetic cross-branch differences. Amit Langote http://git.postgresql.org/pg/commitdiff/c110678a47aac87c661785a70061e160cd17713d
  • Improve error message for rejecting RETURNING clauses with dropped columns. This error message was written with only ON SELECT rules in mind, but since then we also made RETURNING-clause targetlists go through the same logic. This means that you got a rather off-topic error message if you tried to add a rule with RETURNING to a table having dropped columns. Ideally we'd just support that, but some preliminary investigation says that it might be a significant amount of work. Seeing that Nicklas Avén's complaint is the first one we've gotten about this in the ten years or so that the code's been like that, I'm unwilling to put much time into it. Instead, improve the error report by issuing a different message for RETURNING cases, and revise the associated comment based on this investigation. Discussion: 1456176604.17219.9.camel@jordogskog.no http://git.postgresql.org/pg/commitdiff/8d8ff5f7db7d58240fac7d5f620308c91485b253
  • Suppress scary-looking log messages from async-notify isolation test. I noticed that the async-notify test results in log messages like these: LOG: could not send data to client: Broken pipe FATAL: connection to client lost This is because it unceremoniously disconnects a client session that is about to have some NOTIFY messages delivered to it. Such log messages during a regression test might well cause people to go looking for a problem that doesn't really exist (it did cause me to waste some time that way). We can shut it up by adding an UNLISTEN command to session teardown. Patch HEAD only; this doesn't seem significant enough to back-patch. http://git.postgresql.org/pg/commitdiff/3d523564c53ab8f35edf4d20627f0a375a17624d
  • Improve coverage of pltcl regression tests. Test composite-type arguments and the argisnull and spi_lastoid Tcl commmands. This stuff was not covered before, but needs to be exercised since the upcoming Tcl object-conversion patch changes these code paths (and broke at least one of them). http://git.postgresql.org/pg/commitdiff/68c521eb92c3515e3306f51a7fd3f32d16c97524
  • Fix TAP tests for older Perls. Commit 7132810c (Retain tempdirs for failed tests) used Test::More's is_passing method, but that was added in Test::More 0.89_01 which is sometime later than Perl 5.10.1. Popular platforms such as RHEL6 don't have that, nevermind some of our older dinosaurs. Do it the hard way. Michael Paquier, based on research by Craig Ringer http://git.postgresql.org/pg/commitdiff/3b8d7215533ed3128b1b9174eae830d70c0453d0
  • Convert PL/Tcl to use Tcl's "object" interfaces. The original implementation of Tcl was all strings, but they improved performance significantly by introducing typed "objects" (integers, lists, code, etc). It's past time we made use of that; that happened in Tcl 8.0 which was released in 1997. This patch also modernizes some of the error-reporting code, which may cause small changes in the spelling of complaints about bad calls to PL/Tcl-provided commands. Jim Nasby and Karl Lehenbauer, reviewed by Victor Wagner http://git.postgresql.org/pg/commitdiff/287822068246a6ae30bb2c7191de727672ae6328
  • Make PL/Tcl require Tcl 8.4 or later. As of commit 287822068246a6ae30bb2c7191de727672ae6328, PL/Tcl will not compile against pre-8.0 Tcl, whereas it used to work (more or less anyway) with quite prehistoric versions. As long as we're moving these goalposts, let's reinstall them at someplace that has some thought behind it. This commit sets the minimum allowed Tcl version at 8.4, and rips out some bits of compatibility cruft that are in consequence no longer needed. Reasons for requiring 8.4 include: * 8.4 was released in 2002; there seems little reason to believe that anyone would want to use older versions with Postgres 9.6+. * We have no buildfarm members testing anything older than 8.4, and thus no way to know if it's broken. * We need at least 8.1 to allow enforcement of database encoding security (8.1 standardized Tcl on using UTF8 internally, before that it was pretty unpredictable). * Some versions between 8.1 and 8.4 allowed the backend to become multithreaded, which is disastrous. We need at least 8.4 to be able to disable the Tcl notifier subsystem to prevent that. A small side benefit is that we can make the code more readable by doing s/CONST84/const/g. http://git.postgresql.org/pg/commitdiff/e2609323eb58ee273ac03a66343447e6a56154d5
  • Fix PL/Tcl's encoding conversion logic. PL/Tcl appears to contain logic to convert strings between the database encoding and UTF8, which is the only encoding modern Tcl will deal with. However, that code has been disabled since commit 034895125d648b86, which made it "#if defined(UNICODE_CONVERSION)" and neglected to provide any way for that symbol to become defined. That might have been all right back in 2001, but these days we take a dim view of allowing strings with incorrect encoding into the database. Remove the conditional compilation, fix warnings about signed/unsigned char conversions, clean up assorted places that didn't bother with conversions. (Notably, there were lots of assumptions that database table and field names didn't need conversion...) Add a regression test based on plpython_unicode. It's not terribly thorough, but better than no test at all. http://git.postgresql.org/pg/commitdiff/c8c7c93de8e116d802eddfd8821f8f77588aee00
  • Create stub functions to support pg_upgrade of old contrib/tsearch2. Commits 9ff60273e35cad6e and dbe2328959e12701 adjusted the declarations of some core functions referenced by contrib/tsearch2's install script, forgetting that in a pg_upgrade situation, we'll be trying to restore operator class definitions that reference the old signatures. We've hit this problem before; solve it in the same way as before, namely by installing stub functions that have the expected signature and just invoke the correct function. Per report from Jeff Janes. (Someday we ought to stop supporting contrib/tsearch2, but I'm not sure today is that day.) http://git.postgresql.org/pg/commitdiff/eb43e851d6b3fa0c4516efcfdf29a183f7717a43
  • Fix json_to_record() bug with nested objects. A thinko concerning nesting depth caused json_to_record() to produce bogus output if a field of its input object contained a sub-object with a field name matching one of the requested output column names. Per bug #13996 from Johann Visagie. I added a regression test case based on his example, plus parallel tests for json_to_recordset, jsonb_to_record, jsonb_to_recordset. The latter three do not exhibit the same bug (which suggests that we may be missing some opportunities to share code...) but testing seems like a good idea in any case. Back-patch to 9.4 where these functions were introduced. http://git.postgresql.org/pg/commitdiff/a9d199f6d3b998929cdb8e8aa61e5cd8db9b220f
  • Make stats regression test robust in the face of parallel query. Historically, the wait_for_stats() function in this test has simply checked for a report of an indexscan on tenk2, corresponding to the last command issued before we expect stats updates to appear. However, with parallel query that indexscan could be done by a parallel worker that will emit its stats counters to the collector before the session's main backend does (a full second before, in fact, thanks to the "pg_sleep(1.0)" added by commit 957d08c81f9cc277). That leaves a sizable window in which an autovacuum-triggered write of the stats files would present a state in which the indexscan on tenk2 appears to have been done, but none of the write updates performed by the test have been. This is evidently the explanation for intermittent failures seen by me and on buildfarm member mandrill. To fix, we should check separately for both the tenk2 seqscan and indexscan counts, since those might be reported by different processes that could be delayed arbitrarily on an overloaded test machine. And we need to check for at least one update-related count. If we ever allow parallel workers to do writes, this will get even more complicated ... but in view of all the other hard problems that will entail, I don't feel a need to solve this one today. Per research by Rahila Syed and myself; part of this patch is Rahila's. http://git.postgresql.org/pg/commitdiff/60690a6fe8351995b1eeb9a53f2b634c3bce3a3d

Dean Rasheed pushed:

  • Fix incorrect varlevelsup in security_barrier_replace_vars(). When converting an RTE with securityQuals into a security barrier subquery RTE, ensure that the Vars in the new subquery's targetlist all have varlevelsup = 0 so that they correctly refer to the underlying base relation being wrapped. The original code was creating new Vars by copying them from existing Vars referencing the base relation found elsewhere in the query, but failed to account for the fact that such Vars could come from sublink subqueries, and hence have varlevelsup > 0. In practice it looks like this could only happen with nested security barrier views, where the outer view has a WHERE clause containing a correlated subquery, due to the order in which the Vars are processed. Bug: #13988 Reported-by: Adam Guthrie Backpatch-to: 9.4, where updatable SB views were introduced http://git.postgresql.org/pg/commitdiff/41fedc24626696fdf55d0c43131d91757dbe1c66

Ãlvaro Herrera pushed:

Peter Eisentraut pushed:

Robert Haas pushed:

Andres Freund pushed:

  • logical decoding: fix decoding of a commit's commit time. When adding replication origins in 5aa235042, I somehow managed to set the timestamp of decoded transactions to InvalidXLogRecptr when decoding one made without a replication origin. Fix that, and the wrong type of the new commit_time variable. This didn't trigger a regression test failure because we explicitly don't show commit timestamps in the regression tests, as they obviously are variable. Add a test that checks that a decoded commit's timestamp is within minutes of NOW() from before the commit. Reported-By: Weiping Qu Diagnosed-By: Artur Zakirov Discussion: 56D4197E.9050706@informatik.uni-kl.de, 56D42918.1010108@postgrespro.ru Backpatch: 9.5, where 5aa235042 originates. http://git.postgresql.org/pg/commitdiff/7c17aac69dcae610b08c5965161151cd282f16bc
  • Force synchronous_commit=on in test_decoding's concurrent_ddl_dml.spec. Otherwise running installcheck-force on a server with synchronous_commit=off will result in the tests failing. All the other tests already do so... Backpatch: 9.4, where logical decoding was added http://git.postgresql.org/pg/commitdiff/1986c3c440151b056877b446e7d9c2861906aa26
  • logical decoding: old/newtuple in spooled UPDATE changes was switched around. Somehow I managed to flip the order of restoring old & new tuples when de-spooling a change in a large transaction from disk. This happens to only take effect when a change is spooled to disk which has old/new versions of the tuple. That only is the case for UPDATEs where he primary key changed or where replica identity is changed to FULL. The tests didn't catch this because either spooled updates, or updates that changed primary keys, were tested; not both at the same time. Found while adding tests for the following commit. Backpatch: 9.4, where logical decoding was added http://git.postgresql.org/pg/commitdiff/0bda14d54cf24dedcd2011559a53cc62702e421b
  • logical decoding: Fix handling of large old tuples with replica identity full. When decoding the old version of an UPDATE or DELETE change, and if that tuple was bigger than MaxHeapTupleSize, we either Assert'ed out, or failed in more subtle ways in non-assert builds. Normally individual tuples aren't bigger than MaxHeapTupleSize, with big datums toasted. But that's not the case for the old version of a tuple for logical decoding; the replica identity is logged as one piece. With the default replica identity btree limits that to small tuples, but that's not the case for FULL. Change the tuple buffer infrastructure to separate allocate over-large tuples, instead of always going through the slab cache. This unfortunately requires changing the ReorderBufferTupleBuf definition, we need to store the allocated size someplace. To avoid requiring output plugins to recompile, don't store HeapTupleHeaderData directly after HeapTupleData, but point to it via t_data; that leaves rooms for the allocated size. As there's no reason for an output plugin to look at ReorderBufferTupleBuf->t_data.header, remove the field. It was just a minor convenience having it directly accessible. Reported-By: Adam DratwiÅ„ski Discussion: CAKg6ypLd7773AOX4DiOGRwQk1TVOQKhNwjYiVjJnpq8Wo+i62Q@mail.gmail.com http://git.postgresql.org/pg/commitdiff/c8f621c43a599b35dc004ee09627bf4688cbbb84
  • logical decoding: Tell reorderbuffer about all xids. Logical decoding's reorderbuffer keeps transactions in an LSN ordered list for efficiency. To make that's efficiently possible upper-level xids are forced to be logged before nested subtransaction xids. That only works though if these records are all looked at: Unfortunately we didn't do so for e.g. row level locks, which are otherwise uninteresting for logical decoding. This could lead to errors like: "ERROR: subxact logged without previous toplevel record". It's not sufficient to just look at row locking records, the xid could appear first due to a lot of other types of records (which will trigger the transaction to be marked logged with MarkCurrentTransactionIdLoggedIfAny). So invent infrastructure to tell reorderbuffer about xids seen, when they'd otherwise not pass through reorderbuffer.c. Reported-By: Jarred Ward Bug: #13844 Discussion: 20160105033249.1087.66040@wrigleys.postgresql.org Backpatch: 9.4, where logical decoding was added http://git.postgresql.org/pg/commitdiff/d9e903f3cbbd00c7ba7d4974e6852c3d2cbf4447

Magnus Hagander pushed:

Simon Riggs pushed:

Teodor Sigaev pushed:

Fujii Masao pushed:

  • Ignore recovery_min_apply_delay until recovery has reached consistent state. Previously recovery_min_apply_delay was applied even before recovery had reached consistency. This could cause us to wait a long time unexpectedly for read-only connections to be allowed. It's problematic because the standby was useless during that wait time. This patch changes recovery_min_apply_delay so that it's applied once the database has reached the consistent state. That is, even if the delay is set, the standby tries to replay WAL records as fast as possible until it has reached consistency. Author: Michael Paquier Reviewed-By: Julien Rouhaud Reported-By: Greg Clough Backpatch: 9.4, where recovery_min_apply_delay was added Bug: #13770 Discussion: http://www.postgresql.org/message-id/20151111155006.2644.84564@wrigleys.postgresql.org http://git.postgresql.org/pg/commitdiff/d34794f7d5566effd342dd0ebaca3de3b48656f0

Joe Conway pushed:

  • Expose control file data via SQL accessible functions. Add four new SQL accessible functions: pg_control_system(), pg_control_checkpoint(), pg_control_recovery(), and pg_control_init() which expose a subset of the control file data. Along the way move the code to read and validate the control file to src/common, where it can be shared by the new backend functions and the original pg_controldata frontend program. Patch by me, significant input, testing, and review by Michael Paquier. http://git.postgresql.org/pg/commitdiff/dc7d70ea05deca9dfc6a25043d406b57cc8f6c30

Correctifs rejetés (à ce jour)

No one was disappointed this week :-)

Correctifs en attente

Peter Eisentraut sent in another revision of a patch add pg_ctl promote wait.

Thomas Munro sent in two more revisions of a patch to add 'causal reads.'

Joe Conway and Pavel Stěhule traded patches to add a SET ROLE hook.

Alexander Korotkov sent in another revision of a patch to implement access method extensibility.

Kaigai Kouhei sent in a patch to rework the serialization/deserialization interface for CustomScan.

Michaël Paquier sent in a patch to fix a bug where setting OPTIMIZER_DEBUG broke the compilation.

Teodor Sigaev sent in another revision of a patch to make OR use indexes.

Dmitry Dolgov sent in another revision of a patch to add a jsonb_insert() function.

Petr Jelínek sent in another revision of a patch to implement generic logical WAL messages.

Peter Geoghegan sent in a patch to add amcheck, a B-Tree integrity checking tool.

Peter Eisentraut sent in another revision of a patch to remove the "archive" WAL level.

Abhijit Menon-Sen sent in a patch to add a DEPENDENCY_AUTO_EXTENSION dependency type.

Fabrízio de Royes Mello sent in a patch to reduce lock levels others reloptions in ALTER TABLE.

Iacob Catalin and Pavel Stěhule traded patches to implement ereport in PL/PythonU.

Stephen Frost sent in a patch to make pg_dump dump ACLs for pg_catalog objects.

Ãlvaro Herrera sent in a patch to fix pg_dump and copy for huge (~1 GB) lines.

Kevin Grittner sent in two more revisions of a patch to implement "snapshot too old" configured by time.

Michaël Paquier sent in a patch to fix OOM error handling in COPY protocol of libpq.

Peter Eisentraut sent in a patch to fix the jumble that is the state of the pg_resetxlog documentation.

Pavan Deolasee sent in a patch to WAL log only the necessary part of 2PC GID.

Stephen Frost sent in another revision of a patch to implement default roles.

Julien Rouhaud sent in two more revisions of a patch to add hooks to autovacuum and add a pg_stat_autovacuum to use same.

Dilip Kumar sent in two more revisions of a patch to help scale relation extension.

Alexander Korotkov sent in another revision of a patch to move PinBuffer and UnpinBuffer to atomics.

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

Roma Sokolov sent in another revision of a patch to fix DROP OPERATOR to reset links to itself on commutator and negator.

Anastasia Lubennikova sent in another revision of a patch to add covering + unique indexes.

Craig Ringer sent in three more revisions of a patch to allow logical slots to follow timeline switches.

Etsuro Fujita sent in another revision of a patch to improve write performance in the PostgreSQL FDW.

Michaël Paquier sent in another revision of a patch to fix handling of --enable-tap-tests in MSVC scripts.

Robert Haas sent in a patch to add a contrib module to examine the visibility map.

Tomas Vondra sent in another revision of a patch to add multivariate statistics for query planning.

Ãlvaro Herrera sent in a patch to fix the omission of BRIN from the CREATE OPERATOR CLASS documentation.

Tomas Vondra sent in a patch to check DBEntry,stats_timestamp in pgstat_recv_inquiry() and ignore requests that are already resolved by the last write.

Haribabu Kommi sent in another revision of a patch to add a pg_hba_lookup() function.

Emre Hasegeli sent in a patch to add SP-GiST support for inet datatypes.

Alexander Shulgin sent in another revision of a patch to account for NULLs in ANALYZE more strictly and try to account for skewed distributions in ANALYZE.

Pavel Stěhule sent in another revision of a patch to add ELEMENT OF to PL/pgsql declarations.

Mithun Cy sent in another revision of a patch to cache data in GetSnapshotData.

Kyotaro HORIGUCHI sent in a patch to allow :: casts to tab complete in psql.

SAWADA Masahiko sent in two more revisions of a patch to allow N>1 synchronous standbys.

Robert Haas sent in another revision of a patch to fix an issue that manifested with the postgres_fdw in force_parallel_mode on ppc.

Amit Kapila and Thom Brown traded patches to replace pg_stat_activity.waiting with something more descriptive.

Michaël Paquier sent in two more revisions of a patch to support VS 2015.

David Rowley and Haribabu Kommi traded patches to implement parallel aggregate.

Fabien COELHO sent in three more revisions of a patch to add pgbench stats per script, etc.

Petr Jelínek sent in another revision of a patch to add a sequence access method.

Fabien COELHO sent in another revision of a patch to extend pgbench expressions with functions.

Amit Langote sent in two more revisions of a patch to implement a vacuum progress checker.

Tom Lane sent in two more revisions of a patch to path-ify the upper planner.

Michaël Paquier sent in another revision of a patch to ensure that xlog fsync happens in a way that does not lose data.

Guillaume Lelarge sent in a patch to fix a typo in the psql documentation.