Les nouveautés des produits dérivés

Fonctionnalité 9.3 de la semaine

L'astuce de la semaine

Offres d'emplois autour de PostgreSQL en novembre

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.

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.

(lien vers l'article original)

Correctifs appliqués

Heikki Linnakangas a poussé :

  • Count locked pages that don't need vacuuming as scanned. Previously, if VACUUM skipped vacuuming a page because it's pinned, it didn't count that page as scanned. However, that meant that relfrozenxid was not bumped up either, which prevented anti-wraparound vacuum from doing its job. Report by Миша Тюрин, analysis and patch by Sergey Burladyn and Jeff Janes. Backpatch to 9.2, where the skip-locked-pages behavior was introduced. http://git.postgresql.org/pg/commitdiff/4c697d8f4845823a8af67788b219ffa4516ad14c
  • Replace appendPQExpBuffer(..., <constant>) with appendPQExpBufferStr. Arguably makes the code a bit more readable, and might give a small performance gain. David Rowley http://git.postgresql.org/pg/commitdiff/32ceba3ea730b6b1bd3eca786f72d61945ad42b7
  • Spell SQL keywords in uppercase in pg_dump's query. The server won't care, but let's be consistent. David Rowley. http://git.postgresql.org/pg/commitdiff/fea437681d68a0027091c2d534c69c0965cf06f1
  • Further GIN refactoring. Merge some functions that were always called together. Makes the code little bit more readable. http://git.postgresql.org/pg/commitdiff/04965ad40e10677ceec94d871a183e73023b238f
  • Refactor the internal GIN B-tree interface for forming a downlink. This creates a new gin-btree callback function for creating a downlink for a page. Previously, ginxlog.c duplicated the logic used during normal operation. http://git.postgresql.org/pg/commitdiff/501012631e7d2e1c22f60b11ff8cb62ae021625e
  • More GIN refactoring. Split off the portion of ginInsertValue that inserts the tuple to current level into a separate function, ginPlaceToPage. ginInsertValue's charter is now to recurse up the tree to insert the downlink, when a page split is required. This is in preparation for a patch to change the way incomplete splits are handled, which will need to do these operations separately. And IMHO makes the code more readable anyway. http://git.postgresql.org/pg/commitdiff/04eee1fa9ee80dabf7cf4b8b9106897272e9b291
  • Avoid acquiring spinlock when checking if recovery has finished, for speed. RecoveryIsInProgress() can be called very frequently. During normal operation, it just checks a backend-local variable and returns quickly, but during hot standby, it checks a spinlock-protected shared variable. Those spinlock acquisitions can become a point of contention on a busy hot standby system. Replace the spinlock acquisition with a memory barrier. Per discussion with Andres Freund, Ants Aasma and Merlin Moncure. http://git.postgresql.org/pg/commitdiff/1a3d104475ce01326fc00601ed66ac4d658e37e5
  • Fix Hot-Standby initialization of clog and subtrans. These bugs can cause data loss on standbys started with hot_standby=on at the moment they start to accept read only queries, by marking committed transactions as uncommited. The likelihood of such corruptions is small unless the primary has a high transaction rate. 5a031a5556ff83b8a9646892715d7fef415b83c3 fixed bugs in HS's startup logic by maintaining less state until at least STANDBY_SNAPSHOT_PENDING state was reached, missing the fact that both clog and subtrans are written to before that. This only failed to fail in common cases because the usage of ExtendCLOG in procarray.c was superflous since clog extensions are actually WAL logged. f44eedc3f0f347a856eea8590730769125964597/I then tried to fix the missing extensions of pg_subtrans due to the former commit's changes - which are not WAL logged - by performing the extensions when switching to a state > STANDBY_INITIALIZED and not performing xid assignments before that - again missing the fact that ExtendCLOG is unneccessary - but screwed up twice: Once because latestObservedXid wasn't updated anymore in that state due to the earlier commit and once by having an off-by-one error in the loop performing extensions. This means that whenever a CLOG_XACTS_PER_PAGE (32768 with default settings) boundary was crossed between the start of the checkpoint recovery started from and the first xl_running_xact record old transactions commit bits in pg_clog could be overwritten if they started and committed in that window. Fix this mess by not performing ExtendCLOG() in HS at all anymore since it's unneeded and evidently dangerous and by performing subtrans extensions even before reaching STANDBY_SNAPSHOT_PENDING. Analysis and patch by Andres Freund. Reported by Christophe Pettus. Backpatch down to 9.0, like the previous commit that caused this. http://git.postgresql.org/pg/commitdiff/98f58a30c1beb6ec0870d6520f49fb40d9d0b566

Robert Haas a poussé :

Bruce Momjian a poussé :

Peter Eisentraut a poussé :

Fujii Masao a poussé :

  • Add tab completion for \pset in psql. Pavel Stehule, reviewed by Ian Lawrence Barwick http://git.postgresql.org/pg/commitdiff/b1543cc8a84bc3ca699497286608274c2533fd53
  • Fix pg_isready to handle -d option properly. Previously, -d option for pg_isready was broken. When the name of the database was specified by -d option, pg_isready failed with an error. When the conninfo specified by -d option contained the setting of the host name but not Numeric IP address (i.e., hostaddr), pg_isready displayed wrong connection message. -d option could not handle a valid URI prefix at all. This commit fixes these bugs of pg_isready. Backpatch to 9.3, where pg_isready was introduced. Per report from Josh Berkus and Robert Haas. Original patch by Fabrízio de Royes Mello, heavily modified by me. http://git.postgresql.org/pg/commitdiff/38f432898131270e5b64245786cb67f322538bae

Tom Lane a poussé :

  • Support multi-argument UNNEST(), and TABLE() syntax for multiple functions. This patch adds the ability to write TABLE( function1(), function2(), ...) as a single FROM-clause entry. The result is the concatenation of the first row from each function, followed by the second row from each function, etc; with NULLs inserted if any function produces fewer rows than others. This is believed to be a much more useful behavior than what Postgres currently does with multiple SRFs in a SELECT list. This syntax also provides a reasonable way to combine use of column definition lists with WITH ORDINALITY: put the column definition list inside TABLE(), where it's clear that it doesn't control the ordinality column as well. Also implement SQL-compliant multiple-argument UNNEST(), by turning UNNEST(a,b,c) into TABLE(unnest(a), unnest(b), unnest(c)). The SQL standard specifies TABLE() with only a single function, not multiple functions, and it seems to require an implicit UNNEST() which is not what this patch does. There may be something wrong with that reading of the spec, though, because if it's right then the spec's TABLE() is just a pointless alternative spelling of UNNEST(). After further review of that, we might choose to adopt a different syntax for what this patch does, but in any case this functionality seems clearly worthwhile. Andrew Gierth, reviewed by Zoltán Böszörményi and Heikki Linnakangas, and significantly revised by me http://git.postgresql.org/pg/commitdiff/784e762e886e6f72f548da86a27cd2ead87dbd1c
  • Fix quoting in help messages in uuid-ossp extension scripts. The command we're telling people to type needs to include double-quoting around the unfortunately-chosen extension name. Twiddle the textual quoting so that it looks somewhat sane. Per gripe from roadrunner6. http://git.postgresql.org/pg/commitdiff/f29baf92257ae614a2097b4e86b00e8a62802e2e
  • Flatten join alias Vars before pulling up targetlist items from a subquery. pullup_replace_vars()'s decisions about whether a pulled-up replacement expression needs to be wrapped in a PlaceHolderVar depend on the assumption that what looks like a Var behaves like a Var. However, if the Var is a join alias reference, later flattening of join aliases might replace the Var with something that's not a Var at all, and should have been wrapped. To fix, do a forcible pass of flatten_join_alias_vars() on the subquery targetlist before we start to copy items out of it. We'll re-run that processing on the pulled-up expressions later, but that's harmless. Per report from Ken Tanzer; the added regression test case is based on his example. This bug has been there since the PlaceHolderVar mechanism was invented, but has escaped detection because the circumstances that trigger it are fairly narrow. You need a flattenable query underneath an outer join, which contains another flattenable query inside a join of its own, with a dangerous expression (a constant or something else non-strict) in that one's targetlist. Having seen this, I'm wondering if it wouldn't be prudent to do all alias-variable flattening earlier, perhaps even in the rewriter. But that would probably not be a back-patchable change. http://git.postgresql.org/pg/commitdiff/f19e92ed040c2afba2333f0ce547848f4dc4ec21
  • Ensure _dosmaperr() actually sets errno correctly. If logging is enabled, either ereport() or fprintf() might stomp on errno internally, causing this function to return the wrong result. That might only end in a misleading error report, but in any code that's examining errno to decide what to do next, the consequences could be far graver. This has been broken since the very first version of this file in 2006 ... it's a bit astonishing that we didn't identify this long ago. Reported by Amit Kapila, though this isn't his proposed fix. http://git.postgresql.org/pg/commitdiff/f145454d57bc9dfd105f7236a03a00dd25395dd2
  • Fix array slicing of int2vector and oidvector values. The previous coding labeled expressions such as pg_index.indkey[1:3] as being of int2vector type; which is not right because the subscript bounds of such a result don't, in general, satisfy the restrictions of int2vector. To fix, implicitly promote the result of slicing int2vector to int2[], or oidvector to oid[]. This is similar to what we've done with domains over arrays, which is a good analogy because these types are very much like restricted domains of the corresponding regular-array types. A side-effect is that we now also forbid array-element updates on such columns, eg while "update pg_index set indkey[4] = 42" would have worked before if you were superuser (and corrupted your catalogs irretrievably, no doubt) it's now disallowed. This seems like a good thing since, again, some choices of subscripting would've led to results not satisfying the restrictions of int2vector. The case of an array-slice update was rejected before, though with a different error message than you get now. We could make these cases work in future if we added a cast from int2[] to int2vector (with a cast function checking the subscript restrictions) but it seems unlikely that there's any value in that. Per report from Ronan Dunklau. Back-patch to all supported branches because of the crash risks involved. http://git.postgresql.org/pg/commitdiff/45e02e3232ac7cc5ffe36f7986159b5e0b1f6fdc
  • Defend against bad trigger definitions in contrib/lo's lo_manage() trigger. This function formerly crashed if called as a statement-level trigger, or if a column-name argument wasn't given. In passing, add the trigger name to all error messages from the function. (None of them are expected cases, so this shouldn't pose any compatibility risk.) Marc Cousin, reviewed by Sawada Masahiko http://git.postgresql.org/pg/commitdiff/64d15e424091d8fd2c882356ee4bf07cfc5ff69d
  • Fix new and latent bugs with errno handling in secure_read/secure_write. These functions must be careful that they return the intended value of errno to their callers. There were several scenarios where this might not happen: 1. The recent SSL renegotiation patch added a hunk of code that would execute after setting errno. In the first place, it's doubtful that we should consider renegotiation to be successfully completed after a failure, and in the second, there's no real guarantee that the called OpenSSL routines wouldn't clobber errno. Fix by not executing that hunk except during success exit. 2. errno was left in an unknown state in case of an unrecognized return code from SSL_get_error(). While this is a "can't happen" case, it seems like a good idea to be sure we know what would happen, so reset errno to ECONNRESET in such cases. (The corresponding code in libpq's fe-secure.c already did this.) 3. There was an (undocumented) assumption that client_read_ended() wouldn't change errno. While true in the current state of the code, this seems less than future-proof. Add explicit saving/restoring of errno to make sure that changes in the called functions won't break things. I see no need to back-patch, since #1 is new code and the other two issues are mostly hypothetical. Per discussion with Amit Kapila. http://git.postgresql.org/pg/commitdiff/36a3be6540b90c6a5d307c0ed9de2076ce5a821c

Michael Meskes a poussé :

Jeff Davis a poussé :

  • Lessen library-loading log level. Previously, messages were emitted at the LOG level every time a backend preloaded a library. That was acceptable (though unnecessary) for shared_preload_libraries; but it was excessive for local_preload_libraries and session_preload_libraries. Reduce to DEBUG1. Also, there was logic in the EXEC_BACKEND case to avoid repeated messages for shared_preload_libraries by demoting them to DEBUG2. DEBUG1 seems more appropriate there, as well, so eliminate that special case. Peter Geoghegan. http://git.postgresql.org/pg/commitdiff/559d535819908adfe2d2ca674fb8562bbd133cef

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Michael Paquier sent in another revision of a patch to implement REINDEX CONCURRENTLY.
  • Fabrízio de Royes Mello sent in another revision of a patch to implement IF NOT EXISTS where appropriate.
  • Rajeev Rastogi sent in three more revisions of a patch to add a count tag to COPY FROM STDIN.
  • Mitsumasa KONDO sent in another revision of a patch to add an average time to pg_stat_statements.
  • Peter Geoghegan sent in another revision of a patch to implement autovacuum_work_mem as a separate parameter from work_mem.
  • Sergey V. Karpov sent in two more revisions of a patch to improve GIN indexing with a fast scan.
  • Haribabu Kommi sent in three more revisions of a patch to allow users to specify a different xlog directory in pg_basebackup.
  • KaiGai Kohei and Jim Mlodgenski traded patches implementing the CustomScan API, providing examples, and documenting same.
  • Sameer Thakur and Peter Geoghegan traded patches to fix some issues in pg_stat_statements where a wrong estimate could propagate in unpleasant ways.
  • Wim Dumon sent in a patch to fix building libpq standalone on Windows.
  • Andres Freund sent in another flock of patches for better atomics.
  • David Rowley sent in another revision of a patch to make CLUSTER always also FREEZE.
  • Christopher Browne sent in two more revisions of a patch to create_user which allows the caller to specify the roles of which the new user is a member.
  • Kyotaro HORIGUCHI sent in more patches to add pathkeys and uniqueness information to the Plan structure and uses same to ensure indexes are used in certain UNION queries.
  • Bruce Momjian sent in four more revisions of a patch to issue a warning when calling SET TRANSACTION outside a transaction block.
  • Heikki Linnakangas sent in another revision of a patch to implement INSERT ... ON DUPLICATE KEY LOCK FOR UPDATE.
  • Fabrízio de Royes Mello sent in a patch to enable storing extension options.
  • Peter Eisentraut sent in another revision of a patch to implement information schema parameter_default.
  • Zoltan Boszormenyi sent in a flock of patches to change the ECPG infrastructure.
  • Atri Sharma sent in another revision of a patch to implement WITHIN GROUP.
  • Craig Ringer sent in a WIP patch to implement updatable security barrier views.
  • Fabrízio de Royes Mello sent in a patch to allow psql to see disabled triggers in \d.
  • Heikki Linnakangas and Michael Paquier traded patches to fix the way GIN indexes handle incomplete splits.
  • Pavel Stehule sent in two more revisions of a patch to add new border styles for psql.
  • Andres Freund sent in another revision of a patch to preserve forensic information when freezing.
  • Amit Kapila sent in two revisions of a patch to ensure that the server starts with DEBUG5 when requested.
  • Haribabu Kommi sent in another revision of a patch to fix a race condition in autovacuum which can cause tables to bloat when autovacuum is running.
  • Alvaro Herrera sent in another revision of a patch to fix an issue where MultiXact was pessimized in 9.3.
  • Gurjeet Singh sent in another revision of a patch to optimize the startup sequence.
  • Kevin Grittner sent in three more revisions of a patch to fix a mismatch in pg_dump with transaction isolation level.
  • Rodolfo Campero sent in two more revisions of a patch to enable domain over array support in PL/PythonU.
  • Tom Lane sent in another revision of a patch to fix an issue where PostgreSQL won't start on Windows with certain parameters of excessive length.
  • Peter Eisentraut sent in another revision of a patch to document the way external programs' exit codes are handled during recovery.
  • J Smith sent in a patch to enable Google coredumper.