== PostgreSQL Weekly News - December 14 2014 == The Melbourne PostgreSQL meetup on February 18, 2015 will be hosting Gabriele Bartolini on PostgreSQL 9.4 for devops. Details below, and R, SVP. http://www.meetup.com/melpug/events/219082475/
Prague PostgreSQL Developer Day (P2D2) 2015 will be in Prague, Czech Republic February 11-12, 2015. http://www.p2d2.cz/ pg_back 1.1, shell script to back up everything for PostgreSQL, released. https://github.com/orgrim/pg_back == PostgreSQL Product News == == PostgreSQL Jobs for December == http://archives.postgresql.org/pgsql-jobs/2014-12/threads.php == PostgreSQL Local == FOSDEM PGDay (January 30, 2015) and the FOSDEM PostgreSQL Dev Room (January 31-February 1, 2015) http://fosdem2015.pgconf.eu/ Prague PostgreSQL Developer Day (P2D2) 2015 will be in Prague, Czech Republic February 11-12, 2015. http://www.p2d2.cz/ pgDaySF 2015 will be held March 10, 2015 in Burlingame, California. http://sfpostgres.org/pgday-sf-2015-call-for-speakers-and-sponsors/ The CfP is open for Nordic PostgreSQL Day 2015, which will be held March 11, 2015 in Copenhagen, Denmark. http://2015.nordicpgday.org/cfp/ The CfP for PGConf US 2015 is open through December 17th, 2014 Notifications will go out on January 10, 2014. The event takes place March 25-27, 2015 in NYC. http://nyc.pgconf.us/2015/ == 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 == Simon Riggs pushed: - Remove duplicate code in heap_prune_chain(). No need to set tuple tableOid twice. Jim Nasby http://git.postgresql.org/pg/commitdiff/c2707547193a1e1f1016552cc92f33e8f0d296d4 - Correct recovery_target_action docs. From Michael Paquier http://git.postgresql.org/pg/commitdiff/da71632fcfc4e642e9bafb2c0074cad109e59486 - Use GetSystemTimeAsFileTime directly in win32. PostgreSQL was calling GetSystemTime followed by SystemTimeToFileTime in the win32 port gettimeofday function. This is not necessary and limits the reported precision to the 1ms granularity that the SYSTEMTIME struct can represent. By using GetSystemTimeAsFileTime we avoid unnecessary conversions and capture timestamps at 100ns granularity, which is then rounded to 1µs granularity for storage in a PostgreSQL timestamp. On most Windows systems this change will actually have no significant effect on timestamp resolution as the system timer tick is typically between 1ms and 15ms depending on what timer resolution currently running applications have requested. You can check this with clockres.exe from sysinternals. Despite the platform limiation this change still permits capture of finer timestamps where the system is capable of producing them and it gets rid of an unnecessary syscall. The higher resolution GetSystemTimePreciseAsFileTime call available on Windows 8 and Windows Server 2012 has the same interface as GetSystemTimeAsFileTime, so switching to GetSystemTimeAsFileTime makes it easier to use the Precise variant later. Craig Ringer, reviewed by David Rowley http://git.postgresql.org/pg/commitdiff/519b0757a37254452e013ea0ac95f4e56391608c - Windows: use GetSystemTimePreciseAsFileTime if available. PostgreSQL on Windows 8 or Windows Server 2012 will now get high-resolution timestamps by dynamically loading the GetSystemTimePreciseAsFileTime function. It'll fall back to to GetSystemTimeAsFileTime if the higher precision variant isn't found, so the same binaries without problems on older Windows releases. No attempt is made to detect the Windows version. Only the presence or absence of the desired function is considered. Craig Ringer http://git.postgresql.org/pg/commitdiff/8001fe67a3d66c95861ce1f7075ef03953670d13 - Execute 18 tests for src/bin/scripts/t/090.. Some requests count as two tests. http://git.postgresql.org/pg/commitdiff/1135aabab5dad2f4e79aade3c907bd7a51ac263a - Silence REINDEX. Previously REINDEX DATABASE and REINDEX SCHEMA produced a stream of NOTICE messages. Removing that since it is inconsistent for such a command to produce output without a VERBOSE option. http://git.postgresql.org/pg/commitdiff/ae4e6887a4eaa62ff3e47a16a8b2b6a91fa6a514 - REINDEX SCHEMA. Add new SCHEMA option to REINDEX and reindexdb. Sawada Masahiko Reviewed by Michael Paquier and Fabrízio de Royes Mello http://git.postgresql.org/pg/commitdiff/fe263d115a7dd16095b8b8f1e943aff2bb4574d2 - Further changes to REINDEX SCHEMA. Ensure we reindex indexes built on Mat Views. Based on patch from Micheal Paquier Add thorough tests to check that indexes on tables, toast tables and mat views are reindexed. Simon Riggs http://git.postgresql.org/pg/commitdiff/2646d2d4a9d8b978fda0d0d1affae4788a30f604 Peter Eisentraut pushed: - doc: Fix DocBook table column count declaration. This was broken in 618c9430a82860c84a3be2711eec2c3b43573b2a. http://git.postgresql.org/pg/commitdiff/611d46ea67c0863ab1d97c73c8cead1ed5ca82f0 - Fix typo. Author: Fabrízio de Royes Mello <fabriziome...@gmail.com> http://git.postgresql.org/pg/commitdiff/7442a8899751c804c7def0407fd1756360917754 - SSL tests: Silence pg_ctl output. Otherwise the pg_ctl start and stop messages get mixed up with the TAP output, which isn't technically valid. http://git.postgresql.org/pg/commitdiff/ce37eff06df66dcd2091c76c0d29178b55e1e407 - SSL tests: Remove trailing blank lines http://git.postgresql.org/pg/commitdiff/2f8607860b4ef3668350a47e9488aee3d640da75 - doc: Move website-stylesheet setting to a more appropriate location http://git.postgresql.org/pg/commitdiff/7afc2336cd9ce294894fcb750ab8c8cf51bf6bce - doc: Fix markup http://git.postgresql.org/pg/commitdiff/c50423c0feac69a4d810f17145536d6844fad01e Álvaro Herrera pushed: - pg_xlogdump/.gitignore: add committsdesc.c. Author: Michael Paquier http://git.postgresql.org/pg/commitdiff/dcbfc00aba1ee4329fea20ad58e13a84848df993 Heikki Linnakangas pushed: - Add a regression test suite for SSL support. It's not run by the global "check" or "installcheck" targets, because the temporary installation it creates accepts TCP connections from any user the same host, which is insecure. http://git.postgresql.org/pg/commitdiff/e39250c644ea7cd3904e4e24570db21a209cf97f - Use correct macro for reltablespace. It's an OID. WRITE_UINT_FIELD is identical to WRITE_OID_FIELD, but let's be tidy. Mark Dilger http://git.postgresql.org/pg/commitdiff/10eb7dfa9b7499d603d09a141b9ae4e36b14393f - Put the logic to decide which synchronous standby is active into a function. This avoids duplicating the code. Michael Paquier, reviewed by Simon Riggs and me http://git.postgresql.org/pg/commitdiff/b1332e98c441b40300670f55a4303bf69cd8b226 - Remove duplicate #define. Mark Dilger http://git.postgresql.org/pg/commitdiff/50f2c0687fabde9c665df66f77b464c539d927a9 Tom Lane pushed: - Fix minor thinko in convertToJsonb(). The amount of space to reserve for the value's varlena header is VARHDRSZ, not sizeof(VARHDRSZ). The latter coding accidentally failed to fail because of the way the VARHDRSZ macro is currently defined; but if we ever change it to return size_t (as one might reasonably expect it to do), convertToJsonb() would have failed. Spotted by Mark Dilger. http://git.postgresql.org/pg/commitdiff/24688f4e5a7d5fadde0e43a5b123432d81577d82 - Fix assorted confusion between Oid and int32. In passing, also make some debugging elog's in pgstat.c a bit more consistently worded. Back-patch as far as applicable (9.3 or 9.4; none of these mistakes are really old). Mark Dilger identified and patched the type violations; the message rewordings are mine. http://git.postgresql.org/pg/commitdiff/06d5803ffa1ea5a42295c9266f30557e44a99de7 - Fix completely broken REINDEX SCHEMA testcase. Aside from not testing the case it claimed to test (namely a permissions failure), it left a login-capable role lying around, which quite aside from possibly being a security hole would cause subsequent regression runs to fail since the role would already exist. http://git.postgresql.org/pg/commitdiff/58af84f4bba5c87b352cf40e276e287fd289bd77 - Make rowsecurity test clean up after itself, too. Leaving global objects like roles hanging around is bad practice. http://git.postgresql.org/pg/commitdiff/0845264642d855d92c63c5d05a4ef83245ca16c5 - Fix corner case where SELECT FOR UPDATE could return a row twice. In READ COMMITTED mode, if a SELECT FOR UPDATE discovers it has to redo WHERE-clause checking on rows that have been updated since the SELECT's snapshot, it invokes EvalPlanQual processing to do that. If this first occurs within a non-first child table of an inheritance tree, the previous coding could accidentally re-return a matching row from an earlier, already-scanned child table. (And, to add insult to injury, I think this could make it miss returning a row that should have been returned, if the updated row that this happens on should still have passed the WHERE qual.) Per report from Kyotaro Horiguchi; the added isolation test is based on his test case. This has been broken for quite awhile, so back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/2db576ba8c449fcaf61ae7aa14ed62e63ebf5924 - Fix planning of SELECT FOR UPDATE on child table with partial index. Ordinarily we can omit checking of a WHERE condition that matches a partial index's condition, when we are using an indexscan on that partial index. However, in SELECT FOR UPDATE we must include the "redundant" filter condition in the plan so that it gets checked properly in an EvalPlanQual recheck. The planner got this mostly right, but improperly omitted the filter condition if the index in question was on an inheritance child table. In READ COMMITTED mode, this could result in incorrectly returning just-updated rows that no longer satisfy the filter condition. The cause of the error is using get_parse_rowmark() when get_plan_rowmark() is what should be used during planning. In 9.3 and up, also fix the same mistake in contrib/postgres_fdw. It's currently harmless there (for lack of inheritance support) but wrong is wrong, and the incorrect code might get copied to someplace where it's more significant. Report and fix by Kyotaro Horiguchi. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/462bd95705a0c23ba0b0ba60a78d32566a0384c1 - Revert misguided change to postgres_fdw FOR UPDATE/SHARE code. In commit 462bd95705a0c23ba0b0ba60a78d32566a0384c1, I changed postgres_fdw to rely on get_plan_rowmark() instead of get_parse_rowmark(). I still think that's a good idea in the long run, but as Etsuro Fujita pointed out, it doesn't work today because planner.c forces PlanRowMarks to have markType = ROW_MARK_COPY for all foreign tables. There's no urgent reason to change this in the back branches, so let's just revert that part of yesterday's commit rather than trying to design a better solution under time pressure. Also, add a regression test case showing what postgres_fdw does with FOR UPDATE/SHARE. I'd blithely assumed there was one already, else I'd have realized yesterday that this code didn't work. http://git.postgresql.org/pg/commitdiff/8ec8760fc87ecde0516e511f1c55aec627b01ea7 - Avoid instability in output of new REINDEX SCHEMA test. The planner seems to like to do this join query as a hash join, making the output ordering machine-dependent; worse, it's a hash on OIDs, so that it's a bit astonishing that the result doesn't change from run to run even on one machine. Add an ORDER BY to get consistent results. Per buildfarm. I also suppressed output from the final DROP SCHEMA CASCADE, to avoid occasional failures similar to those fixed in commit 81d815dc3ed74a7d. That hasn't been observed in the buildfarm yet, but it seems likely to happen in future if we leave it as-is. http://git.postgresql.org/pg/commitdiff/1c5c70df45714f38c033bb1a272d8db4f2dc8a2f - Repair corner-case bug in array version of percentile_cont(). The code for advancing through the input rows overlooked the case that we might already be past the first row of the row pair now being considered, in case the previous percentile also fell between the same two input rows. Report and patch by Andrew Gierth; logic rewritten a bit for clarity by me. http://git.postgresql.org/pg/commitdiff/b0f479113abaf28f1c9ed25d856ffe6fd4bb7dc9 - Improve recovery target settings documentation. Commit 815d71dee hadn't bothered to update the documentation to match the behavioral change, and a lot of other text in this section was badly in need of copy-editing. http://git.postgresql.org/pg/commitdiff/e311cd6ded096122a5f2b5cbe91bc3a9f0dda3cb - Update 9.4 release notes. Set release date, do a final pass of wordsmithing, improve some other new-in-9.4 documentation. http://git.postgresql.org/pg/commitdiff/0923b01e3e64d4985ffbdf8ec912e25ad02e0c0c Andrew Dunstan pushed: - Add json_strip_nulls and jsonb_strip_nulls functions. The functions remove object fields, including in nested objects, that have null as a value. In certain cases this can lead to considerably smaller datums, with no loss of semantic information. Andrew Dunstan, reviewed by Pavel Stehule. http://git.postgresql.org/pg/commitdiff/237a8824430c607fce1eafde0c625744d50a455c - Add several generator functions for jsonb that exist for json. The functions are: to_jsonb(), jsonb_object(), jsonb_build_object(), jsonb_build_array(), jsonb_agg(), and jsonb_object_agg(). Also along the way some better logic is implemented in json_categorize_type() to match that in the newly implemented jsonb_categorize_type(). Andrew Dunstan, reviewed by Pavel Stehule and Alvaro Herrera. http://git.postgresql.org/pg/commitdiff/7e354ab9fe9e7c3b0a7a40f226c76bd5cf6438d0 - Add CINE option for CREATE TABLE Alexander Shulgin and CREATE MATERIALIZED VIEW. Fabrízio de Royes Mello reviewed by Rushabh Lathia. http://git.postgresql.org/pg/commitdiff/e39b6f953e8c699eacd676314574ed5869ebacef == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == Petr (PJMODOS) Jelinek sent in two more revisions of a patch to add a shutdown_at_recovery_target option to recovery.conf. Euler Taveira de Oliveira sent in a patch to bring the documentation of pg_receivexlog into line with its new functionality. Peter Geoghegan sent in another revision of a patch to implement INSERT ... ON CONFLICT {UPDATE | IGNORE}. Petr (PJMODOS) Jelinek sent in another revision of a patch to add a sequence access method. Alexander Shulgin sent in two revisions of a patch to track TRUNCATEs in pgstat transaction stats. Robert Haas and Heikki Linnakangas traded patches to advance local xmin more aggressively. Etsuro Fujita sent in another revision of a patch to allow foreign tables to be part of table inheritance hierarchies. Álvaro Herrera sent in a patch to implement logical column ordering. David Rowley sent in another revision of a patch to allow removing INNER JOIN under some circumstances. Matt Newell sent in another revision of a patch to enable libpq pipelining. Heikki Linnakangas improved the performance of k-Nearest-Neighbor GiST index searches using a Pairing Heap http://en.wikipedia.org/wiki/Pairing_heap. Petr (PJMODOS) Jelinek sent in a patch to implement TABLESAMPLE. Jeff Davis sent in another revision of a patch to implement a memory-bounded HashAgg. Mark Dilger sent in a WIP patch to allow printf formatting for Oids. Kyotaro HORIGUCHI and Tom Lane traded patches to fix an issue where some queries return duplicate rows after FOR UPDATE was blocked, in other words, after getting HeapTupleUpdated in ExecLockRows. Simon Riggs sent in another revision of a patch to turn off heap-only tuple cleanup under some circumstances. Heikki Linnakangas and Michael Paquier traded patches to refactor the code for sync node detection. Peter Eisentraut sent in a patch to ensure that VPATH builds not write to the source tree. Kyotaro HORIGUCHI sent in a patch to fix an issue where SELECT FROM <inheritance parent> WHERE <cond> FOR UPDATE may return results which does not match the <cond>. Alexander Shulgin sent in another revision of a patch to turn recovery.conf into GUCs. Robert Haas sent in a patch to add two new concepts: parallel mode, and parallel contexts. Heikki Linnakangas sent in a patch to place pg_rewind, a tool that allows repurposing an old master server as a new standby server, after promotion, even if the old master was not shut down cleanly, in contrib/. Andreas Karlsson sent in a patch to reduce the required lock strength of trigger and foreign key DDL. Tatsuo Ishii sent in a patch to pgbench from exiting when the pg_bench_* tables do not exist. Michael Paquier sent in another revision of a patch to allow compressing full-page writes. Ali Akbar sent in a test for the patch to fix xpath() to return namespace definitions. Emre Hasegeli sent in another revision of a patch to implement a BRIN range operator class. -- Sent via pgsql-announce mailing list (pgsql-announce@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-announce
