PostgreSQL La base de donnees la plus sophistiquee au monde.

La planète francophone de PostgreSQL

lundi 21 juillet 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 20 juillet 2014

Le début de pgMKE est fixé au 5 août 2014 à Milwaukee, Wisconsin, États-Unis. RSVP pour aider à viser la bonne quantité de pizza : https://plus.google.com/events/c7iba3eh9366v3hki7etcbj2074

Offres d'emplois autour de PostgreSQL en juillet

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

Fujii Masao a poussé :

Noah Misch a poussé :

Peter Eisentraut a poussé :

Magnus Hagander a poussé :

Alvaro Herrera a poussé :

Andrew Dunstan a poussé :

Tom Lane a poussé :

  • Allow join removal in some cases involving a left join to a subquery. We can remove a left join to a relation if the relation's output is provably distinct for the columns involved in the join clause (considering only equijoin clauses) and the relation supplies no variables needed above the join. Previously, the join removal logic could only prove distinctness by reference to unique indexes of a table. This patch extends the logic to consider subquery relations, wherein distinctness might be proven by reference to GROUP BY, DISTINCT, etc. We actually already had some code to check that a subquery's output was provably distinct, but it was hidden inside pathnode.c; which was a pretty bad place for it really, since that file is mostly boilerplate Path construction and comparison. Move that code to analyzejoins.c, which is arguably a more appropriate location, and is certainly the site of the new usage for it. David Rowley, reviewed by Simon Riggs http://git.postgresql.org/pg/commitdiff/f15821eefd70941d4ec5bd77d75c1da5534ba6b2
  • Fix two low-probability memory leaks in regular expression parsing. If pg_regcomp failed after having invoked markst/cleanst, it would leak any "struct subre" nodes it had created. (We've already detected all regex syntax errors at that point, so the only likely causes of later failure would be query cancel or out-of-memory.) To fix, make sure freesrnode knows the difference between the pre-cleanst and post-cleanst cleanup procedures. Add some documentation of this less-than-obvious point. Also, newlacon did the wrong thing with an out-of-memory failure from realloc(), so that the previously allocated array would be leaked. Both of these are pretty low-probability scenarios, but a bug is a bug, so patch all the way back. Per bug #10976 from Arthur O'Dwyer. http://git.postgresql.org/pg/commitdiff/1567e659a877d35ab4b85dafff41b2845d50990f
  • Partial fix for dropped columns in functions returning composite. When a view has a function-returning-composite in FROM, and there are some dropped columns in the underlying composite type, ruleutils.c printed junk in the column alias list for the reconstructed FROM entry. Before 9.3, this was prevented by doing get_rte_attribute_is_dropped tests while printing the column alias list; but that solution is not currently available to us for reasons I'll explain below. Instead, check for empty-string entries in the alias list, which can only exist if that column position had been dropped at the time the view was made. (The parser fills in empty strings to preserve the invariant that the aliases correspond to physical column positions.) While this is sufficient to handle the case of columns dropped before the view was made, we have still got issues with columns dropped after the view was made. In particular, the view could contain Vars that explicitly reference such columns! The dependency machinery really ought to refuse the column drop attempt in such cases, as it would do when trying to drop a table column that's explicitly referenced in views. However, we currently neglect to store dependencies on columns of composite types, and fixing that is likely to be too big to be back-patchable (not to mention that existing views in existing databases would not have the needed pg_depend entries anyway). So I'll leave that for a separate patch. Pre-9.3, ruleutils would print such Vars normally (with their original column names) even though it suppressed their entries in the RTE's column alias list. This is certainly bogus, since the printed view definition would fail to reload, but at least it didn't crash. However, as of 9.3 the printed column alias list is tightly tied to the names printed for Vars; so we can't treat columns as dropped for one purpose and not dropped for the other. This is why we can't just put back the get_rte_attribute_is_dropped test: it results in an assertion failure if the view in fact contains any Vars referencing the dropped column. Once we've got dependencies preventing such cases, we'll probably want to do it that way instead of relying on the empty-string test used here. This fix turned up a very ancient bug in outfuncs/readfuncs, namely that T_String nodes containing empty strings were not dumped/reloaded correctly: the node was printed as "<>" which is read as a string value of <>. Since (per SQL) we disallow empty-string identifiers, such nodes don't occur normally, which is why we'd not noticed. (Such nodes aren't used for literal constants, just identifiers.) Per report from Marc Schablewski. Back-patch to 9.3 which is where the rule printing behavior changed. The dangling-variable case is broken all the way back, but that's not what his complaint is about. http://git.postgresql.org/pg/commitdiff/9b35ddce93a2ef336498baa15581b9d10f01db9c
  • Update time zone data files to tzdata release 2014e. DST law changes in Crimea, Egypt, Morocco. New zone Antarctica/Troll for Norwegian base in Queen Maud Land. http://git.postgresql.org/pg/commitdiff/b1864fabf93f23e4eaa5ec4599a143ac8f7647e5
  • Fix xreflabel for hot_standby_feedback. Rather remarkable that this has been wrong since 9.1 and nobody noticed. http://git.postgresql.org/pg/commitdiff/4cbe3abb3162d14a86bc8ec73fd9331638ec18a0
  • First-draft release notes for 9.3.5. As usual, the release notes for older branches will be made by cutting these down, but put them up for community review first. Note: a few of these items actually don't apply to 9.3, but only to older branches. I'll sort that out when copying the text into the older release-X.Y.sgml files. http://git.postgresql.org/pg/commitdiff/ddc41fd2f4756b529814ad03fa32e8ba10253996

Heikki Linnakangas a poussé :

  • Fix bugs in SP-GiST search with range type's -|- (adjacent) operator. The consistent function contained several bugs: * The "if (which2) { ... }" block was broken. It compared the * argument's lower bound against centroid's upper bound, while it was supposed to compare the argument's upper bound against the centroid's lower bound (the comment was correct, code was wrong). Also, it cleared bits in the "which1" variable, while it was supposed to clear bits in "which2". * If the argument's upper bound was equal to the centroid's lower * bound, we descended to both halves (= all quadrants). That's unnecessary, searching the right quadrants is sufficient. This didn't lead to incorrect query results, but was clearly wrong, and slowed down queries unnecessarily. * In the case that argument's lower bound is adjacent to the * centroid's upper bound, we also don't need to visit all quadrants. Per similar reasoning as previous point. * The code where we compare the previous centroid with the current * centroid should match the code where we compare the current centroid with the argument. The point of that code is to redo the calculation done in the previous level, to see if we were supposed to traverse left or right (or up or down), and if we actually did. If we moved in the different direction, then we know there are no matches for bound. Refactor the code and adds comments to make it more readable and easier to reason about. Backpatch to 9.3 where SP-GiST support for range types was introduced. http://git.postgresql.org/pg/commitdiff/1264ef31a399927f9fe02212e10980bf4ba7b9f2

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Christoph Moench-Tegeder sent in a patch to correct an example in logical decoding.
  • Steve Singer sent in a patch to fix a bug in walsender keepalives.
  • Etsuro Fujita sent in a patch to remove some redundant words from a comment in src/backend/commands/explain.c.
  • Fabrízio de Royes Mello sent in four more revisions of a patch to implement ALTER TABLE ... SET LOGGED.
  • Shigeru HANADA and Kaigai Kouhei traded patches to implement the custom plan API.
  • Amit Kapila sent in a patch to fix an issue with pg_basebackup on Windows with symlinks.
  • Fujii Masao sent in a patch to fix an issue with pg_receivexlog --status-interval. It checked for positive numbers, but should have checked for nonzero ones.
  • Dilip Kumar sent in a patch to ensure that \pset correctly shows both arguments are optional, not just the second.
  • David Rowley sent in another revision of a patch to allow NOT IN to use ANTI JOINs in certain cases.
  • Rajeev Rastogi sent in a patch to fix an issue where there was an unwanted LOG during recovery of DROP TABLESPACE.
  • Jeff Janes sent in another revision of a patch to fix issues that manifested as "Could not open file "pg_multixact/members/xxxx".
  • Dilip Kumar sent in two more revisions of a patch to enable vacuumdb to use >1 core in parallel.
  • Kyotaro HORIGUCHI sent in another revision of a patch to enable using a UNIQUE index for longer pathkeys.
  • Kyotaro HORIGUCHI sent in a patch to fix an issue where xmin and xmax for FDWs give strange results.
  • Kyotaro HORIGUCHI sent in a patch to change a couple of instances of BUFFER_LOCK_EXCLUSIVE in src/backend/access/gin/gininsert.c to GIN_EXCLUSIVE.
  • Viswanatham Kirankumar sent in a patch to process pg_hba.conf keywords as case-insensitive.
  • Fabien COELHO sent in two more revisions of a patch to allow using a Gaussian distribution in pgbench.
  • Amit Kapila sent in a patch to fix the fact that pg_basebackup -T doesn't work on Windows.
  • Michael Paquier sent in two more revisions of a patch to do better at HINTing an appropriate column within errorMissingColumn().
  • Dmitry Voronin sent in another revision of a patch to augment the sslinfo contrib extension.
  • Petr (PJMODOS) Jelinek sent in another revision of a patch to allow setting a new system identifier via pg_resetxlog.
  • Michael Paquier sent in another revision of a patch to fix some WAL replay bugs.
  • MauMau sent in a patch to suppress "autovacuum: found orphan temp table" messages.
  • Tomas Vondra sent in another revision of a patch to tweak NTUP_PER_BUCKET.
  • Tomas Vondra sent in another revision of a patch to fix an issue where bad estimation together with large work_mem generates terrible slow hash joins.
  • Fabien COELHO sent in two revisions of a patch to fix an issue where pg_stat_statements is cluttered with "DEALLOCATE dbdpg_p*".

par N Bougain le lundi 21 juillet 2014 à 23h44

dimanche 20 juillet 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 13 juillet 2014

Le 4ème PgDay équatorien aura lieu le mardi 7 octobre en la ville de Quito, intégré au 5ème congrès international du Logiciel Libre. Envoyez vos propositions de conférences à ecpug AT postgresql DOT org.

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en juillet

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

Bruce Momjian a poussé :

Tom Lane a poussé :

  • In pg_dump, show server and pg_dump versions with or without --verbose. We used to print this information only in verbose mode, but it's argued that it's useful enough to print always; one reason being that this provides some documentation about which Postgres versions the dump is meant to reload into. Jing Wang, reviewed by Jeevan Chalke http://git.postgresql.org/pg/commitdiff/7700597b34fbb26e377f419271f65be1c13c518f
  • Don't assume a subquery's output is unique if there's a SRF in its tlist. While the x output of "select x from t group by x" can be presumed unique, this does not hold for "select x, generate_series(1,10) from t group by x", because we may expand the set-returning function after the grouping step. (Perhaps that should be re-thought; but considering all the other oddities involved with SRFs in targetlists, it seems unlikely we'll change it.) Put a check in query_is_distinct_for() so it's not fooled by such cases. Back-patch to all supported branches. David Rowley http://git.postgresql.org/pg/commitdiff/9e2f2d7a0539460dc76dbf76dfae17024d5e031e
  • Implement IMPORT FOREIGN SCHEMA. This command provides an automated way to create foreign table definitions that match remote tables, thereby reducing tedium and chances for error. In this patch, we provide the necessary core-server infrastructure and implement the feature fully in the postgres_fdw foreign-data wrapper. Other wrappers will throw a "feature not supported" error until/unless they are updated. Ronan Dunklau and Michael Paquier, additional work by me http://git.postgresql.org/pg/commitdiff/59efda3e50ca4de6a9d5aa4491464e22b6329b1e
  • Fix bug with whole-row references to append subplans. ExecEvalWholeRowVar incorrectly supposed that it could "bless" the source TupleTableSlot just once per query. But if the input is coming from an Append (or, perhaps, other cases?) more than one slot might be returned over the query run. This led to "record type has not been registered" errors when a composite datum was extracted from a non-blessed slot. This bug has been there a long time; I guess it escaped notice because when dealing with subqueries the planner tends to expand whole-row Vars into RowExprs, which don't have the same problem. It is possible to trigger the problem in all active branches, though, as illustrated by the added regression test. http://git.postgresql.org/pg/commitdiff/d68581483564ec0fbfdc255e493c4f5bd165e55e

Fujii Masao a poussé :

Peter Eisentraut a poussé :

Robert Haas a poussé :

Magnus Hagander a poussé :

Andres Freund a poussé :

  • Fix decoding of consecutive MULTI_INSERTs emitted by one heap_multi_insert(). Commit 1b86c81d2d fixed the decoding of toasted columns for the rows contained in one xl_heap_multi_insert record. But that's not actually enough, because heap_multi_insert() will actually first toast all passed in rows and then emit several *_multi_insert records; one for each page it fills with tuples. Add a XLOG_HEAP_LAST_MULTI_INSERT flag which is set in xl_heap_multi_insert->flag denoting that this multi_insert record is the last emitted by one heap_multi_insert() call. Then use that flag in decode.c to only set clear_toast_afterwards in the right situation. Expand the number of rows inserted via COPY in the corresponding regression test to make sure that more than one heap page is filled with tuples by one heap_multi_insert() call. Backpatch to 9.4 like the previous commit. http://git.postgresql.org/pg/commitdiff/626bfad6cc5701eb385b8995e1431ad6a5f24928
  • Minimal psql tab completion support for SET search_path. Complete SET search_path = ... to non-temporary and non-toast schemas. Since there pretty much is no use case to add those to the search path and there can be many it's helpful to exclude them. It'd be nicer to complete multiple search path elements, but that's not easy. Jeff Janes http://git.postgresql.org/pg/commitdiff/bd409519bd19911d2d2cba64415447d2fa9d766b

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Abhijit Menon-Sen sent in another revision of a patch to introduce XLogLockBlockRangeForCleanup().
  • Abhijit Menon-Sen sent in another revision of a patch to add a --stats option to xlogdump.
  • Kaigai Kouhei sent in another revision of a patch to implement a custom plan API.
  • Etsuro Fujita sent in a WIP patch to optimize updating foreign tables in the PostgreSQL FDW.
  • David Rowley sent in two more revisions of a patch to allow removing LEFT JOINs under certain circumstances.
  • Haribabu Kommi sent in another revision of a patch to add min() and max() for the inet data type.
  • Amit Kapila sent in another revision of a patch to fix an issue with duplicate parameters in ALTER SYSTEM.
  • Dilip Kumar sent in another revision of a patch to allow parallel cores to be used by vacuumdb.
  • SAWADA Masahiko and Jeevan Chalke traded patches to add line number as prompt option to psql.
  • Alvaro Herrera sent in another revision of a patch to add minmax indexes.
  • Tomonari Katsumata sent in a patch to ensure that rounding up time value is not less than its unit.
  • Furuya Osamu sent in another revision of a patch to add a synchronous mode to pg_receivexlog.
  • SAWADA Masahiko sent in a patch to fix an issue in timeout of pg_receivexlog --status-interval.
  • Alvaro Herrera sent in a patch to fix the README in anticipation of another patch for CSN-based snapshots.
  • Kyotaro HORIGUCHI and Michael Paquier traded patches to fix an issue in WAL replay.
  • Jeff Davis sent in another revision of a patch to allow IGNORE NULLS in the lead() and lag() windowing functions.
  • Jeff Davis sent in two revisions of a patch to Allow multi-byte characters as escape in SIMILAR TO and SUBSTRING.
  • Tom Lane sent in another revision of a patch to allow NOT IN to use ANTI joins.
  • Tomas Vondra sent in two more revisions of a patch to tweak NTUP_PER_BUCKET.
  • Ali Akbar sent in another revision of a patch to fix xpath() to return namespace definitions.
  • Etsuro Fujita sent in a patch to fix an incorrect comment in postgres_fdw.c.
  • Christoph Martin sent in a patch to fix the search_path default value separator.
  • Etsuro Fujita sent in a patch to add information on exact/lossy blocks for a bitmap heap scan when both the numbers of exact/lossy pages retrieved by the node are zero.
  • Noah Misch sent in a patch to fix an OpenLDAP issue.
  • Fabrízio de Royes Mello sent in another revision of a patch to add ALTER TABLE ... SET LOGGED.
  • Christoph Berg sent in another revision of a patch to secure "make check."
  • Magnus Hagander sent in a patch to include SSL compression info in a psql header.
  • Emre Hesegeli sent in a patch to add selectivity estimation for inet operators.
  • Tomas Vondra sent in another revision of a patch to fix an issue where bad estimation together with large work_mem generates slow hash joins.

par N Bougain le dimanche 20 juillet 2014 à 21h20

Nouvelles hebdomadaires de PostgreSQL - 6 juillet 2014

Les inscriptions pour la PGConf.EU 2014 de Madrid, Espagne, du 21 au 24 octobre sont à présent ouvertes : http://2014.pgconf.eu/registration/

Le PGDay.IT 2014 aura lieu à Prato le 7 novembre 2014. L'appel international à conférenciers a été lancé : http://2014.pgday.it/call-for-papers-en/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en juillet

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é :

  • Fix and enhance the assertion of no palloc's in a critical section. The assertion failed if WAL_DEBUG or LWLOCK_STATS was enabled; fix that by using separate memory contexts for the allocations made within those code blocks. This patch introduces a mechanism for marking any memory context as allowed in a critical section. Previously ErrorContext was exempt as a special case. Instead of a blanket exception of the checkpointer process, only exempt the memory context used for the pending ops hash table. http://git.postgresql.org/pg/commitdiff/1c6821be31f91ab92547a8ed4246762c8cefb1b3

Andres Freund a poussé :

  • Fix typos in the cluster_name commit. Thom Brown and Fujii Masao http://git.postgresql.org/pg/commitdiff/6647f59fd256927aaf9e35929b5c284d10d18839
  • Check interrupts during logical decoding more frequently. When reading large amounts of preexisting WAL during logical decoding using the SQL interface we possibly could fail to check interrupts in due time. Similarly the same could happen on systems with a very high WAL volume while creating a new logical replication slot, independent of the used interface. Previously these checks where only performed in xlogreader's read_page callbacks, while waiting for new WAL to be produced. That's not sufficient though, if there's never a need to wait. Walsender's send loop already contains a interrupt check. Backpatch to 9.4 where the logical decoding feature was introduced. http://git.postgresql.org/pg/commitdiff/1cbc9480106241aaa8db112331e93d0a265b6db0
  • Rename logical decoding's pg_llog directory to pg_logical. The old name wasn't very descriptive as of actual contents of the directory, which are historical snapshots in the snapshots/ subdirectory and mappingdata for rewritten tuples in mappings/. There's been a fair amount of discussion what would be a good name. I'm settling for pg_logical because it's likely that further data around logical decoding and replication will need saving in the future. Also add the missing entry for the directory into storage.sgml's list of PGDATA contents. Bumps catversion as the data directories won't be compatible. http://git.postgresql.org/pg/commitdiff/a36a8fa376631e59d006772bcd30f4f827d7e6b9
  • Fix decoding of MULTI_INSERTs when rows other than the last are toasted. When decoding the results of a HEAP2_MULTI_INSERT (currently only generated by COPY FROM) toast columns for all but the last tuple weren't replaced by their actual contents before being handed to the output plugin. The reassembled toast datums where disregarded after every REORDER_BUFFER_CHANGE_(INSERT|UPDATE|DELETE) which is correct for plain inserts, updates, deletes, but not multi inserts - there we generate several REORDER_BUFFER_CHANGE_INSERTs for a single xl_heap_multi_insert record. To solve the problem add a clear_toast_afterwards boolean to ReorderBufferChange's union member that's used by modifications. All row changes but multi_inserts always set that to true, but multi_insert sets it only for the last change generated. Add a regression test covering decoding of multi_inserts - there was none at all before. Backpatch to 9.4 where logical decoding was introduced. Bug found by Petr Jelinek. http://git.postgresql.org/pg/commitdiff/1b86c81d2d255d3fb665ddc77c2bc3dfd751a1df

Noah Misch a poussé :

Bruce Momjian a poussé :

Tom Lane a poussé :

  • Allow empty replacement strings in contrib/unaccent. This is useful in languages where diacritic signs are represented as separate characters; it's also one step towards letting unaccent be used for arbitrary substring substitutions. In passing, improve the user documentation for unaccent, which was sadly vague about some important details. Mohammad Alhashash, reviewed by Abhijit Menon-Sen http://git.postgresql.org/pg/commitdiff/97c40ce61465582b96944e41ed6ec06c2016b95c
  • Allow multi-character source strings in contrib/unaccent. This could be useful in languages where diacritic signs are represented as separate characters; more generally it supports using unaccent dictionaries for substring substitutions beyond narrowly conceived "diacritic removal". In any case, since the rule-file parser doesn't complain about multi-character source strings, it behooves us to do something unsurprising with them. http://git.postgresql.org/pg/commitdiff/1b2488731cc2c87cc9a4cb8d654e4d9981fdf9ac
  • Issue a WARNING about invalid rule file format in contrib/unaccent. We were already issuing a WARNING, albeit only elog not ereport, for duplicate source strings; so warning rather than just being stoically silent seems like the best thing to do here. Arguably both of these complaints should be upgraded to ERRORs, but that might be more behavioral change than people want. Note: the faulty line is already printed via an errcontext hook, so there's no need for more information than these messages provide. http://git.postgresql.org/pg/commitdiff/03a25cec8de3737924c9dd33bb868d4bc7a33ad5
  • Fix inadequately-sized output buffer in contrib/unaccent. The output buffer size in unaccent_lexize() was calculated as input string length times pg_database_encoding_max_length(), which effectively assumes that replacement strings aren't more than one character. While that was all that we previously documented it to support, the code actually has always allowed replacement strings of arbitrary length; so if you tried to make use of longer strings, you were at risk of buffer overrun. To fix, use an expansible StringInfo buffer instead of trying to determine the maximum space needed a-priori. This would be a security issue if unaccent rules files could be installed by unprivileged users; but fortunately they can't, so in the back branches the problem can be labeled as improper configuration by a superuser. Nonetheless, a memory stomp isn't a nice way of reacting to improper configuration, so let's back-patch the fix. http://git.postgresql.org/pg/commitdiff/5a421a47eb1fc4398f42678c09e35aa72dc7cf18
  • Improve handling of OOM score adjustment in sample Linux start script. Per a suggestion from Christoph Berg. http://git.postgresql.org/pg/commitdiff/f23425fa950fec3aff458de117037c9caadbc35c
  • Remove some useless code in the configure script. Almost ten years ago, commit e48322a6d6cfce1ec52ab303441df329ddbc04d1 broke the logic in ACX_PTHREAD by looping through all the possible flags rather than stopping with the first one that would work. This meant that $acx_pthread_ok was no longer meaningful after the loop; it would usually be "no", whether or not we'd found working thread flags. The reason nobody noticed is that Postgres doesn't actually use any of the symbols set up by the code after the loop. Rather than complicate things some more to make it work as designed, let's just remove all that dead code, and thereby save a few cycles in each configure run. http://git.postgresql.org/pg/commitdiff/2e8ce9ae46d15b2bfd34c2d53193da9858d3471a
  • Refactor CREATE/ALTER DATABASE syntax so options need not be keywords. Most of the existing option names are keywords anyway, but we can get rid of LC_COLLATE and LC_CTYPE as keywords known to the lexer/grammar. This immediately reduces the size of the grammar tables by about 8KB, and will save more when we add additional CREATE/ALTER DATABASE options in future. A side effect of the implementation is that the CONNECTION LIMIT option can now also be spelled CONNECTION_LIMIT. We choose not to document this, however. Vik Fearing, based on a suggestion by me; reviewed by Pavel Stehule http://git.postgresql.org/pg/commitdiff/15c82efd6994affd1d5654d13bc8911a9faff659
  • Allow CREATE/ALTER DATABASE to manipulate datistemplate and datallowconn. Historically these database properties could be manipulated only by manually updating pg_database, which is error-prone and only possible for superusers. But there seems no good reason not to allow database owners to set them for their databases, so invent CREATE/ALTER DATABASE options to do that. Adjust a couple of places that were doing it the hard way to use the commands instead. Vik Fearing, reviewed by Pavel Stehule http://git.postgresql.org/pg/commitdiff/fbb1d7d73f8e23a3a61e702629c53cef48cb0918
  • Add some errdetail to checkRuleResultList(). This function wasn't originally thought to be really user-facing, because converting a table to a view isn't something we expect people to do manually. So not all that much effort was spent on the error messages; in particular, while the code will complain that you got the column types wrong it won't say exactly what they are. But since we repurposed the code to also check compatibility of rule RETURNING lists, it's definitely user-facing. It now seems worthwhile to add errdetail messages showing exactly what the conflict is when there's a mismatch of column names or types. This is prompted by bug #10836 from Matthias Raffelsieper, which might have been forestalled if the error message had reported the wrong column type as being "record". Back-patch to 9.4, but not into older branches where the set of translatable error strings is supposed to be stable. http://git.postgresql.org/pg/commitdiff/7980ab30ecf36162699f138697e2ff5589d9063e
  • Improve support for composite types in PL/Python. Allow PL/Python functions to return arrays of composite types. Also, fix the restriction that plpy.prepare/plpy.execute couldn't handle query parameters or result columns of composite types. In passing, adopt a saner arrangement for where to release the tupledesc reference counts acquired via lookup_rowtype_tupdesc. The callers of PLyObject_ToCompositeDatum were doing the lookups, but then the releases happened somewhere down inside subroutines of PLyObject_ToCompositeDatum, which is bizarre and bug-prone. Instead release in the same function that acquires the refcount. Ed Behn and Ronan Dunklau, reviewed by Abhijit Menon-Sen http://git.postgresql.org/pg/commitdiff/8b6010b8350a1756cd85595705971df81b5ffc07
  • Redesign API presented by nodeAgg.c for ordered-set and similar aggregates. The previous design exposed the input and output ExprContexts of the Agg plan node, but work on grouping sets has suggested that we'll regret doing that. Instead provide more narrowly-defined APIs that can be implemented in multiple ways, namely a way to get a short-term memory context and a way to register an aggregate shutdown callback. Back-patch to 9.4 where the bad APIs were introduced, since we don't want third-party code using these APIs and then having to change in 9.5. Andrew Gierth http://git.postgresql.org/pg/commitdiff/6f5034eda05c4946b65858fb8831d069f2873083
  • Don't cache per-group context across the whole query in orderedsetaggs.c. Although nodeAgg.c currently uses the same per-group memory context for all groups of a query, that might change in future. Avoid assuming it. This costs us an extra AggCheckCallContext() call per group, but that's pretty cheap and is probably good from a safety standpoint anyway. Back-patch to 9.4 in case any third-party code copies this logic. Andrew Gierth http://git.postgresql.org/pg/commitdiff/ecd657974478fc713fdc3a625d648cd6a985e3e6

Robert Haas a poussé :

  • Avoid copying index tuples when building an index. The previous code, perhaps out of concern for avoid memory leaks, formed the tuple in one memory context and then copied it to another memory context. However, this doesn't appear to be necessary, since index_form_tuple and the functions it calls take precautions against leaking memory. In my testing, building the tuple directly inside the sort context shaves several percent off the index build time. Rearrange things so we do that. Patch by me. Review by Amit Kapila, Tom Lane, Andres Freund. http://git.postgresql.org/pg/commitdiff/9f03ca915196dfc871804a1f8aad26207f601fd6
  • Remove swpb-based spinlock implementation for ARMv5 and earlier. Per recent analysis by Andres Freund, this implementation is in fact unsafe, because ARMv5 has weak memory ordering, which means tha the CPU could move loads or stores across the volatile store performed by the default S_UNLOCK. We could try to fix this, but have no ARMv5 hardware to test on, so removing support seems better. We can still support ARMv5 systems on GCC versions new enough to have built-in atomics support for this platform, and can also re-add support for the old way if someone has hardware that can be used to test a fix. However, since the requirement to use a relatively-new GCC hasn't been an issue for ARMv6 or ARMv7, which lack the swpb instruction altogether, perhaps it won't be an issue for ARMv5 either. http://git.postgresql.org/pg/commitdiff/4893ccd02459fdb444f4f4b34c441eb899592879

Fujii Masao a poussé :

Kevin Grittner a poussé :

  • Smooth reporting of commit/rollback statistics. If a connection committed or rolled back any transactions within a PGSTAT_STAT_INTERVAL pacing interval without accessing any tables, the reporting of those statistics would be held up until the connection closed or until it ended a PGSTAT_STAT_INTERVAL interval in which it had accessed a table. This could result in under- reporting of transactions for an extended period, followed by a spike in reported transactions. While this is arguably a bug, the impact is minimal, primarily affecting, and being affected by, monitoring software. It might cause more confusion than benefit to change the existing behavior in released stable branches, so apply only to master and the 9.4 beta. Gurjeet Singh, with review and editing by Kevin Grittner, incorporating suggested changes from Abhijit Menon-Sen and Tom Lane. http://git.postgresql.org/pg/commitdiff/ac46de56eab9bb93c23c7f34070f3a16e7e76743
  • Remove dead typeStruct variable from plpy_spi.c. Left behind by 8b6010b8350a1756cd85595705971df81b5ffc07. http://git.postgresql.org/pg/commitdiff/e254ff21d1286fe5c2b5ecdfa9bb6b15a7830fcd

Peter Eisentraut a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Noah Misch, Kyotaro HORIGUCHI and Etsuro Fujita traded patches around allowing foreign tables to be part of table inheritance hierarchies.
  • Andres Freund sent in another revision of a patch to add a cluster_name GUC, which controls whether same is visible in ps output.
  • Abhijit Menon-Sen and Marti Raudsepp traded patches to add a --stats option to pg_xlogdump.
  • Pavel Stehule sent in two more revisions of a patch to allow psql to show only failed queries.
  • Michael Paquier sent in another revision of a patch to extend MSVC scripts to support --with-extra-version.
  • Heikki Linnakangas sent in another revision of a patch to change the WAL format and API.
  • Michael Paquier and Ronan Dunklau traded patches for IMPORT FOREIGN SCHEMA.
  • Tomas Vondra sent in three more revisions of a patch to make hash buckets grow appropriately.
  • Kyotaro HORIGUCHI sent in a patch to break socket-blocking on termination.
  • Kevin Grittner sent in another revision of a patch to add a C extension to test delta relations in AFTER triggers.
  • Robert Haas and Andres Freund traded patches to simulate memory barriers on platforms where they are not available.
  • Gurjeet Singh sent in another revision of a patch to send transaction commit/rollback stats to the stats collector unconditionally.
  • Kyotaro HORIGUCHI sent in a patch to correct the documentation of ALTER USER SET local_preload_libraries.
  • Ronan Dunklau sent in another revision of a patch to improve the functionality of arrays of composite types returned from PL/Python.
  • Dilip Kumar sent in three more revisions of a patch to allow vacuumdb to use multiple cores in parallel.
  • Michael Paquier sent two flocks of patches to fix an issue in WAL replay.
  • Fujii Masao sent in another revision of a patch to make log_disconnections PGC_SUSET rather than PGC_SIGHUP.
  • Michael Banck sent in a patch to add an additional documentation subsection for page-level locks in the explicit-locking section.
  • Ian Lawrence Barwick sent in another revision of a patch to add a "RETURNING PRIMARY KEY" syntax extension to DML.
  • Fabien COELHO sent in two more revisions of a patch to add a Gaussian distribution to pgbench.
  • Peter Geoghegan sent in another revision of a patch to do better at HINTing an appropriate column within errorMissingColumn().
  • Tomas Vondra sent in another revision of a patch to tweak NTUP_PER_BUCKET.
  • Tatsuo Ishii sent in another revision of a patch to add a shared_memory_type GUC.
  • Abhijit Menon-Sen sent in two more revisions of a patch to introduce XLogLockBlockRangeForCleanup().
  • Rahila Syed sent in two more revisions of a patch to allow various compression algorithms for full page writes.
  • Andrew (RhodiumToad) Gierth sent in two patch sets intended to be infrastructure for GROUPING SETS.
  • Furuya Osamu sent in another revision of a patch add a synchronous mode to pg_receivexlog.
  • Thomas Munro sent in a PoC patch to enable DISTINCT with btree skip scan (a.k.a. "loose index scan").
  • David Rowley sent in another revision of a patch to allow NOT IN to use ANTI joins.
  • David Rowley sent in another revision of a patch to allow subquery LEFT JOIN removal where that would produce correct results.
  • Andrew (RhodiumToad) Gierth sent in two revisions of a patch to fix a performance regression related to ScalarArrayOpExpr.
  • Craig Ringer sent in a patch to improve bytea error messages.
  • Emre Hasegeli sent in another revision of a patch to add selectivity estimation for inet operators.
  • SAWADA Masahiko sent in another revision of a patch to add line number as prompt option to psql.
  • Jeff Davis sent in another revision of a patch to make it possible for the LAG and LEAD window functions to ignore nulls.

par N Bougain le dimanche 20 juillet 2014 à 21h16

jeudi 3 juillet 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 29 juin 2014

Les nouveautés des produits dérivés

  • PgBackMan 1.0.0, un utilitaire de gestion des sauvegardes logiques générées via pg_dump et pg_dumpall : http://www.pgbackman.org/

Offres d'emplois autour de PostgreSQL en juin

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é :

  • Fix bug in WAL_DEBUG. The record header was not copied correctly to the buffer that was passed to the rm_desc function. Broken by my rm_desc signature refactoring patch. http://git.postgresql.org/pg/commitdiff/85ba0748ed5aa069643887af84fc28c380b1e815
  • Improve tab-completion of DROP and ALTER ENABLE/DISABLE on triggers and rules. At "DROP RULE/TRIGGER triggername ON ...", tab-complete tables that have a rule/trigger with that name. At "ALTER TABLE tablename ENABLE/DISABLE TRIGGER/RULE ...", tab-complete to rules/triggers on that table. Previously, we would tab-complete to all rules or triggers, not just those that are on that table. Also, filter out internal RI triggers from the list. You can't DROP them, and enabling/disabling them is such a rare (and dangerous) operation that it seems better to hide them. Andreas Karlsson, reviewed by Ian Barwick. http://git.postgresql.org/pg/commitdiff/631e7f6b4e0629077408d3f8caf282627765f3f0
  • Don't allow foreign tables with OIDs. The syntax doesn't let you specify "WITH OIDS" for foreign tables, but it was still possible with default_with_oids=true. But the rest of the system, including pg_dump, isn't prepared to handle foreign tables with OIDs properly. Backpatch down to 9.1, where foreign tables were introduced. It's possible that there are databases out there that already have foreign tables with OIDs. There isn't much we can do about that, but at least we can prevent them from being created in the future. Patch by Etsuro Fujita, reviewed by Hadi Moshayedi. http://git.postgresql.org/pg/commitdiff/a87a7dc8b64a99e5e497591dddb37b3ecdfae2eb

Fujii Masao a poussé :

Robert Haas a poussé :

  • Check for interrupts during tuple-insertion loops. Normally, this won't matter too much; but if I/O is really slow, for example because the system is overloaded, we might write many pages before checking for interrupts. A single toast insertion might write up to 1GB of data, and a multi-insert could write hundreds of tuples (and their corresponding TOAST data). http://git.postgresql.org/pg/commitdiff/c922353b1c7e7fe5fa506664ccf0c87a0abfdda2

Bruce Momjian a poussé :

Tom Lane a poussé :

  • Fix handling of nested JSON objects in json_populate_recordset and friends. populate_recordset_object_start() improperly created a new hash table (overwriting the link to the existing one) if called at nest levels greater than one. This resulted in previous fields not appearing in the final output, as reported by Matti Hameister in bug #10728. In 9.4 the problem also affects json_to_recordset. This perhaps missed detection earlier because the default behavior is to throw an error for nested objects: you have to pass use_json_as_text = true to see the problem. In addition, fix query-lifespan leakage of the hashtable created by json_populate_record(). This is pretty much the same problem recently fixed in dblink: creating an intended-to-be-temporary context underneath the executor's per-tuple context isn't enough to make it go away at the end of the tuple cycle, because MemoryContextReset is not MemoryContextResetAndDeleteChildren. Michael Paquier and Tom Lane http://git.postgresql.org/pg/commitdiff/57d8c1270e1538d1f02e4fa1cdb1d8ded82f7c70
  • Cosmetic improvements in jsonfuncs.c. Re-pgindent, remove a lot of random vertical whitespace, remove useless (if not counterproductive) inline markings, get rid of unnecessary zero-padding of strings for hashtable searches. No functional changes. http://git.postgresql.org/pg/commitdiff/8d2d7ad5aba6fdabd58a2a829038596f48cae723
  • Rationalize error messages within jsonfuncs.c. I noticed that the functions in jsonfuncs.c sometimes printed error messages that claimed I'd called some other function. Investigation showed that this was from repurposing code into "worker" functions without taking much care as to whether it would mention the right SQL-level function if it threw an error. Moreover, there was a weird mismash of messages that contained a fixed function name, messages that used %s for a function name, and messages that constructed a function name out of spare parts, like "json%s_populate_record" (which, quite aside from being ugly as sin, wasn't even sufficient to cover all the cases). This would put an undue burden on our long-suffering translators. Standardize on inserting the SQL function name with %s so as to reduce the number of translatable strings, and pass function names around as needed to make sure we can report the right one. Fix up some gratuitous variations in wording, too. http://git.postgresql.org/pg/commitdiff/798e2357905f759913166d4f5be249e76a84c662
  • Forward-patch regression test for "could not find pathkey item to sort". Commit a87c729153e372f3731689a7be007bc2b53f1410 already fixed the bug this is checking for, but the regression test case it added didn't cover this scenario. Since we managed to miss the fact that there was a bug at all, it seems like a good idea to propagate the extra test case forward to HEAD. http://git.postgresql.org/pg/commitdiff/344eed91e9d5bfea698b30351abde69ea4e893a8
  • Get rid of bogus separate pg_proc entries for json_extract_path operators. These should not have existed to begin with, but there was apparently some misunderstanding of the purpose of the opr_sanity regression test item that checks for operator implementation functions with their own comments. The idea there is to check for unintentional violations of the rule that operator implementation functions shouldn't be documented separately .... but for these functions, that is in fact what we want, since the variadic option is useful and not accessible via the operator syntax. Get rid of the extra pg_proc entries and fix the regression test and documentation to be explicit about what we're doing here. http://git.postgresql.org/pg/commitdiff/f71136eeeb5c6a234e19a245db7ae1484fc7bf4f
  • Disallow pushing volatile qual expressions down into DISTINCT subqueries. A WHERE clause applied to the output of a subquery with DISTINCT should theoretically be applied only once per distinct row; but if we push it into the subquery then it will be evaluated at each row before duplicate elimination occurs. If the qual is volatile this can give rise to observably wrong results, so don't do that. While at it, refactor a little bit to allow subquery_is_pushdown_safe to report more than one kind of restrictive condition without indefinitely expanding its argument list. Although this is a bug fix, it seems unwise to back-patch it into released branches, since it might de-optimize plans for queries that aren't giving any trouble in practice. So apply to 9.4 but not further back. http://git.postgresql.org/pg/commitdiff/1147035203a47a424b2399fc74829d097b7061e4
  • Allow pushdown of WHERE quals into subqueries with window functions. We can allow this even without any specific knowledge of the semantics of the window function, so long as pushed-down quals will either accept every row in a given window partition, or reject every such row. Because window functions act only within a partition, such a case can't result in changing the window functions' outputs for any surviving row. Eliminating entire partitions in this way obviously can reduce the cost of the window-function computations substantially. The fly in the ointment is that it's hard to be entirely sure whether this is true for an arbitrary qual condition. This patch allows pushdown if (a) the qual references only partitioning columns, and (b) the qual contains no volatile functions. We are at risk of incorrect results if the qual can produce different answers for values that the partitioning equality operator sees as equal. While it's not hard to invent cases for which that can happen, it seems to seldom be a problem in practice, since no one has complained about a similar assumption that we've had for many years with respect to DISTINCT. The potential performance gains seem to be worth the risk. David Rowley, reviewed by Vik Fearing; some credit is due also to Thomas Mayer who did considerable preliminary investigation. http://git.postgresql.org/pg/commitdiff/d222585a9f7a18f2d793785c82be4c877b90c461
  • Remove use_json_as_text options from json_to_record/json_populate_record. The "false" case was really quite useless since all it did was to throw an error; a definition not helped in the least by making it the default. Instead let's just have the "true" case, which emits nested objects and arrays in JSON syntax. We might later want to provide the ability to emit sub-objects in Postgres record or array syntax, but we'd be best off to drive that off a check of the target field datatype, not a separate argument. For the functions newly added in 9.4, we can just remove the flag arguments outright. We can't do that for json_populate_record[set], which already existed in 9.3, but we can ignore the argument and always behave as if it were "true". It helps that the flag arguments were optional and not documented in any useful fashion anyway. http://git.postgresql.org/pg/commitdiff/a749a23d7af4dba9b3468076ec561d2cbf69af09

Alvaro Herrera a poussé :

  • Don't allow relminmxid to go backwards during VACUUM FULL. We were allowing a table's pg_class.relminmxid value to move backwards when heaps were swapped by VACUUM FULL or CLUSTER. There is a similar protection against relfrozenxid going backwards, which we neglected to clone when the multixact stuff was rejiggered by commit 0ac5ad5134f276. Backpatch to 9.3, where relminmxid was introduced. As reported by Heikki in http://www.postgresql.org/message-id/52401AEA.9000608@vmware.com http://git.postgresql.org/pg/commitdiff/b7e51d9c06e6a0da50abbbd0603ecb80f0b6f02b
  • Have multixact be truncated by checkpoint, not vacuum. Instead of truncating pg_multixact at vacuum time, do it only at checkpoint time. The reason for doing it this way is twofold: first, we want it to delete only segments that we're certain will not be required if there's a crash immediately after the removal; and second, we want to do it relatively often so that older files are not left behind if there's an untimely crash. Per my proposal in http://www.postgresql.org/message-id/20140626044519.GJ7340@eldon.alvh.no-ip.org we now execute the truncation in the checkpointer process rather than as part of vacuum. Vacuum is in only charge of maintaining in shared memory the value to which it's possible to truncate the files; that value is stored as part of checkpoints also, and so upon recovery we can reuse the same value to re-execute truncate and reset the oldest-value-still-safe-to-use to one known to remain after truncation. Per bug reported by Jeff Janes in the course of his tests involving bug #8673. While at it, update some comments that hadn't been updated since multixacts were changed. Backpatch to 9.3, where persistency of pg_multixact files was introduced by commit 0ac5ad5134f2. http://git.postgresql.org/pg/commitdiff/f741300c90141ee274f19a13629ae03a9806b598
  • Fix broken Assert() introduced by 8e9a16ab8f7f0e58. Don't assert MultiXactIdIsRunning if the multi came from a tuple that had been share-locked and later copied over to the new cluster by pg_upgrade. Doing that causes an error to be raised unnecessarily: MultiXactIdIsRunning is not open to the possibility that its argument came from a pg_upgraded tuple, and all its other callers are already checking; but such multis cannot, obviously, have transactions still running, so the assert is pointless. Noticed while investigating the bogus pg_multixact/offsets/0000 file left over by pg_upgrade, as reported by Andres Freund in http://www.postgresql.org/message-id/20140530121631.GE25431@alap3.anarazel.de Backpatch to 9.3, as the commit that introduced the buglet. http://git.postgresql.org/pg/commitdiff/b2770576486265c2ce35b64e875028672a3bb7b5

Andres Freund a poussé :

  • Remove Alpha and Tru64 support. Support for running postgres on Alpha hasn't been tested for a long while. Due to Alpha's uniquely lax cache coherency model it's a hard to develop for platform (especially blindly!) and thought to be unlikely to currently work correctly. As Alpha is the only supported architecture for Tru64 drop support for it as well. Tru64's support has ended 2012 and it has been in maintenance-only mode for much longer. Also remove stray references to __ksr__ and ultrix defines. http://git.postgresql.org/pg/commitdiff/a6d488cb538c8761658f0f7edfc40cecc8c29f2d
  • Add cluster_name GUC which is included in process titles if set. When running several postgres clusters on one OS instance it's often inconveniently hard to identify which "postgres" process belongs to which postgres instance. Add the cluster_name GUC, whose value will be included as part of the process titles if set. With that processes can more easily identified using tools like 'ps'. To avoid problems with encoding mismatches between postgresql.conf, consoles, and individual databases replace non-ASCII chars in the name with question marks. The length is limited to NAMEDATALEN to make it less likely to truncate important information at the end of the status. Thomas Munro, with some adjustments by me and review by a host of people. http://git.postgresql.org/pg/commitdiff/51adcaa0df81da5e94b582d47de64ebb17129937

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • rohtodeveloper sent in a patch to make PostgreSQL more compatible with MS SQL Server.
  • Jeff Janes sent in another revision of a patch to enable tab completion for setting search_path in psql.
  • David Rowley sent in two more revisions of a patch to allow removal of certain cases of LEFT JOIN.
  • Furuya Osamu and Fujii Masao traded patches to add a synchronous mode to pg_receivexlog.
  • Abhijit Menon-Sen sent in a patch to add a pg_audit contrib module.
  • Pavel Stehule, Petr (PJMODOS) Jelinek, and Abhijit Menon-Sen traded patches to add --help-variables to psql.
  • Pavan Deolasee and Heikki Linnakangas traded patches to fix a bug in SP-GiST.
  • David Rowley sent in two more revisions of a patch to allow NOT IN to use anti-JOINs.
  • Fabrízio de Royes Mello sent in two revisions of a patch to allow pg_filedump to work in PostgreSQL 9.4.
  • John Lumby sent in another revision of a patch to allow extended prefetching using asynchronous I/O where available.
  • Amit Kapila sent in another revision of a patch to help scale shared buffer eviction.
  • Pavel Stehule sent in another revision of a patch to allow logging only failed queries in psql.
  • Ian Lawrence Barwick sent in another revision of a patch to add RETURNING PRIMARY KEY.
  • Dilip Kumar sent in two more revisions of a patch to allow vacuumdb to use >1 core.
  • Vik Fearing sent in three revisions of a patch to enable ALTER SYSTEM RESET.
  • Andres Freund sent in another revision of a patch to do atomic operations in a more systematic way based on available hardware.
  • Fabrízio de Royes Mello sent in another revision of a patch to enable ALTER TABLE ... SET LOGGED.
  • Andreas Karlsson sent in a patch to fix a bug in his prior patch to allow using SChannel instead of OpenSSL for SSL.
  • Michael Paquier sent in another revision of a patch to extend MSVC scripts to support --with-extra-version.
  • Petr (PJMODOS) Jelinek sent in another revision of a patch to support built-in binning functions.
  • Michael Paquier sent in two more revisions of a patch to fix some WAL replay bugs.
  • Andres Freund sent in another revision of a patch to simulate memory barriers with spinlocks for platforms lacking the former.
  • Petr (PJMODOS) Jelinek sent in two more revisions of a patch to allow setting a new system identifier using pg_resetxlog.
  • Vik Fearing sent in another revision of a patch to allowing SQL access for setting database attributes.
  • Tomas Vondra sent in four revisions of a patch to fix an issue where bad estimation together with large work_mem generates slow hash joins.
  • Kyotaro HORIGUCHI sent in a patch to enable pg_resetxlog to clear backup start/end locations.
  • Rajeev Rastogi sent in another revision of a patch to enable autonomous transactions.
  • Noah Misch sent in a patch to fix an issue where pgstat_heap() consults freed memory.
  • Asbjørn Sloth Tønnesen sent in three revisions of a patch to add tau, (a.k.a. 2 pi) to PostgreSQL.
  • David Fetter sent in a patch to implement a C-based trigger function atop Kevin Grittner's patch to enable row access in per-statement AFTER triggers.
  • Pavel Stehule sent in a patch to allows ignoring nulls in the row_to_json() function.
  • Matheus de Oliveira sent in a patch to allow using a real temporary tablespace.
  • Pavel Stehule sent in another revision of a patch to enable psql unicode border line styles.
  • Mohammad Alhashash and Abhijit Menon-Sen traded patches to allow empty targets in unaccent dictionary.
  • Kevin Grittner sent in another revision of a patch to send transaction commit/rollback stats to the stats collector unconditionally.
  • Thomas Munro sent in another revision of a patch to to implement SKIP LOCKED DATA.

par N Bougain le jeudi 3 juillet 2014 à 07h25

mercredi 2 juillet 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 22 juin 2014

Postgres Open 2014 aura lieu à Chicago (Illinois, États-Unis) du 17 au 19 septembre. Les tickets "lève-tôt" (disponibles jusqu'au 6 juillet) et les tutoriaux sont disponibles à l'achat : https://postgresopen.org/2014/tickets/ http://postgresopen.org/2014/callforpapers/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en juin

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

Bruce Momjian a poussé :

Tom Lane a poussé :

  • Avoid recursion when processing simple lists of AND'ed or OR'ed clauses. Since most of the system thinks AND and OR are N-argument expressions anyway, let's have the grammar generate a representation of that form when dealing with input like "x AND y AND z AND ...", rather than generating a deeply-nested binary tree that just has to be flattened later by the planner. This avoids stack overflow in parse analysis when dealing with queries having more than a few thousand such clauses; and in any case it removes some rather unsightly inconsistencies, since some parts of parse analysis were generating N-argument ANDs/ORs already. It's still possible to get a stack overflow with weirdly parenthesized input, such as "x AND (y AND (z AND ( ... )))", but such cases are not mainstream usage. The maximum depth of parenthesization is already limited by Bison's stack in such cases, anyway, so that the limit is probably fairly platform-independent. Patch originally by Gurjeet Singh, heavily revised by me http://git.postgresql.org/pg/commitdiff/2146f13408cdb85c738364fe8f7965209e08c6be
  • Implement UPDATE tab SET (col1,col2,...) = (SELECT ...), ... This SQL-standard feature allows a sub-SELECT yielding multiple columns (but only one row) to be used to compute the new values of several columns to be updated. While the same results can be had with an independent sub-SELECT per column, such a workaround can require a great deal of duplicated computation. The standard actually says that the source for a multi-column assignment could be any row-valued expression. The implementation used here is tightly tied to our existing sub-SELECT support and can't handle other cases; the Bison grammar would have some issues with them too. However, I don't feel too bad about this since other cases can be converted into sub-SELECTs. For instance, "SET (a,b,c) = row_valued_function(x)" could be written "SET (a,b,c) = (SELECT * FROM row_valued_function(x))". http://git.postgresql.org/pg/commitdiff/8f889b1083f38f4f5b3bd3512008a3f60e939244
  • Fix weird spacing in error message. Seems to have been introduced in 1a3458b6d8d202715a83c88474a1b63726d0929e. http://git.postgresql.org/pg/commitdiff/66802246e22d51858cd543877fcfddf24e6812f2
  • Improve our mechanism for controlling the Linux out-of-memory killer. Arrange for postmaster child processes to respond to two environment variables, PG_OOM_ADJUST_FILE and PG_OOM_ADJUST_VALUE, to determine whether they reset their OOM score adjustments and if so to what. This is superior to the previous design involving #ifdef's in several ways. The behavior is now available in a default build, and both ends of the adjustment --- the original adjustment of the postmaster's level and the subsequent readjustment by child processes --- can now be controlled in one place, namely the postmaster launch script. So it's no longer necessary for the launch script to act on faith that the server was compiled with the appropriate options. In addition, if someone wants to use an OOM score other than zero for the child processes, that doesn't take a recompile anymore; and we no longer have to cater separately to the two different historical kernel APIs for this adjustment. Gurjeet Singh, somewhat revised by me http://git.postgresql.org/pg/commitdiff/df8b7bc9ffff5b00aacff774600b569992cddeb8
  • Document SQL functions' behavior of parsing the whole function at once. Haribabu Kommi, somewhat rewritten by me http://git.postgresql.org/pg/commitdiff/f28d9b10f73440adcb2e094440b4b213673e491b
  • Avoid leaking memory while evaluating arguments for a table function. ExecMakeTableFunctionResult evaluated the arguments for a function-in-FROM in the query-lifespan memory context. This is insignificant in simple cases where the function relation is scanned only once; but if the function is in a sub-SELECT or is on the inside of a nested loop, any memory consumed during argument evaluation can add up quickly. (The potential for trouble here had been foreseen long ago, per existing comments; but we'd not previously seen a complaint from the field about it.) To fix, create an additional temporary context just for this purpose. Per an example from MauMau. Back-patch to all active branches. http://git.postgresql.org/pg/commitdiff/45b0f357235236dd3198f8abcca277adc0d7459a
  • Add Asserts to verify that catalog cache keys are unique and not null. The catcache code is effectively assuming this already, so let's insist that the catalog and index are actually declared that way. Having done that, the comments in indexing.h about non-unique indexes not being used for catcaches are completely redundant not just mostly so; and we didn't have such a comment for every such index anyway. So let's get rid of them. Per discussion of whether we should identify primary keys for catalogs. We might or might not take that further step, but this change in itself will allow quicker detection of misdeclared catcaches, so it seems worth doing in any case. http://git.postgresql.org/pg/commitdiff/8b38a538c0aa5a432dacd90f10805dc667a3d1a0

Heikki Linnakangas a poussé :

Noah Misch a poussé :

Andrew Dunstan a poussé :

Fujii Masao a poussé :

  • Don't allow data_directory to be set in postgresql.auto.conf by ALTER SYSTEM. data_directory could be set both in postgresql.conf and postgresql.auto.conf so far. This could cause some problematic situations like circular definition. To avoid such situations, this commit forbids a user to set data_directory in postgresql.auto.conf. Backpatch this to 9.4 where ALTER SYSTEM command was introduced. Amit Kapila, reviewed by Abhijit Menon-Sen, with minor adjustments by me. http://git.postgresql.org/pg/commitdiff/9ba78fb0b9e6c5a7115592c2c3116fca16b5184e

Kevin Grittner a poussé :

  • Fix calculation of PREDICATELOCK_MANAGER_LWLOCK_OFFSET. Commit ea9df812d8502fff74e7bc37d61bdc7d66d77a7f failed to include NUM_BUFFER_PARTITIONS in this offset, resulting in a bad offset. Ultimately this threw off NUM_FIXED_LWLOCKS which is based on earlier offsets, leading to memory allocation problems. It seems likely to have also caused increased LWLOCK contention when serializable transactions were used, because lightweight locks used for that overlapped others. Reported by Amit Kapila with analysis and fix. Backpatch to 9.4, where the bug was introduced. http://git.postgresql.org/pg/commitdiff/bfaa8c665fcbd4388e01c53c4b5137f1f53d1787
  • Fix documentation template for CREATE TRIGGER. By using curly braces, the template had specified that one of "NOT DEFERRABLE", "INITIALLY IMMEDIATE", or "INITIALLY DEFERRED" was required on any CREATE TRIGGER statement, which is not accurate. Change to square brackets makes that optional. Backpatch to 9.1, where the error was introduced. http://git.postgresql.org/pg/commitdiff/734bea8a69db9ffd675a697f614e80baeb2ca854

Andres Freund a poussé :

  • Don't allow to disable backend assertions via the debug_assertions GUC. The existance of the assert_enabled variable (backing the debug_assertions GUC) reduced the amount of knowledge some static code checkers (like coverity and various compilers) could infer from the existance of the assertion. That could have been solved by optionally removing the assertion_enabled variable from the Assert() et al macros at compile time when some special macro is defined, but the resulting complication doesn't seem to be worth the gain from having debug_assertions. Recompiling is fast enough. The debug_assertions GUC is still available, but readonly, as it's useful when diagnosing problems. The commandline/client startup option -A, which previously also allowed to enable/disable assertions, has been removed as it doesn't serve a purpose anymore. While at it, reduce code duplication in bufmgr.c and localbuf.c assertions checking for spurious buffer pins. That code had to be reindented anyway to cope with the assert_enabled removal. http://git.postgresql.org/pg/commitdiff/3bdcf6a5a7555035810e2ba2b8a0fb04dc5c66b8
  • Do all-visible handling in lazy_vacuum_page() outside its critical section. Since fdf9e21196a lazy_vacuum_page() rechecks the all-visible status of pages in the second pass over the heap. It does so inside a critical section, but both visibilitymap_test() and heap_page_is_all_visible() perform operations that should not happen inside one. The former potentially performs IO and both potentially do memory allocations. To fix, simply move all the all-visible handling outside the critical section. Doing so means that the PD_ALL_VISIBLE on the page won't be included in the full page image of the HEAP2_CLEAN record anymore. But that's fine, the flag will be set by the HEAP2_VISIBLE logged later. Backpatch to 9.3 where the problem was introduced. The bug only came to light due to the assertion added in 4a170ee9 and isn't likely to cause problems in production scenarios. The worst outcome is a avoidable PANIC restart. This also gets rid of the difference in the order of operations between master and standby mentioned in 2a8e1ac5. Per reports from David Leverton and Keith Fiske in bug #10533. http://git.postgresql.org/pg/commitdiff/ecac0e2b9e8e8e78d771b20fe441e95bb02db2fa

Joe Conway a poussé :

  • Clean up data conversion short-lived memory context. dblink uses a short-lived data conversion memory context. However it was not deleted when no longer needed, leading to a noticeable memory leak under some circumstances. Plug the hole, along with minor refactoring. Backpatch to 9.2 where the leak was introduced. Report and initial patch by MauMau. Reviewed/modified slightly by Tom Lane and me. http://git.postgresql.org/pg/commitdiff/1dde5782e34a1c5ef2ce9d97cf18007fed5fde92

Correctifs rejetés (à ce jour)

  • No one was disappointed this week :-)

Correctifs en attente

  • Robert Haas sent in another revision of a patch to avoiding tuple copying in btree index builds.
  • Peter Geoghegan sent in a patch to add an extension, btreecheck, which provides SQL-callable functions for checking nbtree index invariants on live systems.
  • Furuya Osamu sent in another revision of a patch to add a synchronous mode to pg_receivexlog.
  • Ronan Dunklau sent in another revision of a patch to implement IMPORT FOREIGN SCHEMA.
  • Michael Paquier sent in a set of three patches to fix some bugs in WAL replay.
  • Abhijit Menon-Sen sent in another revision of a patch to fix xpath() to return namespace definitions.
  • Jeevan Chalke sent in another revision of a patch to include pg_dump client and PostgreSQL server versions without requiring the verbose option in the pg_dump output comments.
  • Kaigai Kouhei sent in another revision of a patch to implement a custom plan API.
  • MauMau sent in two revisions of a patch to implement pg_copy, a command for reliable WAL archiving.
  • Andreas Karlsson sent in another revision of a patch to add psql tab completion for DROP TRIGGER/RULE and ALTER TABLE ... DISABLE/ENABLE.
  • Mitsumasa KONDO sent in another revision of a patch to add a Gaussian distribution option to pgbench.
  • Steven Siebert sent in a patch to fix the problem of ldapbindpasswd leaking in clear text to the postgresql log.
  • Jeff Janes sent in a patch to fix an issue where certain files in pg_multixact couldn't be read on the replica during hot standby.
  • Vik Fearing and David Rowley traded patches to allow pushdowns of items matching PARTITION BY clauses.
  • Matheus de Oliveira sent in a patch to allow creating distinct temporary tablespaces.
  • David Rowley sent in another revision of a patch to allow join removals for certain LEFT JOINs.
  • Emre Hasegeli sent in another revision of a patch to add selectivity estimation for inet operators.
  • Rajeev Rastogi sent in another revision of a patch to add autonomous transactions.
  • Michael Paquier sent in a patch to remove usage of wsock32 in Windows builds.
  • Andres Freund sent in two more revisions of a patch to disallow people from disabling backend assertions via the assert_enabled GUC.
  • Kyotaro HORIGUCHI sent in another revision of a patch to allow INHERIT support for foreign tables.
  • Fujii Masao sent in a patch to redefine log_statement as a list.
  • Kevin Grittner sent in a patch to add delta relations in AFTER triggers, per SQL specification.
  • Pavel Stehule and Erik Rijkers traded patches to allow listing the available configuration variables from the psql command line.
  • Vik Fearing sent in another revision of a patch to add a way to make "idle in transaction" sessions terminate via a settable timeout.

par N Bougain le mercredi 2 juillet 2014 à 23h49

vendredi 27 juin 2014

Dimitri Fontaine

PHP Tour 2014

En début de semaine se tenait le PHP Tour 2014 à Lyon, et j'ai eu le privilège d'y être invité afin de présenter comment Utiliser PostgreSQL en 2014.

À l'heure où le NoSQL passe de mode doucement, il est temps de se poser les bonnes questions vis à vis des technologies de bases de données à utiliser, comment et pourquoi. PostgreSQL entre de plein droit dans la case des SGBD relationnels classiques, aussi nous commencerons par étudier ce que de ces outils apportent. Puis nous ferons le tour des fonctionnalités avancées de PostgreSQL, qui le positionnent comme un élément clé de votre architecture d'application.

J'ai ensuite eu le plaisir de présenter ce même contenu dans un contexte très différent grâce aux Brown Bag Lunch, dans les locaux d'une entreprise qui envisageait l'utilisation de MongoDB. Mais ça c'était avant.

Voici une liste des articles contenant des exemples avancés d'utilisation de PostgreSQL, plusieurs d'entre eux ayant servi de support lors de la réalisation des slides utilisés dans la présentation :

 

PHP Tour 2014

June, 27 2014

En début de semaine se tenait le PHP Tour 2014 à Lyon, et j'ai eu le privilège d'y être invité afin de présenter comment Utiliser PostgreSQL en 2014.

 

Last week some PostgreSQL users, contributors and advocates have organized a really great conference in Stockholm, Sweden, where I had the please to give the following talk:

 

In our previous article Aggregating NBA data, PostgreSQL vs MongoDB we spent time comparing the pretty new MongoDB Aggregation Framework with the decades old SQL aggregates. Today, let's showcase more of those SQL aggregates, producing a nice histogram right from our SQL console.

 

When reading the article Crunching 30 Years of NBA Data with MongoDB Aggregation I coulnd't help but think that we've been enjoying aggregates in SQL for 3 or 4 decades already. When using PostgreSQL it's even easy to actually add your own aggregates given the SQL command create aggregate.

 

Back from the FODESM 2014 Conference, here's the slides I've been using for the Advanced Extension Use Cases talk I gave, based on the ongoing work to be found under the Tour of Extensions index in this web site.

 

Back From Dublin

November, 05 2013

Last week I had the pleasure to present two talks at the awesome PostgreSQL Conference Europe. The first one was actually a tutorial about Writing & using Postgres Extensions where we spent 3 hours on what are PostgreSQL Extensions, what you can expect from them, and how to develop a new one. Then I also had the opportunity to present the new version of pgloader in a talk about Migrating from MySQL to PostgreSQL.

 

Denormalizing Tags

October, 24 2013

In our Tour of Extensions today's article is about advanced tag indexing. We have a great data collection to play with and our goal today is to be able to quickly find data matching a complex set of tags. So, let's find out those lastfm tracks that are tagged as blues and rhythm and blues, for instance.

 

At the Open World Forum two weeks ago I had the pleasure to meet with Colin Charles. We had a nice talk about the current state of both MariaDB and PostgreSQL, and even were both interviewed by the Open World Forum Team. The interview is now available online. Dear French readers, it's in English.

 

PostgreSQL is an all round impressive Relational DataBase Management System which implements the SQL standard (see the very useful reference page Comparison of different SQL implementations for details). PostgreSQL also provides with unique solutions in the database market and has been leading innovation for some years now. Still, there's no support for Autonomous Transactions within the server itself. Let's have a look at how to easily implement them with PL/Proxy.

 

Let's get back to our Tour of Extensions that had to be kept aside for awhile with other concerns such as last chance PostgreSQL data recovery. Now that we have a data loading tool up to the task (read about it in the Loading Geolocation Data article) we're going to be able to play with the awesome ip4r extension from RhodiumToad.

 

Last Friday I had the chance to be speaking at the Open World Forum in the NewSQL track, where we had lots of interest and excitement around the NoSQL offerings. Of course, my talk was about explaining how PostgreSQL is Web Scale with some historical background and technical examples about what this database engine is currently capable of.

 

Using trigrams against typos

September, 06 2013

In our ongoing Tour of Extensions we played with earth distance in How far is the nearest pub? then with hstore in a series about trigger, first to generalize Trigger Parameters then to enable us to Auditing Changes with Hstore. Today we are going to work with pg_trgm which is the trigrams PostgreSQL extension: its usage got seriously enhanced in recent PostgreSQL releases and it's now a poor's man Full Text Search engine.

 

In a previous article about Trigger Parameters we have been using the extension hstore in order to compute some extra field in our records, where the fields used both for the computation and for storing the results were passed in as dynamic parameters. Today we're going to see another trigger use case for hstore: we are going to record changes made to our tuples.

 

Trigger Parameters

August, 23 2013

Sometimes you want to compute values automatically at INSERT time, like for example a duration column out of a start and an end column, both timestamptz. It's easy enough to do with a BEFORE TRIGGER on your table. What's more complex is to come up with a parametrized spelling of the trigger, where you can attach the same stored procedure to any table even when the column names are different from one another.

 

There was SQL before window functions and SQL after window functions: that's how powerful this tool is. Being that of a deal breaker unfortunately means that it can be quite hard to grasp the feature. This article aims at making it crystal clear so that you can begin using it today and are able to reason about it and recognize cases where you want to be using window functions.

 

In our recent article about The Most Popular Pub Names we did have a look at how to find the pubs nearby, but didn't compute the distance in between that pub and us. That's because how to compute a distance given a position on the earth expressed as longitude and latitude is not that easy. Today, we are going to solve that problem nonetheless, thanks to PostgreSQL Extensions.

 

In his article titled The Most Popular Pub Names Ross Lawley did show us how to perform some quite interesting geographic queries against MongoDB, using some nice Open Data found at the Open Street Map project.

 

In a recent article here we've been talking about how do do Batch Updates in a very efficient way, using the Writable CTE features available in PostgreSQL 9.1. I sometime read how Common Table Expressions changed the life of fellow DBAs and developers, and would say that Writable CTE are at least the same boost again.

 

In a recent article Craig Kerstiens from Heroku did demo the really useful crosstab extension. That function allows you to pivot a table so that you can see the data from different categories in separate columns in the same row rather than in separate rows. The article from Craig is Pivoting in Postgres.

 

Tonight I had the pleasure to present a talk at the Dublin PostgreSQL User Group using remote technologies. The talk is about how to make the most ouf of PostgreSQL when using SQL as a developer, and tries to convince you to dive into mastering SQL by showing how to solve an application example all in SQL, using window functions and common table expressions.

 

Nearest Big City

May, 02 2013

In this article, we want to find the town with the greatest number of inhabitants near a given location.

 

The Need For Speed

March, 29 2013

Hier se tenait la cinquième édition de la conférence organisée par dalibo, où des intervenants extérieurs sont régulièrement invités. Le thème hier était à la fois clair et très vaste : la performance.

 

Batch Update

March, 15 2013

Performance consulting involves some tricks that you have to teach over and over again. One of them is that SQL tends to be so much better at dealing with plenty of rows in a single statement when compared to running as many statements, each one against a single row.

 

HyperLogLog Unions

February, 26 2013

In the article from yesterday we talked about PostgreSQL HyperLogLog with some details. The real magic of that extension has been skimmed over though, and needs another very small article all by itself, in case you missed it.

 

PostgreSQL HyperLogLog

February, 25 2013

If you've been following along at home the newer statistics developments, you might have heard about this new State of The Art Cardinality Estimation Algorithm called HyperLogLog. This technique is now available for PostgreSQL in the extension postgresql-hll available at https://github.com/aggregateknowledge/postgresql-hll and soon to be in debian.

 

PostgreSQL for developers

November, 02 2012

As Guillaume says, we've been enjoying a great evening conference in Lyon 2 days ago, presenting PostgreSQL to developers. He did the first hour presenting the project and the main things you want to know to start using PostgreSQL in production, then I took the opportunity to be talking to developers to show off some SQL.

 

Reset Counter

October, 05 2012

I've been given a nice puzzle that I think is a good blog article opportunity, as it involves some thinking and window functions.

 

Let's say you need to ALTER TABLE foo ALTER COLUMN bar TYPE bigint;, and PostgreSQL is helpfully telling you that no you can't because such and such views depend on the column. The basic way to deal with that is to copy paste from the error message the names of the views involved, then prepare a script wherein you first DROP VIEW ...; then ALTER TABLE and finally CREATE VIEW again, all in the same transaction.

 

Dynamic Triggers in PLpgSQL

November, 24 2010

You certainly know that implementing dynamic triggers in PLpgSQL is impossible. But I had a very bad night, being up from as soon as 3:30 am today, so that when a developer asked me about reusing the same trigger function code from more than one table and for a dynamic column name, I didn't remember about it being impossible.

 

The drawback of hosting a static only website is, obviously, the lack of comments. What happens actually, though, is that I receive very few comments by direct mail. As I don't get another spam source to cleanup, I'm left unconvinced that's such a drawback. I still miss the low probability of seeing blog readers exchange directly, but I think a tapoueh.org mailing list would be my answer, here...

 

Window Functions example

September, 09 2010

So, when 8.4 came out there was all those comments about how getting window functions was an awesome addition. Now, it seems that a lot of people seeking for help in #postgresql just don't know what kind of problem this feature helps solving. I've already been using them in some cases here in this blog, for getting some nice overview about Partitioning: relation size per “group”.

 

Happy Numbers

August, 30 2010

After discovering the excellent Gwene service, which allows you to subscribe to newsgroups to read RSS content ( blogs, planets, commits, etc), I came to read this nice article about Happy Numbers. That's a little problem that fits well an interview style question, so I first solved it yesterday evening in Emacs Lisp as that's the language I use the most those days.

 

Playing with bit strings

August, 26 2010

The idea of the day ain't directly from me, I'm just helping with a very thin subpart of the problem. The problem, I can't say much about, let's just assume you want to reduce the storage of MD5 in your database, so you want to abuse bit strings. A solution to use them works fine, but the datatype is still missing some facilities, for example going from and to hexadecimal representation in text.

 

This time, we are trying to figure out where is the bulk of the data on disk. The trick is that we're using DDL partitioning, but we want a “nice” view of size per partition set. Meaning that if you have for example a parent table foo with partitions foo_201006 and foo_201007, you would want to see a single category foo containing the accumulated size of all the partitions underneath foo.

 

This time we're having a database where sequences were used, but not systematically as a default value of a given column. It's mainly an historic bad idea, but you know the usual excuse with bad ideas and bad code: the first 6 months it's experimental, after that it's historic.

 

The problem was raised this week on IRC and this time again I felt it would be a good occasion for a blog entry: how to load an XML file content into a single field?

par dim@tapoueh.org (Dimitri Fontaine) le vendredi 27 juin 2014 à 07h33

lundi 16 juin 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 15 juin 2014

Le PgDay 2014 de Portland (Oregon, États-Unis) aura lieu le 6 septembre 2014 : https://wiki.postgresql.org/wiki/PDXPUGDay2014

[ndt: meetup à Paris ce mercredi et PHPTour à Lyon : http://tapoueh.org/blog/2014/06/16-conferences-francaises.html]

Offres d'emplois autour de PostgreSQL en juin

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

Alvaro Herrera a poussé :

Tom Lane a poussé :

  • Fix infinite loop when splitting inner tuples in SPGiST text indexes. Previously, the code used a node label of zero both for strings that contain no bytes beyond the inner tuple's prefix, and for cases where an "allTheSame" inner tuple has to be split to allow a string with a different next byte to be inserted into it. Failing to distinguish these cases meant that if a string ending with the current prefix needed to be inserted into an allTheSame tuple, we got into an infinite loop, because after splitting the tuple we'd descend into the child allTheSame tuple and then find we need to split again. To fix, instead use -1 and -2 as the node labels for these two cases. This requires widening the node label type from "char" to int2, but fortunately SPGiST stores all pass-by-value node label types in their Datum representation, which means that this change is transparently upward compatible so far as the on-disk representation goes. We continue to recognize zero as a dummy node label for reading purposes, but will not attempt to push new index entries down into such a label, so that the loop won't occur even when dealing with an existing index. Per report from Teodor Sigaev. Back-patch to 9.2 where the faulty code was introduced. http://git.postgresql.org/pg/commitdiff/c170655cc81fd5e3c152e951c52247171bb57611
  • Forward-port regression test for bug #10587 into 9.3 and HEAD. Although this bug is already fixed in post-9.2 branches, the case triggering it is quite different from what was under consideration at the time. It seems worth memorializing this example in HEAD just to make sure it doesn't get broken again in future. Extracted from commit 187ae17300776f48b2bd9d0737923b1bf70f606e. http://git.postgresql.org/pg/commitdiff/ab76208e3df6841b3770edeece57d0f048392237
  • Stamp HEAD as 9.5devel. Let the hacking begin ... http://git.postgresql.org/pg/commitdiff/a24c104b9a6e4fef0892b2a0b70f4073378e6544
  • Stamp shared-library minor version numbers for 9.5. http://git.postgresql.org/pg/commitdiff/3bd82dd39eb80587b53f7a3af33bf8b2d49632af
  • Fix ancient encoding error in hungarian.stop. When we grabbed this file off the Snowball project's website, we mistakenly supposed that it was in LATIN1 encoding, but evidently it was actually in LATIN2. This resulted in ő (o-double-acute, U+0151, which is code 0xF5 in LATIN2) being misconverted into õ (o-tilde, U+00F5), as complained of in bug #10589 from Zoltán Sörös. We'd have messed up u-double-acute too, but there aren't any of those in the file. Other characters used in the file have the same codes in LATIN1 and LATIN2, which no doubt helped hide the problem for so long. The error is not only ours: the Snowball project also was confused about which encoding is required for Hungarian. But dealing with that will require source-code changes that I'm not at all sure we'll wish to back-patch. Fixing the stopword file seems reasonably safe to back-patch however. http://git.postgresql.org/pg/commitdiff/fd90b5d574f7b882a35fe345359643cb1ff2e67e
  • Remove unnecessary output expressions from unflattened subqueries. If a sub-select-in-FROM gets flattened into the upper query, then we naturally get rid of any output columns that are defined in the sub-select text but not actually used in the upper query. However, this doesn't happen when it's not possible to flatten the subquery, for example because it contains GROUP BY, LIMIT, etc. Allowing the subquery to compute useless output columns is often fairly harmless, but sometimes it has significant performance cost: the unused output might be an expensive expression, or it might be a Var from a relation that we could remove entirely (via the join-removal logic) if only we realized that we didn't really need that Var. Situations like this are common when expanding views, so it seems worth taking the trouble to detect and remove unused outputs. Because the upper query's Var numbering for subquery references depends on positions in the subquery targetlist, we don't want to renumber the items we leave behind. Instead, we can implement "removal" by replacing the unwanted expressions with simple NULL constants. This wastes a few cycles at runtime, but not enough to justify more work in the planner. http://git.postgresql.org/pg/commitdiff/55d5b3c08279b487cfa44d4b6e6eea67a0af89e4
  • Rename lo_create(oid, bytea) to lo_from_bytea(). The previous naming broke the query that libpq's lo_initialize() uses to collect the OIDs of the server-side functions it requires, because that query effectively assumes that there is only one function named lo_create in the pg_catalog schema (and likewise only one lo_open, etc). While we should certainly make libpq more robust about this, the naive query will remain in use in the field for the foreseeable future, so it seems the only workable choice is to use a different name for the new function. lo_from_bytea() won a small straw poll. Back-patch into 9.4 where the new function was introduced. http://git.postgresql.org/pg/commitdiff/154146d208de7518bb6c8131dc8d2361f7f36f56
  • Add regression test to prevent future breakage of legacy query in libpq. Memorialize the expected output of the query that libpq has been using for many years to get the OIDs of large-object support functions. Although we really ought to change the way libpq does this, we must expect that this query will remain in use in the field for the foreseeable future, so until we're ready to break compatibility with old libpq versions we'd better check the results stay the same. See the recent lo_create() fiasco. http://git.postgresql.org/pg/commitdiff/2dd352d4b01648cac0354c754d9617b2e61f065d
  • Remove inadvertent copyright violation in largeobject regression test. Robert Frost is no longer with us, but his copyrights still are, so let's stop using "Stopping by Woods on a Snowy Evening" as test data before somebody decides to sue us. Wordsworth is more safely dead. http://git.postgresql.org/pg/commitdiff/d2783bee3045d6190dcff0ccf985c4e60a660f99
  • Preserve exposed type of subquery outputs when substituting NULLs. I thought I could get away with hardcoded int4 here, but the buildfarm says differently. http://git.postgresql.org/pg/commitdiff/9d4444a6fc011b7e2ca16386cf9f1c9f25c114e5
  • Adjust largeobject regression test to leave a couple of LOs behind. Since we commonly test pg_dump/pg_restore by seeing whether they can dump and restore the regression test database, it behooves us to include some large objects in that test scenario. I tried to include a comment on one of these large objects to improve the test scenario further ... but it turns out that pg_upgrade fails to preserve comments on large objects, and its regression test notices the discrepancy. So uncommenting that COMMENT is a TODO for later. http://git.postgresql.org/pg/commitdiff/70ad7ed4e88d9de3273f2f875cfaa044a35dfa97
  • Improve tuplestore's error messages for I/O failures. We should report the errno when we get a failure from functions like BufFileWrite. "ERROR: write failed" is unreasonably taciturn for a case that's well within the realm of possibility; I've seen it a couple times in the buildfarm recently, in situations that were probably out-of-disk-space, but it'd be good to see the errno to confirm it. I think this code was originally written without assuming that the buffile.c functions would return useful errno; but most other callers *are* assuming that, and a quick look at the buffile code gives no reason to suppose otherwise. Also, a couple of the old messages were phrased on the assumption that a short read might indicate a logic bug in tuplestore itself; but that code's pretty well tested by now, so a filesystem-level problem seems much more likely. http://git.postgresql.org/pg/commitdiff/6554656ea2043c5bb877b427237dc5ddd7c5e5c8
  • Fix pg_restore's processing of old-style BLOB COMMENTS data. Prior to 9.0, pg_dump handled comments on large objects by dumping a bunch of COMMENT commands into a single BLOB COMMENTS archive object. With sufficiently many such comments, some of the commands would likely get split across bufferloads when restoring, causing failures in direct-to-database restores (though no problem would be evident in text output). This is the same type of issue we have with table data dumped as INSERT commands, and it can be fixed in the same way, by using a mini SQL lexer to figure out where the command boundaries are. Fortunately, the COMMENT commands are no more complex to lex than INSERTs, so we can just re-use the existing lexer for INSERTs. Per bug #10611 from Jacek Zalewski. Back-patch to all active branches. http://git.postgresql.org/pg/commitdiff/c81e63d85f0c2c39d3fdfd8b95fc1ead6fdcb89f
  • Improve predtest.c's ability to reason about operator expressions. We have for a long time been able to prove implications and refutations between clauses structured like "expr op const" with the same subexpression and btree-related operators; for example that "x < 4" implies "x <= 5". The implication machinery is needed to detect usability of partial indexes, and the refutation machinery is needed to implement constraint exclusion. This patch extends that machinery to make proofs for operator expressions involving the same two immutable-but-not-necessarily-just-Const input expressions, ie does "expr1 op1 expr2" prove or refute "expr1 op2 expr2" or "expr2 op2 expr1"? An important example is that we can now prove "x = y" given "y = x", which formerly the code could not deduce unless x or y was a constant. We can make use of the system's knowledge of operator commutator and negator pairs, and can also make use of btree opclass relationships, for example "x < y" implies "x <= y" and refutes "x > y" (notice that neither of these could be proven just from commutator or negator links). Inspired by a gripe from Brian Dunavant. This seems more like a new feature than a bug fix, though, so no back-patch. http://git.postgresql.org/pg/commitdiff/3f8c23c4d31d4a0e801041733deb2c7cfa577b32

Fujii Masao a poussé :

Noah Misch a poussé :

Andres Freund a poussé :

Heikki Linnakangas a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week :-)

Correctifs en attente

  • Ian Lawrence Barwick sent in a patch to add RETURNING PRIMARY KEY syntax for DML.
  • MauMau sent in two revisions of a patch to fix a memory leak in contrib/dblink.
  • Furuya Osamu sent in another revision of a patch to add a synchronous mode to pg_receivexlog.
  • Gurjeet Singh sent in two revisions of a patch to add a GUC to control a child backend's oom_score_adj.
  • Heikki Linnakangas sent in a patch to invent a new internal API for interfacing with SSL, which allows a non-OpenSSL implementation, and another patch to implement same with Windows SChannel.
  • Fabrízio de Royes Mello sent in a patch to implement ALTER TABLE ... SET LOGGED.
  • Sergey Muraviov sent in another revision of a patch to fix wrapping in extended mode in the default pager for psql.
  • Fujii Masao sent in a patch to add a log_replication_command GUC, which causes replication commands to be logged.
  • David Johnston sent in a doc patch for setting configuration parameters.
  • Christoph Berg sent in a patch to fix some lacunae in DSM.
  • SAWADA Masahiko sent in a patch to add line number as prompt option to psql.
  • Noah Misch sent in a patch to fix an issue which arises when conflicting LDAP libraries have been loaded.
  • Jaime Casanova sent in a patch to move the reloptions of views into its own structure.
  • Noah Misch sent in a patch to add a check for that mapping relation oids to filenodes and back is done correctly.
  • Amit Kapila sent in another revision of a patch to prohibit ALTER SYSTEM from setting the data directory, which could result in a recursive definition.
  • Kyotaro HORIGUCHI sent in a patch to make PG to use index for longer pathkeys than index columns when all of the following conditions hold: the index is a unique index; all index columns are NOT NULL, and the index column list is a subset of query_pathkeys.
  • Michael Paquier sent in a patch to add a buffer capture facility to check the consistency of WAL replay.
  • Abhijit Menon-Sen sent in a patch to introduce a function XLogLockBlockRangeForCleanup().
  • Heikki Linnakangas sent in another revision of a patch to implement CSN-based snapshots.
  • Kyotaro HORIGUCHI sent in a patch to add the function to clear backup location information to pg_resetxlog.
  • Rahila Syed sent in a patch to do compression of full-page writes in the WAL using the pglz, lz4 and snappy algorithms.
  • Quan Zongliang sent in a patch to extend PL/pgSQL to define multi variables once, assigning only the last if an assignment is included.
  • Fujii Masao sent in a patch to make log_disconnections PGC_SUSET rather than PGC_BACKEND.
  • Alvaro Herrera sent in a patch to enable replicating DROP commands across servers in logical replication.
  • Alvaro Herrera sent in a patch to make ALTER TABLESPACE MOVE a separate command tag.
  • Petr (PJMODOS) Jelinek sent in a patch to implement a function called varwidth_bucket which does binning with variable bucket width.
  • Petr (PJMODOS) Jelinek sent in a patch to make it possible to change the system identifier of the cluster in pg_control.
  • Kaigai Kouhei sent in another revision of a patch to implement a custom plan API.
  • Alvaro Herrera sent in another revision of a patch to add CREATE support to event triggers.
  • Gurjeet Singh sent in another revision of a patch to implement pg_hibernator.
  • Heikki Linnakangas sent in two more revisions of a patch to change the WAL format and API.
  • Alvaro Herrera sent in a patch to fix a crash which happens when assertions are disabled and WAL_DEBUG turned on.
  • Alvaro Herrera sent in another revision of a patch to implement min_max indexes.
  • Kevin Grittner sent in a patch to add delta relations in AFTER triggers.

par N Bougain le lundi 16 juin 2014 à 19h26

Nicolas Thauvin

pgbench et .pgpass

En faisant quelques tests sur le Foreign Data Wrapper, j'ai (re)fait un peu de pgbench. Pour ceux qui ne savent pas pgbench permet de simuler un benchmark TPC-B, en gros des lectures, des écritures par un certain nombre de sessions concurrentes.

Même si on peut le considérer simpliste par rapport à un Tsung, il est fourni dans les contribs de PostgreSQL et permet de facilement tester un aspect qu'on néglige souvent : les effets de la concurrence sur une base.

J'ai donc monté mon petit environnement de test avec un rôle pour pgbench et sa base dédiée. Pour aller plus vite, j'en ai profité pour configurer un fichier .pgpass, sauf que pgbench n'en voulait pas :

postgres@fdw:~$ cat .pgpass 
localhost:5432:*:bench:Clair!

postgres@fdw:~$ pgbench -h localhost -n -c 10 -j 10 -U bench bench
Password: 
Connection to database "bench" failed:
fe_sendauth: no password supplied

Poutant, la ligne pour l'utilisateur bench est correcte. Si on lui fournit explicitement le port 5432, il tient compte de l'entrée dans le .pgpass :

postgres@fdw:~$ pgbench -h localhost -n -c 10 -j 10 -U bench -p 5432 bench
transaction type: TPC-B (sort of)
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10
number of transactions actually processed: 100/100
tps = 77.993447 (including connections establishing)
tps = 88.977249 (excluding connections establishing)

La lecture de ce thread, qui se conclut sur ce patch, nous éclaire sur ce comportement.

Selon le message de commit, la modification change le comportement de la libpq. Elle n'a pas été backportée sur les autres branches et ne sera donc disponible que pour la libpq de la version 9.4 (ou Debian/Ubuntu qui prend toujours la dernière version de la libpq).

lundi 16 juin 2014 à 10h51

Dimitri Fontaine

Conférences Françaises

Ce mois-ci est particulièrement actif en terme de conférences françaises, puisqu'après un détour à Toulon pour les PGDay.fr nous avons un Meetup à Paris puis une conférence PHP Tour 2014 à Lyon.

PHP Tour 2014

À Lyon avec les développeurs PHP nous aborderons ce que signifie Utiliser PostgreSQL en 2014, et voici le résumé de la conférence :

À l'heure où le NoSQL passe de mode doucement, il est temps de se poser les bonnes questions vis à vis des technologies de bases de données à utiliser, comment et pourquoi. PostgreSQL entre de plein droit dans la case des SGBD relationnels classiques, aussi nous commencerons par étudier ce que de ces outils apportent. Puis nous ferons le tour des fonctionnalités avancées de PostgreSQL, qui le positionnent comme un élément clé de votre architecture d'application.

PostgreSQL Meetup Paris

À Paris dès ce mercredi, nous nous retrouverons dans les locaux de Le Bon Coin pour un Meetup PostgreSQL très chaleureux où Jean-Louis nous parlera de leur utilisation de PostgreSQL, et en particulier du fait que 2TB of RAM ought to be enough for anybody!

par dim@tapoueh.org (Dimitri Fontaine) le lundi 16 juin 2014 à 08h32

dimanche 15 juin 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 8 juin 2014

Postgres Open 2014 : les tickets au tarif "lève-tôt" et les sessions de tutoriels sont en vente : https://postgresopen.org/2014/tickets/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en juin

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

Bruce Momjian a poussé :

Andres Freund a poussé :

  • Use unaligned output in another regression test query to reduce diff noise. Use the unaligned/no rowcount output mode in a regression tests that shows all built-in leakproof functions. Currently a new leakproof function will often change the alignment of all existing functions, making it hard to see the actual difference and creating unnecessary patch conflicts. Noticed while looking over a patch introducing new leakproof functions. http://git.postgresql.org/pg/commitdiff/5eebb8d954adf9d600b1a157682539db1e8a3ada
  • Set the process latch when processing recovery conflict interrupts. Because RecoveryConflictInterrupt() didn't set the process latch anything using the latter to wait for events didn't get notified about recovery conflicts. Most latch users are never the target of recovery conflicts, which explains the lack of reports about this until now. Since 9.3 two possible affected users exist though: The sql callable pg_sleep() now uses latches to wait and background workers are expected to use latches in their main loop. Both would currently wait until the end of WaitLatch's timeout. Fix by adding a SetLatch() to RecoveryConflictInterrupt(). It'd also be possible to fix the issue by having each latch user set set_latch_on_sigusr1. That seems failure prone and though, as most of these callsites won't often receive recovery conflicts and thus will likely only be tested against normal query cancels et al. It'd also be unnecessarily verbose. Backpatch to 9.1 where latches were introduced. Arguably 9.3 would be sufficient, because that's where pg_sleep() was converted to waiting on the latch and background workers got introduced; but there could be user level code making use of the latch pre 9.3. http://git.postgresql.org/pg/commitdiff/44445b28d265fe91fea3da47bd3a8c4007ce87e5
  • Fix longstanding bug in HeapTupleSatisfiesVacuum(). HeapTupleSatisfiesVacuum() didn't properly discern between DELETE_IN_PROGRESS and INSERT_IN_PROGRESS for rows that have been inserted in the current transaction and deleted in a aborted subtransaction of the current backend. At the very least that caused problems for CLUSTER and CREATE INDEX in transactions that had aborting subtransactions producing rows, leading to warnings like: WARNING: concurrent delete in progress within table "..." possibly in an endless, uninterruptible, loop. Instead of treating *InProgress xmins the same as *IsCurrent ones, treat them as being distinct like the other visibility routines. As implemented this separatation can cause a behaviour change for rows that have been inserted and deleted in another, still running, transaction. HTSV will now return INSERT_IN_PROGRESS instead of DELETE_IN_PROGRESS for those. That's both, more in line with the other visibility routines and arguably more correct. The latter because a INSERT_IN_PROGRESS will make callers look at/wait for xmin, instead of xmax. The only current caller where that's possibly worse than the old behaviour is heap_prune_chain() which now won't mark the page as prunable if a row has concurrently been inserted and deleted. That's harmless enough. As a cautionary measure also insert a interrupt check before the gotos in IndexBuildHeapScan() that lead to the uninterruptible loop. There are other possible causes, like a row that several sessions try to update and all fail, for repeated loops and the cost of doing so in the retry case is low. As this bug goes back all the way to the introduction of subtransactions in 573a71a5da backpatch to all supported releases. Reported-By: Sandro Santilli http://git.postgresql.org/pg/commitdiff/621a99a666ba1a27b852dc5ddc0e1b224c388f53
  • Move regression test listing of builtin leakproof functions to opr_sanity.sql. The original location in create_function_3.sql didn't invite the close structinity warranted for adding new leakproof functions. Add comments to the test explaining that functions should only be added after careful consideration and understanding what a leakproof function is. Per complaint from Tom Lane after 5eebb8d954ad. http://git.postgresql.org/pg/commitdiff/e0cb4aa89dd61cb1a76d348ced634681255b6a07
  • Consistently spell a replication slot's name as slot_name. Previously there's been a mix between 'slotname' and 'slot_name'. It's not nice to be unneccessarily inconsistent in a new feature. As a post beta1 initdb now is required in the wake of eeca4cd35e, fix the inconsistencies. Most the changes won't affect usage of replication slots because the majority of changes is around function parameter names. The prominent exception to that is that the recovery.conf parameter 'primary_slotname' is now named 'primary_slot_name'. http://git.postgresql.org/pg/commitdiff/f0c108560bce3a5481db57ffc3743e71b5f7b2d1
  • Fix off-by-one in decoding causing one-record events to be skipped. A ReorderBufferTransaction's end_lsn, the sentPtr advocated by walsender keepalive messages, and the end location remembered by the decoding get_*changes* SQL functions all use the location of the last read record + 1. I.e. the LSN points to the beginning of the next record. That cannot realistically be changed without changing the replication protocol because that's how keepalive messages have worked since 9.0. The bug is that the logic inside the snapshot builder, which decides whether a transaction's contents should be decoded, assumed the start location would point towards the last byte of the last record. The reason this didn't actually cause visible problems is that currently that decision is only made for commit records. Since interesting transactions always have at least one additional record - containing actual data - we'd never skip a transaction. But if there ever were transactions, or other events, with just one record containing important information, we'd skip them after stopping and restarting logical decoding. http://git.postgresql.org/pg/commitdiff/fe7337f2dc3177da19b647fcda3a33b73da82e14

Tom Lane a poussé :

  • Make plpython_unicode regression test work in more database encodings. This test previously used a data value containing U+0080, and would therefore fail if the database encoding didn't have an equivalent to that; which only about half of our supported server encodings do. We could fall back to using some plain-ASCII character, but that seems like it's losing most of the point of the test. Instead switch to using U+00A0 (no-break space), which translates into all our supported encodings except the four in the EUC_xx family. Per buildfarm testing. Back-patch to 9.1, which is as far back as this test is expected to succeed everywhere. (9.0 has the test, but without back-patching some 9.1 code changes we could not expect to get consistent results across platforms anyway.) http://git.postgresql.org/pg/commitdiff/2dfa15de5510b3c19ffb92b512c39d5440a07b1e
  • Bump PG_CONTROL_VERSION for previous 9.4 changes. This should have been done in 6bc8ef0b7f1f1df3998745a66e1790e27424aa0c and/or 50e547096c4858a68abf09894667a542cc418315, but better late than never. If we don't change this then we risk 9.3 pg_controldata or pg_resetxlog being inappropriately used against a 9.4 pg_control file, or vice versa. http://git.postgresql.org/pg/commitdiff/eeca4cd35e284c72b2ea1b4494e64e7738896e81
  • Add btree and hash opclasses for pg_lsn. This is needed to allow ORDER BY, DISTINCT, etc to work as expected for pg_lsn values. We had previously decided to put this off for 9.5, but in view of commit eeca4cd35e284c72b2ea1b4494e64e7738896e81 there's no reason to avoid a catversion bump for 9.4beta2, and this does make a pretty significant usability difference for pg_lsn. Michael Paquier, with fixes from Andres Freund and Tom Lane http://git.postgresql.org/pg/commitdiff/4c8ab1b91d2bd154031f7bc8529b9e6e2f1cc443
  • Tweak new regression test case for better portability. Buildfarm says we get different plans on 32-bit and 64-bit platforms, probably because of MAXALIGN-related differences in memory-consumption calculations. Add some dummy WHERE clauses so that the planner estimates different sizes for the three generate_series() relations; that should stabilize the choice of join order. http://git.postgresql.org/pg/commitdiff/d4d48a5edd9eb28a7f2ee2e4cbe20d984274982e
  • Add defenses against running with a wrong selection of LOBLKSIZE. It's critical that the backend's idea of LOBLKSIZE match the way data has actually been divided up in pg_largeobject. While we don't provide any direct way to adjust that value, doing so is a one-line source code change and various people have expressed interest recently in changing it. So, just as with TOAST_MAX_CHUNK_SIZE, it seems prudent to record the value in pg_control and cross-check that the backend's compiled-in setting matches the on-disk data. Also tweak the code in inv_api.c so that fetches from pg_largeobject explicitly verify that the length of the data field is not more than LOBLKSIZE. Formerly we just had Asserts() for that, which is no protection at all in production builds. In some of the call sites an overlength data value would translate directly to a security-relevant stack clobber, so it seems worth one extra runtime comparison to be sure. In the back branches, we can't change the contents of pg_control; but we can still make the extra checks in inv_api.c, which will offer some amount of protection against running with the wrong value of LOBLKSIZE. http://git.postgresql.org/pg/commitdiff/5f93c37805e7485488480916b4585e098d3cc883

Andrew Dunstan a poussé :

  • Output timestamps in ISO 8601 format when rendering JSON. Many JSON processors require timestamp strings in ISO 8601 format in order to convert the strings. When converting a timestamp, with or without timezone, to a JSON datum we therefore now use such a format rather than the type's default text output, in functions such as to_json(). This is a change in behaviour from 9.2 and 9.3, as noted in the release notes. http://git.postgresql.org/pg/commitdiff/f30015b6d794c15d52abbb3df3a65081fbefb1ed
  • Do not escape a unicode sequence when escaping JSON text. Previously, any backslash in text being escaped for JSON was doubled so that the result was still valid JSON. However, this led to some perverse results in the case of Unicode sequences, These are now detected and the initial backslash is no longer escaped. All other backslashes are still escaped. No validity check is performed, all that is looked for is \uXXXX where X is a hexidecimal digit. This is a change from the 9.2 and 9.3 behaviour as noted in the Release notes. Per complaint from Teodor Sigaev. http://git.postgresql.org/pg/commitdiff/0ad1a816320a2b539a51628e2a0b1e83ff096b1d
  • Use EncodeDateTime instead of to_char to render JSON timestamps. Per gripe from Peter Eisentraut and Tom Lane. The output is slightly different, but still ISO 8601 compliant: to_char doesn't output the minutes when time zone offset is an integer number of hours, while EncodeDateTime outputs ":00". The code is slightly adapted from code in xml.c http://git.postgresql.org/pg/commitdiff/ab14a73a6ca5cc4750f0e00a48bdc25a2293034a

Peter Eisentraut a poussé :

Fujii Masao a poussé :

  • Save pg_stat_statements statistics file into $PGDATA/pg_stat directory at shutdown. 187492b6c2e8cafc5b39063ca3b67846e8155d24 changed pgstat.c so that the stats files were saved into $PGDATA/pg_stat directory when the server was shutdowned. But it accidentally forgot to change the location of pg_stat_statements permanent stats file. This commit fixes pg_stat_statements so that its stats file is also saved into $PGDATA/pg_stat at shutdown. Since this fix changes the file layout, we don't back-patch it to 9.3 where this oversight was introduced. http://git.postgresql.org/pg/commitdiff/654e8e444749f053c3bf3fd543d10deb6aa6dd09
  • Add description of pg_stat directory into doc. Back-patch to 9.3 where pg_stat directory was introduced. http://git.postgresql.org/pg/commitdiff/c8c9c1f5a3fcd2db0f94d3126e142dee91c53632

Heikki Linnakangas a poussé :

  • Adjust SP-GiST WAL record formats to reduce alignment padding. The way the code was written, the padding was copied from uninitialized memory areas.. Because the structs are local variables in the code where the WAL records are constructed, making them larger and zeroing the padding bytes would not make the code very pretty, so rather than fixing this directly by zeroing out the padding bytes, it seems more clear to not try to align the tuples in the WAL records. The redo functions are taught to copy the tuple header to a local variable to avoid unaligned access. Stable-branches have the same problem, but we can't change the WAL format there, so fix in master only. Reading a few random extra bytes at the stack is harmless in practice, so it's not worth crafting a different back-patchable fix. Per reports from Kevin Grittner and Andres Freund, using clang static analyzer and Valgrind, respectively. http://git.postgresql.org/pg/commitdiff/8776faa81cb651322b8993422bdd4633f1f6a487

Correctifs rejetés (à ce jour)

  • No one was disappointed this week :-)

Correctifs en attente

  • Haribabu Kommi sent in another WIP patch to add a priority or cache table.
  • Haribabu Kommi and Andres Freund traded patches to allow adding aggregates for the inet data type, among others.
  • David Rowley sent in another revision of a patch to enable removing certain semi- and anti-joins.
  • Gurjeet Singh sent in another revision of a patch to implement pg_hibernator.
  • Vik Fearing sent in two revisions of a patch to implement a timeout for "idle in transaction" scenarios.
  • Fujii Masao sent in a patch to fix an issue where pg_basebackup fails to back up large (>= 4GiB) files because it uses an integer to measure their size.
  • Jeff Janes sent in a patch to fix an issue where anti-wraparound VACUUM could not finish when the stop limit was reached.
  • Abhijit Menon-Sen sent in a patch to allow printing summary statistics instead of individual records via a new --stats option to pg_xlogdump.
  • Amul Sul sent in a patch to pass the Form_pg_attribute to examine_attribute rather than the Relation structure.
  • Pavel Stehule sent in two more revisions of a patch to allow psql to show only failed queries.
  • Andres Freund sent in a patch to hide 'Execution time' in EXPLAIN (COSTS OFF).
  • Peter Eisentraut sent in a patch to allow the regression tests for client programs to work in VPATH builds.
  • Tom Lane sent in a patch to add a new dummy value to distinguish the node label of a pushed-down allTheSame tuple from end-of-string in SP-GiST.
  • Andres Freund sent in a patch to make "slot_name" the standard spelling for all user-visible things.
  • Marc Mamin sent in another revision of a patch to add "pivot aggregation" to contrib/intarray.
  • Furuya Osamu sent in two revisions of a patch to add a synchronous mode to pg_receivelog.
  • Peter Geoghegan sent in a patch to implement a poor-man's hyperloglog.
  • Tom Lane sent in a patch to suppress unused query output columns.
  • Amit Kapila sent in another revision of a patch to scale shared buffer eviction.
  • Naoya Anzai sent in a patch to fix a condition where the message "cancelling statement due to user request error" occurs even though the transaction has committed.
  • David Rowley sent in a patch to allow NOT IN to use anti-joins.
  • Noah Misch sent in another revision of a patch to secure "make check"
  • Kevin Grittner sent in a patch to fix an issue with NUMA systems where memory at start would stay too non-uniform by default.
  • Noah Misch sent in a patch to fix a problem in the MinGW/Cygwin builds.
  • Noah Misch sent in a patch to fix the emacs configuration file so it works with both the newest GNU emacs and older ones.
  • John Lumby sent in another revision of a patch to enable extended prefetching using asynchronous I/O.

par N Bougain le dimanche 15 juin 2014 à 10h50

Nouvelles hebdomadaires de PostgreSQL - 1er juin 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en juin

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

Andres Freund a poussé :

  • Fix pg_recvlogical to accept the documented -I instead only --startpos. The bug was caused by omitting 'I:' from the short argument list togetopt_long(). To make similar bugs in the future less likely reorderoptions in --help, long and short option lists to be in the same,alphabetical within groups, order. Report and fix by Michael Paquier, some additional reordering by me. http://git.postgresql.org/pg/commitdiff/bf2e70ba6c0175e5a676b6aa37e49915d8918a63
  • Don't pay heed to wal_sender_timeout while creating a decoding slot. Sometimes CREATE_REPLICATION_SLOT ... LOGICAL ... needs to wait forfurther WAL using WalSndWaitForWal(). That used to always respectwal_sender_timeout and kill the session when waiting long enoughbecause no feedback/ping messages can be sent while the slot is stillbeing created. Introduce the notion that last_reply_timestamp = 0 means that thewalsender currently doesn't need timeout processing to avoid thatproblem. Use that notion for CREATE_REPLICATION_SLOT ... LOGICAL. Bugreport and initial patch by Steve Singer, revised by me. http://git.postgresql.org/pg/commitdiff/21d48d66c8be053ef5ce0474bb30f8a91b7c3dd6
  • Improvements to the replication protocol documentation. Document the CREATE_REPLICATION_SLOT's output_plugin parameter; thatSTART_REPLICATION ... LOGICAL takes parameters; that START_REPLICATION... LOGICAL uses the same messages as ... PHYSICAL; and be moreconsistent with the usage of <literal/>. Michael Paquier, with some additional changes by me. http://git.postgresql.org/pg/commitdiff/a57509821c3147b2bf57560e3bce1641d78a7519

Tom Lane a poussé :

  • Avoid unportable usage of sscanf(UINT64_FORMAT). On Mingw, it seems that scanf() doesn't necessarily accept the same formatcodes that printf() does, and in particular it may fail to recognize %llu even though printf() does. Since configure only probes printf() behaviorwhile setting up the INT64_FORMAT macros, this means it's unsafe to usethose macros with scanf(). We had only one instance of such a codingpattern, in contrib/pg_stat_statements, so change that code to avoidthe problem. Per buildfarm warnings. Back-patch to 9.0 where the troublesome code was introduced. Michael Paquier http://git.postgresql.org/pg/commitdiff/9d7ded0f4277f5c0063eca8e871a34e2355a8371
  • Support BSD and e2fsprogs UUID libraries alongside OSSP UUID library. Allow the contrib/uuid-ossp extension to be built atop any one of these three popular UUID libraries. (The extension's name is now arguably a misnomer, but we'll keep it the same so as not to cause unnecessary compatibility issues for users.) We would not normally consider a change like this post-beta1, but the issue has been forced by our upgrade to autoconf 2.69, whose more rigorous header checks are causing OSSP's header files to be rejected on some platforms. It's been foreseen for some time that we'd have to move away from depending on OSSP UUID due to lack of upstream maintenance, so this is a down payment on that problem. While at it, add some simple regression tests, in hopes of catching any major incompatibilities between the three implementations. Matteo Beccati, with some further hacking by me http://git.postgresql.org/pg/commitdiff/b8cc8f94730610c0189aa82dfec4ae6ce9b13e34
  • Propagate system identifier generation improvement into pg_resetxlog. Commit 5035701e07e8bd395aa878465a102afd7b74e8c3 improved xlog.c's method for creating a database system identifier, but I neglected to fix the copy of that code appearing in pg_resetxlog.c. Spotted by Andres Freund. http://git.postgresql.org/pg/commitdiff/4bcb3946249cbeed157ffd545a804913cfce749b
  • Fix obsolete config-module-exclusion logic in vcregress.pl. The recent addition of regression tests to uuid-ossp exposed the fact that the MSVC build system wasn't being consistent about whether it was building/testing that contrib module, ie, it would try to test the module even when it hadn't built it. The same hazard was latent for sslinfo. For the moment I just copied the more up-to-date logic from point A to point B, but this is screaming for refactoring. Per buildfarm results. http://git.postgresql.org/pg/commitdiff/8600031147cee7845e1ad9e64304e1f5fe585515
  • pg_lsn should not be marked typispreferred. In general it's not a good idea for built-in types in the 'U' category to be marked preferred; they could draw behavior away from user-defined types with similarly-named operators. pg_lsn is probably at low risk of that right now given the lack of casts between it and other types, but that doesn't make this marking OK. Ordinarily we'd bump catversion when changing any predefined catalog contents like this, but since we're past beta1, the costs of a forced initdb seem to outweigh the benefits of guaranteed behavioral consistency. There's not any known behavioral impact today anyway --- this is more in the nature of being sure there's not problems in future. Per an off-list complaint from Thomas Fanghaenel. http://git.postgresql.org/pg/commitdiff/ec3357a3bc37d9108b2cf2db96499f83f1a93df1
  • Fix stack clobber in new uuid-ossp code. The V5 (SHA1 hashing) code wrote 20 bytes into a 16-byte local variable. This had accidentally failed to fail in my testing and Matteo's, but buildfarm results exposed the problem. http://git.postgresql.org/pg/commitdiff/2103218dd4a0c6a44d05c09c066da20e1c2360fb
  • Improve regression tests for uuid-ossp. On reflection, the timestamp-advances test might fail if we're unlucky enough for the time_mid field to change between two calls, since uuid_cmp is just bytewise comparison and the field ordering has more significant fields later. Build some field extraction functions so we can do a more honest test of that. Also check that the version and reserved fields contain what they should. http://git.postgresql.org/pg/commitdiff/c0f27628d2fb53e8e5d190da2132739a2bb2f500
  • Revert "Fix bogus %name-prefix option syntax in all our Bison files." This reverts commit 45b7abe59e9485657ac9380f35d2d917dd0da25b. It turns out that the %name-prefix syntax without "=" does not work at all in pre-2.4 Bison. We are not prepared to make such a large jump in minimum required Bison version just to suppress a warning message in a version hardly any developers are using yet. When 3.0 gets more popular, we'll figure out a way to deal with this. In the meantime, BISONFLAGS=-Wno-deprecated is recommendable for anyone using 3.0 who doesn't want to see the warning. http://git.postgresql.org/pg/commitdiff/71ed8b3ca76cd720f4013c3c20f0d2706583ab9a
  • Fix uuid-ossp regression tests based on buildfarm feedback. The previous version of these tests expected uuid_generate_v1() to always emit MAC addresses with the local-admin and multicast address bits zero. However, several of the buildfarm critters are reporting values with the local-admin bit set. (Perhaps they're running inside VMs or jails.) And a couple are reporting values with the multicast bit set, probably meaning that the UUID library couldn't read the system MAC address. Also, it emerges that if OSSP UUID can't read the system MAC address, it falls back to V1MC behavior wherein the whole node field gets randomized each time, breaking the test that expected the node field to remain stable in V1 output. (It looks like e2fs doesn't behave that way, though.) It's not entirely clear why we can't get a system MAC address, since the buildfarm scripts would not work without internet access. Nonetheless, the regression tests had better cope with the case, so adjust the tests to expect these behaviors. http://git.postgresql.org/pg/commitdiff/25dd07e0f6ba1aef0a6802474112b5bcce621ea4
  • Fix bogus %name-prefix option syntax in all our Bison files. %name-prefix doesn't use an "=" sign according to the Bison docs, but it silently accepted one anyway, until Bison 3.0. This was originally a typo of mine in commit 012abebab1bc72043f3f670bf32e91ae4ee04bd2, and we seem to have slavishly copied the error into all the other grammar files. Per report from Vik Fearing; analysis by Peter Eisentraut. Back-patch to all active branches, since somebody might try to build a back branch with up-to-date tools. http://git.postgresql.org/pg/commitdiff/45b7abe59e9485657ac9380f35d2d917dd0da25b
  • When using the OSSP UUID library, cache its uuid_t state object. The original coding in contrib/uuid-ossp created and destroyed a uuid_t object (or, in some cases, even two of them) each time it was called. This is not the intended usage: you're supposed to keep the uuid_t object around so that the library can cache its state across uses. (Other UUID libraries seem to keep equivalent state behind-the-scenes in static variables, but OSSP chose differently.) Aside from being quite inefficient, creating a new uuid_t loses knowledge of the previously generated UUID, which in theory could result in duplicate V1-style UUIDs being created on sufficiently fast machines. On at least some platforms, creating a new uuid_t also draws some entropy from /dev/urandom, leaving less for the rest of the system. This seems sufficiently unpleasant to justify back-patching this change. http://git.postgresql.org/pg/commitdiff/c941aed96b482e32dc3a8eba66b298824e7adc39
  • On OS X, link libpython normally, ignoring the "framework" framework. As of Xcode 5.0, Apple isn't including the Python framework as part of the SDK-level files, which means that linking to it might fail depending on whether Xcode thinks you've selected a specific SDK version. According to their Tech Note 2328, they've basically deprecated the framework method of linking to libpython and are telling people to link to the shared library normally. (I'm pretty sure this is in direct contradiction to the advice they were giving a few years ago, but whatever.) Testing says that this approach works fine at least as far back as OS X 10.4.11, so let's just rip out the framework special case entirely. We do still need a special case to decide that OS X provides a shared library at all, unfortunately (I wonder why the distutils check doesn't work ...). But this is still less of a special case than before, so it's fine. Back-patch to all supported branches, since we'll doubtless be hearing about this more as more people update to recent Xcode. http://git.postgresql.org/pg/commitdiff/20561acf93d32b7d7fdd59d054344b2e341d6aa0

Robert Haas a poussé :

Bruce Momjian a poussé :

Magnus Hagander a poussé :

  • Ensure cleanup in case of early errors in streaming base backups. Move the code that sends the initial status information as well as the calculation of paths inside the ENSURE_ERROR_CLEANUP block. If this code failed, we would "leak" a counter of number of concurrent backups, thereby making the system always believe it was in backup mode. This could happen if the sending failed (which it probably never did given that the small amount of data to send would never cause a flush) or if the psprintf calls ran out of memory. Both are very low risk, but all operations after do_pg_start_backup should be protected. http://git.postgresql.org/pg/commitdiff/8232d6df4c943a30c08e65d7ea893cb762bc5612

Heikki Linnakangas a poussé :

Andrew Dunstan a poussé :

  • Improve the efficiency of certain jsonb get operations. Instead of iterating over jsonb structures, use the inbuilt functions findJsonbValueFromContainerLen() and getIthJsonbValueFromContainer() to extract values directly. These functions use algorithms that are O(n log n) and O(1) respectively, whereas iterating is O(n), so we should see considerable speedup here. Teodor Sigaev. http://git.postgresql.org/pg/commitdiff/1a4174a498a15a848d4c4c50a3a9ef500926e4bd

Correctifs rejetés (à ce jour)

  • No one was disappointed this week :-)

Correctifs en attente

  • Peter Geoghegan sent in a patch to describe how Lehman and Yao's work applies to nbtree.
  • Michael Paquier sent in a patch to extend MSVC scripts to support --with-extra-version.
  • Amit Kapila sent in a patch to disable a potential privilege escalation in ALTER SYSTEM.
  • Michael Paquier and Bruce Momjian traded patches to fix a situation where popen and pclose redefinitions are causing many warning in Windows builds.
  • Michael Paquier sent in a doc patch to notify that non-dynamic background workers need to set bgw_notify_pid to 0.
  • Fabrízio de Royes Mello sent in two revisions of a patch to include pg_llog and pg_stat in the appropriate parts of the documentation.
  • John Lumby and Claudio Freire traded patches to allow extended prefetch using asynchronous I/O on Linux and other systems which support same.
  • David Rowley sent in another revision of a patch to allow join removal for more join types.
  • Andrew Dunstan sent in a patch to make it possible to handle timestamps in JSON correctly.
  • Vik Fearing sent in patches to: refactor createdb/alterdb grammars, making all of the options non-keywords that don't otherwise need to be keywords, and the second, to allow SQL access to database attributes via CREATE and ALTER.
  • Fujii Masao sent in a patch to save the pg_stat_statements statistics file in $PGDATA.
  • Peter Geoghegan sent in a patch to fix a formatting bug in to_char().
  • Andrew Dunstan sent in a patch to fix some of the issues to do with unicode escapes Teodor Sigaev raised recently.
  • Ali Akbar sent in a patch to fix an issue with nested or repeated xpath().
  • Heikki Linnakangas sent in a WIP patch to create CSN-based snapshots.
  • Teodor Sigaev sent in two revisions of a patch to fix a memory leak in SP-GiST.
  • Amit Kapila sent in a patch to make some information available to pg_xlogdump for recovery testing.
  • Marc Mamin sent in a patch to add three functions to contrib/intarray to count[distinct] event IDs into arrays, mimicking a column store.
  • David Rowley sent in a patch to teach Var about NOT NULL.

par N Bougain le dimanche 15 juin 2014 à 10h46

Nouvelles hebdomadaires de PostgreSQL - 25 mai 2014

Félicitations à Andres Freund pour son nouveau statut de commiteur PostgreSQL.

Les Commitfests pour la 9.5 commenceront les 15 juin, 15 août, 15 octobre, 15 décembre et 15 février. La Beta sera disponible en juin 2015.

La première PgConf suisse aura lieu le 24 juin 2014 au HSR Hochschule für Technik Rapperswil. http://www.postgres-conference.ch/

Le deuxième Hangout singapourien aura lieu le 28 mai 2014 à 16h30(SGT). RSVP : https://plus.google.com/u/0/events/cvpgsaauh8i1l8a7k22jqu5t0jo

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mai

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

Bruce Momjian a poussé :

Tom Lane a poussé :

  • Fix non-C89-compatible coding in pgbench. C89 says that compound initializers may only contain constant expressions; a restriction violated by commit 89d00cbe. While we've had no actual field complaints about this, C89 is still the project standard, and it's not saving all that much code to break compatibility here. So let's adhere to the old restriction. In passing, replace a bunch of hardwired constants "256" with sizeof(target-variable), just because the latter is more readable and less breakable. And const-ify where possible. Back-patch to 9.3 where the nonportable code was added. Andres Freund and Tom Lane http://git.postgresql.org/pg/commitdiff/e41cc470bb57a0f3a765fa44b77ef104b5a5a01e
  • Update obsolete comment. Peter Geoghegan http://git.postgresql.org/pg/commitdiff/a0841ecd2518d4505b96132b764b918ab5d21ad4
  • Prevent auto_explain from changing the output of a user's EXPLAIN. Commit af7914c6627bcf0b0ca614e9ce95d3f8056602bf, which introduced the EXPLAIN (TIMING) option, for some reason coded explain.c to look at planstate->instrument->need_timer rather than es->timing to decide whether to print timing info. However, the former flag might get set as a result of contrib/auto_explain wanting timing information. We certainly don't want activation of auto_explain to change user-visible statement behavior, so fix that. Also fix an independent bug introduced in the same patch: in the code path for a never-executed node with a machine-friendly output format, if timing was selected, it would fail to print the Actual Rows and Actual Loops items. Per bug #10404 from Tomonari Katsumata. Back-patch to 9.2 where the faulty code was introduced. http://git.postgresql.org/pg/commitdiff/e416830a296f440acd67afd8599dde5411021fc4
  • Allow total number of transactions in pgbench to exceed INT_MAX. Change the total-transactions counters from int32 to int64 to accommodate cases where we do more than 2^31 transactions during a run. This patch does not change the INT_MAX limit on explicit "-t" parameters, but it does allow the product of the -t and -c parameters to exceed INT_MAX, or allow a -T limit that is large enough that more than 2^31 transactions can be completed. While pgbench did not actually fail in such cases, it did print an incorrect total-transactions count, and some of the derived numbers such as TPS would have been wrong as well. Tomas Vondra http://git.postgresql.org/pg/commitdiff/9a65fb350717360c505de9df411024d47e55710b

Fujii Masao a poussé :

Heikki Linnakangas a poussé :

Peter Eisentraut a poussé :

Robert Haas a poussé :

  • Remove unnecessary cleanup code. This is all inside a block guarded by op == DSM_OP_ATTACH, so it can never be the case that op == DSM_OP_CREATE. Reported by Coverity. http://git.postgresql.org/pg/commitdiff/11ad3b35c25de6edcaa8a7695cb1d58650c546d3
  • Andres Freund pushed
  • Silence a couple of spurious valgrind warnings in inval.c. Define padding bytes in SharedInvalidationMessage structs to be defined. Otherwise the sinvaladt.c ringbuffer, which is accessed by multiple processes, will cause spurious valgrind warnings about undefined memory being used. That's because valgrind remembers the undefined bytes from the last local process's store, not realizing that another process has written since, filling the previously uninitialized bytes. http://git.postgresql.org/pg/commitdiff/0564bbe7a1690f025f4424d5a12cb6af9d428c48
  • Don't allocate memory inside an Assert() iff in a critical section. HeapTupleHeaderGetCmax() asserts that it is only used if the tuple has been updated by the current transaction. That check is correct and sensible but requires allocating memory if xmax is a multixact. When wal_level is set to logical cmax needs to be included in a wal record , generated inside a critical section, which can trigger the assertion added in 4a170ee9e. Reported-By: Steve Singer http://git.postgresql.org/pg/commitdiff/9fa93530c878a0e23147a65f7d9a62802b22a995

Correctifs rejetés (à ce jour)

  • No one was disappointed this week :-)

Correctifs en attente

  • Jeff Davis sent in a patch to add some Assert()s to the range types SP-GiST code.
  • Teodor Sigaev sent in a patch to fix a memory leak in SP-GiST.
  • Yuto HAYAMIZU sent in a patch intended to fix a bug where the postmaster crashes with assertion and LWLOCK_STATS enabled.
  • Michael Paquier sent in two revisions of a doc patch to add a mention of logical slots in CREATE_REPLICATION_SLOT for the replication protocol.
  • David Rowley sent in another revision of a patch to allow join removals for more join types.
  • Shigeru HANADA sent in a patch to fix worker_spi to run as non-dynamic background worker.
  • Vik Fearing sent in a patch to add SQL access to database attributes.
  • Ronan Dunklau sent in another revision of a patch to add IMPORT FOREIGN SCHEMA.
  • Anastasia Lubennikova sent in a patch to add index-only scans for GiST.
  • Michael Paquier and Andres Freund traded patches to fix an issue where pg_recvlogical is not accepting -I to specify start LSN position.
  • Matteo Beccati sent in a patch to replace OSSP-UUID for Linux and BSD.
  • Michael Paquier sent in a patch to fix some typos in Solution.pm, one of the MSVC scripts.

par N Bougain le dimanche 15 juin 2014 à 10h44

Nouvelles hebdomadaires de PostgreSQL - 18 mai 2014

PostgreSQL 9.4 Beta 1 est disponible. TEST ! http://www.postgresql.org/developer/beta

La PostgreSQL Conference Europe 2014 aura lieu du 21 au 24 octobre à l'hôtel Miguel Angel à Madrid (Espagne). L'appel à conférenciers est lancé en direction des orateurs anglo- et hispanophones. http://2014.pgconf.eu/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mai

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é :

  • Free PQresult on error in pg_receivexlog. The leak is fairly small and rare, but a leak nevertheless. Per Coverity report. Backpatch to 9.2, where pg_receivexlog was added. pg_basebackup shares the code, but it always exits on error, so there is no real leak. http://git.postgresql.org/pg/commitdiff/c890b488063e23f12b5ab01c9a6d254a034fcea2
  • Initialize padding bytes in btree_gist varbit support. The code expands a varbit gist leaf key to a node key by copying the bit data twice in a varlen datum, as both the lower and upper key. The lower key was expanded to INTALIGN size, but the padding bytes were not initialized. That's a problem because when the lower/upper keys are compared, the padding bytes are used compared too, when the values are otherwise equal. That could lead to incorrect query results. REINDEX is advised for any btree_gist indexes on bit or bit varying data type, to fix any garbage padding bytes on disk. Per Valgrind, reported by Andres Freund. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/540ac7cea919623f691b20892ccc50e5e33b5009
  • Fix harmless access to uninitialized memory. When cache invalidations arrive while ri_LoadConstraintInfo() is busy filling a new cache entry, InvalidateConstraintCacheCallBack() compares the - not yet initialized - oidHashValue field with the to-be-invalidated hash value. To fix, check whether the entry is already marked as invalid. Andres Freund http://git.postgresql.org/pg/commitdiff/f35aef415aa755c4e99f8c0ef83f9d14dbc48bb4
  • Silence warnings about redefining popen on Mingw-w64. Mingw-w64 headers map popen/pclose to _popen and _pclose, but we want to use our popen wrapper rather than the Mingw-w64. #undef the Mingw's version. http://git.postgresql.org/pg/commitdiff/a82a17475d8a8a91df5fd1448b0f93772f590ecd
  • Misc message style and doc fixes. Euler Taveira http://git.postgresql.org/pg/commitdiff/ff810b4928882bfdc4ebe1ce603c47830aba3132
  • Fix race condition in preparing a transaction for two-phase commit. To lock a prepared transaction's shared memory entry, we used to mark it with the XID of the backend. When the XID was no longer active according to the proc array, the entry was implicitly considered as not locked anymore. However, when preparing a transaction, the backend's proc array entry was cleared before transfering the locks (and some other state) to the prepared transaction's dummy PGPROC entry, so there was a window where another backend could finish the transaction before it was in fact fully prepared. To fix, rewrite the locking mechanism of global transaction entries. Instead of an XID, just have simple locked-or-not flag in each entry (we store the locking backend's backend id rather than a simple boolean, but that's just for debugging purposes). The backend is responsible for explicitly unlocking the entry, and to make sure that that happens, install a callback to unlock it on abort or process exit. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/bb38fb0d43c8d7ff54072bfd8bd63154e536b384
  • Handle duplicate XIDs in txid_snapshot. The proc array can contain duplicate XIDs, when a transaction is just being prepared for two-phase commit. To cope, remove any duplicates in txid_current_snapshot(). Also ignore duplicates in the input functions, so that if e.g. you have an old pg_dump file that already contains duplicates, it will be accepted. Report and fix by Jan Wieck. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/8f9b9590d79fc1fc1ad08b207401acfdbb0bfac7
  • Fix a couple of bugs in pg_recvlogical output to stdout. Don't close stdout on SIGHUP. Also, when a SIGHUP is received, close the file immediately, rather than only after receiving some more data from the server. Rename a variable, to avoid mentally dealing with double negatives (not unsynced means synced). http://git.postgresql.org/pg/commitdiff/00c26b6a60e7ceed29ddae34b0a69fe945c08f81
  • Initialize tsId and dbId fields in WAL record of COMMIT PREPARED. Commit dd428c79 added dbId and tsId to the xl_xact_commit struct but missed that prepared transaction commits reuse that struct. Fix that. Because those fields were left unitialized, replaying a commit prepared WAL record in a hot standby node would fail to remove the relcache init file. That can lead to "could not open file" errors on the standby. Relcache init file only needs to be removed when a system table/index is rewritten in the transaction using two phase commit, so that should be rare in practice. In HEAD, the incorrect dbId/tsId values are also used for filtering in logical replication code, causing the transaction to always be filtered out. Analysis and fix by Andres Freund. Backpatch to 9.0 where hot standby was introduced. http://git.postgresql.org/pg/commitdiff/07a4a93a0e35a778c77ffbbbc18de29e859e18f0
  • Open output file before sleeping in pg_recvlogical. Let's complain about e.g an invalid path or permission problem sooner rather than later. Before this patch, we would only try to open the output file after receiving the first decoded message from the server. http://git.postgresql.org/pg/commitdiff/e7873b74d9c1bb7a325960f52043db6de88e6e66
  • Fix thinko in logical decoding of commit-prepared records. The decoding of prepared transaction commits accidentally used the XID of the transaction performing the COMMIT PREPARED, not the XID of the prepared transaction. Before bb38fb0d43c8d that lead to those transactions not being decoded, afterwards to a assertion failure. http://git.postgresql.org/pg/commitdiff/03e2b1017c0d360cae0b4d0a8bf5d6924536c834
  • Fix test_decoding test case's check that slot has been dropped. pg_stat_replication shows connected replication clients. The ddl test case never has any replication clients connected, so querying pg_stat_replication is pointless. To check that a slot has been dropped correctly, query pg_replication_slots instead. Andres Freund http://git.postgresql.org/pg/commitdiff/afd0fcbc5d421454e7d55d949cca0721c9c135c3
  • Add test case for logical decoding of prepared transactions. Andres Freund http://git.postgresql.org/pg/commitdiff/d900e192a3ef2dd73fae4522c35aadce6d72a534
  • Update README, we don't do post-recovery cleanup actions anymore. transam/README explained how B-tree incomplete splits were tracked and fixed after recovery, as an example of handling complex actions that need multiple WAL records, but that's not how it works anymore. Explain the new paradigm. http://git.postgresql.org/pg/commitdiff/a3655dd4a5cee3917a7d1766e07e36013e7e8835

Fujii Masao a poussé :

Tom Lane a poussé :

  • Ignore config.pl and buildenv.pl in src/tools/msvc. config.pl and buildenv.pl can be used to customize build settings when using MSVC. They should never get committed into the common source tree. Back-patch to 9.0; it looks like the rules were different in 8.4. Michael Paquier http://git.postgresql.org/pg/commitdiff/73011f35eca7f4a3973289d5813648abbc1074a0
  • Be more wary in choice of timezone names to test make_timestamptz with. America/Metlakatla hasn't been in the IANA database all that long, so some installations might not have it. It does seem worthwhile to test with a fractional-minute GMT offset, but we can get that from almost any pre-1900 date; I chose Europe/Paris, whose LMT offset from Greenwich should be pretty darn well established. Also, assuming that Mars/Mons_Olympus will never be in the IANA database seems less than future-proof, so let's use a more fanciful location for the bad-zone-name check. Per complaint from Christoph Berg. http://git.postgresql.org/pg/commitdiff/66b737cd9a72e10df4de1867ae7b675bc48d478c
  • Make initdb throw error for bad locale values. Historically we've printed a complaint for a bad locale setting, but then fallen back to the environment default. Per discussion, this is not such a great idea, because rectifying an erroneous locale choice post-initdb (perhaps long after data has been loaded) could be enormously expensive. Better to complain and give the user a chance to double-check things. The behavior was particularly bad if the bad setting came from environment variables rather than a bogus command-line switch: in that case not only was there a fallback to C/SQL_ASCII, but the printed complaint was quite unhelpful. It's hard to be entirely sure what variables setlocale looked at, but we can at least give a hint where the problem might be. Per a complaint from Tomas Vondra. http://git.postgresql.org/pg/commitdiff/31a263237fa3eb6f36d58335fbdb82bab3136db3
  • Code review for recent changes in relcache.c. rd_replidindex should be managed the same as rd_oidindex, and rd_keyattr and rd_idattr should be managed like rd_indexattr. Omissions in this area meant that the bitmapsets computed for rd_keyattr and rd_idattr would be leaked during any relcache flush, resulting in a slow but permanent leak in CacheMemoryContext. There was also a tiny probability of relcache entry corruption if we ran out of memory at just the wrong point in RelationGetIndexAttrBitmap. Otherwise, the fields were not zeroed where expected, which would not bother the code any AFAICS but could greatly confuse anyone examining the relcache entry while debugging. Also, create an API function RelationGetReplicaIndex rather than letting non-relcache code be intimate with the mechanisms underlying caching of that value (we won't even mention the memory leak there). Also, fix a relcache flush hazard identified by Andres Freund: RelationGetIndexAttrBitmap must not assume that rd_replidindex stays valid across index_open. The aspects of this involving rd_keyattr date back to 9.3, so back-patch those changes. http://git.postgresql.org/pg/commitdiff/b23b0f5588d2e2f15edff66e072e339a8c9616a7
  • Fix version check for pg_upgrade line type crosscheck. Per buildfarm. http://git.postgresql.org/pg/commitdiff/4456763c78d2a94c42e67ce788fbe94a07aa12dc
  • In initdb, ensure stdout/stderr buffering behavior is what we expect. Since this program may print to either stdout or stderr, the relative ordering of its messages depends on the buffering behavior of those files. Force stdout to be line-buffered and stderr to be unbuffered, ensuring that the behavior will match standard Unix interactive behavior, even when stdout and stderr are rerouted to a file. Per complaint from Tomas Vondra. The particular case he pointed out is new in HEAD, but issues of the same sort could arise in any branch with other error messages, so back-patch to all branches. I'm unsure whether we might not want to do this in other client programs as well. For the moment, just fix initdb. http://git.postgresql.org/pg/commitdiff/2dc4f011fd61501cce507be78c39a2677690d44b
  • Fix unportable setvbuf() usage in initdb. In yesterday's commit 2dc4f011fd61501cce507be78c39a2677690d44b, I tried to force buffering of stdout/stderr in initdb to be what it is by default when the program is run interactively on Unix (since that's how most manual testing is done). This tripped over the fact that Windows doesn't support _IOLBF mode. We dealt with that a long time ago in syslogger.c by falling back to unbuffered mode on Windows. Export that solution in port.h and use it in initdb. Back-patch to 8.4, like the previous commit. http://git.postgresql.org/pg/commitdiff/f62d417825d6a8e26768a130a5f4c9e454d9dbe4
  • Fix valgrind warning for btree_gist indexes on macaddr. The macaddr opclass stores two macaddr structs (each of size 6) in an index column that's declared as being of type gbtreekey16, ie 16 bytes. In the original coding this led to passing a palloc'd value of size 12 to the index insertion code, so that data would be fetched past the end of the allocated value during index tuple construction. This makes valgrind unhappy. In principle it could result in a SIGSEGV, though with the current implementation of palloc there's no risk since the 12-byte request size would be rounded up to 16 bytes anyway. To fix, add a field to struct gbtree_ninfo showing the declared size of the index datums, and use that in the palloc requests; and use palloc0 to be sure that any wasted bytes are cleanly initialized. Per report from Andres Freund. No back-patch since there's no current risk of a real problem. http://git.postgresql.org/pg/commitdiff/82bbb60c30dbff0633da34387ccab58d843379b5
  • Fix a second cause of undersized pallocs for btree_gist indexes on macaddr. gbt_macad_union also allocated 12-byte structs where we really need 16. Per report from Andres Freund. No back-patch since there's no current risk of a real problem. http://git.postgresql.org/pg/commitdiff/39586bc1e971c136c4487b26eb7334dbbe23b102
  • Suppress some more valgrind whining about btree_gist. A couple of functions didn't bother to zero out pad bytes in datums that would ultimately go to disk. Harmless, but valgrind doesn't know that. http://git.postgresql.org/pg/commitdiff/af215d8190e6ab170c02c24afd1be81f5a147481
  • Make sure chr(int) can't create invalid UTF8 sequences. Several years ago we changed chr(int) so that if the database encoding is UTF8, it would interpret its argument as a Unicode code point and expand it into the appropriate multibyte sequence. However, we weren't sufficiently careful about checking validity of the input. According to RFC3629, UTF8 disallows code points above U+10FFFF (note that the predecessor standard RFC2279 was more liberal). Also, both versions of the UTF8 spec agree that Unicode surrogate-pair codes should never appear in UTF8. Because our encoding validity checks follow RFC3629, our failure to enforce these restrictions in chr() means it could be used to produce text strings that will be rejected when the database is dumped and reloaded. To ensure consistency with the input functions, let's actually apply pg_utf8_islegal() to the proposed output of chr(). Per discussion, this seems like too much of a behavioral change to back-patch, but it's not too late to squeeze it into 9.4. http://git.postgresql.org/pg/commitdiff/7894ac5004d5ec158f46296540d3cf49386d30e4
  • Fix unaligned accesses in DecodeUpdate(). The xl_heap_header_len structures in an XLOG_HEAP_UPDATE record aren't necessarily aligned adequately. The regular replay function for these records is aware of that, but decode.c didn't get the memo. I'm not sure why the buildfarm failed to catch this; the test_decoding test certainly blows up real good on my old HPPA box. Also, I'm pretty sure that the address arithmetic was wrong for the case of XLOG_HEAP_CONTAINS_OLD and not XLOG_HEAP_CONTAINS_NEW_TUPLE, though this apparently can't happen when logical decoding is active. http://git.postgresql.org/pg/commitdiff/6c42b2b10af3f717030966b9f05867f3e065becc
  • Fix a bunch of functions that were declared static then defined not-static. Per testing with a compiler that whines about this. http://git.postgresql.org/pg/commitdiff/c1907f0cc49e38df9853b7547c9afce5204e4784
  • Make fallback implementation of pg_memory_barrier() work. The fallback implementation involves acquiring and releasing a spinlock variable that is otherwise unreferenced --- not even to the extent of initializing it. This accidentally fails to fail on platforms where spinlocks should be initialized to zeroes, but elsewhere it results in a "stuck spinlock" failure during startup. I griped about this last July, and put in a hack that worked for gcc on HPPA, but didn't get around to fixing the general case. Per the discussion back then, the best thing to do seems to be to initialize dummy_spinlock in main.c. http://git.postgresql.org/pg/commitdiff/44cd47c1d49655c5dd9648bde8e267617c3735b4
  • Fix two ancient memory-leak bugs in relcache.c. RelationCacheInsert() ignored the possibility that hash_search(HASH_ENTER) might find a hashtable entry already present for the same OID. However, that can in fact occur during recursive relcache load scenarios. When it did happen, we overwrote the pointer to the pre-existing Relation, causing a session-lifespan leakage of that entire structure. As far as is known, the pre-existing Relation would always have reference count zero by the time we arrive back at the outer insertion, so add code that deletes the pre-existing Relation if so. If by some chance its refcount is positive, elog a WARNING and allow the pre-existing Relation to be leaked as before. Also, AttrDefaultFetch() was sloppy about leaking the cstring form of the pg_attrdef.adbin value it's copying into the relcache structure. This is only a query-lifespan leakage, and normally not very significant, but it adds up during CLOBBER_CACHE testing. These bugs are of very ancient vintage, but I'll refrain from back-patching since there's no evidence that these leaks amount to anything in ordinary usage. http://git.postgresql.org/pg/commitdiff/078b2ed291c758e7125d72c3a235f128d40a232b
  • Ooops, I broke initdb with that last patch. That's what I get for not fully retesting the final version of the patch. The replace_allowed cross-check needs an additional special case for bootstrapping. http://git.postgresql.org/pg/commitdiff/0c19aaba22c4d904f7259ef11f7d6db7b5dd36a0

Bruce Momjian a poussé :

Noah Misch a poussé :

Peter Eisentraut a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week :-)

Correctifs en attente

  • Kaigai Kouhei sent in two revisions of a patch to fix an error in the regression tests for SEPgsql.
  • Thomas Munro and Craig Ringer traded patches to implement SKIP LOCKED DATA.
  • Sergey Muraviov sent in three more revisions of a patch to fix psql's wrapping in extended mode with the default pager.
  • Amit Kapila sent in two revisions of a patch to help scale shared buffer eviction by reducing contention around BufFreelistLock.
  • Emre Hasegeli sent in another revision of a patch to add a selectivity estimation for inet operators.
  • Jeff Janes sent in a patch to fix pg_dump warnings in the MinGW build.
  • Jeff Janes sent in a patch to fix an issue with autovacuum scheduling starvation and frenzy.
  • Craig Ringer sent in a patch to fix an issue where dynamic background workers with BGW_NEVER_RESTART worker restarted on FatalError.
  • Steve Singer and Andres Freund traded patches to fix an issue where CREATE REPLICATION SLOT would fail on a timeout.
  • Andres Freund sent in two revisions of a patch to fix an issue where %d in log_line_prefix doesn't work for bg/autovacuum workers.
  • Heikki Linnakangas sent in another revision of a patch to change the WAL format with corresponding changes to the API.
  • Marko Kreen sent in a patch to clarify the crypto documentation for SSL.
  • Heikki Linnakangas sent in a patch to fix a checksum error in recovery with btree index.
  • David Rowley sent in another revision of a patch to allow join removals for more join types.
  • Fabrízio de Royes Mello sent in a patch to fix an issue where psql's \db+ does not have a size column.
  • Fabrízio de Royes Mello sent in a patch to fix a wrong name in the documentation of the pg_stat_replication view.

par N Bougain le dimanche 15 juin 2014 à 10h41

Nouvelles hebdomadaires de PostgreSQL - 11 mai 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mai

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é :

  • Use Size instead of uint32 to store result of sizeof(). Silences coverity and is more consistent with other functions in the same file. Andres Freund http://git.postgresql.org/pg/commitdiff/c83457683968e2263d7e6bcdcdd52d472f9b0f8d
  • Don't leak memory after connection aborts in pg_recvlogical. Andres Freund, noticed by coverity. http://git.postgresql.org/pg/commitdiff/329de9fa983debc941ebd79f335f2574b72a3694
  • Pass sensible value to memset() when randomizing reorderbuffer's tuple slab. This is entirely harmless, but still wrong. Noticed by coverity. Andres Freund http://git.postgresql.org/pg/commitdiff/377790fbd707b41bdf2264fe6c217e903e241f7c
  • Assert that pre/post-fix updated tuples are on the same page during replay. If they were not 'oldtup.t_data' would be dereferenced while set to NULL in case of a full page image for block 0. Do so primarily to silence coverity; but also to make sure this prerequisite isn't changed without adapting the replay routine as that would appear to work in many cases. Andres Freund http://git.postgresql.org/pg/commitdiff/1460b199e6e748712253fa130d1c3368ba6ac861
  • Replace SYSTEMQUOTEs with Windows-specific wrapper functions. It's easy to forget using SYSTEMQUOTEs when constructing command strings for system() or popen(). Even if we fix all the places missing it now, it is bound to be forgotten again in the future. Introduce wrapper functions that do the the extra quoting for you, and get rid of SYSTEMQUOTEs in all the callers. We previosly used SYSTEMQUOTEs in all the hard-coded command strings, and this doesn't change the behavior of those. But user-supplied commands, like archive_command, restore_command, COPY TO/FROM PROGRAM calls, as well as pgbench's \shell, will now gain an extra pair of quotes. That is desirable, but if you have existing scripts or config files that include an extra pair of quotes, those might need to be adjusted. Reviewed by Amit Kapila and Tom Lane http://git.postgresql.org/pg/commitdiff/a692ee5870f0f442565b4c4bff367094599e9bdf
  • Fix use of free in walsender error handling after a sysid mismatch. Found via valgrind. The bug exists since the introduction of the walsender, so backpatch to 9.0. Andres Freund http://git.postgresql.org/pg/commitdiff/3a8e9e977fa642433986e5cd145e3a6f86601c2c
  • Fix build after removing JsonbValue.estSize field. Oops, I didn't realize that contrib/hstore refers to jsonb stuff. http://git.postgresql.org/pg/commitdiff/7572b7735971cd7a5ef289e133eedf7d82f79c42
  • Clean up jsonb code. The main target of this cleanup is the convertJsonb() function, but I also touched a lot of other things that I spotted into in the process. The new convertToJsonb() function uses an output buffer that's resized on demand, so the code to estimate of the size of JsonbValue is removed. The on-disk format was not changed, even though I refactored the structs used to handle it. The term "superheader" is replaced with "container". The jsonb_exists_any and jsonb_exists_all functions no longer sort the input array. That was a premature optimization, the idea being that if there are duplicates in the input array, you only need to check them once. Also, sorting the array saves some effort in the binary search used to find a key within an object. But there were drawbacks too: the sorting and deduplicating obviously isn't free, and in the typical case there are no duplicates to remove, and the gain in the binary search was minimal. Remove all that, which makes the code simpler too. This includes a bug-fix; the total length of the elements in a jsonb array or object mustn't exceed 2^28. That is now checked. http://git.postgresql.org/pg/commitdiff/364ddc3e5cbd01c93a39896b5260509129a9883e
  • Include files copied from libpqport in .gitignore. Michael Paquier http://git.postgresql.org/pg/commitdiff/02c9a938057ecb811c65999577d32c5f5e9c99dc
  • Protect against torn pages when deleting GIN list pages. To-be-deleted list pages contain no useful information, as they are being deleted, but we must still protect the writes from being torn by a crash after a partial write. To do that, re-initialize the pages on WAL replay. Jeff Janes caught this with a test program to test partial writes. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/4f7bb4b2a36facc94a1d6b821ec6733093aa9bc6
  • Avoid some pnstrdup()s when constructing jsonb. This speeds up text to jsonb parsing and hstore to jsonb conversions somewhat. http://git.postgresql.org/pg/commitdiff/d3c72e23df6ef1fde8122db589d12d49c8495535
  • Minor cleanup of jsonb_util.c. Move the functions around to group related functions together. Remove binequal argument from lengthCompareJsonbStringValue, moving that responsibility to lengthCompareJsonbPair. Fix typo in comment. http://git.postgresql.org/pg/commitdiff/ff7bbb0176a667aeb0577e4d48d651ffdb6afd50
  • More jsonb cleanup. Fix JSONB_MAX_ELEMS and JSONB_MAX_PAIRS macros to use CB_MASK in the calculation. JENTRY_POSMASK happens to have the same value at the moment, but that's just coincidental. Refactor jsonb iterator functions, for readability. Get rid of the JENTRY_ISFIRST flag. Whenever we handle JEntrys, we have access to the whole array and have enough context information to know which entry is the first. This frees up one bit in the JEntry header for future use. While we're at it, shuffle the JEntry bits so that boolean true and false go together, for aesthetic reasons. Bump catalog version as this changes the on-disk format slightly. http://git.postgresql.org/pg/commitdiff/d9daff0e0cb15221789e6c50d9733c8754c054fb
  • Fix bug in lossy-page handling in GIN. When returning rows from a bitmap, as done with partial match queries, we would get stuck in an infinite loop if the bitmap contained a lossy page reference. This bug is new in master, it was introduced by the patch to allow skipping items refuted by other entries in GIN scans. Report and fix by Alexander Korotkov http://git.postgresql.org/pg/commitdiff/866e6e1d04d4ae9615bd1899a757dae0191e9c77

Robert Haas a poussé :

Bruce Momjian a poussé :

Tom Lane a poussé :

  • Fix case of pg_dump -Fc to an unseekable file (such as a pipe). This was accidentally broken in commits cfa1b4a711/5e8e794e3b. It saves a line or so to call ftello unconditionally in _CloseArchive, but we have to expect that it might fail if we're not in hasSeek mode. Per report from Bernd Helmle. In passing, improve _getFilePos to print an appropriate message if ftello fails unexpectedly, rather than just a vague complaint about "ftell mismatch". http://git.postgresql.org/pg/commitdiff/e03485ae8a2025d5deea291ebb24412229cc2fe5
  • Fix possible cache invalidation failure in ReceiveSharedInvalidMessages. Commit fad153ec45299bd4d4f29dec8d9e04e2f1c08148 modified sinval.c to reduce the number of calls into sinvaladt.c (which require taking a shared lock) by keeping a local buffer of collected-but-not-yet-processed messages. However, if processing of the last message in a batch resulted in a recursive call to ReceiveSharedInvalidMessages, we could overwrite that message with a new one while the outer invalidation function was still working on it. This would be likely to lead to invalidation of the wrong cache entry, allowing subsequent processing to use stale cache data. The fix is just to make a local copy of each message while we're processing it. Spotted by Andres Freund. Back-patch to 8.4 where the bug was introduced. http://git.postgresql.org/pg/commitdiff/0f928a85ecd509b165bfb9acd71b18c40063a7a0
  • Fix pg_type.typlen for newly-revived line type. Commit 261c7d4b653bc3e44c31fd456d94f292caa50d8f removed the "m" field from struct LINE, but neglected to make pg_type.h's idea of the type's size match. This resulted in reading past the end of palloc'd LINE values when inserting them into tuples etc. In principle that could cause a SIGSEGV, though the odds of detectable problems seem low. Bump catversion since this makes an incompatible on-disk format change. Note that if the line type had been in use in the field, this would break pg_upgrade'ability of databases containing line values; but it seems unlikely that there are any (they'd have had to be compiled with -DENABLE_LINE_TYPE). Spotted by Andres Freund. http://git.postgresql.org/pg/commitdiff/3727afafeef69088456acc3f6257360315220a20
  • Run autoconf in wake of commit a692ee5870f0f442565b4c4bff367094599e9bdf. Heikki updated configure.in but evidently forgot to include the updated configure script in the commit. Per buildfarm. http://git.postgresql.org/pg/commitdiff/9252b8eec27bbefbeae9d60d8cd4f6b8be80b861
  • hash_any returns Datum, not uint32 (and definitely not "int"). The coding in JsonbHashScalarValue might have accidentally failed to fail given current representational choices, but the key word there would be "accidental". Insert the appropriate datatype conversion macro. And use the right conversion macro for hash_numeric's result, too. In passing make the code a bit cleaner and less repetitive by factoring out the xor step from the switch. http://git.postgresql.org/pg/commitdiff/2c22afaa4e29cbd773bae3f043a941f82ff30e2d
  • Fix some more confusion between uint32 and Datum. http://git.postgresql.org/pg/commitdiff/1891b415f0cf45d56f29af423598f8518754d675
  • Fix failure to set ActiveSnapshot while rewinding a cursor. ActiveSnapshot needs to be set when we call ExecutorRewind because some plan node types may execute user-defined functions during their ReScan calls (nodeLimit.c does so, at least). The wisdom of that is somewhat debatable, perhaps, but for now the simplest fix is to make sure the required context is valid. Failure to do this typically led to a null-pointer-dereference core dump, though it's possible that in more complex cases a function could be executed with the wrong snapshot leading to very subtle misbehavior. Per report from Leif Jensen. It's been broken for a long time, so back-patch to all active branches. http://git.postgresql.org/pg/commitdiff/04e5025be8bbe572e12b19c4ba9e2a8360b8ffe5
  • Avoid buffer bloat in libpq when server is consistently faster than client. If the server sends a long stream of data, and the server + network are consistently fast enough to force the recv() loop in pqReadData() to iterate until libpq's input buffer is full, then upon processing the last incomplete message in each bufferload we'd usually double the buffer size, due to supposing that we didn't have enough room in the buffer to finish collecting that message. After filling the newly-enlarged buffer, the cycle repeats, eventually resulting in an out-of-memory situation (which would be reported misleadingly as "lost synchronization with server"). Of course, we should not enlarge the buffer unless we still need room after discarding already-processed messages. This bug dates back quite a long time: pqParseInput3 has had the behavior since perhaps 2003, getCopyDataMessage at least since commit 70066eb1a1ad in 2008. Probably the reason it's not been isolated before is that in common environments the recv() loop would always be faster than the server (if on the same machine) or faster than the network (if not); or at least it wouldn't be slower consistently enough to let the buffer ramp up to a problematic size. The reported cases involve Windows, which perhaps has different timing behavior than other platforms. Per bug #7914 from Shin-ichi Morita, though this is different from his proposed solution. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/2f557167b19af79ffecb8faedf8b7bce4d48f3e1
  • Allow for platforms that have optreset but not <getopt.h>. Reportedly, some versions of mingw are like that, and it seems plausible in general that older platforms might be that way. However, we'd determined experimentally that just doing "extern int" conflicts with the way Cygwin declares these variables, so explicitly exclude Cygwin. Michael Paquier, tweaked by me to hopefully not break Cygwin http://git.postgresql.org/pg/commitdiff/0c15a524c532c1006a7bd36d3a680f8e8b8db9fc
  • Fix comment. Previous commit was confused about the case we're handling: actually, what the patch is dealing with is platforms that have optreset, *and* have <getopt.h>, but the latter fails to declare the former. Because we use a linking probe to set HAVE_INT_OPTRESET, we need to be sure we have a declaration even if <getopt.h> doesn't think it exists. http://git.postgresql.org/pg/commitdiff/1e81f8462aceb07d520daaabdc999b879381bd12
  • Revert "Auto-tune effective_cache size to be 4x shared buffers". This reverts commit ee1e5662d8d8330726eaef7d3110cb7add24d058, as well as a remarkably large number of followup commits, which were mostly concerned with the fact that the implementation didn't work terribly well. It still doesn't: we probably need some rather basic work in the GUC infrastructure if we want to fully support GUCs whose default varies depending on the value of another GUC. Meanwhile, it also emerged that there wasn't really consensus in favor of the definition the patch tried to implement (ie, effective_cache_size should default to 4 times shared_buffers). So whack it all back to where it was. In a followup commit, I'll do what was recently agreed to, which is to simply change the default to a higher value. http://git.postgresql.org/pg/commitdiff/a16d421ca4fc639929bc964b2585e8382cf16e33
  • Increase the default value of effective_cache_size to 4GB. Per discussion, the old value of 128MB is ridiculously small on modern machines; in fact, it's not even any larger than the default value of shared_buffers, which it certainly should be. Increase to 4GB, which is unlikely to be any worse than the old default for anyone, and should be noticeably better for most. Eventually we might have an autotuning scheme for this setting, but the recent attempt crashed and burned, so for now just do this. http://git.postgresql.org/pg/commitdiff/b910d7ea358b0134d170ebf4002248df25a5f244
  • Document permissions needed for pg_database_size and pg_tablespace_size. Back in 8.3, we installed permissions checks in these functions (see commits 8bc225e7990a and cc26599b7206). But we forgot to document that anywhere in the user-facing docs; it did get mentioned in the 8.3 release notes, but nobody's looking at that any more. Per gripe from Suya Huang. http://git.postgresql.org/pg/commitdiff/fb1974cc6b58cd21a269dd7ab8aea0120906ec3b
  • Fix missing dependencies in ecpg's test Makefiles. Ensure that ecpg preprocessor output files are rebuilt when re-testing after a change in the ecpg preprocessor itself, or a change in any of several include files that get copied verbatim into the output files. The lack of these dependencies was what created problems for Kevin Grittner after the recent pgindent run. There's no way for --enable-depend to discover these dependencies automatically, so we've gotta put them into the Makefiles by hand. While at it, reduce the amount of duplication in the ecpg invocations. http://git.postgresql.org/pg/commitdiff/14d309cc55d88b73d4c9d1fa51b535ae491405fd
  • Improve key representation for GIN jsonb_ops, and fix existence-search bug. Change the key representation so that values that would exceed 127 bytes are hashed into short strings, and so that the original JSON datatype of each value is recorded in the index. The hashing rule eliminates the major objection to having this opclass be the default for jsonb, namely that it could fail for plausible input data (due to GIN's restrictions on maximum key length). Preserving datatype information doesn't really buy us much right now, but it requires no extra space compared to the previous way, and it might be useful later. Also, change the consistency-checking functions to request recheck for exists (jsonb ? text) and related operators. The original analysis that this is an exactly checkable query was incorrect, since the index does not preserve information about whether a key appears at top level in the indexed JSON object. Add a test case demonstrating the problem. Make some other, mostly cosmetic improvements to the code in jsonb_gin.c as well. catversion bump due to on-disk data format change in jsonb_ops indexes. http://git.postgresql.org/pg/commitdiff/46dddf7673c5820be7c78a8b0e098fdbc65d003d
  • Fix typcategory labeling of jsonb. Dunno who had the cute idea of labeling jsonb as typcategory 'C', but it is not a composite type. Label it 'U', since that's what json is using. http://git.postgresql.org/pg/commitdiff/bdf9dd4db78a5f23e863f2d4d8500969dc261fd0
  • Teach add_json() that jsonb is of TYPCATEGORY_JSON. This code really needs to be refactored so that there aren't so many copies that can diverge. Not to mention that this whole approach is probably wrong. But for the moment I'll just stick my finger in the dike. Per report from Michael Paquier. http://git.postgresql.org/pg/commitdiff/62e57ff040e5d0acd303b94e86fff689effc5957
  • Get rid of bogus dependency on typcategory in to_json() and friends. These functions were relying on typcategory to identify arrays and composites, which is not reliable and not the normal way to do it. Using typcategory to identify boolean, numeric types, and json itself is also pretty questionable, though the code in those cases didn't seem to be at risk of anything worse than wrong output. Instead, use the standard lsyscache functions to identify arrays and composites, and rely on a direct check of the type OID for the other cases. In HEAD, also be sure to look through domains so that a domain is treated the same as its base type for conversions to JSON. However, this is a small behavioral change; given the lack of field complaints, we won't back-patch it. In passing, refactor so that there's only one copy of the code that decides which conversion strategy to apply, not multiple copies that could (and have) gotten out of sync. http://git.postgresql.org/pg/commitdiff/0ca6bda8e7501947c05f30c127f6d12ff90b5a64
  • Improve user-facing JSON documentation. I started out with the intention of just fixing the info about the jsonb operator classes, but soon found myself copy-editing most of the JSON material. Hopefully it's more readable now. http://git.postgresql.org/pg/commitdiff/0b92a77c17ebe8bec08e250e1a929b07efef1008
  • Fix broken allocation logic in recently-rewritten jsonb_util.c. reserveFromBuffer() failed to consider the possibility that it needs to more-than-double the current buffer size. Beyond that, it seems likely that we'd someday need to worry about integer overflow of the buffer length variable. Rather than reinvent the logic that's already been debugged in stringinfo.c, let's go back to using that logic. We can still have the same targeted API, but we'll rely on stringinfo.c to manage reallocation. Per report from Alexander Korotkov. http://git.postgresql.org/pg/commitdiff/3d8c2b496fc1fed2b8ff8a403d3a17329325466e
  • Fix bogus documentation of json_object_agg(). It takes two arguments, not one. http://git.postgresql.org/pg/commitdiff/17e4082b6312383075e397b4b2ae324c90716c5b
  • Update config.guess and config.sub http://git.postgresql.org/pg/commitdiff/7fa5bc43aaaf01e23c3a6ab59a57c19273f6f7f4
  • Accept tcl 8.6 in configure's probe for tclsh. Usually the search would find plain "tclsh" without any trouble, but some installations might only have the version-numbered flavor of that program. No compatibility problems have been reported with 8.6, so we might as well back-patch this to all active branches. Christoph Berg http://git.postgresql.org/pg/commitdiff/eaba54c20c5ab2cb6aaffa574444fd4990dfe2c7
  • More work on the JSON/JSONB user documentation. Document existence operator adequately; fix obsolete claim that no Unicode-escape semantic checks happen on input (it's still true for json, but not for jsonb); improve examples; assorted wordsmithing. http://git.postgresql.org/pg/commitdiff/f825c7c850dbe683d62defd7e5bd31664ee3302c
  • Rename min_recovery_apply_delay to recovery_min_apply_delay. Per discussion, this seems like a more consistent choice of name. Fabrízio de Royes Mello, after a suggestion by Peter Eisentraut; some additional documentation wordsmithing by me http://git.postgresql.org/pg/commitdiff/0d0b2bf175a53dfedcc0cb69eaba7576b0c286f2
  • Rename jsonb_hash_ops to jsonb_path_ops. There's no longer much pressure to switch the default GIN opclass for jsonb, but there was still some unhappiness with the name "jsonb_hash_ops", since hashing is no longer a distinguishing property of that opclass, and anyway it seems like a relatively minor detail. At the suggestion of Heikki Linnakangas, we'll use "jsonb_path_ops" instead; that captures the important characteristic that each index entry depends on the entire path from the document root to the indexed value. Also add a user-facing explanation of the implementation properties of these two opclasses. http://git.postgresql.org/pg/commitdiff/12e611d43e6efbf0e36014a3055ed47366facee7
  • Find postgresql.auto.conf in PGDATA even when postgresql.conf is elsewhere. The original coding for ALTER SYSTEM made a fundamentally bogus assumption that postgresql.auto.conf could be sought relative to the main config file if we hadn't yet determined the value of data_directory. This fails for common arrangements with the config file elsewhere, as reported by Christoph Berg. The simplest fix is to not try to read postgresql.auto.conf until after SelectConfigFiles has chosen (and locked down) the data_directory setting. Because of the logic in ProcessConfigFile for handling resetting of GUCs that've been removed from the config file, we cannot easily read the main and auto config files separately; so this patch adopts a brute force approach of reading the main config file twice during postmaster startup. That's a tad ugly, but the actual time cost is likely to be negligible, and there's no time for a more invasive redesign before beta. With this patch, any attempt to set data_directory via ALTER SYSTEM will be silently ignored. It would probably be better to throw an error, but that can be dealt with later. This bug, however, would prevent any testing of ALTER SYSTEM by a significant fraction of the userbase, so it seems important to get it fixed before beta. http://git.postgresql.org/pg/commitdiff/195e81aff5d63da1e1f9dd0314a13859ec15c7c0
  • Stamp 9.4beta1. http://git.postgresql.org/pg/commitdiff/e6df2e1be6330660ba4d81daa726ae4a71535aa9

Michael Meskes a poussé :

  • Fix handling of array of char pointers in ecpglib. When array of char * was used as target for a FETCH statement returning more than one row, it tried to store all the result in the first element. Instead it should dump array of char pointers with right offset, use the address instead of the value of the C variable while reading the array and treat such variable as char **, instead of char * for pointer arithmetic. Patch by Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> http://git.postgresql.org/pg/commitdiff/8d6a07fa01cece1bd3508a81e59c0c0cbc0bb867

Simon Riggs a poussé :

Peter Eisentraut a poussé :

Jeff Davis a poussé :

  • Improve comment for tricky aspect of index-only scans. Index-only scans avoid taking a lock on the VM buffer, which would cause a lot of contention. To be correct, that requires some intricate assumptions that weren't completely documented in the previous comment. Reviewed by Robert Haas. http://git.postgresql.org/pg/commitdiff/35c0cd3b05b0be18dc2d049c33b38a2d13993ffe
  • Fix interval test, which was broken for floating-point timestamps. Commit 4318daecc959886d001a6e79c6ea853e8b1dfb4b introduced a test that couldn't be made consistent between integer and floating-point timestamps. It was designed to test the longest possible interval output length, so removing four zeros from the number of hours, as this patch does, is not ideal. But the test still has some utility for its original purpose, and there aren't a lot of other good options. Noah Misch suggested a different approach where we test that the output either matches what we expect from integer timestamps or what we expect from floating-point timestamps. That seemed to obscure an otherwise simple test, however. Reviewed by Tom Lane and Noah Misch. http://git.postgresql.org/pg/commitdiff/348aa75a678069569923c501206d5a1ca03654e3

Noah Misch a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week :-)

Correctifs en attente

  • Robert Haas sent in a patch to avoiding tuple copying in btree index builds.
  • Thomas Munro sent in three revisions of a patch to include the (non-default) cluster name in ps output.
  • Heikki Linnakangas sent in a patch to fix a race condition between PREPARE TRANSACTION and COMMIT PREPARED.
  • Robert Haas sent in a patch to create a new memory allocator, sb_alloc.
  • Heikki Linnakangas sent in a patch to prevent a doPickSplit stack buffer overflow in XLogInsert by removing MAXALIGNs in SP-GiST WAL.
  • Andreas Karlsson sent in a patch to fix tab completion for DROP TRIGGER/RULE and ALTER TABLE ... DISABLE/ENABLE in psql.
  • Andres Freund sent in two more revisions of a patch to add a pg_shmem_allocations view.
  • Michael Paquier, Andres Freund, and Fabrízio de Royes Mello traded patches to fix an issue where the new pg_lsn type doesn't have hash/btree opclasses, resulting in inability to do certain relativel simple queries with them.
  • Kaigai Kouhei sent in another revision of a patch to implement a custom plan API.
  • Jaime Casanova sent in a WIP patch to show index maintenance in EXPLAIN ANALYZE VERBOSE.
  • Andres Freund sent in a patch to fix harmless access to uninitialized memory in ri_triggers.c.
  • Robert Haas sent in a doc patch to fix what appeared to be an issue where PQputCopyEnd didn't adhere to its API contract.
  • MauMau sent in another revision of a patch to fix an issue on Windows where pg_ctl always uses the same event source.
  • Peter Eisentraut sent in a patch to fix imprecise pg_basebackup documentation about excluded files.
  • Christoph Berg sent in a patch to update config.guess/config.sub for ppc64le.
  • Alexander Korotkov sent in a patch to fix a breakage of lossy bitmap scans in GIN indexes.
  • Fabrízio de Royes Mello sent in a patch to rename min_recovery_apply_delay to recovery_min_apply_delay.
  • Peter Eisentraut sent in a patch to enable accepting empty values as xml "content" values, per the XML spec.

par N Bougain le dimanche 15 juin 2014 à 10h38

jeudi 8 mai 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 4 mai 2014

[ndt: stand PostgreSQL aux Solutions Linux les 20 & 21 mai : http://www.postgresql.org/message-id/1398694675.8172.52.camel@deiphobe]

Offres d'emplois autour de PostgreSQL en mai

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

Robert Haas a poussé :

Heikki Linnakangas a poussé :

  • Fix two bugs in WAL-logging of GIN pending-list pages. In writeListPage, never take a full-page image of the page, because we have all the information required to re-initialize in the WAL record anyway. Before this fix, a full-page image was always generated, unless full_page_writes=off, because when the page is initialized its LSN is always 0. In stable-branches, keep the code to restore the backup blocks if they exist, in case that the WAL is generated with an older minor version, but in master Assert that there are no full-page images. In the redo routine, add missing "off++". Otherwise the tuples are added to the page in reverse order. That happens to be harmless because we always scan and remove all the tuples together, but it was clearly wrong. Also, it was masked by the first bug unless full_page_writes=off, because the page was always restored from a full-page image. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/d2722443d993988ff2e529b652c61fec1ca527f7
  • Add missing SYSTEMQUOTEs. Some popen() calls were missing SYSTEMQUOTEs, which caused initdb and pg_upgrade to fail on Windows, if the installation path contained both spaces and @ signs. Patch by Nikhil Deshpande. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/503de5462158b0260f6deaf3087cf0945f7327f0

Gregory Stark a poussé :

Tom Lane a poussé :

  • Improve planner to drop constant-NULL inputs of AND/OR where it's legal. In general we can't discard constant-NULL inputs, since they could change the result of the AND/OR to be NULL. But at top level of WHERE, we do not need to distinguish a NULL result from a FALSE result, so it's okay to treat NULL as FALSE and then simplify AND/OR accordingly. This is a very ancient oversight, but in 9.2 and later it can lead to failure to optimize queries that previous releases did optimize, as a result of more aggressive parameter substitution rules making it possible to reduce more subexpressions to NULL constants. This is the root cause of bug #10171 from Arnold Scheffler. We could alternatively have fixed that by teaching orclauses.c to ignore constant-NULL OR arms, but it seems better to get rid of them globally. I resisted the temptation to back-patch this change into all active branches, but it seems appropriate to back-patch as far as 9.2 so that there will not be performance regressions of the kind shown in this bug. http://git.postgresql.org/pg/commitdiff/95811032d782049642a672e3db0a5382616ab084
  • Fix uninitialized-variable warnings induced by recent commit. http://git.postgresql.org/pg/commitdiff/5358bfdc98a47d26649ae094dab88842a0603968
  • Fix indentation of JOIN clauses in rule/view dumps. The code attempted to outdent JOIN clauses further left than the parent FROM keyword, which was odd in any case, and led to inconsistent formatting since in simple cases the clauses couldn't be moved any further left than that. And it left a permanent decrement of the indentation level, causing subsequent lines to be much further left than they should be (again, this couldn't be seen in simple cases for lack of indentation to give up). After a little experimentation I chose to make it indent JOIN keywords two spaces from the parent FROM, which is one space more than the join's lefthand input in cases where that appears on a different line from FROM. Back-patch to 9.3. This is a purely cosmetic change, and the bug is quite old, so that may seem arbitrary; but we are going to be making some other changes to the indentation behavior in both HEAD and 9.3, so it seems reasonable to include this in 9.3 too. I committed this one first because its effects are more visible in the regression test results as they currently stand than they will be later. http://git.postgresql.org/pg/commitdiff/d166eed302400a71eed1aaa301d30be3af7b5715
  • Limit overall indentation in rule/view dumps. Continuing to indent no matter how deeply nested we get doesn't really do anything for readability; what's worse, it results in O(N^2) total whitespace, which can become a performance and memory-consumption issue. To address this, once we get past 40 characters of indentation, reduce the indentation step distance 4x, and also limit the maximum indentation by reducing it modulo 40. This latter choice is a bit weird at first glance, but it seems to preserve readability better than a simple cap would do. Back-patch to 9.3, because since commit 62e666400d the performance issue is a hazard for pg_dump. Greg Stark and Tom Lane http://git.postgresql.org/pg/commitdiff/0601cb54dac14d979d726ab2ebeda251ae36e857
  • Reduce indentation/parenthesization of set operations in rule/view dumps. A query such as "SELECT x UNION SELECT y UNION SELECT z UNION ..." produces a left-deep nested parse tree, which we formerly showed in its full nested glory and with all the possible parentheses. This does little for readability, though, and long UNION lists resulting in excessive indentation are common. Instead, let's omit parentheses and indent all the subqueries at the same level in such cases. This patch skips indentation/parenthesization whenever the lefthand input of a SetOperationStmt is another SetOperationStmt of the same kind and ALL/DISTINCT property. We could teach the code the exact syntactic precedence of set operations and thereby avoid parenthesization in some more cases, but it's not clear that that'd be a readability win: it seems better to parenthesize if the set operation changes. (As an example, if there's one UNION in a long list of UNION ALL, it now stands out like a sore thumb, which seems like a good thing.) Back-patch to 9.3. This completes our response to a complaint from Greg Stark that since commit 62e666400d there's a performance problem in pg_dump for views containing long UNION sequences (or other types of deeply nested constructs). The previous commit 0601cb54dac14d979d726ab2ebeda251ae36e857 handles the general problem, but this one makes the specific case of UNION lists look a lot nicer. http://git.postgresql.org/pg/commitdiff/41de93c53aa941167d445ecb7d91d58829adcc92
  • Check for interrupts and stack overflow during rule/view dumps. Since ruleutils.c recurses, it could be driven to stack overflow by deeply nested constructs. Very large queries might also take long enough to deparse that a check for interrupts seems like a good idea. Stick appropriate tests into a couple of key places. Noted by Greg Stark. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/0bff398761b5e6119b40550bbe3751f4194dc7a7
  • Rationalize common/relpath.[hc]. Commit a73018392636ce832b09b5c31f6ad1f18a4643ea created rather a mess by putting dependencies on backend-only include files into include/common. We really shouldn't do that. To clean it up: * Move TABLESPACE_VERSION_DIRECTORY back to its longtime home in catalog/catalog.h. We won't consider this symbol part of the FE/BE API. * Push enum ForkNumber from relfilenode.h into relpath.h. We'll consider relpath.h as the source of truth for fork numbers, since relpath.c was already partially serving that function, and anyway relfilenode.h was kind of a random place for that enum. * So, relfilenode.h now includes relpath.h rather than vice-versa. This direction of dependency is fine. (That allows most, but not quite all, of the existing explicit #includes of relpath.h to go away again.) * Push forkname_to_number from catalog.c to relpath.c, just to centralize fork number stuff a bit better. * Push GetDatabasePath from catalog.c to relpath.c; it was rather odd that the previous commit didn't keep this together with relpath(). * To avoid needing relfilenode.h in common/, redefine the underlying function (now called GetRelationPath) as taking separate OID arguments, and make the APIs using RelFileNode or RelFileNodeBackend into macro wrappers. (The macros have a potential multiple-eval risk, but none of the existing call sites have an issue with that; one of them had such a risk already anyway.) * Fix failure to follow the directions when "init" fork type was added; specifically, the errhint in forkname_to_number wasn't updated, and neither was the SGML documentation for pg_relation_size(). * Fix tablespace-path-too-long check in CreateTableSpace() to account for fork-name component of maximum-length pathnames. This requires putting FORKNAMECHARS into a header file, but it was rather useless (and actually unreferenced) where it was. The last couple of items are potentially back-patchable bug fixes, if anyone is sufficiently excited about them; but personally I'm not. Per a gripe from Christoph Berg about how include/common wasn't self-contained. http://git.postgresql.org/pg/commitdiff/2d00190495b22e0d0ba351b2cda9c95fb2e3d083
  • Improve error messages in reorderbuffer.c. Be more clear about failure cases in relfilenode->relation lookup, and fix some other places that were inconsistent or not per our message style guidelines. Andres Freund and Tom Lane http://git.postgresql.org/pg/commitdiff/203b0d132fe9038061b5bab70f1362440ae94aa8
  • Fix failure to detoast fields in composite elements of structured types. If we have an array of records stored on disk, the individual record fields cannot contain out-of-line TOAST pointers: the tuptoaster.c mechanisms are only prepared to deal with TOAST pointers appearing in top-level fields of a stored row. The same applies for ranges over composite types, nested composites, etc. However, the existing code only took care of expanding sub-field TOAST pointers for the case of nested composites, not for other structured types containing composites. For example, given a command such as UPDATE tab SET arraycol = ARRAY[(ROW(x,42)::mycompositetype] ... where x is a direct reference to a field of an on-disk tuple, if that field is long enough to be toasted out-of-line then the TOAST pointer would be inserted as-is into the array column. If the source record for x is later deleted, the array field value would become a dangling pointer, leading to errors along the line of "missing chunk number 0 for toast value ..." when the value is referenced. A reproducible test case for this was provided by Jan Pecek, but it seems likely that some of the "missing chunk number" reports we've heard in the past were caused by similar issues. Code-wise, the problem is that PG_DETOAST_DATUM() is not adequate to produce a self-contained Datum value if the Datum is of composite type. Seen in this light, the problem is not just confined to arrays and ranges, but could also affect some other places where detoasting is done in that way, for example form_index_tuple(). I tried teaching the array code to apply toast_flatten_tuple_attribute() along with PG_DETOAST_DATUM() when the array element type is composite, but this was messy and imposed extra cache lookup costs whether or not any TOAST pointers were present, indeed sometimes when the array element type isn't even composite (since sometimes it takes a typcache lookup to find that out). The idea of extending that approach to all the places that currently use PG_DETOAST_DATUM() wasn't attractive at all. This patch instead solves the problem by decreeing that composite Datum values must not contain any out-of-line TOAST pointers in the first place; that is, we expand out-of-line fields at the point of constructing a composite Datum, not at the point where we're about to insert it into a larger tuple. This rule is applied only to true composite Datums, not to tuples that are being passed around the system as tuples, so it's not as invasive as it might sound at first. With this approach, the amount of code that has to be touched for a full solution is greatly reduced, and added cache lookup costs are avoided except when there actually is a TOAST pointer that needs to be inlined. The main drawback of this approach is that we might sometimes dereference a TOAST pointer that will never actually be used by the query, imposing a rather large cost that wasn't there before. On the other side of the coin, if the field value is used multiple times then we'll come out ahead by avoiding repeat detoastings. Experimentation suggests that common SQL coding patterns are unaffected either way, though. Applications that are very negatively affected could be advised to modify their code to not fetch columns they won't be using. In future, we might consider reverting this solution in favor of detoasting only at the point where data is about to be stored to disk, using some method that can drill down into multiple levels of nested structured types. That will require defining new APIs for structured types, though, so it doesn't seem feasible as a back-patchable fix. Note that this patch changes HeapTupleGetDatum() from a macro to a function call; this means that any third-party code using that macro will not get protection against creating TOAST-pointer-containing Datums until it's recompiled. The same applies to any uses of PG_RETURN_HEAPTUPLEHEADER(). It seems likely that this is not a big problem in practice: most of the tuple-returning functions in core and contrib produce outputs that could not possibly be toasted anyway, and the same probably holds for third-party extensions. This bug has existed since TOAST was invented, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/3f8c8e3c61cef5729980ee4372ec159862a979f1
  • Fix yet another corner case in dumping rules/views with USING clauses. ruleutils.c tries to cope with additions/deletions/renamings of columns in tables referenced by views, by means of adding machine-generated aliases to the printed form of a view when needed to preserve the original semantics. A recent blog post by Marko Tiikkaja pointed out a case I'd missed though: if one input of a join with USING is itself a join, there is nothing to stop the user from adding a column of the same name as the USING column to whichever side of the sub-join didn't provide the USING column. And then there'll be an error when the view is re-parsed, since now the sub-join exposes two columns matching the USING specification. We were catching a lot of related cases, but not this one, so add some logic to cope with it. Back-patch to 9.3, which is the first release that makes any serious attempt to cope with such cases (cf commit 2ffa740be and follow-ons). http://git.postgresql.org/pg/commitdiff/91e16b980612d80de1017e97e9f206239afb9026
  • Fix "quiet inline" configure test for newer clang compilers. This test used to just define an unused static inline function and check whether that causes a warning. But newer clang versions warn about unused static inline functions when defined inside a .c file, but not when defined in an included header, which is the case we care about. Change the test to cope. Andres Freund http://git.postgresql.org/pg/commitdiff/4c8aa8b5aea1e032f569222d4b6c1019e84622dc

Peter Eisentraut a poussé :

Bruce Momjian a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Ashutosh Bapat sent in a patch to fix an issue in ECPG where using an array of char pointers gives wrong results.
  • Andres Freund sent in a patch to fix an issue Coverity pointed out where it wasn't Assert()ed that pre/post-fix updated tuples are on the same page during replay.
  • Tom Lane sent in a patch to remove what appeared to be obsolete code in fork_process.c.
  • Peter Eisentraut sent in a patch to require xmllint so buildfarm animal borka would have it.
  • Dilip Kumar sent in another revision of a patch to allow merge joins on some criteria other than equality.
  • Hadi Moshayedi sent in a patch to make array_length() faster by detoasting only the overhead part of the datum.
  • Jeff Janes sent in a patch to add tab completion for setting search_path.
  • Andres Freund sent in a patch to add a pg_shmem_allocations view.
  • Jeff Davis sent in a patch to fix the in-line documentation for index-only scans.

par N Bougain le jeudi 8 mai 2014 à 15h10

Nouvelles hebdomadaires de PostgreSQL - 27 avril 2014

La date limite de dépôt des propositions de conférences pour les événements outre-manche a été reportée au 2 mai :
http://www.char14.info - propositions à simon AT 2ndQuadrant.com
http://www.postgresqlusergroup.org.uk - propositions à simon AT 2ndQuadrant.com

Les nouveautés des produits dérivés

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.

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

Tom Lane a poussé :

  • pg_stat_statements forgot to let previous occupant of hook get control too. pgss_post_parse_analyze() neglected to pass the call on to any earlier occupant of the post_parse_analyze_hook. There are no other users of that hook in contrib/, and most likely none in the wild either, so this is probably just a latent bug. But it's a bug nonetheless, so back-patch to 9.2 where this code was introduced. http://git.postgresql.org/pg/commitdiff/78a3c9b6a5f6cc84abaf4e13deb58c620eb2161b
  • Fix broken logic in logical_heap_rewrite_flush_mappings(). It's blatantly obvious that commit 4d0d607a454ee832574afd52a3c515099cc85eb3 wasn't tested. The leak's real enough, though. http://git.postgresql.org/pg/commitdiff/c6a4ace5bf839b2480e8bb4c36bd3ec850c55c65
  • Fix documentation of FmgrInfo.fn_nargs. Some ancient comments claimed that fn_nargs could be -1 to indicate a variable number of input arguments; but this was never implemented, and is at variance with what we ultimately did with "variadic" functions. Update the comments. http://git.postgresql.org/pg/commitdiff/d26b042ce577a4012b9798528f0b1bcfa6e502eb
  • Allow polymorphic aggregates to have non-polymorphic state data types. Before 9.4, such an aggregate couldn't be declared, because its final function would have to have polymorphic result type but no polymorphic argument, which CREATE FUNCTION would quite properly reject. The ordered-set-aggregate patch found a workaround: allow the final function to be declared as accepting additional dummy arguments that have types matching the aggregate's regular input arguments. However, we failed to notice that this problem applies just as much to regular aggregates, despite the fact that we had a built-in regular aggregate array_agg() that was known to be undeclarable in SQL because its final function had an illegal signature. So what we should have done, and what this patch does, is to decouple the extra-dummy-arguments behavior from ordered-set aggregates and make it generally available for all aggregate declarations. We have to put this into 9.4 rather than waiting till later because it slightly alters the rules for declaring ordered-set aggregates. The patch turned out a bit bigger than I'd hoped because it proved necessary to record the extra-arguments option in a new pg_aggregate column. I'd thought we could just look at the final function's pronargs at runtime, but that didn't work well for variadic final functions. It's probably just as well though, because it simplifies life for pg_dump to record the option explicitly. While at it, fix array_agg() to have a valid final-function signature, and add an opr_sanity test to notice future deviations from polymorphic consistency. I also marked the percentile_cont() aggregates as not needing extra arguments, since they don't. http://git.postgresql.org/pg/commitdiff/f0fedfe82c8adea78354652d67c027a1a8fbce88
  • Fix incorrect pg_proc.proallargtypes entries for two built-in functions. pg_sequence_parameters() and pg_identify_object() have had incorrect proallargtypes entries since 9.1 and 9.3 respectively. This was mostly masked by the correct information in proargtypes, but a few operations such as pg_get_function_arguments() (and thus psql's \df display) would show the wrong data types for these functions' input parameters. In HEAD, fix the wrong info, bump catversion, and add an opr_sanity regression test to catch future mistakes of this sort. In the back branches, just fix the wrong info so that installations initdb'd with future minor releases will have the right data. We can't force an initdb, and it doesn't seem like a good idea to add a regression test that will fail on existing installations. Andres Freund http://git.postgresql.org/pg/commitdiff/a0f9358149b78c62871a0b7d3c167b78f9b2c77e
  • Reset pg_stat_activity.xact_start during PREPARE TRANSACTION. Once we've completed a PREPARE, our session is not running a transaction, so its entry in pg_stat_activity should show xact_start as null, rather than leaving the value as the start time of the now-prepared transaction. I think possibly this oversight was triggered by faulty extrapolation from the adjacent comment that says PrepareTransaction should not call AtEOXact_PgStat, so tweak the wording of that comment. Noted by Andres Freund while considering bug #10123 from Maxim Boguk, although this error doesn't seem to explain that report. Back-patch to all active branches. http://git.postgresql.org/pg/commitdiff/d19bd29f07aef9e508ff047d128a4046cc8bc1e2
  • Clean up temp installations after client program tests. Commit 7d0f493f19607774fdccb1a1ea06fdd96a3d9698 added infrastructure to perform tests in assorted src/bin/ subdirectories, but forgot to teach "make clean" to clean up the detritus the tests leave behind. http://git.postgresql.org/pg/commitdiff/49137ec9d469f744289d0dfa2487a7fc1ef217cb
  • Fix off-by-one bug in LWLockRegisterTranche(). Original coding failed to enlarge the array as required if the requested tranche_id was equal to LWLockTranchesAllocated. In passing, fix poor style of not casting the result of (re)palloc. http://git.postgresql.org/pg/commitdiff/4bfc5f1396b18da3a0db73e4406badc4ce793a1e
  • Record the proper typmod for an index expression column. We should use exprTypmod() to extract the typmod of the expression, instead of just blindly storing -1. This seems to have been an aboriginal oversight in commit fc8d970cbcdd6f025475822a4cf01dfda0873226 which introduced general-expression indexes. The consequences are only cosmetic at present, since the index machinery doesn't really look at typmod for index columns; but still it seems best to describe the column type as precisely as we can. Per off-list complaint from Thomas Fanghaenel. http://git.postgresql.org/pg/commitdiff/39b0c7681e465f3e486ca2a5d13fbbafbe25cb1a
  • Don't #include utils/palloc.h in common/fe_memutils.h. This breaks the principle that common/ ought not depend on anything in the server, not only code-wise but in the headers. The only arguable advantage is avoidance of duplication of half a dozen extern declarations, and even that is rather dubious, considering that the previous coding was wrong about which declarations to duplicate: it exposed pnstrdup() to frontend code even though no such function is provided in fe_memutils.c. On the same principle, don't #include utils/memutils.h in the frontend build of psprintf.c. This requires duplicating the definition of MaxAllocSize, but that seems fine to me: there's no a-priori reason why frontend code should use the same size limit as the backend anyway. In passing, clean up some rather odd layout and ordering choices that were imposed on palloc.h to reduce the number of #ifdefs required by the previous approach. Per gripe from Christoph Berg. There's still more work to do to make include/common/ clean, but this part seems reasonably noncontroversial. http://git.postgresql.org/pg/commitdiff/528c454b2ada89ca0f0cd9a64f939e775b55b879
  • Improve generation algorithm for database system identifier. As noted some time ago, the original coding had a typo ("|" for "^") that made the result less unique than intended. Even the intended behavior is obsolete since it was based on wanting to produce a usable value even if we didn't have int64 arithmetic --- a limitation we stopped supporting years ago. Instead, let's redefine the system identifier as tv_sec in the upper 32 bits (same as before), tv_usec in the next 20 bits, and the low 12 bits of getpid() in the remaining bits. This is still hardly guaranteed-universally-unique, but it's noticeably better than before. Per my proposal at <29019.1374535940@sss.pgh.pa.us> http://git.postgresql.org/pg/commitdiff/5035701e07e8bd395aa878465a102afd7b74e8c3
  • Can't completely get rid of #ifndef FRONTEND in palloc.h pg_controldata includes postgres.h not postgres_fe.h, so utils/palloc.h must be able to compile in a "#define FRONTEND" context. It appears that Solaris Studio is smart enough to persuade us to define PG_USE_INLINE, but not smart enough to not make a copy of unreferenced static functions; which leads to an unsatisfied reference to CurrentMemoryContext. So we need an #ifndef FRONTEND around that declaration. Per buildfarm. http://git.postgresql.org/pg/commitdiff/a9baeb361d635963a19a0268a7d60636c813d2ee

Heikki Linnakangas a poussé :

Peter Eisentraut a poussé :

Bruce Momjian a poussé :

Magnus Hagander a poussé :

Alvaro Herrera a poussé :

  • Fix race when updating a tuple concurrently locked by another process. If a tuple is locked, and this lock is later upgraded either to an update or to a stronger lock, and in the meantime some other process tries to lock, update or delete the same tuple, it (the tuple) could end up being updated twice, or having conflicting locks held. The reason for this is that the second updater checks for a change in Xmax value, or in the HEAP_XMAX_IS_MULTI infomask bit, after noticing the first lock; and if there's a change, it restarts and re-evaluates its ability to update the tuple. But it neglected to check for changes in lock strength or in lock-vs-update status when those two properties stayed the same. This would lead it to take the wrong decision and continue with its own update, when in reality it shouldn't do so but instead restart from the top. This could lead to either an assertion failure much later (when a multixact containing multiple updates is detected), or duplicate copies of tuples. To fix, make sure to compare the other relevant infomask bits alongside the Xmax value and HEAP_XMAX_IS_MULTI bit, and restart from the top if necessary. Also, in the belt-and-suspenders spirit, add a check to MultiXactCreateFromMembers that a multixact being created does not have two or more members that are claimed to be updates. This should protect against other bugs that might cause similar bogus situations. Backpatch to 9.3, where the possibility of multixacts containing updates was introduced. (In prior versions it was possible to have the tuple lock upgraded from shared to exclusive, and an update would not restart from the top; yet we're protected against a bug there because there's always a sleep to wait for the locking transaction to complete before continuing to do anything. Really, the fact that tuple locks always conflicted with concurrent updates is what protected against bugs here.) Per report from Andrew Dunstan and Josh Berkus in thread at http://www.postgresql.org/message-id/534C8B33.9050807@pgexperts.com Bug analysis by Andres Freund. http://git.postgresql.org/pg/commitdiff/1a917ae8610d44985fd2027da0cfe60ccece9104

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Dmitry Voronin sent in two more revisions of a patch to add functionality to the sslinfo contrib extension.
  • Bruce Momjian sent in a patch to fix an issue where pg_dump was ignoring read and write errors, which manifested as failure to produce a return code in the -Fd case.
  • Etsuro Fujita sent in a patch to revert that part of CREATE FOREIGN TABLE that could create an OID column.
  • Andres Freund sent in a patch to add a GUC which controls whether shared memory is System V or mmap.
  • Etsuro Fujita sent in a patch to fix the docs for ALTER TABLE.
  • Michael Paquier sent in a patch to ensure correct compilation of pg_recvlogical on Windows.
  • Etsuro Fujita sent in a patch to fix the docs for foreign data wrapper handlers.
  • Gurjeet Singh and Tom Lane traded patches to flatten long AND/OR lists in queries rather than processing them recursively as occurs now.
  • Tom Lane sent in another revision of a patch to remove TOAST pointers from composite Datums.
  • Gregory Stark sent in another revision of a patch to improve the display of wide tables in psql.

par N Bougain le jeudi 8 mai 2014 à 14h49

jeudi 24 avril 2014

Actualités PostgreSQL.fr

PGDay France 2014

Les 5 et 6 juin prochains le PGDay France fêtera les 10 ans de la communauté francophone de PostgreSQL !

A cette occasion, la communauté francophone aura le grand plaisir d'accueillir **Bruce Momjan** l'un des co-fondateurs et leader du SGBDR open source.

Le PGDay France 2014 est un évènement de deux jours, composés d'ateliers, de conférences et d'échanges sur le thème de PostgreSQL. Cela sera ainsi l'occasion de rencontrer Bruce Momjan mais aussi les principaux acteurs PostgreSQL en France.

Que vous soyez DBA, architecte, développeur, administrateur système, chef de projet utilisant PostgreSQL, vous découvrirez des retours d'expérience d'autres utilisateurs, ainsi que des présentations techniques de PostgreSQL, de PostGIS (cartouche spatiale) ou d'autres extensions.

Cet événement est organisé par la communauté francophone des utilisateurs de PostgreSQL, avec le soutien de plusieurs entreprises partenaires.

Inscrivez-vous dès à présent, et retrouvez les informations complémentaires sur le site PGDay France <http://www.pgday.fr/>.

Le programme des conférences vous fera découvrir les sujets suivants :

 * 2TB of RAM ought to be enough for anybody, par //Jean-Louis Bergamo// (Le Bon Coin)
 * Les nouveautés de PostgreSQL 9.4, par //Damien Clochard// (DALIBO)
 * Gestion du cache en lecture / écriture par //Cédric Villemain//  (2ndQuadrant)
 * Adoption de PostgreSQL dans les grandes entreprises par //Jean-Paul Argudo//  (DALIBO)
 * Aller plus loin avec VACUUM par //Julien Rouhaud// (DALIBO)
 * Carte Sanitaire, Carte Universitaire, Carte des Cantines Scolaires de Côte d’Ivoire par //Solange NIAMKE//
 * Intégrer des données dans PostgreSQL par //Dimitri Fontaine// (2ndQuadrant)
 * La réplication avec PostgreSQL 9.4 par //Thomas Reiss//  (DALIBO)
 * Principales avancées et nouveautés de PostGIS par //Olivier Courtin// (Oslandia)
 * Expérience du 3ème type par //Grégoire Hubert// (PragmaFabrik)

Le nombre de places étant limité, nous vous invitons à vous inscrire rapidement ! <http://www.pgday.fr/inscription.html>

Rendez-vous à Toulon les 5 et 6 juin !

par Gautier Di Folco le jeudi 24 avril 2014 à 09h04

lundi 21 avril 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 20 avril 2014

La PGConf européenne 2014 se tiendra à Madrid (Espagne) du 21 au 24 octobre, à l'hôtel Miguel Angel : http://2014.pgconf.eu/

[ndt: résumé du dernier meetup parisien (le prochain aura peut-être lieu au mois de juin) : http://tapoueh.org/blog/2014/04/17-Meetup-PostgreSQL.html]

Les nouveautés des produits dérivés

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.

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é :

  • GIN entry pages follow the standard page layout - tell XLogInsert. The entry B-tree pages all follow the standard page layout. The 9.3 code has this right. I inadvertently changed this at some point during the big refactorings in git master. http://git.postgresql.org/pg/commitdiff/1bd3842163f2b44e29938b5ab158bb6a589ff10c
  • Remove dead checks for invalid left page in ginDeletePage. In some places, the function assumes the left page is valid, and in others, it checks if it is valid. Remove all the checks. http://git.postgresql.org/pg/commitdiff/e3e6e3af560116adc20cc9d6e75bfe82a45def60
  • Set pd_lower on internal GIN posting tree pages. This allows squeezing out the unused space in full-page writes. And more importantly, it can be a useful debugging aid. In hindsight we should've done this back when GIN was added - we wouldn't need the 'maxoff' field in the page opaque struct if we had used pd_lower and pd_upper like on normal pages. But as long as there can be pages in the index that have been binary-upgraded from pre-9.4 versions, we can't rely on that, and have to continue using 'maxoff'. Most of the code churn comes from renaming some macros, now that they're used on internal pages, too. This change is completely backwards-compatible, no effect on pg_upgrade. http://git.postgresql.org/pg/commitdiff/f1dadd34fa9fccc72800ed206b8c274073dfd039
  • Use correctly-sized buffer when zero-filling a WAL file. I mixed up BLCKSZ and XLOG_BLCKSZ when I changed the way the buffer is allocated a couple of weeks ago. With the default settings, they are both 8k, but they can be changed at compile-time. http://git.postgresql.org/pg/commitdiff/848b9f05ab283724dd063d936a92568c1fdf422b
  • Set the all-visible flag on heap page before writing WAL record, not after. If we set the all-visible flag after writing WAL record, and XLogInsert takes a full-page image of the page, the image would not include the flag. We will then proceed to set the VM bit, which would then be set without the corresponding all-visible flag on the heap page. Found by comparing page images on master and standby, after writing/replaying each WAL record. (There is still a discrepancy: the all-visible flag won't be set after replaying the HEAP_CLEAN record, even though it is set in the master. However, it will be set when replaying the HEAP2_VISIBLE record and setting the VM bit, so the all-visible flag and VM bit are always consistent on the standby, even though they are momentarily out-of-sync with master) Backpatch to 9.3 where this code was introduced. http://git.postgresql.org/pg/commitdiff/2a8e1ac598c864ac2775f33da21a117c363c6c7f

Robert Haas a poussé :

Tom Lane a poussé :

  • Fix bogus handling of bad strategy number in GIST consistent() functions. Make sure we throw an error instead of silently doing the wrong thing when fed a strategy number we don't recognize. Also, in the places that did already throw an error, spell the error message in a way more consistent with our message style guidelines. Per report from Paul Jones. Although this is a bug, it won't occur unless a superuser tries to do something he shouldn't, so it doesn't seem worth back-patching. http://git.postgresql.org/pg/commitdiff/4dfb065b3ab662dcc96d07ee7fc9dadf6975a0cb
  • Use AF_UNSPEC not PF_UNSPEC in getaddrinfo calls. According to the Single Unix Spec and assorted man pages, you're supposed to use the constants named AF_xxx when setting ai_family for a getaddrinfo call. In a few places we were using PF_xxx instead. Use of PF_xxx appears to be an ancient BSD convention that was not adopted by later standardization. On BSD and most later Unixen, it doesn't matter much because those constants have equivalent values anyway; but nonetheless this code is not per spec. In the same vein, replace PF_INET by AF_INET in one socket() call, which wasn't even consistent with the other socket() call in the same function let alone the remainder of our code. Per investigation of a Cygwin trouble report from Marco Atzeri. It's probably a long shot that this will fix his issue, but it's wrong in any case. http://git.postgresql.org/pg/commitdiff/cad4fe6455bdc9ef1026b4a247eeb588ab3a8bd6
  • Update oidjoins regression test for 9.4. Now that we're pretty much feature-frozen, it's time to update the checks on system catalog foreign-key references. (It looks like we missed doing this altogether for 9.3. Sigh.) http://git.postgresql.org/pg/commitdiff/cbb5e23bfa92973bddf11935448a9d42c32f7d42
  • Fix contrib/postgres_fdw's remote-estimate representation of array Params. We were emitting "(SELECT null::typename)", which is usually interpreted as a scalar subselect, but not so much in the context "x = ANY(...)". This led to remote-side parsing failures when remote_estimate is enabled. A quick and ugly fix is to stick in an extra cast step, "((SELECT null::typename)::typename)". The cast will be thrown away as redundant by parse analysis, but not before it's done its job of making sure the grammar sees the ANY argument as an a_expr rather than a select_with_parens. Per an example from Hannu Krosing. http://git.postgresql.org/pg/commitdiff/5b68d81697bcb0d16136bd037e454ee53c521185
  • Rename EXPLAIN ANALYZE's "total runtime" output to "execution time". Now that EXPLAIN also outputs a "planning time" measurement, the use of "total" here seems rather confusing: it sounds like it might include the planning time which of course it doesn't. Majority opinion was that "execution time" is a better label, so we'll call it that. This should be noted as a backwards incompatibility for tools that examine EXPLAIN ANALYZE output. In passing, I failed to resist the temptation to do a little editing on the materialized-view example affected by this change. http://git.postgresql.org/pg/commitdiff/5f86cbd714c9d43c0fbb43a7b172f77ebf429548
  • Fix unused-variable warning on Windows. Introduced in 585bca39: msgid is not used in the Windows code path. Also adjust comments a tad (mostly to keep pgindent from messing it up). David Rowley http://git.postgresql.org/pg/commitdiff/01563158235f5650743fd9b1dfa80c3d8faf89bb

Peter Eisentraut a poussé :

Bruce Momjian a poussé :

Magnus Hagander a poussé :

Andrew Dunstan a poussé :

  • Attempt to get plpython regression tests working again for MSVC builds. This has probably been broken for quite a long time. Buildfarm member currawong's current results suggest that it's been broken since 9.1, so backpatch this to that branch. This only supports Python 2 - I will handle Python 3 separately, but this is a fairly simple fix. http://git.postgresql.org/pg/commitdiff/ef158312e96960bfbc802ceda58f432f6fd845eb

Alvaro Herrera a poussé :

Correctifs rejetés (à ce jour)

  • Andreas 'ads' Scherbaum's patch to change iff to if. The former is actually correct.

Correctifs en attente

  • David Rowley sent in another revision of a patch to optimize window functions by allowing pushdowns of items matching PARTITION BY clauses.
  • Heikki Linnakangas sent in a patch to fix a race condition between PREPARE TRANSACTION and COMMIT PREPARED.
  • Fabrízio de Royes Mello sent in another revision of a patch to implement CINE functionality for sequences.
  • Stephen Frost sent in another revision of a patch to implement row-level access controls.
  • Peter Geoghegan sent in a patch to improve on the current clock sweep algorithm for B-tree leaf pages.
  • Amit Kapila sent in a patch to fix a dsm invalid errcode issue.
  • Zoltan Boszormenyi sent in another rebased revision of the ECPG FETCH patch.
  • Nicholas White sent in another revision of a patch to make it possible for lag and lead window functions to ignore nulls.
  • Dmitri Voronin sent in a patch to add a new sslinfo extension which includes: ssl_get_count_of_extensions(), ssl_get_extension_names(), ssl_get_extension_value(text), and ssl_is_critical_extension(text).
  • Michael Paquier and Fabrízio de Royes Mello traded patches to make it possible for the verbose output of pg_dump to include schema names for everything dumped.
  • MauMau sent in another revision of a patch to fix an issue where pg_ctl always uses the same event source on Windows.
  • Tom Lane sent in two more revisions of a patch to detoast composite array elements.
  • Mohammad Alhashash sent in a patch to allow empty targets in the unaccent dictionary.
  • Christian Ullrich sent in a patch to arrange things so on Windows, PostgreSQL silently ignores control-C and control-break when started via pg_ctl.
  • Michael Paquier sent in a patch to remove a dependency on wsock32.lib when compiling code on Windows.

par N Bougain le lundi 21 avril 2014 à 19h55

mardi 15 avril 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 13 avril 2014

Google hangout le 17 avril à 8h UTC intitulé "Postgres Performance Diagnostics: Busting that slow running SQL" (diagnostics de performance sous PostgreSQL : débusquer cet SQL paresseux). RSVP: https://plus.google.com/events/cm0roo9chi2s6p3afp679lhv1bk

Postgres Open 2014 aura lieu à Chicago, Illinois - USA, du 17 au 19 septembre. L'appel à conférenciers est lancé : http://postgresopen.org/2014/callforpapers/

Les nouveautés des produits dérivés

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.

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é :

Robert Haas a poussé :

Tom Lane a poussé :

  • Add an in-core GiST index opclass for inet/cidr types. This operator class can accelerate subnet/supernet tests as well as btree-equivalent ordered comparisons. It also handles a new network operator inet && inet (overlaps, a/k/a "is supernet or subnet of"), which is expected to be useful in exclusion constraints. Ideally this opclass would be the default for GiST with inet/cidr data, but we can't mark it that way until we figure out how to do a more or less graceful transition from the current situation, in which the really-completely-bogus inet/cidr opclasses in contrib/btree_gist are marked as default. Having the opclass in core and not default is better than not having it at all, though. While at it, add new documentation sections to allow us to officially document GiST/GIN/SP-GiST opclasses, something there was never a clear place to do before. I filled these in with some simple tables listing the existing opclasses and the operators they support, but there's certainly scope to put more information there. Emre Hasegeli, reviewed by Andreas Karlsson, further hacking by me http://git.postgresql.org/pg/commitdiff/f23a5630ebc797219b62797f566dec9f65090e03
  • Create infrastructure for moving-aggregate optimization. Until now, when executing an aggregate function as a window function within a window with moving frame start (that is, any frame start mode except UNBOUNDED PRECEDING), we had to recalculate the aggregate from scratch each time the frame head moved. This patch allows an aggregate definition to include an alternate "moving aggregate" implementation that includes an inverse transition function for removing rows from the aggregate's running state. As long as this can be done successfully, runtime is proportional to the total number of input rows, rather than to the number of input rows times the average frame length. This commit includes the core infrastructure, documentation, and regression tests using user-defined aggregates. Follow-on commits will update some of the built-in aggregates to use this feature. David Rowley and Florian Pflug, reviewed by Dean Rasheed; additional hacking by me http://git.postgresql.org/pg/commitdiff/a9d9acbf219b9e96585779cd5f99d674d4ccba74
  • Provide moving-aggregate support for a bunch of numerical aggregates. First installment of the promised moving-aggregate support in built-in aggregates: count(), sum(), avg(), stddev() and variance() for assorted datatypes, though not for float4/float8. In passing, remove a 2001-vintage kluge in interval_accum(): interval array elements have been properly aligned since around 2003, but nobody remembered to take out this workaround. Also, fix a thinko in the opr_sanity tests for moving-aggregate catalog entries. David Rowley and Florian Pflug, reviewed by Dean Rasheed http://git.postgresql.org/pg/commitdiff/9d229f399e87d2ae7132c2e8feef317ce1479728
  • Provide moving-aggregate support for boolean aggregates. David Rowley and Florian Pflug, reviewed by Dean Rasheed http://git.postgresql.org/pg/commitdiff/d95425c8b9d3ea1681bd91b76ce73be95ca5ee21
  • Suppress compiler warning in new contrib/pg_trgm code. MSVC doesn't seem to like it when a constant initializer loses precision upon being assigned. David Rowley http://git.postgresql.org/pg/commitdiff/46a60abfe9fa13087dbbe15953c20df35f006968
  • Improve some O(N^2) behavior in window function evaluation. Repositioning the tuplestore seek pointer in window_gettupleslot() turns out to be a very significant expense when the window frame is sizable and the frame end can move. To fix, introduce a tuplestore function for skipping an arbitrary number of tuples in one call, parallel to the one we introduced for tuplesort objects in commit 8d65da1f. This reduces the cost of window_gettupleslot() to O(1) if the tuplestore has not spilled to disk. As in the previous commit, I didn't try to do any real optimization of tuplestore_skiptuples for the case where the tuplestore has spilled to disk. There is probably no practical way to get the cost to less than O(N) anyway, but perhaps someone can think of something later. Also fix PersistHoldablePortal() to make use of this API now that we have it. Based on a suggestion by Dean Rasheed, though this turns out not to look much like his patch. http://git.postgresql.org/pg/commitdiff/e0c91a7ff015fab0ccbb0f75b6819f29ae00295e

Michael Meskes a poussé :

Bruce Momjian a poussé :

Stephen Frost a poussé :

  • Make security barrier views automatically updatable. Views which are marked as security_barrier must have their quals applied before any user-defined quals are called, to prevent user-defined functions from being able to see rows which the security barrier view is intended to prevent them from seeing. Remove the restriction on security barrier views being automatically updatable by adding a new securityQuals list to the RTE structure which keeps track of the quals from security barrier views at each level, independently of the user-supplied quals. When RTEs are later discovered which have securityQuals populated, they are turned into subquery RTEs which are marked as security_barrier to prevent any user-supplied quals being pushed down (modulo LEAKPROOF quals). Dean Rasheed, reviewed by Craig Ringer, Simon Riggs, KaiGai Kohei http://git.postgresql.org/pg/commitdiff/842faa714c0454d67e523f5a0b6df6500e9bc1a5
  • Make a dedicated AlterTblSpcStmt production. Given that ALTER TABLESPACE has moved on from just existing for general purpose rename/owner changes, it deserves its own top-level production in the grammar. This also cleans up the RenameStmt to only ever be used for actual RENAMEs again- it really wasn't appropriate to hide non-RENAME productions under there. Noted by Alvaro Herrera. http://git.postgresql.org/pg/commitdiff/5f508b6dea19b66961c645bf5e5c427ac3af8359
  • Add ANALYZE into regression tests. Looks like we can end up with different plans happening on the buildfarm, which breaks the regression tests when we include EXPLAIN output (which is done in the regression tests for updatable security views, to ensure that the user-defined function isn't pushed down to a level where it could view the rows before the security quals are applied). This adds in ANALYZE to hopefully make the plans consistent. The ANALYZE ends up changing the original plan too, so the update looks bigger than it really is. The new plan looks perfectly valid, of course. http://git.postgresql.org/pg/commitdiff/b3e6593716efef901fcc847f33256c6b49958898

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Michael Paquier sent in two more revisions of a patch to include replication slot data in base backups.
  • Ian Lawrence Barwick sent in three revisions of a patch to add psql tab completion for event triggers.
  • Vaishnavi Prabakaran sent in a patch to add a new option in pg_basebackup to exclude pg_log files during base backup.
  • Rajeev Rastogi sent in two revisions of a patch to add autonomous transactions.
  • Etsuro Fujita sent in two revisions of a patch to improve the ALTER TABLE documentation.
  • Bruce Momjian sent in another revision of a patch to fix a socket issue on Windows.
  • Simon Riggs sent in a patch to implement ALTER TABLE ... SET reloptions.
  • Amit Kapila sent in a patch to fix a dsm invalid errcode issue.
  • Bruce Momjian sent in another revision of a patch to fix the oid display in psql's \d+.
  • Kyotaro HORIGUCHI sent in another revision of a patch to get more from indexes.
  • Sergey Muraviov and Gregory Stark traded patches to improve the display of wide tables in psql.
  • MauMau sent in another revision of a patch to fix an issue where pg_ctl always uses the same event source.
  • Andres Freund sent in a patch to add pinning_backends column to the pg_buffercache extension.
  • David Rowley sent in a patch to add a window function optimization which allows pushdowns of items matching PARTITION BY clauses.
  • Jan Wieck sent in two revisions of a patch to ensure that a snapshot's txids are unique.

par N Bougain le mardi 15 avril 2014 à 22h14

samedi 12 avril 2014

Guillaume Lelarge

Nouvelle série d'articles pour GLMF

Après l'article sur les nouveautés de la version 9.3, j'ai entamé une série d'articles sur le fonctionnement du planificateur (aussi appelé optimiseur).

Le premier est sorti ce mois-ci, dans le numéro 170 de GNU/Linux Magazine France. Il traite des différents types de parcours que l'optimiseur peut planifier.

J'ai loupé le coche pour le prochain numéro, donc celui sur les jointures devrait sortir dans le numéro 172 si tout va bien. En fait, je viens tout juste de finir son écriture.

Il y aura certainement deux autres articles, un sur les autres types de nœuds et un sur les outils pour la compréhension des plans d'exécution mais ils restent à écrire.

En attendant, je suis preneur de toute remarque/critique sur mes articles :)

par Guillaume Lelarge le samedi 12 avril 2014 à 16h11

vendredi 11 avril 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 6 avril 2014

Les nouveautés des produits dérivés

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.

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é :

Robert Haas a poussé :

Tom Lane a poussé :

  • Doc: improve discussion of reverse+forward host name lookup in pg_hba.conf. Fix some grammatical issues and make it a bit more readable. http://git.postgresql.org/pg/commitdiff/6eff0accfe6b6170d10b91df769ea523b50927b8
  • Fix bugs in manipulation of PgBackendStatus.st_clienthostname. Initialization of this field was not being done according to the st_changecount protocol (it has to be done within the changecount increment range, not outside). And the test to see if the value should be reported as null was wrong. Noted while perusing uses of Port.remote_hostname. This was wrong from the introduction of this code (commit 4a25bc145), so back-patch to 9.1. http://git.postgresql.org/pg/commitdiff/682c5bbec5d9533d2d654d6a096c36bbae9f5bd0
  • De-anonymize the union in JsonbValue. Needed for strict C89 compliance. http://git.postgresql.org/pg/commitdiff/f33a71a7865a1dd54f04b370e2637f88665f8db8
  • Fix assorted issues in client host name lookup. The code for matching clients to pg_hba.conf lines that specify host names (instead of IP address ranges) failed to complain if reverse DNS lookup failed; instead it silently didn't match, so that you might end up getting a surprising "no pg_hba.conf entry for ..." error, as seen in bug #9518 from Mike Blackwell. Since we don't want to make this a fatal error in situations where pg_hba.conf contains a mixture of host names and IP addresses (clients matching one of the numeric entries should not have to have rDNS data), remember the lookup failure and mention it as DETAIL if we get to "no pg_hba.conf entry". Apply the same approach to forward-DNS lookup failures, too, rather than treating them as immediate hard errors. Along the way, fix a couple of bugs that prevented us from detecting an rDNS lookup error reliably, and make sure that we make only one rDNS lookup attempt; formerly, if the lookup attempt failed, the code would try again for each host name entry in pg_hba.conf. Since more or less the whole point of this design is to ensure there's only one lookup attempt not one per entry, the latter point represents a performance bug that seems sufficient justification for back-patching. Also, adjust src/port/getaddrinfo.c so that it plays as well as it can with this code. Which is not all that well, since it does not have actual support for rDNS lookup, but at least it should return the expected (and required by spec) error codes so that the main code correctly perceives the lack of functionality as a lookup failure. It's unlikely that PG is still being used in production on any machines that require our getaddrinfo.c, so I'm not excited about working harder than this. To keep the code in the various branches similar, this includes back-patching commits c424d0d1052cb4053c8712ac44123f9b9a9aa3f2 and 1997f34db4687e671690ed054c8f30bb501b1168 into 9.2 and earlier. Back-patch to 9.1 where the facility for hostnames in pg_hba.conf was introduced. http://git.postgresql.org/pg/commitdiff/fc752505a99a4e2c781a070d3d42a25289c22e3c
  • Avoid promising that "ADD COLUMN ... DEFAULT NULL" is free. The system realizes that DEFAULT NULL is dummy in simple cases, but not if a cast function (such as a length coercion) needs to be applied. It's dubious that suppressing that function call would be appropriate, anyway. For the moment, let's just adjust the docs to say that you should omit the DEFAULT clause if you don't want a rewrite to happen. Per gripe from Amit Langote. http://git.postgresql.org/pg/commitdiff/879808e5197c374e431e81fb5599dfea533bb9aa
  • Fix documentation about joining pg_locks to other views. The advice to join to pg_prepared_xacts via the transaction column was not updated when the transaction column was replaced by virtualtransaction. Since it's not quite obvious how to do that join, give an explicit example. For consistency also give an example for the adjacent case of joining to pg_stat_activity. And link-ify the view references too, just because we can. Per bug #9840 from Alexey Bashtanov. Michael Paquier and Tom Lane http://git.postgresql.org/pg/commitdiff/42c6236f37988b4cb067f3fc908b247e70177496
  • Code review for commit d26888bc4d1e539a82f21382b0000fe5bbf889d9. Mostly, copy-edit the comments; but also fix it to not reject domains over arrays. http://git.postgresql.org/pg/commitdiff/741364bf5caeeae79b83bbdba778805d286622ba
  • Fix non-equivalence of VARIADIC and non-VARIADIC function call formats. For variadic functions (other than VARIADIC ANY), the syntaxes foo(x,y,...) and foo(VARIADIC ARRAY[x,y,...]) should be considered equivalent, since the former is converted to the latter at parse time. They have indeed been equivalent, in all releases before 9.3. However, commit 75b39e790 made an ill-considered decision to record which syntax had been used in FuncExpr nodes, and then to make equal() test that in checking node equality --- which caused the syntaxes to not be seen as equivalent by the planner. This is the underlying cause of bug #9817 from Dmitry Ryabov. It might seem that a quick fix would be to make equal() disregard FuncExpr.funcvariadic, but the same commit made that untenable, because the field actually *is* semantically significant for some VARIADIC ANY functions. This patch instead adopts the approach of redefining funcvariadic (and aggvariadic, in HEAD) as meaning that the last argument is a variadic array, whether it got that way by parser intervention or was supplied explicitly by the user. Therefore the value will always be true for non-ANY variadic functions, restoring the principle of equivalence. (However, the planner will continue to consider use of VARIADIC as a meaningful difference for VARIADIC ANY functions, even though some such functions might disregard it.) In HEAD, this change lets us simplify the decompilation logic in ruleutils.c, since the funcvariadic/aggvariadic flag tells directly whether to print VARIADIC. However, in 9.3 we have to continue to cope with existing stored rules/views that might contain the previous definition. Fortunately, this just means no change in ruleutils.c, since its existing behavior effectively ignores funcvariadic for all cases other than VARIADIC ANY functions. In HEAD, bump catversion to reflect the fact that FuncExpr.funcvariadic changed meanings; this is sort of pro forma, since I don't believe any built-in views are affected. Unfortunately, this patch doesn't magically fix everything for affected 9.3 users. After installing 9.3.5, they might need to recreate their rules/views/indexes containing variadic function calls in order to get everything consistent with the new definition. As in the cited bug, the symptom of a problem would be failure to use a nominally matching index that has a variadic function call in its definition. We'll need to mention this in the 9.3.5 release notes. http://git.postgresql.org/pg/commitdiff/c7b353959931ae8e95177fe0a138b8119db9b802
  • Fix bogus time printout in walreceiver's debug log messages. The displayed sendtime and receipttime were always exactly equal, because somebody forgot that timestamptz_to_str returns a static buffer (thereby simplifying life for most callers, at the cost of complicating it for those who need two results concurrently). Apply the same pstrdup solution used by the other call sites with this issue. Back-patch to 9.2 where the faulty code was introduced. Per bug #9849 from Haruka Takatsuka, though this is not exactly his patch. Possibly we should change timestamptz_to_str's API, but I wouldn't want to do so in the back branches. http://git.postgresql.org/pg/commitdiff/8120c7452a51a773ad7a249b55557439f39d41ef
  • Make sure -D is an absolute path when starting server on Windows. This is needed because Windows services may get started with a different current directory than where pg_ctl is executed. We want relative -D paths to be interpreted relative to pg_ctl's CWD, similarly to what happens on other platforms. In support of this, move the backend's make_absolute_path() function into src/port/path.c (where it probably should have been long since) and get rid of the rather inferior version in pg_regress. Kumar Rajeev Rastogi, reviewed by MauMau http://git.postgresql.org/pg/commitdiff/9aca51250681d2e8d18ed1d73e7cd1283d1cf303
  • Preserve errno across free(). Dept. of second thoughts: free() isn't guaranteed not to change errno. Make sure we report the right error if getcwd() fails. http://git.postgresql.org/pg/commitdiff/2209c0f8618bbed257975055e017efab139e3fa3
  • Allow "-C variable" and "--describe-config" even to root users. There's no really compelling reason to refuse to do these read-only, non-server-starting options as root, and there's at least one good reason to allow -C: pg_ctl uses -C to find out the true data directory location when pointed at a config-only directory. On Windows, this is done before dropping administrator privileges, which means that pg_ctl fails for administrators if and only if a config-only layout is used. Since the root-privilege check is done so early in startup, it's a bit awkward to check for these switches. Make the somewhat arbitrary decision that we'll only skip the root check if -C is the first switch. This is not just to make the code a bit simpler: it also guarantees that we can't misinterpret a --boot mode switch. (While AuxiliaryProcessMain doesn't currently recognize any such switch, it might have one in the future.) This is no particular problem for pg_ctl, and since the whole behavior is undocumented anyhow, it's not a documentation issue either. (--describe-config only works as the first switch anyway, so this is no restriction for that case either.) Back-patch to 9.2 where pg_ctl first began to use -C. MauMau, heavily edited by me http://git.postgresql.org/pg/commitdiff/b203c57bb778d90bb8728be19e78825134d5820f
  • Fix tablespace creation WAL replay to work on Windows. The code segment that removes the old symlink (if present) wasn't clued into the fact that on Windows, symlinks are junction points which have to be removed with rmdir(). Backpatch to 9.0, where the failing code was introduced. MauMau, reviewed by Muhammad Asif Naeem and Amit Kapila http://git.postgresql.org/pg/commitdiff/abe075dfffe2ef7e76ebbf5717fa3823f9a70a1f
  • ecpg/ecpglib must build the src/port files it uses with -DFRONTEND. Remarkably, this hasn't been noticed before, though it surely should have been happening since around the fall of the Byzantine empire. Commit 438b529604 changed path.c to depend on FRONTEND, and that exposed the omission, per buildfarm reports. I'm suspicious that some other subdirectories are missing this too, but this one change is enough to make ecpg tests pass for me. http://git.postgresql.org/pg/commitdiff/44c5d387eafb4ba1a032f8d7b13d85c553d69181
  • Fix processing of PGC_BACKEND GUC parameters on Windows. EXEC_BACKEND builds (i.e., Windows) failed to absorb values of PGC_BACKEND parameters if they'd been changed post-startup via the config file. This for example prevented log_connections from working if it were turned on post-startup. The mechanism for handling this case has always been a bit of a kluge, and it wasn't revisited when we implemented EXEC_BACKEND. While in a normal forking environment new backends will inherit the postmaster's value of such settings, EXEC_BACKEND backends have to read the settings from the CONFIG_EXEC_PARAMS file, and they were mistakenly rejecting them. So this case has always been broken in the Windows port; so back-patch to all supported branches. Amit Kapila http://git.postgresql.org/pg/commitdiff/6862ca6970d11c47996d99e49a1cf8b55ef9b40d
  • Block signals earlier during postmaster startup. Formerly, we set up the postmaster's signal handling only when we were about to start launching subprocesses. This is a bad idea though, as it means that for example a SIGINT arriving before that will kill the postmaster instantly, perhaps leaving lockfiles, socket files, shared memory, etc laying about. We'd rather that such a signal caused orderly postmaster termination including releasing of those resources. A simple fix is to move the PostmasterMain stanza that initializes signal handling to an earlier point, before we've created any such resources. Then, an early-arriving signal will be blocked until we're ready to deal with it in the usual way. (The only part that really needs to be moved up is blocking of signals, but it seems best to keep the signal handler installation calls together with that; for one thing this ensures the kernel won't drop any signals we wished to get. The handlers won't get invoked in any case until we unblock signals in ServerLoop.) Per a report from MauMau. He proposed changing the way "pg_ctl stop" works to deal with this, but that'd just be masking one symptom not fixing the core issue. It's been like this since forever, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/5d8117e1f38d7240e99d57e624a9d880872c7e98
  • Improve contrib/pg_trgm's heuristics for regexp index searches. When extracting trigrams from a regular expression for search of a GIN or GIST trigram index, it's useful to penalize (preferentially discard) trigrams that contain whitespace, since those are typically far more common in the index than trigrams not containing whitespace. Of course, this should only be a preference not a hard rule, since we might otherwise end up with no trigrams to search for. The previous coding tended to produce fairly inefficient trigram search sets for anchored regexp patterns, as reported by Erik Rijkers. This patch penalizes whitespace-containing trigrams, and also reduces the target number of extracted trigrams, since experience suggests that the original coding tended to select too many trigrams to search for. Alexander Korotkov, reviewed by Tom Lane http://git.postgresql.org/pg/commitdiff/80a5cf643adb496abe577a1ca6dc0c476d849c19

Simon Riggs a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Michael Paquier sent in another revision of a patch to add a new parameter RollbackError to control rollback behavior on error.
  • Edward Behn sent in a patch to allow returning array of composites from PL/Python.
  • Etsuro Fujita sent in two more revisions of a patch to allow foreign tables to be part of table inheritance hierarchies.
  • Yugo Nagata and Robert Haas traded patches to add to_regclass and friends.
  • Peter Geoghegan sent in another revision of a patch to add a B-Tree support function.
  • Bruce Momjian sent in a patch to fix an issue with socket handling on Windows.
  • Fabien COELHO sent in another revision of a patch to add a Gaussian distribution option to pgbench.
  • Kumar Rajeev Rastogi sent in a patch to document the usage of CREATE DATABASE with template specified.
  • Adrian Vondendriesch sent in two more revisions of a patch to provide a allback_application_name in contrib/pgbench, oid2name, and dblink.
  • Ashutosh Bapat sent in a patch to fix an infelicity in how ECPG handles types.
  • Michael Paquier sent in a patch to include replication slot data in base backups.
  • Florian Pflug and Dean Rasheed traded patches to add inverse transition functions for aggregates.
  • Abhijit Menon-Sen sent in a patch to add a fastbloat module.
  • Robert Haas sent in a draft patch to get rid of the dynamic shared memory state file.
  • Heikki Linnakangas sent in another revision of a patch to change the WAL format and API.
  • Kumar Rajeev Rastogi sent in a patch to fix an issue where CREATE TABLE failed to fail on invalid syntax.
  • Emre Hasegeli sent in another revision of a patch to add GiST indexing support for inet data types.

par N Bougain le vendredi 11 avril 2014 à 00h46

vendredi 4 avril 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 1er avril 2014

PostgreSQL 10 a été publiée.
Cette version est munie d'un système de réplication multi-maîtres, incorporé et sans compromis, d'une intégration complète de tous les autres systèmes de stockage, et un large choix de dialectes SQL parmi lesquels Cassandra, Hadoop, Oracle, MS-SQL Server, MySQL et mSQL. http://db.cs.berkeley.edu/papers/ERL-M85-95.pdf

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.

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

Correctifs rejetés (à ce jour)

  • Déception pour tout le monde cette semaine !

par N Bougain le vendredi 4 avril 2014 à 07h06

Nouvelles hebdomadaires de PostgreSQL - 30 mars 2014

Offres d'emplois autour de PostgreSQL en mars

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é :

Fujii Masao a poussé :

Bruce Momjian a poussé :

Magnus Hagander a poussé :

Andrew Dunstan a poussé :

Tom Lane a poussé :

  • Fix refcounting bug in PLy_modify_tuple(). We must increment the refcount on "plntup" as soon as we have the reference, not sometime later. Otherwise, if an error is thrown in between, the Py_XDECREF(plntup) call in the PG_CATCH block removes a refcount we didn't add, allowing the object to be freed even though it's still part of the plpython function's parsetree. This appears to be the cause of crashes seen on buildfarm member prairiedog. It's a bit surprising that we've not seen it fail repeatably before, considering that the regression tests have been exercising the faulty code path since 2009. The real-world impact is probably minimal, since it's unlikely anyone would be provoking the "Tom Dunstan["new"] is not a dictionary" error in production, and that's the only case that is actually wrong. Still, it's a bug affecting the regression tests, so patch all supported branches. In passing, remove dead variable "plstr", and demote "platt" to a local variable inside the PG_TRY block, since we don't need to clean it up in the PG_CATCH path. http://git.postgresql.org/pg/commitdiff/2d5e0f07de0119045fb889f9c11de0e486ce4ac5
  • Document that Python 2.3 requires cdecimal module for full functionality. This has been true for some time, but we were leaving users to discover it the hard way. Back-patch to 9.2. It might've been true before that, but we were claiming Python 2.2 compatibility before that, so I won't guess at the exact requirements back then. http://git.postgresql.org/pg/commitdiff/f3cfc23195e3363ceab49449ed851944bcaf0849
  • Improve documentation note about Python 2.3 and cdecimal. Explain exactly what fails (ie, function arguments of type numeric) if you don't have it. http://git.postgresql.org/pg/commitdiff/e5a452b3a4600dfc9c045e1591c25e6a567d8d73
  • Un-break peer authentication. Commit 613c6d26bd42dd8c2dd0664315be9551475b8864 sloppily replaced a lookup of the UID obtained from getpeereid() with a lookup of the server's own user name, thus totally destroying peer authentication. Revert. Per report from Christoph Berg. In passing, make sure get_user_name() zeroes *errstr on success on Windows as well as non-Windows. I don't think any callers actually depend on this ATM, but we should be consistent across platforms. http://git.postgresql.org/pg/commitdiff/b777be0d48a042f500cac72140ffb50392973aa2
  • Fix EquivalenceClass processing for nested append relations. The original coding of EquivalenceClasses didn't foresee that appendrel child relations might themselves be appendrels; but this is possible for example when a UNION ALL subquery scans a table with inheritance children. The oversight led to failure to optimize ordering-related issues very well for the grandchild tables. After some false starts involving explicitly flattening the appendrel representation, we found that this could be fixed easily by removing a few implicit assumptions about appendrel parent rels not being children themselves. Kyotaro Horiguchi and Tom Lane, reviewed by Noah Misch http://git.postgresql.org/pg/commitdiff/a87c729153e372f3731689a7be007bc2b53f1410
  • Improve regression test for pg_filenode_relation(). Make it print the details in case there's a failure. Andres Freund, slightly modified by me http://git.postgresql.org/pg/commitdiff/9613a1d98e5f940d8124850e61b0a950157c8863
  • Fix dumping of a materialized view that depends on a table's primary key. It is possible for a view or materialized view to depend on a table's primary key, if the view query relies on functional dependency to abbreviate a GROUP BY list. This is problematic for pg_dump since we ordinarily want to dump view definitions in the pre-data section but indexes in post-data. pg_dump knows how to deal with this situation for regular views, by breaking the view's ON SELECT rule apart from the view proper. But it had not been taught what to do about materialized views, and in fact mistakenly dumped them as regular views in such cases, as seen in bug #9616 from Jesse Denardo. If we had CREATE OR REPLACE MATERIALIZED VIEW, we could fix this in a manner analogous to what's done for regular views; but we don't yet, and we'd not back-patch such a thing into 9.3 anyway. As a hopefully- temporary workaround, break the circularity by postponing the matview into post-data altogether when this case occurs. http://git.postgresql.org/pg/commitdiff/62215de2925705bc607635e45ff800364456b1a1

Noah Misch a poussé :

  • Force consistent row order in contrib/test_decoding regression test. http://git.postgresql.org/pg/commitdiff/7ed908be41fbca1635d34f97138abb13beab8b24
  • Document platform-specificity of unix_socket_permissions. Back-patch to 8.4 (all supported versions). http://git.postgresql.org/pg/commitdiff/fbd32b0cab806a2244bd5171e4b60e53f4a9dfe7
  • Secure Unix-domain sockets of "make check" temporary clusters. Any OS user able to access the socket can connect as the bootstrap superuser and in turn execute arbitrary code as the OS user running the test. Protect against that by placing the socket in the temporary data directory, which has mode 0700 thanks to initdb. Back-patch to 8.4 (all supported versions). The hazard remains wherever the temporary cluster accepts TCP connections, notably on Windows. Attempts to run "make check" from a directory with a long name will now fail. An alternative not sharing that problem was to place the socket in a subdirectory of /tmp, but that is only secure if /tmp is sticky. The PG_REGRESS_SOCK_DIR environment variable is available as a workaround when testing from long directory paths. As a convenient side effect, this lets testing proceed smoothly in builds that override DEFAULT_PGSOCKET_DIR. Popular non-default values like /var/run/postgresql are often unwritable to the build user. Security: CVE-2014-0067 http://git.postgresql.org/pg/commitdiff/31c6e54ec9abab0c63d709e492ef18a701b02641
  • Revert "Secure Unix-domain sockets of "make check" temporary clusters." About half of the buildfarm members use too-long directory names, strongly suggesting that this approach is a dead end. http://git.postgresql.org/pg/commitdiff/8f5578d0f9681ef81bc71a3762a191d66a29c8b1

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Kaigai Kouhei sent in another revision of a patch to implement custom plan nodes.
  • Petr (PJMODOS) Jelinek sent in another revision of a patch to implement plpgsql.extra_warnings, plpgsql.extra_errors.
  • MauMau sent in another revision of a patch to fix an issue where PostgreSQL fails to start on Windows if it crashes after tablespace creation.
  • Michael Paquier sent in a patch to add a new parameter RollbackError to control rollback behavior on error.
  • Etsuro Fujita sent in two more revisions of a patch to implement inherit support for foreign tables.
  • David Rowley and Florian Pflug traded patches to implement inverse transition functions for aggregates.
  • Peter Geoghegan sent in a patch to fix an infelicity in jsonfuncs.c.
  • Ants Aasma sent in a patch to add in a missing pfree in logical_heap_rewrite_flush_mappings().
  • Rajeev Rastogi sent in a patch to fix an issue where datistemplate of pg_database does not behave as per description in documentation.
  • Kaigai Kouhei sent in another revision of a patch to implement the custom plan interface.
  • Tomas Vondra sent in a patch to decreasing memory needlessly consumed by array_agg.
  • Peter Geoghegan sent in a patch to do better at HINTing an appropriate column within errorMissingColumn().
  • Jan Pecek (honzap AT gmail DOT com) sent in a patch to fix an issue where storinig data in pg_toast for a custom type fails.
  • Bruce Momjian and Fabrízio de Royes Mello traded patches to change the display of OID-ness for tables in psql.
  • Peter Geoghegan sent in a patch to extend SortSupport and tuplesort to support "poor man's normalized keys".

par N Bougain le vendredi 4 avril 2014 à 06h49

vendredi 28 mars 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 23 mars 2014

Les versions correctives 9.3.4, 9.2.8, 9.1.13, 9.0.17 et 8.4.21 sont disponibles. Mettez à jour dès que possible : http://www.postgresql.org/about/news/1511/ [ndt: vf]

Le sixième PGDay cubain aura lieu les 13 et 14 octobre 2014 à la Havane : https://postgresql.uci.cu/?p=380

Les appels à conférenciers pour Char(13) et le PGDay anglais, les 8 et 9 juillet 2014 respectivement, ont été lancés et les réponses sont attendues avant le 17 avril. speakers AT char14 DOT info, et speakers AT postgresqlusergroup DOT org DOT uk, respectivement : http://www.char14.info

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

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

Magnus Hagander a poussé :

Fujii Masao a poussé :

Heikki Linnakangas a poussé :

  • Fix thinko: have trueTriConsistentFn return GIN_TRUE. While we're at it, also improve comments in ginlogic.c. http://git.postgresql.org/pg/commitdiff/d663d4399e767223e454302ea90d04f78b2f9d29
  • Make the handling of interrupted B-tree page splits more robust. Splitting a page consists of two separate steps: splitting the child page, and inserting the downlink for the new right page to the parent. Previously, we handled the case that you crash in between those steps with a cleanup routine after the WAL recovery had finished, which finished the incomplete split. However, that doesn't help if the page split is interrupted but the database doesn't crash, so that you don't perform WAL recovery. That could happen for example if you run out of disk space. Remove the end-of-recovery cleanup step. Instead, when a page is split, the left page is marked with a new INCOMPLETE_SPLIT flag, and when the downlink is inserted to the parent, the flag is cleared again. If an insertion sees a page with the flag set, it knows that the split was interrupted for some reason, and inserts the missing downlink before proceeding. I used the same approach to fix GIN and GiST split algorithms earlier. This was the last WAL cleanup routine, so we could get rid of that whole machinery now, but I'll leave that for a separate patch. Reviewed by Peter Geoghegan. http://git.postgresql.org/pg/commitdiff/40dae7ec537c5619fc93ad602c62f37be786d161
  • Fix misc typos in comments. http://git.postgresql.org/pg/commitdiff/1d3b258cbe4aedfb49c92c28b9cbd7c18d277e04
  • Fix compilation of pg_xlogdump, now that rm_safe_restartpoint is no more. Oops. Pointed out by Andres Freund. http://git.postgresql.org/pg/commitdiff/033dc1c92cf018d396e983d425b821dda420cfff
  • Remove rm_safe_restartpoint machinery. It is no longer used, none of the resource managers have multi-record actions that would make it unsafe to perform a restartpoint. Also don't allow rm_cleanup to write WAL records, it's also no longer required. Move the call to rm_cleanup routines to make it more symmetric with rm_startup. http://git.postgresql.org/pg/commitdiff/59a5ab3f426e74e3f901dc2cf533726bcea08ed2
  • Replace the XLogInsert slots with regular LWLocks. The special feature the XLogInsert slots had over regular LWLocks is the insertingAt value that was updated atomically with releasing backends waiting on it. Add new functions to the LWLock API to do that, and replace the slots with LWLocks. This reduces the amount of duplicated code. (There's still some duplication, but at least it's all in lwlock.c now.) Reviewed by Andres Freund. http://git.postgresql.org/pg/commitdiff/68a2e52bbaf98f136a96b3a0d734ca52ca440a95
  • Fix build with LWLOCK_STATS or dtrace. Also fix the name of the dtrace probe for LWLockAcquireOrWait(). The function was renamed from LWLockWaitUntilFree to LWLockAqcuireOrWait, but the dtrace probe was neglected. Pointed out by Andres Freund and the buildfarm. http://git.postgresql.org/pg/commitdiff/dea6ed2c980286e89caf4166ad329f506abbff29
  • Fix thinkos in GinLogicValue enum. It was incorrectly declared as global variable, not an enum type, and the comments for GIN_FALSE and GIN_TRUE were backwards. http://git.postgresql.org/pg/commitdiff/4c0e97c2d58f1cec9fc24237342962811de3cfee

Tom Lane a poussé :

  • During index build, check and elog (not just Assert) for broken HOT chain. The recently-fixed bug in WAL replay could result in not finding a parent tuple for a heap-only tuple. The existing code would either Assert or generate an invalid index entry, neither of which is desirable. Throw a regular error instead. http://git.postgresql.org/pg/commitdiff/d70cf811f7dd26c07dbb78df4a51b667e7a3489b
  • Release notes for 9.3.4, 9.2.8, 9.1.13, 9.0.17, 8.4.21. http://git.postgresql.org/pg/commitdiff/551fb5ac742eb7dbf92aa80743aa5a52b8a0189f
  • Fix pg_dumpall option parsing: -i doesn't take an argument. This used to work properly, but got fat-fingered in commit 3dee636e0404885d07885d41c0d70e50c784f324. Per bug #9620 from Nicolas Payart. http://git.postgresql.org/pg/commitdiff/19f2d6cdae2bfa97c2ce8a7f5ac453a91f40704a
  • Fix relcache reference leak in refresh_by_match_merge(). One path through the loop over indexes forgot to do index_close(). Rather than adding a fourth call, restructure slightly so that there's only one. In passing, get rid of an unnecessary syscache lookup: the pg_index struct for the index is already available from its relcache entry. Per report from YAMAMOTO Takashi, though this is a bit different from his suggested patch. This is new code in HEAD, so no need for back-patch. http://git.postgresql.org/pg/commitdiff/f7271c44278352516ec66b2de311952ce330b6d5
  • Fix some remaining int64 vestiges in contrib/test_shm_mq. Andres Freund and Tom Lane http://git.postgresql.org/pg/commitdiff/b6ec7c92ac7ab6223b3c45dc554efffd1953758f
  • Fix memory leak during regular expression execution. For a regex containing backrefs, pg_regexec() might fail to free all the sub-DFAs that were created during execution, resulting in a permanent (session lifespan) memory leak. Problem was introduced by me in commit 587359479acbbdc95c8e37da40707e37097423f5. Per report from Sandro Santilli; diagnosis by Greg Stark. http://git.postgresql.org/pg/commitdiff/ea8c7e9054abf23fa3de2f8e4414f60ac8a8b620
  • Again fix initialization of auto-tuned effective_cache_size. The previous method was overly complex and underly correct; in particular, by assigning the default value with PGC_S_OVERRIDE, it prevented later attempts to change the setting in postgresql.conf, as noted by Jeff Janes. We should just assign the default value with source PGC_S_DYNAMIC_DEFAULT, which will have the desired priority relative to the boot_val as well as user-set values. There is still a gap in this method: if there's an explicit assignment of effective_cache_size = -1 in the postgresql.conf file, and that assignment appears before shared_buffers is assigned, the code will substitute 4 times the bootstrap default for shared_buffers, and that value will then persist (since it will have source PGC_S_FILE). I don't see any very nice way to avoid that though, and it's not a case to be expected in practice. The existing comments in guc-file.l look forward to a redesign of the DYNAMIC_DEFAULT mechanism; if that ever happens, we should consider this case as one of the things we'd like to improve. http://git.postgresql.org/pg/commitdiff/af930e606a3217db3909029c6c3f8d003ba70920

Robert Haas a poussé :

Alvaro Herrera a poussé :

  • Setup error context callback for transaction lock waits With this in place, a session blocking behind another one because of tuple locks will get a context line mentioning the relation name, tuple TID, and operation being done on tuple. For example: LOG: process 11367 still waiting for ShareLock on transaction 717 after 1000.108 ms DETAIL: Process holding the lock: 11366. Wait queue: 11367. CONTEXT: while updating tuple (0,2) in relation "foo" STATEMENT: UPDATE foo SET value = 3; Most usefully, the new line is displayed by log entries due to log_lock_waits, although of course it will be printed by any other log message as well. Author: Christian Kruse, some tweaks by Álvaro Herrera Reviewed-by: Amit Kapila, Andres Freund, Tom Lane, Robert Haas http://git.postgresql.org/pg/commitdiff/f88d4cfc9d417dac2ee41a8f5e593898e56fd2bd

Bruce Momjian a poussé :

Noah Misch a poussé :

  • Address ccvalid/ccnoinherit in TupleDesc support functions. equalTupleDescs() neglected both of these ConstrCheck fields, and CreateTupleDescCopyConstr() neglected ccnoinherit. At this time, the only known behavior defect resulting from these omissions is constraint exclusion disregarding a CHECK constraint validated by an ALTER TABLE VALIDATE CONSTRAINT statement issued earlier in the same transaction. Back-patch to 9.2, where these fields were introduced. http://git.postgresql.org/pg/commitdiff/c31305de5f5a4880b0ba2f5983025ef0210a3b2a
  • Offer triggers on foreign tables. This covers all the SQL-standard trigger types supported for regular tables; it does not cover constraint triggers. The approach for acquiring the old row mirrors that for view INSTEAD OF triggers. For AFTER ROW triggers, we spool the foreign tuples to a tuplestore. This changes the FDW API contract; when deciding which columns to populate in the slot returned from data modification callbacks, writable FDWs will need to check for AFTER ROW triggers in addition to checking for a RETURNING clause. In support of the feature addition, refactor the TriggerFlags bits and the assembly of old tuples in ModifyTable. Ronan Dunklau, reviewed by KaiGai Kohei; some additional hacking by me. http://git.postgresql.org/pg/commitdiff/7cbe57c34dec4860243e6d0f81738cfbb6e5d069
  • Improve comments about AfterTriggerBeginQuery() query level usage. http://git.postgresql.org/pg/commitdiff/6115480c543c0141011a99db78987ad13540be59
  • Don't test xmin/xmax columns of a postgres_fdw foreign table. Their values are unspecified and system-dependent. Per buildfarm member kouprey. http://git.postgresql.org/pg/commitdiff/b2b2491b06074e68fc7c96148cb0fdf0c8eb0469

Andrew Dunstan a poussé :

  • Introduce jsonb, a structured format for storing json. The new format accepts exactly the same data as the json type. However, it is stored in a format that does not require reparsing the orgiginal text in order to process it, making it much more suitable for indexing and other operations. Insignificant whitespace is discarded, and the order of object keys is not preserved. Neither are duplicate object keys kept - the later value for a given key is the only one stored. The new type has all the functions and operators that the json type has, with the exception of the json generation functions (to_json, json_agg etc.) and with identical semantics. In addition, there are operator classes for hash and btree indexing, and two classes for GIN indexing, that have no equivalent in the json type. This feature grew out of previous work by Oleg Bartunov and Teodor Sigaev, which was intended to provide similar facilities to a nested hstore type, but which in the end proved to have some significant compatibility issues. Authors: Oleg Bartunov, Teodor Sigaev, Peter Geoghegan and Andrew Dunstan. Review: Andres Freund http://git.postgresql.org/pg/commitdiff/d9134d0a355cfa447adc80db4505d5931084278a
  • Fix mis-spelling in jsonb docs. Per Thom Brown. http://git.postgresql.org/pg/commitdiff/ca07cd59b24e00e428ed26716754244cec7f56b7
  • Do jsonb regression test input in the conventional way. This should make the buildfarm happier. http://git.postgresql.org/pg/commitdiff/ab22b149c60a10b842e3ec7fe3eb3b0b66c6611a

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Mitsumasa KONDO sent in another revision of a patch to allow using a Gaussian distribution in pgbench.
  • Jürgen Strobel sent in a patch to add a command line option --no-table-lock to pg_dump.
  • Fabrízio de Royes Mello sent in another revision of a patch to fix some wrong behavior in ALTER ... RESET.
  • Petr (PJMODOS) Jelinek sent in two more revisions of a patch to add plpgsql.warn_shadow.
  • Vaishnavi Prabakaran sent in another revision of a patch to add a catalog view to pg_hba.conf.
  • Heikki Linnakangas sent in a patch to change the way locks are acquired on B-trees.
  • Bruce Momjian sent in two revisions of a patch to fix a bug in pg_archivecleanup.
  • Jing Wang sent in another revision of a patch to issue a log message to suggest VACUUM FULL if a table is nearly empty.
  • Gurjeet Singh sent in a patch to send transaction commit/rollback stats to the stats collector unconditionally.
  • Kyotaro HORIGUCHI sent in another revision of a patch to fix an infelicity in some situations in archive recovery.
  • Petr (PJMODOS) Jelinek sent in two more revisions of a patch to add plpgsql.extra_warnings and plpgsql.extra_errors.
  • Fujii Masao sent in a patch to fix an issue where effective_cache_size cannot be changed by a reload (HUP) of the backend.
  • Vik Fearing sent in a patch to fix some typos in the patch to reduce lock strengths needed for ALTER TABLE.
  • Etsuro Fujita sent in another revision of a patch to allow foreign tables in table inheritance hierarchies.
  • Dilip Kumar sent in another revision of a patch to add parallelism to the vacuumdb program.
  • MauMau sent in another revision of a patch to fix an issue where PostgreSQL fails to start on Windows if it crashes after tablespace creation.
  • Andres Freund sent in a PoC patch not to require a NBuffer-sized PrivateRefCount array of local buffer pins.
  • Emanuel Calvo sent in a patch to clarify the documentation of what events rewrite RULEs can apply to.
  • Bruce Momjian sent in another revision of a patch to fix some psql output for the Replica type displayed.
  • Noah Misch sent in two more revisions of a patch to warn about some escalation attacks possible during "make check".

par N Bougain le vendredi 28 mars 2014 à 00h15

mardi 11 mars 2014

Rodolphe Quiédeville

Debian, PG9.3, Osmosis et Nominatim

Même en se basant sur des références en terme de stabilité il peut arriver que certains combos soient fatals à votre production. C'est ce qui m'est arrivé récemment pour un serveur Nominatim installé pourtant sur une Debian Wheezy, osmosis est utilisé pour la mise à jour continue de la base Nominatim et m'a fait des misères que je m'en vais vous conter.

En parallèle de la version de PostgreSQL 9.1 standard Debian j'ai installé sur la machine une 9.3 en provenance du dépôt Debian de la communauté PG (le support dfe JSON dans PG 9.3 c'est awesome), jusque là tout va bien. Seulement à l'upgrade suivant le paquet libpostgis-java est passé en version 2.1.1-5.pgdg70+1 (celle-ci étant disponible sur le dépôt PG) ; malheureusement cette version est incompatible avec osmosis packagé chez Debian qui nécessite la version 1.5.3 de cette librairie, et là c'est le drâme !

Donc si au lancement d'osmosis vous rencontrez l'erreur :

java.io.FileNotFoundException: /usr/share/java/postgis.jar

Alors que le sus-dit fichier est bien présent, et que vous commencez à dire du mal des backtraces java, il existe un solution simple et efficace, downgrader la version de libpostgis-java en quelques commandes :

dpkg --remove osmosis
apt-get install libpostgis-java=1.5.3-2 osmosis

Sans oublier un pinning pour éviter la future mise à jour du paquet.

par Rodolphe Quiédeville le mardi 11 mars 2014 à 08h07

dimanche 2 mars 2014

Guillaume Lelarge

Deux news... (GLMF + traduction)

Ça fait un bon moment que je n'ai pas publié un billet sur ce blog... ça fait peur :)

Bref, deux nouvelles intéressantes.

J'ai mis à jour les manuels français suite aux dernières versions mineures. Ça n'a pas été spécialement long de le faire. Par contre, il n'a pas été simple de trouver le temps pour le faire. Mais bon, c'est fait, les manuels sont à jour.

Il y avait aussi longtemps que je n'avais pas écrit un article pour le GNU/Linux Magazine France. J'ai enfin repris, avec un article sur les nouveautés de la version 9.3. Il est paru sur le GLMF 169. Je pense qu'il y aura d'autres articles, suivant le temps à ma disposition et la motivation que j'ai. Pour l'instant, j'essaie d'écrire sur le planificateur de requêtes, sujet que j'étudie depuis plus d'un an maintenant. Cela étant dit, si vous avez des idées de sujets, je suis preneur :)

par Guillaume Lelarge le dimanche 2 mars 2014 à 21h54

dimanche 22 septembre 2013

Guillaume Lelarge

Manuels de PostgreSQL en PDF

J'ai fini par m'y remettre. Depuis que je suis passé d'Ubuntu à Fedora, je ne pouvais plus générer le manuel de PostgreSQL au format PDF. Cet après-midi, n'arrivant pas à me mettre à autre chose, je me suis collé à ça.

Pour réussir à générer, j'ai récupéré la dernière version de docbook-xsl (1.78.1), puis j'ai modifié le fichier /opt/docbook-xsl/profiling/profile-mode.xsl pour mettre en commentaire la ligne 214. Ensuite, j'ai modifié le fichier stylesheets/pg-profile.xsl des sources de la documentation pour remplacer l'appel à /opt/docbook-xsl/profiling/profile-mode-pdf.xsl par un appel à /opt/docbook-xsl/profiling/profile-mode.xsl. Enfin, j'ai exporté deux variables :

export PATH=/home/guillaume/bin/fop-0.20.5:$PATH
export JAVA_HOME=/usr

Et, pas de soucis pour générer le PDF des versions 8.4 et 9.0. La 9.1 continue à me donner du fil à retordre mais rien de bien méchant. C'est simplement (très) long car il faut tester la génération, parfois plusieurs fois, pour trouver et comprendre chaque problème rencontré.

Mais bon, ça va venir. Ce n'est plus qu'une question de jours...

par Guillaume Lelarge le dimanche 22 septembre 2013 à 21h57

mardi 2 juillet 2013

Guillaume Lelarge

Plus que 20 jours pour proposer une conférence à pgconf.eu 2013

L'appel à conférencier est disponible depuis un moment. Du coup, il ne reste plus que 20 jours pour proposer une conférence à pgconf.eu 2013. Cela étant dit, ne désespérez pas, il vous reste un peu de temps :)

Ne pensez pas que vous n'avez rien d'intéressant à raconter. Votre expérience, vos compétences, vos outils sont autant de sujets passionnants de conférences.

Bref, n'hésitez pas à proposer une conférence. Et de toute façon, venez participer à cet événement. Les conférences, les tutoriels, les rencontres avec les contributeurs sont des raisons amplement suffisantes pour venir à Dublin.

par Guillaume Lelarge le mardi 2 juillet 2013 à 21h08

mercredi 26 juin 2013

Sébastien Lardière

Suivi de l'activité dans PostgreSQL 9.2

Jumelles

La vue pg_stat_activity a évolué dans la version 9.2 de PostgreSQL, ajoutant des informations essentielles, et modifiant des éléments déjà existant. Afin de l'exploiter au mieux, j'ai ajouté une vue dans certaines instances de production, simplifiant son usage.

Une première différence notable, qui peut-être déroutante lorsqu'on ne la connaît pas, est la séparation dans l'ancien attribut query_string en deux attributs : state et query ; auparavant, l'état d'une session se lisait en fonction de la valeur de query_string, et si la valeur était une requête SQL, c'est que la session était active. Désormais,... Lire Suivi de l'activité dans PostgreSQL 9.2

par Sébastien Lardière le mercredi 26 juin 2013 à 12h10

lundi 24 juin 2013

Guillaume Lelarge

Petit compte-rendu sur le pgday.fr 2013

L'association PostgreSQL.fr a organisé un pgday à Nantes cette année. Il a eu lieu le 13 juin et a réuni plus d'une centaine de participants. Il s'agit à ma connaissance du plus gros événement PostgreSQL sur une journée en France à ce jour. Félicitations aux organisateurs. Sans compter que l'organisation a été sans faille : enregistrement simple, bon traiteur (sans que ce soit aussi génial que Ivanne et Sidonie, les traiteurs des PostgreSQL Sessions et du pgday.fr 2009), salle assez grande mais pas trop, une soirée sympa... vraiment bien.

Au niveau des conférences, il y avait aussi du très bon. Cédric Villemain a parlé de la gestion des ressources mémoires, Damien Clochard des nouveautés de la version 9.3, Philippe Beaudoin de simulation de charge, Hugo Mercier de PostGIS 2.0, Grégoire Hubert de POMM, Gilles Darold d'ora2pg, Dimitri Fontaine de grosses volumétries avec PostgreSQL, moi-même des plans d'exécution et de la commande EXPLAIN, et Vik Fearing de la clause LATERAL. Une journée donc très chargée et bourrée d'informations. Sans parler des discussions entre les conférences, qui sont souvent un moment particulier pour apprendre, découvrir et s'enthousiasmer.

Pour moi, la meilleure conférence était celle de Philippe Beaudoin. Il a une manière très agréable et assez unique de présenter une étude de cas. Il a donc montré comment il a cherché (et réussi) à simuler une charge au niveau de la base de données pour s'assurer que son projet était viable. Sans aller dans les détails, il a quand même indiqué les outils qu'il a utilisé, quelques requêtes et ses résultats. Ce n'était pas vraiment technique, pas seulement une étude de cas... ce qui fait que tout le monde a pu trouver quelque chose à récupérer de sa conférence. Très impressionnant. En tout cas, ça m'a donné des idées pour de nouveaux rapports d'activité dans pgbadger.

Autre conférence intéressante, celle de Grégoire Hubert sur POMM. En fait, c'est plutôt le conférencier qui était intéressant. Il sait très bien faire passer son message. Ça fait très showman mais c'est très efficace et ça a permis de tenir éveiller les participants lors du créneau particulièrement difficile qui suit le repas :)

Quant à ma propre conférence, elle s'est plutôt bien passée. J'ai du aller vite car il y avait beaucoup de contenu pour seulement 50 minutes. J'espère n'avoir perdu personne. Les retours sont bons dans l'ensemble et ça fait plaisir. Les slides sont disponibles sur le site du pgday.fr ainsi que sur le site dalibo.org.

Pour terminer sur ce compte-rendu, je dois avouer que j'ai été agréablement surpris et que j'espère pouvoir être présent l'année prochaine pour l'édition 2014.

Et pour les intéressés, mes photos des conférenciers et des organisateurs.

par Guillaume Lelarge le lundi 24 juin 2013 à 20h09

mercredi 5 juin 2013

Nicolas Thauvin

pitrery - Faciliter la restauration PITR de PostgreSQL

pitrery est un ensemble de scripts pour gérer plus facilement la sauvegarde à chaud et la restauration PITR dans PostgreSQL.

Il s'agit de simples scripts bash, qui n'imposent pas autant de dépendances qu'un pgbarman et se concentre uniquement sur le PITR, là où des outils tels que OmniPITR, PITRTools semblent vouloir gérer également la réplication. L'objectif de pitrery est de pouvoir facilement restaurer.

La version 1.3 apporte des nouveauté intéressantes :

  • Support de PostgreSQL 9.2, les changements de définition du catalogue en 9.2 sont pris en compte pour les tablespaces
  • Simplification du script d'archivage
  • Possibilité de configurer un utilisateur de connexion différent pour les accès SSH
  • Amélioration de l'affichage de la liste des sauvegardes
  • Ajout d'un résumé des informations essentielles pour la restauration
  • Possibilité de modifier l'emplacement de chaque tablespace à la restauration
  • Mode sans exécution de la restauration avec affichage des informations de restauration seulement
  • Beaucoup de corrections de bug

La documentation est à jour sur https://dalibo.github.io/pitrery/documentation.html

Le code est disponible sur https://github.com/dalibo/pitrery/

Les versions sont disponibles sur https://dl.dalibo.com/public/pitrery/

Les idées pour la prochaine version sont, en vrac :

  • Faire le base backup avec rsync, pour pallier la production de tarball énormes
  • Paralléliser le tar des tablespaces
  • Ajouter l'exécution de commandes pré/post backup
  • Améliorer l'algo de rétention des backups pour gérer age et nombre de backup en même temps

mercredi 5 juin 2013 à 08h38

lundi 20 mai 2013

Guillaume Lelarge

Traduction de la documentation de PostgreSQL 9.3

Contrairement à l'année dernière, le merge a été beaucoup plus rapide. En moins de trois jours, il était terminé. Il est donc possible de commencer la traduction.

Ceux qui veulent participer sont les bienvenus. La liste des fichiers et les instructions sont disponibles sur : https://github.com/gleu/pgdocs_fr/wiki/translation-9.3. Il est possible que j'ai besoin de donner le droit de modifier cette page, on verra bien. J'avoue que je ne connais pas bien le système wiki de github.

De mon côté, je vais commencer certainement dès demain.

Pour ceux qui ont besoin de cette documentation, elle est disponible sur http://docs.postgresql.fr/9.3/. Elle sera mise à jour au fur et à mesure de l'avancement de la traduction (donc, là, c'est juste la documentation de la 9.2 en français, dans laquelle se trouvent greffés les ajouts de la 9.3 en anglais).

par Guillaume Lelarge le lundi 20 mai 2013 à 17h01

lundi 13 mai 2013

ulhume

EXT3/4 et performances

Il y a quelques mois, je me suis retrouvé confronté à un postreSQL complètement poussif sur une machine pourtant performante et fraichement installée. Alors PostgreSQL fautif ? Pas du tout car le même problème s'est reproduit sur une autre machine, mais cette fois tournant avec MySQL. La source du problème : EXT4.

EDIT: Ajout du chapitre "et maintenant, EXT3 aussi..."

EXT4 et la barrière d'écriture

En fait le problème apparaît uniquement en écriture. À haut débit, celles-ci peuvent être jusqu'à 30% plus lentes qu'avec un disque formaté sous EXT3. Les répercutions se font donc sentir sur tout système amené à écrire à haute fréquence, bases de données en tête évidemment.

La raison tient en un système ajouté pour garantir l'intégrité des caches lors des écritures. EXT4 va en effet émettre un "write barrier" (barrière d'écriture) à chaque synchronisation des caches (fsync). De la sorte, si un crash complet du système survient (ou si quelqu'un se prend les pieds dans les prises du rack de serveurs), EX4 garanti que je journal est parfaitement à jour. Tout ceci est très bien pour un serveur critique, c'est un peu moins pertinent pour une machine utilisée comme serveur web ou une machine de développement.

La "solution" consiste donc à désactiver la levée des barrières, ce qui se fait à chaud par

$sudo mount /dev/sdaX -o remount,nobarrier
levée des barrières d'écriture sur EXT4

Et maintenant Ext3 aussi...

Après mise à jour de ma machine de développement qui utilise EXT3 sous Debian Wheezy, j'ai découvert au détours d'un mount l'apparition d'un barrier=1. Il semble que la fonctionnalité "barrier" d'EXT4 ait donc été backportée sous EXT3. Supposition confirmée par les calamiteuses performances de Wheezy/PostgreSQL 9.1/EXT4.

Pour "corriger" cela, même motif, même punition que pour EXT4, le nobarrier fonctionne strictement de la même manière et apporte la même "solution".

Conclusion

On est bien d'accord que la chose n'est pas à faire sur une machine où l'atomicité des écritures est critique. Mais dans tous les autres cas, c'est un gain de 30% en écriture, ce qui m'a un peu sauvé la vie lors d'une énorme migration d'un PHPBB de 12 ans d'age sous Drupal :).

par Yoran le lundi 13 mai 2013 à 10h19

mercredi 8 mai 2013

Guillaume Lelarge

Nouvelles versions mineures de la documentation

Désolé, encore une fois, j'ai mis du temps avant de me coller à la mise à jour de la traduction des manuels. C'est fait, c'est disponible, pour les version 8.4 à 9.2.

La version beta de la 9.3 devrait sortir demain. Je m'y colle dès demain soir. Ça risque d'être épique, comme à chaque mise à jour majeure :) Quoique, après avoir regardé le diff, ça ne semble pas si dur que ça. Sont principalement touchées la documentation sur les fonctions, la configuration, les catalogues (rien que de l'habituel jusqu'ici), ainsi que les triggers sur événement, les vues matérialisées, les Foreign Data Wrapper, pg_xlogdump, pg_isready et les Background Workers (les grosses nouveautés de cette version). N'empêche qu'il y a du boulot.

par Guillaume Lelarge le mercredi 8 mai 2013 à 14h57

mercredi 3 avril 2013

Christophe Chauvet

Utiliser le dépôt Debian/Ubuntu de PostgreSQL.org

Le projet PostgreSQL possède depuis peu son propre dépôt APT pour les différentes versions des serveurs encore maintenu et PgAdmin3, sur les versions de Debian et Ubuntu suivantes

  • Debian
    • Etch
    • Lenny
    • Squeeze
    • Wheezy
    • Sid
  • Ubuntu
    • Precise (12.04)

Si vous utilisiez déjà le dépôt squeeze backports par exemple, vous pouvez basculer facilement vers ce nouveau dépôt sans problème

Clé de signature des paquets

Avant d'installer une version de PostgreSQL, il faut ajouter la clé d'authentification des paquets dans notre trousseau de clé.

wget -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -

source.list

Il faut ensuite ajouter le dépôt au source.list, pour cela créer le fichier /etc/apt/sources.list.d/pgdg.list et ajouter les lignes suivantes (l'exemple ci-dessous est pour la version squeeze).

deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main

Remplacer Squeeze par le nom de votre distribution.

Préférences

Pour indiquer à votre distribution de prendre et mettre à jour votre ou vos PostgreSQL à partir de cette source, il faut rajouter une configuration dite de pinning

Créer le fichier /etc/apt/preferences.d/pgdg.pref et ajouter les lignes suivantes

Package: *
Pin: release o=apt.postgresql.org
Pin-Priority: 500

Initialisation

Une fois la configuration, il faut faire un update pour mettre a jour votre gestionnaire de paquet avec ce nouveau dépôt, et charger le trousseau de clé

apt-get update
apt-get install pgdg-keyring

Ensuite il en reste plus qu'a installer la version de PostgreSQL que vous souhaitez.

apt-get install postgresql-9.2 postgresql-client-9.2 postgresql-contrib-9.2 postgresql-plpython-9.2 postgresql-server-dev-9.2 libpq-dev

par Christophe Chauvet le mercredi 3 avril 2013 à 06h05

mardi 12 mars 2013

Damien Clochard

PG Day France 2013 : Encore quelques jours pour soumettre vos propositions

Cette année encore la communauté francophone de PostgreSQL se réunira pour le PG Day France qui se tiendra à Nantes le 13 juin 2013.

L'occasion parfaite pour présenter une étude de cas, un projet en cours de développement ou une fonctionnalité de PostgreSQL !

L'appel à orateurs se terminera dans quelques jours, d'ici là n'hésitez pas à proposer une intervention !

http://pgday.fr/pgday2013:appel_a_orateurs

par damien le mardi 12 mars 2013 à 22h08

samedi 9 mars 2013

Philippe Beaudoin

1 500 milliards de requêtes SQL !

Mon activité professionnelle chez Bull m'amène à travailler très régulièrement avec la CNAF (Caisse Nationale d'Allocations Familiales).
En 2008-2009, j'ai eu la joie de participer activement à la mise en place de PostgreSQL pour une application éminemment critique, celle qui supporte le cœur du métier des CAF : le calcul et le paiement des prestations sociales.
Cette semaine, j'ai pu compiler diverses statistiques techniques. Quelques règles de 3 plus tard, j'en suis arrivé à la conclusion que nous venions tout juste de franchir la barre symbolique des :
1 500 milliards de requêtes SQL exécutées !
et ceci sans que le SGBD ne soit jamais pris en défaut.
Quelqu'un doutait-il de la robustesse de PostgreSQL ?

par philippe beaudoin le samedi 9 mars 2013 à 16h26

lundi 25 février 2013

Thomas Reiss

Les bases de données relationnelles avec PHP

L'AFUP organise une soirée intitulée les bases de données relationnelles avec PHP. Je viendrai présenter PostgreSQL, son développement et sa communauté, ses principales fonctionnalités et quelques retours d'expérience.

Si vous ne connaissez pas PostgreSQL, c'est le moment de venir le découvrir à cette occasion, près de la Défense, à Paris.

Pour vous inscrire, ça se passe ici :

par Thomas Reiss le lundi 25 février 2013 à 19h01

mardi 8 janvier 2013

Dimitri Fontaine (2nd Quadrant)

PostgreSQL 2013

Bonne Année 2013 à tous !

Le changement de calendrier civil est un moment privilégié pour faire le bilan de l’année passée et quelques prévisions pour l’année à venir. La rétrospective 2012 de PostgreSQL présente un très bon bilan, avec la mise à disposition de la version 9.2 dont l’adoption est en pleine croissance. Ce décalage entre la date de sortie d’une version et son adoption à grande échelle fait que 2012 est l’année où la version PostgreSQL 9.1 a été fortement déployée en production.

En ce qui concerne les conférences, nos experts ont profité de 2012 pour faire un petit tour du monde de PostgreSQL : nous avons en effet eu l’opportunité de présenter nos travaux en Belgique, au Canada, aux États Unis, en République Tchèque et plusieurs fois en France, à Lyon. Nous avons parlé des nouvelles fonctionnalités dont nous participons au développement (les Extensions, les Triggers étendus), les architectures que nous avons mises en place pour répondre à des besoins de Haute-Disponibilité, de Répartition de Charge, mais surtout de Durabilité des Données ; sans oublier bien sûr les aspects de mesures et améliorations des performances. Les contenus que nous avons utilisés lors de ces conférences sont bien sûr disponibles en ligne!

Ce tour du monde nous a permis de constater que PostgreSQL est de mieux en mieux équipé pour répondre à vos problématiques de garanties de données et de continuité des services, en particulier avec les version 9.1 et 9.2. Le circuit des conférences de la communauté PostgreSQL est un lieu d’échange très riche, où nous avons plaisir à rencontrer les utilisateurs et à écouter leur besoin, à prendre en compte leur point de vue pour les prochaines versions de votre moteur de base données préféré.

La prochaine conférence PostgreSQL organisée par la communauté Européenne de PostgreSQL se tient en belgique, à Bruxelles, le vendredi 1er février, en ouverture du FOSDEM. Je vous encourage fortement à vous y inscrire et à nous rejoindre!

Qu’attendre donc de 2013, après une telle année 2012 pour PostgreSQL ?

Suite à la circulaire de notre Premier Ministre, je pense que nous allons assister à un mouvement continu de migrations vers PostgreSQL, cette tendance me semble loin d’être arrivée en bout de course. L’arrivée sur le marché de solutions dites NoSQL se transforme doucement en « Not Only SQL », et PostgreSQL est prêt à s’intégrer dans des environnements de production hétérogènes complexes, en particulier avec ses Foreign Data Wrappers.

La période où l’on écrit sa lettre au père noël est bien évidemment terminée, mais si je pouvais émettre un voeux pour 2013… il concernerait la version de PostgreSQL qui sortira en 2014 et que nous allons commencer à préparer dès mars ou avril prochain. J’aimerais que cette version inclue une nouvelle approche plus simple et plus efficace pour résoudre le problème des tables à grands nombres de ligne, où la solution actuelle consister à créer des partitions de manière explicite. N’hésitez pas à nous contacter si vous partagez ce problème !

par Dimitri Fontaine le mardi 8 janvier 2013 à 11h05

jeudi 3 janvier 2013

Damien Clochard

Refonte de pgBadger en HTML5 : premier aperçu

PgBadger est un outil puissant pour optimiser vos serveurs PostgreSQL. À partir des fichiers log de vos serveurs, pgBadger va analyser le trafic SQL et produire une profusion de statistiques (les plus longues requêtes, le ratio lecture/écriture, etc.) Tout ceci est très utile, mais la quantité de données générée est si importante que les rapports générés par pgbadger sont difficiles à lire et que les stats utiles sont souvent noyées dans le flot ... Pour vous en convaincre, jetez un coup d'oeil sur cet exemple de rapport pgBadger  !

comme je le disais dans mon article précédent , nous avons travaillé dur pour redéfinir l'expérience utilisateur de pgBadger et créer un nouveau modèle de rapport.

Voici donc le premier aperçu du nouveau prototype (pour l'instant il s'agit simplement de code HTML statique, nous espérons présenter une démo interactive dans les prochaines semaines).

Comme vous pouvez le voir, nous avons suivi quelques principes de base:

  • Divisez les grosses tables HTML en petites tables et utiliser des onglets pour naviguer de l'une à l'autre:

pgbadger_proto_1.png

  • Laissez quelques espaces vides pour aérer et faciliter la lecture:

pgbadger_proto_2.png

  • Masquer les tableaux derrière les graphes, lorsque c'est possible:

pgbadger_proto_3.png

  • Mettre les infos détaillées dans des fenêtres popup que l'on peut masquer/afficher:

pgbadger_proto_4.png

  • Mettre l'accent sur les «valeurs clés» en jouant sur la taille des polices:

pgbadger_proto_5.png

Tout cela n'a rien d'extraordinaire et ce n'est pas très original... Mais ce nouveau design sera sûrement plus lisible et plus efficace. Nous espérons que cela va attirer de nouveaux utilisateurs et que cela sera apprécié par les utilisateurs existants.

Si vous avez des commentaires ou des idées, n'hésitez pas à rejoindre à la liste de discussion du projet pgBadger . Nous sommes à l'écoute !

par damien le jeudi 3 janvier 2013 à 22h06