PostgreSQL La base de donnees la plus sophistiquee au monde.

La planète francophone de PostgreSQL

jeudi 17 avril 2014

Dimitri Fontaine

Meetup PostgreSQL à Paris

Hier soir se déroulait le troisième Meetup PostgreSQL à Paris, et je crois pouvoir dire que tous les participants étaient ravis de cette édition.

Malheureusement, quelques uns sont restés bloqués du mauvais côté de la porte hier soir, j'en suis navré. Nous essayerons d'étendre nos horaires d'accueil lors des prochaines rencontres, dans la mesure du possible.

Les sponsors de la soirée

Un grand merci à nos sponsors : hier soir eNovance nous accueillait dans une superbe salle de conférence et Hegoa s'est chargée de mettre à disposition des participants un buffet très apprécié.

Si vous désirez intervenir auprès de notre organisation en tant que sponsor ou bien en tant que conférencier, n'hésitez pas à m'envoyer un email afin de nous organiser au mieux !

Annonce Officielle du Ministère de l'Intérieur

Vincent Laborie a eu l'opportunité hier soir de faire la première annonce officielle de l'utilisation de PostgreSQL en production au sein des Systèmes d'Information de la Sécurité Intérieure, c'est à dire dans les applications critiques déployées au sein du Ministère de l'Intérieur et dont les utilisateurs sont nos policiers et gendarmes.

OpenStack et PostgreSQL

Julien Danjou nous a ensuite présenté l'utilisation faite par OpenStack de PostgreSQL, et nous retiendrons qu'il reste beaucoup de progrès à faire en la matière, à commencer par une intégration véritable des tests unitaires.

Une fois de plus, une analyse faite avec assez de recul montre que l'utilisation d'un ORM reste un problème car il impose un nivellement par le bas des fonctionnalités exploitées dans les applications. La portabilité du SGBD cible ne s'obtient qu'à un coût exhorbitant.

Learn everything you need to build a successful Python project

Si vous voulez approfondir le sujet, je détaille mon point de vue sur les problèmes d'intégration liés aux ORM dans l'excellent livre The Hacker's Guide to PythonJulien me fait l'honneur d'une interview.

Tsung et PostgreSQL

Rodolphe Quiédeville nous a ensuite présenté le merveilleux outil Tsung dans un cas d'utilisation convainquant. Ses slides sont déjà disponibles en ligne : Un Tsung vaut mieux que 2 "croisons les doigts".

Tsung is an open-source multi-protocol distributed load testing tool

Prochaine rencontre

Les détails sont encore à valider, à priori la prochaine rencontre des utilisateurs de PostgreSQL à Paris se tiendra au moins de juin, un mois déjà chargé en activité PostgreSQL avec non seulement le PGDay France à Toulon les 5 et 6 juin, mais également une belle participation de notre communauté à la conférence PHP Tour qui se tiendra à Lyon les 23 et 24 Juin 2014.

Note : c'est la rencontre qui est située à Paris, les utilisateurs de PostgreSQL sont les bienvenus d'où qu'ils viennent !

par dim@tapoueh.org (Dimitri Fontaine) le jeudi 17 avril 2014 à 11h28

mardi 15 avril 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 13 avril 2014

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

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

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en avril

PostgreSQL Local

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Heikki Linnakangas a poussé :

Robert Haas a poussé :

Tom Lane a poussé :

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

Michael Meskes a poussé :

Bruce Momjian a poussé :

Stephen Frost a poussé :

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

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

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

par N Bougain le mardi 15 avril 2014 à 22h14

samedi 12 avril 2014

Guillaume Lelarge

Nouvelle série d'articles pour GLMF

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

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

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

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

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

par Guillaume Lelarge le samedi 12 avril 2014 à 16h11

vendredi 11 avril 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 6 avril 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en avril

PostgreSQL Local

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Heikki Linnakangas a poussé :

Robert Haas a poussé :

Tom Lane a poussé :

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

Simon Riggs a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

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

par N Bougain le vendredi 11 avril 2014 à 00h46

vendredi 4 avril 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 1er avril 2014

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

Offres d'emplois autour de PostgreSQL en avril

PostgreSQL Local

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Correctifs rejetés (à ce jour)

  • Déception pour tout le monde cette semaine !

par N Bougain le vendredi 4 avril 2014 à 07h06

Nouvelles hebdomadaires de PostgreSQL - 30 mars 2014

Offres d'emplois autour de PostgreSQL en mars

PostgreSQL Local

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Heikki Linnakangas a poussé :

Fujii Masao a poussé :

Bruce Momjian a poussé :

Magnus Hagander a poussé :

Andrew Dunstan a poussé :

Tom Lane a poussé :

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

Noah Misch a poussé :

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

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

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

par N Bougain le vendredi 4 avril 2014 à 06h49

vendredi 28 mars 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 23 mars 2014

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

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

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

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

PostgreSQL Local

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Magnus Hagander a poussé :

Fujii Masao a poussé :

Heikki Linnakangas a poussé :

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

Tom Lane a poussé :

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

Robert Haas a poussé :

Alvaro Herrera a poussé :

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

Bruce Momjian a poussé :

Noah Misch a poussé :

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

Andrew Dunstan a poussé :

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

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

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

par N Bougain le vendredi 28 mars 2014 à 00h15

Nouvelles hebdomadaires de PostgreSQL - 16 mars 2014

Surveillez l'arrivée des correctifs 9.3.4, etc. qui ne tarderont plus, et préparez-vous à mettre à jour.

Les nouveautés des produits dérivés

  • MJSQLView Version 3.48, une interface graphique en Java et compatible PostgreSQL : http://myjsqlview.org

Offres d'emplois autour de PostgreSQL en mars

PostgreSQL Local

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Robert Haas a poussé :

Tom Lane a poussé :

  • Fix tracking of psql script line numbers during \copy from another place. Commit 08146775acd8bfe0fcc509c71857abb928697171 changed do_copy() to temporarily scribble on pset.cur_cmd_source. That was a mighty ugly bit of code in any case, but in particular it broke handleCopyIn's ability to tell whether it was reading from the current script source file (in which case pset.lineno should be incremented for each line of COPY data), or from someplace else (in which case it shouldn't). The former case still worked, the latter not so much. The visible effect was that line numbers reported for errors in a script file would be wrong if there were an earlier \copy that was reading anything other than inline-in-the-script-file data. To fix, introduce another pset field that holds the file do_copy wants the COPY code to use. This is a little bit ugly, but less so than passing the file down explicitly through several layers that aren't COPY-specific. Extracted from a larger patch by Kumar Rajeev Rastogi; that patch also changes printing of COPY command tags, which is not a bug fix and shouldn't get back-patched. This particular idea was from a suggestion by Amit Khandekar, if I'm reading the thread correctly. Back-patch to 9.2 where the faulty code was introduced. http://git.postgresql.org/pg/commitdiff/e85a5ffba8ae559b612b6fbc07acf1b16636887e
  • Avoid transaction-commit race condition while receiving a NOTIFY message. Use TransactionIdIsInProgress, then TransactionIdDidCommit, to distinguish whether a NOTIFY message's originating transaction is in progress, committed, or aborted. The previous coding could accept a message from a transaction that was still in-progress according to the PGPROC array; if the client were fast enough at starting a new transaction, it might fail to see table rows added/updated by the message-sending transaction. Which of course would usually be the point of receiving the message. We noted this type of race condition long ago in tqual.c, but async.c overlooked it. The race condition probably cannot occur unless there are multiple NOTIFY senders in action, since an individual backend doesn't send NOTIFY signals until well after it's done committing. But if two senders commit in close succession, it's certainly possible that we could see the second sender's message within the race condition window while responding to the signal from the first one. Per bug #9557 from Marko Tiikkaja. This patch is slightly more invasive than what he proposed, since it removes the now-redundant TransactionIdDidAbort call. Back-patch to 9.0, where the current NOTIFY implementation was introduced. http://git.postgresql.org/pg/commitdiff/7bae0284eeb0863220260e0d5ac80f0b37053690
  • Allow psql to print COPY command status in more cases. Previously, psql would print the "COPY nnn" command status only for COPY commands executed server-side. Now it will print that for frontend copies too (including \copy). However, we continue to suppress the command status for COPY TO STDOUT, since in that case the copy data has been routed to the same place that the command status would go, and there is a risk of the status line being mistaken for another line of COPY data. Doing that would break existing scripts, and it doesn't seem worth the benefit --- this case seems fairly analogous to SELECT, for which we also suppress the command status. Kumar Rajeev Rastogi, with substantial review by Amit Khandekar http://git.postgresql.org/pg/commitdiff/f70a78bc1f5556546d809a8164b9ba6a907f266f
  • Prevent interrupts while reporting non-ERROR elog messages. This should eliminate the risk of recursive entry to syslog(3), which appears to be the cause of the hang reported in bug #9551 from James Morton. Arguably, the real problem here is auth.c's willingness to turn on ImmediateInterruptOK while executing fairly wide swaths of backend code. We may well need to work at narrowing the code ranges in which the authentication_timeout interrupt is enabled. For the moment, though, this is a cheap and reasonably noninvasive fix for a field-reported failure; the other approach would be complex and not necessarily bug-free itself. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/6c461cb92f295788446fbd5659b92e279244c725
  • Update time zone data files to tzdata release 2014a. DST law changes in Fiji, Turkey; historical changes in Israel, Ukraine. http://git.postgresql.org/pg/commitdiff/aba7f56779f9ca231f6b612f1566771e3a9380e8
  • First-draft release notes for 9.3.4. As usual, the release notes for older branches will be made by cutting these down, but put them up for community review first. http://git.postgresql.org/pg/commitdiff/e3c9f23250fc445568b2aefab8bcdc25371cff5b
  • Fix advertised dispsize for libpq's sslmode connection parameter. "8" was correct back when "disable" was the longest allowed value, but since "verify-full" was added, it should be "12". Given the lack of complaints, I wouldn't be surprised if nobody is actually using these values ... but still, if they're in the API, they should be right. Noticed while pursuing a different problem. It's been wrong for quite a long time, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/f4051e363c1757a5fa05825a361d9dd0e54508bc
  • Fix unportable shell-script syntax in pg_upgrade's test.sh. I discovered the hard way that on some old shells, the locution FOO="" unset FOO does not behave the same as FOO=""; unset FOO and in fact leaves FOO set to an empty string. test.sh was inconsistently spelling it different ways on adjacent lines. This got broken relatively recently, in commit c737a2e56, so the lack of field reports to date doesn't represent a lot of evidence that the problem is rare. http://git.postgresql.org/pg/commitdiff/0268d21e5d3c732bf5543d68a6d870e4eee7e673

Heikki Linnakangas a poussé :

  • Fix race condition in B-tree page deletion. In short, we don't allow a page to be deleted if it's the rightmost child of its parent, but that situation can change after we check for it.
  • Problem: We check that the page to be deleted is not the rightmost child of its parent, and then lock its left sibling, the page itself, its right sibling, and the parent, in that order. However, if the parent page is split after the check but before acquiring the locks, the target page might become the rightmost child, if the split happens at the right place. That leads to an error in vacuum (I reproduced this by setting a breakpoint in debugger): ERROR: failed to delete rightmost child 41 of block 3 in index "foo_pkey" We currently re-check that the page is still the rightmost child, and throw the above error if it's not. We could easily just give up rather than throw an error, but that approach doesn't scale to half-dead pages. To recap, although we don't normally allow deleting the rightmost child, if the page is the *only* child of its parent, we delete the child page and mark the parent page as half-dead in one atomic operation. But before we do that, we check that the parent can later be deleted, by checking that it in turn is not the rightmost child of the grandparent (potentially recursing all the way up to the root). But the same situation can arise there - the grandparent can be split while we're not holding the locks. We end up with a half-dead page that we cannot delete. To make things worse, the keyspace of the deleted page has already been transferred to its right sibling. As the README points out, the keyspace at the grandparent level is "out-of-whack" until the half-dead page is deleted, and if enough tuples with keys in the transferred keyspace are inserted, the page might get split and a downlink might be inserted into the grandparent that is out-of-order. That might not cause any serious problem if it's transient (as the README ponders), but is surely bad if it stays that way.
  • Solution: This patch changes the page deletion algorithm to avoid that problem. After checking that the topmost page in the chain of to-be-deleted pages is not the rightmost child of its parent, and then deleting the pages from bottom up, unlink the pages from top to bottom. This way, the intermediate stages are similar to the intermediate stages in page splitting, and there is no transient stage where the keyspace is "out-of-whack". The topmost page in the to-be-deleted chain doesn't have a downlink pointing to it, like a page split before the downlink has been inserted. This also allows us to get rid of the cleanup step after WAL recovery, if we crash during page deletion. The deletion will be continued at next VACUUM, but the tree is consistent for searches and insertions at every step. This bug is old, all supported versions are affected, but this patch is too big to back-patch (and changes the WAL record formats of related records). We have not heard any reports of the bug from users, so clearly it's not easy to bump into. Maybe backpatch later, after this has had some field testing. Reviewed by Kevin Grittner and Peter Geoghegan. http://git.postgresql.org/pg/commitdiff/efada2b8e920adfdf7418862e939925d2acd1b89
  • In WAL replay, restore GIN metapage unconditionally to avoid torn page. We don't take a full-page image of the GIN metapage; instead, the WAL record contains all the information required to reconstruct it from scratch. But to avoid torn page hazards, we must re-initialize it from the WAL record every time, even if it already has a greater LSN, similar to how normal full page images are restored. This was highly unlikely to cause any problems in practice, because the GIN metapage is small. We rely on an update smaller than a 512 byte disk sector to be atomic elsewhere, at least in pg_control. But better safe than sorry, and this would be easy to overlook if more fields are added to the metapage so that it's no longer small. Reported by Noah Misch. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/fecfc2b913c4be5eeed24b32ef51a3239580bd1e
  • Items on GIN data pages are no longer always 6 bytes; update gincostestimate. Also improve the comments a bit. http://git.postgresql.org/pg/commitdiff/17d787a3b160eefb2ff4a3fdf12ca1fedc02cbc1
  • Only WAL-log the modified portion in an UPDATE, if possible. When a row is updated, and the new tuple version is put on the same page as the old one, only WAL-log the part of the new tuple that's not identical to the old. This saves significantly on the amount of WAL that needs to be written, in the common case that most fields are not modified. Amit Kapila, with a lot of back and forth with me, Robert Haas, and others. http://git.postgresql.org/pg/commitdiff/a3115f0d9ec1ac93b82156535dc00b10172a4fe7
  • Fix a couple of typos in docs. Thom Brown http://git.postgresql.org/pg/commitdiff/16ff08b79443cb1a9963e77530b307156d904d8b
  • Allow opclasses to provide tri-valued GIN consistent functions. With the GIN "fast scan" feature, GIN can skip items without fetching all the keys for them, if it can prove that they don't match regardless of those keys. So far, it has done the proving by calling the boolean consistent function with all combinations of TRUE/FALSE for the unfetched keys, but since that's O(n^2), it becomes unfeasible with more than a few keys. We can avoid calling consistent with all the combinations, if we can tell the operator class implementation directly which keys are unknown. This commit includes a triConsistent function for the built-in array and tsvector opclasses. Alexander Korotkov, with some changes by me. http://git.postgresql.org/pg/commitdiff/c5608ea26a1f51998ad3cf987c3f0bda643c87a8

Fujii Masao a poussé :

Bruce Momjian a poussé :

Magnus Hagander a poussé :

  • Cleanups from the remove-native-krb5 patch. krb_srvname is actually not available anymore as a parameter server-side, since with gssapi we accept all principals in our keytab. It's still used in libpq for client side specification. In passing remove declaration of krb_server_hostname, where all the functionality was already removed. Noted by Stephen Frost, though a different solution than his suggestion http://git.postgresql.org/pg/commitdiff/0294023a6b1c5df7683707a77238ab634d4ea8c1

Peter Eisentraut a poussé :

Alvaro Herrera a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Kyotaro HORIGUCHI sent in another revision of a patch to get more from indexes.
  • Dimitri Fontaine sent in another revision of a patch to implement a control path for extensions.
  • Kyotaro HORIGUCHI and Etsuro Fujita traded patches to allow foreign tables to be part of table inheritance.
  • Alvaro Herrera sent in another revision of a patch to store extension options.
  • Christian Kruse and Amit Kapila traded patches to show relation and tuple infos of a lock to acquire.
  • Peter Geoghegan, Alexander Korotkov and Andrew Dunstan traded patches around jsonb and nested hstore.
  • Mitsumasa KONDO and Heikki Linnakangas traded patches to allow Gaussian distribution as an option in pgbench.
  • Pavel Stehule sent in another revision of a patch to allow choosing more different border styles using Unicode characters in psql.
  • Kaigai Kouhei sent in two more flocks of patches to implement custom plan nodes.
  • Kaigai Kouhei sent in another revision of a patch to implemnt cache-only table scans.
  • Andres Freund sent in another revision of a patch to implement changeset extraction.
  • Rukh Meski sent in two more revisions of a patch to implement UPDATE/DELETE ... ORDER BY ... LIMIT ...
  • Bruce Momjian sent in another revision of a patch to fix a pg_archivecleanup bug.
  • Andres Freund and Heikki Linnakangas traded patches to fix an issue with memory ordering in LWLockRelease, WakeupWaiters, WALInsertSlotRelease.
  • Vaishnavi Prabakaran sent in a patch to implement a VIEW of pg_hba.conf.
  • Kyotaro HORIGUCHI sent in a patch to fix an issue where archive recovery doesn't complete in some situations.
  • Joshua Yanovski sent in a WIP patch to improve the way (partial index)-only scans work.
  • Peter Geoghegan and Andrew Dunstan traded patches for jsonb.
  • Haribabu Kommi sent in a PoC patch to check whether a buffer pool split can improve performance.
  • Michael Paquier sent in a patch to fix a typo.

par N Bougain le vendredi 28 mars 2014 à 00h04

lundi 24 mars 2014

Actualités PostgreSQL.fr

PostgreSQL 9.3.4, 9.2.8, 9.1.13, 9.0.17, et 8.4.21 publiées

Le PostgreSQL Global Development Group a publié une mise-à-jour de toutes les versions supportées du SGBD, soit les versions 9.3.4, 9.2.8, 9.1.13, 9.0.17, et 8.4.21.

Le PostgreSQL Global Development Group a publié une mise-à-jour de toutes les versions supportées du SGBD, soit les versions 9.3.4, 9.2.8, 9.1.13, 9.0.17, et 8.4.21.

Cette version corrige un problème de corruption des données par réplication et reprise sur panne sur la version 9.3, ainsi que quelques autres problèmes sur toutes les versions. Il est urgent pour tous les utilisateurs de version 9.3 d'effectuer la mise à jour. L'urgence est moindre pour les autres versions.

La corruption de données sous PostgreSQL 9.3 affecte les serveurs de secours en réplication binaire, les serveurs restaurés par PITR et les serveurs autonomes en cas de reprise sur incident.

Le bogue entraîne une corruption d'index irréparable lors de la récupération du fait d'un rejeu incorrect des opérations de verrous de niveau ligne. Cela peut conduire à des résultats de requête inconsistants en fonction de l'utilisation ou non d'un index, et éventuellement amener à la violation de clés primaires ou problèmes similaires. 

C'est pour cette raison qu'il est nécessaire de recréer tous les serveurs de secours après l'application de la mise à jour.

Autres correctifs sur la seule version 9.3 :

  • Assurance que les fichiers de statistiques des bases supprimées sont effacés ;
  • Permettre aux vues matérialisées d'être référencées par les requêtes UPDATE et DELETE ;
  • Ajout du paramètre data_checksum en lecture seule ;
  • Empêcher les propagations abusives d'opérateurs dans postgres_fdw.

Ce correctif résoud également d'autres problèmes sur les autres versions de PostgreSQL, à savoir :

  • Résolution d'un problème de consistence temporelleavec NOTIFY ;
  • Permettre l'annulation de l'exécution d'une expression rationnelle ;
  • Vérification des index sur les nouvelles colonnes insérées plus performantes ;
  • Empêcher la déconnexion prématurée du walsender ;
  • Empêcher les erreurs de mémoire sur les versions de Windows les plus récentes ;
  • Mise-à-jour des fichiers de fuseaux horaires.

Les autres modifications et les détails des problèmes ci-dessus sont consultables dans les « Release Notes ».

Des informations complémentaires concernant deux des bogues affectant la version 9.3 se trouvent sur la page Wiki de la mise à jour vers 9.3.4.

L'attention des utilisateurs de versions 8.4 est attirée sur le fait qu'elle atteint sa fin de vie (EOL) d'ici trois mois, conformément à la Politique de Versions. Cela signifie que nous approchons de la dernière version de cette branche. Il est recomandé de prévoir la migration vers une version plus récente de PostgreSQL.

Comme pour toute mise-à-jour mineure, il n'est pas nécessaire de prévoir un export/import des bases ou d'utiliser pg_upgrade pour l'appliquer. Il suffit d'arrêter l'instance et de mettre à jour les binaires. Des étapes supplémentaires peuvent être nécessaires si plusieurs mises-à-jour mineures ont été omises. On se référera aux notes de versions pour les détails.

Liens : 

par SAS le lundi 24 mars 2014 à 15h11

vendredi 14 mars 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 9 mars 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

PostgreSQL Local

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Stephen Frost a poussé :

  • Another round of Coverity fixes. Additional non-security issues/improvements spotted by Coverity. In backend/libpq, no sense trying to protect against port->hba being NULL after we've already dereferenced it in the switch() statement. Prevent against possible overflow due to 32bit arithmitic in basebackup throttling (not yet released, so no security concern). Remove nonsensical check of array pointer against NULL in procarray.c, looks to be a holdover from 9.1 and earlier when there were pointers being used but now it's just an array. Remove pointer check-against-NULL in tsearch/spell.c as we had already dereferenced it above (in the strcmp()). Remove dead code from adt/orderedsetaggs.c, isnull is checked immediately after each tuplesort_getdatum() call and if true we return, so no point checking it again down at the bottom. Remove recently added minor error-condition memory leak in pg_regress. http://git.postgresql.org/pg/commitdiff/5592ebac55460866da867df5c783c34e3c9a7cae
  • Fix issues with pg_ctl. The new, small, free_readfile managed to have bug in it which could cause it to try and free something it shouldn't, and fix the case where it was being called with an invalid pointer leading to a segfault. Noted by Bruce Momjian, issues introduced and fixed by me. http://git.postgresql.org/pg/commitdiff/eb933162cdcbcaa5c56c75eb21b9c055af9748a0
  • Allocate fresh memory for post_opts/exec_path. Instead of having read_post_opts() depend on the memory allocated for the config file (which is now getting free'd), pg_strdup() for post_opts and exec_path (similar to how it's being done elsewhere). Noted by Thom Brown. http://git.postgresql.org/pg/commitdiff/dd917bb793b27f8c7616f0e64f9a119e8d98eb24

Robert Haas a poussé :

Alvaro Herrera a poussé :

  • pg_dump, et al: Add --if-exists option This option makes pg_dump, pg_dumpall and pg_restore inject an IF EXISTS clause to each DROP command they emit. (In pg_dumpall, the clause is not added to individual objects drops, but rather to the CREATE DATABASE commands, as well as CREATE ROLE and CREATE TABLESPACE.) This allows for a better user dump experience when using --clean in case some objects do not already exist. Per bug #7873 by Dave Rolsky. Author: Pavel Stěhule Reviewed-by: Jeevan Chalke, Álvaro Herrera, Josh Kupershmidt http://git.postgresql.org/pg/commitdiff/9067310cc5dd590e36c2c3219dbf3961d7c9f8cb
  • Constructors for interval, timestamp, timestamptz. Author: Pavel Stěhule, editorialized somewhat by Álvaro Herrera Reviewed-by: Tomáš Vondra, Marko Tiikkaja With input from Fabrízio de Royes Mello, Jim Nasby http://git.postgresql.org/pg/commitdiff/84df54b22e8035addc7108abd9ff6995e8c49264
  • auto_explain: Add logging of trigger execution. Author: Kyotaro HORIGUCHI Reviewed-by: Jaime Casanova http://git.postgresql.org/pg/commitdiff/e2a0fc5363e293d29053d0582a1009bc9fef0276
  • Remove the correct pgstat file on DROP DATABASE. We were unlinking the permanent file, not the non-permanent one. But since the stat collector already unlinks all permanent files on startup, there was nothing for it to unlink. The non-permanent file remained in place, and was copied to the permanent directory on shutdown, so in effect no file was ever dropped. Backpatch to 9.3, where the issue was introduced by commit 187492b6c2e8. Before that, there were no per-database files and thus no file to drop on DROP DATABASE. Per report from Thom Brown. Author: Tomáš Vondra http://git.postgresql.org/pg/commitdiff/2b4f2ab33dea09e47b93a2eb4be05aa4d40b49ee

Peter Eisentraut a poussé :

Heikki Linnakangas a poussé :

  • Rename huge_tlb_pages to huge_pages, and improve docs. Christian Kruse http://git.postgresql.org/pg/commitdiff/f8ce16d0d2645f3e223b1a68cd8f6b2fa3d56627
  • Error out on send failure in walsender loop. I changed the loop in 9.3 to use "goto send_failure" instead of "break" on errors, but I missed this one case. It was a relatively harmless bug: if the flush fails once it will most likely fail again as soon as we try to flush the output again. But it's a bug nevertheless. Report and fix by Andres Freund. http://git.postgresql.org/pg/commitdiff/7558cc95d31edbf1437321d910562494071c5589
  • Fix lastReplayedEndRecPtr calculation when starting from shutdown checkpoint. When entering crash recovery followed by archive recovery, and the latest checkpoint is a shutdown checkpoint, and there are no more WAL records to replay before transitioning from crash to archive recovery, we would not immediately allow read-only connections in hot standby mode even if we could. That's because when starting from a shutdown checkpoint, we set lastReplayedEndRecPtr incorrectly to the record before the checkpoint record, instead of the checkpoint record itself. We don't run the redo routine of the shutdown checkpoint record, but starting recovery from it goes through the same motions, so it should be considered as replayed. Reported by Kyotaro HORIGUCHI. All versions with hot standby are affected, so backpatch to 9.0. http://git.postgresql.org/pg/commitdiff/af246c37c056e3b16be04e899e94e3a100f3918e
  • Do wal_level and hot standby checks when doing crash-then-archive recovery. CheckRequiredParameterValues() should perform the checks if archive recovery was requested, even if we are going to perform crash recovery first. Reported by Kyotaro HORIGUCHI. Backpatch to 9.2, like the crash-then-archive recovery mode. http://git.postgresql.org/pg/commitdiff/956685f82b6983ff17e6a39bd386b11f554715a8
  • isdigit() needs an unsigned char argument. Per the C standard, the routine should be passed an int, with a value that's representable as an unsigned char or EOF. Passing a signed char is wrong, because a negative value is not representable as an unsigned char. Unfortunately no compiler warns about that. http://git.postgresql.org/pg/commitdiff/a0c2fa9b5cfaf9595e8809a68eec929a5052834e
  • Send keepalives from walsender even when busy sending WAL. If walsender doesn't hear from the client for the time specified by wal_sender_timeout, it will conclude the connection or client is dead, and disconnect. When half of wal_sender_timeout has elapsed, it sends a ping to the client, leaving it the remainig half of wal_sender_timeout to respond. However, it only checked if half of wal_sender_timeout had elapsed when it was about to sleep, so if it was busy sending WAL to the client for long enough, it would not send the ping request in time. Then the client would not know it needs to send a reply, and the walsender will disconnect even though the client is still alive. Fix that. Andres Freund, reviewed by Robert Haas, and some further changes by me. Backpatch to 9.3. Earlier versions relied on the client to send the keepalives on its own, and hence didn't have this problem. http://git.postgresql.org/pg/commitdiff/94ae6ba74dfc626efa271461902db1be35d2a551
  • Fix name of syslog_ident GUC in docs. Michael Paquier http://git.postgresql.org/pg/commitdiff/2b8483d69d1be9700abae0dc7c48c5b7edb77498
  • Fix dangling smgr_owner pointer when a fake relcache entry is freed. A fake relcache entry can "own" a SmgrRelation object, like a regular relcache entry. But when it was free'd, the owner field in SmgrRelation was not cleared, so it was left pointing to free'd memory. Amazingly this apparently hasn't caused crashes in practice, or we would've heard about it earlier. Andres found this with Valgrind. Report and fix by Andres Freund, with minor modifications by me. Backpatch to all supported versions. http://git.postgresql.org/pg/commitdiff/55566c9a740144439b54ff3aacbd43d11b6de52f
  • Avoid memcpy() with same source and destination address. The behavior of that is undefined, although unlikely to lead to problems in practice. Found by running regression tests with Valgrind. http://git.postgresql.org/pg/commitdiff/ad7b48ea08d6c33bae0a33c5f2a06272293c0f2f

Andrew Dunstan a poussé :

  • Provide a FORCE NULL option to COPY in CSV mode. This forces an input field containing the quoted null string to be returned as a NULL. Without this option, only unquoted null strings behave this way. This helps where some CSV producers insist on quoting every field, whether or not it is needed. The option takes a list of fields, and only applies to those columns. There is an equivalent column-level option added to file_fdw. Ian Barwick, with some tweaking by Andrew Dunstan, reviewed by Payal Singh. http://git.postgresql.org/pg/commitdiff/3b5e03dca2afea7a2c12dbc8605175d0568b5555

Bruce Momjian a poussé :

Tom Lane a poussé :

  • Remove unused field "evttype". Apparent oversight in commit 3855968f. http://git.postgresql.org/pg/commitdiff/114b26c06fb93d74afd6993d4be49b5b3e960979
  • Add comment that ec_relids excludes "child" EquivalenceClass members. This was already documented a few lines further down, but the comment just beside the field declaration could be misleading. Per gripe from Kyotaro Horiguchi. http://git.postgresql.org/pg/commitdiff/8cf0ad1ea38db3e16ac04b408168df4c937862e6
  • Fix portability issues in recently added make_timestamp/make_interval code. Explicitly reject infinity/NaN inputs, rather than just assuming that something else will do it for us. Per buildfarm. While at it, make some over-parenthesized and under-legible code more readable. http://git.postgresql.org/pg/commitdiff/f1ba94bcd9717b94b36868d6905547e313f3a359
  • Don't reject ROW_MARK_REFERENCE rowmarks for materialized views. We should allow this so that matviews can be referenced in UPDATE/DELETE statements in READ COMMITTED isolation level. The requirement for that is that a re-fetch by TID will see the same row version the query saw earlier, which is true of matviews, so there's no reason for the restriction. Per bug #9398. Michael Paquier, after a suggestion by me http://git.postgresql.org/pg/commitdiff/bf4052faa1c289883799d49f063715161a8a4f1e
  • Avoid getting more than AccessShareLock when deparsing a query. In make_ruledef and get_query_def, we have long used AcquireRewriteLocks to ensure that the querytree we are about to deparse is up-to-date and the schemas of the underlying relations aren't changing. Howwever, that function thinks the query is about to be executed, so it acquires locks that are stronger than necessary for the purpose of deparsing. Thus for example, if pg_dump asks to deparse a rule that includes "INSERT INTO t", we'd acquire RowExclusiveLock on t. That results in interference with concurrent transactions that might for example ask for ShareLock on t. Since pg_dump is documented as being purely read-only, this is unexpected. (Worse, it used to actually be read-only; this behavior dates back only to 8.1, cf commit ba4200246.) Fix this by adding a parameter to AcquireRewriteLocks to tell it whether we want the "real" execution locks or only AccessShareLock. Report, diagnosis, and patch by Dean Rasheed. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/7c31874945120c0a263c5d0fe15ab362e6e5c99d
  • Fix contrib/postgres_fdw to handle multiple join conditions properly. The previous coding supposed that it could consider just a single join condition in any one parameterized path for the foreign table. But in reality, the parameterized-path machinery forces all join clauses that are "movable to" the foreign table to be evaluated at that node; including clauses that we might not consider safe to send across. Such cases would result in an Assert failure in an assert-enabled build, and otherwise in sending an unsafe clause to the foreign server, which might result in errors or silently-wrong answers. A lesser problem was that the cost/rowcount estimates generated for the parameterized path failed to account for any additional join quals that get assigned to the scan. To fix, rewrite postgresGetForeignPaths so that it correctly collects all the movable quals for any one outer relation when generating parameterized paths; we'll now generate just one path per outer relation not one per join qual. Also fix bogus assumptions in postgresGetForeignPlan and estimate_path_cost_size that only safe-to-send join quals will be presented. Based on complaint from Etsuro Fujita that the path costs were being miscalculated, though this is significantly different from his proposed patch. http://git.postgresql.org/pg/commitdiff/83204e100c7855a50ccffd761bcd45474955b5fb
  • Remove unportable use of anonymous unions from reorderbuffer.h. In b89e151054a I had assumed it was ok to use anonymous unions as struct members, but while a longstanding extension in many compilers, it's only been standardized in C11. To fix, remove one of the anonymous unions which tried to hide some implementation specific enum values and give the other a name. The latter unfortunately requires changes in output plugins, but since the feature has only been added a few days ago... Andres Freund http://git.postgresql.org/pg/commitdiff/ea177a3ba7a7901f6467eadb0a407e03d46462fd

Simon Riggs a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Heikki Linnakangas sent in another revision of a patch to reduce the amount of WAL written during update operations.
  • Kaigai Kouhei sent in three more revisions of a patch to implement custom scans.
  • Andres Freund sent in another flock of patches for logical changesets.
  • Florian Pflug sent in another revision of a patch to implement inverse transition functions for aggregates.
  • SIMON Riggs sent in a patch to fix the locking in pg_dump to lower (but still safe) levels.
  • Simon Riggs sent in two more revisions of a patch to reduce the lock level needed for ALTER TABLE.
  • Pavel Raiskup sent in a patch to allow multiple -o/-O options to pg_upgrade.
  • Erik Rijkers sent in a patch to fix typos in decode.
  • Andres Freund sent in a patch to fix an assertion failure in pg_dump.
  • Michael Paquier sent in three revisions of a patch to fix the FORCE NULL option for CSV format COPY.
  • Bruce Momjian and Amit Kapila traded patches to fix an issue when pg_ctl encounters a nonexistent directory.
  • Mitsumasa KONDO sent in another revision of a patch to patch to allow using Gaussian distributions in pgbench.
  • Heikki Linnakangas sent in another revision of a patch to fix an issue with memory ordering in LWLockRelease, WakeupWaiters, and WALInsertSlotRelease.
  • Martín Marqués sent in two revisions of a patch to fix the regression tests for hot standbys.
  • Emre Hasegeli sent in another revision of a patch to add GiST indexing support for inet datatypes.
  • Bruce Momjian sent in a patch to change PQconndefaults() to ignore invalid service files.
  • Tomonari Katsumata sent in two revisions of a patch to clarify what client_min_messages means.
  • Jing Wang sent in a patch to issue a log message to suggest VACUUM FULL if a table is nearly empty.
  • Kyotaro HORIGUCHI sent in another revision of a patch to use indexes in the case of UNION ALL on partitioned tables.

par N Bougain le vendredi 14 mars 2014 à 12h00

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

jeudi 6 mars 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 2 mars 2014

Les versions correctives 9.3.3, 9.2.7, 9.1.12, 9.0.16 et 8.4.20 de PostgreSQL ont été publiées. Mettez à jour dès que possible ! http://www.postgresql.org/about/news/1506/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

PostgreSQL Local

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Robert Haas a poussé :

Bruce Momjian a poussé :

Peter Eisentraut a poussé :

Tom Lane a poussé :

  • Use SnapshotDirty rather than an active snapshot to probe index endpoints. If there are lots of uncommitted tuples at the end of the index range, get_actual_variable_range() ends up fetching each one and doing an MVCC visibility check on it, until it finally hits a visible tuple. This is bad enough in isolation, considering that we don't need an exact answer only an approximate one. But because the tuples are not yet committed, each visibility check does a TransactionIdIsInProgress() test, which involves scanning the ProcArray. When multiple sessions do this concurrently, the ensuing contention results in horrid performance loss. 20X overall throughput loss on not-too-complicated queries is easy to demonstrate in the back branches (though someone's made it noticeably less bad in HEAD). We can dodge the problem fairly effectively by using SnapshotDirty rather than a normal MVCC snapshot. This will cause the index probe to take uncommitted tuples as good, so that we incur only one tuple fetch and test even if there are many such tuples. The extent to which this degrades the estimate is debatable: it's possible the result is actually a more accurate prediction than before, if the endmost tuple has become committed by the time we actually execute the query being planned. In any case, it's not very likely that it makes the estimate a lot worse. SnapshotDirty will still reject tuples that are known committed dead, so we won't give bogus answers if an invalid outlier has been deleted but not yet vacuumed from the index. (Because btrees know how to mark such tuples dead in the index, we shouldn't have a big performance problem in the case that there are many of them at the end of the range.) This consideration motivates not using SnapshotAny, which was also considered as a fix. Note: the back branches were using SnapshotNow instead of an MVCC snapshot, but the problem and solution are the same. Per performance complaints from Bartlomiej Romanski, Josh Berkus, and others. Back-patch to 9.0, where the issue was introduced (by commit 40608e7f949fb7e4025c0ddd5be01939adc79eec). http://git.postgresql.org/pg/commitdiff/fccebe421d0c410e6378fb281419442c84759213
  • Remove dependency on database encoding in citext regression test. Testing convert_to(..., 'ISO-8859-1') fails if there isn't a conversion function available from the database encoding to ISO-8859-1. This has been broken since day one, but the breakage was hidden by pg_do_encoding_conversion's failure to complain, up till commit 49c817eab78c6f0ce8c3bf46766b73d6cf3190b7. Since the data being converted in this test is plain ASCII, no actual conversion need happen (and if it did, it would prove little about citext anyway). So that we still have some code coverage of the convert() family of functions, let's switch to using convert_from, with SQL_ASCII as the specified source encoding. Per buildfarm. http://git.postgresql.org/pg/commitdiff/1161d895d826950cbb736e5872935f3f53cc2e27
  • Allow regex operations to be terminated early by query cancel requests. The regex code didn't have any provision for query cancel; which is unsurprising given its non-Postgres origin, but still problematic since some operations can take a long time. Introduce a callback function to check for a pending query cancel or session termination request, and call it in a couple of strategic spots where we can make the regex code exit with an error indicator. If we ever actually split out the regex code as a standalone library, some additional work will be needed to let the cancel callback function be specified externally to the library. But that's straightforward (certainly so by comparison to putting the locale-dependent character classification logic on a similar arms-length basis), and there seems no need to do it right now. A bigger issue is that there may be more places than these two where we need to check for cancels. We can always add more checks later, now that the infrastructure is in place. Since there are known examples of not-terribly-long regexes that can lock up a backend for a long time, back-patch to all supported branches. I have hopes of fixing the known performance problems later, but adding query cancel ability seems like a good idea even if they were all fixed. http://git.postgresql.org/pg/commitdiff/9662143f0c35d64d7042fbeaf879df8f0b54be32

Heikki Linnakangas a poussé :

Jeff Davis a poussé :

  • Fix crash in json_to_record(). json_to_record() depends on get_call_result_type() for the tuple descriptor of the record that should be returned, but in some cases that cannot be determined. Add a guard to check if the tuple descriptor has been properly resolved, similar to other callers of get_call_result_type(). Also add guard for two other callers of get_call_result_type() in jsonfuncs.c. Although json_to_record() is the only actual bug, it's a good idea to follow convention. http://git.postgresql.org/pg/commitdiff/486ea0b19e08c10ff53e36e46209a928df048281

Alvaro Herrera a poussé :

  • Fix WAL replay of locking an updated tuple We were resetting the tuple's HEAP_HOT_UPDATED flag as well as t_ctid on WAL replay of a tuple-lock operation, which is incorrect when the tuple is already updated. Back-patch to 9.3. The clearing of both header elements was there previously, but since no update could be present on a tuple that was being locked, it was harmless. Bug reported by Peter Geoghegan and Greg Stark in CAM3SWZTMQiCi5PV5OWHb+bYkUcnCk=O67w0cSswPvV7XfUcU5g@mail.gmail.com and CAM-w4HPTOeMT4KP0OJK+mGgzgcTOtLRTvFZyvD0O4aH-7dxo3Q@mail.gmail.com respectively; diagnosis by Andres Freund. http://git.postgresql.org/pg/commitdiff/6bfa88acd3df830a5f7e8677c13512b1b50ae813
  • Allow BASE_BACKUP to be throttled. A new MAX_RATE option allows imposing a limit to the network transfer rate from the server side. This is useful to limit the stress that taking a base backup has on the server. pg_basebackup is now able to specify a value to the server, too. Author: Antonin Houska Patch reviewed by Stefan Radomski, Andres Freund, Zoltán Böszörményi, Fujii Masao, and Álvaro Herrera. http://git.postgresql.org/pg/commitdiff/ef5856fd9b77ef9d0d0c31fb314bb61bbfb1d704

Stephen Frost a poussé :

  • Various Coverity-spotted fixes A number of issues were identified by the Coverity scanner and are addressed in this patch. None of these appear to be security issues and many are mostly cosmetic changes. Short comments for each of the changes follows. Correct the semi-colon placement in be-secure.c regarding SSL retries. Remove a useless comparison-to-NULL in proc.c (value is dereferenced prior to this check and therefore can't be NULL). Add checking of chmod() return values to initdb. Fix a couple minor memory leaks in initdb. Fix memory leak in pg_ctl- involves free'ing the config file contents. Use an int to capture fgetc() return instead of an enum in pg_dump. Fix minor memory leaks in pg_dump. (note minor change to convertOperatorReference()'s API) Check fclose()/remove() return codes in psql. Check fstat(), find_my_exec() return codes in psql. Various ECPG memory leak fixes. Check find_my_exec() return in ECPG. Explicitly ignore pqFlush return in libpq error-path. Change PQfnumber() to avoid doing an strdup() when no changes required. Remove a few useless check-against-NULL's (value deref'd beforehand). Check rmtree(), malloc() results in pg_regress. Also check get_alternative_expectfile() return in pg_regress. http://git.postgresql.org/pg/commitdiff/b1aebbb6a86e96d7b8f3035ac730dfc24652496c

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Josh Berkus sent in a patch to clarify documentation for the upcoming jsonb type.
  • Christian Kruse sent in another revision of a patch to show xid and xmin in pg_stat_activity and pg_stat_replication.
  • Michael Paquier sent in two more revisions of a patch to change the pageinspect extension use the new LSN type.
  • Andres Freund sent in another flock of patches aimed at changeset extraction.
  • Christian Kruse sent in two more revisions of a patch to show relationinfo and tupleinfo of a lock to acquire.
  • Shigeru HANADA sent in a patch to fix an infelicity in the custom scan patches.
  • Pavel Stehule sent in another revision of a patch to change selfuncs to use a cached SnapshotDirty instead of repeated calls to GetActiveSnapshot().
  • Peter Eisentraut sent in a patch to correct the way Windows libraries get installed.
  • Yugo Nagata sent in another revision of a patch to implement to_regclass, etc.
  • Sergey Burladyan and Alex Hunsaker traded patches to fix a memory leak in PL/PerlU.
  • Tom Lane sent in a patch to avoid having deeply nested AND/OR trees in the parser.
  • Andrew Dunstan and Erik Rijkers sent in patches re: nested hstore and jsonb, built on same.
  • Alexander Korotkov sent in another revision of a patch to add a fast GIN scan.
  • Alvaro Herrera sent in a patch to fix an data corruption bug.
  • Noah Misch sent in another revision of a patch to fix the fact that UNION ALL on partitioned tables won't use indexes.
  • Christian Kruse sent in three more revisions of a patch to use MAP_HUGETLB where supported.
  • Michael Paquier sent in a patch to create a define macro for the LSN type's oid in pg_type.h for use by extension developers.
  • Andres Freund sent in a patch to fix an issue where VACUUM FULL/CLUSTER doesn't update pg_class's pg_class.relfrozenxid.
  • Jing Wang sent in a patch to report versions of the server of pg_dump as comments in the output.
  • Peter Eisentraut sent in another revision of a patch to add tests for client programs.
  • Fabien COELHO sent in a patch to fix an omission in pgbench's help output.
  • Dimitri Fontaine sent in two more revisions of a patch to add an extension control path.
  • Simon Riggs and Vik Fearing traded patches to reduce the lock levels needed for ALTER TABLE.
  • Alvaro Herrera and Pavel Stehule traded patches for the upcoming make_timestamp function.
  • Fabrízio de Royes Mello sent in another revision of a patch to allow storing custom reloptions.
  • Alvaro Herrera and Pavel Stehule traded patches to add --if-exists as an option for pg_dump.
  • Noah Misch sent in a patch to make "make check" harder to use as a vulnerability.
  • Kyotaro HORIGUCHI and Heikki Linnakangas traded patches to fix an issue where a hot standby doesn't come up in some situations.
  • Pavel Stehule sent in a patch to allow showing only failed queries in psql.
  • Alexander Korotkov sent in another revision of a patch to optimize the regex case for trigram indexes.
  • Sean Chittenden sent in a patch to fix an omission where pg_dump -Fd doesn't return write(2)'s return status, which was causing it to return success when it had in fact failed.
  • Pavel Stehule sent in a patch to add a --help-variables option to psql.
  • Andrew Dunstan and Ian Lawrence Barwick traded patches to add a FORCE_NULL option for copy COPY in CSV mode.

par N Bougain le jeudi 6 mars 2014 à 01h18

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

samedi 1 mars 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 23 février 2014

Les versions correctives  9.3.3, 9.2.7, 9.1.12, 9.0.16 et 8.4.20 sont disponibles. Mettez à jour dès que possible : http://www.postgresql.org/about/news/1506/ [ndt: version française]

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en février

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

  • Improve documentation about multixact IDs. Per gripe from Josh Berkus. http://git.postgresql.org/pg/commitdiff/e7f409756dac9fedc12d5aece0f8df5efb8d9e01
  • Remove broken code that tried to handle OVERLAPS with a single argument. The SQL standard says that OVERLAPS should have a two-element row constructor on each side. The original coding of OVERLAPS support in our grammar attempted to extend that by allowing a single-element row constructor, which it internally duplicated ... or tried to, anyway. But that code has certainly not worked since our List infrastructure was rewritten in 2004, and I'm none too sure it worked before that. As it stands, it ends up building a List that includes itself, leading to assorted undesirable behaviors later in the parser. Even if it worked as intended, it'd be a bit evil because of the possibility of duplicate evaluation of a volatile function that the user had written only once. Given the lack of documentation, test cases, or complaints, let's just get rid of the idea and only support the standard syntax. While we're at it, improve the error cursor positioning for the wrong-number-of-arguments errors, and inline the makeOverlaps() function since it's only called in one place anyway. Per bug #9227 from Joshua Yanovski. Initial patch by Joshua Yanovski, extended a bit by me. http://git.postgresql.org/pg/commitdiff/a222f7fda6a04ab8ec655cd5a9de5ff70ff916c3
  • Fix some missing .gitignore and "make clean" items in ecpg. Some of the files we optionally link in from elsewhere weren't ignored and/or weren't cleaned up at "make clean". Noted while testing on a machine that needs our version of snprintf.c. http://git.postgresql.org/pg/commitdiff/52acfd27f11ca586f90c2c1255ca9a4a66766b57
  • Avoid using dllwrap to build pgevent in Mingw builds. If this works, we can get rid of configure's support for locating dllwrap ... but let's see what the buildfarm says, first. Hiroshi Inoue http://git.postgresql.org/pg/commitdiff/4f5f485d10cad372a3a0cd8dd70780f1a32f43f0
  • Remove inappropriate EXPORTS line. Looks like this gets added later ... http://git.postgresql.org/pg/commitdiff/ae5266f25910d6e084692a7cdbd02b9e52800046
  • Prevent potential overruns of fixed-size buffers. Coverity identified a number of places in which it couldn't prove that a string being copied into a fixed-size buffer would fit. We believe that most, perhaps all of these are in fact safe, or are copying data that is coming from a trusted source so that any overrun is not really a security issue. Nonetheless it seems prudent to forestall any risk by using strlcpy() and similar functions. Fixes by Peter Eisentraut and Jozef Mlich based on Coverity reports. In addition, fix a potential null-pointer-dereference crash in contrib/chkpass. The crypt(3) function is defined to return NULL on failure, but chkpass.c didn't check for that before using the result. The main practical case in which this could be an issue is if libc is configured to refuse to execute unapproved hashing algorithms (e.g., "FIPS mode"). This ideally should've been a separate commit, but since it touches code adjacent to one of the buffer overrun changes, I included it in this commit to avoid last-minute merge issues. This issue was reported by Honza Horak. Security: CVE-2014-0065 for buffer overruns, CVE-2014-0066 for crypt() http://git.postgresql.org/pg/commitdiff/01824385aead50e557ca1af28640460fa9877d51
  • Document risks of "make check" in the regression testing instructions. Since the temporary server started by "make check" uses "trust" authentication, another user on the same machine could connect to it as database superuser, and then potentially exploit the privileges of the operating-system user who started the tests. We should change the testing procedures to prevent this risk; but discussion is required about the best way to do that, as well as more testing than is practical for an undisclosed security problem. Besides, the same issue probably affects some user-written test harnesses. So for the moment, we'll just warn people against using "make check" when there are untrusted users on the same machine. In passing, remove some ancient advice that suggested making the regression testing subtree world-writable if you'd built as root. That looks dangerously insecure in modern contexts, and anyway we should not be encouraging people to build Postgres as root. Security: CVE-2014-0067 http://git.postgresql.org/pg/commitdiff/6ef325429cad60d7d24504fa25b5318fd4e35379
  • Last-minute updates for release notes. Add entries for security issues. Security: CVE-2014-0060 through CVE-2014-0067 http://git.postgresql.org/pg/commitdiff/7b1fab3fd2e17063fb1ec98e8ff5512a6b3da9b6
  • Do ScalarArrayOp estimation correctly when array is a stable expression. Most estimation functions apply estimate_expression_value to see if they can reduce an expression to a constant; the key difference is that it allows evaluation of stable as well as immutable functions in hopes of ending up with a simple Const node. scalararraysel didn't get the memo though, and neither did gincost_opexpr/gincost_scalararrayopexpr. Fix that, and remove a now-unnecessary estimate_expression_value step in the subsidiary function scalararraysel_containment. Per complaint from Alexey Klyukin. Back-patch to 9.3. The problem goes back further, but I'm hesitant to change estimation behavior in long-stable release branches. http://git.postgresql.org/pg/commitdiff/77585bce03042e8fee62d8df0dde9c008a904699
  • Plug some more holes in encoding conversion. Various places assume that pg_do_encoding_conversion() and pg_server_to_any() will ensure encoding validity of their results; but they failed to do so in the case that the source encoding is SQL_ASCII while the destination is not. We cannot perform any actual "conversion" in that scenario, but we should still validate the string according to the destination encoding. Per bug #9210 from Digoal Zhou. Arguably this is a back-patchable bug fix, but on the other hand adding more enforcing of encoding checks might break existing applications that were being sloppy. On balance there doesn't seem to be much enthusiasm for a back-patch, so fix in HEAD only. While at it, remove some apparently-no-longer-needed provisions for letting pg_do_encoding_conversion() "work" outside a transaction --- if you consider it "working" to silently fail to do the requested conversion. Also, make a few cosmetic improvements in mbutils.c, notably removing some Asserts that are certainly dead code since the variables they assert aren't null are never null, even at process start. (I think this wasn't true at one time, but it is now.) http://git.postgresql.org/pg/commitdiff/49c817eab78c6f0ce8c3bf46766b73d6cf3190b7
  • Prefer pg_any_to_server/pg_server_to_any over pg_do_encoding_conversion. A large majority of the callers of pg_do_encoding_conversion were specifying the database encoding as either source or target of the conversion, meaning that we can use the less general functions pg_any_to_server/pg_server_to_any instead. The main advantage of using the latter functions is that they can make use of a cached conversion-function lookup in the common case that the other encoding is the current client_encoding. It's notationally cleaner too in most cases, not least because of the historical artifact that the latter functions use "char *" rather than "unsigned char *" in their APIs. Note that pg_any_to_server will apply an encoding verification step in some cases where pg_do_encoding_conversion would have just done nothing. This seems to me to be a good idea at most of these call sites, though it partially negates the performance benefit. Per discussion of bug #9210. http://git.postgresql.org/pg/commitdiff/769065c1b2471f484bb48bb58a8bdcf1d12a419c

Robert Haas a poussé :

  • Fix capitalization in README. Vik Fearing http://git.postgresql.org/pg/commitdiff/876f78d57566a60e443d40f7c789c36566749e2f
  • Add a pg_lsn data type, to represent an LSN. Robert Haas and Michael Paquier http://git.postgresql.org/pg/commitdiff/7d03a83f4d0736ba869fa6f93973f7623a27038a
  • pg_lsn macro naming and type behavior revisions. Change pg_lsn_mi so that it can return negative values when subtracting LSNs, and clean up some perhaps ill-considered macro names. http://git.postgresql.org/pg/commitdiff/844a28a9dd1a48045ad1db9246da5e2783c9bd40
  • Switch various builtin functions to use pg_lsn instead of text. The functions in slotfuncs.c don't exist in any released version, but the changes to xlogfuncs.c represent backward-incompatibilities. Per discussion, we're hoping that the queries using these functions are few enough and simple enough that this won't cause too much breakage for users. Michael Paquier, reviewed by Andres Freund and further modified by me. http://git.postgresql.org/pg/commitdiff/6f289c2b7d00f07f13f679092f7c71f78950e9da
  • Document pg_replslot in storage.sgml. Per an observation from Amit Kapila. http://git.postgresql.org/pg/commitdiff/7b3cf9ba9d3d12ad95c0a06cef04f9097a9c65cf
  • Further code review for pg_lsn data type. Change input function error messages to be more consistent with what is done elsewhere. Remove a bunch of redundant type casts, so that the compiler will warn us if we screw up. Don't pass LSNs by value on platforms where a Datum is only 32 bytes, per buildfarm. Move macros for packing and unpacking LSNs to pg_lsn.h so that we can include access/xlogdefs.h, to avoid an unsatisfied dependency on XLogRecPtr. http://git.postgresql.org/pg/commitdiff/694e3d139a9d090c58494428bebfadad216419da
  • Avoid repeated name lookups during table and index DDL. If the name lookups come to different conclusions due to concurrent activity, we might perform some parts of the DDL on a different table than other parts. At least in the case of CREATE INDEX, this can be used to cause the permissions checks to be performed against a different table than the index creation, allowing for a privilege escalation attack. This changes the calling convention for DefineIndex, CreateTrigger, transformIndexStmt, transformAlterTableStmt, CheckIndexCompatible (in 9.2 and newer), and AlterTable (in 9.1 and older). In addition, CheckRelationOwnership is removed in 9.2 and newer and the calling convention is changed in older branches. A field has also been added to the Constraint node (FkConstraint in 8.4). Third-party code calling these functions or using the Constraint node will require updating. Report by Andres Freund. Patch by Robert Haas and Andres Freund, reviewed by Tom Lane. Security: CVE-2014-0062 http://git.postgresql.org/pg/commitdiff/5f173040e324f6c2eebb90d86cf1b0cdb5890f0a

Heikki Linnakangas a poussé :

Magnus Hagander a poussé :

Peter Eisentraut a poussé :

  • doc: Clarify documentation page header customization code. The customization overrode the fast-forward code with its custom Up link. So this is no longer really the fast-forward feature, so we might as well turn that off and override the non-ff template instead, thus removing one mental indirection. Fix the wrong column span declaration. Clarify and update the documentation. http://git.postgresql.org/pg/commitdiff/8c059dffd83384fa0c2fe6050429d601355bc3af
  • pg_basebackup: Add support for relocating tablespaces. Tablespaces can be relocated in plain backup mode by specifying one or more -T olddir=newdir options. Author: Steeve Lennmark <steevel@handeldsbanken.se> Reviewed-by: Peter Eisentraut <peter_e@gmx.net> http://git.postgresql.org/pg/commitdiff/fb05f3ce83d225dd0f39f8860ce04082753e9e98
  • configure.in: Use dnl in place of # where appropriate. The comment added by ed011d9754fd4b76eac0eaa8c057fcfc0c302a6a used #, which means it gets copied into configure, but it doesn't make sense there. So use dnl, which gets dropped when creating configure. http://git.postgresql.org/pg/commitdiff/2c65856b7b444a5e804d4f694438e7444811d26b
  • doc: Improve DocBook XML validity. DocBook XML is superficially compatible with DocBook SGML but has a slightly stricter DTD that we have been violating in a few cases. Although XSLT doesn't care whether the document is valid, the style sheets don't necessarily process invalid documents correctly, so we need to work toward fixing this. This first commit moves the indexterms in refentry elements to an allowed position. It has no impact on the output. http://git.postgresql.org/pg/commitdiff/bb4eefe7bf518e42c73797ea37b033a5d8a8e70a

Noah Misch a poussé :

  • Prevent privilege escalation in explicit calls to PL validators. The primary role of PL validators is to be called implicitly during CREATE FUNCTION, but they are also normal functions that a user can call explicitly. Add a permissions check to each validator to ensure that a user cannot use explicit validator calls to achieve things he could not otherwise achieve. Back-patch to 8.4 (all supported versions). Non-core procedural language extensions ought to make the same two-line change to their own validators. Andres Freund, reviewed by Tom Lane and Noah Misch. Security: CVE-2014-0061 http://git.postgresql.org/pg/commitdiff/537cbd35c893e67a63c59bc636c3e888bd228bc7
  • Shore up ADMIN OPTION restrictions. Granting a role without ADMIN OPTION is supposed to prevent the grantee from adding or removing members from the granted role. Issuing SET ROLE before the GRANT bypassed that, because the role itself had an implicit right to add or remove members. Plug that hole by recognizing that implicit right only when the session user matches the current role. Additionally, do not recognize it during a security-restricted operation or during execution of a SECURITY DEFINER function. The restriction on SECURITY DEFINER is not security-critical. However, it seems best for a user testing his own SECURITY DEFINER function to see the same behavior others will see. Back-patch to 8.4 (all supported versions). The SQL standards do not conflate roles and users as PostgreSQL does; only SQL roles have members, and only SQL users initiate sessions. An application using PostgreSQL users and roles as SQL users and roles will never attempt to grant membership in the role that is the session user, so the implicit right to add or remove members will never arise. The security impact was mostly that a role member could revoke access from others, contrary to the wishes of his own grantor. Unapproved role member additions are less notable, because the member can still largely achieve that by creating a view or a SECURITY DEFINER function. Reviewed by Andres Freund and Tom Lane. Reported, independently, by Jonas Sundman and Noah Misch. Security: CVE-2014-0060 http://git.postgresql.org/pg/commitdiff/fea164a72a7bfd50d77ba5fb418d357f8f2bb7d0
  • Fix handling of wide datetime input/output. Many server functions use the MAXDATELEN constant to size a buffer for parsing or displaying a datetime value. It was much too small for the longest possible interval output and slightly too small for certain valid timestamp input, particularly input with a long timezone name. The long input was rejected needlessly; the long output caused interval_out() to overrun its buffer. ECPG's pgtypes library has a copy of the vulnerable functions, which bore the same vulnerabilities along with some of its own. In contrast to the server, certain long inputs caused stack overflow rather than failing cleanly. Back-patch to 8.4 (all supported versions). Reported by Daniel Schüssler, reviewed by Tom Lane. Security: CVE-2014-0063 http://git.postgresql.org/pg/commitdiff/4318daecc959886d001a6e79c6ea853e8b1dfb4b
  • Predict integer overflow to avoid buffer overruns. Several functions, mostly type input functions, calculated an allocation size such that the calculation wrapped to a small positive value when arguments implied a sufficiently-large requirement. Writes past the end of the inadvertent small allocation followed shortly thereafter. Coverity identified the path_in() vulnerability; code inspection led to the rest. In passing, add check_stack_depth() to prevent stack overflow in related functions. Back-patch to 8.4 (all supported versions). The non-comment hstore changes touch code that did not exist in 8.4, so that part stops at 9.0. Noah Misch and Heikki Linnakangas, reviewed by Tom Lane. Security: CVE-2014-0064 http://git.postgresql.org/pg/commitdiff/31400a673325147e1205326008e32135a78b4d8a

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Emre Hasegeli sent in another revision of a patch to improve the display of wide tables in psql.
  • SAWADA Masahiko sent in a patch to fix an issue where pg_basebackup skips the pg_replslot directory.
  • Bruce Momjian sent in a patch to update the defaults for work_mem and maintenance_work_mem to post-1990s server specifications.
  • Etsuro Fujita sent in a patch to fix an issue where create_foreignscan_path() does not set the rowcounts based on ParamPathInfo when the path is a parameterized path.
  • Heikki Linnakangas sent in two more revisions of a patch to fix a memory ordering issue in LWLockRelease, WakeupWaiters, and WALInsertSlotRelease.
  • Mitsumasa KONDO and Fabien COELHO traded patches to create an option for pgbench to use a Guassian distribution.
  • Emre Hasegeli sent in two more revisions of a patch to add GiST indexing support for inet types.
  • Pavel Stehule sent in another revision of a patch to add an --if-exists option for pg_dump.
  • Alvaro Herrera and Pavel Stehule traded patches to implement a new make_timestamp() function.
  • Florian Pflug sent in two more revisions of a patch to implement inverse transition functions for aggregates.
  • Hiroshi Inoue sent in a patch to simplify and correct linking to Perl on Mingw.
  • Michael Paquier sent in a patch to quiet a compiler warning which whas recently introduced into pg_backup_archiver.c.
  • Kaigai Kouhei sent in two more revisions of a patch to implement custom scan nodes and use same.
  • Rajeev Rastogi sent in two more revisions of a patch to fix an issue where the PostgreSQL Service on Windows does not start if data directory given is relative path.
  • Ronan Dunklau sent in a patch to implement IMPORT FOREIGN SCHEMA.
  • Christian Kruse sent in three more revisions of a patch to show xid and xmin in pg_stat_activity and pg_stat_replication.
  • Tomas Vondra sent in a patch to fix an issue where pgstat_recv_dropdb(), instead of building path to the temporary file in pg_stat_tmp, builds a path to the permanent file in pg_stat.
  • Christian Kruse sent in another revision of a patch to show relation and tuple information of a lock to acquire.
  • Michael Paquier sent in a patch to update the pageinspect extension so it can see page_header with pg_lsn datatype.
  • Rukh Meski sent in a patch to implement UPDATE/DELETE ... ORDER BY ... LIMIT ...

par N Bougain le samedi 1 mars 2014 à 19h44

Nouvelles hebdomadaires de PostgreSQL - 16 février 2014

Les MAJ correctives  9.3.3, 9.2.7, 9.1.12, 9.0.16 et 8.4.20 seront bientôt disponibles. Préparez-vous à mettre à jour !

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en février

PostgreSQL Local

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Heikki Linnakangas a poussé :

  • Use memmove() instead of memcpy() for copying overlapping regions. In commit d2495f272cd164ff075bee5c4ce95aed11338a36, I fixed this bug in to_tsquery(), but missed the fact that plainto_tsquery() has the same bug. http://git.postgresql.org/pg/commitdiff/6c2744f1d3a0d2e456f8d52776c976da3eb8d3a0
  • Fix WakeupWaiters() to not wake up an exclusive locker unnecessarily. WakeupWaiters() is supposed to wake up all LW_WAIT_UNTIL_FREE waiters of the slot, but the loop incorrectly also woke up the first LW_EXCLUSIVE waiter, if there was no LW_WAIT_UNTIL_FREE waiters in the queue. Noted by Andres Freund. This code is new in 9.4, so no backpatching. http://git.postgresql.org/pg/commitdiff/d699ba41349e4ef397222a7223606fa03f4c4870
  • Change the order that pg_xlog and WAL archive are polled for WAL segments. If there is a WAL segment with same ID but different TLI present in both the WAL archive and pg_xlog, prefer the one with higher TLI. Before this patch, the archive was polled first, for all expected TLIs, and only if no file was found was pg_xlog scanned. This was a change in behavior from 9.3, which first scanned archive and pg_xlog for the highest TLI, then archive and pg_xlog for the next highest TLI and so forth. This patch reverts the behavior back to what it was in 9.2. The reason for this is that if for example you try to do archive recovery to timeline 2, which branched off timeline 1, but the WAL for timeline 2 is not archived yet, we would replay past the timeline switch point on timeline 1 using the archived files, before even looking timeline 2's files in pg_xlog Report and patch by Kyotaro Horiguchi. Backpatch to 9.3 where the behavior was changed. http://git.postgresql.org/pg/commitdiff/4d894b41cd12179b710526eba9dc62c2b99abc4d

Tom Lane a poussé :

  • Don't generate plain-text HISTORY and src/test/regress/README anymore. Providing this information as plain text was doubtless worth the trouble ten years ago, but it seems likely that hardly anyone reads it in this format anymore. And the effort required to maintain these files (in the form of extra-complex markup rules in the relevant parts of the SGML documentation) is significant. So, let's stop doing that and rely solely on the other documentation formats. Per discussion, the plain-text INSTALL instructions might still be worth their keep, so we continue to generate that file. Rather than remove HISTORY and src/test/regress/README from distribution tarballs entirely, replace them with simple stub files that tell the reader where to find the relevant documentation. This is mainly to avoid possibly breaking packaging recipes that expect these files to exist. Back-patch to all supported branches, because simplifying the markup requirements for release notes won't help much unless we do it in all branches. http://git.postgresql.org/pg/commitdiff/2895415205d86cc7ab55acab5f90fd70a7c68f3c
  • Cygwin build fixes. Get rid of use of dlltool for linking the main postgres executable. dlltool is obsolete and we'd prefer to stop depending on it. Also, include $(LDAP_LIBS_FE) in $(libpq_pgport). (It's not clear that this is really needed, or why it's not a linker bug if it is needed. But reports are that it's needed on current Cygwin.) We might want to back-patch this if it works, but first let's see what the buildfarm thinks. Marco Atzeri http://git.postgresql.org/pg/commitdiff/cba6ffaef3987211fb31ba869eb2a476bad6f6d3
  • Get rid of use of dlltool in Mingw builds. We are almost completely out of the dlltool game, if this works. Hiroshi Inoue http://git.postgresql.org/pg/commitdiff/846e91e0223cf9f2821c3ad4dfffffbb929cb027
  • Flush a stray definition of $(DLLTOOL). Even if this is needed, it'd be configure's responsibility to set it. http://git.postgresql.org/pg/commitdiff/7a98d323df2d0839ebb4aab2004c626b64343b76
  • Make gendef.pl emit DATA annotations for global variables. This should make the MSVC build act more like builds for other platforms, i.e. backend global variables will be automatically available to loadable libraries without need for explicit PGDLLIMPORT marking. Craig Ringer http://git.postgresql.org/pg/commitdiff/a5eed4d7706749046e74fa2e23823beb43f254fd
  • Tweak position of $(DLL_DEFFILE) in shared-library link commands. Reading the GNU ld man page suggests that this is order-sensitive and should go in front of library references. Correction to commit 846e91e0223cf9f2821c3ad4dfffffbb929cb027. http://git.postgresql.org/pg/commitdiff/b23fd2d8b3cdfea5b6998c1ab95ae3e776a8f832
  • Remove --enable-auto-import linker switch in Cygwin build. This is expected to make it start failing when contrib modules reference non-PGDLLIMPORT'ed global variables, as the other Windows build methods do. Aside from the value of consistency, the underlying implementation of this switch is pretty ugly and not really something we want to rely on if we have to use PGDLLIMPORT anyway for MSVC. http://git.postgresql.org/pg/commitdiff/30657b796c7fdcaf9c0eb9ac53d4bab6399eb65b
  • In XLogReadBufferExtended, don't assume P_NEW yields consecutive pages. In a database that's not yet reached consistency, it's possible that some segments of a relation are not full-size but are not the last ones either. Because of the way smgrnblocks() works, asking for a new page with P_NEW will fill in the last not-full-size segment --- and if that makes it full size, the apparent EOF of the relation will increase by more than one page, so that the next P_NEW request will yield a page past the next consecutive one. This breaks the relation-extension logic in XLogReadBufferExtended, possibly allowing a page update to be applied to some page far past where it was intended to go. This appears to be the explanation for reports of table bloat on replication slaves compared to their masters, and probably explains some corrupted-slave reports as well. Fix the loop to check the page number it actually got, rather than merely Assert()'ing that dead reckoning got it to the desired place. AFAICT, there are no other places that make assumptions about exactly which page they'll get from P_NEW. Problem identified by Greg Stark, though this is not the same as his proposed patch. It's been like this for a long time, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/6f2aead1ffec6f056dc3c371c2ec6a12d7d5ccd3
  • Improve libpq's error recovery for connection loss during COPY. In pqSendSome, if the connection is already closed at entry, discard any queued output data before returning. There is no possibility of ever sending the data, and anyway this corresponds to what we'd do if we'd detected a hard error while trying to send(). This avoids possible indefinite bloat of the output buffer if the application keeps trying to send data (or even just keeps trying to do PQputCopyEnd, as psql indeed will). Because PQputCopyEnd won't transition out of PGASYNC_COPY_IN state until it's successfully queued the COPY END message, and pqPutMsgEnd doesn't distinguish a queuing failure from a pqSendSome failure, this omission allowed an infinite loop in psql if the connection closure occurred when we had at least 8K queued to send. It might be worth refactoring so that we can make that distinction, but for the moment the other changes made here seem to offer adequate defenses. To guard against other variants of this scenario, do not allow PQgetResult to return a PGRES_COPY_XXX result if the connection is already known dead. Make sure it returns PGRES_FATAL_ERROR instead. Per report from Stephen Frost. Back-patch to all active branches. http://git.postgresql.org/pg/commitdiff/fa4440f51628d692f077d54b8313aea31af087ea
  • Improve text of stub HISTORY file. Per Peter Eisentraut. http://git.postgresql.org/pg/commitdiff/dea5a8c402b11819a24a06f1e110c371a908d359
  • Improve cross-references between minor version release notes. We have a practice of providing a "bread crumb" trail between the minor versions where the migration section actually tells you to do something. Historically that was just plain text, eg, "see the release notes for 9.2.4"; but if you're using a browser or PDF reader, it's a lot nicer if it's a live hyperlink. So use "<xref>" instead. Any argument against doing this vanished with the recent decommissioning of plain-text release notes. Vik Fearing http://git.postgresql.org/pg/commitdiff/4a6f136c4676bd183b5c1145387eedd837c56ffa
  • Fix length checking for Unicode identifiers containing escapes (U&"..."). We used the length of the input string, not the de-escaped string, as the trigger for NAMEDATALEN truncation. AFAICS this would only result in sometimes printing a phony truncation warning; but it's just luck that there was no worse problem, since we were violating the API spec for truncate_identifier(). Per bug #9204 from Joshua Yanovski. This has been wrong since the Unicode-identifier support was added, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/44c216330201126d12e466442c667a8e091decd3
  • Clean up error cases in psql's COPY TO STDOUT/FROM STDIN code. Adjust handleCopyOut() to stop trying to write data once it's failed one time. For typical cases such as out-of-disk-space or broken-pipe, additional attempts aren't going to do anything but waste time, and in any case clean truncation of the output seems like a better behavior than randomly dropping blocks in the middle. Also remove dubious (and misleadingly documented) attempt to force our way out of COPY_OUT state if libpq didn't do that. If we did have a situation like that, it'd be a bug in libpq and would be better fixed there, IMO. We can hope that commit fa4440f51628d692f077d54b8313aea31af087ea took care of any such problems, anyway. Also fix longstanding bug in handleCopyIn(): PQputCopyEnd() only supports a non-null errormsg parameter in protocol version 3, and will actively fail if one is passed in version 2. This would've made our attempts to get out of COPY_IN state after a failure into infinite loops when talking to pre-7.4 servers. Back-patch the COPY_OUT state change business back to 9.2 where it was introduced, and the other two fixes into all supported branches. http://git.postgresql.org/pg/commitdiff/b8f00a46bc4ae77c09f4564f3b3c675fb9e51974
  • Cosmetic improvements in plpython's make rule for libpython import library. This build technique is remarkably ugly, but that doesn't mean it has to be unreadable too. Be a bit more liberal with the vertical whitespace, and give the .def file a proper dependency, just in case. http://git.postgresql.org/pg/commitdiff/a7983e989d9cafc9cef49becfee054e34b1ed9b4
  • In mingw builds, make our own import library for libperl. Borrow the method already used by plpython. This is pretty ugly, but it might fix the build failure exhibited by buildfarm member narwhal since commit 846e91e0223cf9f2821c3ad4dfffffbb929cb027. Hiroshi Inoue http://git.postgresql.org/pg/commitdiff/02b61dd08f9973eee3058c458afba7b9336230dc
  • Suggest shell here-documents instead of psql -c for multiple commands. The documentation suggested using "echo | psql", but not the often-superior alternative of a here-document. Also, be more direct about suggesting that people avoid -c for multiple commands. Per discussion. http://git.postgresql.org/pg/commitdiff/1ea081bbd73bffed2bd4b0300fe9d99afec465ce
  • In mingw builds, make our own import library for libtcl, too. Per buildfarm results. http://git.postgresql.org/pg/commitdiff/dcbf39774ff3159e17c614a24740ce00fdb14620
  • Update regression testing instructions. This documentation never got the word about the existence of check-world or installcheck-world. Revise to recommend use of those, and document all the subsidiary test suites. Do some minor wordsmithing elsewhere, too. In passing, remove markup related to generation of plain-text regression test instructions, since we don't do that anymore. Back-patch to 9.1 where check-world was added. (installcheck-world exists in 9.0; but since check-world doesn't, this patch would need additional work to cover that branch, and it doesn't seem worth the effort.) http://git.postgresql.org/pg/commitdiff/2128c52f5c476276fcaa2bc49b31f6d445365f95
  • Fix fat-fingered makefile changes for pltcl. I put the OBJS assignments in the wrong order. Per buildfarm. http://git.postgresql.org/pg/commitdiff/638b153f2a23dadbbc5079c30f062a10be42ad11
  • Update time zone data files to tzdata release 2013i. DST law changes in Jordan; historical changes in Cuba. Also, remove the zones Asia/Riyadh87, Asia/Riyadh88, and Asia/Riyadh89. Per the upstream announcement: The files solar87, solar88, and solar89 are no longer distributed. They were a negative experiment -- that is, a demonstration that tz data can represent solar time only with some difficulty and error. Their presence in the distribution caused confusion, as Riyadh civil time was generally not solar time in those years. http://git.postgresql.org/pg/commitdiff/e04641f4b4d1578f00160878f1f3f801f38221cb
  • Ooops, forgot to remove solar87 and friends from src/timezone/Makefile. Per buildfarm. http://git.postgresql.org/pg/commitdiff/1c5143a0b58259df723ed2473ae11d45d08a8b24
  • Use --disable-auto-import linker switch in Mingw builds, too. This is evidently the default on buildfarm member narwhal, but that is a pretty ancient Mingw version, and there is reason to think that more recent versions of GNU ld have this feature turned on by default. Since we are trying to achieve consistency of link behavior across all Windows toolchains, let's just make sure here. http://git.postgresql.org/pg/commitdiff/1c9acd5c86a71b8ab73bc139eb5e0ad292b9a7d4
  • Centralize getopt-related declarations in a new header file pg_getopt.h. We used to have externs for getopt() and its API variables scattered all over the place. Now that we find we're going to need to tweak the variable declarations for Cygwin, it seems like a good idea to have just one place to tweak. In this commit, the variables are declared "#ifndef HAVE_GETOPT_H". That may or may not work everywhere, but we'll soon find out. Andres Freund http://git.postgresql.org/pg/commitdiff/60ff2fdd9970ba29f5267317a5e7354d2658c1e5
  • Fix unportable coding in DetermineSleepTime(). We should not assume that struct timeval.tv_sec is a long, because it ain't necessarily. (POSIX says that it's a time_t, which might well be 64 bits now or in the future; or for that matter might be 32 bits on machines with 64-bit longs.) Per buildfarm member panther. Back-patch to 9.3 where the dubious coding was introduced. http://git.postgresql.org/pg/commitdiff/f0ee42d59b797603d645df8876ae3abf6d016f1e
  • Fix unportable coding in BackgroundWorkerStateChange(). PIDs aren't necessarily ints; our usual practice for printing them is to explicitly cast to long. Per buildfarm member rover_firefly. http://git.postgresql.org/pg/commitdiff/643f75ca9b5b3883395576aaf5246b67270a657b
  • On Windows, expect to find Tcl DLL in bin directory not lib directory. Still another step in the continuing saga of trying to get --disable-auto-import to work. Hiroshi Inoue http://git.postgresql.org/pg/commitdiff/56caaf195e996919088d532832a2a57ca33431b2
  • First-draft release notes for 9.3.3. As usual, the release notes for older branches will be made by cutting these down, but put them up for community review first. http://git.postgresql.org/pg/commitdiff/cefd3e507d7cc402225e5da100d05dcafb90c0bd
  • Improve release notes per comments from Andres Freund. Make a bit more noise about the timeout-interrupt bug. Also, remove the release note entry for commit 423e1211a; that patch fixed a problem introduced post-9.3.2, so there's no need to document it in the release notes. http://git.postgresql.org/pg/commitdiff/8fd994e40cb42b56d6bdef07e1bd7ac79270816b
  • PGDLLIMPORT'ify DateStyle and IntervalStyle. This is needed on Windows to support contrib/postgres_fdw. Although it's been broken since last March, we didn't notice until recently because there were no active buildfarm members that complained about missing PGDLLIMPORT marking. Efforts are underway to improve that situation, in support of which we're delaying fixing some other cases of global variables that should be marked PGDLLIMPORT. However, this case affects 9.3, so we can't wait any longer to fix it. I chose to mark DateOrder as well, though it's not strictly necessary for postgres_fdw. http://git.postgresql.org/pg/commitdiff/a5cf60682e4c61e7cc35c5024abf52ed561775ea
  • Further wordsmithing on 9.3.3 release notes. No substantive changes, but reorder some items and improve some descriptions. http://git.postgresql.org/pg/commitdiff/734ff84b086e098e6106f19c4146357c5eaa9594
  • Revert to using --enable-auto-import in Cygwin builds. Disabling auto-import requires that all libraries we use be careful about declspecs for exported variables; and it seems they aren't. This means that Cygwin will not give us useful info about missing PGDLLIMPORT markers; but it's probably sufficient that MSVC and Mingw builds do. http://git.postgresql.org/pg/commitdiff/8d6e2d4abf77c422714448e5f4270fdb1a84d973
  • Fix unportable coding in tarCreateHeader(). uid_t and gid_t might be wider than int on some platforms. Per buildfarm member brolga. http://git.postgresql.org/pg/commitdiff/a1c802712c369af4085c365cb79c3063b8407ef4
  • PGDLLIMPORT-ify MainLWLockArray, ProcDiePending, proc_exit_inprogress. These are needed in HEAD to make assorted contrib modules build on Windows. Now that all the MSVC and Mingw buildfarm members seem to be on the same page about the need for them, we can have some confidence that future problems of this ilk will be detected promptly; there seems nothing more to be learned by delaying this fix further. I chose to mark QueryCancelPending as well, since it's easy to imagine code that wants to touch ProcDiePending also caring about QueryCancelPending. http://git.postgresql.org/pg/commitdiff/fa1f0d785921b34a98562a806aed2c3d34aaf7be
  • Release notes for 9.3.3, 9.2.7, 9.1.12, 9.0.16, 8.4.20. http://git.postgresql.org/pg/commitdiff/0983315b1d37cc17b2174dad87449d8402e357ee

Peter Eisentraut a poussé :

Bruce Momjian a poussé :

Magnus Hagander a poussé :

Alvaro Herrera a poussé :

  • Separate multixact freezing parameters from xid's. Previously we were piggybacking on transaction ID parameters to freeze multixacts; but since there isn't necessarily any relationship between rates of Xid and multixact consumption, this turns out not to be a good idea. Therefore, we now have multixact-specific freezing parameters: vacuum_multixact_freeze_min_age: when to remove multis as we come across them in vacuum (default to 5 million, i.e. early in comparison to Xid's default of 50 million) vacuum_multixact_freeze_table_age: when to force whole-table scans instead of scanning only the pages marked as not all visible in visibility map (default to 150 million, same as for Xids). Whichever of both which reaches the 150 million mark earlier will cause a whole-table scan. autovacuum_multixact_freeze_max_age: when for cause emergency, uninterruptible whole-table scans (default to 400 million, double as that for Xids). This means there shouldn't be more frequent emergency vacuuming than previously, unless multixacts are being used very rapidly. Backpatch to 9.3 where multixacts were made to persist enough to require freezing. To avoid an ABI break in 9.3, VacuumStmt has a couple of fields in an unnatural place, and StdRdOptions is split in two so that the newly added fields can go at the end. Patch by me, reviewed by Robert Haas, with additional input from Andres Freund and Tom Lane. http://git.postgresql.org/pg/commitdiff/801c2dc72cb3c68a7c430bb244675b7a68fd541a

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Pavel Stehule sent in another revision of a patch to make it possible to have multiple PL/pgsql plugins.
  • Alexander Korotkov and Marti Raudsepp traded patches for partial sort.
  • Hiroshi Inoue and Andres Freund traded patches to fix an issue with PGDLLIMPORT on Windows.
  • David Fetter sent in another revision of a patch to add UPDATE ... RETURNING BEFORE/AFTER.
  • Mitsumasa KONDO sent in another revision of a patch to add min and max execute statement times to pg_stat_statement.
  • Mark Kirkwood and Haribabu Kommi traded patches to fix an infelicity in autovacuum_cost_delay.
  • Bruce Momjian sent in a patch to display disabled system triggers separately from user ones in psql.
  • Christian Kruse sent in another revision of a patch to show xid and xmin in pg_stat_activity and pg_stat_replication.
  • MauMau sent in another revision of a patch to fix an issue where the WALs get much larger than needed during point-in-time recovery.
  • Gregory Stark and Tom Lane traded patches intended to fix an issue where the standby's data can be much larger than the primary's, even though theoretically they should be exact copies.
  • Vik Fearing sent in a patch to fix the documentation for nextVictimBuffer.
  • Fabrízio de Royes Mello sent in another revision of a patch to store custom relopts.
  • Tom Lane sent in a patch to fix an issue with issue with GIN inserts under very high load.
  • Mitsumasa KONDO and Fabien COELHO traded patches to add a Gaussian distribution option to pgbench.
  • Bruce Momjian sent in two more revisions of a patch to remove references to long-unsupported versions of PostgreSQL from the documentation.
  • Amit Kapila sent in three more revisions of a patch to improve performance by reducing WAL for update operations.
  • Andres Freund sent in two more patch sets for logical changesets.
  • David Beck sent in a patch to add a hook after raw parsing, but before analyze.
  • Etsuro Fujita sent in another revision of a patch to implement INHERIT support for foreign tables.
  • Andres Freund sent in a patch to fix an omission in abfd192b where one of the error cases wasn't changed when WalSndLoop was changed to be able to return.
  • Andres Freund sent in a patch to separate two tests in WalSndLoop(), as they don't have the dependency the current statement would imply.
  • Bruce Momjian sent in two revisions of a patch to fix a memory leak in psql.
  • Andres Freund sent in patches to fix a memory ordering issue in LWLockRelease, WakeupWaiters, and WALInsertSlotRelease by using volatiles to avoid reordering.
  • Peter Eisentraut sent in a patch to hack together a fix for uuid-ossp on OSX.
  • Peter Eisentraut sent in another revision of a patch to allow for relocating tablespaces in pg_basebackup.
  • Bruce Momjian sent in another revision of a patch to fix an issue where abnormal heap fetches were occurring after VACUUM FULL.
  • Sergey Muraviov sent in another revision of a patch to make displaying wide tables in psql look better.
  • David Fetter sent in another revision of a patch to enable CREATE FOREIGN TABLE (... LIKE ...).

par N Bougain le samedi 1 mars 2014 à 19h42

Nouvelles hebdomadaires de PostgreSQL - 9 février 2014

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en février

PostgreSQL Local

  • La 7ème conférence annuelle "Prague PostgreSQL Developers Day" (P2D2), organisée par le CSPUG (PUG tchèque et slovaque), aura lieu le 6 février 2014 à la Faculté des Sciences Mathématiques & Physiques de l'Université Charles (Malostranske namesti 25, Prague). Infos en langue tchèque ci-après : http://www.p2d2.cz/
  • Le PGDay Nordique 2014 aura lieu à Stockholm (Suède) à l'hôtel Hilton le 20 mars 2014 : http://2014.nordicpgday.org/
  • La PGConf NYC 2014 aura lieu les 3 & 4 avril 2014 à New-York (New-York, USA) : http://nyc.pgconf.us/2014/
  • Le sommet Open Data aura lieu le 11 avril 2014 à Denver (Colorado, États-Unis) : http://www.opendatasummit.com
  • La PGCon 2014, la conférence mondiale des développeurs PostgreSQL, se tiendra à Ottawa (Ontario, Canada) du 20 au 24 mai 2014 : http://www.pgcon.org/2014/

PostgreSQL dans les média

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

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

(lien vers l'article original)

Correctifs appliqués

Fujii Masao a poussé :

Andrew Dunstan a poussé :

Robert Haas a poussé :

Tom Lane a poussé :

  • Fix *-qualification of named parameters in SQL-language functions. Given a composite-type parameter named x, "$1.*" worked fine, but "x.*" not so much. This has been broken since named parameter references were added in commit 9bff0780cf5be2193a5bad0d3df2dbe143085264, so patch back to 9.2. Per bug #9085 from Hardy Falk. http://git.postgresql.org/pg/commitdiff/0def2573c5f0ff127d0c7dc12ec7da56ae6fb7fe
  • Fix lexing of U& sequences just before EOF. Commit a5ff502fceadc7c203b0d7a11b45c73f1b421f69 was a brick shy of a load in the backend lexer too, not just psql. Per further testing of bug #9068. In passing, improve related comments. http://git.postgresql.org/pg/commitdiff/0c2338abbb17b7b319f36a73d8db77735346804f
  • Improve connection-failure error handling in contrib/postgres_fdw. postgres_fdw tended to say "unknown error" if it tried to execute a command on an already-dead connection, because some paths in libpq just return a null PGresult for such cases. Out-of-memory might result in that, too. To fix, pass the PGconn to pgfdw_report_error, and look at its PQerrorMessage() string if we can't get anything out of the PGresult. Also, fix the transaction-exit logic to reliably drop a dead connection. It was attempting to do that already, but it assumed that only connection cache entries with xact_depth > 0 needed to be examined. The folly in that is that if we fail while issuing START TRANSACTION, we'll not have bumped xact_depth. (At least for the case I was testing, this fix masks the other problem; but it still seems like a good idea to have the PGconn fallback logic.) Per investigation of bug #9087 from Craig Lucas. Backpatch to 9.3 where this code was introduced. http://git.postgresql.org/pg/commitdiff/00d4f2af8bd6a1b9db2f676cc76b64d98ace99fb
  • Remove unnecessary relcache flushes after changing btree metapages. These flushes were added in my commit d2896a9ed, which added the btree logic that keeps a cached copy of the index metapage data in index relcache entries. The idea was to ensure that other backends would promptly update their cached copies after a change. However, this is not really necessary, since _bt_getroot() has adequate defenses against believing a stale root page link, and _bt_getrootheight() doesn't have to be 100% right. Moreover, if it were necessary, a relcache flush would be an unreliable way to do it, since the sinval mechanism believes that relcache flush requests represent transactional updates, and therefore discards them on transaction rollback. Therefore, we might as well drop these flush requests and save the time to rebuild the whole relcache entry after a metapage change. If we ever try to support in-place truncation of btree indexes, it might be necessary to revisit this issue so that _bt_getroot() can't get caught by trying to follow a metapage link to a page that no longer exists. A possible solution to that is to make use of an smgr, rather than relcache, inval request to force other backends to discard their cached metapages. But for the moment this is not worth pursuing. http://git.postgresql.org/pg/commitdiff/ac8bc3b6e4a28cf7cd33fe11866d72f6deb2a38f
  • Assert(IsTransactionState()) in RelationIdGetRelation(). Commit 42c80c696e9c8323841180029cc62741c21bd356 added an Assert(IsTransactionState()) in SearchCatCache(), to catch any code that thought it could do a catcache lookup outside transactions. Extend the same idea to relcache lookups. http://git.postgresql.org/pg/commitdiff/ddfc9cb054abed4d08cc2709c9b2197dab96f449
  • In RelationClearRelation, postpone cache reload if !IsTransactionState(). We may process relcache flush requests during transaction startup or shutdown. In general it's not terribly safe to do catalog access at those times, so the code's habit of trying to immediately revalidate unflushable relcache entries is risky. Although there are no field trouble reports that are positively traceable to this, we have been able to demonstrate failure of the assertions recently added in RelationIdGetRelation() and SearchCatCache(). On the other hand, it seems safe to just postpone revalidation of the cache entry until we're inside a valid transaction. The one case where this is questionable is where we're exiting a subtransaction and the outer transaction is holding the relcache entry open --- but if we made any significant changes to the rel inside such a subtransaction, we've got problems anyway. There are mechanisms in place to prevent that (to wit, locks for cross-session cases and CheckTableNotInUse() for intra-session cases), so let's trust to those mechanisms to keep us out of trouble. http://git.postgresql.org/pg/commitdiff/8de3e410faa06ab20ec1aa6d0abb0a2c040261ba

Peter Eisentraut a poussé :

Heikki Linnakangas a poussé :

  • Fix thinko in comment. Amit Langote http://git.postgresql.org/pg/commitdiff/e001030c2711c0fb65cf72813f16a8eb26483c16
  • Speed up "rare & frequent" type GIN queries. If you have a GIN query like "rare & frequent", we currently fetch all the items that match either rare or frequent, call the consistent function for each item, and let the consistent function filter out items that only match one of the terms. However, if we can deduce that "rare" must be present for the overall qual to be true, we can scan all the rare items, and for each rare item, skip over to the next frequent item with the same or greater TID. That greatly speeds up "rare & frequent" type queries. To implement that, introduce the concept of a tri-state consistent function, where the 3rd value is MAYBE, indicating that we don't know if that term is present. Operator classes only provide a boolean consistent function, so we simulate the tri-state consistent function by calling the boolean function several times, with the MAYBE arguments set to all combinations of TRUE and FALSE. Testing all combinations is only feasible for a small number of MAYBE arguments, but it is envisioned that we'll provide a way for operator classes to provide a native tri-state consistent function, which can be much more efficient. But that is not included in this patch. We were already using that trick to for lossy pages, calling the consistent function with the lossy entry set to TRUE and FALSE. Now that we have the tri-state consistent function, use it for lossy pages too. Alexander Korotkov, with fair amount of refactoring by me. http://git.postgresql.org/pg/commitdiff/dbc649fd773e7e16458bfbec2611bf15f4355bc4
  • Initialize the entryRes array between each call to triConsistent. The shimTriConstistentFn, which calls the opclass's consistent function with all combinations of TRUE/FALSE for any MAYBE argument, modifies the entryRes array passed by the caller. Change startScanKey to re-initialize it between each call to accommodate that. It's actually a bad habit by shimTriConsistentFn to modify its argument. But the only caller that doesn't already re-initialize the entryRes array was startScanKey, and it's easy for startScanKey to do so. Add a comment to shimTriConsistentFn about that. Note: this does not give a free pass to opclass-provided consistent functions to modify the entryRes argument; shimTriConsistent assumes that they don't, even though it does it itself. While at it, refactor startScanKey to allocate the requiredEntries and additionalEntries after it knows exactly how large they need to be. Saves a little bit of memory, and looks nicer anyway. Per complaint by Tom Lane, buildfarm and the pg_trgm regression test. http://git.postgresql.org/pg/commitdiff/6aa2bdf6a01ce099e315cb313396ca4b8415321b

Magnus Hagander a poussé :

Stephen Frost a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Kyotaro HORIGUCHI sent in another revision of a patch make UNION ALL on partitioned tables use indices.
  • Rajeev Rastogi sent in another revision of a patch to fix a bug where the PostgreSQL Service on Windows does not start if data directory given is relative path.
  • Antonin Houska sent in another revision of a patch to allow throttling backups.
  • Gurjeet Singh sent in a patch to create a pg_hibernate, which enables hibernation of PostgreSQL shared buffers.
  • Bruce Momjian sent in a patch to make pg_upgrade allocate and use memory more efficiently.
  • Christian Kruse sent in two more revisions of a patch to show the process IDs of processes holding a lock, and show relation and tuple info for a lock to acquire.
  • Kyotaro HORIGUCHI sent in a patch to fix a bug in handling of non-supported IPv6.
  • Michael Paquier sent in four revisions of a patch to add a XLogRecPtr/LSN data type.
  • Alvaro Herrera sent in two more revisions of a patch to add CREATE support to event triggers.
  • Christian Kruse sent in two more revisions of a patch to show xid and xmin in pg_stat_activity and pg_stat_replication.
  • Alexander Korotkov and Heikki Linnakangas traded patches to add a "fast scan" option for GIN.
  • MauMau sent in another revision of a patch to fix a bug where PostgreSQL fails to start on Windows if it crashes after tablespace creation.
  • Ronan Dunklau sent in another revision of a patch to make it possible to create triggers on foreign tables.
  • Heikki Linnakangas sent in another revision of a patch to fix an issue with failure while inserting parent tuple to B-tree.
  • Amit Kapila and Heikki Linnakangas traded patches to improve performance by reducing WAL for update operations.
  • Jeremy Harris sent in three revisions of a patch to improve performance in the transition to external sort.
  • SAWADA Masahiko sent in a patch to add 'dml' as a possible value for log_statement.
  • Emre Hasegeli sent in another revision of a patch to add GiST support for inet datatypes.
  • Andrew Dunstan sent in another revision of a patch to implement jsonb and nested hstore. Erik Rijkers sent in patches to update the documentation for same.
  • Laurenz Albe sent in a patch to the psql documentation to bring it into line with recent changes in how encoding is handled.
  • Etsuro Fujita sent in another revision of a patch to add INHERIT support for foreign tables.
  • Kaigai Kouhei sent in another revision of a patch to add cache-only scans.
  • Amit Kapila sent in another revision of a patch to allow retaining dynamic shared memory segments for the postmaster lifetime.
  • Andres Freund sent in another revision of a patch to support logical changeset extraction.
  • Andres Freund sent in another revision of a patch to allow escaping of option values for options passed at connection start.
  • Peter Eisentraut sent in another revision of a patch to add tests for client programs.
  • Tom Lane sent in a patch to remove some legacy silliness from the document build.
  • Craig Ringer, Tom Lane, and Marco Atzeri traded patches to fix the cygwin build.
  • Hardy Falk sent in a patch to speed up duplicate elimination in NOTIFY.
  • Magnus Hagander sent in a patch to ensure that the streaming xlog process of pg_basebackup dies when when the foreground process does.
  • Pavel Stehule sent in another revision of a patch to enable PL/pgsql to have multiple plugins.

par N Bougain le samedi 1 mars 2014 à 19h41

Nouvelles hebdomadaires de PostgreSQL - 2 février 2014

Le programme de la PGConf NYC 2014 a été publié et les inscriptions spéciales "lève-tôt" sont ouvertes : http://nyc.pgconf.us/2014/schedule/ http://pgconfnyc2014.eventbrite.com/?aff=pgann

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en février

PostgreSQL Local

  • La 7ème conférence annuelle "Prague PostgreSQL Developers Day" (P2D2), organisée par le CSPUG (PUG tchèque et slovaque), aura lieu le 6 février 2014 à la Faculté des Sciences Mathématiques & Physiques de l'Université Charles (Malostranske namesti 25, Prague). Infos en langue tchèque ci-après : http://www.p2d2.cz/
  • Le PGDay Nordique 2014 aura lieu à Stockholm (Suède) à l'hôtel Hilton le 20 mars 2014. L'appel à conférenciers est ouvert jusqu'au 2 février 2014 : http://2014.nordicpgday.org/
  • La PGConf NYC 2014 aura lieu les 3 & 4 avril 2014 à New-York (New-York, USA) : http://nyc.pgconf.us/2014/
  • Le sommet Open Data aura lieu le 11 avril 2014 à Denver (Colorado, États-Unis) : http://www.opendatasummit.com
  • La PGCon 2014, la conférence mondiale des développeurs PostgreSQL, se tiendra à Ottawa (Ontario, Canada) du 20 au 24 mai 2014 : http://www.pgcon.org/2014/

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

  • Relax the requirement that all lwlocks be stored in a single array. This makes it possible to store lwlocks as part of some other data structure in the main shared memory segment, or in a dynamic shared memory segment. There is still a main LWLock array and this patch does not move anything out of it, but it provides necessary infrastructure for doing that in the future. This change is likely to increase the size of LWLockPadded on some platforms, especially 32-bit platforms where it was previously only 16 bytes. Patch by me. Review by Andres Freund and KaiGai Kohei. http://git.postgresql.org/pg/commitdiff/ea9df812d8502fff74e7bc37d61bdc7d66d77a7f
  • Fix compiler warning in EXEC_BACKEND builds. Per a report by Rajeev Rastogi. http://git.postgresql.org/pg/commitdiff/b7643b19f0fdbfb1636db52e39db4be6f0174ce0
  • Add convenience functions pg_sleep_for and pg_sleep_until. Vik Fearing, reviewed by Pavel Stehule and myself http://git.postgresql.org/pg/commitdiff/760c770ff66b5c2f77f2e17750a2e263a74d82b3
  • Clear MyProc and MyProcSignalState before they become invalid. Evidence from buildfarm member crake suggests that the new test_shm_mq module is routinely crashing the server due to the arrival of a SIGUSR1 after the shared memory segment has been unmapped. Although processes using the new dynamic background worker facilities are more likely to receive a SIGUSR1 around this time, the problem is also possible on older branches, so I'm back-patching the parts of this change that apply to older branches as far as they apply. It's already generally the case that code checks whether these pointers are NULL before deferencing them, so the important thing is mostly to make sure that they do get set to NULL before they become invalid. But in master, there's one case in procsignal_sigusr1_handler that lacks a NULL guard, so add that. Patch by me; review by Tom Lane. http://git.postgresql.org/pg/commitdiff/d1981719adbcc05fa15f540e8fc4327907991fc6
  • Introduce replication slots. Replication slots are a crash-safe data structure which can be created on either a master or a standby to prevent premature removal of write-ahead log segments needed by a standby, as well as (with hot_standby_feedback=on) pruning of tuples whose removal would cause replication conflicts. Slots have some advantages over existing techniques, as explained in the documentation. In a few places, we refer to the type of replication slots introduced by this patch as "physical" slots, because forthcoming patches for logical decoding will also have slots, but with somewhat different properties. Andres Freund and Robert Haas http://git.postgresql.org/pg/commitdiff/858ec11858a914d4c380971985709b6d6b7dd6fc
  • Include planning time in EXPLAIN ANALYZE output. This doesn't work for prepared queries, but it's not too easy to get the information in that case and there's some debate as to exactly what the right thing to measure is, so just do this for now. Andreas Karlsson, with slight doc changes by me. http://git.postgresql.org/pg/commitdiff/9347baa5bbc70368f2f01438bbb8116863dac1ec

Tom Lane a poussé :

  • Log a detail message for auth failures due to missing or expired password. It's worth distinguishing these cases from run-of-the-mill wrong-password problems, since users have been known to waste lots of time pursuing the wrong theory about what's failing. Now, our longstanding policy about how to report authentication failures is that we don't really want to tell the *client* such things, since that might be giving information to a bad guy. But there's nothing wrong with reporting the details to the postmaster log, and indeed the comments in this area of the code contemplate that interesting details should be so reported. We just weren't handling these particular interesting cases usefully. To fix, add infrastructure allowing subroutines of ClientAuthentication() to return a string to be added to the errdetail_log field of the main authentication-failed error report. We might later want to use this to report other subcases of authentication failure the same way, but for the moment I just dealt with password cases. Per discussion of a patch from Josh Drake, though this is not what he proposed. http://git.postgresql.org/pg/commitdiff/64e43c59b817a78ddf70f2fd62de31a4add5d988
  • Keep pg_stat_statements' query texts in a file, not in shared memory. This change allows us to eliminate the previous limit on stored query length, and it makes the shared-memory hash table very much smaller, allowing more statements to be tracked. (The default value of pg_stat_statements.max is therefore increased from 1000 to 5000.) In typical scenarios, the hash table can be large enough to hold all the statements commonly issued by an application, so that there is little "churn" in the set of tracked statements, and thus little need to do I/O to the file. To further reduce the need for I/O to the query-texts file, add a way to retrieve all the columns of the pg_stat_statements view except for the query text column. This is probably not of much interest for human use but it could be exploited by programs, which will prefer using the queryid anyway. Ordinarily, we'd need to bump the extension version number for the latter change. But since we already advanced pg_stat_statements' version number from 1.1 to 1.2 in the 9.4 development cycle, it seems all right to just redefine what 1.2 means. Peter Geoghegan, reviewed by Pavel Stehule http://git.postgresql.org/pg/commitdiff/f0d6f20278b7c5c412ce40a9b86c6b31dc2fbfdd
  • Update comment. generate_normalized_query() no longer needs to truncate text, but this one comment didn't get the memo. Per Peter Geoghegan. http://git.postgresql.org/pg/commitdiff/98d62c28fd774ad8d123b66131dcdaa0b9c9d6d4
  • Fix unsafe references to errno within error messaging logic. Various places were supposing that errno could be expected to hold still within an ereport() nest or similar contexts. This isn't true necessarily, though in some cases it accidentally failed to fail depending on how the compiler chanced to order the subexpressions. This class of thinko explains recent reports of odd failures on clang-built versions, typically missing or inappropriate HINT fields in messages. Problem identified by Christian Kruse, who also submitted the patch this commit is based on. (I fixed a few issues in his patch and found a couple of additional places with the same disease.) Back-patch as appropriate to all supported branches. http://git.postgresql.org/pg/commitdiff/571addd729a400cece396d79696adcc63387e43b
  • Fix bogus handling of "postponed" lateral quals. When pulling a "postponed" qual from a LATERAL subquery up into the quals of an outer join, we must make sure that the postponed qual is included in those seen by make_outerjoininfo(). Otherwise we might compute a too-small min_lefthand or min_righthand for the outer join, leading to "JOIN qualification cannot refer to other relations" failures from distribute_qual_to_rels. Subtler errors in the created plan seem possible, too, if the extra qual would only affect join ordering constraints. Per bug #9041 from David Leverton. Back-patch to 9.3. http://git.postgresql.org/pg/commitdiff/043f6ff05d0a5140dfe25faf277ec9f1d7169005
  • Fix potential coredump on bad locale value in pg_upgrade. Thinko in error report (and a typo in the message text, too). We're failing anyway, but it would be good to print something useful first. Noted while reviewing a patch to make pg_upgrade's locale code laxer. http://git.postgresql.org/pg/commitdiff/41e364ec67ec3a009574db9d20d1b85a654f95ae
  • Be forgiving of variant spellings of locale names in pg_upgrade. Even though the server tries to canonicalize stored locale names, the platform often doesn't cooperate, so it's entirely possible that one DB thinks its locale is, say, "en_US.UTF-8" while the other has "en_US.utf8". Rather than failing, we should try to allow this where it's clearly OK. There is already pretty robust encoding lookup in encnames.c, so make use of that to compare the encoding parts of the names. The locale identifier parts are just compared case-insensitively, which we were already doing. The major problem known to exist in the field is variant encoding-name spellings, so hopefully this will be Good Enough. If not, we can try being even laxer. Pavel Raiskup, reviewed by Rushabh Lathia http://git.postgresql.org/pg/commitdiff/58274728fb8e087049df67c0eee903d9743fdeda
  • Allow unrecognized encoding names in locales, as long as they're the same. The buildfarm says commit 58274728fb8e087049df67c0eee903d9743fdeda doesn't work so well on Windows. This is because the encoding part of Windows locale names can be just a code page number, eg "1252", which we don't consider to be a valid encoding name. Add a check to accept encoding parts that are case-insensitively string equal; this at least ensures that the new code doesn't reject any cases that the old code allowed. http://git.postgresql.org/pg/commitdiff/cd3e0071b8c9e082f5fe903a019d4e474be98e57
  • Add some examples to the postgres_fdw documentation. Michael Paquier http://git.postgresql.org/pg/commitdiff/e93ca1618b92ff4ca3e1ed3bff89179d3e2abd9e
  • Disallow use of SSL v3 protocol in the server as well as in libpq. Commit 820f08cabdcbb8998050c3d4873e9619d6d8cba4 claimed to make the server and libpq handle SSL protocol versions identically, but actually the server was still accepting SSL v3 protocol while libpq wasn't. Per discussion, SSL v3 is obsolete, and there's no good reason to continue to accept it. So make the code really equivalent on both sides. The behavior now is that we use the highest mutually-supported TLS protocol version. Marko Kreen, some comment-smithing by me http://git.postgresql.org/pg/commitdiff/326e1d73c476a0b5061ef00134bdf57aed70d5e7
  • Fix some more bugs in signal handlers and process shutdown logic. WalSndKill was doing things exactly backwards: it should first clear MyWalSnd (to stop signal handlers from touching MyWalSnd->latch), then disown the latch, and only then mark the WalSnd struct unused by clearing its pid field. Also, WalRcvSigUsr1Handler and worker_spi_sighup failed to preserve errno, which is surely a requirement for any signal handler. Per discussion of recent buildfarm failures. Back-patch as far as the relevant code exists. http://git.postgresql.org/pg/commitdiff/214c7a4f0b1784ce855512c2961b09c9f51dafd8
  • Fix some wide-character bugs in the text-search parser. In p_isdigit and other character class test functions generated by the p_iswhat macro, the code path for non-C locales with multibyte encodings contained a bogus pointer cast that would accidentally fail to malfunction if types wchar_t and wint_t have the same width. Apparently that is true on most platforms, but not on recent Cygwin releases. Remove the cast, as it seems completely unnecessary (I think it arose from a false analogy to the need to cast to unsigned char when dealing with the <ctype.h> functions). Per bug #8970 from Marco Atzeri. In the same functions, the code path for C locale with a multibyte encoding simply ANDed each wide character with 0xFF before passing it to the corresponding <ctype.h> function. This could result in false positive answers for some non-ASCII characters, so use a range test instead. Noted by me while investigating Marco's complaint. Also, remove some useless though not actually buggy maskings and casts in the hand-coded p_isalnum and p_isalpha functions, which evidently got tested a bit more carefully than the macro-generated functions. http://git.postgresql.org/pg/commitdiff/082c0dfa140b5799bc7eb574d68610dcfaa619ba
  • Clean up some sloppy coding in repl_gram.y. Remove unused copy-and-pasted macro definitions, and improve formatting of recently-added productions. I got interested in this because buildfarm member protosciurus has been crashing in "bison repl_gram.y" since commit 858ec11. It's a long shot that this will fix that, though maybe the missing trailing semicolon has something to do with it? In any case, there's no need to approve of dead code, nor of code whose formatting isn't even self-consistent let alone consistent with what's around it. http://git.postgresql.org/pg/commitdiff/46825d4978b63a0ae9637efbf6298220c833fa8d
  • Switch in psql_scan() must cover all lexer states (except backslash cases). Oversight in commit f7559c0101afa33bfb4e104036ca46adac900111, which changed UESCAPE lexing in psql. Per bug #9068 from Manuel Gómez. http://git.postgresql.org/pg/commitdiff/47aaebaac95c9000549d1a6de809e15b729231f5

Stephen Frost a poussé :

  • Revert dup2() checking in syslogger.c Per the expanded comment- As we're just trying to reset these to go to DEVNULL, there's not much point in checking for failure from the close/dup2 calls here, if they fail then presumably the file descriptors are closed and any writes will go into the bitbucket anyway. Pointed out by Tom Lane. http://git.postgresql.org/pg/commitdiff/aef61bf433a9e9b6e2d98b0fdcce8562c3ad526f

Bruce Momjian a poussé :

Andrew Dunstan a poussé :

Fujii Masao a poussé :

Heikki Linnakangas a poussé :

  • Fix docs build. Broken by the huge_tlb_pages patch. Vik Fearing. http://git.postgresql.org/pg/commitdiff/991659dcd768163c77924e67a75088e91c713189
  • Allow skipping some items in a multi-key GIN search. In a multi-key search, ie. something like "col @> 'foo' AND col @> 'bar'", as soon as we find the next item that matches the first criteria, we don't need to check the second criteria for TIDs smaller the first match. That saves a lot of effort, especially if one of the terms is rare, while the second occurs very frequently. Based on ideas from Alexander Korotkov's fast scan patch. http://git.postgresql.org/pg/commitdiff/e20c70cb0fa74d5bffa080e21a99b44bf0768667
  • Further optimize multi-key GIN searches. If we're skipping past a certain TID, avoid decoding posting list segments that only contain smaller TIDs. Extracted from Alexander Korotkov's fast scan patch, heavily modified. http://git.postgresql.org/pg/commitdiff/25b1dafab63f465a65c63b26834dc18857f0fa0c
  • Allow using huge TLB pages on Linux (MAP_HUGETLB) This patch adds an option, huge_tlb_pages, which allows requesting the shared memory segment to be allocated using huge pages, by using the MAP_HUGETLB flag in mmap(). This can improve performance. The default is 'try', which means that we will attempt using huge pages, and fall back to non-huge pages if it doesn't work. Currently, only Linux has MAP_HUGETLB. On other platforms, the default 'try' behaves the same as 'off'. In the passing, don't try to round the mmap() size to a multiple of pagesize. mmap() doesn't require that, and there's no particular reason for PostgreSQL to do that either. When using MAP_HUGETLB, however, round the request size up to nearest 2MB boundary. This is to work around a bug in some Linux kernel versions, but also to avoid wasting memory, because the kernel will round the size up anyway. Many people were involved in writing this patch, including Christian Kruse, Richard Poole, Abhijit Menon-Sen, reviewed by Peter Geoghegan, Andres Freund and me. http://git.postgresql.org/pg/commitdiff/1a3458b6d8d202715a83c88474a1b63726d0929e
  • Further optimize GIN multi-key searches. When skipping over some items in a posting tree, re-find the new location by descending the tree from root, rather than walking the right links. This can save a lot of I/O. Heavily modified from Alexander Korotkov's fast scan patch. http://git.postgresql.org/pg/commitdiff/626a120656a75bf4fe64b1d0d83c23cb38d3771a
  • Fix thinko in huge_tlb_pages patch. We calculated the rounded-up size for the allocation, but then failed to use the rounded-up value in the mmap() call. Oops. Also, initialize allocsize, to silence warnings seen with some compilers, as pointed out by Jeff Janes. http://git.postgresql.org/pg/commitdiff/699b1f40da3139def660235fa8a782ec8dd8f575

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Sawada Masahiko and David Fetter traded patches to expand "iff" into "if and only if" in documentation and comments.
  • Alexander Korotkov sent in another revision of a patch to implement partial sorting.
  • Heikki Linnakangas sent in two more revisions of a patch to fix a race condition in B-Tree page deletion.
  • Stephen Frost sent in a patch not to tweak NTUP_PER_BUCKET, but instead to do a different calculation which doesn't include it.
  • Simon Riggs sent in another revision of a patch to decrease lock levels for certain operations.
  • Andreas Karlsson sent in three more revisions of a patch to allow including planning time in EXPLAIN [ANALYZE].
  • Dimitri Fontaine sent in another revision of a patch to implement an extension control path.
  • Andres Freund and Robert Haas traded patches aimed at logical changeset extraction.
  • Amit Kapila and Heikki Linnakangas traded patches to improve performance by reducing WAL for updates.
  • Mitsumasa KONDO sent in another revision of a patch to add min and max execute statement times to pg_stat_statement.
  • Amit Kapila sent in another revision of a patch to allow retaining dynamic shared memory segments for the postmaster lifetime.
  • Robert Haas sent in a patch to make the world safe for procsignal_sigusr1_handler.
  • Andrew Dunstan sent in four more revisions of a patch to implement jsonb and nested hstore.
  • Erik Rijkers sent in a patch to document jsonb and nested hstore.
  • Peter Geoghegan sent in a patch to correct a comment to reflect the fact storing pg_stat_statements query texts externally removes certain limits.
  • Mitsumasa KONDO sent in another revision of a patch to drop duplicate buffers in the OS.
  • Björn Harrtell sent in a patch to implement a regexp_matches variant returning an array of matching positions.
  • Ian Lawrence Barwick sent in two more revisions of a patch to add a FORCE_NULL option for copy COPY in CSV mode.
  • Kyotaro HORIGUCHI sent in another revision of a patch to allow UNION ALL queries on partitioned tables to use indexes.
  • Yugo Nagata sent in two more revisions of a patch to implement to_regclass and like kind functions.
  • Salah Jubeh sent in two more revisions of a patch to add a --force option to dropdb.
  • Christian Kruse sent in another revision of a patch to show process IDs of processes holding a lock and show the relation and tuple info of a lock to acquire.
  • Steeve Lennmark sent in another revision of a patch to allow relocating tablespaces in pg_basebackup.
  • Pavel Stehule sent in another revision of a patch to ensure that COPY ... FROM STDIN shows the count tag.
  • Michael Paquier sent in a patch to remove xloginsert_slots.
  • Craig Ringer sent in another set of patches aimed toward fixing an infinite recursion in the upcoming updateable security barrier views.
  • Christian Kruse sent in a patch to fix an assumption about the order of operations that is true in GCC but not in Clang.
  • Christian Kruse sent in a patch to explain how to compile the docs in Gentoo Linux.
  • Craig Ringer and Dean Rasheed traded patches to implement updateable security barrier views.
  • Michael Paquier sent in a patch to rename PgStat_GlobalStats to PgStat_BgWriterStats.
  • Ronan Dunklau sent in two more revisions of a patch to allow triggers on foreign tables.
  • Shigeru HANADA sent in another revision of a patch to allow foreign tables to INHERIT from local ones.
  • Andreas Karlsson sent in another revision of a patch to add GiST index support for inet datatypes.
  • Etsuro Fujita sent in a patch atop the patch which allows foreign tables to inherit from local ones. This patch enables ANALYZE where appropriate.
  • Amit Kapila sent in another revision of a patch to fix an issue in ALTER SYSTEM with PGC_BACKEND parameters.
  • Michael Paquier and Robert Haas traded patches to document the fact that the regression tests need to operate on a database named "regression."
  • Pavel Stehule and Jeevan Chalke traded patches to add an --if-exists option to pg_dump, which would inject IF EXISTS into its output at appropriate places.
  • MauMau sent in another revision of a patch to fix a bug where pg_ctl always uses the same event source on Windows.
  • MauMau sent in another revision of a patch to fix a bug where pg_ctl fails with config-only directory.
  • Bruce Momjian sent in another revision of a patch to fix an error where the insert result does not match record count due to an overflow.
  • Fujii Masao sent in a patch to change basebackup.c so that it skips all files in both pg_stat_tmp and stats_temp_directory.
  • Christian Kruse sent in another revision of a patch to show xid and xmin in pg_stat_activity and pg_stat_replication.
  • Julien Rouhaud sent in a patch to change pg_sleep(interval) to use clock_timestamp() (time on the clock) instead of now() (time at which the transaction started).
  • Andres Freund sent in a patch to fix an issue where misaligned BufferDescriptors was causing major performance problems on AMD.
  • Andres Freund sent in a patch to fix an issue where startup slows because of LWLockAssign() spinlock.
  • Tom Lane sent in a patch to fix an issue where CacheInvalidateRelcache in btree was not doing the right thing.
  • Tom Lane sent in a patch to avoid unsafe cache reloads.
  • David Fetter sent in another revision of a patch to implement UPDATE ... RETURNING BEFORE/AFTER.
  • Craig Ringer sent in a patch to fix a memory leak in json_array_elements.
  • Marko (johto) Tiikkaja sent in another revision of a patch to implement plpgsql.warn_shadow.
  • Rajeev Rastogi sent in another revision of a patch to fix an issue where the PostgreSQL Service on Windows does not start if data directory given is relative path.

par N Bougain le samedi 1 mars 2014 à 19h40

Nouvelles hebdomadaires de PostgreSQL - 26 janvier 2014

Offres d'emplois autour de PostgreSQL en janvier

PostgreSQL Local

  • FOSDEM PGDay, une conférence d'une journée, tenue avant le FOSDEM à Bruxelles, aura lieu le 31 janvier 2014. Détails : http://fosdem2014.pgconf.eu/ http://fosdem2014.pgconf.eu/registration/
  • La 7ème conférence annuelle "Prague PostgreSQL Developers Day" (P2D2), organisée par le CSPUG (PUG tchèque et slovaque), aura lieu le 6 février 2014 à la Faculté des Sciences Mathématiques & Physiques de l'Université Charles (Malostranske namesti 25, Prague). Infos en langue tchèque ci-après : http://www.p2d2.cz/
  • Le PGDay Nordique 2014 aura lieu à Stockholm (Suède) à l'hôtel Hilton le 20 mars 2014. L'appel à conférenciers est ouvert jusqu'au 2 février 2014 : http://2014.nordicpgday.org/
  • La PGConf NYC 2014 aura lieu les 3 & 4 avril 2014 à New-York (New-York, USA) : http://nyc.pgconf.us/2014/
  • Le sommet Open Data aura lieu le 11 avril 2014 à Denver (Colorado, États-Unis) : http://www.opendatasummit.com
  • La PGCon 2014, la conférence mondiale des développeurs PostgreSQL, se tiendra à Ottawa (Ontario, Canada) du 20 au 24 mai 2014 : http://www.pgcon.org/2014/

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

Fujii Masao a poussé :

Tom Lane a poussé :

  • Fix to_timestamp/to_date's handling of consecutive spaces in format string. When there are consecutive spaces (or other non-format-code characters) in the format, we should advance over exactly that many characters of input. The previous coding mistakenly did a "skip whitespace" action between such characters, possibly allowing more input to be skipped than the user intended. We only need to skip whitespace just before an actual field. This is really a bug fix, but given the minimal number of field complaints and the risk of breaking applications coded to expect the old behavior, let's not back-patch it. Jeevan Chalke http://git.postgresql.org/pg/commitdiff/9a8f5729b4625ec0468ad5a48296c3e729cf3e65
  • Remove pg_stat_statements--1.1.sql. Commit 91484409bdd17f330d10671d388b72d4ef1451d7 should have removed this file, not just reduced it to zero size. http://git.postgresql.org/pg/commitdiff/fe0c690dfdcf628671d62d04caa39449fdc56078
  • Tweak parse location assignment for CURRENT_DATE and related constructs. All these constructs generate parse trees consisting of a Const and a run-time type coercion (perhaps a FuncExpr or a CoerceViaIO). Modify the raw parse output so that we end up with the original token's location attached to the type coercion node while the Const has location -1; before, it was the other way around. This makes no difference in terms of what exprLocation() will say about the parse tree as a whole, so it should not have any user-visible impact. The point of changing it is that we do not want contrib/pg_stat_statements to treat these constructs as replaceable constants. It will do the right thing if the Const has location -1 rather than a valid location. This is a pretty ugly hack, but then this code is ugly already; we should someday replace this translation with special-purpose parse node(s) that would allow ruleutils.c to reconstruct the original query text. (See also commit 5d3fcc4c2e137417ef470d604fee5e452b22f6a7, which also hacked location assignment rules for the benefit of pg_stat_statements.) Back-patch to 9.2 where pg_stat_statements grew the ability to recognize replaceable constants. Kyotaro Horiguchi http://git.postgresql.org/pg/commitdiff/69c7a9838c82bbfdd61301c697e3774e9543805e
  • Allow use of "z" flag in our printf calls, and use it where appropriate. Since C99, it's been standard for printf and friends to accept a "z" size modifier, meaning "whatever size size_t has". Up to now we've generally dealt with printing size_t values by explicitly casting them to unsigned long and using the "l" modifier; but this is really the wrong thing on platforms where pointers are wider than longs (such as Win64). So let's start using "z" instead. To ensure we can do that on all platforms, teach src/port/snprintf.c to understand "z", and add a configure test to force use of that implementation when the platform's version doesn't handle "z". Having done that, modify a bunch of places that were using the unsigned-long hack to use "z" instead. This patch doesn't pretend to have gotten everyplace that could benefit, but it catches many of them. I made an effort in particular to ensure that all uses of the same error message text were updated together, so as not to increase the number of translatable strings. It's possible that this change will result in format-string warnings from pre-C99 compilers. We might have to reconsider if there are any popular compilers that will warn about this; but let's start by seeing what the buildfarm thinks. Andres Freund, with a little additional work by me http://git.postgresql.org/pg/commitdiff/ac4ef637ad2ff2a24847f67d14027b8745f6741e
  • Code review for auto-tuned effective_cache_size. Fix integer overflow issue noted by Magnus Hagander, as well as a bunch of other infelicities in commit ee1e5662d8d8330726eaef7d3110cb7add24d058 and its unreasonably large number of followups. http://git.postgresql.org/pg/commitdiff/2850896961994aa0993b9e2ed79a209750181b8a

Alvaro Herrera a poussé :

Robert Haas a poussé :

Stephen Frost a poussé :

  • Allow type_func_name_keywords in even more places. A while back, 2c92edad48796119c83d7dbe6c33425d1924626d allowed type_func_name_keywords to be used in more places, including role identifiers. Unfortunately, that commit missed out on cases where name_list was used for lists-of-roles, eg: for DROP ROLE. This resulted in the unfortunate situation that you could CREATE a role with a type_func_name_keywords-allowed identifier, but not DROP it (directly- ALTER could be used to rename it to something which could be DROP'd). This extends allowing type_func_name_keywords to places where role lists can be used. Back-patch to 9.0, as 2c92edad48796119c83d7dbe6c33425d1924626d was. http://git.postgresql.org/pg/commitdiff/6c36f383df728866d7085c155cbe45ebc07b195f
  • ALTER TABLESPACE ... MOVE ... OWNED BY. Add the ability to specify the objects to move by who those objects are owned by (as relowner) and change ALL to mean ALL objects. This makes the command always operate against a well-defined set of objects and not have the objects-to-be-moved based on the role of the user running the command. Per discussion with Simon and Tom. http://git.postgresql.org/pg/commitdiff/fbe19ee3b87590f1006d072be5fecf8a33d4e9f5
  • Avoid minor leak in parallel pg_dump. During parallel pg_dump, a worker process closing the connection caused a minor memory leak (particularly minor as we are likely about to exit anyway). Instead, free the memory in this case prior to returning NULL to indicate connection closed. Spotting by the Coverity scanner. Back patch to 9.3 where this was introduced. http://git.postgresql.org/pg/commitdiff/6794a9f9a194e24862e60a918eac031b7641686c
  • Use E, not e, for escaping in example docs. From the Department of Nitpicking, be consistent with other escaping and use 'E' instead of 'e' to escape the string in the example docs for GET DISAGNOSTICS stack = PG_CONTEXT. Noticed by Department Chief Magnus Hagander. http://git.postgresql.org/pg/commitdiff/00ba97365d356823c48c02147b4cd66f8f06b1d6
  • Check dup2() results in syslogger. Consistently check the dup2() call results throughout syslogger.c. It's pretty unlikely that they'll error out, but if they do, ereport(FATAL) instead of blissfully continuing on. Spotted by the Coverity scanner. http://git.postgresql.org/pg/commitdiff/790eaa699e4a9626d8a610ec5844e1fd70d73b4e
  • Fix minor leak in pg_dump. Move allocation to after we check the remote server version, to avoid a possible, very minor, memory leak. This makes us more consistent throughout as most places in pg_dump are done in the same way (due, in part, to previous fixes like this). Spotted by the Coverity scanner. http://git.postgresql.org/pg/commitdiff/152d24f5ddbc535bb437b57856fa3c7c5c630472

Andrew Dunstan a poussé :

Heikki Linnakangas a poussé :

Bruce Momjian a poussé :

Noah Misch a poussé :

Magnus Hagander a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Rushabh Lathia sent in two revisions of a patch to improve the operation of NOT NULL constraints on foreign tables.
  • MauMau sent in another revision of a patch to prevent localizing messages in startup.
  • Oskari Saarenmaa sent in another revision of a patch to throttle pg_basebackup's progress report down to a maximum of once per second.
  • KaiGai Kohei sent in another revision of a patch to implement cache_scan as a contrib module atop the custom scan patch.
  • Laurence Rowe sent in two revisions of a patch to implement json_array_elements_text.
  • Jov sent in a patch to ensure that ALTER USER is actually identical to ALTER ROLE.
  • Alvaro Herrera sent in a patch to implement a multixact_freeze_table_age GUC and set its default.
  • Antonin Houska sent in another revision of a patch to allow throttling backups.
  • Jov sent in another revision of a patch to clarify the -F option in psql.
  • Alexander Korotkov and Marti Raudsepp traded patches to implement partial sorting.
  • Michael Paquier sent in another revision of a patch to implement REINDEX CONCURRENTLY.
  • Steve Crawford sent in a patch to document the broad range of inputs to_date() and to_timestamp() accept.
  • Fujii Masao sent in another revision of a patch to fix a bug where disk space in pg_xlog increases during archive recovery.
  • Mitsumasa KONDO sent in two more revisions of a patch to add min and max execute statement time to the pg_stat_statement view.
  • David Rowley and Florian Pflug traded patches to implement negative transition functions for aggregates.
  • Jon Nelson sent in a PoC patch to elide tuples during an external sort.
  • Kyotaro HORIGUCHI sent in another revision of a patch to add trigger information to auto_explain.
  • Christian Kruse sent in four more revisions of a patch to show process IDs of processes holding a lock.
  • Andrew Dunstan sent in two more revisions of a patch to implement more json functions.
  • Andres Freund sent in two more flocks of patches in service of logical changeset extraction.
  • Michael Paquier and Fujii Masao traded patches to implement the pg_stat_archiver view.
  • Dean Rasheed sent in another revision of a patch to implement updatable security barrier views.
  • Ronan Dunklau sent in another revision of a patch to implement triggers on foreign tables.
  • Emre Hasegeli sent in another revision of a patch to add GiST support for inet datatypes.
  • Andres Freund sent in a patch to add %z support to elog() and ereport().
  • MauMau sent in two more revisions of a patch to fix a bug on Windows where pg_ctl always uses the same event source.
  • Craig Ringer sent in another flock of patches in service of implementing row-level access control.
  • Bruce Momjian and Tom Lane traded patches to fix some issues with authentication error messages.
  • Heikki Linnakangas sent in four more revisions of a patch to add a "fast scan" method to GIN indexes.
  • Simon Riggs sent in another revision of a patch to fix a locking issue in ALTER TABLE.
  • Pavel Raiskup sent in another revision of a patch to make locale comparisons more tolerant of fuzz in pg_upgrade.
  • Bruce Momjian sent in a patch to fix some behaviors after VACUUM FULL.
  • MauMau sent in a patch to allow recovery up to a backup point.
  • MauMau sent in another revision of a patch to fix an issue caused by Address Space Layout Randomization in Windows 8/2012.
  • Bruce Momjian sent in a patch to change a test for attnum to an Assert.
  • Marco Atzeri sent in another revision of a patch to make building on Cygwin work better.
  • Pavel Stehule sent in three more revisions of a patch to add an --if-exists option to pg_dump.
  • Bruce Momjian sent in a patch to fix an issue with how INTERVAL handles overflow conditions.
  • Tom Lane sent in a patch to store pg_stat_statements externally.
  • Andrew Dunstan sent in another revision of a patch to implement nested hstore and jsonb atop that.
  • Andrew Dunstan sent in a patch to allow running "make check" with only specified tests.

par N Bougain le samedi 1 mars 2014 à 19h39

Nouvelles hebdomadaires de PostgreSQL - 19 janvier 2014

La quatrième et dernière commitfest de la version 9.4 de PostgreSQL a commencé. Lancez-vous : relisez et révisez ces patchs ! https://commitfest.postgresql.org/action/commitfest_view?id=21

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en janvier

PostgreSQL Local

  • FOSDEM PGDay, une conférence d'une journée, tenue avant le FOSDEM à Bruxelles, aura lieu le 31 janvier 2014. Détails : http://fosdem2014.pgconf.eu/ http://fosdem2014.pgconf.eu/registration/
  • La 7ème conférence annuelle "Prague PostgreSQL Developers Day" (P2D2), organisée par le CSPUG (PUG tchèque et slovaque), aura lieu le 6 février 2014 à la Faculté des Sciences Mathématiques & Physiques de l'Université Charles (Malostranske namesti 25, Prague). Infos en langue tchèque ci-après : http://www.p2d2.cz/
  • Le PGDay Nordique 2014 aura lieu à Stockholm (Suède) à l'hôtel Hilton le 20 mars 2014. L'appel à conférenciers est ouvert jusqu'au 2 février 2014 : http://2014.nordicpgday.org/
  • La PGConf NYC 2014 aura lieu les 3 & 4 avril 2014 à New-York (New-York, USA) : http://nyc.pgconf.us/2014/
  • Le sommet Open Data aura lieu le 11 avril 2014 à Denver (Colorado, États-Unis) : http://www.opendatasummit.com
  • La PGCon 2014, la conférence mondiale des développeurs PostgreSQL, se tiendra à Ottawa (Ontario, Canada) du 20 au 24 mai 2014 : http://www.pgcon.org/2014/

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

Michael Meskes a poussé :

Heikki Linnakangas a poussé :

Tom Lane a poussé :

  • Fix possible buffer overrun in contrib/pg_trgm. Allow for the possibility that folding a string to lower case makes it longer (due to replacing a character with a longer multibyte character). This doesn't change the number of trigrams that will be extracted, but it does affect the required size of an intermediate buffer in generate_trgm(). Per bug #8821 from Ufuk Kayserilioglu. Also install some checks that the input string length is not so large as to cause overflow in the calculations of palloc request sizes. Back-patch to all supported versions. http://git.postgresql.org/pg/commitdiff/c3ccc9ee584b9b015dd9c1931e261e21f3961e5f
  • Fix multiple bugs in index page locking during hot-standby WAL replay. In ordinary operation, VACUUM must be careful to take a cleanup lock on each leaf page of a btree index; this ensures that no indexscans could still be "in flight" to heap tuples due to be deleted. (Because of possible index-tuple motion due to concurrent page splits, it's not enough to lock only the pages we're deleting index tuples from.) In Hot Standby, the WAL replay process must likewise lock every leaf page. There were several bugs in the code for that: * The replay scan might come across unused, all-zero pages in the * index. While btree_xlog_vacuum itself did the right thing (ie, nothing) with such pages, xlogutils.c supposed that such pages must be corrupt and would throw an error. This accounts for various reports of replication failures with "PANIC: WAL contains references to invalid pages". To fix, add a ReadBufferMode value that instructs XLogReadBufferExtended not to complain when we're doing this. * btree_xlog_vacuum performed the extra locking if standbyState == STANDBY_SNAPSHOT_READY, but that's not the correct test: we won't open up for hot standby queries until the database has reached consistency, and we don't want to do the extra locking till then either, for fear of reading corrupted pages (which bufmgr.c would complain about). Fix by exporting a new function from xlog.c that will report whether we're actually in hot standby replay mode. * To ensure full coverage of the index in the replay scan, * btvacuumscan would emit a dummy WAL record for the last page of the index, if no vacuuming work had been done on that page. However, if the last page of the index is all-zero, that would result in corruption of said page, since the functions called on it weren't prepared to handle that case. There's no need to lock any such pages, so change the logic to target the last normal leaf page instead. The first two of these bugs were diagnosed by Andres Freund, the other one by me. Fixes based on ideas from Heikki Linnakangas and myself. This has been wrong since Hot Standby was introduced, so back-patch to 9.0. http://git.postgresql.org/pg/commitdiff/061b079f89800929a863a692b952207cadf15886
  • Improve FILES section of psql reference page. Primarily, explain where to find the system-wide psqlrc file, per recent gripe from John Sutton. Do some general wordsmithing and improve the markup, too. Also adjust psqlrc.sample so its comments about file location are somewhat trustworthy. (Not sure why we bother with this file when it's empty, but whatever.) Back-patch to 9.2 where the startup file naming scheme was last changed. http://git.postgresql.org/pg/commitdiff/5df99f6481b1eadbcbc8547d2e387f4dcf192c6f
  • Add display of oprcode (the underlying function's name) to psql's \do+. The + modifier of \do didn't use to do anything, but now it adds an oprcode column. This is useful both as an additional form of documentation of what the operator does, and to save a step when finding out properties of the underlying function. Marko Tiikkaja, reviewed by Rushabh Lathia, adjusted a bit by me http://git.postgresql.org/pg/commitdiff/515d2c596c1b6b95d020d14edaab0d233d5d9ea9
  • Add gen_random_uuid() to contrib/pgcrypto. This function provides a way of generating version 4 (pseudorandom) UUIDs based on pgcrypto's PRNG. The main reason for doing this is that the OSSP UUID library depended on by contrib/uuid-ossp is becoming more and more of a porting headache, so we need an alternative for people who can't install that. A nice side benefit though is that this implementation is noticeably faster than uuid-ossp's uuid_generate_v4() function. Oskari Saarenmaa, reviewed by Emre Hasegeli http://git.postgresql.org/pg/commitdiff/e6170126fc201052b0ec5fc92177eb181d602d26
  • Minor code beautification in contrib/sslinfo. Static-ify some functions that didn't need to be exported, and improve a couple of comments. Gurjeet Singh http://git.postgresql.org/pg/commitdiff/af9e3d652358664f2e749be2398428732121e317
  • Make various variables const (read-only). These changes should generally improve correctness/maintainability. A nice side benefit is that several kilobytes move from initialized data to text segment, allowing them to be shared across processes and probably reducing copy-on-write overhead while forking a new backend. Unfortunately this doesn't seem to help libpq in the same way (at least not when it's compiled with -fpic on x86_64), but we can hope the linker at least collects all nominally-const data together even if it's not actually part of the text segment. Also, make pg_encname_tbl[] static in encnames.c, since there seems no very good reason for any other code to use it; per a suggestion from Wim Lewis, who independently submitted a patch that was mostly a subset of this one. Oskari Saarenmaa, with some editorialization by me http://git.postgresql.org/pg/commitdiff/0d79c0a8cc20dbaa39112d78a9abb821c4ca3554
  • Fix VACUUM's reporting of dead-tuple counts to the stats collector. Historically, VACUUM has just reported its new_rel_tuples estimate (the same thing it puts into pg_class.reltuples) to the stats collector. That number counts both live and dead-but-not-yet-reclaimable tuples. This behavior may once have been right, but modern versions of the pgstats code track live and dead tuple counts separately, so putting the total into n_live_tuples and zero into n_dead_tuples is surely pretty bogus. Fix it to report live and dead tuple counts separately. This doesn't really do much for situations where updating transactions commit concurrently with a VACUUM scan (possibly causing double-counting or omission of the tuples they add or delete); but it's clearly an improvement over what we were doing before. Hari Babu, reviewed by Amit Kapila http://git.postgresql.org/pg/commitdiff/115f414124e71749d2d8f512e469ca63bc2166e5

Robert Haas a poussé :

Peter Eisentraut a poussé :

Alvaro Herrera a poussé :

  • Split ECPGdo() in constituent parts. This splits ECPGdo() into ecpg_prologue(), ecpg_do() and ecpg_epilogue(), and renames free_params() into ecpg_free_params() and exports it. This makes it possible for future code to use these routines for their own purposes. There is no user-visible functionality change here, only code reorganization. Zoltán Böszörményi Reviewed by Antonin Houska. Larger, older versions of this patch were reviewed by Noah Misch and Michael Meskes. http://git.postgresql.org/pg/commitdiff/3291301385ee5e9ca38d70a68b93ce31cc2674ac
  • Split ecpg_execute() in constituent parts. Split the rather long ecpg_execute() function into ecpg_build_params(), ecpg_autostart_transaction(), a smaller ecpg_execute() and ecpg_process_output(). There is no user-visible change here, only code reorganization to support future patches. Author: Zoltán Böszörményi Reviewed by Antonin Houska. Larger, older versions of this patch were reviewed by Noah Misch and Michael Meskes. http://git.postgresql.org/pg/commitdiff/61bee9f756ce875f3b678099a6bb9654bd2fa21a

Bruce Momjian a poussé :

Magnus Hagander a poussé :

Andrew Dunstan a poussé :

Stephen Frost a poussé :

  • Allow SET TABLESPACE to database default. We've always allowed CREATE TABLE to create tables in the database's default tablespace without checking for CREATE permissions on that tablespace. Unfortunately, the original implementation of ALTER TABLE ... SET TABLESPACE didn't pick up on that exception. This changes ALTER TABLE ... SET TABLESPACE to allow the database's default tablespace without checking for CREATE rights on that tablespace, just as CREATE TABLE works today. Users could always do this through a series of commands (CREATE TABLE ... Alexander Shulgin SELECT * FROM ...; DROP TABLE ...; etc), so let's fix the oversight in SET TABLESPACE's original implementation. http://git.postgresql.org/pg/commitdiff/6f25c62d788ea6312fe718ed57a3d169d8efc066
  • Add ALTER TABLESPACE ... MOVE command. This adds a 'MOVE' sub-command to ALTER TABLESPACE which allows moving sets of objects from one tablespace to another. This can be extremely handy and avoids a lot of error-prone scripting. ALTER TABLESPACE ... MOVE will only move objects the user owns, will notify the user if no objects were found, and can be used to move ALL objects or specific types of objects (TABLES, INDEXES, or MATERIALIZED VIEWS). http://git.postgresql.org/pg/commitdiff/76e91b38ba64e1da70ea21744b342cb105ea3400
  • Add CREATE TABLESPACE ... WITH ... Options. Tablespaces have a few options which can be set on them to give PG hints as to how the tablespace behaves (perhaps it's faster for sequential scans, or better able to handle random access, etc). These options were only available through the ALTER TABLESPACE command. This adds the ability to set these options at CREATE TABLESPACE time, removing the need to do both a CREATE TABLESPACE and ALTER TABLESPACE to get the correct options set on the tablespace. Vik Fearing, reviewed by Michael Paquier. http://git.postgresql.org/pg/commitdiff/5254958e924cd54f33d37026d85483fef986060d

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Amit Kapila sent in another revision of a patch to create a contrib module to demonstrate dynamic shared memory.
  • Steeve Lennmark sent in three more revisions of a patch to enable pg_basebackup to relocate tablespaces.
  • Alexander Korotkov sent in another revision of a patch to use partial sorting in KNN-GiST to speed up queries.
  • Mitsumasa KONDO sent in another revision of a patch to allow setting a Gaussian distribution in pgbench.
  • Yugo Nagata sent in a patch to implement to_regclass, to_regproc, to_regoper, and to_regtype for the new regclass type.
  • Dilip Kumar sent in a patch to create a case-sensitive mode in the Windows build.
  • Erik Rijkers and Andrew Dunstan traded patches to implement and document nested hstore/jsonb.
  • Alexander Korotkov sent in two more revisions of a patch to improve GIN indexing by storing additional information.
  • Alexander Korotkov and Marti Raudsepp traded patches around partial sorting.
  • Christian Kruse sent in another revision of a patch to show xid and xmin in pg_stat_activity and pg_stat_replication.
  • Kyotaro HORIGUCHI sent in another revision of a patch to ensure that UNION ALL on partitioned tables will use indexes appropriately.
  • Kyotaro HORIGUCHI sent in another revision of a patch to get more from indexes.
  • Kyotaro HORIGUCHI sent in another revision of a patch to ensure that UNION on partitioned tables can take advantage of appropriate indexes.
  • Kyotaro HORIGUCHI sent in another revision of a patch to make it possible for auto_explain to show trigger statistics.
  • Rajeev Rastogi sent in another revision of a patch to fix an issue where the PostgreSQL service on Windows does not start if the data directory given is relative path.
  • KaiGai Kohei sent in another flock of patches implementing and demonstrating the custom scan API.
  • Mitsumasa KONDO sent in another revision of a patch to optimize kernel readahead using buffer access strategy.
  • KaiGai Kohei sent in a patch to implement an alternative way to scan a table using in-memory cache instead of the usual heap access method.
  • Simon Riggs sent in a patch to patch to expose a function GetCurrentTransactionWALVolume() that gives the total number of bytes written to WAL by current transaction.
  • Shigeru HANADA sent in another revision of a patch to allow foreign tables to be children of tables in the sense of table inheritance.
  • Dimitri Fontaine sent in a patch to implement a new GUC that allows users to set up a list of path where PostgreSQL will search for the extension control files at CREATE EXTENSION time.
  • Peter Eisentraut sent in a patch to integrate pg_upgrade's analyze_new_cluster.sh into vacuumdb.
  • Alvaro Herrera sent in another revision of a patch to enable CREATE support for event triggers.
  • Peter Eisentraut sent in a patch to create a function prototype as part of PG_FUNCTION_INFO_V1.
  • Alexander Korotkov sent in another revision of a patch to improve GIN by creating a fast scan option.
  • Simon Riggs sent in two more revisions of a patch to fix an issue where the reduction in lock strength for ALTER TABLE was unsafe.
  • Jaime Casanova sent in another revision of a patch to turn recovery.conf into GUCs.
  • Peter Eisentraut sent in a patch to add tests for client programs (psql, etc.).
  • Mitsumasa KONDO sent in a patch to drop duplicate buffers in OS using a usage_count algorithm.
  • Simon Riggs sent in two more revisions of a patch to allow rate-limiting on WAL.
  • Oskari Saarenmaa sent in another revision of a patch to allow filtering error log statements by SQLSTATE.
  • Simon Riggs sent in a patch to control how aggressively HOT/Cleanup operates for SELECT statements.
  • Heikki Linnakangas and Peter Geoghegan traded patches for INSERT...ON DUPLICATE KEY LOCK FOR UPDATE.
  • Andres Freund sent in another revision of a patch to implement changeset extraction.
  • Marko (johto) Tiikkaja sent in a patch to implement plpgsql.warn_shadow, which allows people to get warnings when a variable shadows a previously defined variable.
  • David Rowley and Florian Pflug traded patches implementing inverse transition functions for aggregates.
  • Simon Riggs sent in another revision of a patch to tune COPY vs. volatile default expressions including nextval(), which is used for surrogate keys.
  • Alexander Korotkov sent in a patch to fix a trigram regex index peculiarity.
  • Peter Eisentraut sent in another revision of a patch to implement TRANSFORMS.
  • Amit Kapila sent in another revision of a patch to improve performance by reducing the volume of WAL during update operations.
  • Alvaro Herrera sent in another revision of a patch to allow throttling of backups.
  • Salah Jubeh sent in another revision of a patch to add a 'force' option to dropdb.
  • Amit Kapila sent in a patch to fix a memory leak in parse_config.
  • Pavel Stehule sent in another revision of a patch to add --if-exists, which inserts IF EXISTS in appropriate spots in pg_dump's output.
  • Andrew Dunstan sent in two more revisions of a patch to implement a flock of new JSON functions.
  • Marko (johto) Tiikkaja sent in three revisions of a patch to implement CARDINALITY for arrays.
  • Jov sent a patch to clarify psql's -F command line option.
  • Kyotaro HORIGUCHI sent in a patch to fix some odd ways a query can be stored in pg_stat_statements.
  • Marti Raudsepp sent in a patch to fix a potential relcache leak in get_object_address_attribute.
  • Michael Paquier sent in a patch to fix some ALTER SYSTEM SET typos and add a fix for temporary file name management.
  • Maor Lipchuk and Tom Lane traded patches to add the value to the error message when a column's data is too large for the size of the column.
  • Craig Ringer sent in another revision of a patch to implement row-level access control.
  • Jeevan Chalke sent in a patch to fix some surprising to_timestamp behavior.

par N Bougain le samedi 1 mars 2014 à 19h38

Nouvelles hebdomadaires de PostgreSQL - 12 janvier 2014

Les programmes du FOSDEM et du FOSDEM/PGDay ont été publiés : http://www.postgresql.eu/events/schedule/fosdem2014/

Le PGDay Nordique 2014 aura lieu à Stockholm (Suède) à l'hôtel Hilton le 20 mars 2014. L'appel à conférenciers est ouvert jusqu'au 2 février 2014 : http://2014.nordicpgday.org/

Le sommet Open Data aura lieu le 11 avril 2014 à Denver (Colorado, États-Unis) : http://www.opendatasummit.com

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en janvier

PostgreSQL Local

  • FOSDEM PGDay, une conférence d'une journée, tenue avant le FOSDEM à Bruxelles, aura lieu le 31 janvier 2014. Détails : http://fosdem2014.pgconf.eu/ http://fosdem2014.pgconf.eu/registration/
  • La 7ème conférence annuelle "Prague PostgreSQL Developers Day" (P2D2), organisée par le CSPUG (PUG tchèque et slovaque), aura lieu le 6 février 2014 à la Faculté des Sciences Mathématiques & Physiques de l'Université Charles (Malostranske namesti 25, Prague). Infos en langue tchèque ci-après. L'appel à conférenciers court jusqu'au 3 janvier 2014 : http://www.p2d2.cz/
  • L'appel à conférenciers pour la PGConf NYC 2014 court jusqu'au 10 janvier 2014. Les notifications seront envoyées le 15 janvier : http://nyc.pgconf.us/2014/
  • L'appel à conférenciers pour le PGCon 2014 est lancé. Date limite : 19 janvier 2014 : http://www.pgcon.org/2014/

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

  • Remove bogus -K option from pg_dump. I added it to the getopt call by accident in commit 691e595dd9c7786d37d73ccd327f8c2b6f0dace6. Amit Kapila http://git.postgresql.org/pg/commitdiff/10a82cda67731941c18256e009edad4a784a2994
  • Silence compiler warning on MSVC. MSVC doesn't know that elog(ERROR) doesn't return, and gives a warning about missing return. Silence that. Amit Kapila http://git.postgresql.org/pg/commitdiff/f68220df92cb56f0452919f51eeef16262ec8f3b
  • Fix bug in determining when recovery has reached consistency. When starting WAL replay from an online checkpoint, the last replayed WAL record variable was initialized using the checkpoint record's location, even though the records between the REDO location and the checkpoint record had not been replayed yet. That was noted as "slightly confusing" but harmless in the comment, but in some cases, it fooled CheckRecoveryConsistency to incorrectly conclude that we had already reached a consistent state immediately at the beginning of WAL replay. That caused the system to accept read-only connections in hot standby mode too early, and also PANICs with message "WAL contains references to invalid pages". Fix by initializing the variables to the REDO location instead. In 9.2 and above, change CheckRecoveryConsistency() to use lastReplayedEndRecPtr variable when checking if backup end location has been reached. It was inconsistently using EndRecPtr for that check, but lastReplayedEndRecPtr when checking min recovery point. It made no difference before this patch, because in all the places where CheckRecoveryConsistency was called the two variables were the same, but it was always an accident waiting to happen, and would have been wrong after this patch anyway. Report and analysis by Tomonari Katsumata, bug #8686. Backpatch to 9.0, where hot standby was introduced. http://git.postgresql.org/pg/commitdiff/d59ff6c110162fc6f3f62b160ff451bfda871af0
  • Fix pause_at_recovery_target + recovery_target_inclusive combination. If pause_at_recovery_target is set, recovery pauses *before* applying the target record, even if recovery_target_inclusive is set. If you then continue with pg_xlog_replay_resume(), it will apply the target record before ending recovery. In other words, if you log in while it's paused and verify that the database looks OK, ending recovery changes its state again, possibly destroying data that you were tring to salvage with PITR. Backpatch to 9.1, this has been broken since pause_at_recovery_target was added. http://git.postgresql.org/pg/commitdiff/3739e5ab93afb21b69da2e42f6e161ef63aa95c8
  • If multiple recovery_targets are specified, use the latest one. The docs say that only one of recovery_target_xid, recovery_target_time, or recovery_target_name can be specified. But the code actually did something different, so that a name overrode time, and xid overrode both time and name. Now the target specified last takes effect, whether it's an xid, time or name. With this patch, we still accept multiple recovery_target settings, even though docs say that only one can be specified. It's a general property of the recovery.conf file parser that you if you specify the same option twice, the last one takes effect, like with postgresql.conf. http://git.postgresql.org/pg/commitdiff/815d71deed5df2a91b06da76edbe5bc64965bfea
  • Refactor checking whether we've reached the recovery target. Makes the replay loop slightly more readable, by separating the concerns of whether to stop and whether to delay, and how to extract the timestamp from a record. This has the user-visible change that the timestamp of the last applied record is now updated after actually applying it. Before, it was updated just before applying it. That meant that pg_last_xact_replay_timestamp() could return the timestamp of a commit record that is in process of being replayed, but not yet applied. Normally the difference is small, but if min_recovery_apply_delay is set, there could be a significant delay between reading a record and applying it. Another behavioral change is that if you recover to a restore point, we stop after the restore point record, not before it. It makes no difference as far as running queries on the server is concerned, as applying a restore point record changes nothing, but if examine the timeline history you will see that the new timeline branched off just after the restore point record, not before it. One practical consequence is that if you do PITR to the new timeline, and set recovery target to the same named restore point again, it will find and stop recovery at the same restore point. Conceptually, I think it makes more sense to consider the restore point as part of the new timeline's history than not. In principle, setting the last-replayed timestamp before actually applying the record was a bug all along, but it doesn't seem worth the risk to backpatch, since min_recovery_apply_delay was only added in 9.4. http://git.postgresql.org/pg/commitdiff/c945af80cfdaf72adb91d6688fb3a4c4f17c0757

Peter Eisentraut a poussé :

Magnus Hagander a poussé :

  • Avoid including tablespaces inside PGDATA twice in base backups. If a tablespace was crated inside PGDATA it was backed up both as part of the PGDATA backup and as the backup of the tablespace. Avoid this by skipping any directory inside PGDATA that contains one of the active tablespaces. Dimitri Fontaine and Magnus Hagander http://git.postgresql.org/pg/commitdiff/b168c5ef2730d0ecaa7462f0b90345b0a3798c16
  • Move permissions check from do_pg_start_backup to pg_start_backup. And the same for do_pg_stop_backup. The code in do_pg_* is not allowed to access the catalogs. For manual base backups, the permissions check can be handled in the calling function, and for streaming base backups only users with the required permissions can get past the authentication step in the first place. Reported by Antonin Houska, diagnosed by Andres Freund http://git.postgresql.org/pg/commitdiff/9544cc0d657ea09d27667c8c70302b06fbe0121b

Tom Lane a poussé :

  • Fix LATERAL references to target table of UPDATE/DELETE. I failed to think much about UPDATE/DELETE when implementing LATERAL . The implemented behavior ended up being that subqueries in the FROM or USING clause (respectively) could access the update/delete target table as though it were a lateral reference; which seems fine if they said LATERAL, but certainly ought to draw an error if they didn't. Fix it so you get a suitable error when you omit LATERAL. Per report from Emre Hasegeli. http://git.postgresql.org/pg/commitdiff/0c051c90082da0b7e5bcaf9aabcbd4f361137cdc
  • Save a few cycles in advance_transition_function(). Keep a pre-initialized FunctionCallInfoData in AggStatePerAggData, and re-use that at each row instead of doing InitFunctionCallInfoData each time. This saves only half a dozen assignments and maybe some stack manipulation, and yet that seems to be good for a percent or two of the overall query run time for simple aggregates such as count(*). The cost is that the FunctionCallInfoData (which is about a kilobyte, on 64-bit machines) stays allocated for the duration of the query instead of being short-lived stack data. But we're already paying an equivalent space cost for each regular FuncExpr or OpExpr node, so I don't feel bad about paying it for aggregate functions. The code seems a little cleaner this way too, since the number of things passed to advance_transition_function decreases. http://git.postgresql.org/pg/commitdiff/e6336b8b5772b9856d65ef967e0b9f748f0f7b0b
  • Avoid extra AggCheckCallContext() checks in ordered-set aggregates. In the transition functions, we don't really need to recheck this after the first call. I had been feeling paranoid about possibly getting a non-null argument value in some other context; but it's probably game over anyway if we have a non-null "internal" value that's not what we are expecting. In the final functions, the general convention in pre-existing final functions seems to be that an Assert() is good enough, so do it like that here too. This seems to save a few tenths of a percent of overall query runtime, which isn't much, but still it's just overhead if there's not a plausible case where the checks would fire. http://git.postgresql.org/pg/commitdiff/847e46abc92333a5a948d8fa886604832c1db238
  • Fix "cannot accept a set" error when only some arms of a CASE return a set. In commit c1352052ef1d4eeb2eb1d822a207ddc2d106cb13, I implemented an optimization that assumed that a function's argument expressions would either always return a set (ie multiple rows), or always not. This is wrong however: we allow CASE expressions in which some arms return a set of some type and others just return a scalar of that type. There may be other examples as well. To fix, replace the run-time test of whether an argument returned a set with a static precheck (expression_returns_set). This adds a little bit of query startup overhead, but it seems barely measurable. Per bug #8228 from David Johnston. This has been broken since 8.0, so patch all supported branches. http://git.postgresql.org/pg/commitdiff/080b7db72ebbec22580237631d6b07d0e1147b01
  • We don't need to include pg_sema.h in s_lock.h anymore. Minor improvement to commit daa7527afc2274432094ebe7ceb03aa41f916607: s_lock.h no longer has any need to mention PGSemaphoreData, so we can rip out the #include that supplies that. In a non-HAVE_SPINLOCKS build, this doesn't really buy much since we still need the #include in spin.h --- but everywhere else, this reduces #include footprint by some trifle, and helps keep the different locking facilities separate. http://git.postgresql.org/pg/commitdiff/220b34331f77effdb46798ddd7cca0cffc1b2858
  • Remove unnecessary local variables to work around an icc optimization bug. Buildfarm member dunlin has been crashing since commit 8b49a60, but other machines seem fine with that code. It turns out that removing the local variables in ordered_set_startup() that are copies of fields in "qstate" dodges the problem. This might cost a few cycles on register-rich machines, but it's probably a wash on others, and in any case this code isn't performance-critical. Thanks to Jeremy Drake for off-list investigation. http://git.postgresql.org/pg/commitdiff/faab7a957d31389f4abfd83784f622c91d076f49
  • Add another regression test cross-checking operator and function comments. Add a query that lists all the functions that are operator implementation functions and have a SQL comment that doesn't just say "implementation of XYZ operator". (Note that the preceding test checks that such functions' comments exactly match the corresponding operators' comments.) While it's not forbidden to add more functions to this list, that should only be done when we're encouraging users to use either the function or operator syntax for the functionality, which is a fairly rare situation. http://git.postgresql.org/pg/commitdiff/28233ffaa436852218113d34aa79b7e54f470ed7
  • Fix compute_scalar_stats() for case that all values exceed WIDTH_THRESHOLD. The standard typanalyze functions skip over values whose detoasted size exceeds WIDTH_THRESHOLD (1024 bytes), so as to limit memory bloat during ANALYZE. However, we (I think I, actually :-() failed to consider the possibility that *every* non-null value in a column is too wide. While compute_minimal_stats() seems to behave reasonably anyway in such a case, compute_scalar_stats() just fell through and generated no pg_statistic entry at all. That's unnecessarily pessimistic: we can still produce valid stanullfrac and stawidth values in such cases, since we do include too-wide values in the average-width calculation. Furthermore, since the general assumption in this code is that too-wide values are probably all distinct from each other, it seems reasonable to set stadistinct to -1 ("all distinct"). Per complaint from Kadri Raudsepp. This has been like this since roughly neolithic times, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/6286526207d53e5b31968103adb89b4c9cd21499
  • Fix possible crashes due to using elog/ereport too early in startup. Per reports from Andres Freund and Luke Campbell, a server failure during set_pglocale_pgservice results in a segfault rather than a useful error message, because the infrastructure needed to use ereport hasn't been initialized; specifically, MemoryContextInit hasn't been called. One known cause of this is starting the server in a directory it doesn't have permission to read. We could try to prevent set_pglocale_pgservice from using anything that depends on palloc or elog, but that would be messy, and the odds of future breakage seem high. Moreover there are other things being called in main.c that look likely to use palloc or elog too --- perhaps those things shouldn't be there, but they are there today. The best solution seems to be to move the call of MemoryContextInit to very early in the backend's real main() function. I've verified that an elog or ereport occurring immediately after that is now capable of sending something useful to stderr. I also added code to elog.c to print something intelligible rather than just crashing if MemoryContextInit hasn't created the ErrorContext. This could happen if MemoryContextInit itself fails (due to malloc failure), and provides some future-proofing against someone trying to sneak in new code even earlier in server startup. Back-patch to all supported branches. Since we've only heard reports of this type of failure recently, it may be that some recent change has made it more likely to see a crash of this kind; but it sure looks like it's broken all the way back. http://git.postgresql.org/pg/commitdiff/910bac5953012198e210848660ea31f27ab08abc
  • Disallow LATERAL references to the target table of an UPDATE/DELETE. On second thought, commit 0c051c90082da0b7e5bcaf9aabcbd4f361137cdc was over-hasty: rather than allowing this case, we ought to reject it for now. That leaves the field clear for a future feature that allows the target table to be re-specified in the FROM (or USING) clause, which will enable left-joining the target table to something else. We can then also allow LATERAL references to such an explicitly re-specified target table. But allowing them right now will create ambiguities or worse for such a feature, and it isn't something we documented 9.3 as supporting. While at it, add a convenience subroutine to avoid having several copies of the ereport for disalllowed-LATERAL-reference cases. http://git.postgresql.org/pg/commitdiff/158b7fa6a34006bdc70b515e14e120d3e896589b

Bruce Momjian a poussé :

Robert Haas a poussé :

  • Reduce the number of semaphores used under --disable-spinlocks. Instead of allocating a semaphore from the operating system for every spinlock, allocate a fixed number of semaphores (by default, 1024) from the operating system and multiplex all the spinlocks that get created onto them. This could self-deadlock if a process attempted to acquire more than one spinlock at a time, but since processes aren't supposed to execute anything other than short stretches of straight-line code while holding a spinlock, that shouldn't happen. One motivation for this change is that, with the introduction of dynamic shared memory, it may be desirable to create spinlocks that last for less than the lifetime of the server. Without this change, attempting to use such facilities under --disable-spinlocks would quickly exhaust any supply of available semaphores. Quite apart from that, it's desirable to contain the quantity of semaphores needed to run the server simply on convenience grounds, since using too many may make it harder to get PostgreSQL running on a new platform, which is mostly the point of --disable-spinlocks in the first place. Patch by me; review by Tom Lane. http://git.postgresql.org/pg/commitdiff/daa7527afc2274432094ebe7ceb03aa41f916607

Michael Meskes a poussé :

Alvaro Herrera a poussé :

  • Accept pg_upgraded tuples during multixact freezing. The new MultiXact freezing routines introduced by commit 8e9a16ab8f7 neglected to consider tuples that came from a pg_upgrade'd database; a vacuum run that tried to freeze such tuples would die with an error such as ERROR: MultiXactId 11415437 does no longer exist -- apparent wraparound To fix, ensure that GetMultiXactIdMembers is allowed to return empty multis when the infomask bits are right, as is done in other callsites. Per trouble report from F-Secure. In passing, fix a copy&paste bug reported by Andrey Karpov from VIVA64 from their PVS-Studio static checked, that instead of setting relminmxid to Invalid, we were setting relfrozenxid twice. Not an important mistake because that code branch is about relations for which we don't use the frozenxid/minmxid values at all in the first place, but seems to warrants a fix nonetheless. http://git.postgresql.org/pg/commitdiff/423e1211a86df0d0dd8914223137edbfd4d52400

Andrew Dunstan a poussé :

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Robert Haas sent in a patch to keep lwlocks in a hash table for dynamic shared memory.
  • Rajeev Rastogi sent in a patch to fix a case where PostgreSQL as win32 service does not start.
  • Dean Rasheed sent in three more revisions of a patch to make updateable security barrier views.
  • Christian Ullrich sent in a patch to prevent Windows from stopping unnecessarily when running in console and a random control-C gets sent.
  • Ronan Dunklau sent in two revisions of a patch to implement triggers on foreign tables.
  • Tom Lane sent in a patch to speed up nodeAgg by avoiding redoing InitFunctionCallInfoData for each row.
  • Dilip Kumar sent in a patch to implement a machine-readable pg_controldata.
  • Gabriele Bartolini sent in two more revisions of a patch to implement a pg_stat_archiver view.
  • Peter Geoghegan and Heikki Linnakangas traded patches to implement INSERT ... ON DUPLICATE KEY LOCK FOR UPDATE.
  • Pavel Raiskup sent in another revision of a patch to make the locale comparison more tolerant.
  • Andreas Karlsson sent in another revision of a patch to include planning time in EXPLAIN [ANALYZE].
  • Alexander Korotkov sent in another revision of a patch to improve GIN by having it store more information.
  • Peter Eisentraut sent in a patch to integrate pg_upgrade analyze_new_cluster.sh into vacuumdb.
  • Oleg Bartunov sent in another revision of a patch to implement an efficient on-disk representation of nested hstores. Andrew Dunstan used this to add those efficiencies to JSONB. Erik Rijkers pitched in some documentation fixes.
  • Oskari SaarEnmaa sent in a patch allow the default log_min_error_statement to be overridden per SQLSTATE.
  • Marko (johto) Tiikkaja sent in a patch to display oprcode and its volatility in \do+.
  • Etsuro Fujita sent in another revision of a patch to show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan.
  • Amit Kapila sent in a patch to fixes both the display of wrong file name and usage of PG_AUTOCONF_FILENAME in ALTER SYSTEM.
  • Amit Kapila sent in another revision of a patch to improve performance of update operations by reducing the WAL they generate.
  • Fabrízio de Royes Mello sent in two revisions of a patch to remove the newlines at end of generated SQL in src/bin/scripts.
  • Heikki Linnakangas sent in a patch to add a recovery target called 'immediate' which stops recovery at the earliest time it can do so successfully.
  • Pavel Stehule sent in two revisions of a patch to allow multiple PL/pgsql plugins.
  • Oskari Saarenmaa sent in a patch to implement gen_random_uuid().
  • Steeve Lennmark sent in three revisions of a patch to enable relocating tablespaces in pg_basebackup.
  • Robert Haas sent in a patch to create a LWLock array for dynamic shared memory.
  • Bruce Momjian sent in a patch to update pg_resetxlog.c in light of the earlier update to Autoconf 2.69.
  • David Rowley sent in another revision of a patch to implement inverse transition functions for aggregates.
  • Craig Ringer sent in a patch to fix double-inclusion of pg_config_os.h when building extensions with Visual Studio.
  • Amit Kapila sent in a patch to enable retaining dynamic shared memory segments for postmaster lifetime.
  • Pavel Stehule sent in another revision of a patch to implement an efficient make_timestamp() function which only takes numeric inputs.
  • Marko (johto) Tiikkaja sent in a patch to make the PL/pgsql version of SELECT ... INTO error out instead of producing unexpected results silently when fed more than one row.

par N Bougain le samedi 1 mars 2014 à 19h36

Nouvelles hebdomadaires de PostgreSQL - 5 janvier 2014

Bonne année de la part des nouvelles hebdo !

Offres d'emplois autour de PostgreSQL en janvier

PostgreSQL Local

  • FOSDEM PGDay, une conférence d'une journée, tenue avant le FOSDEM à Bruxelles, aura lieu le 31 janvier 2014. Détails : http://fosdem2014.pgconf.eu/ http://fosdem2014.pgconf.eu/registration/
  • La 7ème conférence annuelle "Prague PostgreSQL Developers Day" (P2D2), organisée par le CSPUG (PUG tchèque et slovaque), aura lieu le 6 février 2014 à la Faculté des Sciences Mathématiques & Physiques de l'Université Charles (Malostranske namesti 25, Prague). Infos en langue tchèque ci-après. L'appel à conférenciers court jusqu'au 3 janvier 2014 : http://www.p2d2.cz/
  • L'appel à conférenciers pour la PGConf NYC 2014 court jusqu'au 10 janvier 2014. Les notifications seront envoyées le 15 janvier : http://nyc.pgconf.us/2014/
  • L'appel à conférenciers pour le PGCon 2014 est lancé. Date limite : 19 janvier 2014 : http://www.pgcon.org/2014/
  • Le Nordic PGDay 2014 aura lieu à Stockholm le 20 mars 2014 : http://www.nordicpgday.org/

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

  • Extract restriction OR clauses whether or not they are indexable. It's possible to extract a restriction OR clause from a join clause that has the form of an OR-of-ANDs, if each sub-AND includes a clause that mentions only one specific relation. While PG has been aware of that idea for many years, the code previously only did it if it could extract an indexable OR clause. On reflection, though, that seems a silly limitation: adding a restriction clause can be a win by reducing the number of rows that have to be filtered at the join step, even if we have to test the clause as a plain filter clause during the scan. This should be especially useful for foreign tables, where the change can cut the number of rows that have to be retrieved from the foreign server; but testing shows it can win even on local tables. Per a suggestion from Robert Haas. As a heuristic, I made the code accept an extracted restriction clause if its estimated selectivity is less than 0.9, which will probably result in accepting extracted clauses just about always. We might need to tweak that later based on experience. Since the code no longer has even a weak connection to Path creation, remove orindxpath.c and create a new file optimizer/util/orclauses.c. There's some additional janitorial cleanup of now-dead code that needs to happen, but it seems like that's a fit subject for a separate commit. http://git.postgresql.org/pg/commitdiff/f343a880d5555faf1dad0286c5632047c8f599ad
  • Remove dead code now that orindxpath.c is history. We don't need make_restrictinfo_from_bitmapqual() anymore at all. generate_bitmap_or_paths() doesn't need to be exported, and we can drop its rather klugy restriction_only flag. http://git.postgresql.org/pg/commitdiff/f7fbf4b0be509d964041ee13311b9203a035e4ab
  • Fix alphabetization in catalogs.sgml. Some recent patches seem not to have grasped the concept that the catalogs are described in alphabetical order. http://git.postgresql.org/pg/commitdiff/d7ee4311afb14118d340024ac9e4dd5d2a9d6966
  • Fix broken support for event triggers as extension members. CREATE EVENT TRIGGER forgot to mark the event trigger as a member of its extension, and pg_dump didn't pay any attention anyway when deciding whether to dump the event trigger. Per report from Moshe Jacobson. Given the obvious lack of testing here, it's rather astonishing that ALTER EXTENSION ADD/DROP EVENT TRIGGER work, but they seem to. http://git.postgresql.org/pg/commitdiff/c01bc51f8d596d425d897c833ef11ca3670ea984
  • Fix contrib/pg_upgrade to clean all the cruft made during "make check". Although these files get cleaned up if the test runs to completion, a failure partway through leaves trash all over the floor. The Makefile ought to be bright enough to get rid of it when you say "make clean". http://git.postgresql.org/pg/commitdiff/4cf81b737d5bb5f695671479c427c78f95c82119
  • Fix calculation of maximum statistics-message size. The PGSTAT_NUM_TABENTRIES macro should have been updated when new fields were added to struct PgStat_MsgTabstat in commit 644828908, but it wasn't. Fix that. Also, add a static assertion that we didn't overrun the intended size limit on stats messages. This will not necessarily catch every mistake in computing the maximum array size for stats messages, but it will catch ones that have practical consequences. (The assertion in fact doesn't complain about the aforementioned error in PGSTAT_NUM_TABENTRIES, because that was not big enough to cause the array length to increase.) No back-patch, as there's no actual bug in existing releases; this is just in the nature of future-proofing. Mark Dilger and Tom Lane http://git.postgresql.org/pg/commitdiff/a7ef273e1cebb913cd4a524fcf3b42caa41bd431
  • Ooops, should use double not single quotes in StaticAssertStmt(). That's what I get for testing this on an older compiler. http://git.postgresql.org/pg/commitdiff/a3b4aeecfe9870fd5895cf362cd1e92544ec885a
  • Fix typo in comment. classifyClauses was renamed to classifyConditions somewhere along the line, but this comment didn't get the memo. Ian Barwick http://git.postgresql.org/pg/commitdiff/9929975666904bcad6df5cb8fe73451fd4910621
  • Fix header comment for bitncmp(). The result is an int less than, equal to, or greater than zero, in the style of memcmp (and, in fact, exactly the output of memcmp in some cases). This comment previously said -1, 1, or 0, which was an overspecification, as noted by Emre Hasegeli. All of the existing callers appear to be fine with the actual behavior, so just fix the comment. In passing, improve infelicitous formatting of some call sites. http://git.postgresql.org/pg/commitdiff/5858cf8ab2c7a186fab050725992d6ef640e38a5
  • Fix translatability markings in psql, and add defenses against future bugs. Several previous commits have added columns to various \d queries without updating their translate_columns[] arrays, leading to potentially incorrect translations in NLS-enabled builds. Offenders include commit 893686762 (added prosecdef to \df+), c9ac00e6e (added description to \dc+) and 3b17efdfd (added description to \dC+). Fix those cases back to 9.3 or 9.2 as appropriate. Since this is evidently more easily missed than one would like, in HEAD also add an Assert that the supplied array is long enough. This requires an API change for printQuery(), so it seems inappropriate for back branches, but presumably all future changes will be tested in HEAD anyway. In HEAD and 9.3, also clean up a whole lot of sloppiness in the emitted SQL for \dy (event triggers): lack of translatability due to failing to pass words-to-be-translated through gettext_noop(), inadequate schema qualification, and sloppy formatting resulting in unnecessarily ugly -E output. Peter Eisentraut and Tom Lane, per bug #8702 from Sergey Burladyan http://git.postgresql.org/pg/commitdiff/92459e7a7f87f91fc3012bea9eef870cf464d91f
  • Cache catalog lookup data across groups in ordered-set aggregates. The initial commit of ordered-set aggregates just did all the setup work afresh each time the aggregate function is started up. But in a GROUP BY query, the catalog lookups need not be repeated for each group, since the column datatypes and sort information won't change. When there are many small groups, this makes for a useful, though not huge, performance improvement. Per suggestion from Andrew Gierth. Profiling of these cases suggests that it might be profitable to avoid duplicate lookups within tuplesort startup as well; but changing the tuplesort APIs would have much broader impact, so I left that for another day. http://git.postgresql.org/pg/commitdiff/8b49a6044d06b557047210dba2735081bb037e96

Michael Meskes a poussé :

Robert Haas a poussé :

Alvaro Herrera a poussé :

  • Wrap multixact/members correctly during extension. In the 9.2 code for extending multixact/members, the logic was very simple because the number of entries in a members page was a proper divisor of 2^32, and thus at 2^32 wraparound the logic for page switch was identical than at any other page boundary. In commit 0ac5ad5134f I failed to realize this and introduced code that was not able to go over the 2^32 boundary. Fix that by ensuring that when we reach the last page of the last segment we correctly zero the initial page of the initial segment, using correct uint32-wraparound-safe arithmetic. Noticed while investigating bug #8673 reported by Serge Negodyuck, as diagnosed by Andres Freund. http://git.postgresql.org/pg/commitdiff/a50d97625497b76e3dc7c4aa22cd2c70317ec54d
  • Handle 5-char filenames in SlruScanDirectory. Original users of slru.c were all producing 4-digit filenames, so that was all that that code was prepared to handle. Changes to multixact.c in the course of commit 0ac5ad5134f made pg_multixact/members create 5-digit filenames once a certain threshold was reached, which SlruScanDirectory wasn't prepared to deal with; in particular, 5-digit-name files were not removed during truncation. Change that routine to make it aware of those files, and have it process them just like any others. Right now, some pg_multixact/members directories will contain a mixture of 4-char and 5-char filenames. A future commit is expected fix things so that each slru.c user declares the correct maximum width for the files it produces, to avoid such unsightly mixtures. Noticed while investigating bug #8673 reported by Serge Negodyuck. http://git.postgresql.org/pg/commitdiff/638cf09e76d70dd83d8123e7079be6c0532102d2
  • Handle wraparound during truncation in multixact/members. In pg_multixact/members, relying on modulo-2^32 arithmetic for wraparound handling doesn't work all that well. Because we don't explicitely track wraparound of the allocation counter for members, it is possible that the "live" area exceeds 2^31 entries; trying to remove SLRU segments that are "old" according to the original logic might lead to removal of segments still in use. To fix, have the truncation routine use a tailored SlruScanDirectory callback that keeps track of the live area in actual use; that way, when the live range exceeds 2^31 entries, the oldest segments still live will not get removed untimely. This new SlruScanDir callback needs to take care not to remove segments that are "in the future": if new SLRU segments appear while the truncation is ongoing, make sure we don't remove them. This requires examination of shared memory state to recheck for false positives, but testing suggests that this doesn't cause a problem. The original coding didn't suffer from this pitfall because segments created when truncation is running are never considered to be removable. Per Andres Freund's investigation of bug #8673 reported by Serge Negodyuck. http://git.postgresql.org/pg/commitdiff/722acf51a0d074d19782ad7e97ebe3fdb63fbb87
  • Restore some comments lost during 15732b34e8c8. Michael Paquier http://git.postgresql.org/pg/commitdiff/1a3e82a7f94e63979032ee84b2f8b0c93d441fbd

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Christian Kruse sent in a pair of patches to improve the log messages generated by log_lock_waits.
  • Andreas Karlsson sent in a patch to improve the previous patch which uses partial sorting to improve the speed of plan execution. This patch reuses the tuplesort state.
  • Christian Kruse sent in two revisions of a patch to show relation and tuple infos of a lock to acquire.
  • Dimitri Fontaine and Magnus Hagander traded patches to fix pg_basebackup with tablespaces found in $PGDATA.
  • Peter Eisentraut sent in a patch to introduce more psprintf()s to replace hard-coded palloc(N) + sprintf() and similar constructs.
  • David Rowley sent in two more revisions of a patch to implement inverse transition functions for aggregates.
  • David Rowley sent in a patch to remove some apparently duplicate if conditions.
  • Wim Lewis sent in a patch to make various variables read-only (const).
  • Fabien COELHO sent in a patch to fix a bug in the ISN extension checksum.
  • Andrew Dunstan sent in a WIP patch to implement json_to_record, json_to_recordset, json_object, json_build_array, json_build_object, and json_object_agg.
  • Peter Geoghegan sent in another revision of a patch to implement INSERT...ON DUPLICATE KEY LOCK FOR UPDATE.
  • Amit Kapila sent in a patch to remove the non-existent -K option from pg_dump.
  • Gabriele Bartolini sent in two revisions of a patch to implement a pg_stat_archiver view.
  • Ian Lawrence Barwick sent in a patch to fix a typo in a comment in postgres_fdw/postgres_fdw.c.
  • Emre Hasegeli sent in another revision of a patch to add GiST indexing support for inet datatypes.
  • Robert Haas sent in a patch to handle a case where in DSM, something tries to release a lwlock it hadn't acquired, by printing the pointer.

par N Bougain le samedi 1 mars 2014 à 19h35

dimanche 23 février 2014

Actualités PostgreSQL.fr

[PG Day France 2014] Appel à orateurs

Le PG Day France est la conférence annuelle de la communauté francophone de PostgreSQL. Cette année, l’événement se tiendra le 5 et 6 juin à Toulon. Une centaine de visiteurs sont attendus pour une journée d'échanges autour de PostgreSQL et de ses projets associés. Retrouvez plus d'informations sur le site de l’événement : http://www.pgday.fr

Vous êtes expert sur un domaine lié aux bases de données libres ? Vous avez utilisé PostgreSQL dans un contexte spécifique (gros volumes, forte charge, client reconnu, projet innovant, etc.) ? Vous participez à un projet libre lié à PostgreSQL ? Alors n'hésitez pas à proposer une présentation !

Pour l’édition 2014, les thèmes particulièrement mis en lumière sont les suivants :

 * Administration de bases volumineuses
 * Études de cas / témoignages
 * Industrialisation 
 * Entrepôts de données
 * Systèmes décisionnels
 * Travaux sur la sémantique
 * Big Data
 * Data Mining / Exploration de Données
 * Systèmes d'Information Géographiques

Cette liste n'est pas exhaustive. Il est possible de proposer d'autres sujets liés à PostgreSQL.

La conférence PG Day France est à destination des professionnels, notamment les directeurs informatiques, les décideurs, les chefs de projets, les administrateurs de bases de données, les développeurs, les administrateurs systèmes et tous les profils qui entrent en contact avec un SGBD.

Pour soumettre une intervention, il vous suffit d'envoyer un e-mail à l'adresse contact@pgday.fr, en précisant les éléments suivants :

 * vos nom et prénom
 * votre société/employeur
 * une bio succincte (300 caractères max.)
 * votre compte twitter (optionnel)
 * le titre de votre intervention
 * la durée de votre intervention (45 min. max.)
 * une description courte (200 caractères max.)
 * une description longue (700 caractères max.)
 * une photo (200x200 pixels minimum)

Les interventions devront être en français et disponibles sous licence libre. Les interventions pourront faire l'objet d'une captation audio/vidéo et d'une diffusion sur internet.

La date limite de réception des propositions est fixée au 14 mars 2014 à 23h59 CEST.

Dans le courant du mois de mars 2014, un sondage communautaire sera organisé au sein de la communauté francophone pour évaluer les différentes propositions.

Ensuite, le comité de sélection étudiera toutes les propositions valides. Le choix des sessions sera basé sur la présentation de la soumission, son intérêt pour une audience professionnelle, la cohérence du programme de la journée et sur le résultat du vote préliminaire. La décision du comité de sélection sera finale et sans appel.

Les membres du comité s'expriment en leur nom propre. Leurs choix ne reflètent pas la position de leur employeur.

Les orateurs sélectionnés seront avertis par e-mail avant le 7 avril 2014, jour de l'annonce du programme.

Pour toute question à propos de cet appel à conférenciers et du PG Day France en général, vous pouvez envoyer un message à l'adresse : contact@pgday.fr

par Gautier Di Folco le dimanche 23 février 2014 à 12h15

jeudi 20 février 2014

Actualités PostgreSQL.fr

Sortie des versions correctives 9.3.3, 9.2.7, 9.1.12, 9.0.16 et 8.4.20

Le PostgreSQL Global Development Group publie aujourd'hui une mise-à-jour importante de toutes les versions supportées du SGBD PostgreSQL. Les versions publiées sont : 9.3.3, 9.2.7, 9.1.12, 9.0.16 et 8.4.20.

Cette mise-à-jour contient les correctifs de nombreux problèmes de sécurité et d'intégrité des données.

Cette mise-à-jour doit être appliquée avec la plus grande célérité, spécialement si la réplication binaire est utilisée, ou dans le cas d'applications sécurisées.

Correctifs de sécurité


Cette mise-à-jour corrige l'annonce de sécurité CVE-2014-0060, qui relève que PostgreSQL n'applique pas correctement la permission WITH ADMIN OPTION de la gestion des rôles.
Avant ce correctif, tout membre d'un ROLE pouvait accorder à d'autres la possibilité d'accéder à ce ROLE, même s'il n'avait pas le pouvoir WITH ADMIN OPTION. Il corrige aussi plusieurs problèmes d'escalade de privilèges, dont :
CVE-2014-0061,
CVE-2014-0062,
CVE-2014-0063,
CVE-2014-0064,
CVE-2014-0065 et
CVE-2014-0066.

Pour plus d'informations concernant ces problèmes, se référer aux pages de sécurité et au détail sur le WIKI.

Avec cette version, nous alertons également les utilisateurs sur une faille de sécurité connue qui permet aux utilisateurs d'une même machine d'accéder à un compte système lors d'un "make check" en cas de compilation depuis les sources : CVE-2014-0067.

Cette faille ne peut être corrigée sans problème pour notre infrastructure de test, aussi nous publierons un correctif distinct, et publique. Cette vulnérabilité étant temporelle, il est fortement recommandé de ne pas lancer de "make check" sur des systèmes où se trouvent des comptes utilisateurs non approuvés.

Corectifs de réplication et d'intégrité des données


Cette mise-à-jour corrige également des problèmes affectant la réplication binaire et le verrouillage de niveau ligne, et peut conduire à des corruptions de données récupérables.

Elle contient aussi des correctifs au verrouillage des pages d'index qui peut entraîner la corruption des index sur le réplica.

Un correctif d'un bogue du gel de transaction est fourni pour la version 9.3. Ce bogue pouvait faire réapparaître d'anciennes versions de lignes au sein de bases qui ont subit de nombreuses boucles dans les ID de transactions.

3 bogues pouvant empêcher le démarrage de nouveaux serveurs en StandBy.

Enfin, cette version corrige un bogue de corruption de clés étrangères, bien que les clés doivent être réparées manuellement après l'application du correctif.

En version 9.3, ces correctifs résultent en l'ajout de nouveaux paramètres de configuration du serveur pour gérer le gel des multixact.

À noter que les serveurs de secours doivent être mis à jour en 9.3.3 avant que le maître de réplication ne soit mis à jour, au risque de casser la réplication.

Autres améliorations


En plus des informations précédentes, les problèmes suivants ont été corrigés :

  • Correctif des traces binaires des modifications de la carte de visibilité ;
  • S'assurer que les index GIN tracent toutes les insertions ;
  • S'assurer que pause_at_recovery_target s'interrompe au bon moment ;
  • S'assurer que walreceiver envoie les messages de retour hot-standby au bon moment ;
  • Empêcher le code principal de perdre la main du fait de timeout ;
  • Eliminer de nombreuses conditions de concurrence critique ;
  • Corriger quelques HINTs dans les messages d'erreur ;
  • Prévenir le blocage du serveur en cas de perte de la connexion SSL ;
  • Correction de deux erreurs de gestion de l'Unicode ;
  • Prévenir le crash sur certaines syntaxes de sous-requêtes ;
  • Prévenir le crash lors de sélection sur des tables sans colonnes ;
  • Correction de deux bogues sur LATERAL ;
  • Correction de problèmes avec UNION ALL, le partitionnement et les updates ;
  • S'assurer que ANALYZE comprenne les domaines sur intervalles ;
  • Eliminer les vérifications de permissions lors de l'utilisation du tablespace par défaut ;
  • Correction de fuites mémoire dans les fonctions JSON ;
  • Autoriser les extensions sur déclencheurs par événement (event triggers) ;
  • Distinguer correctement les nombres dans les sorties JSON ;
  • Corriger les permissions pour pg_start_backup() et pg_stop_backup() ;
  • Accepter SHIFT_JIS comme nom de locale ;
  • Corriger le développement des .* pour les variables de fonctions SQL ;
  • Prévenir les boucles sans fin dans quelques erreurs de COPY ;
  • Plusieurs correctifs de problème client sous Windows ;
  • Rendre possible la construction de PostgreSQL avec Visual Studio 2013 ;
  • Mise-à-jour des fichiers de time zone en fonction des modifications récentes.

Des correctifs des modules suivants sont également publiés : ECPG, dblink, ISN, pgbench, pg_stat_statements et postgres_fdw. Les modifications et détails sont accessibles dans les notes de version de la documentation officielle.

Comme pour toute mise-à-jour mineure, il n'est pas nécessaire d'exporter/importer les bases ou d'utiliser pg_upgrade. Il suffit d'arrêter PostgreSQL, d'installer les nouveaux binaires, et de redémarrer. Si des versions mineures ont été oubliées précédemment, il peut être nécessaire de procéder à des étapes supplémentaires. Tous les détails sont accessibles dans les notes de version de la documentation officielle.

Liens:

 * Téléchargement
 * Notes de version
 * Security Page
 * Security Issue Detail Page

par SAS le jeudi 20 février 2014 à 10h13

mercredi 15 janvier 2014

Dimitri Fontaine

PostgreSQL User Group Paris

Notre première rencontre des utilisateurs Parisiens de PostgreSQL avait eu lieue le 28 novembre 2013 et a fait l'objet d'un billet de présentation ici-même : l'article Groupe d'Utilisateurs PostgreSQL à Paris annonce la création du groupe !

Magnus Hagander, core-team member

Pour notre seconde rencontre, un membre de la “core-team” de PostgreSQL sera parmis nous. En effet Magnus Hagander de passage à Paris viendra nous présenter un sujet soumis au vote : préférez-vous entendre parler de PostgreSQL 9.4 ou bien de la gestion avancée de l'invalidation d'un moteur de cache avec PostgreSQL ?

Appel à orateurs : les lightning talks

Pour cette prochaine rencontre, je tiens à trouver quelques volontaires afin de nous présenter leur sujet de prédilection autour de PostgreSQL, dans une présentation de 5 minutes chrono. Les slides ne sont pas obligatoires, mais fortement conseillés.

Pour ce type de présentation, privilégiés un support très léger : 1 image par vignette, ou bien 1 à 5 mots écrits très gros. Venez avec un message simple, et si vous êtes contents de votre présentation, vous pourrez envisager sereinement de le développer lors d'une prochaine rencontre !

Afin de pouvoir nous présenter votre lightning talk le 20 février au soir, merci de m'envoyer par email votre proposition.

Déroulement de la soirée

Les détails se trouvent sur la page Meetup de l'évènement où il faut vous inscrire afin d'être comptabilisés parmis les heureux bénéficiaires de la soirée : si l' IRILL sponsorise une fois de plus l'hébergement de l'évènement, cette fois 2ndQuadrant prendra en charge le buffet.

Après une présentation de la part de l'un des 6 membres de la Core Team, nous partageront des pizzas et des boissons de circonstance (eau, soda, bière, vin…).

Venez nombreux !

par dim@tapoueh.org (Dimitri Fontaine) le mercredi 15 janvier 2014 à 20h23

mercredi 1 janvier 2014

Actualités PostgreSQL.fr

Nouvelles hebdomadaires de PostgreSQL - 29 décembre 2013

Le Nordic PGDay 2014 aura lieu à Stockholm le 20 mars 2014 : http://www.nordicpgday.org/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en décembre

PostgreSQL Local

  • FOSDEM PGDay, une conférence d'une journée, tenue avant le FOSDEM à Bruxelles, aura lieu le 31 janvier 2014. Détails : http://fosdem2014.pgconf.eu/ http://fosdem2014.pgconf.eu/registration/
  • La 7ème conférence annuelle "Prague PostgreSQL Developers Day" (P2D2), organisée par le CSPUG (PUG tchèque et slovaque), aura lieu le 6 février 2014 à la Faculté des Sciences Mathématiques & Physiques de l'Université Charles (Malostranske namesti 25, Prague). Infos en langue tchèque ci-après. L'appel à conférenciers court jusqu'au 3 janvier 2014 : http://www.p2d2.cz/
  • L'appel à conférenciers pour la PGConf NYC 2014 court jusqu'au 10 janvier 2014. Les notifications seront envoyées le 15 janvier : http://nyc.pgconf.us/2014/
  • L'appel à conférenciers pour le PGCon 2014 est lancé. Date limite : 19 janvier 2014 : http://www.pgcon.org/2014/

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

Robert Haas a poussé :

  • Revise documentation for new freezing method. Commit 37484ad2aacef5ec794f4dd3d5cf814475180a78 invalidated a good chunk of documentation, so patch it up to reflect the new state of play. Along the way, patch remaining documentation references to FrozenXID to say instead FrozenTransactionId, so that they match the way we actually spell it in the code. http://git.postgresql.org/pg/commitdiff/d43760b624b7cebc35e5f9c60d2e1439cbe9b220

Peter Eisentraut a poussé :

Andrew Dunstan a poussé :

  • Properly detect invalid JSON numbers when generating JSON. Instead of looking for characters that aren't valid in JSON numbers, we simply pass the output string through the JSON number parser, and if it fails the string is quoted. This means among other things that money and domains over money will be quoted correctly and generate valid JSON. Fixes bug #8676 reported by Anderson Cristian da Silva. Backpatched to 9.2 where JSON generation was introduced. http://git.postgresql.org/pg/commitdiff/29dcf7ded5ef8533376689a85c5b242fc7ace01d

Kevin Grittner a poussé :

  • Fix misplaced right paren bugs in pgstatfuncs.c. The bug would only show up if the C sockaddr structure contained zero in the first byte for a valid address; otherwise it would fail to fail, which is probably why it went unnoticed for so long. Patch submitted by Joel Jacobson after seeing an article by Andrey Karpov in which he reports finding this through static code analysis using PVS-Studio. While I was at it I moved a definition of a local variable referenced in the buggy code to a more local context. Backpatch to all supported branches. http://git.postgresql.org/pg/commitdiff/a133bf7031ad5b62281b21dbd6b2097d3d400f0d
  • Don't attempt to limit target database for pg_restore. There was an apparent attempt to limit the target database for pg_restore to version 7.1.0 or later. Due to a leading zero this was interpreted as an octal number, which allowed targets with version numbers down to 2.87.36. The lowest actual release above that was 6.0.0, so that was effectively the limit. Since the success of the restore attempt will depend primarily on on what statements were generated by the dump run, we don't want pg_restore trying to guess whether a given target should be allowed based on version number. Allow a connection to any version. Since it is very unlikely that anyone would be using a recent version of pg_restore to restore to a pre-6.0 database, this has little to no practical impact, but it makes the code less confusing to read. Issue reported and initial patch suggestion from Joel Jacobson based on an article by Andrey Karpov reporting on issues found by PVS-Studio static code analyzer. Final patch based on analysis by Tom Lane. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/47f50262e7ce5ba37940d8a94198b7a7f4892c0e

Correctifs rejetés (à ce jour)

  • No one was disappointed this week

Correctifs en attente

  • Robert Haas sent in another revision of a patch to implement shared memory message queues.
  • Michael Paquier sent in a patch to align the internal name of wal_log_hints with its external name.
  • Andres Freund sent in a WIP patch to remove system columns from pg_attribute.
  • Robert Haas sent in a patch to implement a pg_tuple_header function.
  • Andreas Karlsson sent in two revisions of a patch to allow including planning time in EXPLAIN [ANALYZE].
  • Sergey Muraviov sent in another revision of a patch to improve how psql displays wide output.
  • Michael Paquier sent in a patch to add a pg_prewarm--unpackaged--1.0.sql to the new pg_prewarm contrib module.
  • Vik Fearing sent in a patch to implement CREATE TABLESPACE ... SET ...
  • MauMau sent in a patch to fix an issue where an ECPG application crashes due to SIGBUS on SPARC Solaris.
  • MauMau sent in a patch to fix an issue where "pg_ctl stop" times out when it should respond quickly.
  • Joel Jacobson sent in a patch to fix an issue where compare returned value by socket() against PGINVALID_SOCKET instead of < 0.
  • David Rowley sent in two more revisions of a patch to implement inverse transition functions for aggregates.
  • Marko Kreen sent in a patch to fix an issue where memset was being used in a dangerous way in pg_crypto. The patch replaces memset in places where it might be optimized away with px_memset.
  • Etsuro Fujita sent in another revision of a patch to show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan.
  • David Rowley sent in a patch to fix an issue where the diff options on Windows would wrongly hide whitespace changes deemed important.
  • David Rowley sent in a patch to prevent a possible overflow in work_mem calculations.
  • Tom Lane sent in a patch to generalize OR-clause extraction for foreign tables.
  • Alexander Korotkov sent in two more revisions of a patch to implement partial sorting.
  • Peter Geoghegan sent in another revision of a patch to implement INSERT...ON DUPLICATE KEY LOCK FOR UPDATE.

par N Bougain le mercredi 1 janvier 2014 à 17h35

dimanche 22 septembre 2013

Guillaume Lelarge

Manuels de PostgreSQL en PDF

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

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

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

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

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

par Guillaume Lelarge le dimanche 22 septembre 2013 à 21h57

mardi 2 juillet 2013

Guillaume Lelarge

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

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

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

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

par Guillaume Lelarge le mardi 2 juillet 2013 à 21h08

mercredi 26 juin 2013

Sébastien Lardière

Suivi de l'activité dans PostgreSQL 9.2

Jumelles

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

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

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

lundi 24 juin 2013

Guillaume Lelarge

Petit compte-rendu sur le pgday.fr 2013

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

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

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

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

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

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

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

par Guillaume Lelarge le lundi 24 juin 2013 à 20h09

mercredi 5 juin 2013

Nicolas Thauvin

pitrery : Faciliter la restauration PITR de PostgreSQL

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

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

La version 1.3 apporte des nouveauté intéressantes :

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

La documentation est à jour sur https://github.com/dalibo/pitrery/w...

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

Les versions sont disponinbles sur https://dl.dalibo.com/public/pitrer...

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

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

par Orgrim le mercredi 5 juin 2013 à 08h38

lundi 20 mai 2013

Guillaume Lelarge

Traduction de la documentation de PostgreSQL 9.3

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

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

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

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

par Guillaume Lelarge le lundi 20 mai 2013 à 17h01

lundi 13 mai 2013

ulhume

EXT3/4 et performances

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

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

EXT4 et la barrière d'écriture

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

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

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

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

Et maintenant Ext3 aussi...

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

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

Conclusion

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

par Yoran le lundi 13 mai 2013 à 10h19

mercredi 8 mai 2013

Guillaume Lelarge

Nouvelles versions mineures de la documentation

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

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

par Guillaume Lelarge le mercredi 8 mai 2013 à 14h57

samedi 6 avril 2013

Nicolas Thauvin

Oups : des tablespaces imbriqués

Imbriquer des tablespaces n'a pas vraiment de sens dans PostgreSQL surtout si on veut se prendre la tête avec des montages dans tous les sens... Mais bon c'est permis, car PostgreSQL utilise uniquement les liens symboliques dans $PGDATA/pg_tblspc pour accéder au contenu des tablespaces.

Si on veut savoir ce qu'il en est voici une requête qui montre ça.

Pour PostgreSQL <= 9.1 :

SELECT f.oid, f.spcname AS name, f.spclocation AS location,
  f.spclocation ~ ('^' || (SELECT setting FROM pg_settings
                           WHERE name = 'data_directory')) AS inside_pgdata,
  (SELECT o.spcname
   FROM pg_tablespace o
   WHERE f.spclocation ~ ('^' || o.spclocation || '/')
     AND o.spcname NOT IN ('pg_default', 'pg_global')
   ORDER BY o.spclocation DESC LIMIT 1) AS parent,
  (SELECT o.spclocation
   FROM pg_tablespace o
   WHERE f.spclocation ~ ('^' || o.spclocation || '/')
     AND o.spcname NOT IN ('pg_default', 'pg_global')
   ORDER BY o.spclocation DESC LIMIT 1) AS parent_location
FROM pg_tablespace f
WHERE f.spcname NOT IN ('pg_default', 'pg_global');

Pour PostgreSQL >= 9.2, la colonne spcname de pg_tablespace a été remplacée par la fonction pg_tablespace_location() :

SELECT f.oid, f.spcname AS name, pg_tablespace_location(f.oid) AS location,
  pg_tablespace_location(f.oid) ~ ('^' || (SELECT setting FROM pg_settings
                                    WHERE name = 'data_directory')) AS inside_pgdata,
  (SELECT o.spcname
   FROM pg_tablespace o
   WHERE pg_tablespace_location(f.oid) ~ ('^' || pg_tablespace_location(o.oid) || '/')
     AND o.spcname NOT IN ('pg_default', 'pg_global')
   ORDER BY pg_tablespace_location(o.oid) DESC LIMIT 1) AS parent,
  (SELECT pg_tablespace_location(o.oid)
   FROM pg_tablespace o
   WHERE pg_tablespace_location(f.oid) ~ ('^' || pg_tablespace_location(o.oid) || '/')
     AND o.spcname NOT IN ('pg_default', 'pg_global')
   ORDER BY pg_tablespace_location(o.oid) DESC LIMIT 1) AS parent_location
FROM pg_tablespace f
WHERE f.spcname NOT IN ('pg_default', 'pg_global');

On obtient par exemple :

  oid  |   name   |                       location                       | inside_pgdata | parent  |                 parent_location                 
-------+----------+------------------------------------------------------+---------------+---------+-------------------------------------------------
 16399 | inside   | /home/pgsql/postgresql-9.0.10/data/inside            | t             |         | 
 16400 | outside  | /home/pgsql/postgresql-9.0.10/outside                | f             |         | 
 16414 | imbrique | /home/pgsql/postgresql-9.0.10/outside/imbrique       | f             | outside | /home/pgsql/postgresql-9.0.10/outside
 16415 | rimb     | /home/pgsql/postgresql-9.0.10/outside/rimb           | f             | outside | /home/pgsql/postgresql-9.0.10/outside
 16416 | rimbimb  | /home/pgsql/postgresql-9.0.10/outside/rimb/imb       | f             | rimb    | /home/pgsql/postgresql-9.0.10/outside/rimb
 16417 | deuimb   | /home/pgsql/postgresql-9.0.10/outside/rimb/truc/2imb | f             | truc    | /home/pgsql/postgresql-9.0.10/outside/rimb/truc
 16418 | truc     | /home/pgsql/postgresql-9.0.10/outside/rimb/truc      | f             | rimb    | /home/pgsql/postgresql-9.0.10/outside/rimb
(7 rows)

Pour bien gérer ses tablespaces, l'idéal est de faire un répertoire dédié au même niveau que $PGDATA, et de créer les tablespaces dedans, avec un tablespace par répertoire sur un seul niveau :

 /home/pgsql/postgresql-9.2.4
├── data
└── tblspc
    ├── tbs_1
    ├── tbs_2
    ├── ...
    └── tbs_n

C'est plus propre et on voit tout de suite l'utilisation disque avec df : parce qu'on met des disques différents derrière, bien entendu... :)

par Orgrim le samedi 6 avril 2013 à 07h10

mercredi 3 avril 2013

Christophe Chauvet

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

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

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

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

Clé de signature des paquets

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

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

source.list

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

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

Remplacer Squeeze par le nom de votre distribution.

Préférences

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

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

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

Initialisation

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

apt-get update
apt-get install pgdg-keyring

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

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

par Christophe Chauvet le mercredi 3 avril 2013 à 06h05

mardi 12 mars 2013

Damien Clochard

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

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

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

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

http://pgday.fr/pgday2013:appel_a_orateurs

par damien le mardi 12 mars 2013 à 22h08

samedi 9 mars 2013

Philippe Beaudoin

1 500 milliards de requêtes SQL !

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

par philippe beaudoin le samedi 9 mars 2013 à 16h26

lundi 25 février 2013

Thomas Reiss

Les bases de données relationnelles avec PHP

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

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

Pour vous inscrire, ça se passe ici :

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

mardi 8 janvier 2013

Dimitri Fontaine (2nd Quadrant)

PostgreSQL 2013

Bonne Année 2013 à tous !

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

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

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

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

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

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

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

par Dimitri Fontaine le mardi 8 janvier 2013 à 11h05

jeudi 3 janvier 2013

Damien Clochard

Refonte de pgBadger en HTML5 : premier aperçu

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

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

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

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

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

pgbadger_proto_1.png

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

pgbadger_proto_2.png

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

pgbadger_proto_3.png

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

pgbadger_proto_4.png

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

pgbadger_proto_5.png

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

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

par damien le jeudi 3 janvier 2013 à 22h06