== PostgreSQL Weekly News - September 27 2015 ==

== PostgreSQL Weekly News - September 27 2015 ==

== PostgreSQL Product News ==

pg_back 1.2, shell script to back up everything for PostgreSQL,
released.
https://github.com/orgrim/pg_back

PostgreDAC 3.0.3 a Delphi/C++ builder for PostgreSQL, released.
http://microolap.com/products/connectivity/postgresdac/download/

sql_firewall 0.8.1, an extension intended to protect database from SQL
injection or unexpected queries, released.
https://github.com/uptimejp/sql_firewall

== PostgreSQL Jobs for September ==

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

== PostgreSQL Local ==

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. Registration is
open.
http://pgbr.postgresql.org.br/2015/en/

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

Noah Misch pushed:

- Remove the SECURITY_ROW_LEVEL_DISABLED security context bit. This
commit's parent made superfluous the bit's sole usage. Referential
integrity checks have long run as the subject table's owner, and
that now implies RLS bypass. Safe use of the bit was tricky,
requiring strict control over the SQL expressions evaluating
therein. Back-patch to 9.5, where the bit was introduced. Based on
a patch by Stephen Frost.
http://git.postgresql.org/pg/commitdiff/7f11724bd61b420acb2839908d51674e86e52034

- Restrict file mode creation mask during tmpfile(). Per Coverity.
Back-patch to 9.0 (all supported versions). Michael Paquier,
reviewed (in earlier versions) by Heikki Linnakangas.
http://git.postgresql.org/pg/commitdiff/8346218c029dc0db425e3bea20033f96e1543df9

- Remove the row_security=force GUC value. Every query of a single
ENABLE ROW SECURITY table has two meanings, with the row_security
GUC selecting between them. With row_security=force available,
every function author would have been advised to either set the GUC
locally or test both meanings. Non-compliance would have threatened
reliability and, for SECURITY DEFINER functions, security. Authors
already face an obligation to account for search_path, and we should
not mimic that example. With this change, only BYPASSRLS roles need
exercise the aforementioned care. Back-patch to 9.5, where the
row_security GUC was introduced. Since this narrows the domain of
pg_db_role_setting.setconfig and pg_proc.proconfig, one might bump
catversion. A row_security=force setting in one of those columns
will elicit a clear message, so don't.
http://git.postgresql.org/pg/commitdiff/537bd178c73b1d25938347b17e9e3e62898fc231

Tom Lane pushed:

- Fix possible internal overflow in numeric multiplication. mul_var()
postpones propagating carries until it risks overflow in its
internal digit array. However, the logic failed to account for the
possibility of overflow in the carry propagation step, allowing
wrong results to be generated in corner cases. We must slightly
reduce the when-to-propagate-carries threshold to avoid that.
Discovered and fixed by Dean Rasheed, with small adjustments by me.
This has been wrong since commit
d72f6c75038d8d37e64a29a04b911f728044d83b, so back-patch to all
supported branches.
http://git.postgresql.org/pg/commitdiff/246693e5ae8a36f015294aa8c73dbbf0d4e542a8

- Docs: fix typo in to_char() example. Per bug #13631 from KOIZUMI
Satoru.
http://git.postgresql.org/pg/commitdiff/b1d5cc375b8786b2ff6293b3f3986c8edf120898

- Make ANALYZE compute basic statistics even for types with no "="
operator. Previously, ANALYZE simply ignored columns of datatypes
that have neither a btree nor hash opclass (which means they have no
recognized equality operator). Without a notion of equality, we
can't identify most-common values nor estimate the number of
distinct values. But we can still count nulls and compute the
average physical column width, and those stats might be of value.
Moreover there are some tools out there that don't work so well if
rows are missing from pg_statistic. So let's add suitable logic for
this case. While this is arguably a bug fix, it also has the
potential to change query plans, and the gain seems not worth taking
a risk of that in stable branches. So back-patch into 9.5 but not
further. Oleksandr Shulgin, rewritten a bit by me.
http://git.postgresql.org/pg/commitdiff/82e1ba7fd6cc9ac3fb1d9b819dc7295b268d3703

- Improve handling of collations in contrib/postgres_fdw. If we have
a local Var of say varchar type with default collation, and we apply
a RelabelType to convert that to text with default collation, we
don't want to consider that as creating an FDW_COLLATE_UNSAFE
situation. It should be okay to compare that to a remote Var, so
long as the remote Var determines the comparison collation. (When
we actually ship such an expression to the remote side, the local
Var would become a Param with default collation, meaning the remote
Var would in fact control the comparison collation, because
non-default implicit collation overrides default implicit collation
in parse_collate.c.) To fix, be more precise about what
FDW_COLLATE_NONE means: it applies either to a noncollatable data
type or to a collatable type with default collation, if that
collation can't be traced to a remote Var. (When it can,
FDW_COLLATE_SAFE is appropriate.) We were essentially using that
interpretation already at the Var/Const/Param level, but we weren't
bubbling it up properly. An alternative fix would be to introduce a
separate FDW_COLLATE_DEFAULT value to describe the second situation,
but that would add more code without changing the actual behavior,
so it didn't seem worthwhile. Also, since we're clarifying the rule
to be that we care about whether operator/function input collations
match, there seems no need to fail immediately upon seeing a
Const/Param/non-foreign-Var with nondefault collation. We only have
to reject if it appears in a collation-sensitive context (for
example, "var IS NOT NULL" is perfectly safe from a collation
standpoint, whatever collation the var has). So just set the state
to UNSAFE rather than failing immediately. Per report from Jeevan
Chalke. This essentially corrects some sloppy thinking in commit
ed3ddf918b59545583a4b374566bc1148e75f593, so back-patch to 9.3 where
that logic appeared.
http://git.postgresql.org/pg/commitdiff/76f965ff1f2896d74812a1cad9158fb9f4a1aab9

- Allow planner to use expression-index stats for function calls in
WHERE. Previously, a function call appearing at the top level of
WHERE had a hard-wired selectivity estimate of 0.3333333, a kludge
conveniently dated in the source code itself to July 1992. The
expectation at the time was that somebody would soon implement
estimator support functions analogous to those for operators; but no
such code has appeared, nor does it seem likely to in the near
future. We do have an alternative solution though, at least for
immutable functions on single relations: creating an expression
index on the function call will allow ANALYZE to gather stats about
the function's selectivity. But the code in clause_selectivity()
failed to make use of such data even if it exists. Refactor so that
that will happen. I chose to make it try this technique for any
clause type for which clause_selectivity() doesn't have a special
case, not just functions. To avoid adding unnecessary overhead in
the common case where we don't learn anything new, make selfuncs.c
provide an API that hooks directly to examine_variable() and then
var_eq_const(), rather than the previous coding which laboriously
constructed an OpExpr only so that it could be expensively
deconstructed again. I preserved the behavior that the default
estimate for a function call is 0.3333333. (For any other
expression node type, it's 0.5, as before.) I had originally thought
to make the default be 0.5 across the board, but changing a default
estimate that's survived for twenty-three years seems like something
not to do without a lot more testing than I care to put into it
right now. Per a complaint from Jehan-Guillaume de Rorthais.
Back-patch into 9.5, but not further, at least for the moment.
http://git.postgresql.org/pg/commitdiff/39df0f150ca69fac1c89537065ddc97af18921b8

- Fix psql's code for locale-aware formatting of numeric output. This
code did the wrong thing entirely for numbers with an exponent but
no decimal point (e.g., '1e6'), as reported by Jeff Janes in bug
#13636. More generally, it made lots of unverified assumptions
about what the input string could possibly look like. Rearrange so
that it only fools with leading digits that it's directly verified
are there, and an immediately adjacent decimal point. While at it,
get rid of some useless inefficiencies, like converting the grouping
count string to integer over and over (and over). This has been
broken for a long time, so back-patch to all supported branches.
http://git.postgresql.org/pg/commitdiff/4778a0bdaac3cb8eeb89acb585fd913783d80c4b

- Further fix for psql's code for locale-aware formatting of numeric
output. On closer inspection, those seemingly redundant atoi()
calls were not so much inefficient as just plain wrong: the author
of this code either had not read, or had not understood, the POSIX
specification for localeconv(). The grouping field is *not* a
textual digit string but separate integers encoded as chars. We'll
follow the existing code as well as the backend's cash.c in only
honoring the first group width, but let's at least honor it
correctly. This doesn't actually result in any behavioral change in
any of the locales I have installed on my Linux box, which may
explain why nobody's complained; grouping width 3 is close enough to
universal that it's barely worth considering other cases. Still,
wrong is wrong, so back-patch.
http://git.postgresql.org/pg/commitdiff/6325527d845b629243fb3f605af6747a7a4ac45f

- Further fix for psql's code for locale-aware formatting of numeric
output. (Third time's the charm, I hope.) Additional testing
disclosed that this code could mangle already-localized output from
the "money" datatype. We can't very easily skip applying it to
"money" values, because the logic is tied to column
right-justification and people expect "money" output to be
right-justified. Short of decoupling that, we can fix it in what
should be a safe enough way by testing to make sure the string
doesn't contain any characters that would not be expected in plain
numeric output.
http://git.postgresql.org/pg/commitdiff/77130fc1482f978629a16c8e956a5477a05d2c04

- Second try at fixing O(N^2) problem in foreign key references. This
replaces ill-fated commit 5ddc72887a012f6a8b85707ef27d85c274faf53d,
which was reverted because it broke active uses of FK cache entries.
In this patch, we still do nothing more to invalidatable cache
entries than mark them as needing revalidation, so we won't break
active uses. To keep down the overhead of
InvalidateConstraintCacheCallBack(), keep a list of just the
currently-valid cache entries. (The entries are large enough that
some added space for list links doesn't seem like a big problem.)
This would still be O(N^2) when there are many valid entries,
though, so when the list gets too long, just force the "sinval
reset" behavior to remove everything from the list. I set the
threshold at 1000 entries, somewhat arbitrarily. Possibly that
could be fine-tuned later. Another item for future study is whether
it's worth adding reference counting so that we could safely remove
invalidated entries. As-is, problem cases are likely to end up with
large and mostly invalid FK caches. Like the previous attempt,
backpatch to 9.3. Jan Wieck and Tom Lane
http://git.postgresql.org/pg/commitdiff/2abfd9d5e9cb7fe5345c415475622a4a95ea61e2

Peter Eisentraut pushed:

- Fix whitespace
http://git.postgresql.org/pg/commitdiff/13200777e7ab678d7f3535147852113ec67e6c32

- doc: Tweak synopsis indentation for consistency
http://git.postgresql.org/pg/commitdiff/84ae1af8f48fc910bbdacee4fc26a2145dc09657

- Use gender-neutral language in documentation. Based on patch by
Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, although I rephrased
most of the initial work.
http://git.postgresql.org/pg/commitdiff/741ccd5015f82e31f80cdc5d2ae81263ea92d794

- doc: Tweak "cube" index entry. With the arrival of the CUBE key
word/feature, the index entries for the cube extension and the CUBE
feature were collapsed into one. Tweak the entry for the cube
extension so they are separate entries.
http://git.postgresql.org/pg/commitdiff/ac7cbf4fb00a8a0f8348653b954c4c75880997aa

- pg_ctl: Improve help formatting and order
http://git.postgresql.org/pg/commitdiff/72ed3905563cfd847c6ab0b39415423238652ef1

- reindexdb: Fix mistake in help output
http://git.postgresql.org/pg/commitdiff/71fc49dfe1d99bd83cd99c2e7a39f93e07d19310

Andres Freund pushed:

- Correct value of LW_SHARED_MASK. The previous wrong value lead to
wrong LOCK_DEBUG output, never showing any shared lock holders.
Reported-By: Alexander Korotkov Discussion:
CAPpHfdsPmWqz9FB0AnxJrwp1=KLF0n=-iB+QvR0Q8GSmpFVdUQ(at)mail(dot)gmail(dot)com
Backpatch: 9.5, where the bug was introduced.
http://git.postgresql.org/pg/commitdiff/98d5b084d23b0304569ef2c1b4794754166cf0a5

- Add some notes about coding conventions do the docs. This deserves
to be greatly expanded and improved, but it's a start. Discussion:
20150827145219(dot)GI2435(at)awork2(dot)anarazel(dot)de
http://git.postgresql.org/pg/commitdiff/d9cb34abb40c547325dbf24e8986698dffc9ffa8

- test_decoding: Protect against rare spurious test failures. A bunch
of tests missed specifying that empty transactions shouldn't be
displayed. That causes problems when e.g. autovacuum runs in an
unfortunate moment. The tests in question only run for a very short
time, making this quite unlikely. Reported-By: Buildfarm member
axolotl Backpatch: 9.4, where logical decoding was introduced
http://git.postgresql.org/pg/commitdiff/eef34e523693c15eb4ad7067c96269ab96c87ef5

- Lower *_freeze_max_age minimum values. The old minimum values are
rather large, making it time consuming to test related behaviour.
Additionally the current limits, especially for multixacts, can be
problematic in space-constrained systems. 10000000 multixacts can
contain a lot of members. Since there's no good reason for the
current limits, lower them a good bit. Setting them to 0 would be a
bad idea, triggering endless vacuums, so still retain a limit.
While at it fix autovacuum_multixact_freeze_max_age to refer to
multixact.c instead of varsup.c. Reviewed-By: Robert Haas
Discussion:
CA+TgmoYmQPHcrc3GSs7vwvrbTkbcGD9Gik=OztbDGGrovkkEzQ(at)mail(dot)gmail(dot)com
Backpatch: back to 9.0 (in parts)
http://git.postgresql.org/pg/commitdiff/020235a5754be6ba1f0d240b4c86c642e1a62d70

- Remove legacy multixact truncation support. In 9.5 and master there
is no need to support legacy truncation. This is just committed
separately to make it easier to backpatch the WAL logged multixact
truncation to 9.3 and 9.4 if we later decide to do so. I bumped
master's magic from 0xD086 to 0xD088 and 9.5's from 0xD085 to 0xD087
to avoid 9.5 reusing a value that has been in use on master while
keeping the numbers increasing between major versions. Discussion:
20150621192409(dot)GA4797(at)alap3(dot)anarazel(dot)de Backpatch: 9.5
http://git.postgresql.org/pg/commitdiff/aa29c1ccd9f785f9365809f5133e5491acc7ae53

- Rework the way multixact truncations work. The fact that multixact
truncations are not WAL logged has caused a fair share of problems.
Amongst others it requires to do computations during recovery while
the database is not in a consistent state, delaying truncations till
checkpoints, and handling members being truncated, but offset not.
We tried to put bandaids on lots of these issues over the last
years, but it seems time to change course. Thus this patch
introduces WAL logging for multixact truncations. This allows: 1)
to perform the truncation directly during VACUUM, instead of
delaying it to the checkpoint. 2) to avoid looking at the offsets
SLRU for truncation during recovery, we can just use the master's
values. 3) simplify a fair amount of logic to keep in memory limits
straight, this has gotten much easier During the course of fixing
this a bunch of additional bugs had to be fixed: 1) Data was not
purged from memory the member's SLRU before deleting segments. This
happened to be hard or impossible to hit due to the interlock
between checkpoints and truncation. 2) find_multixact_start()
relied on SimpleLruDoesPhysicalPageExist - but that doesn't work for
offsets that haven't yet been flushed to disk. Add code to flush the
SLRUs to fix. Not pretty, but it feels slightly safer to only make
decisions based on actual on-disk state. 3) find_multixact_start()
could be called concurrently with a truncation and thus fail. Via
SetOffsetVacuumLimit() that could lead to a round of emergency
vacuuming. The problem remains in pg_get_multixact_members(), but
that's quite harmless. For now this is going to only get applied to
9.5+, leaving the issues in the older branches in place. It is quite
possible that we need to backpatch at a later point though. For the
case this gets backpatched we need to handle that an updated standby
may be replaying WAL from a not-yet upgraded primary. We have to
recognize that situation and use "old style" truncation (i.e.
looking at the SLRUs) during WAL replay. In contrast to before, this
now happens in the startup process, when replaying a checkpoint
record, instead of the checkpointer. Doing truncation in the
restartpoint is incorrect, they can happen much later than the
original checkpoint, thereby leading to wraparound. To avoid
"multixact_redo: unknown op code 48" errors standbys would have to
be upgraded before primaries. A later patch will bump the WAL page
magic, and remove the legacy truncation codepaths. Legacy truncation
support is just included to make a possible future backpatch easier.
Discussion: 20150621192409(dot)GA4797(at)alap3(dot)anarazel(dot)de Reviewed-By:
Robert Haas, Alvaro Herrera, Thomas Munro Backpatch: 9.5 for now
http://git.postgresql.org/pg/commitdiff/4f627f897367f15702d59973f75f6391d5d3e06f

Robert Haas pushed:

- Teach planstate_tree_walker about custom scans. This logic was
missing from ExplainPreScanNode, from which I derived
planstate_tree_walker. But it shouldn't be missing, especially not
from a generic walker function, so add it. KaiGai Kohei
http://git.postgresql.org/pg/commitdiff/262e56bcae4f529c47ce4fbb8306fd6e26762975

- Print a MergeJoin's mergeNullsFirst array as bool, not int. It's
declared as being an array of bool, but it's printed differently
from the way bool and arrays of bool are handled elsewhere. Patch
by Amit Kapila. Anomaly noted independently by Amit Kapila and
KaiGai Kohei.
http://git.postgresql.org/pg/commitdiff/4fe6f72bda7b4c3eb39560e3da50a093d833647d

- Add readfuncs.c support for plan nodes. For parallel query, we need
to be able to pass a Plan to a worker, so that it knows what it's
supposed to do. We could invent our own way of serializing plans
for that purpose, but piggybacking on the existing node
infrastructure seems like a much better idea. Initially, we'll
probably only support a limited number of nodes within parallel
workers, but this commit adds support for everything in plannodes.h
except CustomScan, because doing it all at once seems easier than
doing it piecemeal, and it makes testing this code easier, too.
CustomScan is excluded because making that work requires a larger
rework of that facility. Amit Kapila, reviewed and slightly revised
by me.
http://git.postgresql.org/pg/commitdiff/a0d9f6e434bb56f7e5441b7988f3982feead33b3

- Don't zero opfuncid when reading nodes. The comments here stated
that this was just in case we ever had an ALTER OPERATOR command
that could remap an operator to a different function. But those
comments have been here for a long time, and no such command has
come about. In the absence of such a feature, forcing the pg_proc
OID to be looked up again each time we reread a stored rule or
similar is just a waste of cycles. Moreover, parallel query needs a
way to reread the exact same node tree that was written out, not one
that has been slightly stomped on. So just get rid of this for now.
Per discussion with Tom Lane.
http://git.postgresql.org/pg/commitdiff/9f1255ac859364a86264a67729dbd1a36dd63ff2

Teodor Sigaev pushed:

- Allow autoanalyze to add pages deleted from pending list to FSM
Commit e95680832854cf300e64c10de9cc2f586df558e8 introduces adding
pages to FSM for ordinary insert, but autoanalyze was able just
cleanup pending list without adding to FSM. Also fix double call of
IndexFreeSpaceMapVacuum() during ginvacuumcleanup() Report from
Fujii Masao Patch by me Review by Jeff Janes
http://git.postgresql.org/pg/commitdiff/dc943ad952329a46ce8dc0103b0980e1fe5893a8

Fujii Masao pushed:

- Make pg_controldata report newest XID with valid commit timestamp.
Previously pg_controldata didn't report newestCommitTs and this was
an oversight in commit 73c986a. Also this patch changes
pg_resetxlog so that it uses the same sentences as pg_controldata
does, regarding oldestCommitTs and newestCommitTs, for the sake of
consistency. Back-patch to 9.5 where track_commit_timestamp was
added. Euler Taveira
http://git.postgresql.org/pg/commitdiff/18d938de7a99d698d33cc3a94507c8fe0a0005ab

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Gurjeet Singh sent in a patch to redefine GIST_MAX_SPLIT_PAGES so that
in case of a split, gistplacetopage() now throws an error when the
block-ids needed exceed 32.

Corey Huinker sent in another revision of a patch to allow setting
fetch size by foreign server and table.

Thomas Munro sent in another revision of a patch to strip "volatile"
setting from replication commands.

Dmitry Dolgov sent in two more revisions of a patch to fix some issues
with jsonb_set().

Rahila Syed sent in another revision of a patch to implement a vacuum
progress checker.

Jim Nasby sent in two revisions of a patch to fix some less than ideal
error reporting in pg_stat_statements.

Amit Kapila sent in two revisions of a patch to add parallel support
for all the plan and other nodes (like SubPlan which could be required
for worker) except CustomScan node.

David Rowley sent in three more revisions of a patch to use foreign
keys to improve join estimates.

Jeff Janes sent in a patch to invert ResolveRecoveryConflictWithLock
to be called back from the lmgr code so that is it like
ResolveRecoveryConflictWithBufferPin code.

Tom Lane sent in a patch to look for stats for bool functions.

Robert Haas and Amit Kapila traded patches for parallel seq scan.

Taiki Kondo sent in another revision of a patch to enable join
pushdown with table partitions.

Teodor Sigaev sent in another revision of a patch to fix a GIN vacuum
bug.

Stephen Frost sent in a patch to rename withCheckOptions to
insertedCheckClauses.

Thomas Munro sent in a patch to use the lockbit-on macro consistently.

Kaigai Kouhei sent in a patch to add custom scan on readfuncs.

Amir Rohan sent in a patch to fix in-core regression tests.

Rushabh Lathia sent in a patch to fix the HINT for slot name error
message.

Peter Eisentraut sent in a patch to documented the cluster_name and
update_process_title settings near one another.

Fabien COELHO sent in a patch to fix some computations in pgbench.

Tom Lane sent in a patch to fix some issues with pg_ctl/pg_rewind on
AIX.

Jeff Janes sent in a patch to add tab completion in psql for ALTER
COLUMN SET STATISTICS.

Teodor Sigaev sent in another revision of a patch to rework the access
method interface.

Nikolay Shaplov sent in two more revisions of a patch to allow showing
tuple data in pageinspect.

Takashi Horikawa sent in another revision of a patch to implement
partitioned checkpointing.

Paul Ramsey sent in another revision of a patch to enable FDW
extension support.

Alexander Shulgin sent in another revision of a patch to add on-demand
running query plans using auto_explain and signals.

Tomas Vondra sent in two more revisions of a patch to allow index
scans based on partial indexes.

Jinyu Zhang sent in a patch to optimize memory allocation in function
bringetbitmap().