== PostgreSQL Weekly News - March 22 2015 ==

== 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 david(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot) Spanish language
to pwn(at)arpug(dot)com(dot)ar(dot)

== 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(dot)50709(at)proxel(dot)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(dot)50709(at)proxel(dot)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.