== PostgreSQL Weekly News - July 19 2015 == == PostgreSQL Product News ==
BDR 0.9.2, a replication system based on logical WALs, released. http://bdr-project.org/docs/stable/release-0.9.2.html == PostgreSQL Jobs for July == http://archives.postgresql.org/pgsql-jobs/2015-07/ == PostgreSQL Local == Rubens Souza from 2ndQuadrant Italia will lead a meetup on "How to install PostgreSQL on a Raspberry PI" on Thursday, July 23 in Prato. http://goo.gl/YYpsy7 PGDay Campinas 2015 will take place in Campinas on August 7. http://pgdaycampinas.com.br/english/ PostgresOpen 2015 will being held in Dallas, Texas September 16-18. http://2015.postgresopen.org/ PostgreSQL Session #7, will be held September 24th, 2015 in Paris, France, http://www.postgresql-sessions.org/7/about PGDay.IT 2015 will take place in Prato on October 23, 2015. The International Call For Papers is open until August 8. http://pgday.it PostgreSQL Conference Europe 2015 will be held on October 27-30 in the Vienna Marriott Hotel, in Vienna, Austria. The CfP is open until August 7. http://2015.pgconf.eu/ PGConf Silicon Valley 2015 is November 17-18 at the South San Francisco Convention Center. http://www.pgconfsv.com PGBR2015 (The Brazilian PostgreSQL Conference) will take place in Porto Alegre, Rio Grande do Sul, on November 18, 19 and 20. http://pgbr.postgresql.org.br/2015/en/#call-for-papers PostgreSQL Conference Europe 2015 will be held on October 27-30 in the Vienna Marriott Hotel, in Vienna, Austria. The CfP is open until August 7. http://2015.pgconf.eu/ == 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 == Bruce Momjian pushed: - release notes: markup: vacuumdb is an application, not command http://git.postgresql.org/pg/commitdiff/716f97f96663bdb8b64db9ed26121e2d399e06e0 Heikki Linnakangas pushed: - Reformat code in ATPostAlterTypeParse. The code in ATPostAlterTypeParse was very deeply indented, mostly because there were two nested switch-case statements, which add a lot of indentation. Use if-else blocks instead, to make the code less indented and more readable. This is in preparation for next patch that makes some actualy changes to the function. These cosmetic parts have been separated to make it easier to see the real changes in the other patch. http://git.postgresql.org/pg/commitdiff/1ab9faaecb03e685aeeb16143c19c0a24d6b0048 - Make regression test output stable. In the test query I added for ALTER TABLE retaining comments, the order of the result rows was not stable, and varied across systems. Add an ORDER BY to make the order predictable. This should fix the buildfarm failures. http://git.postgresql.org/pg/commitdiff/1a56498e5f6db949a066fb125199a7389a798421 - Add ALTER OPERATOR command, for changing selectivity estimator functions. Other options cannot be changed, as it's not totally clear if cached plans would need to be invalidated if one of the other options change. Selectivity estimator functions only change plan costs, not correctness of plans, so those should be safe. Original patch by Uriy Zhuravlev, heavily edited by me. http://git.postgresql.org/pg/commitdiff/321eed5f0f7563a0cabb3d7a98132856287c1ad1 - Fix event trigger support for the new ALTER OPERATOR command. Also, the lock on pg_operator should not be released until end of transaction. http://git.postgresql.org/pg/commitdiff/d5c0495cd4b9c78fdfc00961f4ae14c39f877f59 - Retain comments on indexes and constraints at ALTER TABLE ... TYPE ... When a column's datatype is changed, ATExecAlterColumnType() rebuilds all the affected indexes and constraints, and the comments from the old indexes/constraints were not carried over. To fix, create a synthetic COMMENT ON command in the work queue, to re-add any comments on constraints. For indexes, there's a comment field in IndexStmt that is used. This fixes bug #13126, reported by Kirill Simonov. Original patch by Michael Paquier, reviewed by Petr Jelinek and me. This bug is present in all versions, but only backpatch to 9.5. Given how minor the issue is, it doesn't seem worth the work and risk to backpatch further than that. http://git.postgresql.org/pg/commitdiff/e42375fc8124e99c33fa330c53c2b4b502fa0baf Fujii Masao pushed: - Prevent pgstattuple() from reporting BRIN as unknown index. Also this patch removes obsolete comment. Back-patch to 9.5 where BRIN index was added. http://git.postgresql.org/pg/commitdiff/705d397cd9cede1fd6fb1260d1689570bf6142d4 Álvaro Herrera pushed: - Mention table_rewrite as valid event trigger tag. This was forgotten in 618c9430a8. http://git.postgresql.org/pg/commitdiff/b1b667172242aaffa33f91360d9448fe98b65697 Robert Haas pushed: - Remove regression test added on auto-pilot. Test does not match the comment which precedes it. Peter Geoghegan http://git.postgresql.org/pg/commitdiff/aa6b2e629cb5debc432e0dcca8ffdcb8d8e6da39 - Add new function pg_notification_queue_usage. This tells you what fraction of NOTIFY's queue is currently filled. Brendan Jurd, reviewed by Merlin Moncure and Gurjeet Singh. A few further tweaks by me. http://git.postgresql.org/pg/commitdiff/a04bb65f70dafdf462e0478ad19e6de56df89bfc Noah Misch pushed: - AIX: Link the postgres executable with -Wl,-brtllib. This allows PostgreSQL modules and their dependencies to have undefined symbols, resolved at runtime. Perl module shared objects rely on that in Perl 5.8.0 and later. This fixes the crash when PL/PerlU loads such modules, as the hstore_plperl test suite does. Module authors can link using -Wl,-G to permit undefined symbols; by default, linking will fail as it has. Back-patch to 9.0 (all supported versions). http://git.postgresql.org/pg/commitdiff/bcd7c41206faf6d9654aa6e3766f87770d4fb305 - MinGW: Link ltree_plpython with plpython. The MSVC build system already did this, and building against Python 3 requires it. Back-patch to 9.5, where the module was introduced. http://git.postgresql.org/pg/commitdiff/736c1f238b3eeaf0f1cecf1753eb5194367fbad9 - AIX: Link TRANSFORM modules with their dependencies. The result closely resembles linking of these modules for the "win32" port. Augment the $(exports_file) header so the file is also usable as an import file. Unfortunately, relocating an AIX installation will now require adding $(pkglibdir) to LD_LIBRARY_PATH. Back-patch to 9.5, where the modules were introduced. http://git.postgresql.org/pg/commitdiff/7193436744819270eeb772f6ada4ec7a388c0b5f - AIX: Test the -qlonglong option before use. xlc provides "long long" unconditionally at C99-compatible language levels, and this option provokes a warning. The warning interferes with "configure" tests that fail in response to any warning. Notably, before commit 85a2a8903f7e9151793308d0638621003aded5ae, it interfered with the test for -qnoansialias. Back-patch to 9.0 (all supported versions). http://git.postgresql.org/pg/commitdiff/43d89a23d59c487bc9258fad7a6187864cb8c0c0 Magnus Hagander pushed: - Fix copy/past error in comment. David Christensen http://git.postgresql.org/pg/commitdiff/64c9d8a6c8810796ab9f09d435c248ea516c5f3c - Fix spelling error. David Rowley http://git.postgresql.org/pg/commitdiff/828df727a673d718265766611e59aa5189d102ba Tom Lane pushed: - Fix a low-probability crash in our qsort implementation. It's standard for quicksort implementations, after having partitioned the input into two subgroups, to recurse to process the smaller partition and then handle the larger partition by iterating. This method guarantees that no more than log2(N) levels of recursion can be needed. However, Bentley and McIlroy argued that checking to see which partition is smaller isn't worth the cycles, and so their code doesn't do that but just always recurses on the left partition. In most cases that's fine; but with worst-case input we might need O(N) levels of recursion, and that means that qsort could be driven to stack overflow. Such an overflow seems to be the only explanation for today's report from Yiqing Jin of a SIGSEGV in med3_tuple while creating an index of a couple billion entries with a very large maintenance_work_mem setting. Therefore, let's spend the few additional cycles and lines of code needed to choose the smaller partition for recursion. Also, fix up the qsort code so that it properly uses size_t not int for some intermediate values representing numbers of items. This would only be a live risk when sorting more than INT_MAX bytes (in qsort/qsort_arg) or tuples (in qsort_tuple), which I believe would never happen with any caller in the current core code --- but perhaps it could happen with call sites in third-party modules? In any case, this is trouble waiting to happen, and the corrected code is probably if anything shorter and faster than before, since it removes sign-extension steps that had to happen when converting between int and size_t. In passing, move a couple of CHECK_FOR_INTERRUPTS() calls so that it's not necessary to preserve the value of "r" across them, and prettify the output of gen_qsort_tuple.pl a little. Back-patch to all supported branches. The odds of hitting this issue are probably higher in 9.4 and up than before, due to the new ability to allocate sort workspaces exceeding 1GB, but there's no good reason to believe that it's impossible to crash older branches this way. http://git.postgresql.org/pg/commitdiff/9d6077abf9d6efd992a59f05ef5aba981ea32096 - Fix entirely broken permissions test in new alter_operator regression test. Not only did this test fail to test what it was supposed to test, but it left a user definition lying around, which caused subsequent runs of the regression tests to fail. http://git.postgresql.org/pg/commitdiff/266e771435bfed648138f6b684c895c8225dc8fc - Repair mishandling of cached cast-expression trees in plpgsql. In commit 1345cc67bbb014209714af32b5681b1e11eaf964, I introduced caching of expressions representing type-cast operations into plpgsql. However, I supposed that I could cache both the expression trees and the evaluation state trees derived from them for the life of the session. This doesn't work, because we execute the expressions in plpgsql's simple_eval_estate, which has an ecxt_per_query_memory that is only transaction-lifespan. Therefore we can end up putting pointers into the evaluation state tree that point to transaction-lifespan memory; in particular this happens if the cast expression calls a SQL-language function, as reported by Geoff Winkless. The minimum-risk fix seems to be to treat the state trees the same way we do for "simple expression" trees in plpgsql, ie create them in the simple_eval_estate's ecxt_per_query_memory, which means recreating them once per transaction. Since I had to introduce bookkeeping overhead for that anyway, I bought back some of the added cost by sharing the read-only expression trees across all functions in the session, instead of using a per-function table as originally. The simple-expression bookkeeping takes care of the recursive-usage risk that I was concerned about avoiding before. At some point we should take a harder look at how all this works, and see if we can't reduce the amount of tree reinitialization needed. But that won't happen for 9.5. http://git.postgresql.org/pg/commitdiff/0fc94a5bab4d0155db5d15197ed3bd8cb435eb21 - Make WaitLatchOrSocket's timeout detection more robust. In the previous coding, timeout would be noticed and reported only when poll() or socket() returned zero (or the equivalent behavior on Windows). Ordinarily that should work well enough, but it seems conceivable that we could get into a state where poll() always returns a nonzero value --- for example, if it is noticing a condition on one of the file descriptors that we do not think is reason to exit the loop. If that happened, we'd be in a busy-wait loop that would fail to terminate even when the timeout expires. We can make this more robust at essentially no cost, by deciding to exit of our own accord if we compute a zero or negative time-remaining-to-wait. Previously the code noted this but just clamped the time-remaining to zero, expecting that we'd detect timeout on the next loop iteration. Back-patch to 9.2. While 9.1 had a version of WaitLatchOrSocket, it was primitive compared to later versions, and did not guarantee reliable detection of timeouts anyway. (Essentially, this is a refinement of commit 3e7fdcffd6f77187, which was back-patched only as far as 9.2.) http://git.postgresql.org/pg/commitdiff/576a95b3a1ce465066c38d6859ccf64fca656e49 Andrew Dunstan pushed: - Support JSON negative array subscripts everywhere Previously, there was an inconsistency across json/jsonb operators that operate on datums containing JSON arrays -- only some operators supported negative array count-from-the-end subscripting. Specifically, only a new-to-9.5 jsonb deletion operator had support (the new "jsonb - integer" operator). This inconsistency seemed likely to be counter-intuitive to users. To fix, allow all places where the user can supply an integer subscript to accept a negative subscript value, including path-orientated operators and functions, as well as other extraction operators. This will need to be called out as an incompatibility in the 9.5 release notes, since it's possible that users are relying on certain established extraction operators changed here yielding NULL in the event of a negative subscript. For the json type, this requires adding a way of cheaply getting the total JSON array element count ahead of time when parsing arrays with a negative subscript involved, necessitating an ad-hoc lex and parse. This is followed by a "conversion" from a negative subscript to its equivalent positive-wise value using the count. From there on, it's as if a positive-wise value was originally provided. Note that there is still a minor inconsistency here across jsonb deletion operators. Unlike the aforementioned new "-" deletion operator that accepts an integer on its right hand side, the new "#-" path orientated deletion variant does not throw an error when it appears like an array subscript (input that could be recognized by as an integer literal) is being used on an object, which is wrong-headed. The reason for not being stricter is that it could be the case that an object pair happens to have a key value that looks like an integer; in general, these two possibilities are impossible to differentiate with rhs path text[] argument elements. However, we still don't allow the "#-" path-orientated deletion operator to perform array-style subscripting. Rather, we just return the original left operand value in the event of a negative subscript (which seems analogous to how the established "jsonb/json #> text[]" path-orientated operator may yield NULL in the event of an invalid subscript). In passing, make SetArrayPath() stricter about not accepting cases where there is trailing non-numeric garbage bytes rather than a clean NUL byte. This means, for example, that strings like "10e10" are now not accepted as an array subscript of 10 by some new-to-9.5 path-orientated jsonb operators (e.g. the new #- operator). Finally, remove dead code for jsonb subscript deletion; arguably, this should have been done in commit b81c7b409. Peter Geoghegan and Andrew Dunstan http://git.postgresql.org/pg/commitdiff/e02d44b8a74810341c90add4cd49e428b9d406b9 - Release note compatibility item. Note that json and jsonb extraction operators no longer consider a negative subscript to be invalid. http://git.postgresql.org/pg/commitdiff/473865048517c7808ddcf2299d054d8fe30fc6d5 - Enable transforms modules to build and test on Cygwin. This still doesn't work correctly with Python 3, but I am committing this so we can get Cygwin buildfarm members building with Python 2. http://git.postgresql.org/pg/commitdiff/00eff86cb8c2c9de9197197b4176362d1433f8f6 - Remove dead code. Defect noticed by Coverity. http://git.postgresql.org/pg/commitdiff/9aa663463bbf123e9d38dab88eeaef981fbc6caf == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == Haribabu Kommi sent in two more revisions of a patch to help improve the performance of vacuum truncation scans. Dinesh Kumar sent in a patch to add an SQL function to report a log message. Jeevan Chalke sent in a patch to fix some infelicities in GROUPING SETS. Jeff Janes sent in another revision of a patch to make pg_trgm work better with GIN. Amit Kapila sent in another revision of a patch to add machinery for assessing parallel safety. Paul Ramsey sent in two revisions of a patch to allow specifying extensions understood to be installed in PostgreSQL foreign servers. Jeevan Chalke, Kyotaro HORIGUCHI, and Andrew Gierth traded patches to fix an issue with GROUPING SETS that resulted in the not especially helpful, "unrecognized node type" error. Sameer Thakur and Rahila Syed traded patches to provide a VACUUM progress checker. Heikki Linnakangas sent in a patch to fix LWLock "variable" support broken by the lwlock scalability patch. SAWADA Masahiko sent in another revision of a patch to avoid freezing very large tables without need. Kyotaro HORIGUCHI sent in another revision of a patch to implement multivariate statistics. Michael Paquier sent in a patch to ensure that pg_rewind ignores xlog. Peter Geoghegan sent in two revisions of a patch to use software-based memory prefetching while sequentially fetching from SortTuple array and tuplestore. Jeevan Chalke sent in a patch to fix a collation bug in GROUPING SETS. Brendan Jurd sent in another revision of a patch to add pg_notification_queue_usage(). Pavel Stehule sent in a patch to add tab completion to DROP POLICY in psql. Fabien COELHO sent in two revisions of a patch to add per-script statistics and other improvements to pgbench. Michael Paquier sent in another revision of a patch to retain comments on indexes and constraints after issuing ALTER TABLE. Petr Jelinek sent in another revision of a patch to allow adding an extension including its dependencies. Julien Rouhaud sent in a patch to allow setting effective_io_concurrency per tablespace. Álvaro Herrera sent in a patch to ensure that breakage of object_class be obvious. Andres Freund sent in two revisions of a patch to make heap extension saner. Pavel Stehule sent in another revision of a patch to add a --strict option to pg_dump. -- Sent via pgsql-announce mailing list (pgsql-announce@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-announce