PostgreSQL La base de donnees la plus sophistiquee au monde.

La planète francophone de PostgreSQL

jeudi 18 décembre 2014

Actualités PostgreSQL.fr

PostgreSQL 9.4.0


Contenu

  Texte original
  Détail des fonctionnalités
  Téléchargement
  Documentation
  Licence
  Contacts
  Images et Logos
  Informations concernant les sociétés citées
  Support professionnel

Texte original

PostgreSQL 9.4 améliore la flexibilité, l'évolutivité et les performances

18 DÉCEMBRE 2014 : Le PostgreSQL Global Development Group annonce la sortie de PostgreSQL 9.4. Il s'agit de la dernière version en date du système de gestion de bases de données libre de référence. Cette version ajoute de nombreuses fonctionnalités qui améliorent la flexibilité, l'évolutivité et les performances de PostgreSQL. Et cela pour de nombreux cas d'utilisation différents, avec notamment l'amélioration du support de JSON, de la réplication et de la performance de l'indexation.

Flexibilité

Avec le nouveau type de données JSONB, plus besoin de choisir entre le stockage relationnel et non-relationnel : il y a les deux à la fois. JSONB supporte les recherches rapides et les requêtes de recherche d'expressions simples en utilisant les index de type Generalized Inverted Indexes (GIN). De nombreuses nouvelles fonctions permettent de manipuler les données JSON, avec des performances souvent meilleures que celles obtenues avec les bases de données orientées document les plus populaires. Avec JSONB, les données des tables peuvent être facilement intégrées à celles des documents permettant d'obtenir un environnement de bases de données complètement intégré.

« JSONB rapproche PostgreSQL des développeurs de la communauté JavaScript en permettant aux données JSON d'être stockées et requêtées nativement. node.js et les autres frameworks JavaScript côté serveur peuvent désormais profiter de la sécurité et de la robustesse de PostgreSQL, tout en continuant à stocker les données dans le format sans schéma qu'ils préfèrent », précise Matt Soldo, Responsable Produit chez Heroku Postgres.

Evolutivité

Avec la 9.4, le décodage logique (Logical Decoding) offre une nouvelle API pour lire, filtrer et manipuler le flux de réplication de PostgreSQL. Cette interface est la fondation de nouveaux outils de réplication, comme la réplication bi-directionnelle (Bi-Directional Replication), qui permet la création de grappes PostgreSQL multi-maîtres. D'autres améliorations dans la réplication système concernent l'administration et l'utilisation des réplicats, notamment les connecteurs de réplication et les réplicats différés.

« La raison principale à notre adoption immédiate de PostgreSQL 9.4 en production est la nouvelle fonctionnalité de décodage logique », explique Marco Favale, responsable de production Cartographique chez Navionics. « La possibilité d'écrire des greffons de sortie personnalisés et flexibles nous permettra de récupérer de manière transparente les modifications sur certaines tables et de les répliquer où nous voulons, tout en supprimant les contraintes relatives aux solutions de réplication par triggers, lourdes et plus complexes à gérer. »

« Zalando dépend de la stabilité et des performances de centaines de serveurs de bases de données PostgreSQL pour satisfaire des millions de clients partout en Europe », annonce Valentine Gogichashvili, Team Lead Database Operations chez Zalando Technologies. « Nous sommes impatients d'utiliser la réplication différée, immédiatement utilisable, et nous allons évaluer les outils de réplication bi-directionnelle dès qu'ils seront disponibles. »

Performances

La version 9.4 apporte également de multiples gains de performance, permettant aux utilisateurs de tirer plus d'avantages de leur serveur PostgreSQL dont :

  • des améliorations des index GIN, avec une taille réduite de 50% et une vitesse multipliée par 3 ;
  • des vues matérialisées qui peuvent être mises à jour de manière concurrente, pour du reporting plus rapide et plus à jour ;
  • le rechargement rapide du cache de base de données au redémarrage avec pg_prewarm ;
  • une écriture parallèle plus rapide des journaux de transaction de PostgreSQL.

« Nous allons vraiment beaucoup gagner avec le rafraichissement concurrent des vues matérialisées, les réplicats différés (qui permettront aux restaurations après incident d'être plus robustes), ainsi qu'avec les améliorations de performances apportées par chaque nouvelle version », ajoute Marco Favale.

Détail des fonctionnalités

Plus d'informations sur les fonctionnalités ci-dessus et les autres dans les liens suivants :

Téléchargement

Documentation

La documentation au format HTML et les pages de manuel sont installées avec PostgreSQL. La documentation en ligne, exhaustive et interactive, peut être parcourue, interrogée et commentée librement.

Licence

PostgreSQL utilise la licence PostgreSQL, une licence permissive de type BSD. Cette licence certifiée par l'OSI est largement appréciée pour sa flexibilité et sa compatibilité avec le monde des affaires, puisqu'elle ne restreint pas l'utilisation de PostgreSQL dans les applications propriétaires ou commerciales. Associée à un support proposé par de multiples sociétés et une propriété publique du code, sa licence rend PostgreSQL très populaire parmi les revendeurs souhaitant embarquer une base de données dans leurs produits sans avoir à se soucier des prix de licence, des verrous commerciaux ou modifications des termes de licence.

Contacts

Pages Web

Contact presse

France et pays francophones
Stéphane Schildknecht
fr at postgresql dot org
+33 (0) 617 11 37 42

Pour les contacts d'autres régions, consulter la liste des contacts internationaux.

Images et Logos

Tous les logos sont modifiables et redistribuables selon les termes de la licence PostgreSQL. Le nom PostgreSQL et le logo sont des marques déposées de la PostgreSQL Community Association of Canada.

Informations concernant les sociétés citées

« JSONB rapproche PostgreSQL des développeurs de la communauté JavaScript en permettant aux données JSON d'être stockées et requêtées nativement. node.js et les autres frameworks JavaScript côté serveur peuvent désormais profiter de la sécurité et de la robustesse de PostgreSQL, tout en continuant à stocker les données dans le format sans schéma qu'ils préfèrent », précise Matt Soldo, Responsable Produit chez Heroku Postgres.

À propos de Heroku : Heroku est le leader des plateformes en tant que service (PaaS), orienté sur la facilité d'utilisation, l'automatisation, et la fiabilité. Heroku, dont le siège se trouve à San Francisco, en Californie, est un supporter passioné et actif sur les technologies Ruby et les architectures en nuage. Pour plus d'informations, visitez notre site web et notre blog ou suivez Heroku sur Twitter. Heroku est une filiale, propriété exclusive de salesforce.com. Contact (uniquement en Anglais): Heroku PR

« La raison principale à notre adoption immédiate de PostgreSQL 9.4 en production est la nouvelle fonctionnalité de décodage logique », explique Marco Favale, responsable de production Cartographique chez Navionics. « La possibilité d'écrire des greffons de sortie personnalisés et flexibles nous permettra de récupérer de manière transparente les modifications sur certaines tables et de les répliquer où nous voulons, tout en supprimant les contraintes relatives aux solutions de réplication par triggers, lourdes et plus complexes à gérer. »

« Nous allons vraiment beaucoup gagner avec le rafraichissement concurrent des vues matérialisées, les réplicats différés (qui permettront aux restaurations après incident d'être plus robustes), ainsi qu'avec les améliorations de performances apportées par chaque nouvelle version », ajoute Marco Favale.

À propos de Navionics : Navionics (www.navionics.com) dispose de la plus important base de données de cartes marines, de voies navigables et de lacs au monde, couvrant les océans, toutes les mers de la planète ainsi que des centaines de milliers de lacs et rivières. Nombre de ces cartes ont été élaborées par le biais d'enquêtes exclusives de Navionics réalisées à la fois sur le terrain et par sondage à distance notamment par imagerie satellitaire et scanners laser aéroportés. Navionics est situé en Italie, aux États-Unis, en Inde et en Estonie, avec des ventes et des services partout dans le monde. Contact (Anglais ou Italien): Marco Favale

« Zalando dépend de la stabilité et des performances de centaines de serveurs de bases de données PostgreSQL pour satisfaire des millions de clients partout en Europe. Nous nous tenons à jour des dernières versions de développement de PostgreSQL depuis 2010, lorsque nous avons mis en place la première Release Candidate de PostgreSQL 9.0. Chaque nouvelle version nous apporte plus de performances et des améliorations dont nous bénéficions pratiquement dès le premier jour. Nous sommes impatients d'utiliser la réplication différée, immédiatement utilisable avec PostgreSQL 9.4. Nous allons évaluer les outils de réplication bi-directionnelle, rendus possibles avec l'introduction du décodage logique. Notre équipe travaille déjà à l'amélioration de nos outils de supervision open source qui bénéficieront des statistiques temps réel sur les commandes fournies par le module pg_stat_statements et les multiples améliorations qu'il a reçu dans la version 9.4. Les améliorations des aggrégats, comme filter, rendent l'écriture des requêtes avec aggrégats plus facile et encourangent par la suite nos collègues à écrire du SQL bien plus élégant qu'auparavant », explique Valentine Gogichashvili, responsable des opérations bases de données chez Zalando Technologies.

Zalando est l'une des plus grosses entreprises de e-commerce en europe, opérant dans 15 pays à ce jour. Chez Zalando Technology, nous avons développé l'essentiel de notre plateforme en interne, notamment la boutique, les systèmes internes et le logiciel de logistique. En ce qui concerne le développement et les opérations sur les systèmes, nous utilisons majoritairement des solutions open source et travaillons en petites équipes flexibles. Nos équipes technologiques créent l'incroyable aventure de shopping en ligne Zalando, que des millions de clients apprécient chaque jour. Venez visiter notre site web ou notre blog

Support professionnel

PostgreSQL bénéficie du support de nombreuses sociétés, qui financent des développeurs, fournissent l'hébergement ou un support financier. Les plus fervents supporters sont listés sur la page des mécènes du développement.

Le travail sur JSONB a été financé par Engine Yard et soutenu par Heroku, Andrew Dunstan et plusieurs autres contributeurs. Le travail sur le Décodeur Logique a été piloté par 2ndQuadrant. Plusieurs autres sociétés ont contribué à cette version.

Il existe également une très grande communauté de sociétés fournissant du support PostgreSQL, des consultants indépendants aux sociétés multinationales.

Les dons sont acceptés avec plaisir.

Vous pouvez également acheter des produits dérivés de qualité sur la Boutique Zazzle PostgreSQL.

par SAS le jeudi 18 décembre 2014 à 16h57

mardi 16 décembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 14 décembre 2014

Le meetup du 18 février 2015 à Melbourne accueillera Gabriele Bartolini à propos de PostgreSQL 9.4 pour les devops. Détails ci-après, et RSVP : http://www.meetup.com/melpug/events/219082475/

Le Prague PostgreSQL Developer Day (P2D2) 2015 aura lieu à Prague, République Tchèque, les 11 & 12 février 2015 : http://www.p2d2.cz/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en décembre

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

Simon Riggs a poussé :

Peter Eisentraut a poussé :

Álvaro Herrera a poussé :

Heikki Linnakangas a poussé :

Tom Lane a poussé :

  • Fix minor thinko in convertToJsonb(). The amount of space to reserve for the value's varlena header is VARHDRSZ, not sizeof(VARHDRSZ). The latter coding accidentally failed to fail because of the way the VARHDRSZ macro is currently defined; but if we ever change it to return size_t (as one might reasonably expect it to do), convertToJsonb() would have failed. Spotted by Mark Dilger. http://git.postgresql.org/pg/commitdiff/24688f4e5a7d5fadde0e43a5b123432d81577d82
  • Fix assorted confusion between Oid and int32. In passing, also make some debugging elog's in pgstat.c a bit more consistently worded. Back-patch as far as applicable (9.3 or 9.4; none of these mistakes are really old). Mark Dilger identified and patched the type violations; the message rewordings are mine. http://git.postgresql.org/pg/commitdiff/06d5803ffa1ea5a42295c9266f30557e44a99de7
  • Fix completely broken REINDEX SCHEMA testcase. Aside from not testing the case it claimed to test (namely a permissions failure), it left a login-capable role lying around, which quite aside from possibly being a security hole would cause subsequent regression runs to fail since the role would already exist. http://git.postgresql.org/pg/commitdiff/58af84f4bba5c87b352cf40e276e287fd289bd77
  • Make rowsecurity test clean up after itself, too. Leaving global objects like roles hanging around is bad practice. http://git.postgresql.org/pg/commitdiff/0845264642d855d92c63c5d05a4ef83245ca16c5
  • Fix corner case where SELECT FOR UPDATE could return a row twice. In READ COMMITTED mode, if a SELECT FOR UPDATE discovers it has to redo WHERE-clause checking on rows that have been updated since the SELECT's snapshot, it invokes EvalPlanQual processing to do that. If this first occurs within a non-first child table of an inheritance tree, the previous coding could accidentally re-return a matching row from an earlier, already-scanned child table. (And, to add insult to injury, I think this could make it miss returning a row that should have been returned, if the updated row that this happens on should still have passed the WHERE qual.) Per report from Kyotaro Horiguchi; the added isolation test is based on his test case. This has been broken for quite awhile, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/2db576ba8c449fcaf61ae7aa14ed62e63ebf5924
  • Fix planning of SELECT FOR UPDATE on child table with partial index. Ordinarily we can omit checking of a WHERE condition that matches a partial index's condition, when we are using an indexscan on that partial index. However, in SELECT FOR UPDATE we must include the "redundant" filter condition in the plan so that it gets checked properly in an EvalPlanQual recheck. The planner got this mostly right, but improperly omitted the filter condition if the index in question was on an inheritance child table. In READ COMMITTED mode, this could result in incorrectly returning just-updated rows that no longer satisfy the filter condition. The cause of the error is using get_parse_rowmark() when get_plan_rowmark() is what should be used during planning. In 9.3 and up, also fix the same mistake in contrib/postgres_fdw. It's currently harmless there (for lack of inheritance support) but wrong is wrong, and the incorrect code might get copied to someplace where it's more significant. Report and fix by Kyotaro Horiguchi. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/462bd95705a0c23ba0b0ba60a78d32566a0384c1
  • Revert misguided change to postgres_fdw FOR UPDATE/SHARE code. In commit 462bd95705a0c23ba0b0ba60a78d32566a0384c1, I changed postgres_fdw to rely on get_plan_rowmark() instead of get_parse_rowmark(). I still think that's a good idea in the long run, but as Etsuro Fujita pointed out, it doesn't work today because planner.c forces PlanRowMarks to have markType = ROW_MARK_COPY for all foreign tables. There's no urgent reason to change this in the back branches, so let's just revert that part of yesterday's commit rather than trying to design a better solution under time pressure. Also, add a regression test case showing what postgres_fdw does with FOR UPDATE/SHARE. I'd blithely assumed there was one already, else I'd have realized yesterday that this code didn't work. http://git.postgresql.org/pg/commitdiff/8ec8760fc87ecde0516e511f1c55aec627b01ea7
  • Avoid instability in output of new REINDEX SCHEMA test. The planner seems to like to do this join query as a hash join, making the output ordering machine-dependent; worse, it's a hash on OIDs, so that it's a bit astonishing that the result doesn't change from run to run even on one machine. Add an ORDER BY to get consistent results. Per buildfarm. I also suppressed output from the final DROP SCHEMA CASCADE, to avoid occasional failures similar to those fixed in commit 81d815dc3ed74a7d. That hasn't been observed in the buildfarm yet, but it seems likely to happen in future if we leave it as-is. http://git.postgresql.org/pg/commitdiff/1c5c70df45714f38c033bb1a272d8db4f2dc8a2f
  • Repair corner-case bug in array version of percentile_cont(). The code for advancing through the input rows overlooked the case that we might already be past the first row of the row pair now being considered, in case the previous percentile also fell between the same two input rows. Report and patch by Andrew Gierth; logic rewritten a bit for clarity by me. http://git.postgresql.org/pg/commitdiff/b0f479113abaf28f1c9ed25d856ffe6fd4bb7dc9
  • Improve recovery target settings documentation. Commit 815d71dee hadn't bothered to update the documentation to match the behavioral change, and a lot of other text in this section was badly in need of copy-editing. http://git.postgresql.org/pg/commitdiff/e311cd6ded096122a5f2b5cbe91bc3a9f0dda3cb
  • Update 9.4 release notes. Set release date, do a final pass of wordsmithing, improve some other new-in-9.4 documentation. http://git.postgresql.org/pg/commitdiff/0923b01e3e64d4985ffbdf8ec912e25ad02e0c0c

Andrew Dunstan a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Petr (PJMODOS) Jelinek sent in two more revisions of a patch to add a shutdown_at_recovery_target option to recovery.conf.
  • Euler Taveira de Oliveira sent in a patch to bring the documentation of pg_receivexlog into line with its new functionality.
  • Peter Geoghegan sent in another revision of a patch to implement INSERT ... ON CONFLICT {UPDATE | IGNORE}.
  • Petr (PJMODOS) Jelinek sent in another revision of a patch to add a sequence access method.
  • Alexander Shulgin sent in two revisions of a patch to track TRUNCATEs in pgstat transaction stats.
  • Robert Haas and Heikki Linnakangas traded patches to advance local xmin more aggressively.
  • Etsuro Fujita sent in another revision of a patch to allow foreign tables to be part of table inheritance hierarchies.
  • Álvaro Herrera sent in a patch to implement logical column ordering.
  • David Rowley sent in another revision of a patch to allow removing INNER JOIN under some circumstances.
  • Matt Newell sent in another revision of a patch to enable libpq pipelining.
  • Heikki Linnakangas improved the performance of k-Nearest-Neighbor GiST index searches using a Pairing Heap http://en.wikipedia.org/wiki/Pairing_heap.
  • Petr (PJMODOS) Jelinek sent in a patch to implement TABLESAMPLE.
  • Jeff Davis sent in another revision of a patch to implement a memory-bounded HashAgg.
  • Mark Dilger sent in a WIP patch to allow printf formatting for Oids.
  • Kyotaro HORIGUCHI and Tom Lane traded patches to fix an issue where some queries return duplicate rows after FOR UPDATE was blocked, in other words, after getting HeapTupleUpdated in ExecLockRows.
  • Simon Riggs sent in another revision of a patch to turn off heap-only tuple cleanup under some circumstances.
  • Heikki Linnakangas and Michael Paquier traded patches to refactor the code for sync node detection.
  • Peter Eisentraut sent in a patch to ensure that VPATH builds not write to the source tree.
  • Kyotaro HORIGUCHI sent in a patch to fix an issue where SELECT FROM <inheritance parent> WHERE <cond> FOR UPDATE may return results which does not match the <cond>.
  • Alexander Shulgin sent in another revision of a patch to turn recovery.conf into GUCs.
  • Robert Haas sent in a patch to add two new concepts: parallel mode, and parallel contexts.
  • Heikki Linnakangas sent in a patch to place pg_rewind, a tool that allows repurposing an old master server as a new standby server, after promotion, even if the old master was not shut down cleanly, in contrib/.
  • Andreas Karlsson sent in a patch to reduce the required lock strength of trigger and foreign key DDL.
  • Tatsuo Ishii sent in a patch to pgbench from exiting when the pg_bench_* tables do not exist.
  • Michael Paquier sent in another revision of a patch to allow compressing full-page writes.
  • Ali Akbar sent in a test for the patch to fix xpath() to return namespace definitions.
  • Emre Hasegeli sent in another revision of a patch to implement a BRIN range operator class.

par N Bougain le mardi 16 décembre 2014 à 22h49

lundi 8 décembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 7 décembre 2014

Le Prague PostgreSQL Developer Day (P2D2) 2015 aura lieu à Prague, République Tchèque, les 11 & 12 février 2015 : http://www.p2d2.cz/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en décembre

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

  • Add a #define for the inet overlaps operator. Extracted from pending inet selectivity patch. The rest of it isn't quite ready to commit, but we might as well push this part so the patch doesn't have to track the moving target of pg_operator.h. http://git.postgresql.org/pg/commitdiff/866737c923730f0cf8dde787868ab3ae697a278a
  • Guard against bad "dscale" values in numeric_recv(). We were not checking to see if the supplied dscale was valid for the given digit array when receiving binary-format numeric values. While dscale can validly be more than the number of nonzero fractional digits, it shouldn't be less; that case causes fractional digits to be hidden on display even though they're there and participate in arithmetic. Bug #12053 from Tommaso Sala indicates that there's at least one broken client library out there that sometimes supplies an incorrect dscale value, leading to strange behavior. This suggests that simply throwing an error might not be the best response; it would lead to failures in applications that might seem to be working fine today. What seems the least risky fix is to truncate away any digits that would be hidden by dscale. This preserves the existing behavior in terms of what will be printed for the transmitted value, while preventing subsequent arithmetic from producing results inconsistent with that. In passing, throw a specific error for the case of dscale being outside the range that will fit into a numeric's header. Before you got "value overflows numeric format", which is a bit misleading. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/0927bf8060eca4e220cde48b1425e7bcf7451b94
  • Minor cleanup of function declarations for BRIN. Get rid of PG_FUNCTION_INFO_V1() macros, which are quite inappropriate for built-in functions (possibly leftovers from testing as a loadable module?). Also, fix gratuitous inconsistency between SQL-level and C-level names of the minmax support functions. http://git.postgresql.org/pg/commitdiff/1511521a36f99a242f51c3bd0811cfcd53c5e63a
  • Fix JSON aggregates to work properly when final function is re-executed. Davide S. reported that json_agg() sometimes produced multiple trailing right brackets. This turns out to be because json_agg_finalfn() attaches the final right bracket, and was doing so by modifying the aggregate state in-place. That's verboten, though unfortunately it seems there's no way for nodeAgg.c to check for such mistakes. Fix that back to 9.3 where the broken code was introduced. In 9.4 and HEAD, likewise fix json_object_agg(), which had copied the erroneous logic. Make some cosmetic cleanups as well. http://git.postgresql.org/pg/commitdiff/75ef435218fe4f6ce29d33e10f6f7a328c93f7e7
  • Improve error messages for malformed array input strings. Make the error messages issued by array_in() uniformly follow the style ERROR: malformed array literal: "actual input string" DETAIL: specific complaint here and rewrite many of the specific complaints to be clearer. The immediate motivation for doing this is a complaint from Josh Berkus that json_to_record() produced an unintelligible error message when dealing with an array item, because it tries to feed the JSON-format array value to array_in(). Really it ought to be smart enough to perform JSON-to-Postgres array conversion, but that's a future feature not a bug fix. In the meantime, this change is something we agreed we could back-patch into 9.4, and it should help de-confuse things a bit. http://git.postgresql.org/pg/commitdiff/475aedd1ef0c0f9fc9d675dd2286380d14804975

Magnus Hagander a poussé :

Heikki Linnakangas a poussé :

Andrew Dunstan a poussé :

  • Fix hstore_to_json_loose's detection of valid JSON number values. We expose a function IsValidJsonNumber that internally calls the lexer for json numbers. That allows us to use the same test everywhere, instead of inventing a broken test for hstore conversions. The new function is also used in datum_to_json, replacing the code that is now moved to the new function. Backpatch to 9.3 where hstore_to_json_loose was introduced. http://git.postgresql.org/pg/commitdiff/e09996ff8dee3f70b0a027cffebccef4388ed5b7

Álvaro Herrera a poussé :

  • dummy_seclabel: add sql/, expected/, and .gitignores. Michael Paquier http://git.postgresql.org/pg/commitdiff/332562437724845635f0e11685d11fdfe8719eb2
  • pageinspect/BRIN: minor tweaks. Michael Paquier. Double-dash additions suggested by Peter Geoghegan http://git.postgresql.org/pg/commitdiff/b52cb4690e0752efea440173c4923d76d2126679
  • Install kludges to fix check-world for src/test/modules. check-world failed in a completely clean tree, because src/test/modules fail to build unless errcodes.h is generated first. To fix this, install a dependency in src/test/modules' Makefile so that the necessary file is generated. Even with this, running "make check" within individual module subdirs will still fail because the dependency is not considered there, but this case is less interesting and would be messier to fix. check-world still failed with the above fix in place, this time because dummy_seclabel used LOAD to load the dynamic library, which doesn't work because the @libdir@ (expanded by the makefile) is expanded to the final install path, not the temporary installation directory used by make check. To fix, tweak things so that CREATE EXTENSION can be used instead, which solves the problem because the library path is expanded by the backend, which is aware of the true libdir. http://git.postgresql.org/pg/commitdiff/da34731bd3c805ab77d6e5914d02b1f011e9479d
  • Fix typos http://git.postgresql.org/pg/commitdiff/6597ec9be6a9ed50390f73235d6654ec32a0b944
  • doc: Fix markup. In the spirit of d34b48a021b181e30c53280d336820740f67570b Per buildfarm member guaibasaurus, via Stefan Kaltenbrunner. http://git.postgresql.org/pg/commitdiff/182362b669f484c1f92e058c710b025d25ddc5dd
  • Keep track of transaction commit timestamps. Transactions can now set their commit timestamp directly as they commit, or an external transaction commit timestamp can be fed from an outside system using the new function TransactionTreeSetCommitTsData(). This data is crash-safe, and truncated at Xid freeze point, same as pg_clog. This module is disabled by default because it causes a performance hit, but can be enabled in postgresql.conf requiring only a server restart. A new test in src/test/modules is included. Catalog version bumped due to the new subdirectory within PGDATA and a couple of new SQL functions. Authors: Álvaro Herrera and Petr Jelínek Reviewed to varying degrees by Michael Paquier, Andres Freund, Robert Haas, Amit Kapila, Fujii Masao, Jaime Casanova, Simon Riggs, Steven Singer, Peter Eisentraut http://git.postgresql.org/pg/commitdiff/73c986adde5d73a5e2555da9b5c8facedb146dcd
  • Move security_label test. Rather than have the core security_label regression test depend on the dummy_seclabel module, have that part of the test be executed by dummy_seclabel itself directly. This simplifies the testing rig a bit; in particular it should silence the problems from the MSVC buildfarm phylum, which haven't yet gotten taught how to install src/test/modules. http://git.postgresql.org/pg/commitdiff/df761e3cf79db09d602610ee61e51cb378288382

Andres Freund a poussé :

  • Don't skip SQL backends in logical decoding for visibility computation. The logical decoding patchset introduced PROC_IN_LOGICAL_DECODING flag PGXACT flag, that allows such backends to be skipped when computing the xmin horizon/snapshots. That's fine and sensible for walsenders streaming out logical changes, but not at all fine for SQL backends doing logical decoding. If the latter set that flag any change they have performed outside of logical decoding will not be regarded as visible - which e.g. can lead to that change being vacuumed away. Note that not setting the flag for SQL backends isn't particularly bothersome - the SQL backend doesn't do streaming, so it only runs for a limited amount of time. Per buildfarm member 'tick' and Alvaro. Backpatch to 9.4, where logical decoding was introduced. http://git.postgresql.org/pg/commitdiff/0fd38e13706e6bd4b2a4ce4e0570d0aa4ad65345
  • Document that pg_stat_*_tables.n_tup_upd includes n_tup_hot_upd. Author: Peter Geoghegan http://git.postgresql.org/pg/commitdiff/5ede3a31171d83b94a773d997fe05b563f8ebb3d

Peter Eisentraut a poussé :

Robert Haas a poussé :

Simon Riggs a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Jim Nasby sent in a patch to log cleanup lock acquisition failures in vacuum.
  • Tom Lane and Emre Hasegeli traded patches to add selectivity estimation for inet operators.
  • Dilip Kumar sent in another revision of a patch to allow vacuumdb to work in parallel.
  • Jeff Janes sent in a patch to attempt to fix startup/recovery problems with unlogged tables by adding an unconditional and unchecked unlink before the copy.
  • Jeff Davis sent in another revision of a patch to do better memory accounting.
  • SAWADA Masahiko and Michael Paquier traded patches to implement REINDEX SCHEMA.
  • Alexander Shulgin sent in another revision of a patch to turn recovery.conf into GUCs.
  • Craig Ringer and David Rowley traded patches to use the faster, higher precision timer API on Windows when available.
  • Teodor Sigaev sent in another revision of a patch to implement a compression method for SP-GiST.
  • Kaigai Kouhei sent in another revision of a patch to implement some interfaces in the custom scan API.
  • Peter Geoghegan sent in patches to: 1. add new abbreviation infrastructure to sortsupport, and add a single client of this new infrastructure, the text sortsupport routine. 2. Estimate total number of rows to be sorted, and 3. Alter comments to reflect current naming.
  • Matt Newell sent in two revisions of a patch to support pipelining in libpq.
  • Peter Geoghegan sent in another revision of a patch to create a Levenshtein distance column HINT for nonexistent columns.
  • Amit Kapila and David Rowley traded patches to do sequential scans in parallel.
  • Peter Geoghegan sent in a patch to clarify the documentation of pg_stat_all_tables to mention that pg_stat_*_tables.n_tup_upd includes HOT updates.
  • Heikki Linnakangas sent in another revision of a patch to implement an SSL regression test suite.
  • Michael Paquier sent in a patch to add missing descriptions for two parameters in XLOG_PARAMETER_CHANGE.
  • Petr (PJMODOS) Jelinek sent in a patch to rename action_at_recovery_target to recovery_target_action everywhere.
  • Michael Paquier sent in two more revisions of a patch to implement compression of full-pages writes.
  • Stephen Frost sent in a patch to change usage of GetUserId() to has_privs_of_role().
  • Stephen Frost sent in a patch to change the default for include_realm to zero.
  • Adam Brightwell sent in two more revisions of a patch to add a role attribute bitmask catalog representation.
  • Tomas Vondra sent in another revision of a patch to implement an adaptive ndistinct estimator.
  • Tomas Vondra sent in a patch to gracefully increasing NTUP_PER_BUCKET instead of batching.

par N Bougain le lundi 8 décembre 2014 à 21h36

lundi 1 décembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 30 novembre 2014

Appel à conférenciers pour la PGCon 2015 lancé le 1er décembre 2014 : http://www.pgcon.org/2015/papers.php

Offres d'emplois autour de PostgreSQL en novembre

PostgreSQL Local

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA.

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

(lien vers l'article original)

Correctifs appliqués

Heikki Linnakangas a poussé :

  • Distinguish XLOG_FPI records generated for hint-bit updates. Add a new XLOG_FPI_FOR_HINT record type, and use that for full-page images generated for hint bit updates, when checksums are enabled. The new record type is replayed exactly the same as XLOG_FPI, but allows them to be tallied separately e.g. in pg_xlogdump. http://git.postgresql.org/pg/commitdiff/0bd624d63b056205fda17a2d694d91db16468e3f
  • Add a few paragraphs to B-tree README explaining L&Y algorithm. This gives an overview of what Lehman & Yao's paper is all about, so that you can understand the rest of the README without having to read the paper. Per discussion with Peter Geoghegan and others. http://git.postgresql.org/pg/commitdiff/49b86fb1c97878ea2e3a8118df072c95f60077ac
  • Make Port->ssl_in_use available, even when built with !USE_SSL. Code that check the flag no longer need #ifdef's, which is more convenient. In particular, makes it easier to write extensions that depend on it. In the passing, modify sslinfo's ssl_is_used function to check ssl_in_use instead of the OpenSSL specific 'ssl' pointer. It doesn't make any difference currently, as sslinfo is only compiled when built with OpenSSL, but seems cleaner anyway. http://git.postgresql.org/pg/commitdiff/e453cc2741416dc784842b2bba68749556cf0f6f
  • Check return value of strdup() in libpq connection option parsing. An out-of-memory in most of these would lead to strange behavior, like connecting to a different database than intended, but some would lead to an outright segfault. Alex Shulgin and me. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/30bf4689a96cd283af33edcdd6b7210df3f20cd8
  • Allow "dbname" from connection string to be overridden in PQconnectDBParams If the "dbname" attribute in PQconnectDBParams contained a connection string or URI (and expand_dbname = TRUE), the database name from the connection string could not be overridden by a subsequent "dbname" keyword in the array. That was not intentional; all other options can be overridden. Furthermore, any subsequent "dbname" caused the connection string from the first dbname value to be processed again, overriding any values for the same options that were given between the connection string and the second dbname option. In the passing, clarify in the docs that only the first dbname option in the array is parsed as a connection string. Alex Shulgin. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/add1b052e2b2d5206474d58db25e48820242d15b
  • Allow using connection URI in primary_conninfo. The old method of appending options to the connection string didn't work if the primary_conninfo was a postgres:// style URI, instead of a traditional connection string. Use PQconnectdbParams instead. Alex Shulgin http://git.postgresql.org/pg/commitdiff/b3fc6727ce54a16ae9227bcccfebfa028ac5b16f
  • Remove dead function prototype. It was added in commit efc16ea5, but never defined. http://git.postgresql.org/pg/commitdiff/1812ee5767a25a36e7002be8a3a032357d3fe4e2
  • Fix assertion failure at end of PITR. InitXLogInsert() cannot be called in a critical section, because it allocates memory. But CreateCheckPoint() did that, when called for the end-of-recovery checkpoint by the startup process. In the passing, fix the scratch space allocation in InitXLogInsert to go to the right memory context. Also update the comment at InitXLOGAccess, which hasn't been totally accurate since hot standby was introduced (in a hot standby backend, InitXLOGAccess isn't called at backend startup). Reported by Michael Paquier http://git.postgresql.org/pg/commitdiff/afeacd274830d1e3d15ffc4e4d4b08a887f76abb

Tom Lane a poussé :

  • Allow simplification of EXISTS() subqueries containing LIMIT. The locution "EXISTS(SELECT ... LIMIT 1)" seems to be rather common among people who don't realize that the database already performs optimizations equivalent to putting LIMIT 1 in the sub-select. Unfortunately, this was actually making things worse, because it prevented us from optimizing such EXISTS clauses into semi or anti joins. Teach simplify_EXISTS_query() to suppress constant-positive LIMIT clauses. That fixes the semi/anti-join case, and may help marginally even for cases that have to be left as sub-SELECTs. Marti Raudsepp, reviewed by David Rowley http://git.postgresql.org/pg/commitdiff/b62f94c60386796fd88256c5b7b1e8301c345166
  • Support arrays as input to array_agg() and ARRAY(SELECT ...). These cases formerly failed with errors about "could not find array type for data type". Now they yield arrays of the same element type and one higher dimension. The implementation involves creating functions with API similar to the existing accumArrayResult() family. I (tgl) also extended the base family by adding an initArrayResult() function, which allows callers to avoid special-casing the zero-inputs case if they just want an empty array as result. (Not all do, so the previous calling convention remains valid.) This allowed simplifying some existing code in xml.c and plperl.c. Ali Akbar, reviewed by Pavel Stehule, significantly modified by me http://git.postgresql.org/pg/commitdiff/bac27394a1c69c20ec904729c593e59485c75c69
  • De-reserve most statement-introducing keywords in plpgsql. Add a bit of context sensitivity to plpgsql_yylex() so that it can recognize when the word it is looking at is the first word of a new statement, and if so whether it is the target of an assignment statement. When we are at start of statement and it's not an assignment, we can prefer recognizing unreserved keywords over recognizing variable names, thereby allowing most statements' initial keywords to be demoted from reserved to unreserved status. This is rather useful already (there are 15 such words that get demoted here), and what's more to the point is that future patches proposing to add new plpgsql statements can avoid objections about having to add new reserved words. The keywords BEGIN, DECLARE, FOR, FOREACH, LOOP, WHILE need to remain reserved because they can be preceded by block labels, and the logic added here doesn't understand about block labels. In principle we could probably fix that, but it would take more than one token of lookback and the benefit doesn't seem worth extra complexity. Also note I didn't de-reserve EXECUTE, because it is used in more places than just statement start. It's possible it could be de-reserved with more work, but that would be an independent fix. In passing, also de-reserve COLLATE and DEFAULT, which shouldn't have been reserved in the first place since they only need to be recognized within DECLARE sections. http://git.postgresql.org/pg/commitdiff/bb1b8f694ad2efc35ebae2acfa2c18a2197b82a1
  • Fix uninitialized-variable warning. In passing, add an Assert defending the presumption that bytes_left is positive to start with. (I'm not exactly convinced that using an unsigned type was such a bright thing here, but let's at least do this much.) http://git.postgresql.org/pg/commitdiff/d934a052348c1fa017ff677d08e74c643e3f416c
  • Free libxml2/libxslt resources in a safer order. Mark Simonetti reported that libxslt sometimes crashes for him, and that swapping xslt_process's object-freeing calls around to do them in reverse order of creation seemed to fix it. I've not reproduced the crash, but valgrind clearly shows a reference to already-freed memory, which is consistent with the idea that shutdown of the xsltTransformContext is trying to reference the already-freed stylesheet or input document. With this patch, valgrind is no longer unhappy. I have an inquiry in to see if this is a libxslt bug or if we're just abusing the library; but even if it's a library bug, we'd want to adjust our code so it doesn't fail with unpatched libraries. Back-patch to all supported branches, because we've been doing this in the wrong(?) order for a long time. http://git.postgresql.org/pg/commitdiff/c168ba311228c523923f7ef7699cba96deedcc6d
  • Improve typcache: cache negative lookup results, add invalidation logic. Previously, if the typcache had for example tried and failed to find a hash opclass for a given data type, it would nonetheless repeat the unsuccessful catalog lookup each time it was asked again. This can lead to a significant amount of useless bufmgr traffic, as in a recent report from Scott Marlowe. Like the catalog caches, typcache should be able to cache negative results. This patch arranges that by making use of separate flag bits to remember whether a particular item has been looked up, rather than treating a zero OID as an indicator that no lookup has been done. Also, install a credible invalidation mechanism, namely watching for inval events in pg_opclass. The sole advantage of the lack of negative caching was that the code would cope if operators or opclasses got added for a type mid-session; to preserve that behavior we have to be able to invalidate stale lookup results. Updates in pg_opclass should be pretty rare in production systems, so it seems sufficient to just invalidate all the dependent data whenever one happens. Adding proper invalidation also means that this code will now react sanely if an opclass is dropped mid-session. Arguably, that's a back-patchable bug fix, but in view of the lack of complaints from the field I'll refrain from back-patching. (Probably, in most cases where an opclass is dropped, the data type itself is dropped soon after, so that this misfeasance has no bad consequences.) http://git.postgresql.org/pg/commitdiff/e384ed6cdec691e0f7c9a077d0fb2a357763c335
  • Improve performance of OverrideSearchPathMatchesCurrent(). This function was initially coded on the assumption that it would not be performance-critical, but that turns out to be wrong in workloads that are heavily dependent on the speed of plpgsql functions. Speed it up by hard-coding the comparison rules, thereby avoiding palloc/pfree traffic from creating and immediately freeing an OverrideSearchPath object. Per report from Scott Marlowe. http://git.postgresql.org/pg/commitdiff/96d66bcfc60d9bcb7db767f23d33abf4d8bc7021
  • Add bms_next_member(), and use it where appropriate. This patch adds a way of iterating through the members of a bitmapset nondestructively, unlike the old way with bms_first_member(). While bms_next_member() is very slightly slower than bms_first_member() (at least for typical-size bitmapsets), eliminating the need to palloc and pfree a temporary copy of the target bitmapset is a significant win. So this method should be preferred in all cases where a temporary copy would be necessary. Tom Lane, with suggestions from Dean Rasheed and David Rowley http://git.postgresql.org/pg/commitdiff/f4e031c662a6b600b786c4849968a099c58fcce7
  • Add bms_get_singleton_member(), and use it where appropriate. This patch adds a function that replaces a bms_membership() test followed by a bms_singleton_member() call, performing both the test and the extraction of a singleton set's member in one scan of the bitmapset. The performance advantage over the old way is probably minimal in current usage, but it seems worthwhile on notational grounds anyway. David Rowley http://git.postgresql.org/pg/commitdiff/d25367ec4f869aac80e97964fa5d7143536818b1
  • Fix minor bugs in commit 30bf4689a96cd283af33edcdd6b7210df3f20cd8 et al. Coverity complained that the "else" added to fillPGconn() was unreachable, which it was. Remove the dead code. In passing, rearrange the tests so as not to bother trying to fetch values for options that can't be assigned. Pre-9.3 did not have that issue, but it did have a "return" that should be "goto oom_error" to ensure that a suitable error message gets filled in. http://git.postgresql.org/pg/commitdiff/1adbb347ec6ca059168a1866357ca7a23d117272

Stephen Frost a poussé :

  • Suppress DROP CASCADE notices in regression tests. In the regression tests, when doing cascaded drops, we need to suppress the notices from DROP CASCADE or there can be transient regression failures as the order of drops can depend on the physical row order in pg_depend. Report and fix suggestion from Tom. http://git.postgresql.org/pg/commitdiff/81d815dc3ed74a7d20f7b4c7d87c42816c7ee7af
  • Add int64 -> int8 mapping to genbki. Per discussion with Tom and Andrew, 64bit integers are no longer a problem for the catalogs, so go ahead and add the mapping from the C int64 type to the int8 SQL identification to allow using them. Patch by Adam Brightwell http://git.postgresql.org/pg/commitdiff/25976710dfd8611d3fc79c0c1e20179ff7a940ec
  • Rename pg_rowsecurity -> pg_policy and other fixes As pointed out by Robert, we should really have named pg_rowsecurity pg_policy, as the objects stored in that catalog are policies. This patch fixes that and updates the column names to start with 'pol' to match the new catalog name. The security consideration for COPY with row level security, also pointed out by Robert, has also been addressed by remembering and re-checking the OID of the relation initially referenced during COPY processing, to make sure it hasn't changed under us by the time we finish planning out the query which has been built. Robert and Alvaro also commented on missing OCLASS and OBJECT entries for POLICY (formerly ROWSECURITY or POLICY, depending) in various places. This patch fixes that too, which also happens to add the ability to COMMENT on policies. In passing, attempt to improve the consistency of messages, comments, and documentation as well. This removes various incarnations of 'row-security', 'row-level security', 'Row-security', etc, in favor of 'policy', 'row level security' or 'row_security' as appropriate. Happy Thanksgiving! http://git.postgresql.org/pg/commitdiff/143b39c1855f8a22f474f20354ee5ee5d2f4d266

Simon Riggs a poussé :

Robert Haas a poussé :

Peter Eisentraut a poussé :

Fujii Masao a poussé :

Álvaro Herrera a poussé :

Noah Misch 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 doing vacuumdb in parallel.
  • Amit Langote sent in a patch to fix a typo in a comment in src/bin/pg_dump.c.
  • Álvaro Herrera sent in another revision of a patch to track commit timestamps.
  • Michael Paquier and Rahila Syed traded patches to improve compression of full-page writes.
  • Adam Brightwell sent in two more revisions of a patch to implement role attribute bitmask catalog representation.
  • Ali Akbar sent in another revision of a patch to fix xpath() to return namespace definitions.
  • Marius Timmer sent in a patch to add an option to EXPLAIN to include sort order information in VERBOSE mode.
  • Michael Paquier sent in two revisions of a patch to allow compiling C++ extensions on MSVC using scripts in src/tools.
  • Ants Aasma sent in a patch to use RTLD_DEEPBIND to handle symbol conflicts in loaded libraries.
  • Pavel Stehule sent in another revision of a patch to add an ASSERT statement to PL/pgsql.
  • Peter Geoghegan sent in another revision of a patch to implement INSERT ... ON CONFLICT {IGNORE | UPDATE}.
  • Alexander Shulgin sent in another revision of a patch to add an 'ssl_protocols' configuration option.
  • Álvaro Herrera sent in a flock of patches to move test modules from contrib to src/test/modules.
  • Mats Erik Andersson sent in a patch to improve error recovery in initdb when the password file is empty.
  • Mats Erik Andersson sent in a patch to localize the text response of pg_isready.
  • Ian Lawrence Barwick sent in a patch to implement DDL deparsing.
  • Michael Paquier sent in another revision of a patch to implement fillfactor for GIN indexes.
  • Alexander Shulgin sent in a patch to allow TOAST tables to be moved to a different tablespace.
  • Peter Eisentraut sent in a patch to allow using Core Foundation locale functions on OSX.
  • Tom Lane sent in a patch to fix some misleading error messages in json_to_record.
  • Michael Paquier sent in a patch to add a new error code to track unsupported contexts.
  • Tomas Vondra sent in another revision of a patch to lower array_agg's memory requirements.
  • Stephen Frost sent in another revision of a patch to clean up GetUserId() for pg_stat and pg_signal.
  • Noah Misch sent in another revision of a patch to help secure "make check".
  • David Rowley sent in a patch to allow removing inner JOINs in some cases.
  • Andrew Dunstan sent in a patch to fix the check for whether something is considered a number in JSON.
  • Ian Lawrence Barwick sent in a patch to fix a typo/spacing problem in doc/src/sgml/wal.sgml.

par N Bougain le lundi 1 décembre 2014 à 22h03

mardi 25 novembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 23 novembre 2014

PostgreSQL 9.4RC1 disponible. Test ! https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4

L'appel à conférenciers pour le PGDay et la Devroom du FOSDEM 2015 est lancé : http://fosdem2015.pgconf.eu/callforpapers/

La pgcon japonaise 2014 aura lieu le 5 décembre à Shinagawa : http://www.postgresql.jp/events/jpug-pgcon2014

pgDaySF 2015 aura lieu le 10 mars 2015 à Burlingame (Californie) : http://sfpostgres.org/pgday-sf-2015-call-for-speakers-and-sponsors/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en novembre

PostgreSQL Local

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA.

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

(lien vers l'article original)

Correctifs appliqués

Álvaro Herrera a poussé :

  • Fix relpersistence setting in reindex_index. Buildfarm members with CLOBBER_CACHE_ALWAYS advised us that commit 85b506bbfc2937 was mistaken in setting the relpersistence value of the index directly in the relcache entry, within reindex_index. The reason for the failure is that an invalidation message that comes after mucking with the relcache entry directly, but before writing it to the catalogs, would cause the entry to become rebuilt in place from catalogs with the old contents, losing the update. Fix by passing the correct persistence value to RelationSetNewRelfilenode instead; this routine also writes the updated tuple to pg_class, avoiding the problem. Suggested by Tom Lane. http://git.postgresql.org/pg/commitdiff/0f9692b40d1292f1b2640f026561908fd37b7407

Heikki Linnakangas a poussé :

  • Fix WAL-logging of B-tree "unlink halfdead page" operation. There was some confusion on how to record the case that the operation unlinks the last non-leaf page in the branch being deleted. _bt_unlink_halfdead_page set the "topdead" field in the WAL record to the leaf page, but the redo routine assumed that it would be an invalid block number in that case. This commit fixes _bt_unlink_halfdead_page to do what the redo routine expected. This code is new in 9.4, so backpatch there. http://git.postgresql.org/pg/commitdiff/c73669c0e0168923e3f9e787beec980f55af2bd8
  • Remove obsolete debugging option, RTDEBUG. The r-tree AM that used it was removed back in 2005. Peter Geoghegan http://git.postgresql.org/pg/commitdiff/dedae6c211415290cdf5faca4ef874f803af204c
  • Add test cases for indexam operations not currently covered. That includes VACUUM on GIN, GiST and SP-GiST indexes, and B-tree indexes large enough to cause page deletions in B-tree. Plus some other special cases. After this patch, the regression tests generate all different WAL record types. Not all branches within the redo functions are covered, but it's a step forward. http://git.postgresql.org/pg/commitdiff/88fc71926392115cdc3672807f3903ce43d0ebcf
  • Silence compiler warning about variable being used uninitialized. It's a false positive - the variable is only used when 'onleft' is true, and it is initialized in that case. But the compiler doesn't necessarily see that. http://git.postgresql.org/pg/commitdiff/f464042161fb980578c33fedc2742c5a4d941fa2
  • Revamp the WAL record format. Each WAL record now carries information about the modified relation and block(s) in a standardized format. That makes it easier to write tools that need that information, like pg_rewind, prefetching the blocks to speed up recovery, etc. There's a whole new API for building WAL records, replacing the XLogRecData chains used previously. The new API consists of XLogRegister* functions, which are called for each buffer and chunk of data that is added to the record. The new API also gives more control over when a full-page image is written, by passing flags to the XLogRegisterBuffer function. This also simplifies the XLogReadBufferForRedo() calls. The function can dig the relation and block number from the WAL record, so they no longer need to be passed as arguments. For the convenience of redo routines, XLogReader now disects each WAL record after reading it, copying the main data part and the per-block data into MAXALIGNed buffers. The data chunks are not aligned within the WAL record, but the redo routines can assume that the pointers returned by XLogRecGet* functions are. Redo routines are now passed the XLogReaderState, which contains the record in the already-disected format, instead of the plain XLogRecord. The new record format also makes the fixed size XLogRecord header smaller, by removing the xl_len field. The length of the "main data" portion is now stored at the end of the WAL record, and there's a separate header after XLogRecord for it. The alignment padding at the end of XLogRecord is also removed. This compansates for the fact that the new format would otherwise be more bulky than the old format. Reviewed by Andres Freund, Amit Kapila, Michael Paquier, Alvaro Herrera, Fujii Masao. http://git.postgresql.org/pg/commitdiff/2c03216d831160bedd72d45f712601b6f7d03f1c
  • Add pageinspect functions for inspecting GIN indexes. Patch by me, Peter Geoghegan and Michael Paquier, reviewed by Amit Kapila. http://git.postgresql.org/pg/commitdiff/3a82bc6f8ab38be3ed095f1c86866900b145f0f6
  • Fix bogus comments in XLogRecordAssemble. Pointed out by Michael Paquier http://git.postgresql.org/pg/commitdiff/8f5dcb56cb1877f7ed6b0f3f72cc26c4e060aa61
  • Add a comment to regress.c explaining what it contains. Ian Barwick http://git.postgresql.org/pg/commitdiff/b10a97b819a2594c97188bac02d87b550908a692
  • No need to call XLogEnsureRecordSpace when the relation is unlogged. Amit Kapila http://git.postgresql.org/pg/commitdiff/622983ea6964fc55f1ad3063d3d7b8f5de8e8816

Tom Lane a poussé :

  • Update time zone data files to tzdata release 2014j. DST law changes in the Turks & Caicos Islands (America/Grand_Turk) and in Fiji. New zone Pacific/Bougainville for portions of Papua New Guinea. Historical changes for Korea and Vietnam. http://git.postgresql.org/pg/commitdiff/bc241488b0f9bde3e14094bfaf0d7987fafb6600
  • Update 9.4 release notes for commits through today. http://git.postgresql.org/pg/commitdiff/832054044f68080eaebccd771e21fdd56824db20
  • Fix some bogus direct uses of realloc(). pg_dump/parallel.c was using realloc() directly with no error check. While the odds of an actual failure here seem pretty low, Coverity complains about it, so fix by using pg_realloc() instead. While looking for other instances, I noticed a couple of places in psql that hadn't gotten the memo about the availability of pg_realloc. These aren't bugs, since they did have error checks, but verbosely inconsistent code is not a good thing. Back-patch as far as 9.3. 9.2 did not have pg_dump/parallel.c, nor did it have pg_realloc available in all frontend code. http://git.postgresql.org/pg/commitdiff/8b13e5c6c0e8a6b797370fb91d207031df5e784a
  • Update comments in find_typedef. These comments don't seem to have been touched in a long time. Make them describe the current implementation rather than what was here last century, and be a bit more explicit about the unreferenced-typedefs issue. http://git.postgresql.org/pg/commitdiff/7aa8d9e56c18b1df9d924f144c06d921131a137e
  • Don't require bleeding-edge timezone data in timestamptz regression test. The regression test cases added in commits b2cbced9e et al depended in part on the Russian timezone offset changes of Oct 2014. While this is of no particular concern for a default Postgres build, it was possible for a build using --with-system-tzdata to fail the tests if the system tzdata database wasn't au courant. Bjorn Munch and Christoph Berg both complained about this while packaging 9.4rc1, so we probably shouldn't insist on the system tzdata being up-to-date. Instead, make an equivalent test using a zone change that occurred in Venezuela in 2007. With this patch, the regression tests should pass using any tzdata set from 2012 or later. (I can't muster much sympathy for somebody using --with-system-tzdata on a machine whose system tzdata is more than three years out-of-date.) http://git.postgresql.org/pg/commitdiff/8d7af8fbe7349cbebb576459c9b5f54dfcc6216a
  • Improve documentation's description of JOIN clauses. In bug #12000, Andreas Kunert complained that the documentation was misleading in saying "FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2". That's correct as far as it goes, but the equivalence doesn't hold when you consider three or more tables, since JOIN binds more tightly than comma. I added a <note> to explain this, and ended up rearranging some of the existing text so that the note would make sense in context. In passing, rewrite the description of JOIN USING, which was unnecessarily vague, and hadn't been helped any by somebody's reliance on markup as a substitute for clear writing. (Mostly this involved reintroducing a concrete example that was unaccountably removed by commit 032f3b7e166cfa28.) Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/8372304e3594a1d67737df779f098d9ae9937603
  • Add missing case for CustomScan. Per KaiGai Kohei. In passing improve formatting of some code added in commit 30d7ae3c, because otherwise pgindent will make a mess of it. http://git.postgresql.org/pg/commitdiff/f9e0255c6fbedda50aae6aa7479f0c5a8f90b831
  • Remove no-longer-needed phony typedefs in genbki.h. Now that we have a policy of hiding varlena catalog fields behind "#ifdef CATALOG_VARLEN", there is no need for their type names to be acceptable to the C compiler. And experimentation shows that it does not matter to pgindent either. (If it did, we'd have problems anyway, since these typedefs are unreferenced so far as the C compiler is concerned, and find_typedef fails to identify such typedefs.) Hence, remove the phony typedefs that genbki.h provided to make some varlena field definitions compilable. In passing, rearrange #define's into what seemed a more logical order. http://git.postgresql.org/pg/commitdiff/c5111ea9ca049a650b63cdcb2551c330c2f998b1
  • Fix another oversight in CustomScan patch. disuse_physical_tlist() must work for all plan types handled by create_scan_plan(). http://git.postgresql.org/pg/commitdiff/03e574af5f5d4682ce3ae6dc401ba126c70ce2ea
  • Fix another oversight in CustomScan patch. execCurrent.c's search_plan_tree() must recognize a CustomScan on the target relation. This would only be helpful for custom providers that support CurrentOfExpr quals, which is probably a bit far-fetched, but it's not impossible I think. But even without assuming that, we need to recognize a scanned-relation match so that we will properly throw error if the desired relation is being scanned with both a CustomScan and a regular scan (ie, self-join). Also recognize ForeignScanState for similar reasons. Supporting WHERE CURRENT OF on a foreign table is probably even more far-fetched than it is for custom scans, but I think in principle you could do it with postgres_fdw (or another FDW that supports the ctid column). This would be a back-patchable bug fix if existing FDWs handled CurrentOfExpr, but I doubt any do so I won't bother back-patching. http://git.postgresql.org/pg/commitdiff/081a6048cff07a83591ebcb08b676a771ae58d2b
  • Initial code review for CustomScan patch. Get rid of the pernicious entanglement between planner and executor headers introduced by commit 0b03e5951bf0a1a8868db13f02049cf686a82165. Also, rearrange the CustomFoo struct/typedef definitions so that all the typedef names are seen as used by the compiler. Without this pgindent will mess things up a bit, which is not so important perhaps, but it also removes a bizarre discrepancy between the declaration arrangement used for CustomExecMethods and that used for CustomScanMethods and CustomPathMethods. Clean up the commentary around ExecSupportsMarkRestore to reflect the rather large change in its API. Const-ify register_custom_path_provider's argument. This necessitates casting away const in the function, but that seems better than forcing callers of the function to do so (or else not const-ify their method pointer structs, which was sort of the whole point). De-export fix_expr_common. I don't like the exporting of fix_scan_expr or replace_nestloop_params either, but this one surely has got little excuse. http://git.postgresql.org/pg/commitdiff/a34fa8ee7cc757671632dc4dcae4f21e8f2e2357
  • Remove dead code supporting mark/restore in SeqScan, TidScan, ValuesScan. There seems no prospect that any of this will ever be useful, and indeed it's questionable whether some of it would work if it ever got called; it's certainly not been exercised in a very long time, if ever. So let's get rid of it, and make the comments about mark/restore in execAmi.c less wishy-washy. The mark/restore support for Result nodes is also currently dead code, but that's due to planner limitations not because it's impossible that it could be useful. So I left it in. http://git.postgresql.org/pg/commitdiff/adbfab119b308a7e0e6b1305de9be222cfd5c85b
  • Simplify API for initially hooking custom-path providers into the planner. Instead of register_custom_path_provider and a CreateCustomScanPath callback, let's just provide a standard function hook in set_rel_pathlist. This is more flexible than what was previously committed, is more like the usual conventions for planner hooks, and requires less support code in the core. We had discussed this design (including centralizing the set_cheapest() calls) back in March or so, so I'm not sure why it wasn't done like this already. http://git.postgresql.org/pg/commitdiff/c2ea2285e978d9289084846a3343cef7d261d880
  • Rearrange CustomScan API. Make it work more like FDW plans do: instead of assuming that there are expressions in a CustomScan plan node that the core code doesn't know about, insist that all subexpressions that need planner attention be in a "custom_exprs" list in the Plan representation. (Of course, the custom plugin can break the list apart again at executor initialization.) This lets us revert the parts of the patch that exposed setrefs.c and subselect.c processing to the outside world. Also revert the GetSpecialCustomVar stuff in ruleutils.c; that concept may work in future, but it's far from fully baked right now. http://git.postgresql.org/pg/commitdiff/447770404cce5ce86174fa4809784c4e5d0a0a76
  • Fix mishandling of system columns in FDW queries. postgres_fdw would send query conditions involving system columns to the remote server, even though it makes no effort to ensure that system columns other than CTID match what the remote side thinks. tableoid, in particular, probably won't match and might have some use in queries. Hence, prevent sending conditions that include non-CTID system columns. Also, create_foreignscan_plan neglected to check local restriction conditions while determining whether to set fsSystemCol for a foreign scan plan node. This again would bollix the results for queries that test a foreign table's tableoid. Back-patch the first fix to 9.3 where postgres_fdw was introduced. Back-patch the second to 9.2. The code is probably broken in 9.1 as well, but the patch doesn't apply cleanly there; given the weak state of support for FDWs in 9.1, it doesn't seem worth fixing. Etsuro Fujita, reviewed by Ashutosh Bapat, and somewhat modified by me http://git.postgresql.org/pg/commitdiff/9c58101117d25f174c8d8013befdc33c632922d0
  • Get rid of redundant production in plpgsql grammar. There may once have been a reason for the intermediate proc_stmts production in the plpgsql grammar, but it isn't doing anything useful anymore, so let's collapse it into proc_sect. Saves some code and probably a small number of nanoseconds per statement list. In passing, correctly alphabetize keyword lists to match pl_scanner.c; note that for "rowtype" vs "row_count", pl_scanner.c must sort on the basis of the lower-case spelling. Noted while fooling with a patch to de-reserve more plpgsql keywords. http://git.postgresql.org/pg/commitdiff/e2dc3f57726199408902ba87eaaf7b95d1223744

Fujii Masao a poussé :

  • Add --synchronous option to pg_receivexlog, for more reliable WAL writing. Previously pg_receivexlog flushed WAL data only when WAL file was switched. Then 3dad73e added -F option to pg_receivexlog so that users could control how frequently sync commands were issued to WAL files. It also allowed users to make pg_receivexlog flush WAL data immediately after writing by specifying 0 in -F option. However feedback messages were not sent back immediately even after a flush location was updated. So even if WAL data was flushed in real time, the server could not see that for a while. This commit removes -F option from and adds --synchronous to pg_receivexlog. If --synchronous is specified, like the standby's wal receiver, pg_receivexlog flushes WAL data as soon as there is WAL data which has not been flushed yet. Then it sends back the feedback message identifying the latest flush location to the server. This option is useful to make pg_receivexlog behave as sync standby by using replication slot, for example. Original patch by Furuya Osamu, heavily rewritten by me. Reviewed by Heikki Linnakangas, Alvaro Herrera and Sawada Masahiko. http://git.postgresql.org/pg/commitdiff/c4f99d20294950576d552dcaf9ce5b9bdc4233a3
  • Fix pg_receivexlog --slot so that it doesn't prevent the server shutdown. When pg_receivexlog --slot is connecting to the server, at the shutdown of the server, walsender keeps waiting for the last WAL record to be replicated and flushed in pg_receivexlog. But previously pg_receivexlog issued sync command only when WAL file was switched. So there was the case where the last WAL was never flushed and walsender had to keep waiting infinitely. This caused the server shutdown to get stuck. pg_recvlogical handles this problem by calling fsync() when it receives the request of immediate reply from the server. That is, at shutdown, walsender sends the request, pg_recvlogical receives it, flushes the last WAL record, and sends the flush location back to the server. Since walsender can see that the last WAL record is successfully flushed, it can exit cleanly. This commit introduces the same logic as pg_recvlogical has, to pg_receivexlog. Back-patch to 9.4 where pg_receivexlog was changed so that it can use the replication slot. Original patch by Michael Paquier, rewritten by me. Bug report by Furuya Osamu. http://git.postgresql.org/pg/commitdiff/f66c20b317578838a39a1de8014c4363bdc98b9a
  • Fix bug in the test of file descriptor of current WAL file in pg_receivexlog. In pg_receivexlog, in order to check whether the current WAL file is being opened or not, its file descriptor has to be checked against -1 as an invalid value. But, oops, 7900e94 added the incorrect test checking the descriptor against 1. This commit fixes that bug. Back-patch to 9.4 where the bug was added. Spotted by Magnus Hagander http://git.postgresql.org/pg/commitdiff/d5f4df7264ec73abda41eb78b81cf430b33f7606

Simon Riggs a poussé :

  • Add pg_dump --snapshot option. Allows pg_dump to use a snapshot previously defined by a concurrent session that has either used pg_export_snapshot() or obtained a snapshot when creating a logical slot. When this option is used with parallel pg_dump, the snapshot defined by this option is used and no new snapshot is taken. Simon Riggs and Michael Paquier http://git.postgresql.org/pg/commitdiff/be1cc8f46f57a04e69d9e4dd268d34da885fe6eb
  • Reduce btree scan overhead for < and > strategies. For <, <=, > and >= strategies, mark the first scan key as already matched if scanning in an appropriate direction. If index tuple contains no nulls we can skip the first Author: Rajeev Rastogi. Reviewer: Haribabu Kommi. Rework of the code and comments by Simon Riggs http://git.postgresql.org/pg/commitdiff/606c0123d627b37d5ac3f7c2c97cd715dde7842f

Robert Haas a poussé :

Peter Eisentraut a poussé :

Andrew Dunstan a poussé :

Noah Misch a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Etsuro Fujita sent in another revision of a patch to allow foreign tables to participate in table inheritance hierarchies.
  • Jeff Davis and Tomas Vondra traded patches to do better memory accounting, in part as infrastructure for a memory-bounded HashAgg.
  • Michael Paquier sent in a patch to fix CLOBBER_CACHE_ALWAYS.
  • Michael Paquier sent in another revision of a patch to track number of files marked as ready for archiving in pg_stat_archiver.
  • SAWADA Masahiko sent in another revision of a patch to implement REINDEX SCHEMA, in this case as an option to reindexdb.
  • Heikki Linnakangas sent in two PoC patches to deal with a performance problem in PageRepairFragmentation.
  • Petr (PJMODOS) Jelinek and Simon Riggs traded patches to add a shutdown_at_recovery_target option to recovery.conf.
  • Dimitri Fontaine sent in three more revisions of a patch to add "table rewrite" as an event which can fire an event trigger.
  • Adam Brightwell sent in a patch to add int64-to-int8 mapping in Catalog.pm.
  • Magnus Hagander sent in another revision of a patch to add a pg_stat_ssl view.
  • Aaron W. Swenson sent in a patch to replace configure.in with configure dot ac.
  • Heikki Linnakangas sent in a patch to add some more tests for WALs.
  • Peter Geoghegan sent in two revisions of a patch to create an amcheck utility, a generalization of his previous btreecheck.
  • Michael Paquier sent in a patch to uppercase some SQL keywords in the BRIN documents.
  • Amit Kapila sent in another revision of a patch to fix some infelicities between pg_basebackup and Windows.
  • Peter Eisentraut sent in a series of patches which: Fix SHLIB_PREREQS use in contrib, allowing PGXS builds, allow the user to set VPATH directly, and clean up some makefile changes that weren't needed.
  • Abhijit Menon-Sen sent in a patch to use slice-by-8 for CRC, which increased the performance of that operation by a factor of 4+.
  • Adam Brightwell sent in another revision of a patch to fix Catalog.pm for role attributes.
  • Laurenz Albe sent in a patch to disallow changing functions which are part of an index definition.
  • Michael Paquier sent in a patch to enable GIN code managing entry insertion not to distinguish fresh from old indexes.
  • Robert Haas sent in another revision of a patch to add infrastructure to save and restore GUC values.
  • Michael Paquier sent in a patch to add fillfactor as storage parameter for GIN indexes.
  • Petr (PJMODOS) Jelinek sent in two more revisions of a patch to track commit timestamps.
  • Adam Brightwell sent in another revision of a patch to clean up superuser shortcuts.
  • Alexander Shulgin sent in another revision of a patch to turn recovery.conf into GUCs.
  • Álvaro Herrera added some opfamilies for BRIN indexes which should help make them more useful.
  • Marko (johto) Tiikkaja sent in two revisions of a patch to make the HINTs more useful for certain types common mistakes in UPDATE.
  • Amit Kapila sent in another revision of a patch to parallelize vacuumdb.
  • David Rowley sent in another revision of a patch to support SEMI and ANTI join removal in certain cases.
  • Tom Lane sent in a patch to remove 17 of PL/pgsql's reserved words.

par N Bougain le mardi 25 novembre 2014 à 00h35

vendredi 21 novembre 2014

Actualités PostgreSQL.fr

Publication de PostgreSQL 9.4 RC1

Le PostgreSQL Global Development Group a publié la version 9.4 RC1.

Il s'agit de la première Release Candidate pour la prochaine version de PostgreSQL.
Cette version devrait être identique à la version finale de PostgreSQL 9.4, à l'exception des éventuels bogues rencontrés dans les deux prochaines semaines.

Merci de la télécharger et de la tester, et au besoin de rapporter les failles rencontrées.

La liste complète des fonctionnalités de la version 9.4 est disponible sur la page des notes de version : http://www.postgresql.org/docs/devel/static/release-9-4.html.

Les descriptions détaillées et les notes complémentaires concernant les nouvelles fonctionnalités sont disponibles sur la page Wiki des fonctionnalités de la version 9.4 : http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4

Nous dépendons des tests de la communauté pour garantir que cette version est réellement performante et exempte de bogues.
Merci de télécharger la RC1 et de la tester dès que possible en conditions opérationnelles réelles. Vos retours et critiques sont indispensables aux développeurs.
Les fonctionnalités et API de cette RC1 sont identiques à la 9.4.0 finale, ce qui permet de construire et tester les applications avec celles-là.

Plus d'informations sur la façon de tester et de rapporter les problèmes sur la page suivante :
http://www.postgresql.org/developer/beta.

PostgreSQL 9.4 RC1 peut être obtenu en se rendant sur la page :
http://www.postgresql.org/download
Cette page inclut les binaires et installeurs pour Windows, Linux et Mac.

La documentation complète de la nouvelle version est disponible en ligne :
http://www.postgresql.org/docs/devel/static, et s'installe également avec PostgreSQL.

par SAS le vendredi 21 novembre 2014 à 11h39

mardi 18 novembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 16 novembre 2014

L'appel à conférenciers pour le Nordic PostgreSQL Day 2015, prévu le 11 mars 2015 à Copenhague (Danemark), est lancé : http://2015.nordicpgday.org/cfp/

Offres d'emplois autour de PostgreSQL en novembre

PostgreSQL Local

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA.

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

(lien vers l'article original)

Correctifs appliqués

Bruce Momjian a poussé :

Robert Haas a poussé :

Kevin Grittner a poussé :

Álvaro Herrera a poussé :

  • Further code and wording tweaks in BRIN. Besides a couple of typo fixes, per David Rowley, Thom Brown, and Amit Langote, and mentions of BRIN in the general CREATE INDEX page again per David, this includes silencing MSVC compiler warnings (thanks Microsoft) and an additional variable initialization per Coverity scanner. http://git.postgresql.org/pg/commitdiff/1e0b4365c22c9f8a1bc7a5f8339f770c767b402f
  • BRIN: fix bug in xlog backup block counting. The code that generates the BRIN_XLOG_UPDATE removes the buffer reference when the page that's target for the updated tuple is freshly initialized. This is a pretty usual optimization, but was breaking the case where the revmap buffer, which is referenced in the same WAL record, is getting a backup block: the replay code was using backup block index 1, which is not valid when the update target buffer gets pruned; the revmap buffer gets assigned 0 instead. Make sure to use the correct backup block index for revmap when replaying. Bug reported by Fujii Masao. http://git.postgresql.org/pg/commitdiff/a590f266e44c492d2a252ab9dee0cd88dbe06dc5
  • Tweak row-level locking documentation. Move the meat of locking levels to mvcc.sgml, leaving only a link to it in the SELECT reference page. Michael Paquier, with some tweaks by Álvaro http://git.postgresql.org/pg/commitdiff/35fed51626328a3ff54adae4749bef956e1e1099
  • Allow interrupting GetMultiXactIdMembers. This function has a loop which can lead to uninterruptible process "stalls" (actually infinite loops) when some bugs are triggered. Avoid that unpleasant situation by adding a check for interrupts in a place that shouldn't degrade performance in the normal case. Backpatch to 9.3. Older branches have an identical loop here, but the aforementioned bugs are only a problem starting in 9.3 so there doesn't seem to be any point in backpatching any further. http://git.postgresql.org/pg/commitdiff/51f9ea25dc4296948eb851a851bb60e0860c70a1
  • Reduce disk footprint of brin regression test. Per complaint from Tom Lane. While at it, throw in some extra tests for nulls as well, and make sure that the set of data we insert on the second round is not identical to the first one. Both measures are intended to improve coverage of the test. Also uncomment the ON COMMIT DROP clause on the CREATE TEMP TABLE commands. This doesn't have any effect for someone examining the regression database after the tests are done, but it reduces clutter for those that execute the script directly. http://git.postgresql.org/pg/commitdiff/86cf9a565069755189e08290343d2d62afdd1f52
  • Document BRIN's pages_per_range in CREATE INDEX. Author: Michael Paquier http://git.postgresql.org/pg/commitdiff/79172a58ea3e858750c93c61d55411a209c5ac69
  • Remove unused InhPaths. Allegedly, the last remaining usages of that struct were removed by 0e99be1c. Author: Peter Geoghegan http://git.postgresql.org/pg/commitdiff/e4d1e264918f4019c86c807e7c349658f7a10397
  • postgres_fdw.h: don't pull in rel.h when relcache.h is enough. http://git.postgresql.org/pg/commitdiff/f9ef578d050c509d0f4bc02b3c0a3a0497056a2a
  • Get rid of SET LOGGED indexes persistence kludge. This removes ATChangeIndexesPersistence() introduced by f41872d0c1239d36 which was too ugly to live for long. Instead, the correct persistence marking is passed all the way down to reindex_index, so that the transient relation built to contain the index relfilenode can get marked correctly right from the start. Author: Fabrízio de Royes Mello. Review and editorialization by Michael Paquier and Álvaro Herrera http://git.postgresql.org/pg/commitdiff/85b506bbfc2937c9abdfcce4e01a8feca8e64ee8

Tom Lane a poussé :

  • Ensure that RowExprs and whole-row Vars produce the expected column names. At one time it wasn't terribly important what column names were associated with the fields of a composite Datum, but since the introduction of operations like row_to_json(), it's important that looking up the rowtype ID embedded in the Datum returns the column names that users would expect. That did not work terribly well before this patch: you could get the column names of the underlying table, or column aliases from any level of the query, depending on minor details of the plan tree. You could even get totally empty field names, which is disastrous for cases like row_to_json(). To fix this for whole-row Vars, look to the RTE referenced by the Var, and make sure its column aliases are applied to the rowtype associated with the result Datums. This is a tad scary because we might have to return a transient RECORD type even though the Var is declared as having some named rowtype. In principle it should be all right because the record type will still be physically compatible with the named rowtype; but I had to weaken one Assert in ExecEvalConvertRowtype, and there might be third-party code containing similar assumptions. Similarly, RowExprs have to be willing to override the column names coming from a named composite result type and produce a RECORD when the column aliases visible at the site of the RowExpr differ from the underlying table's column names. In passing, revert the decision made in commit 398f70ec070fe601 to add an alias-list argument to ExecTypeFromExprList: better to provide that functionality in a separate function. This also reverts most of the code changes in d68581483564ec0f, which we don't need because we're no longer depending on the tupdesc found in the child plan node's result slot to be blessed. Back-patch to 9.4, but not earlier, since this solution changes the results in some cases that users might not have realized were buggy. We'll apply a more restricted form of this patch in older branches. http://git.postgresql.org/pg/commitdiff/bf7ca15875988a88e97302e012d7c4808bef3ea9
  • Fix dependency searching for case where column is visited before table. When the recursive search in dependency.c visits a column and then later visits the whole table containing the column, it needs to propagate the drop-context flags for the table to the existing target-object entry for the column. Otherwise we might refuse the DROP (if not CASCADE) on the incorrect grounds that there was no automatic drop pathway to the column. Remarkably, this has not been reported before, though it's possible at least when an extension creates both a datatype and a table using that datatype. Rather than just marking the column as allowed to be dropped, it might seem good to skip the DROP COLUMN step altogether, since the later DROP of the table will surely get the job done. The problem with that is that the datatype would then be dropped before the table (since the whole situation occurred because we visited the datatype, and then recursed to the dependent column, before visiting the table). That seems pretty risky, and the case is rare enough that it doesn't seem worth expending a lot of effort or risk to make the drops happen in a safe order. So we just play dumb and delete the column separately according to the existing drop ordering rules. Per report from Petr Jelinek, though this is different from his proposed patch. Back-patch to 9.1, where extensions were introduced. There's currently no evidence that such cases can arise before 9.1, and in any case we would also need to back-patch cb5c2ba2d82688d29b5902d86b993a54355cad4d to 9.0 if we wanted to back-patch this. http://git.postgresql.org/pg/commitdiff/2edfc021c64ac2b15c9e9f9ada037ad12150bfa9
  • Loop when necessary in contrib/pgcrypto's pktreader_pull(). This fixes a scenario in which pgp_sym_decrypt() failed with "Wrong key or corrupt data" on messages whose length is 6 less than a power of 2. Per bug #11905 from Connor Penhale. Fix by Marko Tiikkaja, regression test case from Jeff Janes. http://git.postgresql.org/pg/commitdiff/f2ad2bdd0abfb1ce8f274fbb01a82e209f4cc122
  • Explicitly support the case that a plancache's raw_parse_tree is NULL. This only happens if a client issues a Parse message with an empty query string, which is a bit odd; but since it is explicitly called out as legal by our FE/BE protocol spec, we'd probably better continue to allow it. Fix by adding tests everywhere that the raw_parse_tree field is passed to functions that don't or shouldn't accept NULL. Also make it clear in the relevant comments that NULL is an expected case. This reverts commits a73c9dbab0165b3395dfe8a44a7dfd16166963c4 and 2e9650cbcff8c8fb0d9ef807c73a44f241822eee, which fixed specific crash symptoms by hacking things at what now seems to be the wrong end, ie the callee functions. Making the callees allow NULL is superficially more robust, but it's not always true that there is a defensible thing for the callee to do in such cases. The caller has more context and is better able to decide what the empty-query case ought to do. Per followup discussion of bug #11335. Back-patch to 9.2. The code before that is sufficiently different that it would require development of a separate patch, which doesn't seem worthwhile for what is believed to be an essentially cosmetic change. http://git.postgresql.org/pg/commitdiff/677708032c4a4d37cdb2a4bd45726fc260308db7
  • Fix pg_dumpall to restore its ability to dump from ancient servers. Fix breakage induced by commits d8d3d2a4f37f6df5d0118b7f5211978cca22091a and 463f2625a5fb183b6a8925ccde98bb3889f921d9: pg_dumpall has crashed when attempting to dump from pre-8.1 servers since then, due to faulty construction of the query used for dumping roles from older servers. The query was erroneous as of the earlier commit, but it wasn't exposed unless you tried to use --binary-upgrade, which you presumably wouldn't with a pre-8.1 server. However commit 463f2625a made it fail always. In HEAD, also fix additional breakage induced in the same query by commit 491c029dbc4206779cf659aa0ff986af7831d2ff, which evidently wasn't tested against pre-8.1 servers either. The bug is only latent in 9.1 because 463f2625a hadn't landed yet, but it seems best to back-patch all branches containing the faulty query. Gilles Darold http://git.postgresql.org/pg/commitdiff/be09ceb21882507bff95d74bad0ebe25efc227bd
  • Document evaluation-order considerations for aggregate functions. The SELECT reference page didn't really address the question of when aggregate function evaluation occurs, nor did the "expression evaluation rules" documentation mention that CASE can't be used to control whether an aggregate gets evaluated or not. Improve that. Per discussion of bug #11661. Original text by Marti Raudsepp and Michael Paquier, rewritten significantly by me. http://git.postgresql.org/pg/commitdiff/0ce627d465f534a3234750a5b398664dfece4102

Heikki Linnakangas a poussé :

Fujii Masao a poussé :

  • Add GUC and storage parameter to set the maximum size of GIN pending list. Previously the maximum size of GIN pending list was controlled only by work_mem. But the reasonable value of work_mem and the reasonable size of the list are basically not the same, so it was not appropriate to control both of them by only one GUC, i.e., work_mem. This commit separates new GUC, pending_list_cleanup_size, from work_mem to allow users to control only the size of the list. Also this commit adds pending_list_cleanup_size as new storage parameter to allow users to specify the size of the list per index. This is useful, for example, when users want to increase the size of the list only for the GIN index which can be updated heavily, and decrease it otherwise. Reviewed by Etsuro Fujita. http://git.postgresql.org/pg/commitdiff/a1b395b6a26ae80cde17fdfd2def8d351872f399
  • Add generate_series(numeric, numeric). Платон Малюгин. Reviewed by Michael Paquier, Ali Akbar and Marti Raudsepp http://git.postgresql.org/pg/commitdiff/1871c892021055532344266d7429b63f76a892c2
  • Rename pending_list_cleanup_size to gin_pending_list_limit. Since this parameter is only for GIN index, it's better to add "gin" to the parameter name for easier understanding. http://git.postgresql.org/pg/commitdiff/c291503b1c8250c7ba6ca900b7ba2f85a64b1eb6

Peter Eisentraut a poussé :

Noah Misch a poussé :

Andres Freund a poussé :

  • Add interrupt checks to contrib/pg_prewarm. Currently the extension's pg_prewarm() function didn't check interrupts once it started "warming" data. Since individual calls can take a long while it's important for them to be interruptible. Backpatch to 9.4 where pg_prewarm was introduced. http://git.postgresql.org/pg/commitdiff/bd4ae0f396b36955c92f26c0058e7dc0452db817
  • Fix several weaknesses in slot and logical replication on-disk serialization. Heikki noticed in 544E23C0.8090605@vmware.com that slot.c and snapbuild.c were missing the FIN_CRC32 call when computing/checking checksums of on disk files. That doesn't lower the the error detection capabilities of the checksum, but is inconsistent with other usages. In a followup mail Heikki also noticed that, contrary to a comment, the 'version' and 'length' struct fields of replication slot's on disk data where not covered by the checksum. That's not likely to lead to actually missed corruption as those fields are cross checked with the expected version and the actual file length. But it's wrong nonetheless. As fixing these issues makes existing on disk files unreadable, bump the expected versions of on disk files for both slots and logical decoding historic catalog snapshots. This means that loading old files will fail with ERROR: "replication slot file ... has unsupported version 1" and ERROR: "snapbuild state file ... has unsupported version 1 instead of 2" respectively. Given the low likelihood of anybody already using these new features in a production setup that seems acceptable. Fixing these issues made me notice that there's no regression test covering the loading of historic snapshot from disk - so add one. Backpatch to 9.4 where these features were introduced. http://git.postgresql.org/pg/commitdiff/ec5896aed3c01da24c1f335f138817e9890d68b6
  • Fix xmin/xmax horizon computation during logical decoding initialization. When building the initial historic catalog snapshot there were scenarios where snapbuild.c would use incorrect xmin/xmax values when starting from a xl_running_xacts record. The values used were always a bit suspect, but happened to be correct in the easy to test cases. Notably the values used when the the initial snapshot was computed while no other transactions were running were correct. This is likely to be the cause of the occasional buildfarm failures on animals markhor and tick; but it's quite possible to reproduce problems without CLOBBER_CACHE_ALWAYS. Backpatch to 9.4, where logical decoding was introduced. http://git.postgresql.org/pg/commitdiff/5a2c184058c51a41b855b9e824102d1395402ffa
  • Fix and improve cache invalidation logic for logical decoding. There are basically three situations in which logical decoding needs to perform cache invalidation. During/After replaying a transaction with catalog changes, when skipping a uninteresting transaction that performed catalog changes and when erroring out while replaying a transaction. Unfortunately these three cases were all done slightly differently - partially because 8de3e410fa, which greatly simplifies matters, got committed in the midst of the development of logical decoding. The actually problematic case was when logical decoding skipped transaction commits (and thus processed invalidations). When used via the SQL interface cache invalidation could access the catalog - bad, because we didn't set up enough state to allow that correctly. It'd not be hard to setup sufficient state, but the simpler solution is to always perform cache invalidation outside a valid transaction. Also make the different cache invalidation cases look as similar as possible, to ease code review. This fixes the assertion failure reported by Antonin Houska in 53EE02D9.7040702@gmail.com. The presented testcase has been expanded into a regression test. Backpatch to 9.4, where logical decoding was introduced. http://git.postgresql.org/pg/commitdiff/89fd41b390a46202937f647043043d5b0a4eadae
  • Adapt valgrind.supp to the XLogInsert() split. The CRC computation now happens in XLogInsertRecord(), not XLogInsert() itself anymore. http://git.postgresql.org/pg/commitdiff/473f162ce1faabeb2d572b9805311081919e5deb
  • Move BufferGetBlockNumber() out of heap_page_is_all_visible()'s inner loop. In some workloads BufferGetBlockNumber() shows up in profiles due to the sheer number of calls to it (and because it causes cache misses). The compiler can't move it out of the loop because it's a full extern function call... http://git.postgresql.org/pg/commitdiff/0c5af0a537a2d6320eb8ef7f401401aa1f47b79e
  • Add valgrind suppression for pg_atomic_init_u64. pg_atomic_init_u64 (indirectly) uses compare/exchange to guarantee atomic writes on platforms where compare/exchange is available, but 64bit writes aren't atomic (yes, those exist). That leads to a harmless read of the initial value of variable. http://git.postgresql.org/pg/commitdiff/6c878edc1df9d4d9ad7ed4a7e1c34c0bf0f622b9
  • Fix initdb --sync-only to also sync tablespaces. 630cd14426dc added initdb --sync-only, for use by pg_upgrade, by just exposing the existing fsync code. That's wrong, because initdb so far had absolutely no reason to deal with tablespaces. Fix --sync-only by additionally explicitly syncing each of the tablespaces. Backpatch to 9.3 where --sync-only was introduced. Abhijit Menon-Sen and Andres Freund http://git.postgresql.org/pg/commitdiff/522c85a6a27b614589543eddb68a4c8f7fcac901
  • Ensure unlogged tables are reset even if crash recovery errors out. Unlogged relations are reset at the end of crash recovery as they're only synced to disk during a proper shutdown. Unfortunately that and later steps can fail, e.g. due to running out of space. This reset was, up to now performed after marking the database as having finished crash recovery successfully. As out of space errors trigger a crash restart that could lead to the situation that not all unlogged relations are reset. Once that happend usage of unlogged relations could yield errors like "could not open file "...": No such file or directory". Luckily clusters that show the problem can be fixed by performing a immediate shutdown, and starting the database again. To fix, just call ResetUnloggedRelations(UNLOGGED_RELATION_INIT) earlier, before marking the database as having successfully recovered. Discussion: 20140912112246.GA4984@alap3.anarazel.de Backpatch to 9.1 where unlogged tables were introduced. Abhijit Menon-Sen and Andres Freund http://git.postgresql.org/pg/commitdiff/d3586fc8aa5d9365a5c50cb5e555971eb633a4ec
  • Sync unlogged relations to disk after they have been reset. Unlogged relations are only reset when performing a unclean restart. That means they have to be synced to disk during clean shutdowns. During normal processing that's achieved by registering a buffer's file to be fsynced at the next checkpoint when flushed. But ResetUnloggedRelations() doesn't go through the buffer manager, so nothing will force reset relations to disk before the next shutdown checkpoint. So just make ResetUnloggedRelations() fsync the newly created main forks to disk. Discussion: 20140912112246.GA4984@alap3.anarazel.de Backpatch to 9.1 where unlogged tables were introduced. Abhijit Menon-Sen and Andres Freund http://git.postgresql.org/pg/commitdiff/98ec7fd903bbd4ab1694de5aaa4f7bb62935c58d

Stephen Frost a poussé :

  • Revert change to ALTER TABLESPACE summary. When ALTER TABLESPACE MOVE ALL was changed to be ALTER TABLE ALL IN TABLESPACE, the ALTER TABLESPACE summary should have been adjusted back to its original definition. Patch by Thom Brown (thanks!). http://git.postgresql.org/pg/commitdiff/155c0f24b7d370eb57fb8dde0447e11e41c405c2
  • Clean up includes from RLS patch The initial patch for RLS mistakenly included headers associated with the executor and planner bits in rewrite/rowsecurity.h. Per policy and general good sense, executor headers should not be included in planner headers or vice versa. The include of execnodes.h was a mistaken holdover from previous versions, while the include of relation.h was used for Relation's definition, which should have been coming from utils/relcache.h. This patch cleans these issues up, adds comments to the RowSecurityPolicy struct and the RowSecurityConfigType enum, and changes Relation->rsdesc to Relation->rd_rsdesc to follow Relation field naming convention. Additionally, utils/rel.h was including rewrite/rowsecurity.h, which wasn't a great idea since that was pulling in things not really needed in utils/rel.h (which gets included in quite a few places). Instead, use 'struct RowSecurityDesc' for the rd_rsdesc field and add comments explaining why. Lastly, add an include into access/nbtree/nbtsort.c for utils/sortsupport.h, which was evidently missed due to the above mess. Pointed out by Tom in 16970.1415838651@sss.pgh.pa.us; note that the concerns regarding a similar situation in the custom-path commit still need to be addressed. http://git.postgresql.org/pg/commitdiff/80eacaa3cdcd10383c333f6f4625af8cee1f7bee

Simon Riggs a poussé :

Magnus Hagander a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Peter Geoghegan sent in a pair of patches, one to add abbreviated sortsupport keys, the other to estimate total number of rows to be sorted.
  • Tomas Vondra sent in another revision of a patch to implement multi-column statistics.
  • Peter Geoghegan sent in another revision of a patch to implement INSERT ... ON CONFLICT.
  • Magnus Hagander sent in another revision of a patch to implement an SSL information view.
  • Kaigai Kouhei sent in a patch to add a missing Assert() to the custom plan API.
  • Michael Paquier sent in another revision of a patch to compress full-page writes.
  • Kyotaro HORIGUCHI sent in two more revisions of a patch to implement ALTER RULE ... CURRENT_USER.
  • Simon Riggs sent in a patch to add an option to pg_dump which would have it dump constraints as NOT VALID.
  • Michael Paquier sent in another revision of a patch to implement REINDEX CONCURRENTLY.
  • Heikki Linnakangas sent in three more revisions of a patch to change the WAL format and API.
  • Fabrízio de Royes Mello and Michael Paquier traded patches to implement ALTER TABLE ... SET LOGGED.
  • Etsuro Fujita sent in a patch to fix some odd behavior in the postgres_fdw.
  • Robert Haas sent a patch to add infrastructure to save and restore GUC values, and one which depends on it to run commands in a background worker and get the results.
  • Fujii Masao sent in another revision of a patch to add fsync feedback to pg_receivexlog --status-interval.
  • Andreas Karlsson sent in two more revisions of a patch to use 128-bit integers for sum, avg and statistics aggregates.
  • Petr (PJMODOS) Jelinek sent in another revision of a patch to track commit timestamps.
  • Etsuro Fujita sent in two revisions of a patch to fix some typos in the CREATE TABLE documentation.
  • Alexander Shulgin sent in a PoC patch to allow timing out transactions in the "Idle In Transaction" state.
  • Andrew Dunstan sent in a patch to control psql's interaction with the pager at a finer grain.
  • David Rowley sent in another revision of a patch to remove inner joins under certain conditions.
  • Michael Paquier sent in two revisions of a patch to fix an issue where PostgreSQL doesn't stop properly when --slot option is specified with pg_receivexlog.
  • Pavel Stehule sent in another revision of a patch to add asciidoc as an output option for psql.
  • Michael Paquier sent in another revision of a patch to refactor the code for sync node detection.

par N Bougain le mardi 18 novembre 2014 à 22h24

mercredi 12 novembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 9 novembre 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en novembre

PostgreSQL Local

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA.

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

(lien vers l'article original)

Correctifs appliqués

Tom Lane a poussé :

  • Docs: fix incorrect spelling of contrib/pgcrypto option. pgp_sym_encrypt's option is spelled "sess-key", not "enable-session-key". Spotted by Jeff Janes. In passing, improve a comment in pgp-pgsql.c to make it clearer that the debugging options are intentionally undocumented. http://git.postgresql.org/pg/commitdiff/f443de873e500de999a2d165731a0356b79a6ed7
  • Drop no-longer-needed buffers during ALTER DATABASE SET TABLESPACE. The previous coding assumed that we could just let buffers for the database's old tablespace age out of the buffer arena naturally. The folly of that is exposed by bug #11867 from Marc Munro: the user could later move the database back to its original tablespace, after which any still-surviving buffers would match lookups again and appear to contain valid data. But they'd be missing any changes applied while the database was in the new tablespace. This has been broken since ALTER SET TABLESPACE was introduced, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/33f80f8480434f02beb940b8f0627d534f3fd3af
  • Fix volatility markings of some contrib I/O functions. In general, datatype I/O functions are supposed to be immutable or at worst stable. Some contrib I/O functions were, through oversight, not marked with any volatility property at all, which made them VOLATILE. Since (most of) these functions actually behave immutably, the erroneous marking isn't terribly harmful; but it can be user-visible in certain circumstances, as per a recent bug report from Joe Van Dyk in which a cast to text was disallowed in an expression index definition. To fix, just adjust the declarations in the extension SQL scripts. If we were being very fussy about this, we'd bump the extension version numbers, but that seems like more trouble (for both developers and users) than the problem is worth. A fly in the ointment is that chkpass_in actually is volatile, because of its use of random() to generate a fresh salt when presented with a not-yet-encrypted password. This is bad because of the general assumption that I/O functions aren't volatile: the consequence is that records or arrays containing chkpass elements may have input behavior a bit different from a bare chkpass column. But there seems no way to fix this without breaking existing usage patterns for chkpass, and the consequences of the inconsistency don't seem bad enough to justify that. So for the moment, just document it in a comment. Since we're not bumping version numbers, there seems no harm in back-patching these fixes; at least future installations will get the functions marked correctly. http://git.postgresql.org/pg/commitdiff/66c029c842629958b3ae0d389f24ea3407225723
  • Make CREATE TYPE print warnings if a datatype's I/O functions are volatile. This is a followup to commit 43ac12c6e6e397fd9142ed908447eba32d3785b2, which added regression tests checking that I/O functions of built-in types are not marked volatile. Complaining in CREATE TYPE should push developers of add-on types to fix any misdeclared functions in their types. It's just a warning not an error, to avoid creating upgrade problems for what might be just cosmetic mis-markings. Aside from adding the warning code, fix a number of types that were sloppily created in the regression tests. http://git.postgresql.org/pg/commitdiff/465d7e1882bc1f316c7cb2a68e751c34b403e8d7
  • Remove the last vestige of server-side autocommit. Long ago we briefly had an "autocommit" GUC that turned server-side autocommit on and off. That behavior was removed in 7.4 after concluding that it broke far too much client-side logic, and making clients cope with both behaviors was impractical. But the GUC variable was left behind, so as not to break any client code that might be trying to read its value. Enough time has now passed that we should remove the GUC completely. Whatever vestigial backwards-compatibility benefit it had is outweighed by the risk of confusion for newbies who assume it ought to do something, as per a recent complaint from Wolfgang Wilhelm. In passing, adjust what seemed to me a rather confusing documentation reference to libpq's autocommit behavior. libpq as such knows nothing about autocommit, so psql is probably what was meant. http://git.postgresql.org/pg/commitdiff/525a489915cad1c5b2fc39e43e8005025afe63b2
  • Fix normalization of numeric values in JSONB GIN indexes. The default JSONB GIN opclass (jsonb_ops) converts numeric data values to strings for storage in the index. It must ensure that numeric values that would compare equal (such as 12 and 12.00) produce identical strings, else index searches would have behavior different from regular JSONB comparisons. Unfortunately the function charged with doing this was completely wrong: it could reduce distinct numeric values to the same string, or reduce equivalent numeric values to different strings. The former type of error would only lead to search inefficiency, but the latter type of error would cause index entries that should be found by a search to not be found. Repairing this bug therefore means that it will be necessary for 9.4 beta testers to reindex GIN jsonb_ops indexes, if they care about getting correct results from index searches involving numeric data values within the comparison JSONB object. Per report from Thomas Fanghaenel. http://git.postgresql.org/pg/commitdiff/4875931938b27924fe8d6f91bbdb09e2e5a29d0a
  • Cope with more than 64K phrases in a thesaurus dictionary. dict_thesaurus stored phrase IDs in uint16 fields, so it would get confused and even crash if there were more than 64K entries in the configuration file. It turns out to be basically free to widen the phrase IDs to uint32, so let's just do so. This was complained of some time ago by David Boutin (in bug #7793); he later submitted an informal patch but it was never acted on. We now have another complaint (bug #11901 from Luc Ouellette) so it's time to make something happen. This is basically Boutin's patch, but for future-proofing I also added a defense against too many words per phrase. Note that we don't need any explicit defense against overflow of the uint32 counters, since before that happens we'd hit array allocation sizes that repalloc rejects. Back-patch to all supported branches because of the crash risk. http://git.postgresql.org/pg/commitdiff/d6e37b35cda9a88dfd938dd61e9986dd93cc6dd3

Robert Haas a poussé :

Heikki Linnakangas a poussé :

  • Switch to CRC-32C in WAL and other places. The old algorithm was found to not be the usual CRC-32 algorithm, used by Ethernet et al. We were using a non-reflected lookup table with code meant for a reflected lookup table. That's a strange combination that AFAICS does not correspond to any bit-wise CRC calculation, which makes it difficult to reason about its properties. Although it has worked well in practice, seems safer to use a well-known algorithm. Since we're changing the algorithm anyway, we might as well choose a different polynomial. The Castagnoli polynomial has better error-correcting properties than the traditional CRC-32 polynomial, even if we had implemented it correctly. Another reason for picking that is that some new CPUs have hardware support for calculating CRC-32C, but not CRC-32, let alone our strange variant of it. This patch doesn't add any support for such hardware, but a future patch could now do that. The old algorithm is kept around for tsquery and pg_trgm, which use the values in indexes that need to remain compatible so that pg_upgrade works. While we're at it, share the old lookup table for CRC-32 calculation between hstore, ltree and core. They all use the same table, so might as well. http://git.postgresql.org/pg/commitdiff/5028f22f6eb0579890689655285a4778b4ffc460
  • Remove support for 64-bit CRC. It hasn't been used for anything for a long time. http://git.postgresql.org/pg/commitdiff/404bc51cde9dce1c674abe4695635612f08fe27e
  • Remove obsolete cases from GiST update redo code. The code that generated a record to clear the F_TUPLES_DELETED flag hasn't existed since we got rid of old-style VACUUM FULL. I kept the code that sets the flag, although it's not used for anything anymore, because it might still be interesting information for debugging purposes that some tuples have been deleted from a page. Likewise, the code to turn the root page from non-leaf to leaf page was removed when we got rid of old-style VACUUM FULL. Remove the code to replay that action, too. http://git.postgresql.org/pg/commitdiff/2effb72e682a7dbdc9a8a60a80c22ec1fa9d8079
  • Fix generation of SP-GiST vacuum WAL records. I broke these in 8776faa81cb651322b8993422bdd4633f1f6a487. Backpatch to 9.4, where that was done. http://git.postgresql.org/pg/commitdiff/1961b1c131e4211f3dc86ff2be971e430ab2a23d
  • Fix building with WAL_DEBUG. Now that the backup blocks are appended to the WAL record in xloginsert.c, XLogInsert doesn't see them anymore and cannot remove them from the version reconstructed for xlog_outdesc. This makes running with wal_debug=on more expensive, as we now make (unnecessary) temporary copies of the backup blocks, but it doesn't seem worth convoluting the code to keep that optimization. Reported by Alvaro Herrera. http://git.postgresql.org/pg/commitdiff/7250d8535b11d6443a9b27299e586c3df0654302
  • Move the backup-block logic from XLogInsert to a new file, xloginsert.c. xlog.c is huge, this makes it a little bit smaller, which is nice. Functions related to putting together the WAL record are in xloginsert.c, and the lower level stuff for managing WAL buffers and such are in xlog.c. Also move the definition of XLogRecord to a separate header file. This causes churn in the #includes of all the files that write WAL records, and redo routines, but it avoids pulling in xlog.h into most places. Reviewed by Michael Paquier, Alvaro Herrera, Andres Freund and Amit Kapila. http://git.postgresql.org/pg/commitdiff/2076db2aea766c4c828dccc34ae35f614129000d

Peter Eisentraut a poussé :

Bruce Momjian a poussé :

Fujii Masao a poussé :

Álvaro Herrera a poussé :

  • Fix serial schedule. Test misc depends on brin, but it was earlier in the serial schedule file. I didn't notice this because I only run the parallel schedule, but the buildfarm exposed my folly ... http://git.postgresql.org/pg/commitdiff/0e892e04efdc92abf53260e0bb0092cc48060e22
  • BRIN: Block Range Indexes. BRIN is a new index access method intended to accelerate scans of very large tables, without the maintenance overhead of btrees or other traditional indexes. They work by maintaining "summary" data about block ranges. Bitmap index scans work by reading each summary tuple and comparing them with the query quals; all pages in the range are returned in a lossy TID bitmap if the quals are consistent with the values in the summary tuple, otherwise not. Normal index scans are not supported because these indexes do not store TIDs. As new tuples are added into the index, the summary information is updated (if the block range in which the tuple is added is already summarized) or not; in the latter case, a subsequent pass of VACUUM or the brin_summarize_new_values() function will create the summary information. For data types with natural 1-D sort orders, the summary info consists of the maximum and the minimum values of each indexed column within each page range. This type of operator class we call "Minmax", and we supply a bunch of them for most data types with B-tree opclasses. Since the BRIN code is generalized, other approaches are possible for things such as arrays, geometric types, ranges, etc; even for things such as enum types we could do something different than minmax with better results. In this commit I only include minmax. Catalog version bumped due to new builtin catalog entries. There's more that could be done here, but this is a good step forwards. Loosely based on ideas from Simon Riggs; code mostly by Álvaro Herrera, with contribution by Heikki Linnakangas. Patch reviewed by: Amit Kapila, Heikki Linnakangas, Robert Haas. Testing help from Jeff Janes, Erik Rijkers, Emanuel Calvo. p.s.: The research leading to these results has received funding from the European Union's Seventh Framework Programme (FP7/2007-2013) under grant agreement n° 318633. http://git.postgresql.org/pg/commitdiff/7516f5259411c02ae89e49084452dc342aadb2ae
  • Fix some coding issues in BRIN. Reported by David Rowley: variadic macros are a problem. Get rid of them using a trick suggested by Tom Lane: add extra parentheses where needed. In the future we might decide we don't need the calls at all and remove them, but it seems appropriate to keep them while this code is still new. Also from David Rowley: brininsert() was trying to use a variable before initializing it. Fix by moving the brin_form_tuple call (which initializes the variable) to within the locked section. Reported by Peter Eisentraut: can't use "new" as a struct member name, because C++ compilers will choke on it, as reported by cpluspluscheck. http://git.postgresql.org/pg/commitdiff/b89ee54e20e722bb91f388667586a2e0986f197b

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 implement pg_audit.
  • Alexey Vasiliev sent in two revisions of a patch to add a recovery_timeout option to control the timeout of restore_command nonzero status code.
  • Rahila Syed sent in two more revisions of a patch to allow compressing full-page writes.
  • Andrew Dunstan sent in a patch to add a utility function to look up the cast function for a from/to pair of types.
  • Heikki Linnakangas sent in another revision of a patch to change the WAL format and APIs.
  • Ali Akbar sent in another revision of a patch to fix xpath() to return namespace definitions.
  • Peter Geoghegan sent in another revision of a patch to implement INSERT ... ON CONFLICT ...
  • Adam Brightwell sent in another revision of a patch to replace some of the superuser() shortcuts with more specific role checks.
  • Michael Paquier sent in another revision of a patch to implement REINDEX CONCURRENTLY.
  • Robert Haas sent in a PoC patch to implement group locking.
  • Abhijit Menon-Sen sent in another revision of a patch to fsync PGDATA recursively in the case of crash recovery.
  • Fujii Masao sent in another revision of a patch to add a GIN GUC pending_list_cleanup_size.
  • Jim Nasby sent in a patch to retry failed attempts to obtain the cleanup lock on a buffer during VACUUM.
  • Etsuro Fujita sent in another revision of a patch to allow foreign tables to be part of table inheritance hierarchies.
  • Dimitri Fontaine sent in another revision of a patch to add event triggers for table rewrites.
  • Fujii Masao sent in another revision of a patch to add fsync feedback to pg_receivexlog --status-interval.
  • Michael Paquier sent in a patch to add a dedicated macro to grab a relation's persistence.
  • Ad sent in another revision of a patch to add jsonb generator functions.
  • Michael Paquier sent in another revision of a patch to move all quote-related functions into a single header quote.h.
  • Michael Banck sent in a patch to add a log_min_duration_transaction GUC.
  • Tom Lane sent in two revisions of a patch to fix an infelicity between index-only scans and row_to_json.
  • Magnus Hagander sent in a patch to re-order the views in the stats docs for clarity.

par N Bougain le mercredi 12 novembre 2014 à 13h58

mardi 4 novembre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 2 novembre 2014

PG-Cuba aura lieu les 25 & 26 novembre : http://postgresql.uci.cu/?p=1087#more-1087

L'appel à conférenciers pour le PGDay (30 janvier 2015) et la dev room PostgreSQL du FOSDEM (31 janvier au 1er février 2015) est lancé. Date limite des candidatures : 24 novembre 2014 : http://fosdem2015.pgconf.eu/callforpapers/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en novembre

PostgreSQL Local

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA.

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

(lien vers l'article original)

Correctifs appliqués

Bruce Momjian a poussé :

Heikki Linnakangas a poussé :

Robert Haas a poussé :

  • Add missing equals signs to pg_recvlogical documentation. Michael Paquier http://git.postgresql.org/pg/commitdiff/7f609a10adb5419c68fc683594fcd14f1e7322fb
  • Avoid setup work for invalidation messages at start-of-(sub)xact. Instead of initializing a new TransInvalidationInfo for every transaction or subtransaction, we can just do it for those transactions or subtransactions that actually need to queue invalidation messages. That also avoids needing to free those entries at the end of a transaction or subtransaction that does not generate any invalidation messages, which is by far the common case. Patch by me. Review by Simon Riggs and Andres Freund. http://git.postgresql.org/pg/commitdiff/6cb4afff33ba0b6f88cca2967904ad501d648e2f
  • "Pin", rather than "keep", dynamic shared memory mappings and segments. Nobody seemed concerned about this naming when it originally went in, but there's a pending patch that implements the opposite of dsm_keep_mapping, and the term "unkeep" was judged unpalatable. "unpin" has existing precedent in the PostgreSQL code base, and the English language, so use this terminology instead. Per discussion, back-patch to 9.4. http://git.postgresql.org/pg/commitdiff/6057c212f3e783c45e050a78d070edf3e5f069f0
  • Extend dsm API with a new function dsm_unpin_mapping. This reassociates a dynamic shared memory handle previous passed to dsm_pin_mapping with the current resource owner, so that it will be cleaned up at the end of the current query. Patch by me. Review of the function name by Andres Freund, Amit Kapila, Jim Nasby, Petr Jelinek, and Álvaro Herrera. http://git.postgresql.org/pg/commitdiff/f7102b04638a882b38cbba7670471a073a939865
  • Support frontend-backend protocol communication using a shm_mq. A background worker can use pq_redirect_to_shm_mq() to direct protocol that would normally be sent to the frontend to a shm_mq so that another process may read them. The receiving process may use pq_parse_errornotice() to parse an ErrorResponse or NoticeResponse from the background worker and, if it wishes, ThrowErrorData() to propagate the error (with or without further modification). Patch by me. Review by Andres Freund. http://git.postgresql.org/pg/commitdiff/2bd9e412f92bc6a68f3e8bcb18e04955cc35001d

Noah Misch a poussé :

  • MinGW: Include .dll extension in .def file LIBRARY commands. Newer toolchains append the extension implicitly if missing, but buildfarm member narwhal (gcc 3.4.2, ld 2.15.91 20040904) does not. This affects most core libraries having an exports.txt file, namely libpq and the ECPG support libraries. On Windows Server 2003, Windows API functions that load and unload DLLs internally will mistakenly unload a libpq whose DLL header reports "LIBPQ" instead of "LIBPQ.dll". When, subsequently, control would return to libpq, the backend crashes. Back-patch to 9.4, like commit 846e91e0223cf9f2821c3ad4dfffffbb929cb027. Before that commit, we used a different linking technique that yielded "libpq.dll" in the DLL header. Commit 53566fc0940cf557416b13252df57350a4511ce4 worked around this by eliminating a call to a function that loads and unloads DLLs internally. That commit is no longer necessary for correctness, but its improving consistency with the MSVC build remains valid. http://git.postgresql.org/pg/commitdiff/c0e190365b93bbf717540d8e7653c65a9f6c5650
  • Fix win32setlocale.c const-related warnings. Back-patch to 9.2, like commit db29620d4d16e08241f965ccd70d0f65883ff0de. http://git.postgresql.org/pg/commitdiff/846319521753af63c8f9b0893a116adc0a70e936
  • Re-remove dependency on the DLL of pythonxx.def file. The reasons behind commit 0d147e43adcf5d2bff9caa073608f381a27439bf still stand, so this reverts the non-cosmetic portion of commit a7983e989d9cafc9cef49becfee054e34b1ed9b4. Back-patch to 9.4, where the latter commit first appeared. http://git.postgresql.org/pg/commitdiff/00c07e497f31e44ddf971e2d5f14240971ed5175
  • Make ECPG test programs depend on "ecpg$(X)", not "ecpg". Cygwin builds require this of dependencies pertaining to pattern rules. On Cygwin, stat("foo") in the absence of a file with that exact name can locate foo.exe. While GNU make uses stat() for dependencies of ordinary rules, it uses readdir() to assess dependencies of pattern rules. Therefore, a pattern rule dependency should match any underlying file name exactly. Back-patch to 9.4, where the dependency was introduced. http://git.postgresql.org/pg/commitdiff/67a412049489f554c3a552bd523cefd30d038221
  • Remove dead-since-introduction pgcrypto code. Marko Tiikkaja http://git.postgresql.org/pg/commitdiff/1ed8e771ade6f2a58728f4537e9c19b702d8cf25
  • Clarify .def file comments. http://git.postgresql.org/pg/commitdiff/c40212baf6c093b4958c6e04ddde94869788a572

Tom Lane a poussé :

  • Remove obsolete commentary. Since we got rid of non-MVCC catalog scans, the fourth reason given for using a non-transactional update in index_update_stats() is obsolete. The other three are still good, so we're not going to change the code, but fix the comment. http://git.postgresql.org/pg/commitdiff/a00d468e658a245823083b9ac2e423a659a03802
  • Avoid corrupting tables when ANALYZE inside a transaction is rolled back. VACUUM and ANALYZE update the target table's pg_class row in-place, that is nontransactionally. This is OK, more or less, for the statistical columns, which are mostly nontransactional anyhow. It's not so OK for the DDL hint flags (relhasindex etc), which might get changed in response to transactional changes that could still be rolled back. This isn't a problem for VACUUM, since it can't be run inside a transaction block nor in parallel with DDL on the table. However, we allow ANALYZE inside a transaction block, so if the transaction had earlier removed the last index, rule, or trigger from the table, and then we roll back the transaction after ANALYZE, the table would be left in a corrupted state with the hint flags not set though they should be. To fix, suppress the hint-flag updates if we are InTransactionBlock(). This is safe enough because it's always OK to postpone hint maintenance some more; the worst-case consequence is a few extra searches of pg_index et al. There was discussion of instead using a transactional update, but that would change the behavior in ways that are not all desirable: in most scenarios we're better off keeping ANALYZE's statistical values even if the ANALYZE itself rolls back. In any case we probably don't want to change this behavior in back branches. Per bug #11638 from Casey Shobe. This has been broken for a good long time, so back-patch to all supported branches. Tom Lane and Michael Paquier, initial diagnosis by Andres Freund http://git.postgresql.org/pg/commitdiff/e0722d9cb57d06c2e459cf338cf7387ec3edb1f5
  • Test IsInTransactionChain, not IsTransactionBlock, in vac_update_relstats. As noted by Noah Misch, my initial cut at fixing bug #11638 didn't cover all cases where ANALYZE might be invoked in an unsafe context. We need to test the result of IsInTransactionChain not IsTransactionBlock; which is notationally a pain because IsInTransactionChain requires an isTopLevel flag, which would have to be passed down through several levels of callers. I chose to pass in_outer_xact (ie, the result of IsInTransactionChain) rather than isTopLevel per se, as that seemed marginally more apropos for the intermediate functions to know about. http://git.postgresql.org/pg/commitdiff/fd0f651a867ce4a25160e37bcb9085f3b3209bf8

Peter Eisentraut a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Kaigai Kouhei sent in another revision of a patch to implement a custom plan API.
  • Pavel Stehule sent in another revision of a patch to add "strip nulls" functions to JSON[B].
  • Abhijit Menon-Sen sent in another revision of a patch to call ResetUnloggedRelations(UNLOGGED_RELATION_INIT) earlier.
  • Stephen Frost sent in a patch to fix some lacunae in the use of GetUserId().
  • Heikki Linnakangas sent patches to rationalize use of CRC algorithms, removing the unused 64-bit version and redoing the ones that won't break pg_upgrade to algorithms with well-known properties.
  • Ali Akbar and Pavel Stehule traded patches to implement array_agg(ANYARRAY).
  • Michael Paquier sent in another revision of a patch to add a --snapshot option to pg_dump.
  • Andrew Dunstan sent in another revision of a patch to add some missing JSON[B] functions.
  • Stephen Frost sent in another revision of a patch to clean up superuser().
  • Amit Kapila sent in another revision of a patch to allow parallel cores to be used by vacuumdb.
  • Adam Brightwell sent in another revision of a patch to add directory and file access permissions for COPY and generic file access functions.
  • Andres Freund sent in a WIP patch to implement a lockless StrategyGetBuffer() clock sweep.
  • Marko (johto) Tiikkaja sent in two more revisions of a patch to add PGP signatures to the pgcrypto contrib extension.
  • Rahila Syed sent in another revision of a patch to do compression of full-page writes.
  • Michael Paquier sent in another revision of a patch to implement REINDEX CONCURRENTLY.
  • Andres Freund sent in a patch to eliminate "snapshot too large" messages by doubling the array size until the snapshot is no longer too large for it.
  • Peter Eisentraut sent in a patch to see to it that TAP tests run in a deeply nested directory tree.
  • Peter Eisentraut sent in a patch to handle the situation where the "prove" utility is not available.
  • Etsuro Fujita sent in a patch to improve automatic analyze messages for inheritance trees.
  • Stephen Frost sent in a patch to fix an incompatibility between views created with WITH CHECK OPTION and column-level privileges.
  • Szymon Guz and Pavel Stehule traded patches to add asciidoc format to psql table output.
  • Heikki Linnakangas sent in another revision of a patch to change the API and format of WALs.
  • Kyotaro HORIGUCHI and Adam Brightwell traded patches to implement ALTER USER/ROLE...CURRENT USER.
  • Abhijit Menon-Sen sent in a patch to initdb to make initdb -S fsync everything under pg_tblspc.
  • Fujii Masao sent in another revision of a patch to add a pending_list_cleanup_size GUC and associated machinery.
  • Michael Paquier sent in two more revisions of a patch to factor the code for sync node detection out of walsender.c and syncrep.c.
  • Fabrízio de Royes Mello sent in another revision of a patch to implement CINE for indexes.
  • Jim Nasby sent in a patch to move the bulk of exec_simple_query into exec_query_string() so that pg_backend can also make use of it.
  • Robert Haas sent in another revision of a patch to implement pg_background, which runs commands in a background worker, and gets the results.
  • Peter Eisentraut sent in a patch to add a ./configure enable_tap_tests option.
  • Fabrízio de Royes Mello sent in another revision of a patch to implement CINE functionality for materialized views.
  • Furuya Osamu sent in another revision of a patch to add fsync feedback to pg_receivexlog --status-interval.
  • Petr (PJMODOS) Jelinek sent in another revision of a patch to add a shutdown_at_recovery_target option to recovery.conf.
  • Thomas Munro sent in another revision of a patch to support DISTINCT with a btree skip scan.
  • Andres Freund sent in a patch to fix an issue with large tables in two parts: a faster PageIsVerified() for the all zeroes case, and a much faster O-1 mfdvec, removing the quadratic behavior in same.
  • Simon Riggs sent in a patch to reduce logging of AccessExclusiveLocks for temp tables by skipping logging locks in the case of relation_open_temp().
  • Petr (PJMODOS) Jelinek sent in another revision of a patch to track commit timestamps.

par N Bougain le mardi 4 novembre 2014 à 22h00

vendredi 31 octobre 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 26 octobre 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en octobre

PostgreSQL Local

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA.

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

(lien vers l'article original)

Correctifs appliqués

Robert Haas a poussé :

Tom Lane a poussé :

  • Fix file-identification comment in contrib/pgcrypto/pgcrypto--1.2.sql. Cosmetic oversight in commit 32984d8fc3dbb90a3fafb69fece0134f1ea790f9. Marko Tiikkaja http://git.postgresql.org/pg/commitdiff/488a7c9ccff54f947d5a51f00efe873f4bae909a
  • Fix mishandling of FieldSelect-on-whole-row-Var in nested lateral queries. If an inline-able SQL function taking a composite argument is used in a LATERAL subselect, and the composite argument is a lateral reference, the planner could fail with "variable not found in subplan target list", as seen in bug #11703 from Karl Bartel. (The outer function call used in the bug report and in the committed regression test is not really necessary to provoke the bug --- you can get it if you manually expand the outer function into "LATERAL (SELECT inner_function(outer_relation))", too.) The cause of this is that we generate the reltargetlist for the referenced relation before doing eval_const_expressions() on the lateral sub-select's expressions (cf find_lateral_references()), so what's scheduled to be emitted by the referenced relation is a whole-row Var, not the simplified single-column Var produced by optimizing the function's FieldSelect on the whole-row Var. Then setrefs.c fails to match up that lateral reference to what's available from the outer scan. Preserving the FieldSelect optimization in such cases would require either major planner restructuring (to recursively do expression simplification on sub-selects much earlier) or some amazingly ugly kluge to change the reltargetlist of a possibly-already-planned relation. It seems better just to skip the optimization when the Var is from an upper query level; the case is not so common that it's likely anyone will notice a few wasted cycles. AFAICT this problem only occurs for uplevel LATERAL references, so back-patch to 9.3 where LATERAL was added. http://git.postgresql.org/pg/commitdiff/f330a6d14066277e78586cb4ceb8692ca3533046
  • Update expected/sequence_1.out. The last three updates to the sequence regression test have all forgotten to touch the alternate expected-output file. Sigh. Michael Paquier http://git.postgresql.org/pg/commitdiff/31dd7fcd03609dbc3c3be85e645ccc7c715db719
  • Ensure libpq reports a suitable error message on unexpected socket EOF. The EOF-detection logic in pqReadData was a bit confused about who should set up the error message in case the kernel gives us read-ready-but-no-data rather than ECONNRESET or some other explicit error condition. Since the whole point of this situation is that the lower-level functions don't know there's anything wrong, pqReadData itself must set up the message. But keep the assumption that if an errno was reported, a message was set up at lower levels. Per bug #11712 from Marko Tiikkaja. It's been like this for a very long time, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/69fed5b26fa34fc825c7ed0a8d97221ff448e9c5
  • Improve ispell dictionary's defenses against bad affix files. Don't crash if an ispell dictionary definition contains flags but not any compound affixes. (This isn't a security issue since only superusers can install affix files, but still it's a bad thing.) Also, be more careful about detecting whether an affix-file FLAG command is old-format (ispell) or new-format (myspell/hunspell). And change the error message about mixed old-format and new-format commands into something intelligible. Per bug #11770 from Emre Hasegeli. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/b34d6f03dbb34027ee0ee6f1c1887ae30ec7b07d
  • In type_sanity, check I/O functions of built-in types are not volatile. We have a project policy that I/O functions must not be volatile, as per commit aab353a60b95aadc00f81da0c6d99bde696c4b75, but we weren't doing anything to enforce that. In most usage the marking of the function doesn't matter as long as its behavior is sane --- but I/O casts can expose the marking as user-visible behavior, as per today's complaint from Joe Van Dyk about contrib/ltree. This test as such will only protect us against future errors in built-in data types. To catch the same error in contrib or third-party types, perhaps we should make CREATE TYPE complain? But that's a separate issue from enforcing the policy for built-in types. http://git.postgresql.org/pg/commitdiff/43ac12c6e6e397fd9142ed908447eba32d3785b2
  • Improve planning of btree index scans using ScalarArrayOpExpr quals. Since we taught btree to handle ScalarArrayOpExpr quals natively (commit 9e8da0f75731aaa7605cf4656c21ea09e84d2eb1), the planner has always included ScalarArrayOpExpr quals in index conditions if possible. However, if the qual is for a non-first index column, this could result in an inferior plan because we can no longer take advantage of index ordering (cf. commit 807a40c551dd30c8dd5a0b3bd82f5bbb1e7fd285). It can be better to omit the ScalarArrayOpExpr qual from the index condition and let it be done as a filter, so that the output doesn't need to get sorted. Indeed, this is true for the query introduced as a test case by the latter commit. To fix, restructure get_index_paths and build_index_paths so that we consider paths both with and without ScalarArrayOpExpr quals in non-first index columns. Redesign the API of build_index_paths so that it reports what it found, saving useless second or third calls. Report and patch by Andrew Gierth (though rather heavily modified by me). Back-patch to 9.2 where this code was introduced, since the issue can result in significant performance regressions compared to plans produced by 9.1 and earlier. http://git.postgresql.org/pg/commitdiff/a4523c5aa5349510ddb74abddc416add2536ec3d
  • Fix undersized result buffer in pset_quoted_string(). The malloc request was 1 byte too small for the worst-case output. This seems relatively unlikely to cause any problems in practice, as the worst case only occurs if the input string contains no characters other than single-quote or newline, and even then malloc alignment padding would probably save the day. But it's definitely a bug. David Rowley http://git.postgresql.org/pg/commitdiff/9711fa06081da230e62fa52147c49ccf7b9ccbe2
  • Avoid unportable strftime() behavior in pg_dump/pg_dumpall. Commit ad5d46a4494b0b480a3af246bb4227d9bdadca37 thought that we could get around the known portability issues of strftime's %Z specifier by using %z instead. However, that idea seems to have been innocent of any actual research, as it certainly missed the facts that (1) %z is not portable to pre-C99 systems, and (2) %z doesn't actually act differently from %Z on Windows anyway. Per failures on buildfarm member hamerkop. While at it, centralize the code defining what strftime format we want to use in pg_dump; three copies of that string seems a bit much. http://git.postgresql.org/pg/commitdiff/f455fcfdb8ca3b67373223a4e15648c35e2592a9

Andrew Dunstan a poussé :

  • Correct volatility markings of a few json functions. json_agg and json_object_agg and their associated transition functions should have been marked as stable rather than immutable, as they call IO functions indirectly. Changing this probably isn't going to make much difference, as you can't use an aggregate function in an index expression, but we should be correct nevertheless. json_object, on the other hand, should be marked immutable rather than stable, as it does not call IO functions. As discussed on -hackers, this change is being made without bumping the catalog version, as we don't want to do that at this stage of the cycle, and the changes are very unlikely to affect anyone. http://git.postgresql.org/pg/commitdiff/af2b8fd057213f4b1918b9581c63e0b00427573c

Peter Eisentraut a poussé :

Andres Freund a poussé :

  • Flush unlogged table's buffers when copying or moving databases. CREATE DATABASE and ALTER DATABASE .. SET TABLESPACE copy the source database directory on the filesystem level. To ensure the on disk state is consistent they block out users of the affected database and force a checkpoint to flush out all data to disk. Unfortunately, up to now, that checkpoint didn't flush out dirty buffers from unlogged relations. That bug means there could be leftover dirty buffers in either the template database, or the database in its old location. Leading to problems when accessing relations in an inconsistent state; and to possible problems during shutdown in the SET TABLESPACE case because buffers belonging files that don't exist anymore are flushed. This was reported in bug #10675 by Maxim Boguk. Fix by Pavan Deolasee, modified somewhat by me. Reviewed by MauMau and Fujii Masao. Backpatch to 9.1 where unlogged tables were introduced. http://git.postgresql.org/pg/commitdiff/7dbb60693820c20b562c12f58040c9871d6ab787
  • Renumber CHECKPOINT_* flags. Commit 7dbb6069382 added a new CHECKPOINT_FLUSH_ALL flag. As that commit needed to be backpatched I didn't change the numeric values of the existing flags as that could lead to nastly problems if any external code issued checkpoints. That's not a concern on master, so renumber them there. Also add a comment about CHECKPOINT_FLUSH_ALL above CreateCheckPoint(). http://git.postgresql.org/pg/commitdiff/11abd6c90f1094eaeee32e1c57d250c9db5102cb
  • Don't duplicate log_checkpoint messages for both of restart and checkpoints. The duplication originated in cdd46c765, where restartpoints were introduced. In LogCheckpointStart's case the duplication actually lead to the compiler's format string checking not to be effective because the format string wasn't constant. Arguably these messages shouldn't be elog(), but ereport() style messages. That'd even allow to translate the messages... But as there's more mistakes of that kind in surrounding code, it seems better to change that separately. http://git.postgresql.org/pg/commitdiff/5e5b65f359b7a12c05708585a8e6cd4d80ca2652
  • Add native compiler and memory barriers for solaris studio. Discussion: 20140925133459.GB9633@alap3.anarazel.de Author: Oskari Saarenmaa http://git.postgresql.org/pg/commitdiff/4a54b99e9c3989017fa5717f343cceab09aa5542

Noah Misch a poussé :

  • MinGW: Link with shell32.dll instead of shfolder.dll. This improves consistency with the MSVC build. On buildfarm member narwhal, since commit 846e91e0223cf9f2821c3ad4dfffffbb929cb027, shfolder.dll:SHGetFolderPath() crashes when dblink calls it by way of pqGetHomeDirectory(). Back-patch to 9.4, where that commit first appeared. How it caused this regression remains a mystery. This is a partial revert of commit 889f03812916b146ae504c0fad5afdc7bf2e8a2a, which adopted shfolder.dll for Windows NT 4.0 compatibility. PostgreSQL 8.2 dropped support for that operating system. http://git.postgresql.org/pg/commitdiff/53566fc0940cf557416b13252df57350a4511ce4
  • MinGW: Use -static-libgcc when linking a DLL. When commit 846e91e0223cf9f2821c3ad4dfffffbb929cb027 switched the linker driver from dlltool/dllwrap to gcc, it became possible for linking to choose shared libgcc. Backends having loaded a module dynamically linked to libgcc can exit abnormally, which the postmaster treats like a crash. Resume use of static libgcc exclusively, like 9.3 and earlier. Back-patch to 9.4. http://git.postgresql.org/pg/commitdiff/284590e416b72f541c5fe54f36a95e38d80835c3

Heikki Linnakangas a poussé :

  • Update comment. The _bt_tuplecompare() function mentioned in comment hasn't existed for a long time. Peter Geoghegan http://git.postgresql.org/pg/commitdiff/98b37437796bb5fedecf0d0c73cef4aa2ce9d192
  • Complain if too many options are passed to pg_controldata or pg_resetxlog. http://git.postgresql.org/pg/commitdiff/2d53003432f8560b9c3adf569118747c8ac8447d
  • Oops, the commit accept pg_controldata -D datadir missed code changes. I updated the docs and usage blurp, but forgot to commit the code changes required. Spotted by Michael Paquier. http://git.postgresql.org/pg/commitdiff/22b743b2ca18a692ba8d91bc18e7c42a8b7941ad
  • Forgot #include "pg_getopt.h", now that pg_controldata uses getopt. Needed at least on Windows. http://git.postgresql.org/pg/commitdiff/c0c1f6fc97088100a710e7e9c2e74b775985083d
  • Work around Windows locale name with non-ASCII character. Windows has one a locale whose name contains a non-ASCII character: "Norwegian (Bokmål)" (that's an 'a' with a ring on top). That causes trouble; when passing it setlocale(), it's not clear what encoding the argument should be in. Another problem is that the locale name is stored in pg_database catalog table, and the encoding used there depends on what server encoding happens to be in use when the database is created. For example, if you issue the CREATE DATABASE when connected to a UTF-8 database, the locale name is stored in pg_database in UTF-8. As long as all locale names are pure ASCII, that's not a problem. To work around that, map the troublesome locale name to a pure-ASCII alias of the same locale, "norwegian-bokmal". Now, this doesn't change the existing values that are already in pg_database and in postgresql.conf. Old clusters will need to be fixed manually. Instructions for that need to be put in the release notes. This fixes bug #11431 reported by Alon Siman-Tov. Backpatch to 9.2; backpatching further would require more work than seems worth it. http://git.postgresql.org/pg/commitdiff/db29620d4d16e08241f965ccd70d0f65883ff0de

Michael Meskes a poussé :

Fujii Masao a poussé :

  • Prevent the already-archived WAL file from being archived again. Previously the archive recovery always created .ready file for the last WAL file of the old timeline at the end of recovery even when it's restored from the archive and has .done file. That is, there was the case where the WAL file had both .ready and .done files. This caused the already-archived WAL file to be archived again. This commit prevents the archive recovery from creating .ready file for the last WAL file if it has .done file, in order to prevent it from being archived again. This bug was added when cascading replication feature was introduced, i.e., the commit 5286105800c7d5902f98f32e11b209c471c0c69c. So, back-patch to 9.2, where cascading replication was added. Reviewed by Michael Paquier http://git.postgresql.org/pg/commitdiff/c7371c4a607872c4d799e0dac0c9367574067cdc
  • Remove the unused argument of PSQLexec(). This commit simply removes the second argument of PSQLexec that was set to the same value everywhere. Comments and code blocks related to this parameter are removed. Noticed by Heikki Linnakangas, reviewed by Michael Paquier http://git.postgresql.org/pg/commitdiff/efbbbbc8b51bacd32e96c3f747dd6aa4c0e444cb

Álvaro Herrera a poussé :

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 implement PGP signatures in the pgcrypto contrib extension.
  • David Wheeler sent in two revisions of a patch to add launchd support for OSX.
  • Adam Brightwell sent in another revision of a patch to add directory and file access permissions for COPY and generic file access functions.
  • Jim Nasby sent in a patch to remove a spurious set in heap_prune_chain().
  • Jim Nasby sent in a patch to optimize CacheInvalidateHeapTuple.
  • Tom Lane sent in a patch to get rid of "accept incoming network connections" prompts on OS X.
  • Teodor Sigaev sent in a patch to speed up tidbitmap by caching a page.
  • Teodor Sigaev sent in a patch to speed up tidbitmap by hashing BlockNumber.
  • Teodor Sigaev sent in a patch to add a compress method for SP-GiST.
  • David Rowley and Craig Ringer traded patches to use a higher-precision timer API on Windows when available.
  • Teodor Sigaev sent in a patch to add GIN support contains operator for ranges over scalar column.
  • Robert Haas sent in another revision of a patch to implement pg_background.
  • Andreas Karlsson sent in a patch to reduce the lock strength needed for adding foreign keys.
  • Etsuro Fujita sent in a patch to fix an incorrect comment in tablecmds.c.
  • Pavel Stehule sent in a patch to allow AS label inside a row constructor.
  • Fujii Masao sent in another revision of a patch to implement REINDEX SCHEMA.
  • Florian Pflug sent in a patch to update README.tuplock to reflect current realities.
  • Etsuro Fujita sent in another revision of a patch to allow foreign tables to be part of table inheritance hierarchies.
  • Michael Paquier sent in two revisions of a patch to fix some typos in the recvlogical documentation.
  • Peter Geoghegan sent in another revision of a patch to implement INSERT ... ON CONFLICT {UPDATE | IGNORE}.
  • Kyotaro HORIGUCHI sent in another revision of a patch to add ALTER ROLE ... CURRENT USER.
  • Jim Nasby sent in a patch to add some detail to a comment in bgworker.c.
  • Ali Akbar and Pavel Stehule traded patches to implement array_agg(array).
  • Andreas Karlsson sent in a WIP patch to use 128-bit integers for sum, avg and statistics aggregates where available.
  • Michael Paquier sent in another revision of a patch to make pg_controldata accept "-D dirname".
  • Andrew Dunstan sent in two more revisions of a patch to add strip nulls functions for json and jsonb.
  • Marti Raudsepp sent in another revision of a patch to simplify EXISTS subqueries containing LIMIT.
  • David Rowley sent in a patch to fix an issue where pset_quoted_string was broken in psql.

par N Bougain le vendredi 31 octobre 2014 à 00h59

Nouvelles hebdomadaires de PostgreSQL - 19 octobre 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en octobre

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

Kevin Grittner a poussé :

  • Increase number of hash join buckets for underestimate. If we expect batching at the very beginning, we size nbuckets for "full work_mem" (see how many tuples we can get into work_mem, while not breaking NTUP_PER_BUCKET threshold). If we expect to be fine without batching, we start with the 'right' nbuckets and track the optimal nbuckets as we go (without actually resizing the hash table). Once we hit work_mem (considering the optimal nbuckets value), we keep the value. At the end of the first batch, we check whether (nbuckets != nbuckets_optimal) and resize the hash table if needed. Also, we keep this value for all batches (it's OK because it assumes full work_mem, and it makes the batchno evaluation trivial). So the resize happens only once. There could be cases where it would improve performance to allow the NTUP_PER_BUCKET threshold to be exceeded to keep everything in one batch rather than spilling to a second batch, but attempts to generate such a case have so far been unsuccessful; that issue may be addressed with a follow-on patch after further investigation. Tomas Vondra with minor format and comment cleanup by me Reviewed by Robert Haas, Heikki Linnakangas, and Kevin Grittner http://git.postgresql.org/pg/commitdiff/30d7ae3c76d2de144232ae6ab328ca86b70e72c3

Heikki Linnakangas a poussé :

  • Add --latency-limit option to pgbench. This allows transactions that take longer than specified limit to be counted separately. With --rate, transactions that are already late by the time we get to execute them are skipped altogether. Using --latency-limit with --rate allows you to "catch up" more quickly, if there's a hickup in the server causing a lot of transactions to stall momentarily. Fabien COELHO, reviewed by Rukh Meski and heavily refactored by me. http://git.postgresql.org/pg/commitdiff/98aed6c721763f2472ccd23b834baed9f83a944b
  • Fix typo in docs. Shigeru Hanada http://git.postgresql.org/pg/commitdiff/0ff5047d52ab84dad682ad140b6992c294580eb1
  • Fix deadlock with LWLockAcquireWithVar and LWLockWaitForVar. LWLockRelease should release all backends waiting with LWLockWaitForVar, even when another backend has already been woken up to acquire the lock, i.e. when releaseOK is false. LWLockWaitForVar can return as soon as the protected value changes, even if the other backend will acquire the lock. Fix that by resetting releaseOK to true in LWLockWaitForVar, whenever adding itself to the wait queue. This should fix the bug reported by MauMau, where the system occasionally hangs when there is a lot of concurrent WAL activity and a checkpoint. Backpatch to 9.4, where this code was added. http://git.postgresql.org/pg/commitdiff/e0d97d77bf0875e4d5cc7dedfe701d9999bf678c

Bruce Momjian a poussé :

Peter Eisentraut a poussé :

Álvaro Herrera a poussé :

  • pg_dump: Reduce use of global variables. Most pg_dump.c global variables, which were passed down individually to dumping routines, are now grouped as members of the new DumpOptions struct, which is used as a local variable and passed down into routines that need it. This helps future development efforts; in particular it is said to enable a mode in which a parallel pg_dump run can output multiple streams, and have them restored in parallel. Also take the opportunity to clean up the pg_dump header files somewhat, to avoid circularity. Author: Joachim Wieland, revised by Álvaro Herrera Reviewed by Peter Eisentraut http://git.postgresql.org/pg/commitdiff/0eea8047bf0e15b402b951e383e39236bdfe57d5
  • Blind attempt at fixing Win32 pg_dump issues. Per buildfarm failures http://git.postgresql.org/pg/commitdiff/076d29a1eed5fe51fb2b25b98fcde9dd7c506902

Tom Lane a poussé :

  • Print planning time only in EXPLAIN ANALYZE, not plain EXPLAIN. We've gotten enough push-back on that change to make it clear that it wasn't an especially good idea to do it like that. Revert plain EXPLAIN to its previous behavior, but keep the extra output in EXPLAIN ANALYZE. Per discussion. Internally, I set this up as a separate flag ExplainState.summary that controls printing of planning time and execution time. For now it's just copied from the ANALYZE option, but we could consider exposing it to users. http://git.postgresql.org/pg/commitdiff/90063a7612e2730f7757c2a80ba384bbe7e35c4b
  • Support timezone abbreviations that sometimes change. Up to now, PG has assumed that any given timezone abbreviation (such as "EDT") represents a constant GMT offset in the usage of any particular region; we had a way to configure what that offset was, but not for it to be changeable over time. But, as with most things horological, this view of the world is too simplistic: there are numerous regions that have at one time or another switched to a different GMT offset but kept using the same timezone abbreviation. Almost the entire Russian Federation did that a few years ago, and later this month they're going to do it again. And there are similar examples all over the world. To cope with this, invent the notion of a "dynamic timezone abbreviation", which is one that is referenced to a particular underlying timezone (as defined in the IANA timezone database) and means whatever it currently means in that zone. For zones that use or have used daylight-savings time, the standard and DST abbreviations continue to have the property that you can specify standard or DST time and get that time offset whether or not DST was theoretically in effect at the time. However, the abbreviations mean what they meant at the time in question (or most recently before that time) rather than being absolutely fixed. The standard abbreviation-list files have been changed to use this behavior for abbreviations that have actually varied in meaning since 1970. The old simple-numeric definitions are kept for abbreviations that have not changed, since they are a bit faster to resolve. While this is clearly a new feature, it seems necessary to back-patch it into all active branches, because otherwise use of Russian zone abbreviations is going to become even more problematic than it already was. This change supersedes the changes in commit 513d06ded et al to modify the fixed meanings of the Russian abbreviations; since we've not shipped that yet, this will avoid an undesirably incompatible (not to mention incorrect) change in behavior for timestamps between 2011 and 2014. This patch makes some cosmetic changes in ecpglib to keep its usage of datetime lookup tables as similar as possible to the backend code, but doesn't do anything about the increasingly obsolete set of timezone abbreviation definitions that are hard-wired into ecpglib. Whatever we do about that will likely not be appropriate material for back-patching. Also, a potential free() of a garbage pointer after an out-of-memory failure in ecpglib has been fixed. This patch also fixes pre-existing bugs in DetermineTimeZoneOffset() that caused it to produce unexpected results near a timezone transition, if both the "before" and "after" states are marked as standard time. We'd only ever thought about or tested transitions between standard and DST time, but that's not what's happening when a zone simply redefines their base GMT offset. In passing, update the SGML documentation to refer to the Olson/zoneinfo/ zic timezone database as the "IANA" database, since it's now being maintained under the auspices of IANA. http://git.postgresql.org/pg/commitdiff/b2cbced9eef20692b51a84d68d469627f4fc43ac
  • Re-pgindent src/bin/pg_dump/*. Seems to have gotten rather messy lately, as a consequence of a couple of large recent commits. http://git.postgresql.org/pg/commitdiff/7584649a1c58029a83a7a57d74cedcf1af434c97
  • Fix core dump in pg_dump --binary-upgrade on zero-column composite type. This reverts nearly all of commit 28f6cab61ab8958b1a7dfb019724687d92722538 in favor of just using the typrelid we already have in pg_dump's TypeInfo struct for the composite type. As coded, it'd crash if the composite type had no attributes, since then the query would return no rows. Back-patch to all supported versions. It seems to not really be a problem in 9.0 because that version rejects the syntax "create type t as ()", but we might as well keep the logic similar in all affected branches. Report and fix by Rushabh Lathia. http://git.postgresql.org/pg/commitdiff/5c38a1d4ecce849b1e105f59ccca5a926181e4f0
  • Avoid core dump in _outPathInfo() for Path without a parent RelOptInfo. Nearly all Paths have parents, but a ResultPath representing an empty FROM clause does not. Avoid a core dump in such cases. I believe this is only a hazard for debugging usage, not for production, else we'd have heard about it before. Nonetheless, back-patch to 9.1 where the troublesome code was introduced. Noted while poking at bug #11703. http://git.postgresql.org/pg/commitdiff/5ba062ee44c35b4dc49ccf869fe48f3c6f5f926f
  • Declare mkdtemp() only if we're providing it. Follow our usual style of providing an "extern" for a standard library function only when we're also providing the implementation. This avoids issues when the system headers declare the function slightly differently than we do, as noted by Caleb Welton. We might have to go to the extent of probing to see if the system headers declare the function, but let's not do that until it's demonstrated to be necessary. Oversight in commit 9e6b1bf258170e62dac555fc82ff0536dfe01d29. Back-patch to all supported branches, as that was. http://git.postgresql.org/pg/commitdiff/60f8133dc95d8d55ac52186eb9988559816cac49

Stephen Frost a poussé :

Fujii Masao a poussé :

  • Fix bug in handling of connections that pg_receivexlog creates. Previously pg_receivexlog created new connection for WAL streaming even though another connection which had been established to create or delete the replication slot was being left. This caused the unused connection to be left uselessly until pg_receivexlog exited. This bug was introduced by the commit d9f38c7. This patch changes pg_receivexlog so that the connection for the replication slot is reused for WAL streaming. Andres Freund, slightly modified by me, reviewed by Michael Paquier http://git.postgresql.org/pg/commitdiff/504c717599b20cdaf09e9d7b6ecd152cc7a3a71a

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Petr (PJMODOS) Jelinek sent in another revision of a patch to track commit timestamps.
  • Petr (PJMODOS) Jelinek sent in a patch to add an access method for sequences.
  • Andrew Dunstan and Pavel Stehule traded patches to add some missing functionality to JSON[B].
  • Michael Paquier sent in another revision of a patch to add generate_series(numeric, numeric).
  • Michael Paquier sent in another revision of a patch to split builtins.h to quote.h.
  • SAWADA Masahiko sent in a patch to drop any statistics of a table after it's truncated.
  • Fabrízio de Royes Mello sent in another revision of a patch to add CINE for materialized views.
  • Marco Nenciarini sent in another WIP revision of a patch to implement incremental backup.
  • Marti Raudsepp sent in a patch to unify checks for catalog modification.
  • Amit Kapila sent in another revision of a patch to scale shared buffer eviction.
  • Etsuro Fujita sent in two more revisions of a patch to allow foreign tables to be in a table inheritance hierarchy.
  • Dimitri Fontaine sent in two revisions of a patch to add a new type of event trigger: table_rewrite.
  • Robert Haas sent in a patch to implement group locking, a support for parallelism.
  • Stephen Frost sent in a patch to add a few additional role attributes (all requested by users or clients in various forums) for common operations which currently require superuser privileges.
  • Robert Haas sent in a WIP patch to create a dynahash replacement for buffer table.
  • Abhijit Menon-Sen sent in another revision of a patch to implement pg_audit, an extension.
  • David Rowley sent in another revision of a patch to enable inner join removals under certain conditions.
  • Alexander Korotkov sent in a WIP patch to re-add access method extensibility, and an extension using this machinery.
  • Álvaro Herrera sent in another revision of a patch to enable replicating DROP commands across servers.
  • Lucas Lersch sent in a patch to log buffer request trace data.
  • Atri Sharma sent in a patch to implement UPDATE table SET(*)= and similar constructs.
  • Adam Brightwell sent in a patch to implement a directory permission system that allows for providing a directory read/write capability to directories for COPY TO/FROM and Generic File Access Functions to non-superusers.
  • Jeff Davis sent in another revision of a patch to allow better memory accounting for use in an eventual memory-bounded hash aggregate.
  • SAWADA Masahiko and Fabrízio de Royes Mello traded patches to implement REINDEX SCHEMA.
  • Michael Banck sent in a patch to log a notice that checkpoint is to be written on shutdown.
  • Michael Paquier sent in another revision of a patch to give better support of exported snapshots to pg_dump.
  • Rahila Syed sent in another revision of a patch to compress full-page writes.
  • Craig Ringer sent in a patch to detect custom-format dumps in psql and emit a useful error.
  • Pavel Stehule sent in another revision of a patch to add an Assert statement to PL/pgsql.
  • Craig Ringer sent in a patch to add an errhint_log, akin to errdetail_log. This allows a different HINT to be sent to the server error log and to the client, which will be useful where there's security sensitive information that's more appropriate for a HINT than a DETAIL message.
  • Furuya Osamu sent in another revision of a patch to pg_receivexlog --status-interval to add fsync feedback.
  • Fujii Masao sent in a patch to fix an infelicity between RETURNING and POLICY.
  • Fujii Masao sent in a patch to fix a bug in recovery mode.
  • Andreas 'ads' Scherbaum sent in another revision of a patch to better describe PostgreSQL's rounding behavior.
  • Dag-Erling Smørgrav sent in a patch to add ssl_protocols configuration option.
  • Adam Brightwell sent in another revision of a patch to add a has_privs_of_role() function.
  • Ali Akbar sent in another revision of a patch to add array_agg() functionality for array types.
  • Emre Hasegeli sent in a patch to add a BRIN range operator class.
  • Marko (johto) Tiikkaja sent in a patch to fix a bug where a comment in the pg_crypto extension refers to the wrong file.

par N Bougain le vendredi 31 octobre 2014 à 00h48

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 à 20h58

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 à 18h27

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 à 21h55

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 à 21h52

dimanche 28 septembre 2014

Actualités PostgreSQL.fr

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 dimanche 28 septembre 2014 à 23h12

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 à 11h00

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 à 21h17

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 à 22h33

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 à 11h25

lundi 16 juin 2014

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

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&aposest 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&aposécriture

En fait le problème apparaît uniquement en écriture. À haut débit, celles-ci peuvent être jusqu&aposà 30% plus lentes qu&aposavec 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&aposintégrité des caches lors des écritures. EXT4 va en effet émettre un "write barrier" (barrière d&aposécriture) à chaque synchronisation des caches (fsync). De la sorte, si un crash complet du système survient (ou si quelqu&aposun 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&aposest 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&aposécriture sur EXT4

Et maintenant Ext3 aussi...

Après mise à jour de ma machine de développement qui utilise EXT3 sous Debian Wheezy, j&aposai découvert au détours d&aposun mount l&aposapparition d&aposun barrier=1. Il semble que la fonctionnalité "barrier" d&aposEXT4 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&aposaccord que la chose n&aposest pas à faire sur une machine où l&aposatomicité des écritures est critique. Mais dans tous les autres cas, c&aposest un gain de 30% en écriture, ce qui m&aposa un peu sauvé la vie lors d&aposune énorme migration d&aposun PHPBB de 12 ans d&aposage sous Drupal :).

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