== PostgreSQL Weekly News - November 17 2013 ==
От | David Fetter |
---|---|
Тема | == PostgreSQL Weekly News - November 17 2013 == |
Дата | |
Msg-id | 20131118064159.GA10257@fetter.org обсуждение исходный текст |
Список | pgsql-announce |
== PostgreSQL Weekly News - November 17 2013 == The third commitfest of the 9.4 cycle has begun. Review! https://commitfest.postgresql.org/action/commitfest_view?id=20 FOSDEM PGDay, a one day conference held before FOSDEM in Brussels, Belgium, will be on Jan 31st, 2014. Details: http://fosdem2014.pgconf.eu/ CfP is open: http://fosdem2014.pgconf.eu/callforpapers/ PostgreSQL user groups have formed in Singapore and Toronto. == PostgreSQL Product News == MJSQLView Version 3.47, a Java-based UI which supports PostgreSQL, released. http://dandymadeproductions.com/projects/MyJSQLView/ == PostgreSQL Jobs for November == http://archives.postgresql.org/pgsql-jobs/2013-11/threads.php == PostgreSQL Local == PGDay Cuba will be November 28-29, 2013. http://postgresql.uci.cu/?page_id=474 == 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 david@fetter.org, German language to pwn@pgug.de, Italian language to pwn@itpug.org. Spanish language to pwn@arpug.com.ar. == Applied Patches == Magnus Hagander pushed: - Don't abort pg_basebackup when receiving empty WAL block. This is a similar fix as c6ec8793aa59d1842082e14b4b4aae7d4bd883fd 9.2. This should never happen in 9.3 and newer since the special case cannot happen there, but this patch synchronizes up the code so there is no confusion on why they're different. An empty block is as harmless in 9.3 as it was in 9.2, and can safely be ignored. http://git.postgresql.org/pg/commitdiff/705556a631c5908cd3caa0b973be13d994ff63e7 - Fix doc links in README file to work with new website layout. Per report from Colin 't Hart http://git.postgresql.org/pg/commitdiff/f1d6875916055914920f657ad88011c5b525f220 Tom Lane pushed: - Re-allow duplicate aliases within aliased JOINs. Although the SQL spec forbids duplicate table aliases, historically we've allowed queries like SELECT ... FROM tab1 x CROSS JOIN (tab2 x CROSS JOIN tab3 y) z on the grounds that the aliased join (z) hides the aliases within it, therefore there is no conflict between the two RTEs named "x". The LATERAL patch broke this, on the misguided basis that "x" could be ambiguous if tab3 were a LATERAL subquery. To avoid breaking existing queries, it's better to allow this situation and complain only if tab3 actually does contain an ambiguous reference. We need only remove the check that was throwing an error, because the column lookup code is already prepared to handle ambiguous references. Per bug #8444. http://git.postgresql.org/pg/commitdiff/648bd05b13b3624e494ae2996c2d2e0241cefe87 - Fix ruleutils pretty-printing to not generate trailing whitespace. The pretty-printing logic in ruleutils.c operates by inserting a newline and some indentation whitespace into strings that are already valid SQL. This naturally results in leaving some trailing whitespace before the newline in many cases; which can be annoying when processing the output with other tools, as complained of by Joe Abbate. We can fix that in a pretty localized fashion by deleting any trailing whitespace before we append a pretty-printing newline. In addition, we have to modify the code inserted by commit 2f582f76b1945929ff07116cd4639747ce9bb8a1 so that we also delete trailing whitespace when transposing items from temporary buffers into the main result string, when a temporary item starts with a newline. This results in rather voluminous changes to the regression test results, but it's easily verified that they are only removal of trailing whitespace. Back-patch to 9.3, because the aforementioned commit resulted in many more cases of trailing whitespace than had occurred in earlier branches. http://git.postgresql.org/pg/commitdiff/0b7e660d6c70f45e06f1b52f255116bcb44624eb - Fix failure with whole-row reference to a subquery. Simple oversight in commit 1cb108efb0e60d87e4adec38e7636b6e8efbeb57 --- recursively examining a subquery output column is only sane if the original Var refers to a single output column. Found by Kevin Grittner. http://git.postgresql.org/pg/commitdiff/ebefbb5fdeba885bb57d2f2e1eb185a46c9d777d - Clarify CREATE FUNCTION documentation about handling of typmods. The previous text was a bit misleading, as well as unnecessarily vague about what information would be discarded. Per gripe from Craig Skinner. http://git.postgresql.org/pg/commitdiff/5d924f067c0cc0b15709bdf604e6ac5f33158eb6 - Fix incorrect column name in psql \d code. pg_index.indisreplident had at one time in its development been called indisidentity. describe.c got missed when it was renamed. Bug introduced in commit 07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65. Andres Freund http://git.postgresql.org/pg/commitdiff/e694cf25d787354ed04310a14aa508692874dcad - Add a regression test case for \d on an index. Previous commit shows the need for this. The coverage isn't really thorough, but it's better than nothing. http://git.postgresql.org/pg/commitdiff/982b82d6b1fd007b9357ce198d13a55544888534 - Minor comment corrections for sequence hashtable patch. There were enough typos in the comments to annoy me ... http://git.postgresql.org/pg/commitdiff/80e3a470ba46bc35fb1ec22d4e97126270f3d2b3 - Prevent leakage of cached plans and execution trees in plpgsql DO blocks. plpgsql likes to cache query plans and simple-expression execution state trees across calls. This is a considerable win for multiple executions of the same function. However, it's useless for DO blocks, since by definition those are executed only once and discarded. Nonetheless, we were allowing a DO block's expression execution trees to survive until end of transaction, resulting in a significant intra-transaction memory leak, as reported by Yeb Havinga. Worse, if the DO block exited with an error, the compiled form of the block's code was leaked till end of session --- along with subsidiary plancache entries. To fix, make DO blocks keep their expression execution trees in a private EState that's deleted at exit from the block, and add a PG_TRY block to plpgsql_inline_handler to make sure that memory cleanup happens even on error exits. Also add a regression test covering error handling in a DO block, because my first try at this broke that. (The test is not meant to prove that we don't leak memory anymore, though it could be used for that with a much larger loop count.) Ideally we'd back-patch this into all versions supporting DO blocks; but the patch needs to add a field to struct PLpgSQL_execstate, and that would break ABI compatibility for third-party plugins such as the plpgsql debugger. Given the small number of complaints so far, fixing this in HEAD only seems like an acceptable choice. http://git.postgresql.org/pg/commitdiff/c7b849a89645212121da480091f87d11fac82495 - Compute correct em_nullable_relids in get_eclass_for_sort_expr(). Bug #8591 from Claudio Freire demonstrates that get_eclass_for_sort_expr must be able to compute valid em_nullable_relids for any new equivalence class members it creates. I'd worried about this in the commit message for db9f0e1d9a4a0842c814a464cdc9758c3f20b96c, but claimed that it wasn't a problem because multi-member ECs should already exist when it runs. That is transparently wrong, though, because this function is also called by initialize_mergeclause_eclasses, which runs during deconstruct_jointree. The example given in the bug report (which the new regression test item is based upon) fails because the COALESCE() expression is first seen by initialize_mergeclause_eclasses rather than process_equivalence. Fixing this requires passing the appropriate nullable_relids set to get_eclass_for_sort_expr, and it requires new code to compute that set for top-level expressions such as ORDER BY, GROUP BY, etc. We store the top-level nullable_relids in a new field in PlannerInfo to avoid computing it many times. In the back branches, I've added the new field at the end of the struct to minimize ABI breakage for planner plugins. There doesn't seem to be a good alternative to changing get_eclass_for_sort_expr's API signature, though. There probably aren't any third-party extensions calling that function directly; moreover, if there are, they probably need to think about what to pass for nullable_relids anyway. Back-patch to 9.2, like the previous patch in this area. http://git.postgresql.org/pg/commitdiff/f3b3b8d5be00ebcfade3644fca7a80e25fd88a70 - Clean up password prompting logic in streamutil.c. The previous coding was fairly unreadable and drew double-free warnings from clang. I believe the double free was actually not reachable, because PQconnectionNeedsPassword is coded to not return true if a password was provided, so that the loop can't iterate more than twice. Nonetheless it seems worth rewriting. No back-patch since this is just cosmetic. http://git.postgresql.org/pg/commitdiff/3172eea062e779db39df9a48fca0ad7448163f98 - Speed up printing of INSERT statements in pg_dump. In --inserts and especially --column-inserts mode, we can get a useful speedup by generating the common prefix of all a table's INSERT commands just once, and then printing the prebuilt string for each row. This avoids multiple invocations of fmtId() and other minor fooling around. David Rowley http://git.postgresql.org/pg/commitdiff/97e1ec467099f1e581f491c8a57b7d56d0b9c539 - Fix incorrect loop counts in tidbitmap.c. A couple of places that should have been iterating over WORDS_PER_CHUNK words were iterating over WORDS_PER_PAGE words instead. This thinko accidentally failed to fail, because (at least on common architectures with default BLCKSZ) WORDS_PER_CHUNK is a bit less than WORDS_PER_PAGE, and the extra words being looked at were always zero so nothing happened. Still, it's a bug waiting to happen if anybody ever fools with the parameters affecting TIDBitmap sizes, and it's a small waste of cycles too. So back-patch to all active branches. Etsuro Fujita http://git.postgresql.org/pg/commitdiff/f1f21b2d6fd170faf9824306ef4f4950c32ce49d - Remove pgbench's hardwired limit on line length in custom script files. pgbench formerly failed on lines longer than BUFSIZ, unexpectedly splitting them into multiple commands. Allow it to work with any length of input line. Sawada Masahiko http://git.postgresql.org/pg/commitdiff/61a07bae47886b8333b9cce882d73d5fdaaec618 - Allow aggregates to provide estimates of their transition state data size. Formerly the planner had a hard-wired rule of thumb for guessing the amount of space consumed by an aggregate function's transition state data. This estimate is critical to deciding whether it's OK to use hash aggregation, and in many situations the built-in estimate isn't very good. This patch adds a column to pg_aggregate wherein a per-aggregate estimate can be provided, overriding the planner's default, and infrastructure for setting the column via CREATE AGGREGATE. It may be that additional smarts will be required in future, perhaps even a per-aggregate estimation function. But this is already a step forward. This is extracted from a larger patch to improve the performance of numeric and int8 aggregates. I (tgl) thought it was worth reviewing and committing this infrastructure separately. In this commit, all built-in aggregates are given aggtransspace = 0, so no behavior should change. Hadi Moshayedi, reviewed by Pavel Stehule and Tomas Vondra http://git.postgresql.org/pg/commitdiff/6cb86143e8e1e855255edc706bce71c6ebfd9a6c - Improve performance of numeric sum(), avg(), stddev(), variance(), etc. This patch improves performance of most built-in aggregates that formerly used a NUMERIC or NUMERIC array as their transition type; this includes not only aggregates on numeric inputs, but some aggregates on integer inputs where overflow of an int8 value is a possibility. The code now uses a special-purpose data structure to avoid array construction and deconstruction overhead, as well as packing and unpacking overhead for numeric values. These aggregates' transition type is now declared as INTERNAL, since it doesn't correspond to any SQL data type. To keep the planner from thinking that that means a lot of storage will be used, we make use of the just-added pg_aggregate.aggtransspace feature. The space estimate is set to 128 bytes, which is at least in the right ballpark. Hadi Moshayedi, reviewed by Pavel Stehule and Tomas Vondra http://git.postgresql.org/pg/commitdiff/69c8fbac201652282e18b0e2e301d4ada991fbde - Add make_date() and make_time() functions. Pavel Stehule, reviewed by Jeevan Chalke and Atri Sharma http://git.postgresql.org/pg/commitdiff/f901bb50e33ad95593bb68f7b3b55eb2e47607dc Peter Eisentraut pushed: - Remove leftovers of IRIX port. This removes the remaining pieces of the IRIX port that was removed by ea91a6be89575095f61ebf36d67c2df98be093db. http://git.postgresql.org/pg/commitdiff/3626adf26652cdf76a26ae14b2d75cb7801da7a9 - gitattributes: Make syntax compatible with older Git versions. Avoid the use of **, which was only introduced in Git version 1.8.2. http://git.postgresql.org/pg/commitdiff/c0764a542530c7ebf8abd2766d256d49d27e90e8 - Move variable closer to where it is used. This avoids an unused variable warning on Windows when building without asserts From: David Rowley <dgrowleyml@gmail.com> http://git.postgresql.org/pg/commitdiff/aa04b323c36931f96159ab1fd6f74f8401ab4c92 - Fix whitespace http://git.postgresql.org/pg/commitdiff/87d8378f60cef2206b0d4a09eb773ed416fa58fc - pg_upgrade: Fix some whitespace oddities http://git.postgresql.org/pg/commitdiff/55c3d86a2a374f9d8fd88fd947601c1f49a4da08 Robert Haas pushed: - doc: Fix typo. Reported by Thom Brown. http://git.postgresql.org/pg/commitdiff/9cab81b5721b1a64c8e31feebbc0ae5efd6ddf71 - Try again to make pg_isolation_regress work its build directory. We can't search for the isolationtester binary until after we've set up the environment, because otherwise when find_other_exec() tries to invoke it with the -V option, it might fail for inability to locate a working libpq. So postpone that step. Andres Freund http://git.postgresql.org/pg/commitdiff/061b88c732952c59741374806e1e41c1ec845d50 - Fix relfilenodemap.c's handling of cache invalidations. The old code entered a new hash table entry first, then scanned pg_class to determine what value to fill in, and then populated the entry. This fails to work properly if a cache invalidation happens as a result of opening pg_class. Repair. Along the way, get rid of the idea of blowing away the entire hash table as a method of processing invalidations. Instead, just delete all the entries one by one. This is probably not quite as cheap but it's simpler, and shouldn't happen often. Andres Freund http://git.postgresql.org/pg/commitdiff/c46c803f8ad4ba80472b280703983ecf8021099e - doc: Restore proper alphabetical order. Colin 't Hart http://git.postgresql.org/pg/commitdiff/71dd54ada9c3d32dfc0eb082ff2023b12abe881a Heikki Linnakangas pushed: - Fix bug in GIN posting tree root creation. The root page is filled with as many items as fit, and the rest are inserted using normal insertions. However, I fumbled the variable names, and the code actually memcpy'd all the items on the page, overflowing the buffer. While at it, rename the variable to make the distinction more clear. Reported by Teodor Sigaev. This bug was introduced by my recent refactorings, so no backpatching required. http://git.postgresql.org/pg/commitdiff/07fca603b56e39c50478b44070fdfb38313cd51c - Use a hash table to store current sequence values. This speeds up nextval() and currval(), when you touch a lot of different sequences in the same backend. David Rowley http://git.postgresql.org/pg/commitdiff/21025d4a5322307bcfef1222f497a3aacb2fc79a - Fix bogus hash table creation. Andres Freund http://git.postgresql.org/pg/commitdiff/5cb719beeef2e871c9f0b22e799554bf801ac390 Kevin Grittner pushed: - Free ignorelist after each regression test schedule. It's a trivial amount of RAM held until the end of the regression test run; but it's probably worth fixing to silence future warnings from code analyzers. This was the only memory leak pointed out by clang's static code analysis tool. http://git.postgresql.org/pg/commitdiff/fe67d252337ef2aba3d4ea06a4aa87cdbc75b933 - Fix buffer overrun in isolation test program. Commit 061b88c732952c59741374806e1e41c1ec845d50 saved argv0 to a global buffer without ensuring that it was zero terminated, allowing references to it to overrun the buffer and access other memory. This probably would not have presented any security risk, but could have resulted in very confusing failures if the path to the executable was very long. Reported by David Rowley http://git.postgresql.org/pg/commitdiff/7cb964acb794078ef033cbf2e3a0e7670c8992a9 Bruce Momjian pushed: - docs: clarify MVCC introduction to allow for per-statement snapshots http://git.postgresql.org/pg/commitdiff/cd8115e0090cb04380292f880adcec9c2fc2e0f3 Andrew Dunstan pushed: - Fix isolation check for MSVC to handle recent changes. http://git.postgresql.org/pg/commitdiff/869b1e4a678cc6d78a64ed01682ddf8f8debd781 == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == Zoltan Boszormenyi sent in a patch to implement ECPG fetch readahead. Andres Freund sent in another flock of patches for logical changeset generation. Haribabu Kommi sent in another revision of a patch to fix an issue where tables can bloat under heavy load despite autovacuum. Dimitri Fontaine sent in another revision of a patch to implement EXTENSION TEMPLATEs. Pavel Stehule sent in another revision of a patch to implement DROP {TRIGGER|RULE} [IF EXISTS]. Mark Travis sent in a patch to help operate pgbench to large scales. Peter Eisentraut sent in a patch to split off mmfatal() from mmerror() in ECPG. Teodor Sigaev sent in a WIP patch to add nesting, types, and arrays to hstore. Kyotaro HORIGUCHI sent in another revision of a patch to allow ORDER BY to use unique indexes. Peter Eisentraut sent in another revision of a patch to add CREATE TRANSFORM and friends. Jaime Casanova sent in two more revisions of a patch to turn recovery.conf into GUCs. Kyotaro HORIGUCHI sent in another revision of a patch to enable UNION ALL on partitioned tables to use indexes. Colin 't Hart sent in another revision of a patch to clarify the differences between TABLE and SELECT * FROM. Heikki Linnakangas sent in a flock of patches to clean up GIN index handling. Robert Berry sent in a patch to print out GUC cost parameters in explain text output. Peter Eisentraut sent in a patch to report the exit codes from external recovery commands properly. Shigeru HANADA sent in another revision of a patch to allow foreign tables to inherit from local ones. David Rowley sent in a patch to speed up json out functions by changing a call to cstring_to_text to cstring_to_text_with_len using information already available. Mika Eloranta sent in two revisions of a patch to throttle pg_basebackup so it shows progress reports no more than once per second. Peter Eisentraut sent in a patch to fix an issue where pg_upgrade incorrectly reports errors in the case "disk full". Heikki Linnakangas sent in a patch to fix a race condition in B-tree page deletion. Amit Kapila sent in three more revisions of a patch to implement ALTER SYSTEM. Amit Kapila sent in another revision of a patch to allow starting a standalone backend with full FE/BE protocol enabled. SAKAMOTO Masahiko sent in two revisions of a patch to add a new wal_level 'all' which logs WAL when updating hint bits. Kyotaro HORIGUCHI sent in another revision of a patch to allow using indexes for UNION queries. Heikki Linnakangas sent in a patch to handle incomplete B-tree splits. Andres Freund sent in a patch to add replication node identifiers and crash-safe apply progress for LCRs. Marko Kreen sent in a patch to use a better default cypher suite for SSL. Mitsumasa KONDO sent in a patch to add min and max statement execution time to pg_stat_statements. Nigel Heron sent in another revision of a patch to add stats for network traffic. Peter Eisentraut sent in a PoC patch to implement ASSERTIONs in SQL. Alexander Korotkov sent in three more revisions of a patch to improve GIN by adding new information to the indexes. Simon Riggs sent in another revision of a patch to implement a sequence access method along with some code to exercise same. Michael Paquier sent in another revision of a patch to implement REINDEX CONCURRENTLY. Alexander Korotkov sent in another revision of a patch to enable fast scans on GIN indexes. Peter Geoghegan sent in two revisions of a patch to allow pg_stat_statements to store arbitrarily long query texts, using an external, transparently managed lookaside file. Peter Eisentraut sent in another revision of a patch to implement information schema parameter_default. Atri Sharma sent in another revision of a patch to implement WITHIN GROUP, some applications of which include MEDIAN and quantiles. Mitsumasa KONDO sent in a patch to optimize kernel readahead using buffer access strategy. David Rowley sent in two more revisions of a patch to change certain calls to appendPQExpBuffer() into calls to appendPQExpBufferStr() for performance. Pavel Stehule sent in another revision of a patch to add assertions to PL/pgsql. Haribabu Kommi sent in three more revisions of a patch to add an option to pg_basebackup to specify a different directory for pg_xlog. Andres Freund sent in another revision of a patch to add better atomics. David Rowley sent in a patch to change some instances of strncpy to memcpy. Fabrízio de Royes Mello sent in a patch to fix an issue where pg_isready's -d function did not work. Andres Freund sent in a flock of patches to implement a wait-free LW shared acquisition. Heikki Linnakangas sent in another revision of a patch to use HUGE_TLB where supported. Andrew Dunstan sent in two revisions of a patch to implement pre-commit triggers. Chris Browne sent in another revision of a patch to enhance createuser by allowing the caller to specify roles the user will have. Mikhail Tyurin sent in a patch to fix an issue where freeze couldn't be finished. Alexey Klyukin sent in a patch to enhance wildcards in .pgpass. Ian Lawrence Barwick sent in another revision of a patch to add more autocomplete to psql's \pset. Tom Lane sent in another revision of a patch to implement UNNEST with multiple arguments and TABLE() with multiple functions.
В списке pgsql-announce по дате отправления: