Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en janvier

PostgreSQL Local

  • PostgreSQL@SCaLE est un événement de deux jours avec deux programmes qui aura lieu les 21 et 22 janvier 2016 au Pasadena Convention Center à l'occasion du SCaLE 14X. https://www.socallinuxexpo.org/scale/14x/cfp
  • FOSDEM PGDay est une conférence d'une journée qui sera tenue avant le FOSDEM à Bruxelles (Belgique) le 29 janvier 2015. Détails et appel à conférenciers ci-après : http://fosdem2016.pgconf.eu/
  • Prague PostgreSQL Developer Day 2016 (P2D2 2016) est une conférence sur deux jours, les 17 et 18 février 2016, à Prague (République Tchèque). Site en tchèque : http://www.p2d2.cz/
  • Le PGday annuel indien se tiendra à Bangalore (État du Karnataka en Inde) le 26 février 2016. L'appel à conférenciers est lancé : http://pgday.in
  • La première conférence PostgreSQL pan-asiatique se tiendra les 16 et 17 mars 2016 à Singapour. L'appel à conférenciers est ouvert : 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. L'appel à conférenciers est lancé : http://2016.nordicpgday.org/
  • La PGConf US 2016 aura lieu les 18, 19 et 20 avril à New-York. L'appel à conférenciers expire au 31 janvier 2016, 23:59EST : http://www.pgconf.us/2016/
  • La PGCon 2016 se tiendra du 17 au 21 mai 2016 à Ottawa. L'appel à conférenciers a été lancé : http://www.pgcon.org/

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/20160104004346.GH30007@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

Ãlvaro Herrera pushed:

Tom Lane pushed:

  • Update documentation about pseudo-types. Tone down an overly strong statement about which pseudo-types PLs are likely to allow. Add "event_trigger" to the list, as well as "pg_ddl_command" in 9.5/HEAD. Back-patch to 9.3 where event_trigger was added. http://git.postgresql.org/pg/commitdiff/731dfc7d5f07fac2c3c72f47c29a947e363acee9
  • Fix omission of -X (--no-psqlrc) in some psql invocations. As of commit d5563d7df, psql -c no longer implies -X, but not all of our regression testing scripts had gotten that memo. To ensure consistency of results across different developers, make sure that *all* invocations of psql in all scripts in our tree use -X, even where this is not what previously happened. Michael Paquier and Tom Lane http://git.postgresql.org/pg/commitdiff/870df2b3b77414a536d6533566628f11f8f309ec
  • Document the exponentiation operator as associating left to right. Common mathematical convention is that exponentiation associates right to left. We aren't going to change the parser for this, but we could note it in the operator's description. (It's already noted in the operator precedence/associativity table, but users might not look there.) Per bug #13829 from Henrik Pauli. http://git.postgresql.org/pg/commitdiff/54aaafe95f65c95fd9ba085826af87d778c94613
  • Code and docs review for cube kNN support. Commit 33bd250f6c4cc309f4eeb657da80f1e7743b3e5c could have done with some more review: Adjust coding so that compilers unfamiliar with elog/ereport don't complain about uninitialized values. Fix misuse of PG_GETARG_INT16 to retrieve arguments declared as "integer" at the SQL level. (This was evidently copied from cube_ll_coord and cube_ur_coord, but those were wrong too.) Fix non-style-guide-conforming error messages. Fix underparenthesized if statements, which pgindent would have made a hash of, and remove some unnecessary parens elsewhere. Run pgindent over new code. Revise documentation: repeated accretion of more operators without any rethinking of the text already there had left things in a bit of a mess. Merge all the cube operators into one table and adjust surrounding text appropriately. David Rowley and Tom Lane http://git.postgresql.org/pg/commitdiff/81ee726d87ec67c4f2846110c99f72e8a20dcd07
  • Put back one copyObject() in rewriteTargetView(). Commit 6f8cb1e23485bd6d tried to centralize rewriteTargetView's copying of a target view's Query struct. However, it ignored the fact that the jointree->quals field was used twice. This only accidentally failed to fail immediately because the same ChangeVarNodes mutation is applied in both cases, so that we end up with logically identical expression trees for both uses (and, as the code stands, the second ChangeVarNodes call actually does nothing). However, we end up linking *physically* identical expression trees into both an RTE's securityQuals list and the WithCheckOption list. That's pretty dangerous, mainly because prepsecurity.c is utterly cavalier about further munging such structures without copying them first. There may be no live bug in HEAD as a consequence of the fact that we apply preprocess_expression in between here and prepsecurity.c, and that will make a copy of the tree anyway. Or it may just be that the regression tests happen to not trip over it. (I noticed this only because things fell over pretty badly when I tried to relocate the planner's call of expand_security_quals to before expression preprocessing.) In any case it's very fragile because if anyone tried to make the securityQuals and WithCheckOption trees diverge before we reach preprocess_expression, it would not work. The fact that the current code will preprocess securityQuals and WithCheckOptions lists at completely different times in different query levels does nothing to increase my trust that that can't happen. In view of the fact that 9.5.0 is almost upon us and the aforesaid commit has seen exactly zero field testing, the prudent course is to make an extra copy of the quals so that the behavior is not different from what has been in the field during beta. http://git.postgresql.org/pg/commitdiff/fd1952575618cacf7afa544d8b89ddb77be9eaee
  • Add some comments about division of labor between rewriter and planner. The rationale for the way targetlist processing is done wasn't clearly stated anywhere, and I for one had forgotten some of the details. Having just painfully re-learned them, add some breadcrumbs for the next person. http://git.postgresql.org/pg/commitdiff/efe4c9d7049f0bf832b792bfad05c92aaf86aa3c
  • Minor hacking on contrib/cube documentation. Improve markup, particularly of the table of functions; add or improve examples for some of the functions; wordsmith some of the function descriptions. http://git.postgresql.org/pg/commitdiff/e5e5267a91f4880c121bf50865cbc38078441989
  • Avoid useless truncation attempts during VACUUM. VACUUM can skip heap pages altogether when there's a run of consecutive pages that are all-visible according to the visibility map. This causes it to not update its nonempty_pages count, just as if those pages were empty, which means that at the end we will think they are candidates for deletion. Thus, we may take the table's AccessExclusive lock only to find that no pages are really truncatable. This usually causes no real problems on a master server, thanks to the lock being acquired only conditionally; but on hot-standby servers, the same lock must be acquired unconditionally which can result in unnecessary query cancellations. To improve matters, force examination of the table's last page whenever we reach there with a nonempty_pages count that would allow a truncation attempt. If it's not empty, we'll advance nonempty_pages and thereby prevent the truncation attempt. If we are unable to acquire cleanup lock on that page, there's no need to force it, unless we're doing an anti-wraparound vacuum. We can just check for tuples with a shared buffer lock and then give up. (When we are doing an anti-wraparound vacuum, and decide it's okay to skip the page because it contains no freezable tuples, this patch still improves matters because nonempty_pages is properly updated, which it was not before.) Since only the last page is special-cased in this way, we might attempt a truncation that will release many fewer pages than the normal heuristic would suggest; at worst, only one page would be truncated. But that seems all right, because the situation won't repeat during the next vacuum. The real problem with the old logic is that the useless truncation attempt happens every time we vacuum, so long as the state of the last few dozen pages doesn't change. This is a longstanding deficiency, but since the consequences aren't very severe in most scenarios, I'm not going to risk a back-patch. Jeff Janes and Tom Lane http://git.postgresql.org/pg/commitdiff/e842908233bb9c5cea0e765fc828b52badd8228e
  • Dept of second thoughts: the !scan_all exit mustn't increase scanned_pages. In the extreme edge case where contended pages are the only ones that escape being scanned, the previous commit would have allowed us to think that relfrozenxid could be advanced, which is exactly wrong. http://git.postgresql.org/pg/commitdiff/e5d06f2b12a7c75f2b0c7fd2055a14efaa2b59ec
  • Fix ALTER OPERATOR to update dependencies properly. Fix an oversight in commit 321eed5f0f7563a0: replacing an operator's selectivity functions needs to result in a corresponding update in pg_depend. We have a function that can handle that, but it was not called by AlterOperator(). To fix this without enlarging pg_operator.h's #include list beyond what clients can safely include, split off the function definitions into a new file pg_operator_fn.h, similarly to what we've done for some other catalog header files. It's not entirely clear whether any client-side code needs to include pg_operator.h, but it seems prudent to assume that there is some such code somewhere. http://git.postgresql.org/pg/commitdiff/0dab5ef39b3d9d86e45bbbb2f6ea60b4f5517d9a
  • Add a comment noting that FDWs don't have to implement EXCEPT or LIMIT TO. postgresImportForeignSchema pays attention to IMPORT's EXCEPT and LIMIT TO options, but only as an efficiency hack, not for correctness' sake. The FDW documentation does explain that, but someone using postgres_fdw.c as a coding guide might not remember it, so let's add a comment here. Per question from Regina Obe. http://git.postgresql.org/pg/commitdiff/5f36096b77fe47015cbac130d1a20d089f202a1e
  • Add some more defenses against silly estimates to gincostestimate(). A report from Andy Colson showed that gincostestimate() was not being nearly paranoid enough about whether to believe the statistics it finds in the index metapage. The problem is that the metapage stats (other than the pending-pages count) are only updated by VACUUM, and in the worst case could still reflect the index's original empty state even when it has grown to many entries. We attempted to deal with that by scaling up the stats to match the current index size, but if nEntries is zero then scaling it up still gives zero. Moreover, the proportion of pages that are entry pages vs. data pages vs. pending pages is unlikely to be estimated very well by scaling if the index is now orders of magnitude larger than before. We can improve matters by expanding the use of the rule-of-thumb estimates I introduced in commit 7fb008c5ee59b040: if the index has grown by more than a cutoff amount (here set at 4X growth) since VACUUM, then use the rule-of-thumb numbers instead of scaling. This might not be exactly right but it seems much less likely to produce insane estimates. I also improved both the scaling estimate and the rule-of-thumb estimate to account for numPendingPages, since it's reasonable to expect that that is accurate in any case, and certainly pages that are in the pending list are not either entry or data pages. As a somewhat separate issue, adjust the estimation equations that are concerned with extra fetches for partial-match searches. These equations suppose that a fraction partialEntries / numEntries of the entry and data pages will be visited as a consequence of a partial-match search. Now, it's physically impossible for that fraction to exceed one, but our estimate of partialEntries is mostly bunk, and our estimate of numEntries isn't exactly gospel either, so we could arrive at a silly value. In the example presented by Andy we were coming out with a value of 100, leading to insane cost estimates. Clamp the fraction to one to avoid that. Like the previous patch, back-patch to all supported branches; this problem can be demonstrated in one form or another in all of them. http://git.postgresql.org/pg/commitdiff/3c93a60f6019768f5742b7893a93db93fb67e71f
  • Teach flatten_reloptions() to quote option values safely. flatten_reloptions() supposed that it didn't really need to do anything beyond inserting commas between reloption array elements. However, in principle the value of a reloption could be nearly anything, since the grammar allows a quoted string there. Any restrictions on it would come from validity checking appropriate to the particular option, if any. A reloption value that isn't a simple identifier or number could thus lead to dump/reload failures due to syntax errors in CREATE statements issued by pg_dump. We've gotten away with not worrying about this so far with the core-supported reloptions, but extensions might allow reloption values that cause trouble, as in bug #13840 from Kouhei Sutou. To fix, split the reloption array elements explicitly, and then convert any value that doesn't look like a safe identifier to a string literal. (The details of the quoting rule could be debated, but this way is safe and requires little code.) While we're at it, also quote reloption names if they're not safe identifiers; that may not be a likely problem in the field, but we might as well try to be bulletproof here. It's been like this for a long time, so back-patch to all supported branches. Kouhei Sutou, adjusted some by me http://git.postgresql.org/pg/commitdiff/c7e27becd2e6eb93b20965b9f22701eaad42a764
  • Update copyright for 2016 Manually fix some copyright lines missed by the automated script. http://git.postgresql.org/pg/commitdiff/ad08bf5c8b96c2a3a70d96f5be1c04cb83b4ed6e
  • Update copyright for 2016 On closer inspection, the reason copyright.pl was missing files is that it is looking for 'Copyright (c)' and they had 'Copyright (C)'. Fix that, and update a couple more that grepping for that revealed. http://git.postgresql.org/pg/commitdiff/48c9f2889a4ad25a771d13b88f2778a306f2d970
  • Make copyright.pl cope with nonstandard case choices in copyright notices. The need for this is shown by the files it missed in Bruce's recent run. I fixed it so that it will actually adjust the case when needed. In passing, also make it skip .po files, since those will just get overwritten anyway from the translation repository. http://git.postgresql.org/pg/commitdiff/de7c8dbea1a17a0e1709c4b12371615d28e21c13
  • Adjust back-branch release note description of commits a2a718b22 et al. As pointed out by Michael Paquier, recovery_min_apply_delay didn't exist in 9.0-9.3, making the release note text not very useful. Instead make it talk about recovery_target_xid, which did exist then. 9.0 is already out of support, but we can fix the text in the newer branches' copies of its release notes. http://git.postgresql.org/pg/commitdiff/df35af2ca7b5545d32b978a88b665bac2b9fa638
  • Fix overly-strict assertions in spgtextproc.c. spg_text_inner_consistent is capable of reconstructing an empty string to pass down to the next index level; this happens if we have an empty string coming in, no prefix, and a dummy node label. (In practice, what is needed to trigger that is insertion of a whole bunch of empty-string values.) Then, we will arrive at the next level with in->level == 0 and a non-NULL (but zero length) in->reconstructedValue, which is valid but the Assert tests weren't expecting it. Per report from Andreas Seltenreich. This has no impact in non-Assert builds, so should not be a problem in production, but back-patch to all affected branches anyway. In passing, remove a couple of useless variable initializations and shorten the code by not duplicating DatumGetPointer() calls. http://git.postgresql.org/pg/commitdiff/7157fe80f42476db249e062b4f6eef6a3994b234
  • Teach pg_dump to quote reloption values safely. Commit c7e27becd2e6eb93 fixed this on the backend side, but we neglected the fact that several code paths in pg_dump were printing reloptions values that had not gotten massaged by ruleutils. Apply essentially the same quoting logic in those places, too. http://git.postgresql.org/pg/commitdiff/b416c0bb622ce5d33fdbec3bbce00451132f10ec
  • Fix treatment of *lpNumberOfBytesRecvd == 0: that's a completion condition. pgwin32_recv() has treated a non-error return of zero bytes from WSARecv() as being a reason to block ever since the current implementation was introduced in commit a4c40f140d23cefb. However, so far as one can tell from Microsoft's documentation, that is just wrong: what it means is graceful connection closure (in stream protocols) or receipt of a zero-length message (in message protocols), and neither case should result in blocking here. The only reason the code worked at all was that control then fell into the retry loop, which did *not* treat zero bytes specially, so we'd get out after only wasting some cycles. But as of 9.5 we do not normally reach the retry loop and so the bug is exposed, as reported by Shay Rojansky and diagnosed by Andres Freund. Remove the unnecessary test on the byte count, and rearrange the code in the retry loop so that it looks identical to the initial sequence. Back-patch to 9.5. The code is wrong all the way back, AFAICS, but since it's relatively harmless in earlier branches we'll leave it alone. http://git.postgresql.org/pg/commitdiff/90e61df8130dc7051a108ada1219fb0680cb3eb6
  • Do a final round of copy-editing on the 9.5 release notes. http://git.postgresql.org/pg/commitdiff/027989197aab9e555328721b003ebd1839a16704
  • Do some copy-editing on the docs for replication origins. Minor grammar and markup improvements. http://git.postgresql.org/pg/commitdiff/c6aeba353a15d71f584488a7482fb88337f843e3
  • Guard against null arguments in binary_upgrade_create_empty_extension(). The CHECK_IS_BINARY_UPGRADE macro is not sufficient security protection if we're going to dereference pass-by-reference arguments before it. But in any case we really need to explicitly check PG_ARGISNULL for all the arguments of a non-strict function, not only the ones we expect null values for. Oversight in commits 30982be4e5019684e1772dd9170aaa53f5a8e894 and f92fc4c95ddcc25978354a8248d3df22269201bc. Found by Andreas Seltenreich. (The other usages in pg_upgrade_support.c seem safe.) http://git.postgresql.org/pg/commitdiff/939d10cd8711fdeb7f0ff62c9c6b08e3eddbba3e

Joe Conway pushed:

  • Rename (new|old)estCommitTs to (new|old)estCommitTsXid. The variables newestCommitTs and oldestCommitTs sound as if they are timestamps, but in fact they are the transaction Ids that correspond to the newest and oldest timestamps rather than the actual timestamps. Rename these variables to reflect that they are actually xids: to wit newestCommitTsXid and oldestCommitTsXid respectively. Also modify related code in a similar fashion, particularly the user facing output emitted by pg_controldata and pg_resetxlog. Complaint and patch by me, review by Tom Lane and Alvaro Herrera. Backpatch to 9.5 where these variables were first introduced. http://git.postgresql.org/pg/commitdiff/241448b23adf3432988f2b4012ff90a338b4d0bf

Peter Eisentraut pushed:

Noah Misch pushed:

  • Fix comments about WAL rule "write xlog before data" versus pg_multixact. Recovery does not achieve its goal of zeroing all pg_multixact entries whose accompanying WAL records never reached disk. Remove that claim and justify its expendability. Detail the need for TrimMultiXact(), which has little in common with the TrimCLOG() rationale. Merge two tightly-related comments. Stop presenting pg_multixact as specific to heap_lock_tuple(); PostgreSQL 9.3 extended its use to heap_update(). Noticed while investigating a report from Andres Freund. http://git.postgresql.org/pg/commitdiff/3cd1ba147e5619199914e5b71e0edbd188a763d2
  • Cover heap_page_prune_opt()'s cleanup lock tactic in README. Jeff Janes, reviewed by Jim Nasby. http://git.postgresql.org/pg/commitdiff/dfcd9cb30237f882b7308bdcbfb0318b22b1e224

Bruce Momjian pushed:

Correctifs rejetés (à ce jour)

No one was disappointed this week :-)

Correctifs en attente

Peter Geoghegan sent in another revision of a patch to use quicksort when performing external sorts.

Jeff Janes sent in another revision of a patch to add a gin_clean_pending_list() function.

David Rowley sent in two more revisions of a patch to fix some typos.

Grzegorz Sampolski sent in a patch to add rhost as an option for PAM auth.

Haribabu Kommi and Alexander Shulgin traded patches to add a pg_hba_lookup() function.

Dmitry Ivanov sent in a patch to add an array_elemtype() function.

Aleksander Alekseev sent in three more revisions of a patch to fix lock contention for HASHHDR.mutex.

Andreas Karlsson sent in a patch to implement tab completion in psql for COPY with a query.

Joe Conway sent in another revision of a patch to expose pg_controldata and pg_config as functions.

Haribabu Kommi sent in another revision of a patch to implement multi-tenancy with RLS.

Thomas Munro sent in another revision of a patch to implement causal reads.

Tom Lane sent in a patch to better detail logging for password auth failures.

Michael Paquier sent in three revisions of a patch to add support for a case-sensitive comparison facility to psql's tab completion.

Bruce Momjian sent in a patch to ensure that the correct .h files are installed on Windows.

Amit Kapila sent in two more revisions of a patch to refactor LWLock tranches.

Andreas Karlsson sent in a patch to improve psql's tab completion for FDW DDL.

Stas Kelvich sent in another revision of a patch to add Tsvector editing functions.

Pavel Stěhule sent in another revision of a patch to add a custom function for converting human-readable sizes to bytes.

Ãlvaro Herrera sent in another revision of a patch to implement column stores.

Corey Huinker sent in another revision of a patch to allow FETCH to be limited by a specification measured in bytes rather than rows.

Tom Lane sent in a patch to improve the rows estimate for BRIN indexes.

Pavel Stěhule sent in another revision of a patch to make a PL/PythonU version of ereport().

Dilip Kumar sent in another revision of a patch to scale relation extensibility.

David Steele sent in a patch to add a pg_audit extension.

Tomas Vondra sent in another revision of a patch to postpone building buckets to the end of Hash (in HashJoin).

Tomas Vondra sent in another revision of a patch to add a Bloom filter in Hash Joins with batches.

Tomas Vondra sent in a patch to extend the hyperloglog API by adding initHyperLogLogError() and freeHyperLogLog().

Petr Jelínek sent in a patch to add pglogical, a logical replication contrib module.

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

Petr Jelínek sent in a patch to fix a copy-paste error in the logical decoding docs.

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

Simon Riggs sent in a WIP patch to implement failover slots.

Michael Paquier sent in another revision of a patch to fix how pg_dump locks tables.

Jim Nasby sent in two revisions of a patch to improve error reporting in format().

Amit Kapila and Andres Freund traded patches to fix an issue with backend processes not terminating properly.

SAWADA Masahiko sent in another revision of a patch to support N synchronous standby servers for N>1.

Simon Riggs sent in another revision of a patch to avoid standby pin scans.

Andreas Karlsson sent in two approaches to a patch to fix some infelicities between psql's \x auto feature and EXPLAIN.

Pavel Stěhule sent in another revision of a patch to implement a num_nulls() function.