Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en février

PostgreSQL Local

  • Le PGDay NYC aura lieu le 2 avril 2012 au Lighthouse International à New-York : http://pgday.nycpug.org
  • La PGCon 2012 sera tenue à l'Université d'Ottawa, les 17 et 18 mai 2012. Elle sera précédée par deux jours de tutoriels les 15 & 16 mai 2012 : http://www.pgcon.org/2012/
  • Le PGDay France aura lieu à Lyon, le 7 juin 2012 : http://www.pgday.fr

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)

Revues de code

Correctifs appliqués

Alvaro Herrera a poussé :

Robert Haas a poussé :

Tom Lane a poussé :

  • Add locking around WAL-replay modification of shared-memory variables. Originally, most of this code assumed that no Postgres backends could be running concurrently with it, and so no locking could be needed. That assumption fails in Hot Standby. While it's still true that Hot Standby backends should never change values like nextXid, they can examine them, and consistency is important in some cases such as when computing a snapshot. Therefore, prudence requires that WAL replay code obtain the relevant locks when modifying such variables, even though it can examine them without taking a lock. We were following that coding rule in some places but not all. This commit applies the coding rule uniformly to all updates of ShmemVariableCache and MultiXactState fields; a search of the replay routines did not find any other cases that seemed to be at risk. In addition, this commit fixes a longstanding thinko in replay of NEXTOID and checkpoint records: we tried to advance nextOid only if it was behind the value in the WAL record, but the comparison would draw the wrong conclusion if OID wraparound had occurred since the previous value. Better to just unconditionally assign the new value, since OID assignment shouldn't be happening during replay anyway. The additional locking seems to be more in the nature of future-proofing than fixing any live bug, so I am not going to back-patch it. The NEXTOID fix will be back-patched separately. http://git.postgresql.org/pg/commitdiff/c6d76d7c82ebebb7210029f7382c0ebe2c558bca
  • Avoid problems with OID wraparound during WAL replay. Fix a longstanding thinko in replay of NEXTOID and checkpoint records: we tried to advance nextOid only if it was behind the value in the WAL record, but the comparison would draw the wrong conclusion if OID wraparound had occurred since the previous value. Better to just unconditionally assign the new value, since OID assignment shouldn't be happening during replay anyway. The consequences of a failure to update nextOid would be pretty minimal, since we have long had the code set up to obtain another OID and try again if the generated value is already in use. But in the worst case there could be significant performance glitches while such loops iterate through many already-used OIDs before finding a free one. The odds of a wraparound happening during WAL replay would be small in a crash-recovery scenario, and the length of any ensuing OID-assignment stall quite limited anyway. But neither of these statements hold true for a replication slave that follows a WAL stream for a long period; its behavior upon going live could be almost unboundedly bad. Hence it seems worth back-patching this fix into all supported branches. Already fixed in HEAD in commit c6d76d7c82ebebb7210029f7382c0ebe2c558bca. http://git.postgresql.org/pg/commitdiff/f1b8a84dec30b44e6a0b306f95961f5426cb8368
  • Avoid throwing ERROR during WAL replay of DROP TABLESPACE. Although we will not even issue an XLOG_TBLSPC_DROP WAL record unless removal of the tablespace's directories succeeds, that does not guarantee that the same operation will succeed during WAL replay. Foreseeable reasons for it to fail include temp files created in the tablespace by Hot Standby backends, wrong directory permissions on a standby server, etc etc. The original coding threw ERROR if replay failed to remove the directories, but that is a serious overreaction. Throwing an error aborts recovery, and worse means that manual intervention will be needed to get the database to start again, since otherwise the same error will recur on subsequent attempts to replay the same WAL record. And the consequence of failing to remove the directories is only that some probably-small amount of disk space is wasted, so it hardly seems justified to throw an error. Accordingly, arrange to report such failures as LOG messages and keep going when a failure occurs during replay. Back-patch to 9.0 where Hot Standby was introduced. In principle such problems can occur in earlier releases, but Hot Standby increases the odds of trouble significantly. Given the lack of field reports of such issues, I'm satisfied with patching back as far as the patch applies easily. http://git.postgresql.org/pg/commitdiff/5fc78efcec01fd5e857278556ad4312ae94ecc58
  • Fix postmaster to attempt restart after a hot-standby crash. The postmaster was coded to treat any unexpected exit of the startup process (i.e., the WAL replay process) as a catastrophic crash, and not try to restart it. This was OK so long as the startup process could not have any sibling postmaster children. However, if a hot-standby backend crashes, we SIGQUIT the startup process along with everything else, and the resulting exit is hardly "unexpected". Treating it as such meant we failed to restart a standby server after any child crash at all, not only a crash of the WAL replay process as intended. Adjust that. Back-patch to 9.0 where hot standby was introduced. http://git.postgresql.org/pg/commitdiff/442231d7f71764b8c628044e7ce2225f9aa43b67
  • Mark some more I/O-conversion-invoking functions as stable not volatile. When written, textanycat, anytextcat, quote_literal, and quote_nullable were marked volatile, because they could invoke arbitrary type-specific output functions as part of casting their anyelement arguments to text. Since then, we have defined a project policy that I/O functions must not be volatile, as per commit aab353a60b95aadc00f81da0c6d99bde696c4b75. So these functions can safely be downgraded to stable. Most of the time this makes no difference since they'll get inlined anyway, but as noted by Andrew Dunstan, there are cases where the volatile marking prevents optimizations that the planner does before function inlining. (I think I might have overlooked these functions in the earlier commit on the grounds that inlining would make it moot, but not so --- tgl) This change results in a change in the expected output of the json regression tests, because the planner can now flatten a sub-select that it failed to before. The old output is preferable, but getting that back will require some as-yet-unfinished work on RowExpr handling. Marti Raudsepp http://git.postgresql.org/pg/commitdiff/3db6524fe63f0598dcb2b307bb422bc126f2b15d
  • Support min/max index optimizations on boolean columns. Since bool_and() is equivalent to min(), and bool_or() to max(), we might as well let them be index-optimized in the same way. The practical value of this is debatable at best, but it seems nearly cost-free to enable it. Code-wise, we need only adjust the entries in pg_aggregate. There is a measurable planning speed penalty for a query involving one of these aggregates, but it is only a few percent in simple cases, so that seems acceptable. Marti Raudsepp, reviewed by Abhijit Menon-Sen http://git.postgresql.org/pg/commitdiff/cbba55d6d792b55f6b448a31fc14aef84510967c
  • Check misplaced window functions before checking aggregate/group by sanity. If somebody puts a window function in WHERE, we should complain about that in so many words. The previous coding tended to complain about the window function's arguments instead, which is likely to be misleading to users who are unclear on the semantics of window functions; as seen for example in bug #6440 from Matyas Novak. Just another example of how "add new code at the end" is frequently a bad heuristic. http://git.postgresql.org/pg/commitdiff/cb7c84fae8a6780d836687aa2c9655eb936ebd25
  • Fix up dumping conditions for extension configuration tables. Various filters that were meant to prevent dumping of table data were not being applied to extension config tables, notably --exclude-table-data and --no-unlogged-table-data. We also would bogusly try to dump data from views, sequences, or foreign tables, should an extension try to claim they were config tables. Fix all that, and refactor/redocument to try to make this a bit less fragile. This reverts the implementation, though not the feature, of commit 7b070e896ca835318c90b02c830a5c4844413b64, which had broken config-table dumping altogether :-(. It is still the case that the code will dump config-table data even if --schema is specified. That behavior was intentional, as per the comments in getExtensionMembership, so I think it requires some more discussion before we change it. http://git.postgresql.org/pg/commitdiff/d77354eaec53ed469a6f2444813ff3a4fd9d7a48
  • Throw error sooner for unlogged GiST indexes. Throwing an error only after we've built the main index fork is pretty unfriendly when the table already contains data. Per gripe from Jay Levitt. http://git.postgresql.org/pg/commitdiff/331bf6712c71a1c110bc52423eede8b4bac221a1
  • Add ORDER BY to a query to prevent occasional regression test failures. Per buildfarm, we sometimes get row-ordering variations in the output. This also makes this query look more like numerous other ones in the same test file. http://git.postgresql.org/pg/commitdiff/d06e2d200562837afa18058937f20460a3ea526a
  • Fix pg_dump for better handling of inherited columns. Revise pg_dump's handling of inherited columns, which was last looked at seriously in 2001, to eliminate several misbehaviors associated with inherited default expressions and NOT NULL flags. In particular make sure that a column is printed in a child table's CREATE TABLE command if and only if it has attislocal = true; the former behavior would sometimes cause a column to become marked attislocal when it was not so marked in the source database. Also, stop relying on textual comparison of default expressions to decide if they're inherited; instead, don't use default-expression inheritance at all, but just install the default explicitly at each level of the hierarchy. This fixes the search-path-related misbehavior recently exhibited by Chester Young, and also removes some dubious assumptions about the order in which ALTER TABLE SET DEFAULT commands would be executed. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/00bc96bd2b6646c73a073aa91dc68ed4718cf5f3
  • Fix brain fade in previous pg_dump patch. In pre-7.3 databases, pg_attribute.attislocal doesn't exist. The easiest way to make sure the new inheritance logic behaves sanely is to assume it's TRUE, not FALSE. This will result in printing child columns even when they're not really needed. We could work harder at trying to reconstruct a value for attislocal, but there is little evidence that anyone still cares about dumping from such old versions, so just do the minimum necessary to have a valid dump. I had this correct in the original draft of the patch, but for some unaccountable reason decided it wasn't necessary to change the value. Testing against an old server shows otherwise... http://git.postgresql.org/pg/commitdiff/97dc3c8a147c01da38570e4be7b4979af918dca2
  • Fix oversight in pg_dump's handling of extension configuration tables. If an extension has not been selected to be dumped (perhaps because of a --schema or --table switch), the contents of its configuration tables surely should not get dumped either. Per gripe from Hubert Depesz Lubaczewski. http://git.postgresql.org/pg/commitdiff/59de132f9a578ae5d2909228484a61309df986e0
  • Fix I/O-conversion-related memory leaks in plpgsql. Datatype I/O functions are allowed to leak memory in CurrentMemoryContext, since they are generally called in short-lived contexts. However, plpgsql calls such functions for purposes of type conversion, and was calling them in its procedure context. Therefore, any leaked memory would not be recovered until the end of the plpgsql function. If such a conversion was done within a loop, quite a bit of memory could get consumed. Fix by calling such functions in the transient "eval_econtext", and adjust other logic to match. Back-patch to all supported versions. Andres Freund, Jan Urbański, Tom Lane http://git.postgresql.org/pg/commitdiff/58a9596ed4a509467e1781b433ff9c65a4e5b5ce

Michael Meskes a poussé :

Heikki Linnakangas a poussé :

Peter Eisentraut a poussé :

Bruce Momjian a poussé :

Magnus Hagander a poussé :

Correctifs rejetés (à ce jour)

  • Pas de déception cette semaine :-)

Correctifs en attente

  • Marco Nenciarini sent in another revision of the patch to allow the elements of arrays to be enforced as foreign keys.
  • Fujii Masao sent in two revisions of a patch to fix incorrect handling of the timeout in pg_receivexlog.
  • Marko Kreen sent in another revision of the patch to create a new tuple storage in libpq and use same to make dblink more efficient in some cases.
  • Chetan Suttraway sent in another revision of the patch to implement SPI_gettypemod().
  • Fujii Masao sent in a patch to fix an issue where pg_basebackup -x stream from the standby gets stuck.
  • Chetan Suttraway sent in a patch to prevent the specification of conflicting transaction read/write options.
  • Euler Taveira de Oliveira sent in another revision of the patch to do xlog location arithmetic.
  • Shigeru HANADA sent in two more revisions of the patch to add a PostgreSQL FDW.
  • Marti Raudsepp sent in another revision of a patch to remove an optimization barrier involving the volatility of text-any concatenation.
  • Alvaro Herrera and Alex Hunsaker traded patches to fix an issue with missing keywords in make.
  • Peter Eisentraut sent in a patch to fix some of the missing things in psql's SELECT tab completion.
  • Marti Raudsepp sent in a patch to make TRUNCATE more MVCC-safe.
  • Peter Geoghegan sent in another revision of the patch for fast-path ordering, b-tree index creation time.
  • Alex Hunsaker sent in a patch to fix a bug in PL/Perl in databases encoded as SQL_ASCII.
  • Shigeru HANADA sent in another revision of the patch to collect statistics on CSV files attached via FDW.
  • Jean-Baptiste Quenot sent in a patch to fix an issue with PL/Python's handling of result metadata.
  • Kevin Grittner sent in a patch to ensure that if a GUC has a check function, it is run on a RESET just like it is on a SET, to make sure that the resulting value is valid to set within the context.
  • Andrew Dunstan sent in two revisions of a patch to fix a case where auto_explain can produce invalid JSON.
  • Jeff Janes sent in a WIP patch to to set XLP_FIRST_IS_CONTRECORD, this being part of the continuing effort to move more work outside WALInsertLock.
  • Vik Reykja sent in a patch to optimize referential integrity checks.