== PostgreSQL Weekly News - September 13 2015 ==

== PostgreSQL Weekly News - September 13 2015 ==

== PostgreSQL Product News ==

pgbouncer 1.6.1, a light-weight connection pooler for PostgreSQL,
released. This is a security upgrade.
https://pgbouncer.github.io/2015/09/pgbouncer-1-6-1/

== PostgreSQL Jobs for September ==

http://archives.postgresql.org/pgsql-jobs/2015-09/

== PostgreSQL Local ==

PostgresOpen 2015 will being held in Dallas, Texas September 16-18.
http://2015.postgresopen.org/

PostgreSQL Session #7, will be held September 24th, 2015 in Paris,
France,
http://www.postgresql-sessions.org/7/about

PGDay.IT 2015 will take place in Prato on October 23, 2015.
http://pgday.it

PostgreSQL Conference Europe 2015 will be held on October 27-30 in the
Vienna Marriott Hotel, in Vienna, Austria. The schedule is posted.
http://2015.pgconf.eu/

PGConf Silicon Valley 2015 is November 17-18 at the South San
Francisco Convention Center.
http://www.pgconfsv.com

PgCUBA will take place October 19-23, 2015 in Havana. Spanish
language information below:
http://www.postgresql.org/about/event/1813/

PGBR2015 (The Brazilian PostgreSQL Conference) will take place in Porto
Alegre, Rio Grande do Sul, on November 18, 19 and 20. The CfP is open
until August 31.
http://pgbr.postgresql.org.br/2015/en/#call-for-papers

PGConf.DE will be held on November 26-27, 2015 in Hamburg, Germany, at
the Lindner Hotel am Michel.
http://2015.pgconf.de/

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

Teodor Sigaev pushed:

- Update site address of Snowball project
http://git.postgresql.org/pg/commitdiff/a1c44e1af62f7247610ee80bb355ef7084c24001

- Add pages deleted from pending list to FSM. Add pages deleted from
GIN's pending list during cleanup to free space map immediately.
Clean up process could be initiated by ordinary insert but adding
page to FSM might occur only at vacuum. On some workload like
never-vacuumed insert-only tables it could cause a huge bloat. Jeff
Janes.
http://git.postgresql.org/pg/commitdiff/e95680832854cf300e64c10de9cc2f586df558e8

- Make GIN's cleanup pending list process interruptable. Cleanup
process could be called by ordinary insert/update and could take a
lot of time. Add vacuum_delay_point() to make this process
interruptable. Under vacuum this call will also throttle a vacuum
process to decrease system load, called from insert/update it will
not throttle, and that reduces a latency. Backpatch for all
supported branches. Jeff Janes.
http://git.postgresql.org/pg/commitdiff/e26692248ada18417199b47bc2e4e1648d530953

- Microvacuum for GIST. Mark index tuple as dead if it's pointed by
kill_prior_tuple during ordinary (search) scan and remove it during
insert process if there is no enough space for new tuple to insert.
This improves select performance because index will not return tuple
marked as dead and improves insert performance because it reduces
number of page split. Anastasia Lubennikova
<a(dot)lubennikova(at)postgrespro(dot)ru> with minor editorialization by me
http://git.postgresql.org/pg/commitdiff/013ebc0a7b7ea9c1b1ab7a3d4dd75ea121ea8ba7

- Fix oversight in 013ebc0a7b7ea9c1b1ab7a3d4dd75ea121ea8ba7 commit.
Declaration of variable inside code block.
http://git.postgresql.org/pg/commitdiff/223936e22691ccae02be7e2980b6f565518bff19

Gregory Stark pushed:

- Change type of DOW/DOY to UNITS
http://git.postgresql.org/pg/commitdiff/37239ef8c9fa72e0f73a307cc413a03a743878d3

Jeff Davis pushed:

- Add log_line_prefix option 'n' for Unix epoch. Prints time as Unix
epoch with milliseconds. Tomas Vondra, reviewed by Fabien Coelho.
http://git.postgresql.org/pg/commitdiff/f828654e1097bdbb40fedebd9ac1ea045b183e15

- Coordinate log_line_prefix options 'm' and 'n' to share a timeval.
Commit f828654e introduced the 'n' option, but it invoked
gettimeofday() independently of the 'm' option. If both options were
in use (or multiple 'n' options), or if 'n' was in use along with
csvlog, then the reported times could be different for the same log
message. To fix, initialize a global variable with gettimeofday()
once per log message, and use that for both formats. Don't bother
coordinating the time for the 't' option, which has much lower
resolution. Per complaint by Alvaro Herrera.
http://git.postgresql.org/pg/commitdiff/b1e1862a123b6904d51fd0a607e30f5832bf9a1f

Álvaro Herrera pushed:

- Add more sanity checks in contrib/sslinfo. We were missing a few
return checks on OpenSSL calls. Should be pretty harmless, since we
haven't seen any user reports about problems, and this is not a
high-traffic module anyway; still, a bug is a bug, so backpatch this
all the way back to 9.0. Author: Michael Paquier, while reviewing
another sslinfo patch
http://git.postgresql.org/pg/commitdiff/d94c36a45ab457cf43bad76656f77778b3318523

- contrib/sslinfo: add ssl_extension_info SRF. This new function
provides information about SSL extensions present in the X509
certificate used for the current connection. Extension version
updated to version 1.1. Author: Дмитрий Воронин (Dmitry Voronin)
Reviewed by: Michael Paquier, Heikki Linnakangas, Álvaro Herrera
http://git.postgresql.org/pg/commitdiff/49124613f134b04594b1a5c46368eb0a5db16d4b

- Fix error message wording in previous sslinfo commit
http://git.postgresql.org/pg/commitdiff/665a00c9e2598e3be366cb9f99c0a04a51dd8c7a

- Allow per-tablespace effective_io_concurrency. Per discussion,
nowadays it is possible to have tablespaces that have wildly
different I/O characteristics from others. Setting different
effective_io_concurrency parameters for those has been measured to
improve performance. Author: Julien Rouhaud. Reviewed by: Andres
Freund.
http://git.postgresql.org/pg/commitdiff/1aba62ec635f5852bc45ce65482366e541e61ff5

- Add missing ReleaseBuffer call in BRIN revmap code. I think this
particular branch is actually dead, but the analysis to prove that
is not trivial, so instead take the weasel way. Reported by Jinyu
Zhang Backpatch to 9.5, where BRIN was introduced.
http://git.postgresql.org/pg/commitdiff/5cd6538345858aec128945b58265a8d5560777dc

Noah Misch pushed:

- In the pg_rewind test suite, receive WAL fully before promoting. If
a transaction never reaches the standby, later tests find unexpected
cluster state. A "tail-copy: query result matches" test failure has
been the usual symptom. Among the buildfarm members having run this
test suite, most have exhibited that symptom at least once.
Back-patch to 9.5, where pg_rewind was introduced. Michael Paquier,
reported by Christoph Berg.
http://git.postgresql.org/pg/commitdiff/582fbffb0ccc79de76a459df670b86d109d37ca5

Fujii Masao pushed:

- Improve tab-completion for GRANT and REVOKE. Thomas Munro, reviewed
by Michael Paquier, modified by me.
http://git.postgresql.org/pg/commitdiff/2f8880704a697312d8d10ab3a2ad7ffe4b5e3dfd

- Add gin_fuzzy_search_limit to postgresql.conf.sample. This was
forgotten in 8a3631f (commit that originally added the parameter)
and 0ca9907 (commit that added the documentation later that year).
Back-patch to all supported versions.
http://git.postgresql.org/pg/commitdiff/043113e7982942279d940f9bf0e3a5a4b66b9aa7

- Remove files signaling a standby promotion request at postmaster
startup. This commit makes postmaster forcibly remove the files
signaling a standby promotion request. Otherwise, the existence of
those files can trigger a promotion too early, whether a user wants
that or not. This removal of files is usually unnecessary because
they can exist only during a few moments during a standby promotion.
However there is a race condition: if pg_ctl promote is executed and
creates the files during a promotion, the files can stay around even
after the server is brought up to new master. Then, if new standby
starts by using the backup taken from that master, the files can
exist at the server startup and should be removed in order to avoid
an unexpected promotion. Back-patch to 9.1 where promote signal
file was introduced. Problem reported by Feike Steenbergen.
Original patch by Michael Paquier, modified by me. Discussion:
20150528100705(dot)4686(dot)91426(at)wrigleys(dot)postgresql(dot)org
http://git.postgresql.org/pg/commitdiff/96f6a0cb41ee06673960019f0026b1b0fd1e644d

- Correct description of PageHeaderData layout in documentation.
Back-patch to 9.3 where PageHeaderData layout was changed. Michael
Paquier
http://git.postgresql.org/pg/commitdiff/a1b28885177aff123cafe5a7f694555af33ad4e9

Andres Freund pushed:

- psql: Generic tab completion support for enum and bool GUCs.
Author: Pavel Stehule Reviewed-By: Andres Freund Discussion:
5594FE7A(dot)5050205(at)iki(dot)fi
http://git.postgresql.org/pg/commitdiff/3ae16798f0f9d2d941e50062b579c28c9b946c9e

Stephen Frost pushed:

- Lock all relations referred to in updatable views. Even views
considered "simple" enough to be automatically updatable may have
mulitple relations involved (eg: in a where clause). We need to
make sure and lock those relations when rewriting the query.
Back-patch to 9.3 where updatable views were added. Pointed out by
Andres, patch thanks to Dean Rasheed.
http://git.postgresql.org/pg/commitdiff/c3e0ddd403d74b161cd83cdccbb0adc45788934f

- Fix typo in setrefs.c. We're adding OIDs, not TIDs, to invalItems.
Pointed out by Etsuro Fujita. Back-patch to all supported branches.
http://git.postgresql.org/pg/commitdiff/1e460d4bd633462bdee34c61ad7c073aec0a941a

- Fix typo in create_policy.sgml. WTIH -> WITH. Pointed out by
Dmitriy Olshevskiy. Backpatch to 9.5 where create_policy.sgml was
added.
http://git.postgresql.org/pg/commitdiff/e7bf508e6b527bc6c28cd5ddba99a23d27780e94

Tom Lane pushed:

- Fix minor bug in regexp makesearch() function. The list-wrangling
here was done wrong, allowing the same state to get put into the
list twice. The following loop then would clone it twice. The
second clone would wind up with no inarcs, so that there was no
observable misbehavior AFAICT, but a useless state in the finished
NFA isn't an especially good thing.
http://git.postgresql.org/pg/commitdiff/91cf3135b9079bf44ee9b4b445531987cdb7deee

- Fix setrefs.c comment properly. The "typo" alleged in commit
1e460d4bd was actually a comment that was correct when written, but
I missed updating it in commit b5282aa89. Use a slightly less
specific (and hopefully more future-proof) description of what is
collected. Back-patch to 9.2 where that commit appeared, and revert
the comment to its then-entirely-correct state before that.
http://git.postgresql.org/pg/commitdiff/87efbc2be12b07c77b7fa2d54d029329f56d6677

Peter Eisentraut pushed:

- doc: Spell checking
http://git.postgresql.org/pg/commitdiff/103ef2021110e66c2b3b87af4b0a901683b0275e

- Update SQL features list
http://git.postgresql.org/pg/commitdiff/b2ae8f1e35d85e4e24a724ef64c47deb126368f7

- doc: Remove dead links. The web pages of Andy Dong at Berkeley
don't exist anymore, and he is no longer there.
http://git.postgresql.org/pg/commitdiff/c193b8ca9d05e8167b6deb057c06a7580fa517a3

- Add noreturn attribute. per gcc -Wsuggest-attribute=noreturn
http://git.postgresql.org/pg/commitdiff/b5217d696833b15ed5d3c5e858e7f20836defa95

Robert Haas pushed:

- When trace_lwlocks is used, identify individual lwlocks by name.
Naming the individual lwlocks seems like something that may be
useful for other types of debugging, monitoring, or instrumentation
output, but this commit just implements it for the specific case of
trace_lwlocks. Patch by me, reviewed by Amit Kapila and Kyotaro
Horiguchi
http://git.postgresql.org/pg/commitdiff/aa65de042f5828968f2f6cd65f45c543a40cc3e6

- Fix build problems in commit
aa65de042f5828968f2f6cd65f45c543a40cc3e6. The previous way didn't
work for vpath builds, and make distprep was busted too. Reported
off-list by Andres Freund.
http://git.postgresql.org/pg/commitdiff/2ccc4e972eb85829a8fe8d39fc5ce4d770ac2e2e

- Install lwlocknames.h even in vpath builds. Per buildfarm member
crake.
http://git.postgresql.org/pg/commitdiff/a7212a9997e592fe72e2095a5f4f37071787d849

Kevin Grittner pushed:

- Fix an O(N^2) problem in foreign key references. Commit 45ba424f
improved foreign key lookups during bulk updates when the FK value
does not change. When restoring a schema dump from a database with
many (say 100,000) foreign keys, this cache would grow very big and
every ALTER TABLE command was causing an
InvalidateConstraintCacheCallBack(), which uses a sequential hash
table scan. This could cause a severe performance regression in
restoring a schema dump (including during pg_upgrade). The patch
uses a heuristic method of detecting when the hash table should be
destroyed and recreated. InvalidateConstraintCacheCallBack() adds
the current size of the hash table to a counter. When that sum
reaches 1,000,000, the hash table is flushed. This fixes the
regression without noticeable harm to the bulk update use case. Jan
Wieck. Backpatch to 9.3 where the performance regression was
introduced.
http://git.postgresql.org/pg/commitdiff/5ddc72887a012f6a8b85707ef27d85c274faf53d

Bruce Momjian pushed:

- pg_dump, pg_upgrade: allow postgres/template1 tablespace moves.
Modify pg_dump to restore postgres/template1 databases to
non-default tablespaces by switching out of the database to be
moved, then switching back. Also, to fix potentially cases where
the old/new tablespaces might not match, fix pg_upgrade to process
new/old tablespaces separately in all cases. Report by Marti
Raudsepp Patch by Marti Raudsepp, me Backpatch through 9.0
http://git.postgresql.org/pg/commitdiff/7f8d090b8966ef098f45537d42e47e296210145f

Andrew Dunstan pushed:

- Fix the fastpath rule for jsonb_concat with an empty operand. To
prevent perverse results, we now only return the other operand if
it's not scalar, and if both operands are of the same kind (array or
object). Original bug complaint and patch from Oskari Saarenmaa,
extended by me to cover the cases of different kinds of jsonb.
Backpatch to 9.5 where jsonb_concat was introduced.
http://git.postgresql.org/pg/commitdiff/e7e3ac2d51130d707792e657facc58c160182342

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Thomas Munro sent in a patch to use memcmp() rather than numeric_cmp()
in certain aggregation scenarios.

Thomas Munro sent in a patch to stabilize the pg_rewind TAP test.

Thomas Munro sent in another revision of a patch to add a GUC which
allows synchronous commit to mean "apply."

Petr Jelinek and Michael Paquier traded patches to allow CREATE
EXTENSION ... CASCADE, which would include all its dependencies.

Alexander Korotkov sent in another revision of a patch to add CREATE
ACCESS METHOD.

Andres Freund sent in a PoC patch to separate Buffer LWLocks.

Etsuro Fujita sent in another revision of a patch to make FDW join
pushdown and EvalPlanQual work together.

Pavel Stěhule and Alexander Shulgin traded patches to make it possible
to get the explain of a running query by sending the backend a signal.

Michael Paquier sent in a patch to fix some messages in the SSL info
functions.

Nathan Wagner sent in a patch to adjust the GEQO mutation swaps to
avoid having to re-pick ties, and change the initialization and
shuffling algorithm for the gene array to use an in-place Fisher-Yates
shuffling algorithm.

Rajeev Rastogi sent in a patch to allow dumping memory context
information to a file.

Jeff Janes sent in a patch to fix the tab completion for ALTER INDEX
in psql.

Rugal Bernstein sent in a patch to add a better translation version of
Chinese for psql/po/zh_CN.po file.

Robert Haas sent in another revision of a patch to allow the isolation
tester to have >1 waiting process.

Thomas Munro sent in another revision of a patch to make psql's
tab-complete.c easier to understand and maintain.

Michael Paquier sent in another revision of a patch to improve test
coverage of extensions with pg_dump.

Stephen Frost sent in two more revisions of a patch to improve logging
of TAP tests.

Robbie Harwood sent in another revision of a patch to add GSSAPI
encryption support.

Pavel Stěhule sent in four more revisions of a patch to add a
parse_ident() function.

Fabien COELHO sent in another revision of a patch to add pgbench
progress with timestamp.

Etsuro Fujita sent in another revision of a patch to improve
create_foreignscan_plan/ExecInitForeignScan.

Ashutosh Bapat sent in another revision of a patch to add tests for
background worker notify.

Andres Freund sent in another revision of a patch to update the
documented compiler requirements.

David Fetter sent in a patch to rework some test for contrib/tablefunc
for legibility.

Michael Paquier sent in a patch to fix a typo in timeline.h regarding
the meaning of infinity for a timeline history entry.

Fujii Masao sent in two more revisions of a patch to improve
cancelbackup messages.

Fabien COELHO sent in another revision of a patch to add
GUCs checkpoint_sort and checkpoint_warning.

Stephen Frost sent in a patch to remove the notion of
SECURITY_ROW_LEVEL_DISABLED entirely.

Beena Emerson sent in another revision of a patch to add support for N
synchronous standby servers.

Takashi Horikawa sent in four revisions of a patch to partition
checkpointing.

Костя Кузнецов (Kostya Kuznetzov) sent in a patch to add a new GiST
vacuum.

Alexander Korotkov sent in another revision of a patch to rework
the access method interface.

YUriy Zhuravlev sent in another revision of a patch to move PinBuffer
and UnpinBuffer to atomics.

Etsuro Fujita sent in a patch to update some comments in pathnode.c.

Andrew Dunstan sent in a patch to fix the way jsonbconcat works.

Rahila Syed sent in two more revisions of a patch to add a VACUUM
progress checker.

Amit Kapila sent in another revision of a patch to speed up clog
access by increasing CLOG buffers.

Teodor Sigaev sent in another revision of a patch to add a
--strict-names option to pg_dump.

David Rowley sent in another revision of a patch to make
timestamptz_out less slow.

Charles Clavadetscher sent in four revisions of a patch to add COMMENT
ON POLICY.

Kaigai Kouhei sent in a patch to fix a regtest policy for 9.2.

Ildus Kurbangaliev sent in another revision of a patch to refactor
LWLock tranches.

Dean Rasheed and Stephen Frost traded patches to refactor RLS.