== PostgreSQL Weekly News - January 05 2014 == Happy New Year from the PostgreSQL Weekly News!
== PostgreSQL Jobs for January == http://archives.postgresql.org/pgsql-jobs/2014-01/threads.php == PostgreSQL Local == FOSDEM PGDay, a one day conference held before FOSDEM in Brussels, Belgium, will be on Jan 31st, 2014. Details: http://fosdem2014.pgconf.eu/ http://fosdem2014.pgconf.eu/registration/ The 7th annual "Prague PostgreSQL Developers Day" (P2D2) conference organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be held on February 6, 2014 at Faculty of Mathematics and Physics, Charles University (Malostranske namesti 25, Prague). Czech language info below. The CfP will be open through 2014/01/03. http://www.p2d2.cz/ The CfP for PGConf NYC 2014 is open through January 10, 2014. Notifications will go out on January 15, 2014. http://nyc.pgconf.us/2014/ The CfP for PGCon 2014 is open through January 19, 2014. http://www.pgcon.org/2014/ Nordic PGDay 2014 will be held in stockholm on March 20, 2014. http://www.nordicpgday.org/ == PostgreSQL in the News == Planet PostgreSQL: http://planet.postgresql.org/ PostgreSQL Weekly News is brought to you this week by David Fetter Submit news and announcements by Sunday at 3:00pm Pacific time. Please send English language ones to da...@fetter.org, German language to p...@pgug.de, Italian language to p...@itpug.org. Spanish language to p...@arpug.com.ar. == Applied Patches == Tom Lane pushed: - 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 pushed: - Do not use an empty hostname. When trying to connect to a given database libecpg should not try using an empty hostname if no hostname was given. http://git.postgresql.org/pg/commitdiff/7c957ec83eeefa8b6d3179f3af0a5dc338d2252b Robert Haas pushed: - Rename walLogHints to wal_log_hints for easier grepping. Michael Paquier http://git.postgresql.org/pg/commitdiff/4b351841fa0b90cb72f5f63c56f06e4511850ac4 - Aggressively freeze tables when CLUSTER or VACUUM FULL rewrites them. We haven't wanted to do this in the past on the grounds that in rare cases the original xmin value will be needed for forensic purposes, but commit 37484ad2aacef5ec794f4dd3d5cf814475180a78 removes that objection, so now we can. Per extensive discussion, among many people, on pgsql-hackers. http://git.postgresql.org/pg/commitdiff/3cff1879f8d03cb729368722ca823a4bf74c0cac Alvaro Herrera pushed: - 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 == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == 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. -- Sent via pgsql-announce mailing list (pgsql-announce@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-announce