== PostgreSQL Weekly News - March 22 2015 == PGDay UK, Conference will be taking place on 7th July 2015 – it is aimed at the UK PostgreSQL Community. The CfP is open until 13 April 2015. http://www.postgresqlusergroup.org.uk
The Call For Papers for PostgresOpen 2015, being held in Dallas, Texas from September 16th to 18th, is now open. http://2015.postgresopen.org/callforpapers/ == PostgreSQL Product News == pgFormatter v1.3, a SQL formatter/beautifier which supports keywords from SQL-92, SQL-99, SQL-2003, SQL-2008, SQL-2011 and PostgreSQL keywords are not in the standard, released. http://sqlformat.darold.net/ pg_shard 1.1, an extension for scaling out PostgreSQL, released. https://www.citusdata.com/blog/21-jason/139-announcing-pg-shard-1-1 == PostgreSQL Jobs for March == http://archives.postgresql.org/pgsql-jobs/2015-03/threads.php == PostgreSQL Local == PGConf US 2015 takes place March 25-27, 2015 in NYC. http://nyc.pgconf.us/2015/ The CfP for the second Swiss Postgres Conference, to be held June 25-26, 2015 at HSR Rapperswil, is open until April 1. http://www.postgres-conference.ch/cfp/ The constituent assembly of the Swiss PostgreSQL Users Group (SwissPUG) will be Friday, April 10, 2015 http://www.swisspug.org India PostgreSQL UserGroup will hold a PGday in Bengaluru, Karnataka, India on April 11, 2015. RSVP at http://www.meetup.com/India-PUG/events/220553997/ There is a Postgres track in a database technology conference(DTCC) in April 18, 2015 in Beijing, China. http://dtcc.it168.com/list_jiabin.html pgDay Paris will be held in Paris France on April 21, 2015. http://pgday.paris/ PGCon 2015 is June 16-20 in Ottawa, Canada. http://www.pgcon.org/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 == Tom Lane pushed: - Replace insertion sort in contrib/intarray with qsort(). It's all very well to claim that a simplistic sort is fast in easy cases, but O(N^2) in the worst case is not good ... especially if the worst case is as easy to hit as "descending order input". Replace that bit with our standard qsort. Per bug #12866 from Maksym Boguk. Back-patch to all active branches. http://git.postgresql.org/pg/commitdiff/8d1f239003d0245dda636dfa6cf0add13bee69d6 - Allow foreign tables to participate in inheritance. Foreign tables can now be inheritance children, or parents. Much of the system was already ready for this, but we had to fix a few things of course, mostly in the area of planner and executor handling of row locks. As side effects of this, allow foreign tables to have NOT VALID CHECK constraints (and hence to accept ALTER ... VALIDATE CONSTRAINT), and to accept ALTER SET STORAGE and ALTER SET WITH/WITHOUT OIDS. Continuing to disallow these things would've required bizarre and inconsistent special cases in inheritance behavior. Since foreign tables don't enforce CHECK constraints anyway, a NOT VALID one is a complete no-op, but that doesn't mean we shouldn't allow it. And it's possible that some FDWs might have use for SET STORAGE or SET WITH OIDS, though doubtless they will be no-ops for most. An additional change in support of this is that when a ModifyTable node has multiple target tables, they will all now be explicitly identified in EXPLAIN output, for example: Update on pt1 (cost=0.00..321.05 rows=3541 width=46) Update on pt1 Foreign Update on ft1 Foreign Update on ft2 Update on child3 -> Seq Scan on pt1 (cost=0.00..0.00 rows=1 width=46) -> Foreign Scan on ft1 (cost=100.00..148.03 rows=1170 width=46) -> Foreign Scan on ft2 (cost=100.00..148.03 rows=1170 width=46) -> Seq Scan on child3 (cost=0.00..25.00 rows=1200 width=46) This was done mainly to provide an unambiguous place to attach "Remote SQL" fields, but it is useful for inherited updates even when no foreign tables are involved. Shigeru Hanada and Etsuro Fujita, reviewed by Ashutosh Bapat and Kyotaro Horiguchi, some additional hacking by me http://git.postgresql.org/pg/commitdiff/cb1ca4d800621dcae67ca6c799006de99fa4f0a5 Álvaro Herrera pushed: - Fix out-of-array-bounds compiler warning. Since the array length check is using a post-increment operator, the compiler complains that there's a potential write to one element beyond the end of the array. This is not possible currently: the only path to this function is through pg_get_object_address(), which already verifies that the input array is no more than two elements in length. Still, a bug is a bug. No idea why my compiler doesn't complain about this ... Pointed out by Dead Rasheed and Peter Eisentraut http://git.postgresql.org/pg/commitdiff/a190738457353ddb60743e45972f6fe50a75ee77 - Support opfamily members in get_object_address. In the spirit of 890192e99af and 4464303405f: have get_object_address understand individual pg_amop and pg_amproc objects. There is no way to refer to such objects directly in the grammar -- rather, they are almost always considered an integral part of the opfamily that contains them. (The only case that deals with them individually is ALTER OPERATOR FAMILY ADD/DROP, which carries the opfamily address separately and thus does not need it to be part of each added/dropped element's address.) In event triggers it becomes possible to become involved with individual amop/amproc elements, and this commit enables pg_get_object_address to do so as well. To make the overall coding simpler, this commit also slightly changes the get_object_address representation for opclasses and opfamilies: instead of having the AM name in the objargs array, I moved it as the first element of the objnames array. This enables the new code to use objargs for the type names used by pg_amop and pg_amproc. Reviewed by: Stephen Frost http://git.postgresql.org/pg/commitdiff/a61fd5334eb1040d0dcec0368702398a5b49152c - Rationalize vacuuming options and parameters. We were involving the parser too much in setting up initial vacuuming parameters. This patch moves that responsibility elsewhere to simplify code, and also to make future additions easier. To do this, create a new struct VacuumParams which is filled just prior to vacuum execution, instead of at parse time; for user-invoked vacuuming this is set up in a new function ExecVacuum, while autovacuum sets it up by itself. While at it, add a new member VACOPT_SKIPTOAST to enum VacuumOption, only set by autovacuum, which is used to disable vacuuming of the toast table instead of the old do_toast parameter; this relieves the argument list of vacuum() and some callees a bit. This partially makes up for having added more arguments in an effort to avoid having autovacuum from constructing a VacuumStmt parse node. Author: Michael Paquier. Some tweaks by Álvaro Reviewed by: Robert Haas, Stephen Frost, Álvaro Herrera http://git.postgresql.org/pg/commitdiff/0d831389749a3baaced7b984205b9894a82444b9 - Setup cursor position for schema-qualified elements. This makes any errors thrown while looking up such schemas report the position of the error. Author: Ryan Kelly Reviewed by: Jeevan Chalke, Tom Lane http://git.postgresql.org/pg/commitdiff/b8d226b4f9691c7afb986dbaaf3f6ff7b203d1b5 - Install shared libraries to bin/ in Windows under MSVC. Since commit cb4a3b04 we were already doing this for the Cygwin/mingw toolchains, but MSVC had not been updated to do it. At Install.pm time, the Makefile (or GNUmakefile) is inspected, and if a line matching SO_MAJOR_VERSION is found (indicating a shared library is being built), then files with the .dll extension are set to be installed in bin/ rather than lib/, while files with .lib extension are installed in lib/. This makes the MSVC toolchain up to date with cygwin/mingw. This removes ad-hoc hacks that were copying files into bin/ or lib/ manually (libpq.dll in particular was already being copied into bin). So while this is a rather ugly kludge, it's still cleaner than what was there before. Author: Michael Paquier Reviewed by: Asif Naeem http://git.postgresql.org/pg/commitdiff/f9dead5624c63b009fc04229c1e5f660436b747b - array_offset() and array_offsets(). These functions return the offset position or positions of a value in an array. Author: Pavel Stěhule Reviewed by: Jim Nasby http://git.postgresql.org/pg/commitdiff/13dbc7a824b3f905904cab51840d37f31a07a9ef Andres Freund pushed: - Remove docs missed in 51c11a7025. Somehow I misresolved a merge conflict when forward porting Petr's patch leading to a section of the docs remaining... Thankfully Fujii spotted my mistake. http://git.postgresql.org/pg/commitdiff/4559167c6b75be334fabad70d7cc03a38a08d494 - Use 128-bit math to accelerate some aggregation functions. On platforms where we support 128bit integers, use them to implement faster transition functions for sum(int8), avg(int8), var_*(int2/int4),stdev_*(int2/int4). Where not supported continue to use numeric as a transition type. In some synthetic benchmarks this has been shown to provide significant speedups. Bumps catversion. Discussion: 544bb5f1.50...@proxel.se Author: Andreas Karlsson Reviewed-By: Peter Geoghegan, Petr Jelinek, Andres Freund, Oskari Saarenmaa, David Rowley http://git.postgresql.org/pg/commitdiff/959277a4f579da5243968c750069570a58e92b38 - Add, optional, support for 128bit integers. We will, for the foreseeable future, not expose 128 bit datatypes to SQL. But being able to use 128bit math will allow us, in a later patch, to use 128bit accumulators for some aggregates; leading to noticeable speedups over using numeric. So far we only detect a gcc/clang extension that supports 128bit math, but no 128bit literals, and no *printf support. We might want to expand this in the future to further compilers; if there are any that that provide similar support. Discussion: 544bb5f1.50...@proxel.se Author: Andreas Karlsson, with significant editorializing by me Reviewed-By: Peter Geoghegan, Oskari Saarenmaa http://git.postgresql.org/pg/commitdiff/8122e1437e332e156d971a0274879b0ee76e488a - Fix minor copy & pasto in the int128 accumulator patch. It's unlikely that using PG_GETARG_INT16 instead of PG_GETARG_INT32 in this pace can cause actual problems, but this still should be fixed. http://git.postgresql.org/pg/commitdiff/59b0a98af07cf8decfe94739f92bf18ebb34ffc6 Bruce Momjian pushed: - pg_upgrade: document use of rsync for slave upgrades. Also document that rsync has one-second granularity for file change comparisons. Report by Stephen Frost http://git.postgresql.org/pg/commitdiff/417f78a5178815d8c10f86b1561c88c45c53c2d2 - doc: ALTER DOMAIN VALIDATE CONSTRAINT can also fail. Document that ALTER DOMAIN VALIDATE CONSTRAINT can also fail for composite types. Report by Ondřej Bouda http://git.postgresql.org/pg/commitdiff/5f286c02423f28c8e4c4d07569417a3d4f40f1a0 - psql: allow DROP INDEX CONCURRENTLY in AUTOCOMMIT off mode. Previously this threw an error. Patch by Feike Steenbergen http://git.postgresql.org/pg/commitdiff/788e799ed4ee9a0e87f18e0426418622972a4de4 - regression tests: remove polygon diagrams. The diagrams were inaccurate. Report by Emre Hasegeli http://git.postgresql.org/pg/commitdiff/05d1910c1c1f4195065882aa077114bc590b45e3 - docs: mention the optimizer can increase the index usage count. Report by Marko Tiikkaja http://git.postgresql.org/pg/commitdiff/7e9ed623d9988fcb1497a2a8ca7f676a5bfa136f - C comment: update lock level mention in comment. Patch by Etsuro Fujita http://git.postgresql.org/pg/commitdiff/13a10c0ccd984643ef88997ac177da7c4b7e46a6 - vacuumdb --help text: clarify analyze-only. Patch by Mats Erik Andersson http://git.postgresql.org/pg/commitdiff/159134b695f9f2ac4ccefe8c41d5af0aa26bba13 - C comment: clearify SQL command mention. Patch by Amit Langote http://git.postgresql.org/pg/commitdiff/0c8fa710b69afffa2e7926ef3e6c372c2d8609f2 - docs: clarify the use of shell types. Report by Ondřej Bouda http://git.postgresql.org/pg/commitdiff/7d60ce80658cd0d3fa9c59681fc3072f664dda80 - doc: add comma for clarity. Patch by Etsuro Fujita http://git.postgresql.org/pg/commitdiff/c863c91e3fd6d7a9547bf3f0a4320320a1fd6261 - pg_recvlogical: update --help description. Patch by Euler Taveira http://git.postgresql.org/pg/commitdiff/30a5ce8f5da15174f85619963ca0a07e6343f821 - Use mmap MAP_NOSYNC option to limit shared memory writes. mmap() is rarely used for shared memory, but when it is, this option is useful, particularly on the BSDs. Patch by Sean Chittenden http://git.postgresql.org/pg/commitdiff/34afbba84e8fd12bbf905d68d1112f51ed584df9 - Add TOAST table to pg_shseclabel for long label use. Report by Andres Freund http://git.postgresql.org/pg/commitdiff/1c7087af422fcdd6785a8e2dccbfc1ed567171a0 - rm src/test/performance. Last changed in 1997. Report by Andres Freund http://git.postgresql.org/pg/commitdiff/8ac356cde312693aa79f6b2fe7c46b8ed6108787 - to_char(float4/8): don't print "junk" digits. Commit cc0d90b73b2e6dd2f301d46818a7265742c41a14 also avoids printing junk digits, which are digits that are beyond the precision of the underlying type. http://git.postgresql.org/pg/commitdiff/9d61b9953c1489cbb458ca70013cf5fca1bb7710 - to_char(float4/8): zero pad to specified length. Previously, zero padding was limited to the internal length, rather than the specified length. This allows it to match to_char(int/numeric), which always padded to the specified length. Regression tests added. BACKWARD INCOMPATIBILITY http://git.postgresql.org/pg/commitdiff/cc0d90b73b2e6dd2f301d46818a7265742c41a14 - to_char: revert cc0d90b73b2e6dd2f301d46818a7265742c41a14. Revert "to_char(float4/8): zero pad to specified length". There are too many platform-specific problems, and the proper rounding is missing. Also revert companion patch 9d61b9953c1489cbb458ca70013cf5fca1bb7710. http://git.postgresql.org/pg/commitdiff/33a2c5ecd60a2cf68cfcdd37916f3989bb956581 Robert Haas pushed: - Fix status reporting for terminated bgworkers that were never started. Previously, GetBackgroundWorkerPid() would return BGWH_NOT_YET_STARTED if the slot used for the worker registration had not been reused by unrelated activity, and BGWH_STOPPED if it had. Either way, a process that had requested notification when the state of one of its background workers changed did not receive such notifications. Fix things so that GetBackgroundWorkerPid() always returns BGWH_STOPPED in this situation, so that we do not erroneously give waiters the impression that the worker will eventually be started; and send notifications just as we would if the process terminated after having been started, so that it's possible to wait for the postmaster to process a worker termination request without polling. Discovered by Amit Kapila during testing of parallel sequential scan. Analysis and fix by me. Back-patch to 9.4; there may not be anyone relying on this interface yet, but if anyone is, the new behavior is a clear improvement. http://git.postgresql.org/pg/commitdiff/bf740ce9e5d82612889d131f34c079215973ca00 - Add flags argument to dsm_create. Right now, there's only one flag, DSM_CREATE_NULL_IF_MAXSEGMENTS, which suppresses the error that would normally be thrown when the maximum number of segments already exists, instead returning NULL. It might be useful to add more flags in the future, such as one to ignore allocation errors, but I haven't done that here. http://git.postgresql.org/pg/commitdiff/12968cf4085409c50f70c6643d92befdb34008f6 Stephen Frost pushed: - GetUserId() changes to has_privs_of_role(). The pg_stat and pg_signal-related functions have been using GetUserId() instead of has_privs_of_role() for checking if the current user should be able to see details in pg_stat_activity or signal other processes, requiring a user to do 'SET ROLE' for inheirited roles for a permissions check, unlike other permissions checks. This patch changes that behavior to, instead, act like most other permission checks and use has_privs_of_role(), removing the 'SET ROLE' need. Documentation and error messages updated accordingly. Per discussion with Alvaro, Peter, Adam (though not using Adam's patch), and Robert. Reviewed by Jeevan Chalke. http://git.postgresql.org/pg/commitdiff/bf038899965263dbc4aef2b43c8fdfe6f49b788f Peter Eisentraut pushed: - Fix whitespace http://git.postgresql.org/pg/commitdiff/28beb69f8bc4ad7f0d1daca1095e1b47ffeaf011 Heikki Linnakangas pushed: - Make pg_xlogdump MSVC build work more like others. Instead of copying xlogreader.c and *desc.c files into the source directory, build them where they are. That's what we do for other binaries that need to compile and link in files from elsewhere in the source tree. The commit history suggests that it was done this way because of issues with older versions of MSVC. I think this should work, but we'll see if the buildfarm complains. http://git.postgresql.org/pg/commitdiff/1933a5bbc883fd2697c42d82ae12f2d585559b23 == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == Kyotaro HORIGUCHI sent in another revision of a patch to reduce pinning in btree indexes. Kaigai Kouhei sent in two more revisions of a patch to add custom/foreign join APIs. David Rowley sent in another revision of a patch to allow removing inner JOINs under certain conditions. Dean Rasheed sent in another revision of a patch to improve RLS qual pushdown. Álvaro Herrera sent in another revision of a flock of patches to add deparsing utility commands. Adam Brightwell sent in a patch to fix a regression test for sepgsql. Michael Paquier sent in another revision of a patch to add in-core regression tests for recovery. Álvaro Herrera sent in another revision of a patch to add a missing type_schema hint. Álvaro Herrera sent in another revision of a patch to move freeze parameters of VacuumStmt into a separate code path. Robert Haas sent in three more revisions of a patch to add parallel mode and parallel contexts. Julien Tachoires and Andreas Karlsson traded patches to allow toast tables to be moved to a different tablespace. Bruce Momjian and Tom Lane traded patches to prevent post-commit interrupts. Robert Haas sent in another revision of a patch to allow testing parallel safety. Amit Kapila sent in another revision of a patch to implement parallel sequential scan. Michael Paquier sent in another revision of a patch to implement table-level log_autovacuum_min_duration. Michael Paquier sent in a patch to fix an example of a variable referencing itself in example of pgbench.sgml. David Christensen sent in two revisions of a patch to add a two-arg current_setting() with fallback. Max Filippov sent in a patch to compare the linker and compiler outputs in the case of pthread with their default output. Bruce Momjian sent in a patch to document the fact that repeatable read and serializable transactions see data committed after transaction start. Kyotaro HORIGUCHI sent in another revision of a patch to add shared infrastructure and functional dependencies for the upcoming multivariate statistics feature. Bruce Momjian sent in another revision of a patch to change pg_ctl's default shutdown mode from "smart" to "fast." Bruce Momjian sent in four revisions of a patch to simplify examples of dynamic SQL. Bruce Momjian sent in a patch to remove an extra VACUUM in initdb. Bruce Momjian sent in two more revisions of a patch to enable asciidoc as a psql output format. David Wheeler sent in another revision of a patch to add launchd support. Peter Geoghegan sent in two more revisions of a patch to add sort support for numerics. Bruce Momjian sent in a patch to document more clearly the limitations of NUMERIC. David Rowley sent in another revision of a patch to improve performance for joins where outer side is unique. Peter Eisentraut sent in another revision of a patch to add TRANSFORMs. Tomas Vondra sent in a patch to add two new log_line_prefix escape sequences - %T and %M, doing the same thing as %t and %m, but formatting the value as a number. Andrew (RhodiumToad) Gierth sent in two more revisions of a patch to standardize INT64_MIN INT64_MAX UINT64_MAX. Fabien COELHO sent in a patch to fix pgbench --progress report under (very) low rate. Amit Khandekar sent in a patch to reset background worker crash state. Dmitry Voronin sent in a patch to add some new functions to th SSL contrib module. Pavel Stehule sent in another revision of a patch to add an ASSERT statement to SQL. -- Sent via pgsql-announce mailing list (pgsql-announce@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-announce