== PostgreSQL Weekly News - January 03 2016 ==

== PostgreSQL Weekly News - January 03 2016 ==

== PostgreSQL Product News ==

Database Designer for PostgreSQL 1.11.0 released.
http://microolap.com/products/database/postgresql-designer/download/

pglogical, a logical-WAL-based replication system, released.
http://2ndquadrant.com/pglogical

== PostgreSQL Jobs for January ==

http://archives.postgresql.org/pgsql-jobs/2016-01/

== PostgreSQL Local ==

PostgreSQL(at)SCaLE is a two day, two track event which takes place on Jan.
21-22, 2016, at Pasadena Convention Center, as part of SCaLE 14X.
https://www.socallinuxexpo.org/scale/14x/cfp

FOSDEM PGDay is a one day conference that will be held ahead of FOSDEM in
Brussels, Belgium, on Jan 29th, 2016. Details and CfP below:
http://fosdem2016.pgconf.eu/

Prague PostgreSQL Developer Day 2016 (P2D2 2016) is a two-day conference
that will be held on February 17-18 2016 in Prague, Czech Republic.
Czech language web site below:
http://www.p2d2.cz/

The annual Indian PGday will be held in Bengaluru, Karnataka, India on
February 26, 2016. The CfP is open.
http://pgday.in

The first pan-Asian PostgreSQL conference will be held March 16-17,
2016 in Singapore. The CfP is open.
http://2016.pgday.asia/

Nordic PGDay 2016 is a one day one track conference which will be held in
Helsinki, Finland, on March 17, 2016. The CfP is currently open.
http://2016.nordicpgday.org/

PGConf US 2016 will take place April 18-20, 2016 in NYC. The CfP is
open until January 31st, 2016, 11:59pm EST.
http://www.pgconf.us/2016/

PGCon 2016 will be held May 17-21, 2016 in Ottawa. The CfP is out.
http://www.pgcon.org/

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

Álvaro Herrera pushed:

- Fix translation domain in pg_basebackup. For some reason, we've
been overlooking the fact that pg_receivexlog and pg_recvlogical are
using wrong translation domains all along, so their output hasn't
ever been translated. The right domain is pg_basebackup, not their
own executable names. Noticed by Ioseph Kim, who's been working on
the Korean translation. Backpatch pg_receivexlog to 9.2 and
pg_recvlogical to 9.4.
http://git.postgresql.org/pg/commitdiff/fc995bfdbfa52ddc50a6737ae4cc37be39215dd8

- doc: pg_committs -> pg_commit_ts. Reported by: Alain Laporte (#13836)
http://git.postgresql.org/pg/commitdiff/151c4ffe41a7a6fc76df373e7bfbbb59e8d694d6

- Document brin_summarize_new_pages. Pointer out by Jeff Janes
http://git.postgresql.org/pg/commitdiff/ac443d1034d9888c543ae8147befc51cf4eb7839

Tom Lane pushed:

- Update documentation about pseudo-types. Tone down an overly strong
statement about which pseudo-types PLs are likely to allow. Add
"event_trigger" to the list, as well as "pg_ddl_command" in
9.5/HEAD. Back-patch to 9.3 where event_trigger was added.
http://git.postgresql.org/pg/commitdiff/731dfc7d5f07fac2c3c72f47c29a947e363acee9

- Fix omission of -X (--no-psqlrc) in some psql invocations. As of
commit d5563d7df, psql -c no longer implies -X, but not all of our
regression testing scripts had gotten that memo. To ensure
consistency of results across different developers, make sure that
*all* invocations of psql in all scripts in our tree use -X, even
where this is not what previously happened. Michael Paquier and Tom
Lane
http://git.postgresql.org/pg/commitdiff/870df2b3b77414a536d6533566628f11f8f309ec

- Document the exponentiation operator as associating left to right.
Common mathematical convention is that exponentiation associates
right to left. We aren't going to change the parser for this, but
we could note it in the operator's description. (It's already noted
in the operator precedence/associativity table, but users might not
look there.) Per bug #13829 from Henrik Pauli.
http://git.postgresql.org/pg/commitdiff/54aaafe95f65c95fd9ba085826af87d778c94613

- Code and docs review for cube kNN support. Commit
33bd250f6c4cc309f4eeb657da80f1e7743b3e5c could have done with some
more review: Adjust coding so that compilers unfamiliar with
elog/ereport don't complain about uninitialized values. Fix misuse
of PG_GETARG_INT16 to retrieve arguments declared as "integer" at
the SQL level. (This was evidently copied from cube_ll_coord and
cube_ur_coord, but those were wrong too.) Fix
non-style-guide-conforming error messages. Fix underparenthesized
if statements, which pgindent would have made a hash of, and remove
some unnecessary parens elsewhere. Run pgindent over new code.
Revise documentation: repeated accretion of more operators without
any rethinking of the text already there had left things in a bit of
a mess. Merge all the cube operators into one table and adjust
surrounding text appropriately. David Rowley and Tom Lane
http://git.postgresql.org/pg/commitdiff/81ee726d87ec67c4f2846110c99f72e8a20dcd07

- Put back one copyObject() in rewriteTargetView(). Commit
6f8cb1e23485bd6d tried to centralize rewriteTargetView's copying of
a target view's Query struct. However, it ignored the fact that the
jointree->quals field was used twice. This only accidentally failed
to fail immediately because the same ChangeVarNodes mutation is
applied in both cases, so that we end up with logically identical
expression trees for both uses (and, as the code stands, the second
ChangeVarNodes call actually does nothing). However, we end up
linking *physically* identical expression trees into both an RTE's
securityQuals list and the WithCheckOption list. That's pretty
dangerous, mainly because prepsecurity.c is utterly cavalier about
further munging such structures without copying them first. There
may be no live bug in HEAD as a consequence of the fact that we
apply preprocess_expression in between here and prepsecurity.c, and
that will make a copy of the tree anyway. Or it may just be that
the regression tests happen to not trip over it. (I noticed this
only because things fell over pretty badly when I tried to relocate
the planner's call of expand_security_quals to before expression
preprocessing.) In any case it's very fragile because if anyone
tried to make the securityQuals and WithCheckOption trees diverge
before we reach preprocess_expression, it would not work. The fact
that the current code will preprocess securityQuals and
WithCheckOptions lists at completely different times in different
query levels does nothing to increase my trust that that can't
happen. In view of the fact that 9.5.0 is almost upon us and the
aforesaid commit has seen exactly zero field testing, the prudent
course is to make an extra copy of the quals so that the behavior is
not different from what has been in the field during beta.
http://git.postgresql.org/pg/commitdiff/fd1952575618cacf7afa544d8b89ddb77be9eaee

- Add some comments about division of labor between rewriter and
planner. The rationale for the way targetlist processing is done
wasn't clearly stated anywhere, and I for one had forgotten some of
the details. Having just painfully re-learned them, add some
breadcrumbs for the next person.
http://git.postgresql.org/pg/commitdiff/efe4c9d7049f0bf832b792bfad05c92aaf86aa3c

- Minor hacking on contrib/cube documentation. Improve markup,
particularly of the table of functions; add or improve examples for
some of the functions; wordsmith some of the function descriptions.
http://git.postgresql.org/pg/commitdiff/e5e5267a91f4880c121bf50865cbc38078441989

- Avoid useless truncation attempts during VACUUM. VACUUM can skip
heap pages altogether when there's a run of consecutive pages that
are all-visible according to the visibility map. This causes it to
not update its nonempty_pages count, just as if those pages were
empty, which means that at the end we will think they are candidates
for deletion. Thus, we may take the table's AccessExclusive lock
only to find that no pages are really truncatable. This usually
causes no real problems on a master server, thanks to the lock being
acquired only conditionally; but on hot-standby servers, the same
lock must be acquired unconditionally which can result in
unnecessary query cancellations. To improve matters, force
examination of the table's last page whenever we reach there with a
nonempty_pages count that would allow a truncation attempt. If it's
not empty, we'll advance nonempty_pages and thereby prevent the
truncation attempt. If we are unable to acquire cleanup lock on
that page, there's no need to force it, unless we're doing an
anti-wraparound vacuum. We can just check for tuples with a shared
buffer lock and then give up. (When we are doing an anti-wraparound
vacuum, and decide it's okay to skip the page because it contains no
freezable tuples, this patch still improves matters because
nonempty_pages is properly updated, which it was not before.) Since
only the last page is special-cased in this way, we might attempt a
truncation that will release many fewer pages than the normal
heuristic would suggest; at worst, only one page would be truncated.
But that seems all right, because the situation won't repeat during
the next vacuum. The real problem with the old logic is that the
useless truncation attempt happens every time we vacuum, so long as
the state of the last few dozen pages doesn't change. This is a
longstanding deficiency, but since the consequences aren't very
severe in most scenarios, I'm not going to risk a back-patch. Jeff
Janes and Tom Lane
http://git.postgresql.org/pg/commitdiff/e842908233bb9c5cea0e765fc828b52badd8228e

- Dept of second thoughts: the !scan_all exit mustn't increase
scanned_pages. In the extreme edge case where contended pages are
the only ones that escape being scanned, the previous commit would
have allowed us to think that relfrozenxid could be advanced, which
is exactly wrong.
http://git.postgresql.org/pg/commitdiff/e5d06f2b12a7c75f2b0c7fd2055a14efaa2b59ec

- Fix ALTER OPERATOR to update dependencies properly. Fix an
oversight in commit 321eed5f0f7563a0: replacing an operator's
selectivity functions needs to result in a corresponding update in
pg_depend. We have a function that can handle that, but it was not
called by AlterOperator(). To fix this without enlarging
pg_operator.h's #include list beyond what clients can safely
include, split off the function definitions into a new file
pg_operator_fn.h, similarly to what we've done for some other
catalog header files. It's not entirely clear whether any
client-side code needs to include pg_operator.h, but it seems
prudent to assume that there is some such code somewhere.
http://git.postgresql.org/pg/commitdiff/0dab5ef39b3d9d86e45bbbb2f6ea60b4f5517d9a

- Add a comment noting that FDWs don't have to implement EXCEPT or
LIMIT TO. postgresImportForeignSchema pays attention to IMPORT's
EXCEPT and LIMIT TO options, but only as an efficiency hack, not for
correctness' sake. The FDW documentation does explain that, but
someone using postgres_fdw.c as a coding guide might not remember
it, so let's add a comment here. Per question from Regina Obe.
http://git.postgresql.org/pg/commitdiff/5f36096b77fe47015cbac130d1a20d089f202a1e

- Add some more defenses against silly estimates to gincostestimate().
A report from Andy Colson showed that gincostestimate() was not
being nearly paranoid enough about whether to believe the statistics
it finds in the index metapage. The problem is that the metapage
stats (other than the pending-pages count) are only updated by
VACUUM, and in the worst case could still reflect the index's
original empty state even when it has grown to many entries. We
attempted to deal with that by scaling up the stats to match the
current index size, but if nEntries is zero then scaling it up still
gives zero. Moreover, the proportion of pages that are entry pages
vs. data pages vs. pending pages is unlikely to be estimated very
well by scaling if the index is now orders of magnitude larger than
before. We can improve matters by expanding the use of the
rule-of-thumb estimates I introduced in commit 7fb008c5ee59b040: if
the index has grown by more than a cutoff amount (here set at 4X
growth) since VACUUM, then use the rule-of-thumb numbers instead of
scaling. This might not be exactly right but it seems much less
likely to produce insane estimates. I also improved both the
scaling estimate and the rule-of-thumb estimate to account for
numPendingPages, since it's reasonable to expect that that is
accurate in any case, and certainly pages that are in the pending
list are not either entry or data pages. As a somewhat separate
issue, adjust the estimation equations that are concerned with extra
fetches for partial-match searches. These equations suppose that a
fraction partialEntries / numEntries of the entry and data pages
will be visited as a consequence of a partial-match search. Now,
it's physically impossible for that fraction to exceed one, but our
estimate of partialEntries is mostly bunk, and our estimate of
numEntries isn't exactly gospel either, so we could arrive at a
silly value. In the example presented by Andy we were coming out
with a value of 100, leading to insane cost estimates. Clamp the
fraction to one to avoid that. Like the previous patch, back-patch
to all supported branches; this problem can be demonstrated in one
form or another in all of them.
http://git.postgresql.org/pg/commitdiff/3c93a60f6019768f5742b7893a93db93fb67e71f

- Teach flatten_reloptions() to quote option values safely.
flatten_reloptions() supposed that it didn't really need to do
anything beyond inserting commas between reloption array elements.
However, in principle the value of a reloption could be nearly
anything, since the grammar allows a quoted string there. Any
restrictions on it would come from validity checking appropriate to
the particular option, if any. A reloption value that isn't a
simple identifier or number could thus lead to dump/reload failures
due to syntax errors in CREATE statements issued by pg_dump. We've
gotten away with not worrying about this so far with the
core-supported reloptions, but extensions might allow reloption
values that cause trouble, as in bug #13840 from Kouhei Sutou. To
fix, split the reloption array elements explicitly, and then convert
any value that doesn't look like a safe identifier to a string
literal. (The details of the quoting rule could be debated, but
this way is safe and requires little code.) While we're at it, also
quote reloption names if they're not safe identifiers; that may not
be a likely problem in the field, but we might as well try to be
bulletproof here. It's been like this for a long time, so
back-patch to all supported branches. Kouhei Sutou, adjusted some
by me
http://git.postgresql.org/pg/commitdiff/c7e27becd2e6eb93b20965b9f22701eaad42a764

- Update copyright for 2016 Manually fix some copyright lines missed
by the automated script.
http://git.postgresql.org/pg/commitdiff/ad08bf5c8b96c2a3a70d96f5be1c04cb83b4ed6e

- Update copyright for 2016 On closer inspection, the reason
copyright.pl was missing files is that it is looking for 'Copyright
(c)' and they had 'Copyright (C)'. Fix that, and update a couple
more that grepping for that revealed.
http://git.postgresql.org/pg/commitdiff/48c9f2889a4ad25a771d13b88f2778a306f2d970

- Make copyright.pl cope with nonstandard case choices in copyright
notices. The need for this is shown by the files it missed in
Bruce's recent run. I fixed it so that it will actually adjust the
case when needed. In passing, also make it skip .po files, since
those will just get overwritten anyway from the translation
repository.
http://git.postgresql.org/pg/commitdiff/de7c8dbea1a17a0e1709c4b12371615d28e21c13

- Adjust back-branch release note description of commits a2a718b22 et
al. As pointed out by Michael Paquier, recovery_min_apply_delay
didn't exist in 9.0-9.3, making the release note text not very
useful. Instead make it talk about recovery_target_xid, which did
exist then. 9.0 is already out of support, but we can fix the text
in the newer branches' copies of its release notes.
http://git.postgresql.org/pg/commitdiff/df35af2ca7b5545d32b978a88b665bac2b9fa638

- Fix overly-strict assertions in spgtextproc.c.
spg_text_inner_consistent is capable of reconstructing an empty
string to pass down to the next index level; this happens if we have
an empty string coming in, no prefix, and a dummy node label. (In
practice, what is needed to trigger that is insertion of a whole
bunch of empty-string values.) Then, we will arrive at the next
level with in->level == 0 and a non-NULL (but zero length)
in->reconstructedValue, which is valid but the Assert tests weren't
expecting it. Per report from Andreas Seltenreich. This has no
impact in non-Assert builds, so should not be a problem in
production, but back-patch to all affected branches anyway. In
passing, remove a couple of useless variable initializations and
shorten the code by not duplicating DatumGetPointer() calls.
http://git.postgresql.org/pg/commitdiff/7157fe80f42476db249e062b4f6eef6a3994b234

- Teach pg_dump to quote reloption values safely. Commit
c7e27becd2e6eb93 fixed this on the backend side, but we neglected
the fact that several code paths in pg_dump were printing reloptions
values that had not gotten massaged by ruleutils. Apply essentially
the same quoting logic in those places, too.
http://git.postgresql.org/pg/commitdiff/b416c0bb622ce5d33fdbec3bbce00451132f10ec

- Fix treatment of *lpNumberOfBytesRecvd == 0: that's a completion
condition. pgwin32_recv() has treated a non-error return of zero
bytes from WSARecv() as being a reason to block ever since the
current implementation was introduced in commit a4c40f140d23cefb.
However, so far as one can tell from Microsoft's documentation, that
is just wrong: what it means is graceful connection closure (in
stream protocols) or receipt of a zero-length message (in message
protocols), and neither case should result in blocking here. The
only reason the code worked at all was that control then fell into
the retry loop, which did *not* treat zero bytes specially, so we'd
get out after only wasting some cycles. But as of 9.5 we do not
normally reach the retry loop and so the bug is exposed, as reported
by Shay Rojansky and diagnosed by Andres Freund. Remove the
unnecessary test on the byte count, and rearrange the code in the
retry loop so that it looks identical to the initial sequence.
Back-patch to 9.5. The code is wrong all the way back, AFAICS, but
since it's relatively harmless in earlier branches we'll leave it
alone.
http://git.postgresql.org/pg/commitdiff/90e61df8130dc7051a108ada1219fb0680cb3eb6

- Do a final round of copy-editing on the 9.5 release notes.
http://git.postgresql.org/pg/commitdiff/027989197aab9e555328721b003ebd1839a16704

- Do some copy-editing on the docs for replication origins. Minor
grammar and markup improvements.
http://git.postgresql.org/pg/commitdiff/c6aeba353a15d71f584488a7482fb88337f843e3

- Guard against null arguments in
binary_upgrade_create_empty_extension(). The
CHECK_IS_BINARY_UPGRADE macro is not sufficient security protection
if we're going to dereference pass-by-reference arguments before it.
But in any case we really need to explicitly check PG_ARGISNULL for
all the arguments of a non-strict function, not only the ones we
expect null values for. Oversight in commits
30982be4e5019684e1772dd9170aaa53f5a8e894 and
f92fc4c95ddcc25978354a8248d3df22269201bc. Found by Andreas
Seltenreich. (The other usages in pg_upgrade_support.c seem safe.)
http://git.postgresql.org/pg/commitdiff/939d10cd8711fdeb7f0ff62c9c6b08e3eddbba3e

Joe Conway pushed:

- Rename (new|old)estCommitTs to (new|old)estCommitTsXid. The
variables newestCommitTs and oldestCommitTs sound as if they are
timestamps, but in fact they are the transaction Ids that correspond
to the newest and oldest timestamps rather than the actual
timestamps. Rename these variables to reflect that they are
actually xids: to wit newestCommitTsXid and oldestCommitTsXid
respectively. Also modify related code in a similar fashion,
particularly the user facing output emitted by pg_controldata and
pg_resetxlog. Complaint and patch by me, review by Tom Lane and
Alvaro Herrera. Backpatch to 9.5 where these variables were first
introduced.
http://git.postgresql.org/pg/commitdiff/241448b23adf3432988f2b4012ff90a338b4d0bf

Peter Eisentraut pushed:

- doc: Add index entries and better documentation link for Linux OOM
http://git.postgresql.org/pg/commitdiff/805ac78aaba1c5f71e878c3bc50bec8b82e076bb

- doc: Remove redundant duplicate URLs from ulink elements. Empty
ulink elements default to displaying the URL, so there is no need to
specify the URL again. This was already done for most occurrences,
but some cases didn't follow this convention.
http://git.postgresql.org/pg/commitdiff/253de19b8414b8b91e0fd710c2b492dc6375f9fa

Noah Misch pushed:

- Fix comments about WAL rule "write xlog before data" versus
pg_multixact. Recovery does not achieve its goal of zeroing all
pg_multixact entries whose accompanying WAL records never reached
disk. Remove that claim and justify its expendability. Detail the
need for TrimMultiXact(), which has little in common with the
TrimCLOG() rationale. Merge two tightly-related comments. Stop
presenting pg_multixact as specific to heap_lock_tuple(); PostgreSQL
9.3 extended its use to heap_update(). Noticed while investigating
a report from Andres Freund.
http://git.postgresql.org/pg/commitdiff/3cd1ba147e5619199914e5b71e0edbd188a763d2

- Cover heap_page_prune_opt()'s cleanup lock tactic in README. Jeff
Janes, reviewed by Jim Nasby.
http://git.postgresql.org/pg/commitdiff/dfcd9cb30237f882b7308bdcbfb0318b22b1e224

Bruce Momjian pushed:

- Update copyright for 2016 Backpatch certain files through 9.1
http://git.postgresql.org/pg/commitdiff/ee943004466418595363d567f18c053bae407792

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Peter Geoghegan sent in another revision of a patch to use quicksort
when performing external sorts.

Jeff Janes sent in another revision of a patch to add a
gin_clean_pending_list() function.

David Rowley sent in two more revisions of a patch to fix some typos.

Grzegorz Sampolski sent in a patch to add rhost as an option for PAM
auth.

Haribabu Kommi and Alexander Shulgin traded patches to add a
pg_hba_lookup() function.

Dmitry Ivanov sent in a patch to add an array_elemtype() function.

Aleksander Alekseev sent in three more revisions of a patch to fix
lock contention for HASHHDR.mutex.

Andreas Karlsson sent in a patch to implement tab completion in psql
for COPY with a query.

Joe Conway sent in another revision of a patch to expose
pg_controldata and pg_config as functions.

Haribabu Kommi sent in another revision of a patch to implement
multi-tenancy with RLS.

Thomas Munro sent in another revision of a patch to implement causal
reads.

Tom Lane sent in a patch to better detail logging for password auth
failures.

Michael Paquier sent in three revisions of a patch to add support for
a case-sensitive comparison facility to psql's tab completion.

Bruce Momjian sent in a patch to ensure that the correct .h files are
installed on Windows.

Amit Kapila sent in two more revisions of a patch to refactor LWLock
tranches.

Andreas Karlsson sent in a patch to improve psql's tab completion for
FDW DDL.

Stas Kelvich sent in another revision of a patch to add Tsvector
editing functions.

Pavel Stěhule sent in another revision of a patch to add a custom
function for converting human-readable sizes to bytes.

Álvaro Herrera sent in another revision of a patch to implement column
stores.

Corey Huinker sent in another revision of a patch to allow FETCH to be
limited by a specification measured in bytes rather than rows.

Tom Lane sent in a patch to improve the rows estimate for BRIN
indexes.

Pavel Stěhule sent in another revision of a patch to make a PL/PythonU
version of ereport().

Dilip Kumar sent in another revision of a patch to scale relation
extensibility.

David Steele sent in a patch to add a pg_audit extension.

Tomas Vondra sent in another revision of a patch to postpone building
buckets to the end of Hash (in HashJoin).

Tomas Vondra sent in another revision of a patch to add a Bloom filter
in Hash Joins with batches.

Tomas Vondra sent in a patch to extend the hyperloglog API by adding
initHyperLogLogError() and freeHyperLogLog().

Petr Jelínek sent in a patch to add pglogical, a logical replication
contrib module.

Petr Jelínek sent in another revision of a patch to add a sequence
access method.

Petr Jelínek sent in a patch to fix a copy-paste error in the logical
decoding docs.

Petr Jelínek sent in a patch to implement generic WAL logical messages.

Simon Riggs sent in a WIP patch to implement failover slots.

Michael Paquier sent in another revision of a patch to fix how pg_dump
locks tables.

Jim Nasby sent in two revisions of a patch to improve error reporting
in format().

Amit Kapila and Andres Freund traded patches to fix an issue with
backend processes not terminating properly.

SAWADA Masahiko sent in another revision of a patch to support N
synchronous standby servers for N>1.

Simon Riggs sent in another revision of a patch to avoid standby pin
scans.

Andreas Karlsson sent in two approaches to a patch to fix some
infelicities between psql's \x auto feature and EXPLAIN.

Pavel Stěhule sent in another revision of a patch to implement a
num_nulls() function.