Nouvelles hebdomadaires de PostgreSQL - 15 avril 2012
La seconde réunion du PUG de l'Arizona est programmée pour le mercredi 25 avril à 18h30 et débutera avec pizzas et rafraichissements. Vous pouvez rejoindre la mailing-list, et RSVP à : https://www.bigtent.com/groups/azpug
Les nouveautés des produits dérivés
- EnterpriseDB Postgres Enterprise Manager v2.1 : http://www.enterprisedb.com/download-postgres-enterprise-manager
- MyJSQLView Version 3.34 : http://dandymadeproductions.com/projects/MyJSQLView/
- Postgres-XC 1.0beta1, un système de réplication multi-maîtres synchrones basé sur PostgreSQL : http://postgres-xc.sourceforge.net/
Offres d'emplois autour de PostgreSQL en avril
- Internationales : http://archives.postgresql.org/pgsql-jobs/2012-04/threads.php;
- Francophones : http://forums.postgresql.fr/viewforum.php?id=4.
- Réunion du groupe londonien, le 17 avril 2012 : http://www.meetup.com/London-PostgreSQL-Meetup-Group/
- La PGCon 2012 sera tenue à l'Université d'Ottawa, les 17 et 18 mai 2012. Elle sera précédée par deux jours de tutoriels les 15 & 16 mai 2012 : http://www.pgcon.org/2012/
- Le PGDay France aura lieu à Lyon, le 7 juin 2012 : http://www.pgday.fr
PostgreSQL dans les média
- Planet PostgreSQL : http://planet.postgresql.org/
- Planet PostgreSQLFr : http://planete.postgresql.fr/
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.
Revues de code
Heikki Linnakangas a poussé :
- Remove link to ODBCng project from the docs. Thom Browne pointed out that the URL was out of date, and Devrim GÜNDÜZ pointed out that the project isn't maintained anymore. http://git.postgresql.org/pg/commitdiff/140a4fbf1a87891a79a2c61a08416828d39f286a
- Fix typos in docs, some words were doubled. Thom Brown http://git.postgresql.org/pg/commitdiff/fb9bc5d9fb66a6065bd1c3f71a4b5bae5af89c90
- Point the URL to PL/py directly to the page about the procedural language. It used to point to a top-level page that contains client-side tools as well. It was hard to find the procedural language there. http://git.postgresql.org/pg/commitdiff/442da68f73e679ae8a799c249e3bf1fc1a3ee28d
Tom Lane a poussé :
- Save a few cycles while creating "sticky" entries in pg_stat_statements. There's no need to sit there and increment the stats when we know all the increments would be zero anyway. The actual additions might not be very expensive, but skipping acquisition of the spinlock seems like a good thing. Pushing the logic about initialization of the usage count down into entry_alloc() allows us to do that while making the code actually simpler, not more complex. Expansion on a suggestion by Peter Geoghegan. http://git.postgresql.org/pg/commitdiff/e969f9a78008d6a09abf8646f1338e2dff447cbf
- Don't bother copying empty support arrays in a zero-column MergeJoin. The case could not arise when this code was originally written, but it can now (since we made zero-column MergeJoins work for the benefit of FULL JOIN ON TRUE). I don't think there is any actual bug here, but we might as well treat it consistently with other uses of COPY_POINTER_FIELD(). Per comment from Ashutosh Bapat. http://git.postgresql.org/pg/commitdiff/d515365a611a58241019c59a62b0cb79584aa725
- Fix an Assert that turns out to be reachable after all. estimate_num_groups() gets unhappy with create table empty(); select * from empty except select * from empty e2; I can't see any actual use-case for such a query (and the table is illegal per SQL spec), but it seems like a good idea that it not cause an assert failure. http://git.postgresql.org/pg/commitdiff/65fd91333e45114c5d9a07d3d4f6a4786df48768
- Adjust various references to GEQO being non-deterministic. It's still non-deterministic in some sense ... but given fixed settings and identical planning problems, it will now always choose the same plan, so we probably shouldn't tar it with that brush. Per bug #6565 from Guillaume Cottenceau. Back-patch to 9.0 where the behavior was fixed. http://git.postgresql.org/pg/commitdiff/c94b43ce39f3ee48db04fb8284de0db12526af92
- Measure epoch of timestamp-without-time-zone from local not UTC midnight. This patch reverts commit 191ef2b407f065544ceed5700e42400857d9270f and thereby restores the pre-7.3 behavior of EXTRACT(EPOCH FROM timestamp-without-tz). Per discussion, the more recent behavior was misguided on a couple of grounds: it makes it hard to get a non-timezone-aware epoch value for a timestamp, and it makes this one case dependent on the value of the timezone GUC, which is incompatible with having timestamp_part() labeled as immutable. The other behavior is still available (in all releases) by explicitly casting the timestamp to timestamp with time zone before applying EXTRACT. This will need to be called out as an incompatible change in the 9.2 release notes. Although having mutable behavior in a function marked immutable is clearly a bug, we're not going to back-patch such a change. http://git.postgresql.org/pg/commitdiff/0d9819f7e3be3c4186f0398d007d2fef8809da6a
- Make pg_tablespace_location(0) return the database's default tablespace. This definition is convenient when applying the function to the reltablespace column of pg_class, since that's what zero means there; and it doesn't interfere with any other plausible use of the function. Per gripe from Bruce Momjian. http://git.postgresql.org/pg/commitdiff/3769fa5fc64298143cf535c8a06921793598e458
- Silently ignore any nonexistent schemas that are listed in search_path. Previously we attempted to throw an error or at least warning for missing schemas, but this was done inconsistently because of implementation restrictions (in many cases, GUC settings are applied outside transactions so that we can't do system catalog lookups). Furthermore, there were exceptions to the rule even in the beginning, and we'd been poking more and more holes in it as time went on, because it turns out that there are lots of use-cases for having some irrelevant items in a common search_path value. It seems better to just adopt a philosophy similar to what's always been done with Unix PATH settings, wherein nonexistent or unreadable directories are silently ignored. This commit also fixes the documentation to point out that schemas for which the user lacks USAGE privilege are silently ignored. That's always been true but was previously not documented. This is mostly in response to Robert Haas' complaint that 9.1 started to throw errors or warnings for missing schemas in cases where prior releases had not. We won't adopt such a significant behavioral change in a back branch, so something different will be needed in 9.1. http://git.postgresql.org/pg/commitdiff/880bfc3287dd68cfe90d10d9597d7b0fd2dae3e5
- Fix cost estimation for indexscan filter conditions. cost_index's method for estimating per-tuple costs of evaluating filter conditions (a/k/a qpquals) was completely wrong in the presence of derived indexable conditions, such as range conditions derived from a LIKE clause. This was largely masked in common cases as a result of all simple operator clauses having about the same costs, but it could show up in a big way when dealing with functional indexes containing expensive functions, as seen for example in bug #6579 from Istvan Endredy. Rejigger the calculation to give sane answers when the indexquals aren't a subset of the baserestrictinfo list. As a side benefit, we now do the calculation properly for cases involving join clauses (ie, parameterized indexscans), which we always overestimated before. There are still cases where this is an oversimplification, such as clauses that can be dropped because they are implied by a partial index's predicate. But we've never accounted for that in cost estimates before, and I'm not convinced it's worth the cycles to try to do so. http://git.postgresql.org/pg/commitdiff/732bfa2448c77bba5d98f485a387da77e1df8395
- Assorted spelling corrections. Thom Brown http://git.postgresql.org/pg/commitdiff/81e3e4fd14f82e93edfda8621727040ab3c70840
- Remove the "last ditch" code path in join_search_one_level(). So far as I can tell, it is no longer possible for this heuristic to do anything useful, because the new weaker definition of have_relevant_joinclause means that any relation with a joinclause must be considered joinable to at least one other relation. It would still be possible for the code block to be entered, for example if there are join order restrictions that prevent any join of the current level from being formed; but in that case it's just a waste of cycles to attempt to form cartesian joins, since the restrictions will still apply. Furthermore, IMO the existence of this code path can mask bugs elsewhere; we would have noticed the problem with cartesian joins a lot sooner if this code hadn't compensated for it in the simplest case. Accordingly, let's remove it and see what happens. I'm committing this separately from the prerequisite changes in have_relevant_joinclause, just to make the question easier to revisit if there is some fault in my logic. http://git.postgresql.org/pg/commitdiff/e54b10a62db2991235fe800c629baef4531a6d67
- Weaken the planner's tests for relevant joinclauses. We should be willing to cross-join two small relations if that allows us to use an inner indexscan on a large relation (that is, the potential indexqual for the large table requires both smaller relations). This worked in simple cases but fell apart as soon as there was a join clause to a fourth relation, because the existence of any two-relation join clause caused the planner to not consider clauseless joins between other base relations. The added regression test shows an example case adapted from a recent complaint from Benoit Delbosc. Adjust have_relevant_joinclause, have_relevant_eclass_joinclause, and has_relevant_eclass_joinclause to consider that a join clause mentioning three or more relations is sufficient grounds for joining any subset of those relations, even if we have to do so via a cartesian join. Since such clauses are relatively uncommon, this shouldn't affect planning speed on typical queries; in fact it should help a bit, because the latter two functions in particular get significantly simpler. Although this is arguably a bug fix, I'm not going to risk back-patching it, since it might have currently-unforeseen consequences. http://git.postgresql.org/pg/commitdiff/e3ffd05b02468b1a53de31a322cedf195576a625
Bruce Momjian a poussé :
- Remove documentation mention of pglesslog, which was added in 2009 because there was only a beta for 9.0 and it does not compile on 9.1. http://git.postgresql.org/pg/commitdiff/8213624fc95574c9bdd60cadd10ccd180df95217
- Update documentation to more clearly label the streaming replication option. http://git.postgresql.org/pg/commitdiff/75fcb935bc162b466eadac95e6eec6a0c415d18e
- Re-add documentation recommendation to use gzip/gunzip for archive file storage. http://git.postgresql.org/pg/commitdiff/17665f61155babff93ff916f1d080dcc9b5397d3
- Fix pg_upgrade to properly upgrade a table that is stored in the cluster default tablespace, but part of a database that is in a user-defined tablespace. Caused "file not found" error during upgrade. Per bug report from Ants Aasma. Backpatch to 9.1 and 9.0. http://git.postgresql.org/pg/commitdiff/38458e45736efeb0d82739305c77c529c9cf0e1e
Peter Eisentraut a poussé :
- psql: Improve tab completion of WITH. Only match when WITH is the first word, as WITH may appear in many other contexts. Josh Kupershmidt http://git.postgresql.org/pg/commitdiff/6b8c99c386d318b434829ac0c9ba935fb5e53d18
- NLS: Initialize Project-Id-Version field by xgettext. Since xgettext provides options to do this now, we might as well use them. http://git.postgresql.org/pg/commitdiff/eb821b91c86a85a37572c9062902f94a5efba528
- Consistently quote encoding and locale names in messages http://git.postgresql.org/pg/commitdiff/64e1309c76aca35e32e62e69fc11e96aadfb2615
- Rename bytea_agg to string_agg and add delimiter argument. Per mailing list discussion, we would like to keep the bytea functions parallel to the text functions, so rename bytea_agg to string_agg, which already exists for text. Also, to satisfy the rule that we don't want aggregate functions of the same name with a different number of arguments, add a delimiter argument, just like string_agg for text already has. http://git.postgresql.org/pg/commitdiff/c0cc526e8b1e821dfced692a68e4c8978c2bdbc1
- Fix typo. Etsuro Fujita http://git.postgresql.org/pg/commitdiff/f3a3aac8452c0af53d510b4f042ff5ee939a1799
- Document that VALUES can be used in WITH http://git.postgresql.org/pg/commitdiff/aacc50d1758632550bebc4911857b323d65f0996
- Update contrib/README. Remove lots of outdated information that is duplicated by the better-maintained SGML documentation. In particular, remove the outdated listing of contrib modules. Update the installation instructions to mention CREATE EXTENSION, but don't go into too much detail. http://git.postgresql.org/pg/commitdiff/48ea558313fae4409d0e76facde041674678cba2
- PL/Python: Fix crash when colnames() etc. called without result set. The result object methods colnames() etc. would crash when called after a command that did not produce a result set. Now they throw an exception. discovery and initial patch by Jean-Baptiste Quenot http://git.postgresql.org/pg/commitdiff/c03523ed3fc65e219068aff536330ce451f63ca7
Alvaro Herrera a poussé :
- Accept postgres:// URIs in libpq connection functions. postgres:// URIs are an attempt to "stop the bleeding" in this general area that has been said to occur due to external projects adopting their own syntaxes. The syntaxes supported by this patch: postgres://[user[:pwd]@][unix-socket][:port[/dbname]][?param1=value1&...] postgres://[user[:pwd]@][net-location][:port][/dbname][?param1=value1&...] should be enough to cover most interesting cases without having to resort to "param=value" pairs, but those are provided for the cases that need them regardless. libpq documentation has been shuffled around a bit, to avoid stuffing all the format details into the PQconnectdbParams description, which was already a bit overwhelming. The list of keywords has moved to its own subsection, and the details on the URI format live in another subsection. This includes a simple test program, as requested in discussion, to ensure that interesting corner cases continue to work appropriately in the future. Author: Alexander Shulgin Some tweaking by Álvaro Herrera, Greg Smith, Daniel Farina, Peter Eisentraut Reviewed by Robert Haas, Alexey Klyukin (offlist), Heikki Linnakangas,Marko Kreen, and others Oh, it also supports postgresql:// but that's probably just an accident. http://git.postgresql.org/pg/commitdiff/b035cb9db7aa7c0f28581b23feb10d3c559701f6
Robert Haas a poussé :
- Update lazy_scan_heap header comment. The previous comment described how things worked in PostgreSQL 8.2 and prior. http://git.postgresql.org/pg/commitdiff/5630eddf1efe7c374fe8e8bdeaba3c70f8cd628b
- Fix typo in comment. http://git.postgresql.org/pg/commitdiff/61167bfaf2af3bc0bbf3ba9c8ff14f10a483f9ff
- Grammar corrections. Christoph Berg http://git.postgresql.org/pg/commitdiff/7167fcd21bc05e9114b1ededb835c5bc8610a6b0
- Add Peter Geoghegan as additional author of pg_stat_statements. http://git.postgresql.org/pg/commitdiff/cd93425cd1e9b66ad99cfee819c928b202b63ee6
- pg_size_pretty(numeric). The output of the new pg_xlog_location_diff function is of type numeric, since it could theoretically overflow an int8 due to signedness; this provides a convenient way to format such values. Fujii Masao, with some beautification by me. http://git.postgresql.org/pg/commitdiff/4a2d7ad76f5f275ef2d6a57e1a61d5bf756349e8
- Rename synchronous_commit='write' to 'remote_write'. Fujii Masao, per discussion on pgsql-hackers http://git.postgresql.org/pg/commitdiff/ea6a2d8d47b9368f50fb7f4cca206895e6127a0a
Tatsuo Ishii a poussé :
- Add description of long options for '-c', '-D', '-l' and '-s'. Per discussion of hackers list on 2012/3/10 "missing description initdb manual". http://git.postgresql.org/pg/commitdiff/d4db16d303b24ba5c9a6ddf8c2d70f6fd2da36d7
- Add missing descriptions about '--timeout' and '--mode' to help message. They are already implemented in the source code. Suggestions about the message formatting from Tom Lane. http://git.postgresql.org/pg/commitdiff/4efbb7d04f3481da1aaf75630c1203203f400b66
Correctifs rejetés (à ce jour)
- Etsuro Fujita's patch to fix a typo in a comment.
Correctifs en attente
- Vikash S. sent in a patch intended to allow psql to take multiple -f (file) arguments on the command line.
- Robert Haas sent in another revision of the patch implementing pg_prewarm.
- Robert Haas sent in another revision of a patch to fix a bug in fastpath locking.
- Zoltan Boszormenyi sent in another revision of the patch for ECPG FETCH readahead.
- Josh Kupershmidt sent in another revision of the patch to fix psql tab completion in the WITH case of CREATE/ALTER USER/ROLE.
- Zoltan Boszormenyi sent in new versions of two patches: timeout framework and lock timeout.
- Nikhil Sontakke sent in a patch implementing CHECK ONLY in CREATE/ALTER TABLE.
- Heikki Linnakangas sent in a patch to allow compiling PL/pgsql-related items as EXTENSIONs.
- Robert Haas sent in several patches to test different scenarios for memory usage during sorting.
- Etsuro Fujita sent in a patch to add some validating machinery to FDWs and used same to check NULLness in the file_fdw.
- Heikki Linnakangas sent in a patch to ensure that the right domain is used in errcontext() calls.
- Tatsuo Ishii sent in a patch ensuring that pg_ctl --help actually documents all options the code makes available.