PostgreSQL La base de donnees la plus sophistiquee au monde.

La planète francophone de PostgreSQL

vendredi 17 octobre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 12 otobre 2014

PostgreSQL 9.4 Beta 3 publiée. TEST ! http://www.postgresql.org/about/news/1547/

L'appel à conférenciers pour la PGConf NYC 2015 court jusqu'au 17 décembre 2014. Les notifications seront envoyées le 10 janvier. l'événement aura lieu à New-York du 25 au 27 mars 2015 : http://nyc.pgconf.us/2015/

[ndt: meetup à Lyon le 19 novembre : http://www.meetup.com/PostgreSQL-User-Group-Lyon/events/214027472/]

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en octobre

PostgreSQL Local

  • La cinquième édition du PgDay argentin est programmée pour le 28 novembre 2014 à Santa Fe. L'appel international à conférenciers court jusqu'au 28 septembre : http://www.postgresql.org.ar/pgday2014
  • 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.
  • Le sixième PGDay cubain aura lieu les 13 et 14 octobre 2014 à la Havane : https://postgresql.uci.cu/?p=380
  • 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/

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

  • Rename pg_recvlogical's --create/--drop to --create-slot/--drop-slot. A future patch (9.5 only) adds slot management to pg_receivexlog. The verbs create/drop don't seem descriptive enough there. It seems better to rename pg_recvlogical's commands now, in beta, than live with the inconsistency forever. The old form (e.g. --drop) will still be accepted by virtue of most getopt_long() options accepting abbreviations for long commands. Backpatch to 9.4 where pg_recvlogical was introduced. Author: Michael Paquier and Andres Freund Discussion: CAB7nPqQtt79U6FmhwvgqJmNyWcVCbbV-nS72j_jyPEopERg9rg@mail.gmail.com http://git.postgresql.org/pg/commitdiff/c8b6cba84a986ae9154f748e44709a17d6723191
  • Add support for managing physical replication slots to pg_receivexlog. pg_receivexlog already has the capability to use a replication slot to reserve WAL on the upstream node. But the used slot currently has to be created via SQL. To allow using slots directly, without involving SQL, add --create-slot and --drop-slot actions, analogous to the logical slot manipulation support in pg_recvlogical. Author: Michael Paquier Discussion: CABUevEx+zrOHZOQg+dPapNPFRJdsk59b=TSVf30Z71GnFXhQaw@mail.gmail.com http://git.postgresql.org/pg/commitdiff/d9f38c7a555dd5a6b81100c6d1e4aa68342d8771

Stephen Frost a poussé :

  • Clean up Create/DropReplicationSlot query buffer. CreateReplicationSlot() and DropReplicationSlot() were not cleaning up the query buffer in some cases (mostly error conditions) which meant a small leak. Not generally an issue as the error case would result in an immediate exit, but not difficult to fix either and reduces the number of false positives from code analyzers. In passing, also add appropriate PQclear() calls to RunIdentifySystem(). Pointed out by Coverity. http://git.postgresql.org/pg/commitdiff/273b29dbe96b1584dd66615cf8dc83e7e6ae7386

Tom Lane a poussé :

  • Fix array overrun in ecpg's version of ParseDateTime(). The code wrote a value into the caller's field[] array before checking to see if there was room, which of course is backwards. Per report from Michael Paquier. I fixed the equivalent bug in the backend's version of this code way back in 630684d3a130bb93, but failed to think about ecpg's copy. Fortunately this doesn't look like it would be exploitable for anything worse than a core dump: an external attacker would have no control over the single word that gets written. http://git.postgresql.org/pg/commitdiff/55bfdd1cfd991af0864535bbca24a606e8be7e45
  • Fix bogus optimization in JSONB containment tests. When determining whether one JSONB object contains another, it's okay to make a quick exit if the first object has fewer pairs than the second: because we de-duplicate keys within objects, it is impossible that the first object has all the keys the second does. However, the code was applying this rule to JSONB arrays as well, where it does *not* hold because arrays can contain duplicate entries. The test was really in the wrong place anyway; we should do it within JsonbDeepContains, where it can be applied to nested objects not only top-level ones. Report and test cases by Alexander Korotkov; fix by Peter Geoghegan and Tom Lane. http://git.postgresql.org/pg/commitdiff/4a50de13127b7657f32f14dc17beb2e15a3a4777
  • Improve documentation about JSONB array containment behavior. Per gripe from Josh Berkus. http://git.postgresql.org/pg/commitdiff/25ad5a52847699527ddba6e86aa353075e578e9b

Robert Haas a poussé :

  • Fix typo in elog message. http://git.postgresql.org/pg/commitdiff/c421efd21330f2e5bed253b4a53d7ea5e084edf6
  • Extend shm_mq API with new functions shm_mq_sendv, shm_mq_set_handle. shm_mq_sendv sends a message to the queue assembled from multiple locations. This is expected to be used by forthcoming patches to allow frontend/backend protocol messages to be sent via shm_mq, but might be useful for other purposes as well. shm_mq_set_handle associates a BackgroundWorkerHandle with an already-existing shm_mq_handle. This solves a timing problem when creating a shm_mq to communicate with a newly-launched background worker: if you attach to the queue first, and the background worker fails to start, you might block forever trying to do I/O on the queue; but if you start the background worker first, but then die before attaching to the queue, the background worrker might block forever trying to do I/O on the queue. This lets you attach before starting the worker (so that the worker is protected) and then associate the BackgroundWorkerHandle later (so that you are also protected). Patch by me, reviewed by Stephen Frost. http://git.postgresql.org/pg/commitdiff/7bb0e97407c32cbf7245ef91fcc27d120c81e872

Álvaro Herrera a poussé :

  • Implement SKIP LOCKED for row-level locks This clause changes the behavior of SELECT locking clauses in the presence of locked rows: instead of causing a process to block waiting for the locks held by other processes (or raise an error, with NOWAIT), SKIP LOCKED makes the new reader skip over such rows. While this is not appropriate behavior for general purposes, there are some cases in which it is useful, such as queue-like tables. Catalog version bumped because this patch changes the representation of stored rules. Reviewed by Craig Ringer (based on a previous attempt at an implementation by Simon Riggs, who also provided input on the syntax used in the current patch), David Rowley, and Álvaro Herrera. Author: Thomas Munro http://git.postgresql.org/pg/commitdiff/df630b0dd5ea2de52972d456f5978a012436115e
  • Split builtins.h to a new header ruleutils.h. The new header contains many prototypes for functions in ruleutils.c that are not exposed to the SQL level. Reviewed by Andres Freund and Michael Paquier. http://git.postgresql.org/pg/commitdiff/7b1c2a0f2066672b24f6257ec9b8d78a1754f494

Fujii Masao a poussé :

Heikki Linnakangas a poussé :

  • Change the way encoding and locale checks are done in pg_upgrade. Lc_collate and lc_ctype have been per-database settings since server version 8.4, but pg_upgrade was still treating them as cluster-wide options. It fetched the values for the template0 databases in old and new cluster, and compared them. That's backwards; the encoding and locale of the template0 database doesn't matter, as template0 is guaranteed to contain only ASCII characters. But if there are any other databases that exist on both clusters (in particular template1 and postgres databases), their encodings and locales must be compatible. Also, make the locale comparison more lenient. If the locale names are not equal, try to canonicalize both of them by passing them to setlocale(). We used to do that only when upgrading from 9.1 or below, but it seems like a good idea even with newer versions. If we change the canonical form of a locale, this allows pg_upgrade to still work. I'm about to do just that to fix bug #11431, by mapping a locale name that contains non-ASCII characters to a pure-ASCII alias of the same locale. No backpatching, because earlier versions of pg_upgrade still support upgrading from 8.3 servers. That would be more complicated, so it doesn't seem worth it, given that we haven't received any complaints about this from users. http://git.postgresql.org/pg/commitdiff/33755e8edf149dabfc0ed9b697a84f70b0cca0de
  • Remove unnecessary initialization of local variables. Oops, forgot these in the previous commit. http://git.postgresql.org/pg/commitdiff/733be2a5cd124b28a15d5d09de9b227691e6c5a0

Bruce Momjian a poussé :

Peter Eisentraut a poussé :

Noah Misch a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Etsuro Fujita sent in a patch to improve the autoanalyze messages on inheritance trees.
  • Heikki Linnakangas sent in another revision of a patch to change the format and API of WALs.
  • Heikki Linnakangas sent in another revision of a patch to implement an SSL regression test suite.
  • Fabrízio de Royes Mello sent in another revision of a patch to implement CINE for indexes.
  • Ali Akbar sent in two more revisions of a patch to implement generate_series(numeric, numeric).
  • Ali Akbar sent in another revision of a patch to fix xpath() to return namespace definitions.
  • Dilip Kumar sent in another revision of a patch to allow parallel cores to be used by vacuumdb.
  • Álvaro Herrera sent in another revision of a patch to implement BRIN indexes.
  • Feike Steenbergen sent in a patch to add regression tests for autocommit-off mode for psql and fix some omissions, and another to backpatch it to 9.2+.
  • Peter Geoghegan sent in another revision of a patch to implement (INSERT|UPDATE) ... ON CONFLICT...
  • Heikki Linnakangas sent in a patch to implement page inspection functions for GIN indexes.
  • Tom Lane sent in a patch to help fix the mess that is timezone abbreviations.
  • Álvaro Herrera sent in three more revisions of a patch to refactor pg_dump to remove global variables.
  • Fujii Masao sent in another revision of a patch to add a pending_list_cleanup_size GUC and associated machinery.
  • Heikki Linnakangas sent in a patch to fix the CRC algorithm so it matches what the documents say it is.
  • Michael Paquier sent in three more revisions of a patch to ensure that .ready files not appear on replicas.
  • Robert Haas sent in two patches to defer some AtStart* allocations.
  • Kyotaro HORIGUCHI sent in another revision of a patch to fix a situation where it was impossible to escape from a blocked send().
  • Andres Freund sent in three more revisions of a patch to implement wait-free LW_SHARED acquisition.
  • Heikki Linnakangas sent in another revision of a patch to add a throttling latency limit to pgbench.
  • Fujii Masao sent in a patch to allow ALTER ROLE/DATABASE SET to set PGC_BACKEND and PGC_SU_BACKEND parameters.
  • Amit Kapila sent in another revision of a patch to scale buffer eviction.
  • Peter Geoghegan sent in a patch to remove an obsolete reference to _bt_tuplecompare() within tuplesort.c.
  • Kyotaro HORIGUCHI sent in a patch to arrange things so ALTER USER/ROLE now takes CURRENT_USER as user name, rewrite the sysnopsis of the documents for ALTER USER and ALTER ROLE so as to they have exactly same syntax, and modify the syntax of ALTER USER so as to be an alias of ALTER ROLE.
  • Craig Ringer sent in another revision of a patch to use a faster, higher precision timer API on Windows.
  • Michael Paquier sent in a patch to fix some docs around row-level locking.
  • Peter Geoghegan sent in a patch to see that B-Tree index builds use sortsupport.
  • Noah Misch sent in a patch to fix an error caused by the fact that Darwin's setlocale() can start a thread.
  • Fabrízio de Royes Mello sent in a patch to get rid the toast table for pg_seclabel.
  • Peter Geoghegan sent in a patch to make B-Tree and CLUSTER sortsupport use abbreviation and fix PG_CACHE_LINE_SIZE bit rot.
  • Bruce Momjian and Peter Eisentraut traded patches to deal with some psql output changes in 9.4-to-be.
  • Ali Akbar sent in two revisions of a patch to create array_agg() for arrays.
  • Michael Paquier sent in a patch to create a new header quote.h in include/utils that groups all the quote-related functions.
  • Tomas Vondra sent in a WIP patch to implement multivariate statistics.
  • Álvaro Herrera sent in another revision of a patch to add CREATE support to event triggers.
  • SAWADA Masahiko sent in a patch to implement REINDEX SCHEMA.

par N Bougain le vendredi 17 octobre 2014 à 21h58

mardi 14 octobre 2014

Actualités PostgreSQL.fr

Publication de PostgreSQL 9.4 bêta 3

Le « PostgreSQL Global Development Group » vient de publier la version 9.4 bêta 3, la nouvelle révision bêta de la prochaine version.

Cette bêta présente toutes les fonctionnalités qui seront disponibles dans la version 9.4, ainsi que les correctifs de la plupart des problèmes rencontrés pas les utilisateurs qui ont testé la bêta 2. Nous vous invitons à télécharger, tester, et signaler ce que vous trouverez.

Le changement principal dans la bêta 3 est la modification du type de format de données pour JSONB, afin de rendre les champs JSONB plus faciles à compacter et de réduire l'espace de stockage nécessaire. Ce changement n'est pas rétrocompatible. Les utilisateurs qui ont chargé des données dans les champs JSONB, en utilisant la 9.4 bêta 1 ou bêta 2, devront utiliser pg_dump et lancer une restauration pour passer en bêta 3.

Parmi les autres changements faits depuis la bêta 2, citons :

  • Changement des arguments de pg_recvlogical --create/--drop en --create-slot/--drop-slot
  • Éviter le délai excessif au démarrage du « background worker »
  • Suppression du paramètre de configuration num_xloginsert_locks, remplacé par #define
  • Correction de l'option --if-exists de pg_dump pour les « large objects »
  • Suppression de l'interdiction des clés d'objet JSON de taille nulle
  • Retour de NULL par json_object_agg s'il n'y a pas de ligne
  • Les instructions ALTER SYSTEM sont tracées comme des DDL
  • Correction de l'impossibilité pour la contribution auto_explain à afficher les informations temporelles par nœud
  • Plusieurs correctifs des vérifications TAP
  • Support de la commande ALTER SYSTEM RESET
  • Correctif de power_var_int() pour les exposants d'entiers long
  • Correctif pour vacuumdb --analyze-in-stages --all
  • Modification de la façon dont la latence est calculée avec l'option --rate de pgbench
  • Support de ALTER ... ALL IN pour les déclencheurs sur événement
  • Suppression de la restriction aux seuls superutilisateurs pour pg_is_xlog_replay_paused()
  • Correction de FOR UPDATE NOWAIT sur les chaînes de tuples actualisés
  • Correction de la gestion des Var pour les vues de barrière de sécurité
  • Correction des rafraichissements concurrents de niveau superutilisateur pour les vues matérialisées appartenant à autrui
  • Pas de suivi des DEALLOCATE dans pg_stat_statements
  • Correction du comportement aux limites des opérateurs d'extraction JSON/JSONB
  • Modification de la façon dont la cartographie des « tablespace » est effectuée par pg_basebackup
  • Refonte du MOVE ALL en ALTER ALL ... IN TABLESPACE
  • Correction du core dump de l'opérateur jsonb
  • Abandon des modifications du psql pour supporter le mode étendu à retour chariot
  • Ajout de l'option -S pour pg_receivexlog
  • Rejet des doublons de noms de colonnes dans les listes de colonnes référencées par les clés étrangères
  • Correction du crash du checkpointer
  • Multiples corrections mineures sur JSON et les fonctionnalités JSONB
  • Multiples corrections mineures sur le décodage logique
  • De multiples corrections de bugs concernant d'anciens problèmes sont à venir dans une prochaine version mineure
  • Également des améliorations et modifications dans la documentation

La bêta 3 inclut des changements sur pg_control et les catalogues système. De fait, les utilisateurs ayant testé la bêta 1 ou la bêta 2 devront les mettre à jour pour tester la bêta 3. Nous suggérons d'utiliser pg_upgrade pour cette mise à jour afin de le tester également.

Pour une liste complète des fonctionnalités de la version 9.4 bêta, veuillez consulter les notes de version http://www.postgresql.org/docs/devel/static/release-9-4.html.

Des descriptions et notes additionnelles des nouvelles fonctionnalités sont disponibles sur la page wiki des fonctionnalités 9.4 :
http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4.

Nous avons besoin de notre communauté pour nous aider à tester la prochaine version afin de garantir qu'elle a de bonnes performances et qu'elle est exempte de bogues.

Nous vous invitons à télécharger PostgreSQL 9.4 bêta 3 et à l'essayer avec vos projets et applications dès que possible, puis à faire vos retours et critiques aux développeurs PostgreSQL.

Les fonctionnalités et APIs de la bêta 3 ne changeront pas de manière substantielle avant la version finale, il est donc possible de créer des applications autour des nouvelles fonctionnalités en toute sécurité.

Plus d'informations sur la manière de tester et de signaler les problèmes http://www.postgresql.org/developer/beta.

Vous trouverez la bêta 3 de PostgreSQL 9.4, comprenant les binaires et les installeurs pour Windows, Linux et Mac, sur la page de téléchargement http://www.postgresql.org/download.

La documentation complète de la nouvelle version, disponible en ligne http://www.postgresql.org/docs/9.4/static/, est également installée avec postgreSQL.

par SAS le mardi 14 octobre 2014 à 19h27

jeudi 9 octobre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 5 octobre 2014

La PGCon 2015 aura lieu en juin, quelques semaines plus tard par rapport aux années précédentes. Le site web sera bientôt mis à jour. Entre temps, veuillez lire : https://plus.google.com/109158564828766727391/posts/5VrpSxoZDAa

Federico Campoli parlera de PostgreSQL et de Big Data lors du Linux Day italien de Ferrara, le 25 octobre 2014 : http://www.pgdba.co.uk/2014/10/postgresql-and-big-data-talk.html

Notez les dates : la seconde conférence PostgreSQLienne suisse aura lieu les 25 & 26 juin 2015 au HSR Rapperswil. Il y aura une journée dédiée aux tutoriels, et une journée pour les présentations "normales". Plus de détails sous peu.

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en octobre

PostgreSQL Local

  • La cinquième édition du PgDay argentin est programmée pour le 28 novembre 2014 à Santa Fe. L'appel international à conférenciers court jusqu'au 28 septembre : http://www.postgresql.org.ar/pgday2014
  • 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.
  • Le sixième PGDay cubain aura lieu les 13 et 14 octobre 2014 à la Havane : https://postgresql.uci.cu/?p=380
  • 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/

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

Stephen Frost a poussé :

  • Revert 95d737ff to add 'ignore_nulls'. Per discussion, revert the commit which added 'ignore_nulls' to row_to_json. This capability would be better added as an independent function rather than being bolted on to row_to_json. Additionally, the implementation didn't address complex JSON objects, and so was incomplete anyway. Pointed out by Tom and discussed with Andrew and Robert. http://git.postgresql.org/pg/commitdiff/c8a026e4f15e2063cd7fe6cb9ffe44f2a81398bd
  • Also revert e3ec0728, JSON regression tests. Managed to forget to update the other JSON regression test output, again. Revert the commit which fixed it before. Per buildfarm. http://git.postgresql.org/pg/commitdiff/08da8947f4b52ad41770f260a3962b032de77120
  • Correct stdin/stdout usage in COPY .. PROGRAM The COPY documentation incorrectly stated, for the PROGRAM case, that we read from stdin and wrote to stdout. Fix that, and improve consistency by referring to the 'PostgreSQL' user instead of the 'postgres' user, as is done in the rest of the COPY documentation. Pointed out by Peter van Dijk. Back-patch to 9.3 where COPY .. PROGRAM was introduced. http://git.postgresql.org/pg/commitdiff/8492d86342915cb466eeb2d896d9b49887f59350
  • Fix CreatePolicy, pg_dump -v; psql and doc updates. Peter Geoghegan pointed out that valgrind was, rightfully, complaining about CreatePolicy() ending up copying beyond the end of the parsed policy name. Name is a fixed-size type and we need to use namein (through DirectFunctionCall1()) to flush out the entire array before we pass it down to heap_form_tuple. Michael Paquier pointed out that pg_dump --verbose was missing a newline and Fabrízio de Royes Mello further pointed out that the schema was also missing from the messages, so fix those also. Also, based on an off-list comment from Kevin, rework the psql \d output to facilitate copy/pasting into a new CREATE or ALTER POLICY command. Lastly, improve the pg_policies view and update the documentation for it, along with a few other minor doc corrections based on an off-list discussion with Adam Brightwell. http://git.postgresql.org/pg/commitdiff/78d72563ef141ddc507ddd5ae77db613a309041a

Tom Lane a poussé :

  • Change JSONB's on-disk format for improved performance. The original design used an array of offsets into the variable-length portion of a JSONB container. However, such an array is basically uncompressible by simple compression techniques such as TOAST's LZ compressor. That's bad enough, but because the offset array is at the front, it tended to trigger the give-up-after-1KB heuristic in the TOAST code, so that the entire JSONB object was stored uncompressed; which was the root cause of bug #11109 from Larry White. To fix without losing the ability to extract a random array element in O(1) time, change this scheme so that most of the JEntry array elements hold lengths rather than offsets. With data that's compressible at all, there tend to be fewer distinct element lengths, so that there is scope for compression of the JEntry array. Every N'th entry is still an offset. To determine the length or offset of any specific element, we might have to examine up to N preceding JEntrys, but that's still O(1) so far as the total container size is concerned. Testing shows that this cost is negligible compared to other costs of accessing a JSONB field, and that the method does largely fix the incompressible-data problem. While at it, rearrange the order of elements in a JSONB object so that it's "all the keys, then all the values" not alternating keys and values. This doesn't really make much difference right at the moment, but it will allow providing a fast path for extracting individual object fields from large JSONB values stored EXTERNAL (ie, uncompressed), analogously to the existing optimization for substring extraction from large EXTERNAL text values. Bump catversion to denote the incompatibility in on-disk format. We will need to fix pg_upgrade to disallow upgrading jsonb data stored with 9.4 betas 1 and 2. Heikki Linnakangas and Tom Lane http://git.postgresql.org/pg/commitdiff/def4c28cf9147472ba4cfc5b68a098d7a29fb0fb
  • Fix some more problems with nested append relations. As of commit a87c72915 (which later got backpatched as far as 9.1), we're explicitly supporting the notion that append relations can be nested; this can occur when UNION ALL constructs are nested, or when a UNION ALL contains a table with inheritance children. Bug #11457 from Nelson Page, as well as an earlier report from Elvis Pranskevichus, showed that there were still nasty bugs associated with such cases: in particular the EquivalenceClass mechanism could try to generate "join" clauses connecting an appendrel child to some grandparent appendrel, which would result in assertion failures or bogus plans. Upon investigation I concluded that all current callers of find_childrel_appendrelinfo() need to be fixed to explicitly consider multiple levels of parent appendrels. The most complex fix was in processing of "broken" EquivalenceClasses, which are ECs for which we have been unable to generate all the derived equality clauses we would like to because of missing cross-type equality operators in the underlying btree operator family. That code path is more or less entirely untested by the regression tests to date, because no standard opfamilies have such holes in them. So I wrote a new regression test script to try to exercise it a bit, which turned out to be quite a worthwhile activity as it exposed existing bugs in all supported branches. The present patch is essentially the same as far back as 9.2, which is where parameterized paths were introduced. In 9.0 and 9.1, we only need to back-patch a small fragment of commit 5b7b5518d, which fixes failure to propagate out the original WHERE clauses when a broken Emmanuel Cecchet contains constant members. (The regression test case results show that these older branches are noticeably stupider than 9.2+ in terms of the quality of the plans generated; but we don't really care about plan quality in such cases, only that the plan not be outright wrong. A more invasive fix in the older branches would not be a good idea anyway from a plan-stability standpoint.) http://git.postgresql.org/pg/commitdiff/5a6c168c7859e150c61e30b7519471045bab065e
  • Fix bogus logic for zic -P option. The quick hack I added to zic to dump out currently-in-use timezone abbreviations turns out to have a nasty bug: within each zone, it was printing the last "struct ttinfo" to be *defined*, not necessarily the last one in use. This was mainly a problem in zones that had changed the meaning of their zone abbreviation (to another GMT offset value) and later changed it back. As a result of this error, we'd missed out updating the tznames/ files for some jurisdictions that have changed their zone abbreviations since the tznames/ files were originally created. I'll address the missing data updates in a separate commit. http://git.postgresql.org/pg/commitdiff/596857043023738099d6d16f8edbe6b7353876c0
  • Update time zone abbreviations lists. This updates known_abbrevs.txt to be what it should have been already, were my -P patch not broken; and updates some tznames/ entries that missed getting any love in previous timezone data updates because zic failed to flag the change of abbreviation. The non-cosmetic updates: * Remove references to "ADT" as "Arabia Daylight Time", an * abbreviation that's been out of use since 2007; therefore, claiming there is a conflict with "Atlantic Daylight Time" doesn't seem especially helpful. (We have left obsolete entries in the files when they didn't conflict with anything, but that seems like a different situation.) * Fix entirely incorrect GMT offsets for CKT (Cook Islands), FJT, FJST (Fiji); we didn't even have them on the proper side of the date line. (Seems to have been aboriginal errors in our tznames data; there's no evidence anything actually changed recently.) * FKST (Falkland Islands Summer Time) is now used all year round, so don't mark it as a DST abbreviation. * Update SAKT (Sakhalin) to mean GMT+11 not GMT+10. In cosmetic changes, I fixed a bunch of wrong (or at least obsolete) claims about abbreviations not being present in the zic files, and tried to be consistent about how obsolete abbreviations are labeled. Note the underlying timezone/data files are still at release 2014e; this is just trying to get us in sync with what those files actually say before we go to the next update. http://git.postgresql.org/pg/commitdiff/4f499eee9d4b30e761b04fa28406578c10b4badf
  • Update time zone data files to tzdata release 2014h. Most zones in the Russian Federation are subtracting one or two hours as of 2014-10-26. Update the meanings of the abbreviations IRKT, KRAT, MAGT, MSK, NOVT, OMST, SAKT, VLAT, YAKT, YEKT to match. The IANA timezone database has adopted abbreviations of the form AxST/AxDT for all Australian time zones, reflecting what they believe to be current majority practice Down Under. These names do not conflict with usage elsewhere (other than ACST for Acre Summer Time, which has been in disuse since 1994). Accordingly, adopt these names into our "Default" timezone abbreviation set. The "Australia" abbreviation set now contains only CST,EAST,EST,SAST,SAT,WST, all of which are thought to be mostly historical usage. Note that SAST has also been changed to be South Africa Standard Time in the "Default" abbreviation set. Add zone abbreviations SRET (Asia/Srednekolymsk) and XJT (Asia/Urumqi), and use WSST/WSDT for western Samoa. Also a DST law change in the Turks & Caicos Islands (America/Grand_Turk), and numerous corrections for historical time zone data. http://git.postgresql.org/pg/commitdiff/513d06ded1b44206903b87dabefee5a97243ba67
  • Update 9.4 release notes for commits through today. Add entries for recent changes, including noting the JSONB format change and the recent timezone data changes. We should remove those two items before 9.4 final: the JSONB change will be of no interest in the long run, and it's not normally our habit to mention timezone updates in major-release notes. But it seems important to document them temporarily for beta testers. I failed to resist the temptation to wordsmith a couple of existing entries, too. http://git.postgresql.org/pg/commitdiff/f706f2c1a06dfd3fe69194a93d417ca4be8951b1

Andres Freund a poussé :

  • doc fix for pg_recvlogical: --create doesn't immediately exit. Author: Michael Paquier http://git.postgresql.org/pg/commitdiff/445d262852f7aa83f9973a13bd56320d3a63842c
  • Rename CACHE_LINE_SIZE to PG_CACHE_LINE_SIZE. As noted in http://bugs.debian.org/763098 there is a conflict between postgres' definition of CACHE_LINE_SIZE and the definition by various *bsd platforms. It's debatable who has the right to define such a name, but postgres' use was only introduced in 375d8526f290 (9.4), so it seems like a good idea to rename it. Discussion: 20140930195756.GC27407@msg.df7cb.de Per complaint of Christoph Berg in the above email, although he's not the original bug reporter. Backpatch to 9.4 where the define was introduced. http://git.postgresql.org/pg/commitdiff/ef8863844bb0b0dab7b92c5f278302a42b4bf05a
  • Improve documentation about binary/textual output mode for output plugins. Also improve related error message as it contributed to the confusion. Discussion: CAB7nPqQrqFzjqCjxu4GZzTrD9kpj6HMn9G5aOOMwt1WZ8NfqeA@mail.gmail.com, CAB7nPqQXc_+g95zWnqaa=mVQ4d3BVRs6T41frcEYi2ocUrR3+A@mail.gmail.com Per discussion between Michael Paquier, Robert Haas and Andres Freund. Backpatch to 9.4 where logical decoding was introduced. http://git.postgresql.org/pg/commitdiff/0ef3c29a4b43e72d93cff65a17a9ccccff87618d
  • Block signals while computing the sleep time in postmaster's main loop. DetermineSleepTime() was previously called without blocked signals. That's not good, because it allows signal handlers to interrupt its workings. DetermineSleepTime() was added in 9.3 with the addition of background workers (da07a1e856511), where it only read from BackgroundWorkerList. Since 9.4, where dynamic background workers were added (7f7485a0cde), the list is also manipulated in DetermineSleepTime(). That's bad because the list now can be persistently corrupted if modified by both a signal handler and DetermineSleepTime(). This was discovered during the investigation of hangs on buildfarm member anole. It's unclear whether this bug is the source of these hangs or not, but it's worth fixing either way. I have confirmed that it can cause crashes. It luckily looks like this only can cause problems when bgworkers are actively used. Discussion: 20140929193733.GB14400@awork2.anarazel.de Backpatch to 9.3 where background workers were introduced. http://git.postgresql.org/pg/commitdiff/a39e78b710eb588e102aedd2828611d7bc74714b
  • pg_recvlogical.c code review. Several comments still referred to 'initiating', 'freeing', 'stopping' replication slots. These were terms used during different phases of the development of logical decoding, but are no long accurate. Also rename StreamLog() to StreamLogicalLog() and add 'void' to the prototype. Author: Michael Paquier, with some editing by me. Backpatch to 9.4 where pg_recvlogical was introduced. http://git.postgresql.org/pg/commitdiff/fdf81c9a6cf94921084e52c8d2ff903bae362620
  • Install all headers for the new atomics API. Previously, by mistake, only atomics.h was installed. Kohei KaiGai http://git.postgresql.org/pg/commitdiff/952872698d9443fdf9b808a1376017f00c91065a
  • Refactor replication connection code of various pg_basebackup utilities. Move some more code to manage replication connection command to streamutil.c. A later patch will introduce replication slot via pg_receivexlog and this avoid duplicating relevant code between pg_receivexlog and pg_recvlogical. Author: Michael Paquier, with some editing by me. http://git.postgresql.org/pg/commitdiff/0c013e08cfbebd35ec982c4df15d44b521094d52

Bruce Momjian a poussé :

Álvaro Herrera a poussé :

  • Fix pg_dump's --if-exists for large objects. This was born broken in 9067310cc5dd590e36c2c3219dbf3961d7c9f8cb. Per trouble report from Joachim Wieland. Pavel Stěhule and Álvaro Herrera http://git.postgresql.org/pg/commitdiff/fd02931a6c83bf3beb2d03f65700600787d569a3
  • Don't balance vacuum cost delay when per-table settings are in effect. When there are cost-delay-related storage options set for a table, trying to make that table participate in the autovacuum cost-limit balancing algorithm produces undesirable results: instead of using the configured values, the global values are always used, as illustrated by Mark Kirkwood in http://www.postgresql.org/message-id/52FACF15.8020507@catalyst.net.nz Since the mechanism is already complicated, just disable it for those cases rather than trying to make it cope. There are undesirable side-effects from this too, namely that the total I/O impact on the system will be higher whenever such tables are vacuumed. However, this is seen as less harmful than slowing down vacuum, because that would cause bloat to accumulate. Anyway, in the new system it is possible to tweak options to get the precise behavior one wants, whereas with the previous system one was simply hosed. This has been broken forever, so backpatch to all supported branches. This might affect systems where cost_limit and cost_delay have been set for individual tables. http://git.postgresql.org/pg/commitdiff/1021bd6a89bc756c2a72b29eeee1a7a5c25f64f5

Heikki Linnakangas a poussé :

Robert Haas a poussé :

  • Increase the number of buffer mapping partitions to 128. Testing by Amit Kapila, Andres Freund, and myself, with and without other patches that also aim to improve scalability, seems to indicate that this change is a significant win over the current value and over smaller values such as 64. It's not clear how high we can push this value before it starts to have negative side-effects elsewhere, but going this far looks OK. http://git.postgresql.org/pg/commitdiff/3acc10c997f916f6a741d0b4876126b7b08e3892
  • Still another typo fix for 0709b7ee72e4bc71ad07b7120acd117265ab51d0. Buildfarm member anole caught this one. http://git.postgresql.org/pg/commitdiff/9019264f2e2f87c879855449f57aeeb336ee3c88
  • Fix typos in comments. Etsuro Fujita http://git.postgresql.org/pg/commitdiff/017b2e9822cf6c28fc5024a5f9a6f0d3dde885a4
  • Eliminate one background-worker-related flag variable. Teach sigusr1_handler() to use the same test for whether a worker might need to be started as ServerLoop(). Aside from being perhaps a bit simpler, this prevents a potentially-unbounded delay when starting a background worker. On some platforms, select() doesn't return when interrupted by a signal, but is instead restarted, including a reset of the timeout to the originally-requested value. If signals arrive often enough, but no connection requests arrive, sigusr1_handler() will be executed repeatedly, but the body of ServerLoop() won't be reached. This change ensures that, even in that case, background workerw will eventually get launched. This is far from a perfect fix; really, we need select() to return control to ServerLoop() after an interrupt, either via the self-pipe trick or some other mechanism. But that's going to require more work and discussion, so let's do this for now to at least mitigate the damage. Per investigation of test_shm_mq failures on buildfarm member anole. http://git.postgresql.org/pg/commitdiff/d0410d66037c2f3f9bee45e0a2db9e47eeba2bb4

Peter Eisentraut a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Bogdan Pilch sent in a patch to allow trailing commas in SELECT statements.
  • Heikki Linnakangas sent in a patch to use a plain memcmp() to compare the Datums while building the red-black tree for GIN.
  • Peter Eisentraut sent in a patch to correct the documentation for effective_io_concurrency.
  • Peter Geoghegan sent in another revision of a patch to add a new infrastructure to sortsupport, and add a single client of this new infrastructure, the text sortsupport routine.
  • Herwin Weststrate sent in two revisions of a patch to allow format 0000-0000-0000 in the MAC parser.
  • Kaigai Kouhei sent in two more revisions of a patch to create a custom scan API.
  • Michael Paquier and Ali Akbar traded patches to add a generate_series(numeric, numeric) function.
  • Fabrízio de Royes Mello and Marti Raudsepp traded patches to implement CINE for indexes.
  • Michael Paquier sent in another revision of a patch to implement REINDEX CONCURRENTLY.
  • Adam Brightwell sent in three revisions of a patch to clean up superuser().
  • Ilya Kosmodemiansky sent in a patch to to trace LWLocks with the lowest possible performance impact.
  • Thomas Munro sent in two revisions of a patch to implement NEXT VALUE FOR <sequence>.
  • Kaigai Kouhei sent in a patch to insure that the new port/atomics/*.h files are included when installing.
  • Marti Raudsepp sent in a patch to allow semijoin/antijoin/hashed SubPlan optimization when an EXISTS subquery contains a LIMIT clause with a positive constant.
  • Fabrízio de Royes Mello sent in a patch to ensure that the CREATE SEQUENCE syntax correctly summarizes the options for CREATE SEQUENCE.
  • Kaigai Kouhei sent in a patch to make ResourceOwnerForgetBuffer() O(1) instead of O(N^2).
  • Michael Banck sent in a patch to allow logging to notice that a checkpoint is to be written on shutdown.
  • Heikki Linnakangas sent in another revision of a patch to create a pgbench throttling latency limit.
  • Shigeru HANADA sent in a WIP patch to add join push-down support for foreign tables.
  • Andrew Dunstan sent in a patch to implement json[b]_strip_nulls.
  • Marco Nenciarini sent in a patch to add an optional backup profile to pg_basebackup in service of the eventual feature, incremental backup.
  • Heikki Linnakangas sent in another revision of a patch to change the WAL format and API.

par N Bougain le jeudi 9 octobre 2014 à 22h55

jeudi 2 octobre 2014

Dimitri Fontaine

Meetup PostgreSQL à Paris

Mercredi 8 octobre se tiendra le prochain Meetup PostgreSQL à Paris dans les locaux de Mozilla Europe, dont la capacité est de 90 personnes ! Venez nombreux !

Le programme de cette édition est un peu particulier puisque nous avons reçu quatre propositions enthousiastes de présentations. Chacun aura donc 15 à 20 minutes (questions incluses) pour présenter son sujet. Voici le programme :

  • Aggrégation temporelle sous contrainte d'iops, par Jean-Gérard Pailloncy
  • Le partitionnement de tables démystifié, par Flavio Henrique Araque Gurgel
  • PoWA, par Thomas Reiss et Julien Rouhaud
  • PostgreSQL pour les développeurs, par Dimitri Fontaine

Nous aurons aussi le plaisir de partager un buffet sponsorisé cette fois par Novapost, un grand merci à eux.

À mercredi !

par dim@tapoueh.org (Dimitri Fontaine) le jeudi 2 octobre 2014 à 09h49

lundi 29 septembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 28 septembre 2014

[ndt: Meetup PUG parisien le 8 octobre : http://www.meetup.com/PostgreSQL-User-Group-Paris/]

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en septembre

PostgreSQL Local

  • La cinquième édition du PgDay argentin est programmée pour le 28 novembre 2014 à Santa Fe. L'appel international à conférenciers court jusqu'au 28 septembre : http://www.postgresql.org.ar/pgday2014
  • 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.
  • Le sixième PGDay cubain aura lieu les 13 et 14 octobre 2014 à la Havane : https://postgresql.uci.cu/?p=380
  • 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/

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

  • Improve code around the recently added rm_identify rmgr callback. There are four weaknesses in728f152e07f998d2cb4fe5f24ec8da2c3bda98f2: append_init() in heapdesc.c was ugly and required that rm_identify return values are only valid till the next call. Instead just add a couple more switch() cases for the INIT_PAGE cases. Now the returned value will always be valid. A couple rm_identify() callbacks missed masking xl_info with ~XLR_INFO_MASK. pg_xlogdump didn't map a NULL rm_identify to UNKNOWN or a similar string. append_init() was called when id=NULL - which should never actually happen. But it's better to be careful. http://git.postgresql.org/pg/commitdiff/604f7956b9460192222dd37bd3baea24cb669a47
  • Remove postgres --help blurb about the removed -A option. I missed this in 3bdcf6a5a755503. Noticed by Merlin Moncure. Discussion: CAHyXU0yC7uPeeVzQROwtnrOP9dxTEUPYjB0og4qUnbipMEV57w@mail.gmail.com http://git.postgresql.org/pg/commitdiff/0926ef43c160cc8334d3e86f364bf9a7aa2da43f
  • Remove most volatile qualifiers from xlog.c. For the reason outlined in df4077cda2e also remove volatile qualifiers from xlog.c. Some of these uses of volatile have been added after noticing problems back when spinlocks didn't imply compiler barriers. So they are a good test - in fact removing the volatiles breaks when done without the barriers in spinlocks present. Several uses of volatile remain where they are explicitly used to access shared memory without locks. These locations are ok with slightly out of date data, but removing the volatile might lead to the variables never being reread from memory. These uses could also be replaced by barriers, but that's a separate change of doubtful value. http://git.postgresql.org/pg/commitdiff/6ba4ecbf477e0b25dd7bde1b0c4e07fc2da19348
  • Fix VPATH builds of the replication parser from git for some !gcc compilers. Some compilers don't automatically search the current directory for included files. 9cc2c182fc2 fixed that for builds from tarballs by adding an include to the source directory. But that doesn't work when the scanner is generated in the VPATH directory. Use the same search path as the other parsers in the tree. One compiler that definitely was affected is solaris' sun cc. Backpatch to 9.1 which introduced using an actual parser for replication commands. http://git.postgresql.org/pg/commitdiff/56a312aac8802c2aa01530101d2d19e63568eeda
  • Add a basic atomic ops API abstracting away platform/architecture details. Several upcoming performance/scalability improvements require atomic operations. This new API avoids the need to splatter compiler and architecture dependent code over all the locations employing atomic ops. For several of the potential usages it'd be problematic to maintain both, a atomics using implementation and one using spinlocks or similar. In all likelihood one of the implementations would not get tested regularly under concurrency. To avoid that scenario the new API provides a automatic fallback of atomic operations to spinlocks. All properties of atomic operations are maintained. This fallback - obviously - isn't as fast as just using atomic ops, but it's not bad either. For one of the future users the atomics ontop spinlocks implementation was actually slightly faster than the old purely spinlock using implementation. That's important because it reduces the fear of regressing older platforms when improving the scalability for new ones. The API, loosely modeled after the C11 atomics support, currently provides 'atomic flags' and 32 bit unsigned integers. If the platform efficiently supports atomic 64 bit unsigned integers those are also provided. To implement atomics support for a platform/architecture/compiler for a type of atomics 32bit compare and exchange needs to be implemented. If available and more efficient native support for flags, 32 bit atomic addition, and corresponding 64 bit operations may also be provided. Additional useful atomic operations are implemented generically ontop of these. The implementation for various versions of gcc, msvc and sun studio have been tested. Additional existing stub implementations for Intel icc, HUPX acc, and IBM xlc are included but have never been tested. These will likely require fixes based on buildfarm and user feedback. As atomic operations also require barriers for some operations the existing barrier support has been moved into the atomics code. Author: Andres Freund with contributions from Oskari Saarenmaa Reviewed-By: Amit Kapila, Robert Haas, Heikki Linnakangas and Álvaro Herrera Discussion: CA+TgmoYBW+ux5-8Ja=Mcyuy8=VXAnVRHp3Kess6Pn3DMXAPAEA@mail.gmail.com, 20131015123303.GH5300@awork2.anarazel.de, 20131028205522.GI20248@awork2.anarazel.de http://git.postgresql.org/pg/commitdiff/b64d92f1a5602c55ee8b27a7ac474f03b7aee340
  • Fix atomic ops for x86 gcc compilers that don't understand atomic intrinsics. Per buildfarm animal locust. http://git.postgresql.org/pg/commitdiff/88bcdf9da5aa67da11ada0921703432ef2b7c21c
  • Fix atomic ops inline x86 inline assembly for older 32bit gccs. Some x86 32bit versions of gcc apparently generate references to the nonexistant %sil register when using when using the r input constraint, but not with the =q constraint. The latter restricts allocations to a/b/c/d which should all work. http://git.postgresql.org/pg/commitdiff/f18cad944911f05ad2e876af67362e28584b3c61
  • Define META_FREE in a way that doesn't cause -Wempty-body warnings. That get rids of the only -Wempty-body warning when compiling postgres with gcc 4.8/9. As 6550b901f shows, it's useful to be able to use that option routinely. Without asserts there's many more warnings, but that's food for another commit. http://git.postgresql.org/pg/commitdiff/9b6bb9b47153e4bf6d23bef13165491ba440fc23
  • Fix a couple occurrences of 'the the' in the new atomics API. Author: Erik Rijkers http://git.postgresql.org/pg/commitdiff/a30199b01bd4b06bd13484baefb5f2f411ce14f4
  • Further atomic ops portability improvements and bug fixes. Don't play tricks for a more efficient pg_atomic_clear_flag() in the generic gcc implementation. The old version was broken on gcc < 4.7 on !x86 platforms. Per buildfarm member chipmunk: Make usage of __atomic() fences depend on. HAVE_GCC__ATOMIC_INT32_CAS instead of HAVE_GCC__ATOMIC_INT64_CAS - there's platforms with 32bit support that don't support 64bit atomics. Blindly fix two superflous #endif in generic-xlc.h. Check for --disable-atomics in platforms but x86. http://git.postgresql.org/pg/commitdiff/f9f07411a5b879b232ade66fece7071bd2eb5c26

Robert Haas a poussé :

  • Fix mishandling of CreateEventTrigStmt's eventname field. It's a string, not a scalar. Petr Jelinek http://git.postgresql.org/pg/commitdiff/763ba1b0f2aa8353e3e3b02bc6bd965deb4575e0
  • Fix compiler warning. It is meaningless to declare a pass-by-value return type const. http://git.postgresql.org/pg/commitdiff/e38da8d6b1bd494b72a6f84310d30b55b3f67c3e
  • Remove volatile qualifiers from lwlock.c. Now that spinlocks (hopefully!) act as compiler barriers, as of commit 0709b7ee72e4bc71ad07b7120acd117265ab51d0, this should be safe. This serves as a demonstration of the new coding style, and may be optimized better on some machines as well. http://git.postgresql.org/pg/commitdiff/df4077cda2eae3eb4a5cf387da0c1e7616e73204
  • Change locking regimen around buffer replacement. Previously, we used an lwlock that was held from the time we began seeking a candidate buffer until the time when we found and pinned one, which is disastrous for concurrency. Instead, use a spinlock which is held just long enough to pop the freelist or advance the clock sweep hand, and then released. If we need to advance the clock sweep further, we reacquire the spinlock once per buffer. This represents a significant increase in atomic operations around buffer eviction, but it still wins on many workloads. On others, it may result in no gain, or even cause a regression, unless the number of buffer mapping locks is also increased. However, that seems like material for a separate commit. We may also need to consider other methods of mitigating contention on this spinlock, such as splitting it into multiple locks or jumping the clock sweep hand more than one buffer at a time, but those, too, seem like separate improvements. Patch by me, inspired by a much larger patch from Amit Kapila. Reviewed by Andres Freund. http://git.postgresql.org/pg/commitdiff/5d7962c6797c0baae9ffb3b5b9ac0aec7b598bc3
  • Fix identify_locking_dependencies for schema-only dumps. Without this fix, parallel restore of a schema-only dump can deadlock, because when the dump is schema-only, the dependency will still be pointing at the TABLE item rather than the TABLE DATA item. Robert Haas and Tom Lane http://git.postgresql.org/pg/commitdiff/07d46a8963ebbf69ef6e6853bb8a45623612dd34

Stephen Frost a poussé :

  • Process withCheckOption exprs in setrefs.c. While withCheckOption exprs had been handled in many cases by happenstance, they need to be handled during set_plan_references and more specifically down in set_plan_refs for ModifyTable plan nodes. This is to ensure that the opfuncid's are set for operators referenced in the withCheckOption exprs. Identified as an issue by Thom Brown Patch by Dean Rasheed Back-patch to 9.4, where withCheckOption was introduced. http://git.postgresql.org/pg/commitdiff/6ef8c658af2127f4e62cb24feade8b465c9e2fea
  • Log ALTER SYSTEM statements as DDL. Per discussion in bug #11350, log ALTER SYSTEM commands at the log_statement=ddl level, rather than at the log_statement=all level. Pointed out by Tomonari Katsumata. Back-patch to 9.4 where ALTER SYSTEM was introduced. http://git.postgresql.org/pg/commitdiff/43bed84c320443ee7247f7ca8d609640651aab93
  • Add unicode_*_linestyle to \? variables. In a2dabf0 we added the ability to have single or double unicode linestyle for the border, column, or header. Unfortunately, the \? variables output was not updated for these new psql variables. This corrects that oversight. Patch by Pavel Stehule. http://git.postgresql.org/pg/commitdiff/a564307373089fc81a07bce49236fe2bd66de0fe
  • Copy-editing of row security. Address a few typos in the row security update, pointed out off-list by Adam Brightwell. Also include 'ALL' in the list of commands supported, for completeness. http://git.postgresql.org/pg/commitdiff/afd1d95f5bf0cb48af77e5897eb4c356b5371c7b
  • Code review for row security. Buildfarm member tick identified an issue where the policies in the relcache for a relation were were being replaced underneath a running query, leading to segfaults while processing the policies to be added to a query. Similar to how TupleDesc RuleLocks are handled, add in a equalRSDesc() function to check if the policies have actually changed and, if not, swap back the rsdesc field (using the original instead of the temporairly built one; the whole structure is swapped and then specific fields swapped back). This now passes a CLOBBER_CACHE_ALWAYS for me and should resolve the buildfarm error. In addition to addressing this, add a new chapter in Data Definition under Privileges which explains row security and provides examples of its usage, change \d to always list policies (even if row security is disabled- but note that it is disabled, or enabled with no policies), rework check_role_for_policy (it really didn't need the entire policy, but it did need to be using has_privs_of_role()), and change the field in pg_class to relrowsecurity from relhasrowsecurity, based on Heikki's suggestion. Also from Heikki, only issue SET ROW_SECURITY in pg_restore when talking to a 9.5+ server, list Bypass RLS in \du, and document --enable-row-security options for pg_dump and pg_restore. Lastly, fix a number of minor whitespace and typo issues from Heikki, Dimitri, add a missing #include, per Peter E, fix a few minor variable-assigned-but-not-used and resource leak issues from Coverity and add tab completion for role attribute bypassrls as well. http://git.postgresql.org/pg/commitdiff/6550b901fe7c47c03775400e0c790c6c1234a017
  • Fix relcache for policies, and doc updates. Andres pointed out that there was an extra ';' in equalPolicies, which made me realize that my prior testing with CLOBBER_CACHE_ALWAYS was insufficient (it didn't always catch the issue, just most of the time). Thanks to that, a different issue was discovered, specifically in equalRSDescs. This change corrects eqaulRSDescs to return 'true' once all policies have been confirmed logically identical. After stepping through both functions to ensure correct behavior, I ran this for about 12 hours of CLOBBER_CACHE_ALWAYS runs of the regression tests with no failures. In addition, correct a few typos in the documentation which were pointed out by Thom Brown (thanks!) and improve the policy documentation further by adding a flushed out usage example based on a unix passwd file. Lastly, clean up a few comments in the regression tests and pg_dump.h. http://git.postgresql.org/pg/commitdiff/ff27fcfa0affe16405e801ed55fed10e7bc75216

Tom Lane a poussé :

  • Fix incorrect search for "x?" style matches in creviterdissect(). When the number of allowed iterations is limited (either a "?" quantifier or a bound expression), the last sub-match has to reach to the end of the target string. The previous coding here first tried the shortest possible match (one character, usually) and then gave up and back-tracked if that didn't work, typically leading to failure to match overall, as shown in bug #11478 from Christoph Berg. The minimum change to fix that would be to not decrement k before "goto backtrack"; but that would be a pretty stupid solution, because we'd laboriously try each possible sub-match length before finally discovering that only ending at the end can work. Instead, force the sub-match endpoint limit up to the end for even the first shortest() call if we cannot have any more sub-matches after this one. Bug introduced in my rewrite that added the iterdissect logic, commit 173e29aa5deefd9e71c183583ba37805c8102a72. The shortest-first search code was too closely modeled on the longest-first code, which hasn't got this issue since it tries a match reaching to the end to start with anyway. Back-patch to all affected branches. http://git.postgresql.org/pg/commitdiff/3694b4d7e1aa02f917f9d18c550fbb49b96efa83
  • Fix bogus variable-mangling in security_barrier_replace_vars(). This function created new Vars with varno different from varnoold, which is a condition that should never prevail before setrefs.c does the final variable-renumbering pass. The created Vars could not be seen as equal() to normal Vars, which among other things broke equivalence-class processing for them. The consequences of this were indeed visible in the regression tests, in the form of failure to propagate constants as one would expect. I stumbled across it while poking at bug #11457 --- after intentionally disabling join equivalence processing, the security-barrier regression tests started falling over with fun errors like "could not find pathkey item to sort", because of failure to match the corrupted Vars to normal ones. http://git.postgresql.org/pg/commitdiff/3f6f9260e308a331e6809d5309b17d1613ff900f

Andrew Dunstan a poussé :

Heikki Linnakangas a poussé :

  • Add -D option to specify data directory to pg_controldata and pg_resetxlog. It was confusing that to other commands, like initdb and postgres, you would pass the data directory with "-D datadir", but pg_controldata and pg_resetxlog would take just plain path, without the "-D". With this patch, pg_controldata and pg_resetxlog also accept "-D datadir". Abhijit Menon-Sen, with minor kibitzing by me http://git.postgresql.org/pg/commitdiff/b0d81adea650a4bc2b9391234345bb935b89a694
  • Refactor space allocation for base64 encoding/decoding in pgcrypto. Instead of trying to accurately calculate the space needed, use a StringInfo that's enlarged as needed. This is just moving things around currently - the old code was not wrong - but this is in preparation for a patch that adds support for extra armor headers, and would make the space calculation more complicated. Marko Tiikkaja http://git.postgresql.org/pg/commitdiff/1dcfb8da09c47d2a7502d1dfab06c8be4b6cf323

Peter Eisentraut a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Antonin Houska sent in a patch to fix an issue with from_collapse_limit.
  • Pavan Deolasee sent in a patch to fix an assertion failure in synchronous replication.
  • Rahila Syed sent in another revision of a patch to compress full-page writes.
  • Michael Paquier sent in a patch to remove code duplication in walsender.c and syncrep.c in order to detect what is the node with the lowest strictly-positive priority, facilitating maintenance of this code.
  • Andrew Dunstan sent in a patch to add some missing JSONB conversion functions.
  • Rajeev Rastogi sent in another revision of a patch to implement an index scan optimization.
  • Alvaro Herrera sent in two more revisions of a patch to implement BRIN indexes.
  • Robert Haas and Ants Aasma traded patches to help scale buffer eviction.
  • Peter Geoghegan sent in two more revisions of a patch to add INSERT ... ON CONFLICT {UPDATE | IGNORE}.
  • Robert Haas sent in another revision of a patch to add the modulo (%) operator to pgbench.
  • Abhijit Menon-Sen sent in a patch to fix an issue with END_OF_RECOVERY shutdowns and ResetUnloggedRelations().
  • Alvaro Herrera and Michael Paquier traded patches to enable event triggers to fire on CREATE.
  • Dilip Kumar sent in another revision of a patch to allow parallel cores to be used by vacuumdb.
  • Tom Lane sent in another revision of a patch to fix the poor TOAST compression for JSONB.
  • Andrew Dunstan sent in a patch to add JSONB generator functions.
  • Robert Haas sent in another revision of a patch to implement a pg_background contrib extension, which runs commands in a background worker, and gets the results.
  • Andrew (RhodiumToad) Gierth sent in another revision of a patch to implement GROUPING SETS.
  • Andres Freund and Heikki Linnakangas traded patches to fix an issue where it was impossible to escape from a blocked send().
  • Emre Hasegeli sent in another revision of a patch to implement selectivity estimation for inet operators.
  • Stephen Frost sent in a patch to fix an infelicity between WITH CHECK OPTION for views and column-level privileges.
  • Marko (johto) Tiikkaja sent in another revision of a patch to implement PGP armor headers in the contrib/pgcrypto extension.
  • Jeff Janes sent in a patch to fix dynahash logging.
  • Michael Paquier and Fabrízio de Royes Mello traded patches to add missing newlines in verbose logs of pg_dump, introduced by RLS patch.
  • Bogdan Pilch sent in a patch to allow toggling tab completion in psql.
  • Simon Riggs sent in another revision of a patch to allow disabling the HOT optimization.
  • Platon Malugin sent in a patch to add generate_series(numeric, numeric).
  • Bogdan Pilch sent in a patch to make psql's time output format more readable.

par N Bougain le lundi 29 septembre 2014 à 22h52

Nouvelles hebdomadaires de PostgreSQL - 21 septembre 2014

La première rencontre PostgreSQL moscovienne à être organisée via Meetup aura lieu à Moscou le 24 septembre 2014. RSVP pour aider à l'organisation. http://www.meetup.com/postgresqlrussia/

[ndt: Meetup PUG parisien le 8 octobre : http://www.meetup.com/PostgreSQL-User-Group-Paris/]

Offres d'emplois autour de PostgreSQL en septembre

PostgreSQL Local

  • La cinquième édition du PgDay argentin est programmée pour le 28 novembre 2014 à Santa Fe. L'appel international à conférenciers court jusqu'au 28 septembre : http://www.postgresql.org.ar/pgday2014
  • 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.
  • Le sixième PGDay cubain aura lieu les 13 et 14 octobre 2014 à la Havane : https://postgresql.uci.cu/?p=380
  • 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/

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

  • Follow the RFCs more closely in libpq server certificate hostname check. The RFCs say that the CN must not be checked if a subjectAltName extension of type dNSName is present. IOW, if subjectAltName extension is present, but there are no dNSNames, we can still check the CN. Alexey Klyukin http://git.postgresql.org/pg/commitdiff/58e70cf9fb42c1ad60b8ba730fd129f2ce6fa332
  • Fix the return type of GIN triConsistent support functions to "char". They were marked to return a boolean, but they actually return a GinTernaryValue, which is more like a "char". It makes no practical difference, as the triConsistent functions cannot be called directly from SQL because they have "internal" arguments, but this nevertheless seems more correct. Also fix the GinTernaryValue name in the documentation. I renamed the enum earlier, but neglected the docs. Alexander Korotkov. This is new in 9.4, so backpatch there. http://git.postgresql.org/pg/commitdiff/77e65bf369edd0282531794dd907a0943651eff8

Peter Eisentraut a poussé :

Andres Freund a poussé :

  • Fix configure check for %z printf support after INT64_MODIFIER changes. The PGAC_FUNC_SNPRINTF_SIZE_T_SUPPORT test was broken by ce486056ecd28050. Among others it made the UINT64_FORMAT macro to be defined in c.h, instead of directly being defined by configure. This lead to the replacement printf being used on all platforms for a while. Which seems to work, because this was only used due to different profiles Fix by relying on INT64_MODIFIER instead. http://git.postgresql.org/pg/commitdiff/7e3f728353fa9b36c7f98b6ec447d3f1b8deec14
  • Add the capability to display summary statistics to pg_xlogdump. The new --stats/--stats=record options to pg_xlogdump display per rmgr/per record statistics about the parsed WAL. This is useful to understand what the WAL primarily consists of, to allow targeted optimizations on application, configuration, and core code level. It is likely that we will want to fine tune the statistics further, but the feature already is quite helpful. Author: Abhijit Menon-Sen, slightly editorialized by me Reviewed-By: Andres Freund, Dilip Kumar and Furuya Osamu Discussion: 20140604104716.GA3989@toroid.org http://git.postgresql.org/pg/commitdiff/bdd5726c347016ea34e9fb68d1b8b71046e25d2e
  • Add rmgr callback to name xlog record types for display purposes. This is primarily useful for the upcoming pg_xlogdump --stats feature, but also allows to remove some duplicated code in the rmgr_desc routines. Due to the separation and harmonization, the output of dipsplayed records changes somewhat. But since this isn't enduser oriented content that's ok. It's potentially desirable to further change pg_xlogdump's display of records. It previously wasn't possible to show the record type separately from the description forcing it to be in the last column. But that's better done in a separate commit. Author: Abhijit Menon-Sen, slightly editorialized by me Reviewed-By: Álvaro Herrera, Andres Freund, and Heikki Linnakangas Discussion: 20140604104716.GA3989@toroid.org http://git.postgresql.org/pg/commitdiff/728f152e07f998d2cb4fe5f24ec8da2c3bda98f2
  • Avoid 'clobbered by longjmp' warning in psql/copy.c. This was introduced in 51bb79569f934ad2135c2ff859c61b9ab8d51750. http://git.postgresql.org/pg/commitdiff/afaefa1b31ba900bdc6987a752483e1580356e48
  • Mark x86's memory barrier inline assembly as clobbering the cpu flags. x86's memory barrier assembly was marked as clobbering "memory" but not "cc" even though 'addl' sets various flags. As it turns out gcc on x86 implicitly assumes "cc" on every inline assembler statement, so it's not a bug. But as that's poorly documented and might get copied to architectures or compilers where that's not the case, it seems better to be precise. Discussion: 20140919100016.GH4277@alap3.anarazel.de To keep the code common, backpatch to 9.2 where explicit memory barriers were introduced. http://git.postgresql.org/pg/commitdiff/e5603a2f35baa0bc9d61b16373383fdd37e49509

Robert Haas a poussé :

Tom Lane a poussé :

  • Fix failure of contrib/auto_explain to print per-node timing information. This has been broken since commit af7914c6627bcf0b0ca614e9ce95d3f8056602bf, which added the EXPLAIN (TIMING) option. Although that commit included updates to auto_explain, they evidently weren't tested very carefully, because the code failed to print node timings even when it should, due to failure to set es.timing in the ExplainState struct. Reported off-list by Neelakanth Nadgir of Salesforce. In passing, clean up the documentation for auto_explain's options a little bit, including re-ordering them into what seems to me a more logical order. http://git.postgresql.org/pg/commitdiff/898f8a96ef9dfa79eac9ec1621a89e71fe16601c

Stephen Frost a poussé :

  • Row-Level Security Policies (RLS) Building on the updatable security-barrier views work, add the ability to define policies on tables to limit the set of rows which are returned from a query and which are allowed to be added to a table. Expressions defined by the policy for filtering are added to the security barrier quals of the query, while expressions defined to check records being added to a table are added to the with-check options of the query. New top-level commands are CREATE/ALTER/DROP POLICY and are controlled by the table owner. Row Security is able to be enabled and disabled by the owner on a per-table basis using ALTER TABLE .. ENABLE/DISABLE ROW SECURITY. Per discussion, ROW SECURITY is disabled on tables by default and must be enabled for policies on the table to be used. If no policies exist on a table with ROW SECURITY enabled, a default-deny policy is used and no records will be visible. By default, row security is applied at all times except for the table owner and the superuser. A new GUC, row_security, is added which can be set to ON, OFF, or FORCE. When set to FORCE, row security will be applied even for the table owner and superusers. When set to OFF, row security will be disabled when allowed and an error will be thrown if the user does not have rights to bypass row security. Per discussion, pg_dump sets row_security = OFF by default to ensure that exports and backups will have all data in the table or will error if there are insufficient privileges to bypass row security. A new option has been added to pg_dump, --enable-row-security, to ask pg_dump to export with row security enabled. A new role capability, BYPASSRLS, which can only be set by the superuser, is added to allow other users to be able to bypass row security using row_security = OFF. Many thanks to the various individuals who have helped with the design, particularly Robert Haas for his feedback. Authors include Craig Ringer, KaiGai Kohei, Adam Brightwell, Dean Rasheed, with additional changes and rework by me. Reviewers have included all of the above, Greg Smith, Jeff McCormick, and Robert Haas. http://git.postgresql.org/pg/commitdiff/491c029dbc4206779cf659aa0ff986af7831d2ff

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Marko (johto) Tiikkaja sent in another revision of a patch to add PGP signatures to the pgcrypto contrib extension.
  • Kevin Grittner sent in another revision of a patch to add the capacity to use intermediate tables in queries with an immediate eye to the maintenance of materialized views.
  • Heikki Linnakangas sent in another revision of a patch to add a pgbench throttling latency limit.
  • Heikki Linnakangas sent in another revision of a patch to change the WAL format and API.
  • David Rowley sent in two more revisions of a patch to remove inner joins in certain cases where they can be proven redundant.
  • Joachim Wieland sent in another revision of a patch to remove global variables from pg_dump.
  • Szymon Guz sent in two revisions of a patch to add asciidoc as a format psql can use for output.
  • Heikki Linnakangas sent in another revision of a patch to fix an issue where JSONB compressed poorly.
  • Kaigai Kouhei sent in another revision of a patch to implement a custom plan API.
  • Andres Freund sent in a patch to remove the volatile qualifiers from xlog.c.
  • Kyotaro HORIGUCHI sent in another revision of a patch to process pg_hba.conf keywords as case-insensitive.
  • Pavel Stehule sent in two more revisions of a patch to document unicode border styles in psql.
  • Michael Paquier sent in a patch to remove OUTPUT_PLUGIN_* from existing logical decoding APIs.
  • Robert Haas sent in a patch to fix an issue where identify_locking_dependencies was broken for schema-only dumps.
  • Petr (PJMODOS) Jelinek sent in a patch to fix a couple of instances where COPY_SCALAR_FIELD is called instead of the correct COPY_STRING_FIELD.
  • Amit Kapila sent in two more revisions of a patch to scale shared buffer eviction.
  • Dean Rasheed sent in two revisions of a patch to ensure that WITH CHECK OPTION works correctly with row-level access control.
  • Haribabu Kommi sent in another revision of a patch to fix some oddities in per-table autovacuum vacuum cost limit behaviour.
  • Michael Paquier sent in two more revisions of a patch to refactor pg_basebackup and support for replslot creation and drop in pg_receivexlog.
  • Michael Paquier sent in another revision of a patch to make pg_dump able to dump from replication slot creation state.
  • Rajeev Rastogi sent in a patch to optimize index scans.
  • Michael Paquier sent in a patch to fix the documentation for pg_recvlogical's --create mode.

par N Bougain le lundi 29 septembre 2014 à 00h12

lundi 22 septembre 2014

Nicolas Thauvin

Confiner PostgreSQL avec SELinux

J'avais déjà expérimenté un peu avec SELinux il y a deux ans sans aller trop loin, parce que j'entendais souvent la phrase "Si on veut de la sécurité, il faut SELinux" et surtout à cause de l'arrivée de l'extension sepgsql dans les modules contrib de PostgreSQL. Ça avait donné une conf pour le Fosdem où finalement, j'ai plus parlé des privilèges classiques que de SELinux.

Suite à une demande au ${BOULOT}, je me suis replongé dedans, et les choses ont peu évolué. Dans la majorité des recherches que j'ai pu faire, SELinux reste tout de même un truc qui se met en travers la route, c'est-à-dire, qu'il y a toujours plus de résultats sur comment le désactiver plutôt que configurer les choses correctement. Ensuite, certains proposent de faire aveuglement confiance à setroubleshoot et audit2allow pour faire taire la bête. Enfin, j'ai dû trouver une page ou deux après en deux semaines à temps plein sur le sujet qui parlent de comment confiner un utilisateur dans le but d'implémenter ce que promet SELinux et que toutes les entreprises demandent : le RBAC, Role Based Access Control, ou comment donner le moins de droits possibles aux sous-traitants qui hébergent les serveurs.

Cette fois ci, je suis allé plus loin. Déjà, je suis parti sur une installation de CentOS 6, la famille de distribution RHEL/CentOS/Fedora semble être la plus en avance par rapport à SELinux : à peu près tous les services/daemons fournis dans l'install sont confinés par SELinux. Les utilisateurs ne le sont pas au login, il n'y a donc aucune configuration de RBAC. On verra peut-être ça plus tard, j'ai pas encore obtenu de résultat satisfaisant sur ce sujet, et il vaut mieux expliquer comment confiner correctement PostgreSQL avant de passer aux roles. Tout simplement, parce qu'il est très simple de casser ce confinement avec la configuration par défaut de RHEL, grâce à pg_ctl. Enfin, l'installation des paquets du PGDG n'est pas confinée par défaut, il manque les file contexts adaptés aux chemins particuliers de ces paquets, prévu pour faire cohabiter plusieurs versions majeures ; ce qui ne correspond pas ce qu'à prévu Red Hat pour PostgreSQL.

Après cette longue introduction, mais avant de commencer, il faut savoir administrer un minumum SELinux : si vous ne savez pas qu'il existe des options -Z, ce que sont les contexts, les types et les domaines, mieux vaut d'abord se documenter, par exemple chez Red Hat.

Voici donc les file contexts à ajouter dans un fichier module.te pour confiner l'installation PostgreSQL du PGDG :

/etc/rc\.d/init\.d/(se)?postgresql(-.*)?    --  gen_context(system_u:object_r:postgresql_initrc_exec_t,s0)
/usr/pgsql-[0-9]+\.[0-9]+/bin/initdb        --  gen_context(system_u:object_r:postgresql_exec_t,s0)
/usr/pgsql-[0-9]+\.[0-9]+/bin/pg_ctl        --  gen_context(system_u:object_r:postgresql_initrc_exec_t,s0)
/usr/pgsql-[0-9]+\.[0-9]+/bin/postgres      --  gen_context(system_u:object_r:postgresql_exec_t,s0)
/usr/pgsql-[0-9]+.[0-9]+/share/locale(/.*)?     gen_context(system_u:object_r:locale_t,s0)
/usr/pgsql-[0-9]+.[0-9]+/share/man(/.*)?        gen_context(system_u:object_r:man_t,s0)

Tout d'abord, pour l'init script et pg_ctl, on utilise le type postgresql_initrc_exec_t, c'est ce qui permet de lancer PostgreSQL dans le domaine confiné postgresql_t au boot, via l'init script, et manuellement. La méthode la plus propre est de toujours utiliser l'init script, idéalement par l'intermédiaire de run_init pour démarrer, arrêter ou redémarrer le postmaster. On évite alors de laisser trainer des choses dans /var/{run,lock}.

Les programmes postgres et initdb doivent avoir le type postgresql_exec_t car ils exécutent le serveur PostgreSQL ; cela doit se faire dans le domaine postgresql_t.

Enfin, on a placé les labels corrects sur les fichiers de traduction et les pages de man, pour faire plus propre. Ce code source de module SELinux alors doit être compilé et chargé.

Cette configuration est reprise dans le module SELinux disponible sur github. On peut aussi l'ajouter manuellement avec semanage :

semanage fcontext -a -t postgresql_initrc_exec_t '/etc/rc\.d/init\.d/(se)?postgresql(-.*)?'
semanage fcontext -a -t postgresql_exec_t '/usr/pgsql-[0-9]+\.[0-9]+/bin/initdb'
semanage fcontext -a -t postgresql_initrc_exec_t '/usr/pgsql-[0-9]+\.[0-9]+/bin/pg_ctl'
semanage fcontext -a -t postgresql_exec_t '/usr/pgsql-[0-9]+\.[0-9]+/bin/postgres'
semanage fcontext -a -t locale_t '/usr/pgsql-[0-9]+.[0-9]+/share/locale(/.*)?'
semanage fcontext -a -t man_t '/usr/pgsql-[0-9]+.[0-9]+/share/man(/.*)?'

Il existe un certain nombre de booleans pour la configuration des droits SELinux de PostgreSQL, le plus important concerne rsync, souvent nécessaire pour faire des base backups. Il s'agit de postgresql_can_rsync, pour l'activer :

semanage boolean -m --on postgresql_can_rsync

Si on lance l'instance sur un port TCP différent de 5432, il faut l'autoriser dans la configuration locale de SELinux :

semanage port -a -t postgresql_port_t -p tcp <port>

Enfin, il ne faut pas oublier d'appliquer les contexts aux fichiers soit avec restorecon, un relabel complet au reboot ou chcon.

lundi 22 septembre 2014 à 14h23

Rodolphe Quiédeville

Django Paginator oui mais

Après la lecture de l'excellent article de Markus Winnand no-offset j'ai réalisé un test pour mesurer la différence de performance de l'utilisation d'OFFSET avec Django.

La suite à lire sur le blog Novapost's paradize.

par Rodolphe Quiédeville le lundi 22 septembre 2014 à 08h13

lundi 15 septembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 14 septembre 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en septembre

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

  • Fix psql \s to work with recent libedit, and add pager support. psql's \s (print command history) doesn't work at all with recent libedit versions when printing to the terminal, because libedit tries to do an fchmod() on the target file which will fail if the target is /dev/tty. (We'd already noted this in the context of the target being /dev/null.) Even before that, it didn't work pleasantly, because libedit likes to encode the command history file (to ensure successful reloading), which renders it nigh unreadable, not to mention significantly different-looking depending on exactly which libedit version you have. So let's forget using write_history() for this purpose, and instead print the data ourselves, using logic similar to that used to iterate over the history for newline encoding/decoding purposes. While we're at it, insert the ability to use the pager when \s is printing to the terminal. This has been an acknowledged shortcoming of \s for many years, so while you could argue it's not exactly a back-patchable bug fix it still seems like a good improvement. Anyone who's seriously annoyed at this can use "\s /dev/tty" or local equivalent to get the old behavior. Experimentation with this showed that the history iteration logic was actually rather broken when used with libedit. It turns out that with libedit you have to use previous_history() not next_history() to advance to more recent history entries. The easiest and most robust fix for this seems to be to make a run-time test to verify which function to call. We had not noticed this because libedit doesn't really need the newline encoding logic: its own encoding ensures that command entries containing newlines are reloaded correctly (unlike libreadline). So the effective behavior with recent libedits was that only the oldest history entry got newline-encoded or newline-decoded. However, because of yet other bugs in history_set_pos(), some old versions of libedit allowed the existing loop logic to reach entries besides the oldest, which means there may be libedit ~/.psql_history files out there containing encoded newlines in more than just the oldest entry. To ensure we can reload such files, it seems appropriate to back-patch this fix, even though that will result in some incompatibility with older psql versions (ie, multiline history entries written by a psql with this fix will look corrupted to a psql without it, if its libedit is reasonably up to date). Stepan Rutz and Tom Lane http://git.postgresql.org/pg/commitdiff/750c5ee6ce4410faaa4bf2b8d861100f30d97c73
  • Preserve AND/OR flatness while extracting restriction OR clauses. The code I added in commit f343a880d5555faf1dad0286c5632047c8f599ad was careless about preserving AND/OR flatness: it could create a structure with an OR node directly underneath another one. That breaks an assumption that's fairly important for planning efficiency, not to mention triggering various Asserts (as reported by Benjamin Smith). Add a trifle more logic to handle the case properly. http://git.postgresql.org/pg/commitdiff/1b4cc493d2b5216c14ba3ee781cbeff56f96d1b7
  • Add width_bucket(anyelement, anyarray). This provides a convenient method of classifying input values into buckets that are not necessarily equal-width. It works on any sortable data type. The choice of function name is a bit debatable, perhaps, but showing that there's a relationship to the SQL standard's width_bucket() function seems more attractive than the other proposals. Petr Jelinek, reviewed by Pavel Stehule http://git.postgresql.org/pg/commitdiff/e80252d424278abf65b624669c8e6b3fe8587cac
  • Fix JSON regression tests. Commit 95d737ff45a38809130a2c633d9e6bc26c825036 neglected to update expected/json_1.out. Per buildfarm. http://git.postgresql.org/pg/commitdiff/e3ec07280cc2bd4201f62cbde9a15a62eb54bc40
  • Fix power_var_int() for large integer exponents. The code for raising a NUMERIC value to an integer power wasn't very careful about large powers. It got an outright wrong answer for an exponent of INT_MIN, due to failure to consider overflow of the Abs(exp) operation; which is fixable by using an unsigned rather than signed exponent value after that point. Also, even though the number of iterations of the power-computation loop is pretty limited, it's easy for the repeated squarings to result in ridiculously enormous intermediate values, which can take unreasonable amounts of time/memory to process, or even overflow the internal "weight" field and so produce a wrong answer. We can forestall misbehaviors of that sort by bailing out as soon as the weight value exceeds what will fit in int16, since then the final answer must overflow (if exp > 0) or underflow (if exp < 0) the packed numeric format. Per off-list report from Pavel Stehule. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/1d352325b88afef6effbd2c3a52930b824944375
  • Invent PGC_SU_BACKEND and mark log_connections/log_disconnections that way. This new GUC context option allows GUC parameters to have the combined properties of PGC_BACKEND and PGC_SUSET, ie, they don't change after session start and non-superusers can't change them. This is a more appropriate choice for log_connections and log_disconnections than their previous context of PGC_BACKEND, because we don't want non-superusers to be able to affect whether their sessions get logged. Note: the behavior for log_connections is still a bit odd, in that when a superuser attempts to set it from PGOPTIONS, the setting takes effect but it's too late to enable or suppress connection startup logging. It's debatable whether that's worth fixing, and in any case there is a reasonable argument for PGC_SU_BACKEND to exist. In passing, re-pgindent the files touched by this commit. Fujii Masao, reviewed by Joe Conway and Amit Kapila http://git.postgresql.org/pg/commitdiff/fe550b2ac249af5fbd8e9e19290a4ba43c882f2d

Andres Freund a poussé :

  • Fix spinlock implementation for some !solaris sparc platforms. Some Sparc CPUs can be run in various coherence models, ranging from RMO (relaxed) over Phil Sorber (partial) to TSO (total). Solaris has always run CPUs in TSO mode while in userland, but linux didn't use to and the various *BSDs still don't. Unfortunately the sparc TAS/S_UNLOCK were only correct under TSO. Fix that by adding the necessary memory barrier instructions. On sparcv8+, which should be all relevant CPUs, these are treated as NOPs if the current consistency model doesn't require the barriers. Discussion: 20140630222854.GW26930@awork2.anarazel.de Will be backpatched to all released branches once a few buildfarm cycles haven't shown up problems. As I've no access to sparc, this is blindly written. http://git.postgresql.org/pg/commitdiff/07968dbfaad03001db632a5c0ce4acccd5e463e2
  • Fix typo in solaris spinlock fix. 07968dbfaad03 missed part of the S_UNLOCK define when building for sparcv8+. http://git.postgresql.org/pg/commitdiff/50881036b17dc07cc61535d5bf27c56abaa4fbbb
  • Fix typo in 07c8651dd91d5 causing WIN32_ONLY_COMPILER builds to fail. http://git.postgresql.org/pg/commitdiff/b4c28d1b92c81941e4fc124884e51a7c110316bf
  • Add support for optional_argument to our own getopt_long() implementation. 07c8651dd91d5a currently causes compilation errors on mscv (and probably some other) compilers because our getopt_long() implementation doesn't have support for optional_argument. Thus implement optional_argument in our fallback implemenation. It's quite possibly also useful in other cases. Arguably this needs a configure check for optional_argument, but it has existed pretty much since getopt_long() was introduced and thus doesn't seem worth the configure runtime. Normally I'd would not push a patch this fast, but this allows msvc to build again and has low risk as only optional_argument behaviour has changed. Author: Michael Paquier and Andres Freund Discussion: CAB7nPqS5VeedSCxrK=QouokbawgGKLpyc1Q++RRFCa_sjcSVrg@mail.gmail.com http://git.postgresql.org/pg/commitdiff/311da16439ef69fc2054af3f4377fd4acd29a0e3
  • Add new psql help topics, accessible to both --help and \?. Add --help=<topic> for the commandline, and \? <topic> as a backslash command, to show more help than the invocations without parameters do. "commands", "variables" and "options" currently exist as help topics describing, respectively, backslash commands, psql variables, and commandline switches. Without parameters the help commands show their previous topic. Some further wordsmithing or extending of the added help content might be needed; but there seems little benefit delaying the overall feature further. Author: Pavel Stehule, editorialized by many Reviewed-By: Andres Freund, Petr Jelinek, Fujii Masao, MauMau, Abhijit Menon-Sen and Erik Rijkers. Discussion: CAFj8pRDVGuC-nXBfe2CK8vpyzd2Dsr9GVpbrATAnZO=2YQ0s2Q@mail.gmail.com, CAFj8pRA54AbTv2RXDTRxiAd8hy8wxmoVLqhJDRCwEnhdd7OUkw@mail.gmail.com http://git.postgresql.org/pg/commitdiff/07c8651dd91d5aea38bee268acb582674ca4b5ea

Stephen Frost a poussé :

  • Move ALTER ... ALL IN to ProcessUtilitySlow. Now that ALTER TABLE .. ALL IN TABLESPACE has replaced the previous ALTER TABLESPACE approach, it makes sense to move the calls down in to ProcessUtilitySlow where the rest of ALTER TABLE is handled. This also means that event triggers will support ALTER TABLE .. ALL (which was the impetus for the original change, though it has other good qualities also). Álvaro Herrera Back-patch to 9.4 as the original rework was. http://git.postgresql.org/pg/commitdiff/f0051c1a142ecaff7828b87315c26c7acbf8e583
  • Add 'ignore_nulls' option to row_to_json. Provide an option to skip NULL values in a row when generating a JSON object from that row with row_to_json. This can reduce the size of the JSON object in cases where columns are NULL without really reducing the information in the JSON object. This also makes row_to_json into a single function with default values, rather than having multiple functions. In passing, change array_to_json to also be a single function with default values (we don't add an 'ignore_nulls' option yet- it's not clear that there is a sensible use-case there, and it hasn't been asked for in any case). Pavel Stehule http://git.postgresql.org/pg/commitdiff/95d737ff45a38809130a2c633d9e6bc26c825036
  • Handle border = 3 in expanded mode. In psql, expanded mode was not being displayed correctly when using the normal ascii or unicode linestyles and border set to '3'. Now, per the documentation, border '3' is really only sensible for HTML and LaTeX formats, however, that's no excuse for ascii/unicode to break in that case, and provisions had been made for psql to cleanly handle this case (and it did, in non-expanded mode). This was broken when ascii/unicode was initially added a good five years ago because print_aligned_vertical_line wasn't passed in the border setting being used by print_aligned_vertical but instead was given the whole printTableContent. There really isn't a good reason for vertical_line to have the entire printTableContent structure, so just pass in the printTextFormat and border setting (similar to how this is handled in horizontal_line). Pointed out by Pavel Stehule, fix by me. Back-patch to all currently-supported versions. http://git.postgresql.org/pg/commitdiff/82962838d4bea2a20babbd2efacb48b268b4a689
  • Add unicode_{column|header|border}_style to psql With the unicode linestyle, this adds support to control if the column, header, or border style should be single or double line unicode characters. The default remains 'single'. In passing, clean up the border documentation and address some minor formatting/spelling issues. Pavel Stehule, with some additional changes by me. http://git.postgresql.org/pg/commitdiff/a2dabf0e1dda93c860b10bff7b73617e7b090108

Peter Eisentraut a poussé :

Robert Haas a poussé :

  • Change the spinlock primitives to function as compiler barriers. Previously, they functioned as barriers against CPU reordering but not compiler reordering, an odd API that required extensive use of volatile everywhere that spinlocks are used. That's error-prone and has negative implications for performance, so change it. In theory, this makes it safe to remove many of the uses of volatile that we currently have in our code base, but we may find that there are some bugs in this effort when we do. In the long run, though, this should make for much more maintainable code. Patch by me. Review by Andres Freund. http://git.postgresql.org/pg/commitdiff/0709b7ee72e4bc71ad07b7120acd117265ab51d0
  • Fix thinko in 0709b7ee72e4bc71ad07b7120acd117265ab51d0. Buildfarm member castoroides is unhappy with this, for entirely understandable reasons. http://git.postgresql.org/pg/commitdiff/5b26278822c69dd76ef89fd50ecc7cdba9c3f035
  • Add missing volatile qualifier. Yet another silly mistake in 0709b7ee72e4bc71ad07b7120acd117265ab51d0, again found by buildfarm member castoroides. http://git.postgresql.org/pg/commitdiff/68e66923ff629c324e219090860dc9e0e0a6f5d6
  • Change NTUP_PER_BUCKET to 1 to improve hash join lookup speed. Since this makes the bucket headers use ~10x as much memory, properly account for that memory when we figure out whether everything fits in work_mem. This might result in some cases that previously used only a single batch getting split into multiple batches, but it's unclear as yet whether we need defenses against that case, and if so, what the shape of those defenses should be. It's worth noting that even in these edge cases, users should still be no worse off than they would have been last week, because commit 45f6240a8fa9d35548eb2ef23dba2c11540aa02a saved a big pile of memory on exactly the same workloads. Tomas Vondra, reviewed and somewhat revised by me. http://git.postgresql.org/pg/commitdiff/8cce08f168481c5fc5be4e7e29b968e314f1b41e

Heikki Linnakangas a poussé :

  • Pack tuples in a hash join batch densely, to save memory. Instead of palloc'ing each HashJoinTuple individually, allocate 32kB chunks and pack the tuples densely in the chunks. This avoids the AllocChunk header overhead, and the space wasted by standard allocator's habit of rounding sizes up to the nearest power of two. This doesn't contain any planner changes, because the planner's estimate of memory usage ignores the palloc overhead. Now that the overhead is smaller, the planner's estimates are in fact more accurate. Tomas Vondra, reviewed by Robert Haas. http://git.postgresql.org/pg/commitdiff/45f6240a8fa9d35548eb2ef23dba2c11540aa02a
  • Change the way latency is calculated with pgbench --rate option. The reported latency values now include the "schedule lag" time, that is, the time between the transaction's scheduled start time and the time it actually started. This relates better to a model where requests arrive at a certain rate, and we are interested in the response time to the end user or application, rather than the response time of the database itself. Also, when --rate is used, include the schedule lag time in the log output. The --rate option is new in 9.4, so backpatch to 9.4. It seems better to make this change in 9.4, while we're still in the beta period, than ship a 9.4 version that calculates the values differently than 9.5. http://git.postgresql.org/pg/commitdiff/02e3bcc661598275a75a8649b48602dca7aeec79
  • Silence compiler warning on Windows. David Rowley. http://git.postgresql.org/pg/commitdiff/0ed41529f6ab92b836336fdd096f924838eea1d7
  • Fix Windows build. I renamed a variable, but missed an #ifdef WIN32 block. http://git.postgresql.org/pg/commitdiff/01a2bfd172b21ce86894bfa00f6896f504607ab4
  • Simplify calculation of Poisson distributed delays in pgbench --rate mode. The previous coding first generated a uniform random value between 0.0 and 1.0, then converted that to an integer between 1 and 10000, and divided that again by 10000. Those conversions are unnecessary; we can use the double value that pg_erand48() returns directly. While we're at it, put the logic into a helper function, getPoissonRand(). The largest delay generated by the old coding was about 9.2 times the average, because of the way the uniformly distributed value used for the calculation was truncated to 1/10000 granularity. The new coding doesn't have such clamping. With my laptop's DBL_MIN value, the maximum delay with the new coding is about 700x the average. That seems acceptable - any reasonable pgbench session should last long enough to average that out. Backpatch to 9.4. http://git.postgresql.org/pg/commitdiff/54a2d5b37b790cbfaf568fbbe6d2cc89650551d1
  • Remove dead InRecovery check. With the new B-tree incomplete split handling in 9.4, _bt_insert_parent is never called in recovery. http://git.postgresql.org/pg/commitdiff/aae7af3df8054d83b3080f94ca98a5698e8b3de0
  • Fix GIN data page split ratio calculation. The code that tried to split a page at 75/25 ratio, when appending to the end of an index, was buggy in two ways. First, there was a silly typo that caused it to just fill the left page as full as possible. But the logic as it was intended wasn't correct either, and would actually have given a ratio closer to 60/40 than 75/25. Gaetano Mendola spotted the typo. Backpatch to 9.4, where this code was added. http://git.postgresql.org/pg/commitdiff/774a78ffe47557313d69c2b27e7d61480a3b9d1f
  • Support Subject Alternative Names in SSL server certificates. This patch makes libpq check the server's hostname against DNS names listed in the X509 subjectAltName extension field in the server certificate. This allows the same certificate to be used for multiple domain names. If there are no SANs in the certificate, the Common Name field is used, like before this patch. If both are given, the Common Name is ignored. That is a bit surprising, but that's the behavior mandated by the relevant RFCs, and it's also what the common web browsers do. This also adds a libpq_ngettext helper macro to allow plural messages to be translated in libpq. Apparently this happened to be the first plural message in libpq, so it was not needed before. Alexey Klyukin, with some kibitzing by me. http://git.postgresql.org/pg/commitdiff/acd08d764a361dcebd346227281ff0ca62b60936
  • Fix pointer type in size passed to memset. Pointers are all the same size, so it makes no practical difference, but let's be tidy. Found by Coverity, noted off-list by Tom Lane. http://git.postgresql.org/pg/commitdiff/2df465e696f49bb12c0a362aa6f68f75a752d7a8

Bruce Momjian a poussé :

Fujii Masao a poussé :

  • Add GUC to enable logging of replication commands. Previously replication commands like IDENTIFY_COMMAND were not logged even when log_statements is set to all. Some users who want to audit all types of statements were not satisfied with this situation. To address the problem, this commit adds new GUC log_replication_commands. If it's enabled, all replication commands are logged in the server log. There are many ways to allow us to enable that logging. For example, we can extend log_statement so that replication commands are logged when it's set to all. But per discussion in the community, we reached the consensus to add separate GUC for that. Reviewed by Ian Barwick, Robert Haas and Heikki Linnakangas. http://git.postgresql.org/pg/commitdiff/4ad2a548050fdde07fed93e6c60a4d0a7eba0622

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Etsuro Fujita sent in another revision of a patch to fix some odd behavior in the PostgreSQL FDW.
  • Alvaro Herrera sent in another revision of a patch to move a piece of the ALTER TABLESPACE ... MOVE command to a place where triggers can see it.
  • Fabien COELHO and Mitsumasa KONDO traded patches to add modulo (%) to pgbench.
  • Etsuro Fujita sent in another revision of a patch to optimize writes to foreign tables in the PostgreSQL FDW.
  • Alvaro Herrera sent in two more revisions of a patch to implement BRIN (previously minmax) indexes.
  • Michael Paquier sent in another revision of a patch to refactor the pg_basebackup utilities and support replslot creation and drop in pg_receivexlog.
  • Petr (PJMODOS) Jelinek sent in another revision of a patch to allow tracking commit timestamps.
  • Kyotaro HORIGUCHI sent in another revision of a patch to imply cut off the socket if signalled during sending to client.
  • Petr (PJMODOS) Jelinek sent in a patch to add a shutdown_at_recovery_time option to recovery.conf.
  • Fujii Masao sent in another revision of a patch to add a PENDING_LIST_CLEANUP_SIZE for GIN.
  • Tomas Vondra sent in five more revisions of a patch to fix a case where bad estimation together with large work_mem generates terribly slow hash joins.
  • Amit Kapila sent in two more revisions of a patch to scale buffer eviction.
  • Pavel Stehule sent in another revision of a patch to help PL/pgsql by adding a RAISE...WHEN construct.
  • Fabien COELHO and Heikki Linnakangas traded patches for a throttling latency limit in pgbench.
  • Marko (johto) Tiikkaja sent in two more revisions of a patch to add PGP armor headers to the pgcrypto contrib extension.
  • Kyotaro HORIGUCHI sent in another revision of a patch to process pg_hba.conf keywords in a case-insensitive fashion.
  • Robert Haas sent in another revision of a patch to add parallelism infrastructure by supporting frontend-backend protocol using a shared-memomry memory queue.
  • Stephen Frost sent in two more revisions of a patch to implement row-level access control.
  • Alvaro Herrera and Thomas Munro traded patches to implement SKIP LOCKED DATA.
  • David Rowley sent in a patch to fix an MSVC isnan warning.
  • Michael Paquier and Heikki Linnakangas traded patches to fix an issue where there was a suspicious check in the GIN code.
  • Michael Paquier sent in three revisions of a patch to fix an incorrect initialization of sentPtr in walsender.c.
  • David Rowley sent in a patch to allow removing INNER JOINs preemptively if certain conditions hold.
  • Kaigai Kouhei sent in another revision of a patch to implement a custom plan API.
  • Amit Kapila sent in another revision of a patch to fix an issue with pg_basebackup on Windows.
  • Andrew (RhodiumToad) Gierth sent in another revision of a patch to implement GROUPING SETS.
  • Marko (johto) Tiikkaja sent in another revision of a patch to implement PGP signatures for the crypto contrib extension.
  • Andreas 'ads' Scherbaum sent in another revision of a patch to fix the rounding documentation.
  • Alvaro Herrera and Simon Riggs traded patches to limit the use of heap-only tuples (HOT) under several conditions.
  • Heikki Linnakangas sent in a patch to implement compression of full page writes using slice-by-4 for CRCs.
  • Fabrízio de Royes Mello sent in another revision of a patch to implement ALTER TABLE ... SET LOGGED.
  • Peter Geoghegan sent in another revision of a patch to speed up jsonb.
  • Alexander Korotkov sent in another revision of a patch to add partial sorting, which speeds up certain kinds of plans.
  • Peter Eisentraut sent in another revision of a patch to check for DocBook XML validity during the build.
  • Emre Hasegeli and Alexander Korotkov traded patches to implement KNN-GiST with recheck.

par N Bougain le lundi 15 septembre 2014 à 12h00

jeudi 11 septembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 7 septembre 2014

La cinquième édition du PgDay argentin est programmée pour le 28 novembre 2014 à Santa Fe. L'appel international à conférenciers court jusqu'au 28 septembre : http://www.postgresql.org.ar/pgday2014

Le PgDay Campinas 2014 aura lieu à Campinas le 10 septembre. Les inscriptions sont encore ouvertes : http://pgdaycampinas.com.br/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en septembre

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

Tom Lane a poussé :

Fujii Masao a poussé :

Heikki Linnakangas a poussé :

Bruce Momjian a poussé :

Robert Haas a poussé :

Peter Eisentraut a poussé :

Stephen Frost a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Kyotaro HORIGUCHI sent in a patch to make the local_preload_libraries GUC PGC_USERSET.
  • Kyotaro HORIGUCHI sent in two revisions of a patch to fix some odd behavior in the PostgreSQL FDW.
  • Michael Paquier sent in two related patches: one to refactor the IDENTIFY_SYSTEM and replslot create/drop APIs, the other to add support for --create and --drop in pg_receivexlog.
  • Peter Geoghegan sent in another revision of a patch to add INSERT ... ON CONFLICT {UPDATE | IGNORE}.
  • Alexey Klyukin sent in two more revisions of a patch to implement subject alternative names support for SSL connections.
  • Stepan Rutz and Tom Lane traded patches to help support psql better on OSX and other libedit-based platforms.
  • Petr (PJMODOS) Jelinek sent in two more revisions of a patch to add built-in binning functions.
  • Tomonari Katsumata sent in another revision of a patch to allow rounding up time value less than its unit.
  • David Johnston sent in a patch to reorganize the documentation for server configuration settings.
  • Kyotaro HORIGUCHI and Heikki Linnakangas traded patches to escape from blocked send().
  • Amit Kapila sent in two more revisions of a patch to scale shared buffer eviction.
  • Peter Geoghegan sent in another flock of patches to add B-Tree support function number 3 (strxfrm() optimization).
  • Pavel Stehule and Stephen Frost traded patches to allow row_to_json to ignore nulls.
  • Tomas Vondra sent in two more revisions of a patch to create a memory-bounded HashAgg.
  • Pavel Stehule sent in two more revisions of a patch to add --help=variables to psql.
  • Peter Eisentraut sent in a patch to fix analyze in stages.
  • Bruce Momjian sent in two revisions of a patch to display timestamp in pg_dump custom format.
  • Euler Taveira de Oliveira sent in a patch to treat unset units and empty space units as equivalent for GUCs.
  • Kaigai Kouhei sent in another revision of a patch to create a custom scan API.
  • Furuya Osamu sent in another revision of a patch to add --reply-fsync to pg_receivexlog --status-interval.
  • Marko (johto) Tiikkaja sent in another revision of a patch to add PGP signatures to the pgcrypto contrib extension.
  • Joachim Wieland sent in another revision of a patch to eliminate global variables in pg_dump.
  • Pavel Stehule sent in a WIP patch to allow EXPLAINing a running query.
  • Oskari Saarenmaa sent in a patch to optionally emit warnings when column or table aliases are used without the AS keyword.
  • Adam Brightwell sent in another revision of a patch to implement row-level access controls.
  • Marko (johto) Tiikkaja sent in a patch to allow adding a a nullable DOMAIN column with a CHECK constraint in it.
  • Peter Geoghegan sent in another revision of a patch to state the significance of Lehman & Yao in the nbtree README.
  • Emre Hasegeli sent in another revision of a patch to add selectivity estimation for inet operators.
  • Kostya Kuznetzov sent in a patch to recode vacuum in order to accommodate GiST indexes.

par N Bougain le jeudi 11 septembre 2014 à 22h17

mercredi 10 septembre 2014

Dimitri Fontaine

PHP Tour, La Video

En juin dernier se tenait le PHP Tour 2014 à Lyon, où j'ai eu le plaisir de présenter une conférence sur PostgreSQL en 2014.

Cette intervention était filmée, et la vidéo est maintenant disponible :

En espérant que vous regarderez cette vidéo avec autant d'intérêt que j'ai eu à participer à PHP Tour 2014 !

par dim@tapoueh.org (Dimitri Fontaine) le mercredi 10 septembre 2014 à 09h59

jeudi 4 septembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 31 août 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en août

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

Andres Freund a poussé :

  • Fix typos in some error messages thrown by extension scripts when fed to psql. Some of the many error messages introduced in 458857cc missed 'FROM unpackaged'. Also e016b724 and 45ffeb7e forgot to quote extension version numbers. Backpatch to 9.1, just like 458857cc which introduced the messages. Do so because the error messages thrown when the wrong command is copy & pasted aren't easy to understand. http://git.postgresql.org/pg/commitdiff/d153b801611f6fdc58c7941e85dddb0cb25ca4ef
  • Specify the port in dblink and postgres_fdw tests. That allows to run those tests against a postmaster listening on a nonstandard port without requiring to export PGPORT in postmaster's environment. This still doesn't support connecting to a nondefault host without configuring it in postmaster's environment. That's harder and less frequently used though. So this is a useful step. http://git.postgresql.org/pg/commitdiff/57ca1d4f0175f60e9cfb0f997c864173d9c72607
  • Don't hardcode contrib_regression dbname in postgres_fdw and dblink tests. That allows parallel installcheck to succeed inside contrib/. The output is not particularly pretty unless make's -O option to synchronize the output is used. There's other tests, outside contrib, that use a hardcoded, non-unique, database name. Those prohibit paralell installcheck to be used across more directories; but that's something for a separate patch. http://git.postgresql.org/pg/commitdiff/ddc2504dbcf2af97b58a91b071768c17774d58d1
  • Mark IsBinaryUpgrade as PGDLLIMPORT to fix windows builds after a7ae1dc. Author: David Rowley http://git.postgresql.org/pg/commitdiff/5569d75d6a82f0d11d26404fceb848ae59869126
  • Allow escaping of option values for options passed at connection start. This is useful to allow to set GUCs to values that include spaces; something that wasn't previously possible. The primary case motivating this is the desire to set default_transaction_isolation to 'repeatable read' on a per connection basis, but other usecases like seach_path do also exist. This introduces a slight backward incompatibility: Previously a \ in an option value would have been passed on literally, now it'll be taken as an escape. The relevant mailing list discussion starts with 20140204125823.GJ12016@awork2.anarazel.de. http://git.postgresql.org/pg/commitdiff/11a020eb6e4023a1570a2788ba22dd6aafbd02dc
  • Make backend local tracking of buffer pins memory efficient. Since the dawn of time (aka Postgres95) multiple pins of the same buffer by one backend have been optimized not to modify the shared refcount more than once. This optimization has always used a NBuffer sized array in each backend keeping track of a backend's pins. That array (PrivateRefCount) was one of the biggest per-backend memory allocations, depending on the shared_buffers setting. Besides the waste of memory it also has proven to be a performance bottleneck when assertions are enabled as we make sure that there's no remaining pins left at the end of transactions. Also, on servers with lots of memory and a correspondingly high shared_buffers setting the amount of random memory accesses can also lead to poor cpu cache efficiency. Because of these reasons a backend's buffers pins are now kept track of in a small statically sized array that overflows into a hash table when necessary. Benchmarks have shown neutral to positive performance results with considerably lower memory usage. Patch by me, review by Robert Haas. Discussion: 20140321182231.GA17111@alap3.anarazel.de http://git.postgresql.org/pg/commitdiff/4b4b680c3d6d8485155d4d4bf0a92d3a874b7a65
  • Declare two variables in snapbuild.c as static. Neither is accessed externally, I just seem to have missed the static when writing the code. http://git.postgresql.org/pg/commitdiff/8fff977e29cda7c1641c43f9b293134adc77b65a
  • Protect definition of SpinlockSemaArray, just like its declaration. Found via clang's -Wmissing-variable-declarations. http://git.postgresql.org/pg/commitdiff/5c1faa7ba729c780b0ac598dbc23dbb1d6e1b872
  • Declare lwlock.c's LWLockAcquireCommon() as a static inline. 68a2e52bbaf98f136 has introduced LWLockAcquireCommon() containing the previous contents of LWLockAcquire() plus added functionality. The latter then calls it, just like LWLockAcquireWithVar(). Because the majority of callers don't need the added functionality, declare the common code as inline. The compiler then can optimize away the unused code. Doing so is also useful when looking at profiles, to differentiate the users. Backpatch to 9.4, the first branch to contain LWLockAcquireCommon(). http://git.postgresql.org/pg/commitdiff/9c4b55db1dc901dc184b1b3ab9cca3f2089ec311

Alvaro Herrera a poussé :

Bruce Momjian a poussé :

Kevin Grittner a poussé :

Robert Haas a poussé :

Peter Eisentraut a poussé :

Stephen Frost a poussé :

  • Fix Var handling for security barrier views. In some cases, not all Vars were being correctly marked as having been modified for updatable security barrier views, which resulted in invalid plans (eg: when security barrier views were created over top of inheiritance structures). In passing, be sure to update both varattno and varonattno, as _equalVar won't consider the Vars identical otherwise. This isn't known to cause any issues with updatable security barrier views, but was noticed as missing while working on RLS and makes sense to get fixed. Back-patch to 9.4 where updatable security barrier views were introduced. http://git.postgresql.org/pg/commitdiff/e414ba93add966dfe03e22994764e718ed89ee98

Fujii Masao a poussé :

Jeff Davis a poussé :

Tom Lane a poussé :

  • Fix citext upgrade script for disallowance of oidvector element assignment. In commit 45e02e3232ac7cc5ffe36f7986159b5e0b1f6fdc, we intentionally disallowed updates on individual elements of oidvector columns. While that still seems like a sane idea in the abstract, we (I) forgot that citext's "upgrade from unpackaged" script did in fact perform exactly such updates, in order to fix the problem that citext indexes should have a collation but would not in databases dumped or upgraded from pre-9.1 installations. Even if we wanted to add casts to allow such updates, there's no practical way to do so in the back branches, so the only real alternative is to make citext's kluge even klugier. In this patch, I cast the oidvector to text, fix its contents with regexp_replace, and cast back to oidvector. (Ugh!) Since the aforementioned commit went into all active branches, we have to fix this in all branches that contain the now-broken update script. Per report from Eric Malm. http://git.postgresql.org/pg/commitdiff/7f7eec89b66947e4098773cf286653b9c4f01c88
  • Add min and max aggregates for inet/cidr data types. Haribabu Kommi, reviewed by Muhammad Asif Naeem http://git.postgresql.org/pg/commitdiff/6c40f8316ed38a92049784b3e3d3b514ed379b5a

Noah Misch a poussé :

  • Always use our getaddrinfo.c on Windows. Commit a16bac36eca8158cbf78987e95376f610095f792 let "configure" detect the system getaddrinfo() when building under 64-bit MinGW-w64. However, src/include/port/win32/sys/socket.h assumes all native Windows configurations use our replacement. This change placates buildfarm member jacana until we establish a plan for getaddrinfo() on Windows. http://git.postgresql.org/pg/commitdiff/ec544a65c9090bc9da11ea384d1369fd552ca8b0

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • SAWADA Masahiko sent in a WIP patch to add "-C (--concurrently)" option for reindexdb command for concurrently reindexing.
  • Andrew (RhodiumToad) Gierth sent in three more revisions of a patch to add GROUPING SETS and make necessary associated changes to the contrib code.
  • Andres Freund sent in a patch to add .NOTPARALLEL to contrib/Makefile.
  • Fujii Masao sent in another revision of a patch to specify the unit in storage parameters.
  • Thomas Munro sent in four more revisions of a patch to add SKIP LOCKED DATA.
  • Petr (PJMODOS) Jelinek and Tom Lane traded patches to add built-in binning functions.
  • Michael Paquier sent in another revision of a patch to ensure that pg_dump shows the schema name.
  • Alvaro Herrera sent in another revision of a patch to replicate DROP commands across servers.
  • Michael Paquier sent in another revision of a patch to implement REINDEX CONCURRENTLY.
  • Michael Paquier sent in two more revisions of a patch to add a missing comment block at the top of streamutil.h and receivelog.h.
  • David Rowley sent in another revision of a patch to support SEMI and ANTI join removal.
  • Tom Lane and Arthur Silva traded patches to fix a situation where JSONB's structure plays poorly with TOAST compression.
  • Fujii Masao sent in two more revisions of a patch to add a GUC for logging replication commands.
  • Andres Freund sent in two more revisions of a patch to make backend local tracking of buffer pins memory efficient.
  • Alvaro Herrera sent in a patch to introduce a "delay equivalent" to sort out some oddness in autovacuum cost calculations.
  • Kevin Grittner sent in two more revisions of a patch to implement delta relations, among other places, in AFTER triggers.
  • Fabien COELHO sent in six more revisions of a patch to implement a pgbench throttling latency limit.
  • Andres Freund and SAWADA Masahiko traded patches to add line number as an optional prompt in psql.
  • Joachim Wieland sent in another revision of a patch to refactor pg_dump to remove global variables.
  • Michael Paquier sent in another revision of a patch to add support for physical slot creation/deletion in pg_receivexlog.
  • Emre Hasegeli sent in three more revisions of a patch to add selectivity estimation for inet operators.
  • Michael Paquier sent in a patch to simplify calls of pg_class_aclcheck when multiple modes are used.
  • Fujii Masao and Amit Kapila traded patches to allow auditing logout.
  • Pavel Stehule and Andres Freund traded patches to give access to help_variables and usage from psql via psql command.
  • Peter Geoghegan sent in a patch to extend the INSERT statement, adding a new ON CONFLICT {UPDATE | IGNORE} clause.
  • Julien Rouhaud sent in another revision of a patch to track the number of files ready to be archived in pg_stat_archiver.
  • Kaigai Kouhei sent in another revision of a patch to implement a custom plan API.
  • Heikki Linnakangas and Andres Freund traded patches to make the bgwriter do a better job of controlling latency.
  • Michael Paquier sent in another revision of a patch to enable support for N synchronous standby servers, N>1.
  • Etsuro Fujita sent in another revision of a patch to implement INHERIT support for foreign tables.
  • Amit Kapila sent in another revision of a patch to permit scaling shared buffer eviction.
  • Kyotaro HORIGUCHI sent in another revision of a patch to fix some brokein code around socket emulation on win32.
  • Alvaro Herrera sent in another revision of a patch to create a better REINDEX syntax.
  • Etsuro Fujita sent in another revision of a patch to optimize updating foreign tables in the PostgreSQL FDW.
  • Alexey Klyukin sent in another revision of a patch to implement subject alternative names support for SSL connections.
  • Fujii Masao sent in a patch to fix an infelicity in psql's \watch vs. \timing.
  • Craig Ringer sent in a patch to allow distdir to be overridden on the "make" command line.
  • Ali Akbar sent in a patch to fix xpath() to return namespace definitions.
  • Amit Kapila sent in another revision of a patch to implement ALTER SYSTEM RESET.
  • Michael Paquier sent in two more revisions of a patch to fix a misleading error message in logical decoding for binary plugins.
  • Adam Brightwell sent in another revision of a patch to provide the capability to create multiple named row level security policies for a table on a per command basis and assign them to be applied to specific roles/users.
  • Ali Akbar sent in another revision of a patch to accept empty values as xml "content" values.
  • Peter Eisentraut sent in another revision of a patch to improve the speed of "make check-world".

par N Bougain le jeudi 4 septembre 2014 à 23h33

jeudi 28 août 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 24 août 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en août

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

  • Fix obsolete mention of non-int64 support in CREATE SEQUENCE documentation. The old text explained what happened if we didn't have working int64 arithmetic. Since that case has been explicitly rejected by configure since 8.4.3, documenting it in the 9.x branches can only produce confusion. http://git.postgresql.org/pg/commitdiff/2b475c5946bc8a9beaff3f57b45cc440a78561a1
  • Fix core dump in jsonb #> operator, and add regression test cases. jsonb's #> operator segfaulted (dereferencing a null pointer) if the RHS was a zero-length array, as reported in bug #11207 from Justin Van Winkle. json's #> operator returns NULL in such cases, so for the moment let's make jsonb act likewise. Also add a bunch of regression test queries memorializing the -> and #> operators' behavior for this and other corner cases. There is a good argument for changing some of these behaviors, as they are not very consistent with each other, and throwing an error isn't necessarily a desirable behavior for operators that are likely to be used in indexes. However, everybody can agree that a core dump is the Wrong Thing, and we need test cases even if we decide to change their expected output later. http://git.postgresql.org/pg/commitdiff/9bac66020db75871463bafdb394568bf946e8991
  • More regression test cases for json/jsonb extraction operators. Cover some cases I omitted before, such as null and empty-string elements in the path array. This exposes another inconsistency: json_extract_path complains about empty path elements but jsonb_extract_path does not. http://git.postgresql.org/pg/commitdiff/fa069822f5a02bbbe46bada0b6e420a89c5f7484
  • Fix corner-case behaviors in JSON/JSONB field extraction operators. Cause the path extraction operators to return their lefthand input, not NULL, if the path array has no elements. This seems more consistent since the case ought to correspond to applying the simple extraction operator (->) zero times. Cause other corner cases in field/element/path extraction to return NULL rather than failing. This behavior is arguably more useful than throwing an error, since it allows an expression index using these operators to be built even when not all values in the column are suitable for the extraction being indexed. Moreover, we already had multiple inconsistencies between the path extraction operators and the simple extraction operators, as well as inconsistencies between the JSON and JSONB code paths. Adopt a uniform rule of returning NULL rather than throwing an error when the JSON input does not have a structure that permits the request to be satisfied. Back-patch to 9.4. Update the release notes to list this as a behavior change since 9.3. http://git.postgresql.org/pg/commitdiff/41dd50e84df39e31595f3472b0cb6d00f63b3f99
  • Fix another ancient memory-leak bug in relcache.c. CheckConstraintFetch() leaked a cstring in the caller's context for each CHECK constraint expression it copied into the relcache. Ordinarily that isn't problematic, but it can be during CLOBBER_CACHE testing because so many reloads can happen during a single query; so complicate the code slightly to allow freeing the cstring after use. Per testing on buildfarm member barnacle. This is exactly like the leak fixed in AttrDefaultFetch() by commit 078b2ed291c758e7125d72c3a235f128d40a232b. (Yes, this time I did look for other instances of the same coding pattern :-(.) Like that patch, no back-patch, since it seems unlikely that there's any problem except under very artificial test conditions. BTW, it strikes me that both of these places would require further work comparable to commit ab8c84db2f7af008151b848cf1d6a4672a39eecd, if we ever supported defaults or check constraints on system catalogs: they both assume they are copying into an empty relcache data structure, and that conceivably wouldn't be the case during recursive reloading of a system catalog. This does not seem worth worrying about for the moment, since there is no near-term prospect of supporting any such thing. So I'll just note the possibility for the archives' sake. http://git.postgresql.org/pg/commitdiff/73eba19aebe0101837668e39267469ca34373552

Heikki Linnakangas a poussé :

  • Reorganize functions in be-secure-openssl.c. Move the functions within the file so that public interface functions come first, followed by internal functions. Previously, be_tls_write was first, then internal stuff, and finally the rest of the public interface, which clearly didn't make much sense. Per Andres Freund's complaint. http://git.postgresql.org/pg/commitdiff/48d50840d53eb62842c0d9b54eab9cd7c9a3a46d
  • Use comma+space as the separator in the default search_path. While the space is optional, it seems nicer to be consistent with what you get if you do "SET search_path=...". SET always normalizes the separator to be comma+space. Christoph Martin http://git.postgresql.org/pg/commitdiff/02587dcddc2cb5d9f7a17cb0281d168f5a0d6297
  • Add #define INT64_MODIFIER for the printf length modifier for 64-bit ints. We have had INT64_FORMAT and UINT64_FORMAT for a long time, but that's not good enough if you want something more exotic, like "%20lld". Abhijit Menon-Sen, per Andres Freund's suggestion. http://git.postgresql.org/pg/commitdiff/ce486056ecd28050f367894a2b5aad3656d37511
  • Change the way pg_basebackup's tablespace mapping is implemented. Previously, we would first create the symlinks the way they are in the original system, and at the end replace them with the mapped symlinks. That never really made much sense, so now we create the symlink pointing to the correct location to begin with, so that there's no need to fix them at the end. The old coding didn't work correctly on Windows, because Windows junction points look more like directories than files, and ought to be removed with rmdir rather than unlink. Also, it incorrectly used "%d" rather than "%u" to print an Oid, but that's gone now. Report and patch by Amit Kapila, with minor changes by me. Reviewed by MauMau. Backpatch to 9.4, where the --tablespace feature was added. http://git.postgresql.org/pg/commitdiff/8d938eb0797e7acd925a2adf72315f8ab1586d83
  • Fix comment in pg_basebackup. The option is called --tablespace-mapping, not --tablespace. Amit Kapila http://git.postgresql.org/pg/commitdiff/2af93640ad14028297fbc750e4c2338ad11a3c58

Gregory Stark a poussé :

Noah Misch a poussé :

Fujii Masao a poussé :

  • Fix bug in checking of IDENTIFY_SYSTEM result. 5a991ef8692ed0d170b44958a81a6bd70e90585 added new column into the result of IDENTIFY_SYSTEM command. But it was not reflected into several codes checking that result. Specifically though the number of columns in the result was increased to 4, it was still compared with 3 in some replication codes. Back-patch to 9.4 where the number of columns in IDENTIFY_SYSTEM result was increased. Report from Michael Paquier http://git.postgresql.org/pg/commitdiff/083d29c65b7897f90c70e6dc0a4240a5fa75c8f2
  • Revert "Fix bug in checking of IDENTIFY_SYSTEM result." This reverts commit 083d29c65b7897f90c70e6dc0a4240a5fa75c8f2. The commit changed the code so that it causes an errors when IDENTIFY_SYSTEM returns three columns. But which prevents us from using the replication-related utilities against the server with older version. This is not what we want. For that compatibility, we allow the utilities to receive three columns as the result of IDENTIFY_SYSTEM eventhough it actually returns four columns in 9.4 or later. Pointed out by Andres Freund. http://git.postgresql.org/pg/commitdiff/c4762886539b06da682045e19ba73dcee3789638

Bruce Momjian a poussé :

Stephen Frost a poussé :

  • Rework 'MOVE ALL' to 'ALTER .. ALL IN TABLESPACE'. As 'ALTER TABLESPACE .. MOVE ALL' really didn't change the tablespace but instead changed objects inside tablespaces, it made sense to rework the syntax and supporting functions to operate under the 'ALTER (TABLE|INDEX|MATERIALIZED VIEW)' syntax and to be in tablecmds.c. Pointed out by Alvaro, who also suggested the new syntax. Back-patch to 9.4. http://git.postgresql.org/pg/commitdiff/3c4cf080879b386d4ed1814667aca025caafe608

Andres Freund a poussé :

Peter Eisentraut a poussé :

Alvaro Herrera a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Anastasia Lubennikova and Heikki Linnakangas traded patches to enable index-only scans for GiST.
  • Fujii Masao and Michael Paquier traded patches to fix an issue with pg_receivexlog and replication slots.
  • Adam Brightwell sent in another revision of a patch to implement row-level access control.
  • Heikki Linnakangas sent in another revision of a patch to do WAL format and API changes.
  • Adam Brightwell sent in a PoC patch to create a new model around role attributes and fine grained permissions meant to alleviate the current over-dependence on superuser.
  • Furuya Osamu sent in two more revisions of a patch to add fsync feedback to pg_receivexlog --status-interval.
  • David Rowley sent in another revision of a patch to implement partial sorting.
  • Rahila Syed sent in another revision of a patch to compress full-page writes.
  • Tomas Vondra sent in another revision of a patch to fix an issue where bad estimation together with large work_mem generates terribly slow hash joins.
  • Vladislav Sterzhanov sent in a patch to enable KNN searches for SP-GiST.
  • Michael Paquier and Fabrízio de Royes Mello traded patches to ensure that the verbose output of pg_dump show the schema name.
  • Jeff Davis and Tomas Vondra traded patches to do better memory accounting for hash aggregates.
  • John Lumby sent in a patch to extend prefetching using asynchronous I/O.
  • Amit Kapila sent in another revision of a patch to fix pg_basebackup with respect to tablespaces on Windows.
  • Alvaro Herrera sent in two more revisions of a patch to implement minmax indexes.
  • Andreas 'ads' Scherbaum sent in a patch to explains that functions like round() and others might behave differently depending on operating system, and that this is correct according to an IEEE standard.
  • Michael Paquier sent in three more revisions of a patch to support N synchronous standby servers for N>1.
  • Fabrízio de Royes Mello, Alvaro Herrera, and Michael Paquier traded patches to implement ALTER TABLE ... SET LOGGED.
  • Tom Lane sent in a patch to make JSONB more TOAST-compressible.
  • Julien Rouhaud sent in a patch to track number of files ready to be archived in pg_stat_archiver.
  • Bruce Momjian sent in a patch to update recommendations around pg_dump to reflect current capabilities.
  • Bruce Momjian sent in a patch to harden pg_upgrade.
  • Amit Kapila sent in two more revisions of a patch to fix an issue where the -T option of pg_basebackup didn't work on Windows.
  • Bruce Momjian sent in a patch to allow multiple -o/-O options in pg_upgrade.
  • Kevin Grittner sent in another revision of a patch to enable delta relations in AFTER triggers.
  • Peter Geoghegan sent in another revision of a patch to add a b-tree sort support optimization.
  • Pavel Stehule sent in another revision of a patch to revamp row_to_json.
  • Michael Paquier sent in another revision of a patch to remove the dependency on wsock32.lib when compiling code on Windows.
  • Jov and Tom Lane traded patches to change alter user to be a true alias for alter role.
  • Tomonari Katsumata sent in a patch to allow rounding up time value less than its unit.
  • Alvaro Herrera and Thomas Munro traded patches to add SKIP LOCKED DATA.
  • Haribabu Kommi sent in another revision of a patch to enable inet types to support min/max.
  • Michael Paquier sent in a patch to add a missing comment block at the top of streamutil.h and receivelog.h.
  • Fabien COELHO sent in another revision of a patch to create a pgbench throttling latency limit.
  • Alexey Klyukin sent in another revision of a patch to implement subject alternative names support for SSL connections.
  • Amit Kapila sent in another revision of a patch to add ALTER SYSTEM RESET.

par N Bougain le jeudi 28 août 2014 à 12h25

Nouvelles hebdomadaires de PostgreSQL - 17 août 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en août

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

  • Fix documentation oversights about pageinspect and initialization fork. The initialization fork was added in 9.1, but has not been taken into consideration in documents of get_raw_page function in pageinspect and storage layout. This commit fixes those oversights. get_raw_page can read not only a table but also an index, etc. So it should be documented that the function can read any relation. This commit also fixes the document of pageinspect that way. Back-patch to 9.1 where those oversights existed. Vik Fearing, review by MauMau http://git.postgresql.org/pg/commitdiff/7d835ff4db19c515171d6c8de772536c55600753
  • Add tab-completion for \unset and valid setting values of psql variables. This commit also changes tab-completion for \set so that it displays all the special variables like COMP_KEYWORD_CASE. Previously it displayed only variables having the set values. Which was not user-friendly for those who want to set the unset variables. This commit also changes tab-completion for :variable so that only the variables having the set values are displayed. Previously even unset variables were displayed. Pavel Stehule, modified by me. http://git.postgresql.org/pg/commitdiff/e15c4ab5fb0de2cab393ed4be2136e1832746412
  • Change first call of ProcessConfigFile so as to process only data_directory. When both postgresql.conf and postgresql.auto.conf have their own entry of the same parameter, PostgreSQL uses the entry in postgresql.auto.conf because it appears last in the configuration scan. IOW, the other entries which appear earlier are ignored. But, previously, ProcessConfigFile() detected the invalid settings of even those unused entries and emitted the error messages complaining about them, at postmaster startup. Complaining about the entries to ignore is basically useless. This problem happened because ProcessConfigFile() was called twice at postmaster startup and the first call read only postgresql.conf. That is, the first call could check the entry which might be ignored eventually by the second call which read both postgresql.conf and postgresql.auto.conf. To work around the problem, this commit changes ProcessConfigFile so that its first call processes only data_directory and the second one does all the entries. It's OK to process data_directory in the first call because it's ensured that data_directory doesn't exist in postgresql.auto.conf. Back-patch to 9.4 where postgresql.auto.conf was added. Patch by me. Review by Amit Kapila http://git.postgresql.org/pg/commitdiff/3e3f65973a3c94a699a1659254b89c4f77f0a6a7
  • Fix typo in \setrandom document. Fabien COELHO http://git.postgresql.org/pg/commitdiff/d6ded369a7793c926661f492c153a6dd05afc5bc
  • Expose -S option in pg_receivexlog. This option is equivalent to --slot option which pg_receivexlog has already supported, which specifies the replication slot to use for WAL streaming. pg_recvlogical has already supported both options, and this commit makes pg_receivexlog consistent with pg_recvlogical regarding the slot option. Back-patch to 9.4 where the slot option was added. Michael Paquier http://git.postgresql.org/pg/commitdiff/52bffe3499d8e5f61efa385ab3c6a074314ee5db
  • Fix help message in pg_ctl. Previously the help message described that -m is an option for "stop", "restart" and "promote" commands in pg_ctl. But actually that's not an option for "promote". So this commit fixes that incorrect description in the help message. Back-patch to 9.3 where the incorrect description was added. http://git.postgresql.org/pg/commitdiff/a4287a689d10bd4863e3dfbf9c4f232feeca0cdd
  • Add missing index terms for replication commands in the document. Previously only CREATE_REPLICATION_SLOT was exposed as an index term. That's odd and there is no reason not to add index terms for other replication commands. Back-patch to 9.4. http://git.postgresql.org/pg/commitdiff/3e9f70f1287b9324e398a084a8e881ea6aefc3eb

Tatsuo Ishii a poussé :

Andres Freund a poussé :

  • Be less aggressive in asking for feedback of logical walsender clients. When doing logical decoding using START_LOGICAL_REPLICATION in a walsender process the walsender sometimes was sending out keepalive messages too frequently. Asking for feedback every time. WalSndWaitForWal() sends out keepalive messages when it's waiting for new WAL to be generated locally when it sees that the remote side hasn't yet flushed WAL up to the local position. That generally is good but causes problems if the remote side only writes but doesn't flush changes yet. So check for both remote write and flush position. Additionally we've asked for feedback to the keepalive message which isn't warranted when waiting for WAL in contrast to preventing timeouts because of wal_sender_timeout. Complaint and patch by Steve Singer. http://git.postgresql.org/pg/commitdiff/41d5f8ad734f28ca425636d5ac4c8ea9c29002a5
  • pg_recvlogical message and code improvements. Some error messages complained about --init and --stop being used whereas the --create and --drop are the correct verbs. Fix that. Also a XLogRecPtr was tested in a boolean fashion instead of being compared to InvalidXLogRecPtr. Backpatch to 9.4 where pg_recvlogical was introduced. Michael Paquier http://git.postgresql.org/pg/commitdiff/596385be06e1c8ac7291958a556b10b37ee333d5

Tom Lane a poussé :

  • Prevent memory leaks in parseRelOptions(). parseRelOptions() tended to leak memory in the caller's context. Most of the time this doesn't really matter since the caller's context is at most query-lifespan, and the function won't be invoked very many times. However, when testing with CLOBBER_CACHE_RECURSIVELY, the same relcache entry can get rebuilt a *lot* of times in one query, leading to significant intraquery memory bloat if it has any reloptions. Noted while investigating a related report from Tomas Vondra. In passing, get rid of some Asserts that are redundant with the one done by deconstruct_array(). As with other patches to avoid leaks in CLOBBER_CACHE testing, it doesn't really seem worth back-patching this. http://git.postgresql.org/pg/commitdiff/a844c29966d7c0cd6a457e9324f175349bb12df0
  • Prevent memory leaks in RelationGetIndexList, RelationGetIndexAttrBitmap. When replacing rd_indexlist, rd_indexattr, etc, we neglected to pfree any old value of these fields. Under ordinary circumstances, the old value would always be NULL, so this seemed reasonable enough. However, in cases where we're rebuilding a system catalog's relcache entry and another cache flush occurs on that same catalog meanwhile, it's possible for the field to not be NULL when we return to the outer level, because we already refilled it while recovering from the inner flush. This leads to a fairly small session-lifespan leak in CacheMemoryContext. In real-world usage the leak would be too small to notice; but in testing with CLOBBER_CACHE_RECURSIVELY the leakage can add up to the point of causing OOM failures, as reported by Tomas Vondra. The issue has been there a long time, but it only seems worth fixing in HEAD, like the previous fix in this area (commit 078b2ed291c758e7). http://git.postgresql.org/pg/commitdiff/ab8c84db2f7af008151b848cf1d6a4672a39eecd
  • Update SysV parameter configuration documentation for FreeBSD. FreeBSD hasn't made any use of kern.ipc.semmap since 1.1, and newer releases reject attempts to set it altogether; so stop recommending that it be adjusted. Per bug #11161. Back-patch to all supported branches. Before 9.3, also incorporate commit 7a42dff47, which touches the same text and for some reason was not back-patched at the time. http://git.postgresql.org/pg/commitdiff/5ff5bfb5f0d83a538766903275b230499fa9ebe1
  • Fix bogus commutator/negator links for JSONB containment operators. <@ and @> are each other's commutators, but they were incorrectly marked as being each other's negators instead. (This was actually questioned in a comment in the original commit, but nobody followed through :-(.) Per bug #11178 from Christian Pronovost. In passing, fix some JSONB operator descriptions that were randomly different from the phrasing of every other similar description. catversion bump for pg_catalog contents change. http://git.postgresql.org/pg/commitdiff/e3f9c16838358b6b3679dd014455d310919d7efa
  • Add opr_sanity queries to inspect commutator/negator links more closely. Make lists of the names of all operators that are claimed to be commutator pairs or negator pairs. This is analogous to the existing queries that make lists of all operator names appearing in particular opclass strategy slots. Unexpected additions to these lists are likely to be mistakes; had we had these queries in place before, bug #11178 might've been prevented. http://git.postgresql.org/pg/commitdiff/a068b5b65f83660fc99c3faf39ed50493b010fbb
  • Fix bogus return macros in range_overright_internal(). PG_RETURN_BOOL() should only be used in functions following the V1 SQL function API. This coding accidentally fails to fail since letting the compiler coerce the Datum representation of bool back to plain bool does give the right answer; but that doesn't make it a good idea. Back-patch to older branches just to avoid unnecessary code divergence. http://git.postgresql.org/pg/commitdiff/737cdc2d1412f3e4115137ca6fa14d1b3c39dcbc
  • Improve DISCARD documentation. The new DISCARD SEQUENCES option was inadequately described, and hadn't been mentioned at all in the initial Description paragraph. Rather than rectifying the latter the hard way, it seemed better to rewrite the description as a summary, instead of having it basically duplicate statements made under Parameters. Be more consistent about the ordering of the options, too. http://git.postgresql.org/pg/commitdiff/259904ecd42a46f5bb66bb85d547eba5b64a2cfa
  • Make an editorial pass over the 9.4 release notes. Update the notes to include commits through today, and do a lot of wordsmithing and markup adjustment. Notably, don't use <link> where <xref> will do; since we got rid of the text-format HISTORY file, there is no longer a reason to avoid <xref>. http://git.postgresql.org/pg/commitdiff/f66faadc4420fdc34f66057f493a0e09c0ad8a58
  • Use ISO 8601 format for dates converted to JSON, too. Commit f30015b6d794c15d52abbb3df3a65081fbefb1ed made this happen for timestamp and timestamptz, but it seems pretty inconsistent to not do it for simple dates as well. (In passing, I re-pgindent'd json.c.) http://git.postgresql.org/pg/commitdiff/e56ec50c1622010c010e1c84bc02ce44285c4f41

Peter Eisentraut a poussé :

Robert Haas a poussé :

Heikki Linnakangas a poussé :

  • Doc fix: New York State's capital is Albany. The inheritance example in the manual implies that the capital of New York is New York City, but in reality it's Albany. George Hartzell http://git.postgresql.org/pg/commitdiff/ef153ecc55a8f945d576a1e69a51b91046f24bb1
  • Remove remnants of a JENTRY_ISFIRST flag bit. I removed the flag earlier, but missed a few references in jsonb.h. http://git.postgresql.org/pg/commitdiff/c07693f0c7cde7d269025fe714a124223a189e71
  • Break out OpenSSL-specific code to separate files. This refactoring is in preparation for adding support for other SSL implementations, with no user-visible effects. There are now two #defines, USE_OPENSSL which is defined when building with OpenSSL, and USE_SSL which is defined when building with any SSL implementation. Currently, OpenSSL is the only implementation so the two #defines go together, but USE_SSL is supposed to be used for implementation-independent code. The libpq SSL code is changed to use a custom BIO, which does all the raw I/O, like we've been doing in the backend for a long time. That makes it possible to use MSG_NOSIGNAL to block SIGPIPE when using SSL, which avoids a couple of syscall for each send(). Probably doesn't make much performance difference in practice - the SSL encryption is expensive enough to mask the effect - but it was a natural result of this refactoring. Based on a patch by Martijn van Oosterhout from 2006. Briefly reviewed by Alvaro Herrera, Andreas Karlsson, Jeff Janes. http://git.postgresql.org/pg/commitdiff/680513ab79c7e12e402a2aad7921b95a25a4bcc8

Bruce Momjian a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Michael Paquier sent in three more revisions of a patch to support N synchronous standby servers, N>1.
  • Fujii Masao sent in another revision of a patch to allow showing only failed queries in psql.
  • Fujii Masao sent in another revision of a patch to ignore unused GUCs at startup.
  • Michael Paquier sent in two more revisions of a patch to improve versioning on Windows.
  • Michael Paquier sent in another revision of a patch to remove the dependency on wsock32.lib when compiling code on Windows.
  • Michael Paquier sent in a patch to fix the tab padding for the new option -F in the getops switch.
  • Haribabu Kommi sent in another revision of a patch to fix the fact that inet types don't support min/max.
  • Heikki Linnakangas sent in another revision of a patch to add an SSL regression test suite.
  • Fabien COELHO sent in a patch to fix some issues in a previous patch which makes PL/PgSQL throw an error during compilation (instead of runtime) if the number of parameters passed to RAISE don't match the number of placeholders in error message.
  • Sean Chittenden sent in a patch to fix a potential performance regression due to mmap(2) on FreeBSD.
  • Fujii Masao sent in another revision of a patch to add pg_last_xact_insert_timestamp.
  • Heikki Linnakangas sent in another revision of a patch to make WAL format and API changes.
  • Heikki Linnakangas sent in another revision of a patch to support Windows SChannel as an OpenSSL replacement.
  • Furuya Osamu sent in two revisions of a patch to add a setting to send status packets after fsync to --status-interval of pg_receivexlog.
  • Atri Sharma sent in a WIP patch to enable GROUPING SETS.
  • Heikki Linnakangas sent in two revisions of a patch to refactor heapam.c redo routines to use XLogReplayBuffer.
  • Etsuro Fujita sent in another revision of a patch to compute attr_needed for child relations.
  • Dilip Kumar sent in another revision of a patch to allow parallel cores to be used by vacuumdb.
  • Fujii Masao sent in a patch to enable WAL archiving even on a standby.
  • MauMau sent in another revision of a patch to implement pg_copy, a command for reliable WAL archiving.
  • David Rowley sent in a patch to change some security-related uses of strncpy to strlcpy.
  • Tom Lane an Heikki Linnakangas traded patches to fix an issue where TOAST fails to compress JSONB fields.
  • Keith Baker sent in two more revisions of a patch to implement the locking needed for a QNX 6.5 port.
  • SAWADA Masahiko sent in another revision of a patch to add line number as a prompt option to psql.
  • Fabien COELHO sent in a patch to add a throttling latency limit to pgbench.
  • Fujii Masao sent in a patch to change \watch so that it displays how long the query takes if \timing is enabled.
  • Fujii Masao sent in a patch to allow immediate standby promotion.
  • Rukh Meski sent in another revision of a patch to add LIMIT for UPDATE and DELETE.
  • Kyotaro HORIGUCHI sent in a patch to add a new GUC category PGC_BACKEND_USERSET and change local_preload_libraries to use it.
  • Peter Eisentraut sent in a patch to improve the speed of make check-world by having it only create one temporary installation, where before it made a temporary installation for every subdirectory.
  • Alvaro Herrera and Heikki Linnakangas traded patches to implement minmax indexes.
  • Fujii Masao sent in a patch to expose replication commands as index terms.
  • Amit Kapila and Michael Paquier traded patches to fix an issue where the option -T in pg_basebackup doesn't work on Windows.
  • Marko (johto) Tiikkaja sent in another revision of a patch to add PGP signatures to the pgcrypto extension.
  • Marko (johto) Tiikkaja sent in another revision of a patch to add PGP armor headers to the pgcrypto extension.
  • Fujii Masao sent in a patch to add a memory barrier to pgstat.
  • Kaigai Kouhei sent in another revision of a patch to implement the custom plan API.
  • Pavel Stehule sent in a patch to fix an issue with pg_dump where the existence of a large object (lo) could cause an unrestorable dump.
  • Joachim Wieland sent in a patch to refactor pg_dump to remove global variables. In addition to being a good in itself, this is in preparation for a parallel analog of "pg_dump | psql".
  • Noah Misch sent in a patch to fix the fact that the sample LDIF for pg_service.conf no longer works.
  • Fabien COELHO sent in a patch to add a --tuple-size option to pgbench.
  • 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.
  • David Rowley sent in another revision of a patch to support SEMI and ANTI join removal.
  • Rahila Syed sent in another revision of a patch to support various types of compression of full-page writes.
  • Fabrízio de Royes Mello sent in another revision of a patch to enable ALTER TABLE ... SET LOGGED.
  • Stephen Frost sent in two more revisions of a patch to tweak the command tags in ALTER TABLESPACE MOVE.
  • Fujii Masao sent in another revision of a patch to add PENDING_LIST_CLEANUP_SIZE.
  • Marco Nenciarini sent in a patch to add backup profile to base backup.
  • Michael Paquier sent in another revision of a patch to add support for physical slot creation/deletion in pg_receivexlog.

par N Bougain le jeudi 28 août 2014 à 12h10

Nouvelles hebdomadaires de PostgreSQL - 10 août 2014

Le PGDay Campinas 2014 aura lieu à Campinas (Brésil) le 10 septembre. Inscriptions ouvertes et candidatures de conférences-éclair attendues : http://pgdaycampinas.com.br/

Offres d'emplois autour de PostgreSQL en août

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

Bruce Momjian a poussé :

Robert Haas a poussé :

Kevin Grittner a poussé :

Fujii Masao a poussé :

  • Change ParseConfigFp() so that it doesn't process unused entry of each parameter. When more than one setting entries of same parameter exist in the configuration file, PostgreSQL uses only entry appearing last in configuration file scan. Since the other entries are not used, ParseConfigFp() doesn't need to process them, but previously it did that. This problematic behavior caused the configuration file scan to detect invalid settings of unused entries (e.g., existence of multiple entries of PGC_POSTMASTER parameter) and log the messages complaining about them. This commit changes the configuration file scan so that it processes only last entry of each parameter. Note that when multiple entries of same parameter exist both in postgresql.conf and postgresql.auto.conf, unused entries in postgresql.conf are still processed only at postmaster startup. The problem has existed since old version, but a user is more likely to encounter it since 9.4 where ALTER SYSTEM command was introduced. So back-patch to 9.4. Amit Kapila, slightly modified by me. Per report from Christoph Berg. http://git.postgresql.org/pg/commitdiff/e3da0d4d1a47e2049ae16aa30bb7bf3105dc8c45
  • Refactor pg_receivexlog main loop code, for readability, take 2. Previously the source codes for processing the received data and handling the end of stream were included in pg_receivexlog main loop. This commit splits out them as separate functions. This is useful for improving the readability of main loop code and making the future pg_receivexlog-related patch simpler. http://git.postgresql.org/pg/commitdiff/6805e02c66eac3857ef1a3d6cfd1ffeea64d9447
  • Add -F option to pg_receivexlog, for specifying fsync interval. This allows us to specify the maximum time to issue fsync to ensure the received WAL file is safely flushed to disk. Without this, pg_receivexlog always flushes WAL file only when it's closed and which can cause WAL data to be lost at the event of a crash. Furuya Osamu, heavily modified by me. http://git.postgresql.org/pg/commitdiff/3dad73e71f08abd86564d5090a58ca71740e07e0

Tom Lane a poussé :

  • Fix typo in docs. s/XIDs XIDs/XIDs/ in one place in maintenance.sgml. Guillaume Lelarge http://git.postgresql.org/pg/commitdiff/1add956ac70384a99ed6606e2963a5a53f567add
  • Reject duplicate column names in foreign key referenced-columns lists. Such cases are disallowed by the SQL spec, and even if we wanted to allow them, the semantics seem ambiguous: how should the FK columns be matched up with the columns of a unique index? (The matching could be significant in the presence of opclasses with different notions of equality, so this issue isn't just academic.) However, our code did not previously reject such cases, but instead would either fail to match to any unique index, or generate a bizarre opclass-lookup error because of sloppy thinking in the index-matching code. David Rowley http://git.postgresql.org/pg/commitdiff/9da86753735ab89b0ee685aea985b25c4218ca0b
  • Further cleanup of JSON-specific error messages. Fix an obvious typo in json_build_object()'s complaint about invalid number of arguments, and make the errhint a bit more sensible too. Per discussion about how to word the improved hint, change the few places in the documentation that refer to JSON object field names as "names" to say "keys" instead, since that's what we've said in the vast majority of places in the docs. Arguably "name" is more correct, since that's the terminology used in RFC 7159; but we're stuck with "key" in view of the naming of json_object_keys() so let's at least be self-consistent. I adjusted a few code comments to match this as well, and failed to resist the temptation to clean up some odd whitespace choices in the same area, as well as a useless duplicate PG_ARGISNULL() check. There's still quite a bit of code that uses the phrase "field name" in non-user- visible ways, so I left those usages alone. http://git.postgresql.org/pg/commitdiff/495cadda5ed55f63db9a91d174b6fad0e1816204
  • Clean up handling of unknown-type inputs in json_build_object and friends. There's actually no need for any special case for unknown-type literals, since we only need to push the value through its output function and unknownout() works fine. The code that was here was completely bizarre anyway, and would fail outright in cases that should work, not to mention suffering from some copy-and-paste bugs. http://git.postgresql.org/pg/commitdiff/92f57c9ae95668636bc77d3cda3edbdfd83320d9
  • Clarify type resolution behavior for domain types. The user documentation was vague and not entirely accurate about how we treat domain inputs for ambiguous operators/functions. Clarify that, and add an example and some commentary. Per a recent question from Adam Mackler. It's acted like this ever since we added domains, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/6aa61580e08d58909b2a8845a4087b7699335ee0

Peter Eisentraut a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Dilip Kumar sent in another revision of a patch to allow parallel cores to be used by vacuumdb.
  • Etsuro Fujita sent in another revision of a patch to optimize writes to foreign tables using the PostgreSQL FDW.
  • Alexander Korotkov sent in another revision of a patch to add KNN-GiST with recheck.
  • Heikki Linnakangas sent in a patch to create an SSL regression test suite.
  • Fabien COELHO sent in two more revisions of a patch to add modulo to pgbench.
  • Jeff Davis sent in two more revisions of a patch to implement better memory accounting, infrastructure for a memory-bounded HashAgg.
  • Fujii Masao sent in a patch to add PGC_SU_BACKEND.
  • Jeff Janes sent in a patch to fix one of the regression tests changed by another patch which fixed redundant i18n strings in json.
  • Fujii Masao sent in two more revisions of a patch to add a synchrounous mode to pg_receivexlog.
  • Robert Haas and Peter Geoghegan traded patches to add an optional comparator for B-Tree sort support.
  • David Rowley sent in two more revisions of a patch to allow certain SEMI and ANTI join removals.
  • Alvaro Herrera and Heikki Linnakangas traded patches to implement minmax indexes.
  • Marko (johto) Tiikkaja sent in another revision of a patch to add plpgsql.extra_warnings='num_into_expressions'.
  • Marko (johto) Tiikkaja sent in two revisions of a patch to enable PGP signatures in the pgcrypto extenstion.
  • Tatsuo Ishii and Fabien COELHO started patching pgbench to ensure that its parameters are checked better.
  • Etsuro Fujita sent in two revisions of a patch to compute attr_needed for child rels. As there were already two different places this functionality was used, this patch factored out the functionality in order to add a third use case, namely allowing foreign tables to be in table inheritance hierarchies.
  • Heikki Linnakangas sent in another revision of a patch to Invent a new internal API for interfacing with SSLv3.
  • Fujii Masao sent in a patch to enable specifying the unit in storage parameters.
  • Pavel Stehule and Fujii Masao traded patches to allow tab completion for \pset variables in psql.
  • Marko (johto) Tiikkaja sent in a patch to add PGP armor headers in the pgcrypto extension.
  • MauMau sent in another revision of a patch to fix an unconditional truncation that should have been a conditional truncation for temporary relations.
  • Fujii Masao sent in a patch to add a new GIN index storage parameter "PENDING_LIST_CLEANUP_SIZE" which specifies the maximum size of GIN pending list.
  • Michael Paquier sent in a patch to add support for N synchronous standby servers for N > 1.
  • Jeff Davis sent in a patch to enable memory-bounded HashAgg.

par N Bougain le jeudi 28 août 2014 à 11h59

mardi 5 août 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 3 août 2014

Le PGDay.IT 2014 aura lieu à Prato le 7 novembre 2014. La date limite pour le dépôt de candidature des conférenciers a été repoussée au 9 août : http://2014.pgday.it/call-for-papers-en/

Offres d'emplois autour de PostgreSQL en août

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

  • Fix obsolete statement in smgr/README. Since commit 2d00190495b22e0d0ba351b2cda9c95fb2e3d083, fork numbers are defined in relpath.h not relfilenode.h. Fabrízio de Royes Mello http://git.postgresql.org/pg/commitdiff/248fc1f10716964b7e7d0f8ada686801d1e36445
  • Avoid wholesale autovacuuming when autovacuum is nominally off. When autovacuum is nominally off, we will still launch autovac workers to vacuum tables that are at risk of XID wraparound. But after we'd done that, an autovac worker would proceed to autovacuum every table in the targeted database, if they meet the usual thresholds for autovacuuming. This is at best pretty unexpected; at worst it delays response to the wraparound threat. Fix it so that if autovacuum is nominally off, we *only* do forced vacuums and not any other work. Per gripe from Andrey Zhidenkov. This has been like this all along, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/f51ead09df19f0a074c07657b7de8dba0f3050d6

Fujii Masao a poussé :

Heikki Linnakangas a poussé :

  • Oops, fix recoveryStopsBefore functions for regular commits. Pointed out by Tom Lane. Backpatch to 9.4, the code was structured differently in earlier branches and didn't have this mistake. http://git.postgresql.org/pg/commitdiff/60d931827b0c37fbce74d04e45d0220d57ddd06a
  • Treat 2PC commit/abort the same as regular xacts in recovery. There were several oversights in recovery code where COMMIT/ABORT PREPARED records were ignored: pg_last_xact_replay_timestamp() (wasn't updated for 2PC commits), recovery_min_apply_delay (2PC commits were applied immediately), recovery_target_xid (recovery would not stop if the XID used 2PC) The first of those was reported by Sergiy Zuban in bug #11032, analyzed by Tom Lane and Andres Freund. The bug was always there, but was masked before commit d19bd29f07aef9e508ff047d128a4046cc8bc1e2, because COMMIT PREPARED always created an extra regular transaction that was WAL-logged. Backpatch to all supported versions (older versions didn't have all the features and therefore didn't have all of the above bugs). http://git.postgresql.org/pg/commitdiff/e74e0906fad5fcdcc807d4655cdc69dded5d58f2
  • Move log_newpage and log_newpage_buffer to xlog.c. log_newpage is used by many indexams, in addition to heap, but for historical reasons it's always been part of the heapam rmgr. Starting with 9.3, we have another WAL record type for logging an image of a page, XLOG_FPI. Simplify things by moving log_newpage and log_newpage_buffer to xlog.c, and switch to using the XLOG_FPI record type. Bump the WAL version number because the code to replay the old HEAP_NEWPAGE records is removed. http://git.postgresql.org/pg/commitdiff/54685338e37889eebd473804c3feb006dd83a882
  • Fix typo in user manual http://git.postgresql.org/pg/commitdiff/822ff44c822007ee14cacfebfe0792d1de669df9

Alvaro Herrera a poussé :

  • Simplify multixact freezing a bit. Testing for abortedness of a multixact member that's being frozen is unnecessary: we only need to know whether the transaction is still in progress or committed to determine whether it must be kept or not. This let us simplify the code a bit and avoid a useless TransactionIdDidAbort test. Suggested by Andres Freund awhile back. http://git.postgresql.org/pg/commitdiff/c2581794f37e76c910eb91f1bf1f1e581123abd6
  • Avoid uselessly looking up old LOCK_ONLY multixacts. Commit 0ac5ad5134f2 removed an optimization in multixact.c that skipped fetching members of MultiXactId that were older than our OldestVisibleMXactId value. The reason this was removed is that it is possible for multixacts that contain updates to be older than that value. However, if the caller is certain that the multi does not contain an update (because the infomask bits say so), it can pass this info down to GetMultiXactIdMembers, enabling it to use the old optimization. Pointed out by Andres Freund in 20131121200517.GM7240@alap2.anarazel.de http://git.postgresql.org/pg/commitdiff/05315498012530d44cd89a209242a243374e274d

Bruce Momjian a poussé :

Peter Eisentraut a poussé :

Robert Haas a poussé :

  • Fix mishandling of background worker PGPROCs in EXEC_BACKEND builds. InitProcess() relies on IsBackgroundWorker to decide whether the PGPROC for a new backend should be taken from ProcGlobal's freeProcs or from bgworkerFreeProcs. In EXEC_BACKEND builds, InitProcess() is called sooner than in non-EXEC_BACKEND builds, and IsBackgroundWorker wasn't getting initialized soon enough. Report by Noah Misch. Diagnosis and fix by me. http://git.postgresql.org/pg/commitdiff/e280c630a87e1b8325770c6073097d109d79a00f

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Laurenz Albe sent in a patch to make pg_dump behave consistently for different archive formats.
  • MauMau and Andres Freund traded patches to fix an issue with sinval.
  • Thomas Munro sent in three more revisions of a patch to add SKIP LOCKED DATA.
  • Furuya Osamu sent in another revision of a patch to add an synchronous mode to pg_receivexlog.
  • Keith Baker sent in a patch to add a QNX 6.5 port.
  • Fabien COELHO sent in two more revisions of a patch to fix some issues in the Gaussian distribution patch for pgbench.
  • Daniele Varrazzo sent in two revisions of a patch to fix redundant i18n strings in json.
  • Heikki Linnakangas sent in a patch to move log_newpage out of heapam.c.
  • Kyotaro HORIGUCHI sent in a patch to introduce coarse-grained parallelism via the postgres_fdw.
  • Vik Fearing sent in a patch to make the REINDEX syntax more useful.
  • Heikki Linnakangas sent in another revision of a patch to make WAL format and API changes.
  • Anastasia Lubennikova sent in another revision of a patch to add index-only scans for GiST.
  • Heikki Linnakangas sent in another revision of a patch to support Windows SChannel as an OpenSSL replacement.
  • Fabien COELHO sent in a patch to add the modulo (%) operator to pgbench.
  • David Rowley sent in a patch to fix the Windows build.
  • Peter Geoghegan sent in two more revisions of a patch to add a B-Tree support function which abbreviates certain searches.
  • Jeff Davis sent in a patch to explicitly track allocated memory (the blocks, not the chunks) for each memory context, as well as its children.
  • Emre Hasegeli sent in another revision of a patch to enable KNN-GiST with recheck.

par N Bougain le mardi 5 août 2014 à 13h10

Nouvelles hebdomadaires de PostgreSQL - 27 juillet 2014

Publication de PostgreSQL 9.4 Beta 2. TEST! http://www.postgresql.org/about/news/1533/

Les correctifs 9.3.5, 9.2.9, 9.1.14, 9.0.18 et 8.4.22 de PostgreSQL sont disponibles. 8.4 n'est plus maintenu. Mettez à jour dès que possible ! http://www.postgresql.org/about/news/1534/

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

Peter Eisentraut a poussé :

Magnus Hagander a poussé :

Tom Lane a poussé :

  • Defend against bad relfrozenxid/relminmxid/datfrozenxid/datminmxid values. In commit a61daa14d56867e90dc011bbba52ef771cea6770, we fixed pg_upgrade so that it would install sane relminmxid and datminmxid values, but that does not cure the problem for installations that were already pg_upgraded to 9.3; they'll initially have "1" in those fields. This is not a big problem so long as 1 is "in the past" compared to the current nextMultiXact counter. But if an installation were more than halfway to the MXID wrap point at the time of upgrade, 1 would appear to be "in the future" and that would effectively disable tracking of oldest MXIDs in those tables/databases, until such time as the counter wrapped around. While in itself this isn't worse than the situation pre-9.3, where we did not manage MXID wraparound risk at all, the consequences of premature truncation of pg_multixact are worse now; so we ought to make some effort to cope with this. We discussed advising users to fix the tracking values manually, but that seems both very tedious and very error-prone. Instead, this patch adopts two amelioration rules. First, a relminmxid value that is "in the future" is allowed to be overwritten with a full-table VACUUM's actual freeze cutoff, ignoring the normal rule that relminmxid should never go backwards. (This essentially assumes that we have enough defenses in place that wraparound can never occur anymore, and thus that a value "in the future" must be corrupt.) Second, if we see any "in the future" values then we refrain from truncating pg_clog and pg_multixact. This prevents loss of clog data until we have cleaned up all the broken tracking data. In the worst case that could result in considerable clog bloat, but in practice we expect that relfrozenxid-driven freezing will happen soon enough to fix the problem before clog bloat becomes intolerable. (Users could do manual VACUUM FREEZEs if not.) Note that this mechanism cannot save us if there are already-wrapped or already-truncated-away MXIDs in the table; it's only capable of dealing with corrupt tracking values. But that's the situation we have with the pg_upgrade bug. For consistency, apply the same rules to relfrozenxid/datfrozenxid. There are not known mechanisms for these to get messed up, but if they were, the same tactics seem appropriate for fixing them. http://git.postgresql.org/pg/commitdiff/78db307bb238f4d2d27e62c06a246e88b92fa53b
  • Adjust cutoff points in newly-added sanity tests. Per recommendation from Andres Freund. http://git.postgresql.org/pg/commitdiff/87f830e0ce03063fc7f856c95fa75a719b3a8ad0
  • Release notes for 9.3.5, 9.2.9, 9.1.14, 9.0.18, 8.4.22. http://git.postgresql.org/pg/commitdiff/212825f8139abbcfe366a8f884193b5b693921de
  • Reject out-of-range numeric timezone specifications. In commit 631dc390f49909a5c8ebd6002cfb2bcee5415a9d, we started to handle simple numeric timezone offsets via the zic library instead of the old CTimeZone/HasCTZSet kluge. However, we overlooked the fact that the zic code will reject UTC offsets exceeding a week (which seems a bit arbitrary, but not because it's too tight ...). This led to possibly setting session_timezone to NULL, which results in crashes in most timezone-related operations as of 9.4, and crashes in a small number of places even before that. So check for NULL return from pg_tzset_offset() and report an appropriate error message. Per bug #11014 from Duncan Gillis. Back-patch to all supported branches, like the previous patch. (Unfortunately, as of today that no longer includes 8.4.) http://git.postgresql.org/pg/commitdiff/6412f3e2d09b562fafc129c134e7336c4fe790ed
  • Check block number against the correct fork in get_raw_page(). get_raw_page tried to validate the supplied block number against RelationGetNumberOfBlocks(), which of course is only right when accessing the main fork. In most cases, the main fork is longer than the others, so that the check was too weak (allowing a lower-level error to be reported, but no real harm to be done). However, very small tables could have an FSM larger than their heap, in which case the mistake prevented access to some FSM pages. Per report from Torsten Foertsch. In passing, make the bad-block-number error into an ereport not elog (since it's certainly not an internal error); and fix sloppily maintained comment for RelationGetNumberOfBlocksInFork. This has been wrong since we invented relation forks, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/27cef0a56111a7a44e0d9b9a7819f7e9f4980a77
  • Re-enable error for "SELECT ... OFFSET -1". The executor has thrown errors for negative OFFSET values since 8.4 (see commit bfce56eea45b1369b7bb2150a150d1ac109f5073), but in a moment of brain fade I taught the planner that OFFSET with a constant negative value was a no-op (commit 1a1832eb085e5bca198735e5d0e766a3cb61b8fc). Reinstate the former behavior by only discarding OFFSET with a value of exactly 0. In passing, adjust a planner comment that referenced the ancient behavior. Back-patch to 9.3 where the mistake was introduced. http://git.postgresql.org/pg/commitdiff/27048980f503da22dcd289ec8342b7021c8e73e6
  • Rearrange documentation paragraph describing pg_relation_size(). Break the list of available options into an <itemizedlist> instead of inline sentences. This is mostly motivated by wanting to ensure that the cross-references to the FSM and VM docs don't cross page boundaries in PDF format; but it seems to me to read more easily this way anyway. I took the liberty of editorializing a bit further while at it. Per complaint from Magnus about 9.0.18 docs not building in A4 format. Patch all active branches so we don't get blind-sided by this particular issue again in future. http://git.postgresql.org/pg/commitdiff/4fd9e6ffdd9aae51a935c255cbf7691c369ded1b
  • Fix a performance problem in pg_dump's dump order selection logic. findDependencyLoops() was not bright about cases where there are multiple dependency paths between the same two dumpable objects. In most scenarios this did not hurt us too badly; but since the introduction of section boundary pseudo-objects in commit a1ef01fe163b304760088e3e30eb22036910a495, it was possible for this code to take unreasonable amounts of time (tens of seconds on a database with a couple thousand objects), as reported in bug #11033 from Joe Van Dyk. Joe's particular problem scenario involved "pg_dump -a" mode with long chains of foreign key constraints, but I think that similar problems could arise with other situations as long as there were enough objects. To fix, add a flag array that lets us notice when we arrive at the same object again while searching from a given start object. This simple change seems to be enough to eliminate the performance problem. Back-patch to 9.1, like the patch that introduced section boundary objects. http://git.postgresql.org/pg/commitdiff/c8e2e0e7129276440d1806dfe4f930c7177ccaac

Noah Misch a poussé :

  • Diagnose incompatible OpenLDAP versions during build and test. With OpenLDAP versions 2.4.24 through 2.4.31, inclusive, PostgreSQL backends can crash at exit. Raise a warning during "configure" based on the compile-time OpenLDAP version number, and test the crash scenario in the dblink test suite. Back-patch to 9.0 (all supported versions). http://git.postgresql.org/pg/commitdiff/d7cdf6ee36adeac9233678fb8f2a112e6678a770
  • MSVC: Substitute $(top_builddir) in REGRESS_OPTS. Commit d7cdf6ee36adeac9233678fb8f2a112e6678a770 introduced a usage thereof. Back-patch to 9.0, like that commit. http://git.postgresql.org/pg/commitdiff/31f9bbf05928ed8f20b1c371df8098d8c7dddb37
  • Report success when Windows kill() emulation signals an exiting process. This is consistent with the POSIX verdict that kill() shall not report ESRCH for a zombie process. Back-patch to 9.0 (all supported versions). Test code from commit d7cdf6ee36adeac9233678fb8f2a112e6678a770 depends on it, and log messages about kill() reporting "Invalid argument" will cease to appear for this not-unexpected condition. http://git.postgresql.org/pg/commitdiff/0ea1f2a3a8dfcbe8062a65a13700fc5ae83482c6
  • Move PGAC_LDAP_SAFE to config/programs.m4. This restores the style of keeping configure.in free of AC_DEFUN. Per gripe from Tom Lane. http://git.postgresql.org/pg/commitdiff/e565ff7553e60b3d13dde410ef96f5256c5525eb
  • Handle WAIT_IO_COMPLETION return from WaitForMultipleObjectsEx(). This return code is possible wherever we pass bAlertable = TRUE; it arises when Windows caused the current thread to run an "I/O completion routine" or an "asynchronous procedure call". PostgreSQL does not provoke either of those Windows facilities, hence this bug remaining largely unnoticed, but other local code might do so. Due to a shortage of complaints, no back-patch for now. Per report from Shiv Shivaraju Gowda, this bug can cause PGSemaphoreLock() to PANIC. The bug can also cause select() to report timeout expiration too early, which might confuse pgstat_init() and CheckRADIUSAuth(). http://git.postgresql.org/pg/commitdiff/de35a9771004b9d521c9d5882db6be4fba20e80e

Andrew Dunstan a poussé :

Fujii Masao a poussé :

  • Fix bug where pg_receivexlog goes into busy loop if -s option is set to 0. The problem is that pg_receivexlog calls select(2) with timeout=0 and goes into busy loop when --status-interval option is set to 0. This bug was introduced by the commit, 74cbe966fe2d76de1d607d933c98c144dab58769. Per report from Sawada Masahiko http://git.postgresql.org/pg/commitdiff/78493b716802cbe632d36c85f0d7c3bdb708e045

Robert Haas a poussé :

  • docs: Improve documentation of \pset without arguments. The syntax summary previously failed to clarify that the first argument is also optional. The textual description did mention it, but all the way at the bottom. It fits better with the command overview, so move it there, and fix the summary also. Dilip Kumar, reviewed by Fabien Coelho http://git.postgresql.org/pg/commitdiff/967a4e7f3107e3c5b732fe4f8e13a1f31a255e46
  • Avoid access to already-released lock in LockRefindAndRelease. Spotted by Tom Lane. http://git.postgresql.org/pg/commitdiff/32d78894c2a92cbb2fe7b9160936fee31672e7d9
  • Prevent shm_mq_send from reading uninitialized memory. shm_mq_send_bytes didn't invariably initialize *bytes_written before returning, which would cause shm_mq_send to read from uninitialized memory and add the value it found there to mqh->mqh_partial_bytes. This could cause the next attempt to send a message via the queue to fail an assertion (if the queue was detached) or copy data from a garbage pointer value into the queue (if non-blocking mode was in use). http://git.postgresql.org/pg/commitdiff/1144ea3421e4bcc24dd7402a1f21ba94638d591b
  • Fix checkpointer crash in EXEC_BACKEND builds. Nothing in the checkpointer calls InitXLOGAccess(), so WALInsertLocks never got initialized there. Without EXEC_BACKEND, it works anyway because the correct value is inherited from the postmaster, but with EXEC_BACKEND we've got a problem. The problem appears to have been introduced by commit 68a2e52bbaf98f136a96b3a0d734ca52ca440a95. To fix, move the relevant initialization steps from InitXLOGAccess() to XLOGShmemInit(), making this more parallel to what we do elsewhere. Amit Kapila http://git.postgresql.org/pg/commitdiff/250c26ba9cf247c2d5b8dbd2435a36d11382c43e

Andres Freund a poussé :

  • Properly remove ephemeral replication slots after a crash restart. Ephemeral slots - slots that shouldn't survive database restarts - weren't properly cleaned up after a immediate/crash restart. They were ignored in the sense that they weren't restored into memory and thus didn't cause unwanted resource retention; but they prevented a new slot with the same name from being created. Now ephemeral slots are fully removed during startup. Backpatch to 9.4 where replication slots where added. http://git.postgresql.org/pg/commitdiff/93a028f569232fa498279841cb61ad11c2df5c85

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Dilip Kumar sent in another revision of a patch to document correctly that all arguments to pset are optional, not just the second.
  • Marko (johto) Tiikkaja sent in a patch to add plpgsql.extra_warnings='num_into_expressions'
  • Anastasia Lubennikova sent in another revision of a patch to allow index-only scans for multicolumn GiST.
  • Fabien COELHO and Mitsumasa KONDO traded patches to allow pgbench to use a Gaussian distribution.
  • Kaigai Kouhei sent in another revision of a patch to enable the custom plan API.
  • Pavel Stehule sent in another revision of a patch to enable psql unicode border line styles.
  • Viswanatham Kirankumar sent in another revision of a patch to process pg_hba.conf keywords as case-insensitive.
  • Fabrízio de Royes Mello sent in two more revisions of a patch to enable ALTER TABLE ... SET LOGGED.
  • Kyotaro HORIGUCHI sent in another revision of a patch to enable using unique indexes for longer pathkeys.
  • Michael Paquier sent in a patch to add a facility to check Full-Page-Write consistency at WAL replay.
  • Thomas Munro sent in four more revisions of a patch to implement SKIP LOCKED DATA.
  • Fabrízio de Royes Mello sent in another revision of a patch to fix an issue where the verbose output of pg_dump did not show schema names.
  • Furuya Osamu sent in another revision of a patch to add a synchronous mode to pg_receivexlog.
  • Kyotaro HORIGUCHI sent in a patch to introduce coarse-grained parallelism via the postgres_fdw.
  • Alexey Klyukin sent in a patch to implement subject alternative names support for SSL connections.
  • Robert Haas sent in a patch to implement a contrib module that lets you launch arbitrary command in a background worker, and supporting infrastructure patches for core.
  • Guillaume Lelarge sent in a patch to fix the documentation for unix_socket_directory.
  • Marko (johto) Tiikkaja sent in a patch to implement EXIT USING ROLLBACK.
  • Marko (johto) Tiikkaja sent in a patch to make PL/pgsql throw an error during compilation (instead of runtime) if the number of parameters passed to RAISE don't match the number of placeholders in error message.
  • Haribabu Kommi sent in another revision of a patch to enable min/max on inet types.
  • David Rowley sent in a patch to fix an issue where get_loop_count() fails to ignore RELOPT_DEADREL rels.
  • Peter Geoghegan sent in another revision of a patch to add a strxfrm() optimization for B-Trees.

par N Bougain le mardi 5 août 2014 à 13h00

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

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

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

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

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

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