== PostgreSQL Weekly News - February 12 2017 ==

== PostgreSQL Weekly News - February 12 2017 ==

Bug fix releases 9.6.2, 9.5.6, 9.4.11, 9.3.16 and 9.2.20 are out.
Upgrade at the next available downtime.
https://www.postgresql.org/about/news/1733/

Swiss PGDay in Rapperswil will take place June 30, 2017.
The CfP is open through April 14, 2017.
http://www.pgday.ch/2017/

== PostgreSQL Jobs for February ==

http://archives.postgresql.org/pgsql-jobs/2017-02/

== PostgreSQL Local ==

The first pgDay Israel 2017 will take place on March 2, 2017. Registration is
open.
http://pgday.org.il/

PGConf India 2017 will be on March 2-3, 2017 in Bengaluru, Karnataka.
http://pgconf.in/

PostgreSQL(at)SCaLE will take place on March 2-3, 2017, at Pasadena Convention
Center, as part of SCaLE 15X.
http://www.socallinuxexpo.org/scale/15x/

PgConf.Russia 2017 will take place on 15-17 March 2017 in Moscow.
https://pgconf.ru/en

PGDay Asia 2017 will be held March 17-18 in Singapore.
http://2017.pgday.asia/

Nordic PGDay 2017 will be held in Stockholm, Sweden, at the Sheraton
Hotel, on March 21, 2017.
https://2017.nordicpgday.org/

pgDay Paris 2017 will be held in Paris, France on March 23, 2017.
http://2017.pgday.paris/

PGConf US 2017 CfP will be on March 28-31 in Jersey City, New Jersey.
http://www.pgconf.us/2017/

PGCon 2017 will take place in Ottawa on 23-26 May.
http://www.pgcon.org/2017/papers.php

Postgres Vision will take place in Boston, June 26 - 28, 2017.
the CfP is open until February 24, 2017 at CFP AT PostgresVision DOT com.
http://postgresvision.com/

== 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)

== Applied Patches ==

Heikki Linnakangas pushed:

- Fix typos in comments. Backpatch to all supported versions, where applicable,
to make backpatching of future fixes go more smoothly. Josh Soref Discussion:
https://www.postgresql.org/message-id/CACZqfqCf+5qRztLPgmmosr-B0Ye4srWzzw_mo4c_8_B_mtjmJQ@mail.gmail.com
http://git.postgresql.org/pg/commitdiff/181bdb90ba1f2361aec5a621c8f1e01736f8cad2

- Fix typo in variable name. Masahiko Sawada
http://git.postgresql.org/pg/commitdiff/d02d985349cad066330786bd38e186a569415751

- Fix typo also in expected output. Commit 181bdb90ba fixed the typo in the
.sql file, but forgot to update the expected output.
http://git.postgresql.org/pg/commitdiff/d93b7535a68b03cb8126b930af796af469fdd109

Peter Eisentraut pushed:

- Add missing newline to error messages. Also improve the message style a bit
while we're here.
http://git.postgresql.org/pg/commitdiff/afcb0c97efc58459bcbbe795f42d8b7be414e076

- doc: Update CREATE DATABASE examples. The example of using CREATE DATABASE
with the ENCODING option did not work anymore (except in special
circumstances) and did not represent a good general-purpose example, so write
some new examples. Reported-by: marc+pgsql(at)milestonerdl(dot)com
http://git.postgresql.org/pg/commitdiff/549f74733f45804cd3180de853e5d0610eecc22f

- doc: Document sequence function privileges better. Document the privileges
required for each of the sequence functions. This was already in the GRANT
reference page, but also add it to the function description for easier
reference.
http://git.postgresql.org/pg/commitdiff/696af9ab0a92642978169c227e187a65c2f35f17

- Avoid permission failure in pg_sequences.last_value. Before, reading
pg_sequences.last_value would fail unless the user had appropriate sequence
permissions, which would make the pg_sequences view cumbersome to use.
Instead, return null instead of the real value when there are no permissions.
From: Michael Paquier <michael(dot)paquier(at)gmail(dot)com> Reported-by: Shinoda,
Noriyoshi <noriyoshi(dot)shinoda(at)hpe(dot)com>
http://git.postgresql.org/pg/commitdiff/ab82340a43bebe57a3db0e52bb74120b3bb53ae5

- doc: Some improvements in CREATE SUBSCRIPTION ref page. Add link to
description of libpq connection strings. Add link to explanation of
replication access control. This currently points to the description of
streaming replication access control, which is currently the same as for
logical replication, but that might be refined later. Also remove plain-text
passwords from the examples, to not encourage that dubious practice. based on
suggestions from Simon Riggs
http://git.postgresql.org/pg/commitdiff/e35bbea7ddd89c7f51988fcf03c87150938ea2e3

- Fix relcache leaks in get_object_address_publication_rel().
http://git.postgresql.org/pg/commitdiff/115cb31597fac8a17202d1e41da8baf33fcb60cf

- Add CREATE SEQUENCE AS <data type> clause. This stores a data type, required
to be an integer type, with the sequence. The sequences min and max values
default to the range supported by the type, and they cannot be set to values
exceeding that range. The internal implementation of the sequence is not
affected. Change the serial types to create sequences of the appropriate
type. This makes sure that the min and max values of the sequence for a
serial column match the range of values supported by the table column. So the
sequence can no longer overflow the table column. This also makes monitoring
for sequence exhaustion/wraparound easier, which currently requires various
contortions to cross-reference the sequences with the table columns they are
used with. This commit also effectively reverts the pg_sequence column
reordering in f3b421da5f4addc95812b9db05a24972b8fd9739, because the new
seqtypid column allows us to fill the hole in the struct and create a more
natural overall column ordering. Reviewed-by: Steve Singer
<steve(at)ssinger(dot)info> Reviewed-by: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/2ea5b06c7a7056dca0af1610aadebe608fbcca08

Tom Lane pushed:

- Update comment in relcache.c. Commit 665d1fad9 introduced rd_pkindex, and
made RelationGetIndexList responsible for updating it, but didn't bother to
fix RelationGetIndexList's header comment to say so.
http://git.postgresql.org/pg/commitdiff/a59318346ef476d3e66c4d13e92cf4ad6ce328ac

- Avoid returning stale attribute bitmaps in RelationGetIndexAttrBitmap(). The
problem with the original coding here is that we might receive (and clear) a
relcache invalidation signal for the target relation down inside one of the
index_open calls we're doing. Since the target is open, we would not drop the
relcache entry, just reset its rd_indexvalid and rd_indexlist fields. But
RelationGetIndexAttrBitmap() kept going, and would eventually cache and return
potentially-obsolete attribute bitmaps. The case where this matters is where
the inval signal was from a CREATE INDEX CONCURRENTLY telling us about a new
index on a formerly-unindexed column. (In all other cases, the lock we hold
on the target rel should prevent any concurrent change in index state.) Even
just returning the stale attribute bitmap is not such a problem, because it
shouldn't matter during the transaction in which we receive the signal. What
hurts is caching the stale data, because it can survive into later
transactions, breaking CREATE INDEX CONCURRENTLY's expectation that later
transactions will not create new broken HOT chains. The upshot is that
there's a window for building corrupted indexes during CREATE INDEX
CONCURRENTLY. This patch fixes the problem by rechecking that the set of
index OIDs is still the same at the end of RelationGetIndexAttrBitmap() as it
was at the start. If not, we loop back and try again. That's a little more
than is strictly necessary to fix the bug --- in principle, we could return
the stale data but not cache it --- but it seems like a bad idea on general
principles for relcache to return data it knows is stale. There might be more
hazards of the same ilk, or there might be a better way to fix this one, but
this patch definitely improves matters and seems unlikely to make anything
worse. So let's push it into today's releases even as we continue to study
the problem. Pavan Deolasee and myself Discussion:
https://postgr.es/m/CABOikdM2MUq9cyZJi1KyLmmkCereyGp5JQ4fuwKoyKEde_mzkQ@mail.gmail.com
http://git.postgresql.org/pg/commitdiff/2aaec65464dad497536199dea13612b9232eaa3e

- Release note updates. Add item for last-minute CREATE INDEX CONCURRENTLY fix.
Repair a couple of misspellings of patch authors' names. Back-branch updates
will follow shortly, but I thought I'd commit this separately just to make it
more visible.
http://git.postgresql.org/pg/commitdiff/ad6af3fc4256c0e1eecf5d93d510da4b44e0d480

- Correct thinko in last-minute release note item. The CREATE INDEX
CONCURRENTLY bug can only be triggered by row updates, not inserts, since the
problem would arise from an update incorrectly being made HOT. Noted by
Alvaro.
http://git.postgresql.org/pg/commitdiff/39c3ca5161911cebafbcce160ef89e5a4faaf569

- Speed up "brin" regression test a little bit. In the large DO block, collect
row TIDs into array variables instead of creating and dropping a pile of
temporary tables. In a normal build, this reduces the brin test script's
runtime from about 1.1 sec to 0.4 sec on my workstation. That's not all that
exciting perhaps, but in a CLOBBER_CACHE_ALWAYS test build, the runtime drops
from 20 min to 17 min, which is a little more useful. In combination with
some other changes I plan to propose, this will help provide a noticeable
reduction in cycle time for CLOBBER_CACHE_ALWAYS buildfarm critters.
http://git.postgresql.org/pg/commitdiff/242066cc8e587ccbe5e1cf38c4f085f080dcd5e0

- Fix roundoff problems in float8_timestamptz() and make_interval(). When
converting a float value to integer microseconds, we should be careful to
round the value to the nearest integer, typically with rint(); simply
assigning to an int64 variable will truncate, causing apparently off-by-one
values in cases that should work. Most places in the datetime code got this
right, but not these two. float8_timestamptz() is new as of commit e511d878f
(9.6). Previous versions effectively depended on interval_mul() to do
roundoff correctly, which it does, so this fixes an accuracy regression in
9.6. The problem in make_interval() dates to its introduction in 9.4. Aside
from being careful to round not truncate, let's incorporate the hours and
minutes inputs into the result with exact integer arithmetic, rather than risk
introducing roundoff error where there need not have been any.
float8_timestamptz() problem reported by Erik Nordström, though this is not
his proposed patch. make_interval() problem found by me. Discussion:
https://postgr.es/m/CAHuQZDS76jTYk3LydPbKpNfw9KbACmD=49dC4BrzHcfPv6yA1A@mail.gmail.com
http://git.postgresql.org/pg/commitdiff/8f93bd8512466c9b6c4dbc1e5efd0f72b8e2be9a

- Allow index AMs to cache data across aminsert calls within a SQL command.
It's always been possible for index AMs to cache data across successive
amgettuple calls within a single SQL command: the IndexScanDesc.opaque field
is meant for precisely that. However, no comparable facility exists for
amortizing setup work across successive aminsert calls. This patch adds such
a feature and teaches GIN, GIST, and BRIN to use it to amortize catalog
lookups they'd previously been doing on every call. (The other standard index
AMs keep everything they need in the relcache, so there's little to improve
there.) For GIN, the overall improvement in a statement that inserts many rows
can be as much as 10%, though it seems a bit less for the other two. In
addition, this makes a really significant difference in runtime for
CLOBBER_CACHE_ALWAYS tests, since in those builds the repeated catalog lookups
are vastly more expensive. The reason this has been hard up to now is that
the aminsert function is not passed any useful place to cache per-statement
data. What I chose to do is to add suitable fields to struct IndexInfo and
pass that to aminsert. That's not widening the index AM API very much because
IndexInfo is already within the ken of ambuild; in fact, by passing the same
info to aminsert as to ambuild, this is really removing an inconsistency in
the AM API. Discussion: https://postgr.es/m/27568.1486508680@sss.pgh.pa.us
http://git.postgresql.org/pg/commitdiff/86d911ec0f9d4643e9a47db42510959dec0ed76b

- Blind try to fix portability issue in commit 8f93bd851 et al. The S/390
members of the buildfarm are showing failures indicating that they're having
trouble with the rint() calls I added yesterday. There's no good reason for
that, and I wonder if it is a compiler bug similar to the one we worked around
in d9476b838. Try to fix it using the same method as before, namely to store
the result of rint() back into a "double" variable rather than immediately
converting to int64. (This isn't entirely waving a dead chicken, since on
machines with wider-than-double float registers, the extra store forces a
width conversion. I don't know if S/390 is like that, but it seems worth
trying.) In passing, merge duplicate ereport() calls in float8_timestamptz().
Per buildfarm.
http://git.postgresql.org/pg/commitdiff/5d2adf0f81a2e4ca4f101b19b1efea147b462301

Robert Haas pushed:

- Cache hash index's metapage in rel->rd_amcache. This avoids a very
significant amount of buffer manager traffic and contention when scanning hash
indexes, because it's no longer necessary to lock and pin the metapage for
every scan. We do need some way of figuring out when the cache is too stale
to use any more, so that when we lock the primary bucket page to which the
cached metapage points us, we can tell whether a split has occurred since we
cached the metapage data. To do that, we use the hash_prevblkno field in the
primary bucket page, which would otherwise always be set to InvalidBuffer.
This patch contains code so that it will continue working (although less
efficiently) with hash indexes built before this change, but perhaps we should
consider bumping the hash version and ripping out the compatibility code.
That decision can be made later, though. Mithun Cy, reviewed by Jesper
Pedersen, Amit Kapila, and by me. Before committing, I made a number of
cosmetic changes to the last posted version of the patch, adjusted
_hash_getcachedmetap to be more careful about order of operation, and made
some necessary updates to the pageinspect documentation and regression tests.
http://git.postgresql.org/pg/commitdiff/293e24e507838733aba4748b514536af2d39d7f2

- Fix compiler warning. Mithun Cy, per a report by Erik Rijkers
http://git.postgresql.org/pg/commitdiff/94708c0e8c32ad1c9c6ffbdb894fe158eda596e7

- Allow the element allocator for a simplehash to be specified. This is
infrastructure for a pending patch to allow parallel bitmap heap scans. Dilip
Kumar, reviewed (in earlier versions) by Andres Freund and (more recently) by
me. Some further renaming by me, also.
http://git.postgresql.org/pg/commitdiff/565903af474e85cef28ff712d773f13b6701ded5

- Avoid redefining simplehash_allocate/simplehash_free. There's no generic
guard against multiple inclusion in this file, for good reason. But these
typedefs need one, as per a report from Jeff Janes.
http://git.postgresql.org/pg/commitdiff/ac8eb972f268c787bfe7579b1747c3866fced5a9

- Revise the way the element allocator for a simplehash is specified. This
method is more elegant and more efficient. Per a suggestion from Andres
Freund, who also briefly reviewed the patch.
http://git.postgresql.org/pg/commitdiff/c3c4f6e1740be256178cd7551d5b8a7677159b74

- Add WAL consistency checking facility. When the new GUC
wal_consistency_checking is set to a non-empty value, it triggers recording of
additional full-page images, which are compared on the standby against the
results of applying the WAL record (without regard to those full-page images).
Allowable differences such as hints are masked out, and the resulting pages
are compared; any difference results in a FATAL error on the standby. Kuntal
Ghosh, based on earlier patches by Michael Paquier and Heikki Linnakangas.
Extensively reviewed and revised by Michael Paquier and by me, with additional
reviews and comments from Amit Kapila, Álvaro Herrera, Simon Riggs, and Peter
Eisentraut.
http://git.postgresql.org/pg/commitdiff/a507b86900f695aacc8d52b7d2cfcb65f58862a2

- pageinspect: Fix hash_bitmap_info not to read the underlying page. It did
that to verify that the page was an overflow page rather than anything else,
but that means that checking the status of all the overflow bits requires
reading the entire index. So don't do that. The new code validates that the
page is not a primary bucket page or bitmap page by looking at the metapage,
so that using this on large numbers of pages can be reasonably efficient.
Ashutosh Sharma, per a complaint from me, and with further modifications by
me.
http://git.postgresql.org/pg/commitdiff/fc8219dc54c95ea673560b786aa8123ce6ec5977

- Fix race condition in ConditionVariablePrepareToSleep. Thomas Munro
http://git.postgresql.org/pg/commitdiff/3f3d60d3bbd10f6cc118d24aadc60e96b9854576

- simplehash: Additional tweaks to make specifying an allocator work. Even if
we don't emit definitions for SH_ALLOCATE and SH_FREE, we still need
prototypes. The user can't define them before including simplehash.h because
SH_TYPE isn't available yet. For the allocator to be able to access
private_data, it needs to become an argument to SH_CREATE. Previously we
relied on callers to set that after returning from SH_CREATE, but SH_CREATE
calls SH_ALLOCATE before returning. Dilip Kumar, reviewed by me.
http://git.postgresql.org/pg/commitdiff/72257f95781af97108fa9a9e7224ec81a90e7693

- Remove all references to "xlog" from SQL-callable functions in pg_proc.
Commit f82ec32ac30ae7e3ec7c84067192535b2ff8ec0e renamed the pg_xlog directory
to pg_wal. To make things consistent, and because "xlog" is terrible
terminology for either "transaction log" or "write-ahead log" rename all
SQL-callable functions that contain "xlog" in the name to instead contain
"wal". (Note that this may pose an upgrade hazard for some users.) Similarly,
rename the xlog_position argument of the functions that create slots to be
called wal_position. Discussion:
https://www.postgresql.org/message-id/CA+Tgmob=YmA=H3DbW1YuOXnFVgBheRmyDkWcD9M8f=5bGWYEoQ@mail.gmail.com
http://git.postgresql.org/pg/commitdiff/806091c96f9b81f7631e4e37a05af377b473b5da

- Rename user-facing tools with "xlog" in the name to say "wal". This means
pg_receivexlog because pg_receivewal, pg_resetxlog becomes pg_resetwal, and
pg_xlogdump becomes pg_waldump.
http://git.postgresql.org/pg/commitdiff/85c11324cabaddcfaf3347df78555b30d27c5b5a

- Rename dtrace probes for ongoing xlog -> wal conversion. xlog-switch becomes
wal-switch, and xlog-insert becomes wal-insert.
http://git.postgresql.org/pg/commitdiff/3f01fd4ca0b4c81333b1f0dadb09c73aa589ab6e

- Rename command line options for ongoing xlog -> wal conversion. initdb and
pg_basebackup now have a --waldir option rather --xlogdir, and pg_basebackup
now has --wal-method rather than --xlog-method.
http://git.postgresql.org/pg/commitdiff/62e8b387514ce965c8b3d67c81990e0ecf8c9b83

Andres Freund pushed:

- Add explicit ORDER BY to a few tests that exercise hash-join code. A proposed
patch, also by Thomas and in the same thread, would change the output order of
these. Independent of the follow-up patches getting committed, nailing down
the order in these specific tests at worst seems harmless. Author: Thomas
Munro Discussion:
https://postgr.es/m/CAEepm=1D4-tP7j7UAgT_j4ZX2j4Ehe1qgZQWFKBMb8F76UW5Rg@mail.gmail.com
http://git.postgresql.org/pg/commitdiff/7c5d8c16e12e56c1043ff4a28e07a306a15c2b85

Simon Riggs pushed:

- Improve CREATE TABLE documentation of partitioning. Amit Langote, with
corrections by me
http://git.postgresql.org/pg/commitdiff/c1369fe2ea79724e56ee55e4c87778afd6016942

- Add keywords for partitioning. Amit Langote
http://git.postgresql.org/pg/commitdiff/61c9a9dac44c0f0c24342a4fa4f750756ac72f2f

- Update ddl.sgml for declarative partitioning syntax. Add a section titled
"Partitioned Tables" to describe what are partitioned tables, partition, their
similarities with inheritance. The existing section on inheritance is
retained for clarity. Then add examples to the partitioning chapter that show
syntax for partitioned tables. In fact they implement the same partitioning
scheme that is currently shown using inheritance. Amit Langote, with
additional details and explanatory text by me
http://git.postgresql.org/pg/commitdiff/9401883a7a598f1f1664c74835821f697932666f

Noah Misch pushed:

- Ignore tablespace ACLs when ignoring schema ACLs. The ALTER TABLE ALTER TYPE
implementation can issue DROP INDEX and CREATE INDEX to refit existing indexes
for the new column type. Since this CREATE INDEX is an implementation detail
of an index alteration, the ensuing DefineIndex() should skip ACL checks
specific to index creation. It already skips the namespace ACL check. Make
it skip the tablespace ACL check, too. Back-patch to 9.2 (all supported
versions). Reviewed by Tom Lane.
http://git.postgresql.org/pg/commitdiff/f30f34e5897b64e0fb6616154c11dc9765866046

== Pending Patches ==

Amit Khandekar sent in two more revisions of a patch to implement Parallel
Append.

Boris Muratshin sent in a patch to implement 3D Z-curve spatial indexes.

Corey Huinker sent in five more revisions of a patch to implement \if and
friends in psql.

Nikita Glukhov sent in another revision of a patch to implement KNN for B-trees.

Nikita Glukhov sent in another revision of a patch to implement KNN for SP-GiST.

Beena Emerson sent in another revision of a patch to allow increasing the
default WAL segment size.

Nikolay Shaplov sent in another revision of a patch to move all am-related
reloption code into src/backend/access/[am-name] and get rid of relopt_kind for
custom AM.

Fujii Masao sent in a patch to fix a bug that made it impossible to shut down
a subscriber after DROP SUBSCRIPTION.

Heikki Linnakangas sent in another revision of a patch to implement SCRAM
authentication.

Christoph Berg sent in two more revisions of a patch to implement \gx, a
one-shot expanded output for queries, in psql.

Mithun Cy sent in two more revisions of a patch to implement auto_prewarm.

Rafia Sabih sent in two more revisions of a patch to enable passing query string
to workers.

Takeshi Ideriha sent in another revision of a patch to enable DECLARE STATEMENT
to set up a connection in ECPG.

Peter Eisentraut sent in a patch to drop Python 2.3 support.

Pavel Raiskup sent in two revisions of a patch to create a configure-time knob
to set default ssl ciphers.

Naoki Okano sent in a patch to implement CREATE OR REPLACE TRIGGER.

Piotr Stefaniak sent in a patch to pg_bsd_indent to implement -lps ("leave
preprocessor space").

Dilip Kumar and Robert Haas traded patches to implement parallel bitmap heap
scan.

Masahiko Sawada sent in two revisions of a patch to stop the apply worker after
DROP SUBSCRIPTION is committed.

Peter Eisentraut sent in a patch to systematically trim the trailing newlines
off PQerrorMessage() results in backend uses (dblink, postgres_fdw,
libpqwalreceiver).

Peter Eisentraut sent in a patch to implement CREATE COLLATION IF NOT EXISTS.

Michaël Paquier sent in a patch to implement SASLprep(), or NFKC if you want for
UTF-8 strings.

Kyle Gearhart sent in a patch to implement an alternate row processor for libpq
which is faster for certain use cases than the default one.

Amit Langote sent in two revisions of a patch to implement a check partition
strategy in ATExecDropNotNull.

Andres Freund sent in a patch to speed up expression processing, including
several JIT PoCs.

Pavel Stěhule sent in two revisions of a patch to enable specifying a template
database for pg_regress.

Petr Jelínek sent in another revision of a patch to enable existing data copy
for logical replication.

Amit Kapila sent in two more revisions of a patch to implement parallel index
scans.

Amit Kapila sent in another revision of a patch to implement WAL for hash
indexes.

Amit Langote sent in a patch to optimize partitioned tables by noting that
top-level tables are always empty and avoiding that anything that might write to
them can't.

Amit Langote sent in a patch to add relkind checks to certain contrib modules.

Peter Geoghegan sent in a patch to add parallel B-tree index build sorting with
some testing tools.

Ashutosh Bapat sent in three more revisions of a patch to speed up
partition-wise joins on declaratively partitioned tables.

Amit Kapila sent in another revision of a patch to parallelize queries
containing initplans.

Simon Riggs sent in a patch to make log_autovacuum_min_duration log the
durations of vacuums whether or not they were launched by autovacuum workers.

Simon Riggs sent in a patch to enable reporting xmin from VACUUMs.

Peter Geoghegan sent in a patch to add amcheck extension to contrib.

Michael Banck sent in three revisions of a patch to better document
pg_basebackup's behavior in certain corner cases.

Andreas Karlsson sent in another revision of a patch to implement REINDEX
CONCURRENTLY.

Tom Lane sent in a patch to preprocess join OR clauses that might be better
handled as UNIONs.

Magnus Hagander sent in a patch to enable having fallback servers RADIUS auth.