== PostgreSQL Weekly News - July 17 2016 ==
CHAR(16), an international conference to celebrate and showcase the
significant developments the leading PostgreSQL engineering teams have
made in the areas of Clustering, High Availability and Replication,
will take place in New York, December 6, 2016. Call for papers is now
open. The closing date for submissions is midnight (EDT) September
30, 2016.
http://charconference.org/
== PostgreSQL Product News ==
PG Bloat Check v2.2.0, a program which provides very accurate bloat
statistics for both tables and indexes, released.
https://github.com/keithf4/pg_bloat_check
== PostgreSQL Jobs for July ==
http://archives.postgresql.org/pgsql-jobs/2016-07/threads.php
== PostgreSQL Local ==
PostgresOpen 2016 in will be held in Dallas, Texas September 13-16.
The CfP is open.
https://2016.postgresopen.org/callforpapers/
PostgreSQL Session will be held on September 22th, 2016, in Lyon,
France. The submission deadline is May 20, 2016. Send proposals to
call-for-paper AT postgresql-sessions DOT org.
PgConf Silicon Valley 2016 will be held on November 14-16, 2016.
http://www.pgconfsv.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@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.
== Applied Patches ==
Tom Lane pushed:
- Revert "Add some temporary code to record stack usage at server
process exit." This reverts commit
88cf37d2a86d5b66380003d7c3384530e3f91e40 as well as follow-on
commits ea9c4a16d5ad88a1d28d43ef458e3209b53eb106 and
c57562725d219c4249b82f4a4fb5aaeee3ae0d53. We've learned about as
much as we can from the buildfarm.
http://git.postgresql.org/pg/commitdiff/96112ee7c60557bb192a9aa07b514db2400fd45e
- Improve output of psql's \df+ command. Add display of proparallel
(parallel-safety) when the server is >= 9.6, and display of proacl
(access privileges) for all server versions. Minor tweak of column
ordering to keep related columns together. Michael Paquier
Discussion:
<CAB7nPqTR3Vu3xKOZOYqSm-+bSZV0kqgeGAXD6w5GLbkbfd5Q6w@mail.gmail.com>
http://git.postgresql.org/pg/commitdiff/a670c24c382693c4f75e99c9292b2ed0f0d40a72
- Print a given subplan only once in EXPLAIN. We have, for a very
long time, allowed the same subplan (same member of the
PlannedStmt.subplans list) to be referenced by more than one SubPlan
node; this avoids problems for cases such as subplans within an
IndexScan's indxqual and indxqualorig fields. However, EXPLAIN had
not gotten the memo and would print each reference as though it were
an independent identical subplan. To fix, track plan_ids of
subplans we've printed and don't print the same plan_id twice. Per
report from Pavel Stehule. BTW: the particular case of IndexScan
didn't cause visible duplication in a plain EXPLAIN, only EXPLAIN
ANALYZE, because in the former case we short-circuit executor
startup before the indxqual field is processed by ExecInitExpr.
That seems like it could easily lead to other EXPLAIN problems in
future, but it's not clear how to avoid it without breaking the
"EXPLAIN a plan using hypothetical indexes" use-case. For now I've
left that issue alone. Although this is a longstanding bug, it's
purely cosmetic (no great harm is done by the repeat printout) and
we haven't had field complaints before. So I'm hesitant to
back-patch it, especially since there is some small risk of ABI
problems due to the need to add a new field to ExplainState. In
passing, rearrange order of fields in ExplainState to be less
random, and update some obsolete comments about when/where to
initialize them. Report:
<CAFj8pRAimq+NK-menjt+3J4-LFoodDD8Or6=Lc_stcFD+eD4DA@mail.gmail.com>
http://git.postgresql.org/pg/commitdiff/4d042999f94a4bc41b86baca5920cd4829e16895
- Allow IMPORT FOREIGN SCHEMA within pl/pgsql. Since IMPORT FOREIGN
SCHEMA has an INTO clause, pl/pgsql needs to be aware of that and
avoid capturing the INTO as an INTO-variables clause. This isn't
hard, though it's annoying to have to make IMPORT a plpgsql keyword
just for this. (Fortunately, we have the infrastructure now to make
it an unreserved keyword, so at least this shouldn't break any
existing pl/pgsql code.) Per report from Merlin Moncure. Back-patch
to 9.5 where IMPORT FOREIGN SCHEMA was introduced. Report:
<CAHyXU0wpHf2bbtKGL1gtUEFATCY86r=VKxfcACVcTMQ70mCyig@mail.gmail.com>
http://git.postgresql.org/pg/commitdiff/baebab3ace480477f210dadc4633d8d119dfa978
- Fix obsolete header-file reference in pg_buffercache docs. Commit
2d0019049 moved enum ForkNumber from relfilenode.h into relpath.h,
but missed updating this documentation reference. Alexander Law
http://git.postgresql.org/pg/commitdiff/91c0eb5040cde36a62a34e9b3a80d82db64eb98f
- Add a regression test case to improve code coverage for tuplesort.
Test the external-sort code path in CLUSTER for two different
scenarios: multiple-pass external sorting, and the best case for
replacement selection, where only one run is produced, so that no
merge is required. This test would have caught the bug fixed in
commit 1b0fc8507, at least when run with valgrind enabled. In
passing, add a short-circuit test in plan_cluster_use_sort() to make
dead certain that it selects sorting when enable_indexscan is off.
As things stand, that would happen anyway, but it seems like good
future proofing for this test. Peter Geoghegan Discussion:
<CAM3SWZSgxehDkDMq1FdiW2A0Dxc79wH0hz1x-TnGy=1BXEL+nw@mail.gmail.com>
http://git.postgresql.org/pg/commitdiff/cec55013943d160538334ee19ef5db429a085969
- Minor test adjustment. Dept of second thoughts: given the RESET
SESSION AUTHORIZATION that was just added by commit cec550139, we
don't need the reconnection that used to be here. Might as well buy
back a few microseconds.
http://git.postgresql.org/pg/commitdiff/56a997413391337f7fb9926144d6b6fa831b9d22
- Fix GiST index build for NaN values in geometric types. GiST index
build could go into an infinite loop when presented with boxes (or
points, circles or polygons) containing NaN component values. This
happened essentially because the code assumed that x == x is true
for any "double" value x; but it's not true for NaNs. The looping
behavior was not the only problem though: we also attempted to sort
the items using simple double comparisons. Since NaNs violate the
trichotomy law, qsort could (in principle at least) get arbitrarily
confused and mess up the sorting of ordinary values as well as NaNs.
And we based splitting choices on box size calculations that could
produce NaNs, again resulting in undesirable behavior. To fix,
replace all comparisons of doubles in this logic with
float8_cmp_internal, which is NaN-aware and is careful to sort NaNs
consistently, higher than any non-NaN. Also rearrange the box size
calculation to not produce NaNs; instead it should produce an
infinity for a box with NaN on one side and not-NaN on the other. I
don't by any means claim that this solves all problems with NaNs in
geometric values, but it should at least make GiST index insertion
work reliably with such data. It's likely that the index search
side of things still needs some work, and probably regular geometric
operations too. But with this patch we're laying down a convention
for how such cases ought to behave. Per bug #14238 from Guang-Dih
Lei. Back-patch to 9.2; the code used before commit
7f3bd86843e5aad8 is quite different and doesn't lock up on my simple
test case, nor on the submitter's dataset. Report:
<20160708151747.1426.60150@wrigleys.postgresql.org> Discussion:
<28685.1468246504@sss.pgh.pa.us>
http://git.postgresql.org/pg/commitdiff/1acf7572554515b99ef6e783750aaea8777524ec
- Improve documentation about search_path for SECURITY DEFINER
functions. Clarify that the reason for recommending that pg_temp be
put last is to prevent temporary tables from capturing unqualified
table names. Per discussion with Albe Laurenz. Discussion:
<A737B7A37273E048B164557ADEF4A58B5386C6E1@ntex2010i.host.magwien.gv.at>
http://git.postgresql.org/pg/commitdiff/ce150e7e0fc1a127fee7933d71f4204a79ecce04
- Avoid invalidating all foreign-join cached plans when user mappings
change. We must not push down a foreign join when the foreign
tables involved should be accessed under different user mappings.
Previously we tried to enforce that rule literally during planning,
but that meant that the resulting plans were dependent on the
current contents of the pg_user_mapping catalog, and we had to blow
away all cached plans containing any remote join when anything at
all changed in pg_user_mapping. This could have been improved
somewhat, but the fact that a syscache inval callback has very
limited info about what changed made it hard to do better within
that design. Instead, let's change the planner to not consider user
mappings per se, but to allow a foreign join if both RTEs have the
same checkAsUser value. If they do, then they necessarily will use
the same user mapping at runtime, and we don't need to know
specifically which one that is. Post-plan-time changes in
pg_user_mapping no longer require any plan invalidation. This rule
does give up some optimization ability, to wit where two foreign
table references come from views with different owners or one's from
a view and one's directly in the query, but nonetheless the same
user mapping would have applied. We'll sacrifice the first case,
but to not regress more than we have to in the second case, allow a
foreign join involving both zero and nonzero checkAsUser values if
the nonzero one is the same as the prevailing effective userID. In
that case, mark the plan as only runnable by that userID. The
plancache code already had a notion of plans being userID-specific,
in order to support RLS. It was a little confused though, in
particular lacking clarity of thought as to whether it was the
rewritten query or just the finished plan that's dependent on the
userID. Rearrange that code so that it's clearer what depends on
which, and so that the same logic applies to both RLS-injected role
dependency and foreign-join-injected role dependency. Note that
this patch doesn't remove the other issue mentioned in the original
complaint, which is that while we'll reliably stop using a foreign
join if it's disallowed in a new context, we might fail to start
using a foreign join if it's now allowed, but we previously created
a generic cached plan that didn't use one. It was agreed that the
chance of winning that way was not high enough to justify the much
larger number of plan invalidations that would have to occur if we
tried to cause it to happen. In passing, clean up randomly-varying
spelling of EXPLAIN commands in postgres_fdw.sql, and fix a COSTS ON
example that had been allowed to leak into the committed tests.
This reverts most of commits fbe5a3fb7 and 5d4171d1c, which were the
previous attempt at ensuring we wouldn't push down foreign joins
that span permissions contexts. Etsuro Fujita and Tom Lane
Discussion: <d49c1e5b-f059-20f4-c132-e9752ee0113e@lab.ntt.co.jp>
http://git.postgresql.org/pg/commitdiff/45639a0525a58a2700cf46d4c934d6de78349dac
- Advance PG_CONTROL_VERSION. This should have been done in commit
73c986adde5d73a5 which added several new fields to pg_control, and
again in commit 5028f22f6eb05798 which changed the CRC algorithm,
but it wasn't. It's far too late to fix it in the 9.5 branch, but
let's do so in 9.6, so that if a 9.6 postmaster is started against a
9.4-era pg_control it will complain about a versioning problem
rather than a CRC failure. We already forced initdb/pg_upgrade for
beta3, so there's no downside to doing this now. Discussion:
<7615.1468598094@sss.pgh.pa.us>
http://git.postgresql.org/pg/commitdiff/99dd8b05aa5647a59f30ca67e67e2e3377f50094
- Clarify usage of clientcert authentication option. For some reason
this option wasn't discussed at all in client-auth.sgml. Document
it there, and be more explicit about its relationship to the "cert"
authentication method. Per gripe from Srikanth Venkatesh. I failed
to resist the temptation to do some minor wordsmithing in the same
area, too. Discussion:
<20160713110357.1410.30407@wrigleys.postgresql.org>
http://git.postgresql.org/pg/commitdiff/745513c70282180afd83c666e43bdb0b6fb8c688
- Fix crash in close_ps() for NaN input coordinates. The Assert()
here seems unreasonably optimistic. Andreas Seltenreich found that
it could fail with NaNs in the input geometries, and it seems likely
to me that it might fail in corner cases due to roundoff error, even
for ordinary input values. As a band-aid, make the function return
SQL NULL instead of crashing. Report: <87d1md1xji.fsf@credativ.de>
http://git.postgresql.org/pg/commitdiff/278148907a971ec7fa4ffb24248103d8012155d2
- Add regression test case exercising the sorting path for hash index
build. We've broken this code path at least twice in the past, so
it's prudent to have a test case that covers it. To allow
exercising the code path without creating a very large (and slow to
run) test case, redefine the sort threshold to be bounded by
maintenance_work_mem as well as the number of available buffers.
While at it, fix an ancient oversight that when building a temp
index, the number of available buffers is not NBuffers but
NLocBuffer. Also, if assertions are enabled, apply a direct test
that the sort actually does return the tuples in the expected order.
Peter Geoghegan Patch:
<CAM3SWZTBAo4hjbBd780+MrOKiKp_TMo1N3A0Rw9_im8gbD7fQA@mail.gmail.com>
http://git.postgresql.org/pg/commitdiff/9563d5b5e4c75e676d73a45546bd47b77c2bd739
- Improve test case exercising the sorting path for hash index build.
On second thought, we should probably do at least a minimal check
that the constructed index is valid, since the big problem with the
most recent breakage was not whether the sorting was correct but
that the index had incorrect hash codes placed in it.
http://git.postgresql.org/pg/commitdiff/606ccc5e7e97914073f991b077712645e125d531
- Update 9.6 release notes through today.
http://git.postgresql.org/pg/commitdiff/fe03f2896292e6b21828490c76bda64cf75e7867
Magnus Hagander pushed:
- Fix start WAL filename for concurrent backups from standby. On a
standby, ThisTimelineID is always 0, so we would generate a filename
in timeline 0 even for other timelines. Instead, use starttli which
we have retreived from the controlfile. Report by: Francesco
Canovai in bug #14230 Author: Marco Nenciarini Reviewed by: Michael
Paquier and Amit Kapila
http://git.postgresql.org/pg/commitdiff/87d84d67bb15752c79a1c07e603126830642ac84
- Add missing newline in error message
http://git.postgresql.org/pg/commitdiff/ae7d78c3e24d7991a8f76f4676c015df3d143d63
- Remove reference to range mode in pg_xlogdump error. pg_xlogdump
doesn't have any other mode, so it's just confusing to include this
in the error message as it indicates there might be another mode.
http://git.postgresql.org/pg/commitdiff/00e0b67a584b51aac12e4279a31fa5a1130fe05f
Peter Eisentraut pushed:
- doc: Update URL for PL/PHP
http://git.postgresql.org/pg/commitdiff/740bf396a1d5cc41a8398076a9de1485c497c49c
- doc: Fix typo From: Alexander Law <exclusion@gmail.com>
http://git.postgresql.org/pg/commitdiff/d3fbd5929ce5cb7468635aac30c2abf02b7d474a
- Put some things in a better order in psql help
http://git.postgresql.org/pg/commitdiff/b9fc9f7c3c4096aca69261af305c679ffe74c32b
- Add serial comma and quoting to message
http://git.postgresql.org/pg/commitdiff/5d4050064bf3f2be056a4a887ec11aa14d0d2562
- doc: Fix typos From: Alexander Law <exclusion@gmail.com>
http://git.postgresql.org/pg/commitdiff/3aed52a622b1c83abc45c6a7c0ef6dbb9adaa7e1
- Adjust spellings of forms of "cancel"
http://git.postgresql.org/pg/commitdiff/63cfdb8dde7f25a095af03aa204580fea55c6c07
- Use correct symbol for minimum int64 value. The old code used
SEQ_MINVALUE to get the smallest int64 value. This was done as a
convenience to avoid having to deal with INT64_IS_BUSTED, but that
is obsolete now. Also, it is incorrect because the smallest int64
value is actually SEQ_MINVALUE-1. Fix by using PG_INT64_MIN.
http://git.postgresql.org/pg/commitdiff/f36ca9af05dd0468cdee28fbdbded690a10ff08b
- doc: Supply XSLT template for superscript element in man pages. The
default is no decoration, which looks confusing, for example on the
CREATE SEQUENCE man page.
http://git.postgresql.org/pg/commitdiff/7482fc4600ee97f8b2570e87b8c216a83b918065
Stephen Frost pushed:
- Add missing hyphen. Pointed out by Alexander Law
http://git.postgresql.org/pg/commitdiff/42ec6c2da699e8e0b1774988fa97297a2cdf716c
- Correctly dump database and tablespace ACLs. Dump out the
appropriate GRANT/REVOKE commands for databases and tablespaces from
pg_dumpall to replicate what the current state is. This was broken
during the changes to buildACLCommands for 9.6+ servers for
pg_init_privs.
http://git.postgresql.org/pg/commitdiff/47f5bb9f539a7fff089724b1cbacc31613031895
Teodor Sigaev pushed:
- Fix nested NOT operation cleanup in tsquery. During normalization
of tsquery tree it tries to simplify nested NOT operations but there
it's obvioulsy missed that subsequent node could be a leaf node
(value node) Bug #14245: Segfault on weird to_tsquery Reported by
David Kellum.
http://git.postgresql.org/pg/commitdiff/19d290155d084754eeb5ebb2569654da06073ee8
- Fix parsing NOT sequence in tsquery Digging around bug #14245 I
found that commit 6734a1cacd44f5b731933cbc93182b135b167d0c missed
that NOT operation is right associative in opposite to all other.
This miss is resposible for tsquery parser fail on sequence of NOT
operations
http://git.postgresql.org/pg/commitdiff/00f304ce2dd86f4b76606225b41e0854a3362628
Alvaro Herrera pushed:
- Avoid serializability errors when locking a tuple with a committed
update. When key-share locking a tuple that has been
not-key-updated, and the update is a committed transaction, in some
cases we raised serializability errors: ERROR: could not serialize
access due to concurrent update Because the key-share doesn't
conflict with the update, the error is unnecessary and inconsistent
with the case that the update hasn't committed yet. This causes
problems for some usage patterns, even if it can be claimed that
it's sufficient to retry the aborted transaction: given a steady
stream of updating transactions and a long locking transaction, the
long transaction can be starved indefinitely despite multiple
retries. To fix, we recognize that HeapTupleSatisfiesUpdate can
return HeapTupleUpdated when an updating transaction has committed,
and that we need to deal with that case exactly as if it were a
non-committed update: verify whether the two operations conflict,
and if not, carry on normally. If they do conflict, however, there
is a difference: in the HeapTupleBeingUpdated case we can just sleep
until the concurrent transaction is gone, while in the
HeapTupleUpdated case this is not possible and we must raise an
error instead. Per trouble report from Olivier Dony. In addition
to a couple of test cases that verify the changed behavior, I added
a test case to verify the behavior that remains unchanged, namely
that errors are raised when a update that modifies the key is used.
That must still generate serializability errors. One pre-existing
test case changes behavior; per discussion, the new behavior is
actually the desired one. Discussion:
https://www.postgresql.org/message-id/560AA479.4080807@odoo.com
https://www.postgresql.org/message-id/20151014164844.3019.25750@wrigleys.postgresql.org
Backpatch to 9.3, where the problem appeared.
http://git.postgresql.org/pg/commitdiff/533e9c6b0628f6557ddcaf3e5177081878ea7cb6
Andres Freund pushed:
- Make HEAP_LOCK/HEAP2_LOCK_UPDATED replay reset HEAP_XMAX_INVALID.
0ac5ad5 started to compress infomask bits in WAL records.
Unfortunately the replay routines for
XLOG_HEAP_LOCK/XLOG_HEAP2_LOCK_UPDATED forgot to reset the
HEAP_XMAX_INVALID (and some other) hint bits. Luckily that's not
problematic in the majority of cases, because after a crash/on a
standby row locks aren't meaningful. Unfortunately that does not
hold true in the presence of prepared transactions. This means that
after a crash, or after promotion, row level locks held by a
prepared, but not yet committed, prepared transaction might not be
enforced. Discussion:
20160715192319.ubfuzim4zv3rqnxv@alap3.anarazel.de Backpatch: 9.3,
the oldest branch on which 0ac5ad5 is present.
http://git.postgresql.org/pg/commitdiff/a4d357bfbd56bd876817fb74f46525dbb3184bc2
- Fix torn-page, unlogged xid and further risks from heap_update().
When heap_update needs to look for a page for the new tuple version,
because the current one doesn't have sufficient free space, or when
columns have to be processed by the tuple toaster, it has to release
the lock on the old page during that. Otherwise there'd be lock
ordering and lock nesting issues. To avoid concurrent sessions from
trying to update / delete / lock the tuple while the page's content
lock is released, the tuple's xmax is set to the current session's
xid. That unfortunately was done without any WAL logging, thereby
violating the rule that no XIDs may appear on disk, without an
according WAL record. If the database were to crash / fail over
when the page level lock is released, and some activity lead to the
page being written out to disk, the xid could end up being reused;
potentially leading to the row becoming invisible. There might be
additional risks by not having t_ctid point at the tuple itself,
without having set the appropriate lock infomask fields. To fix,
compute the appropriate xmax/infomask combination for locking the
tuple, and perform WAL logging using the existing XLOG_HEAP_LOCK
record. That allows the fix to be backpatched. This issue has
existed for a long time. There appears to have been partial attempts
at preventing dangers, but these never have fully been implemented,
and were removed a long time ago, in 11919160 (cf.
HEAP_XMAX_UNLOGGED). In master / 9.6, there's an additional issue,
namely that the visibilitymap's freeze bit isn't reset at that point
yet. Since that's a new issue, introduced only in a892234f830,
that'll be fixed in a separate commit. Author: Masahiko Sawada and
Andres Freund. Reported-By: Different aspects by Thomas Munro, Noah
Misch, and others Discussion:
CAEepm=3fWAbWryVW9swHyLTY4sXVf0xbLvXqOwUoDiNCx9mBjQ@mail.gmail.com
Backpatch: 9.1/all supported versions
http://git.postgresql.org/pg/commitdiff/bfa2ab56bb8c512dc8613ee3ff0936568a1c8418
== Pending Patches ==
Masahiko Sawada sent another revision of a patch to fix heap_udpate
set xmax without WAL logging in the already_marked = true case.
Jeff Janes sent another revision of a patch which introduces a config
variable (JJNOWAL) that skips all WAL, except for the WAL of certain
checkpoints (which are needed for initdb and to restart the server
after a clean shutdown).
Michael Paquier sent another revision of a patch to allow pointing
pg_basebackup at a disconnected standby.
Etsuro Fujita sent two revisions of a patch to fix a misalignment
between cached plans and FDW user mappings.
Michael Paquier sent another revision of a patch to remove the
RecoveryTargetTLI dead variable from XLogCtlData.
Michael Paquier sent a patch to simplify the interface of
UpdateMinRecoveryPoint.
Andres Freund sent in two revisions a patch to improve the performance
of the executor.
Corey Huinker sent another revision of a patch to add an "interval"
argument to psql's \timing which makes it less necessary to learn to
do arithmetic in modulo 3600000.
Tom Lane sent a patch to move the source code in \df+ to a footer.
Fabien COELHO sent two more revisions of a patch to fix meta command
only scripts in pgbench.
Fabien COELHO sent another revision of a patch to compute and show
latency consistently in pgbench.
Laurenz Albe sent two revisions of a patch to fix the documentation
for CREATE FUNCTION, which refers to a temporary namespace no longer
searched.
Masahiko Sawada sent a patch to fixes a typo in
contrib/postgres_fdw/deparse.c, to wit: s/whenver/whenever/g
Amit Kapila sent another revision of a patch to fix hash indexes so
they're WAL logged.
Dilip Kumar sent in two more revisions of a patch to fix the
pg_indexes view.
Magnus Hagander sent a patch to allow pg_xlogdump to follow into the
future similar to the way "tail -f" does.
Dmitriy Sarafannikov sent a patch to backport
98a64d0bd713cb89e61bef6432befc4b7b5da59e to PostgreSQL 9.5.
Fabien COELHO sent in another revision of a patch to allow pgbench to
store select results into variables.
Pavel Stehule sent another revision of a patch to add a RAW output
format to COPY.
Jun Cheol Gim sent in a patch to add created and last_updated fields
to pg_stat_statements.