== PostgreSQL Weekly News - March 22 2015 == - Mailing list pgsql-announce

== 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@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.  Spanish language
to pwn@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.50709@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.50709@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.