== PostgreSQL Weekly News - September 28 2014 ==

== PostgreSQL Weekly News - September 28 2014 ==

== PostgreSQL Product News ==

pgBadger 6.1, a parallel PostgreSQL log analyzer written in Perl, released:
https://sourceforge.net/projects/pgbadger/
Development:
https://github.com/dalibo/pgbadger/

pgCluu 2.1, a Perl program audit PostgreSQL Cluster performance,
released.
http://pgcluu.darold.net/

== PostgreSQL Jobs for September ==

http://archives.postgresql.org/pgsql-jobs/2014-09/threads.php

== PostgreSQL Local ==

The fifth edition of the Argentinian PostgreSQL Day will be held on
November 28th, 2014 in Santa Fe, Argentina. The International CfP is
open through September 28th, 2014.
http://www.postgresql.org.ar/pgday2014

The 4th PgDay Ecuador will be held on Tuesday 7th in October at the
city of Quito, as part of the 5th International Congress of Free
Software. Send talk proposals to ecpug AT postgresql DOT org.

The sixth PGDay Cubano be held on 13 and 14 October 2014 in Habana.
https://postgresql.uci.cu/?p=380

PGConf.EU 2014 in Madrid, Spain on October 21-24 is now open for
registration.
http://2014.pgconf.eu/registration/

PGDay.IT 2014 will take place in Prato on November the 7th 2014. The
International Call For Papers is now open:
http://2014.pgday.it/call-for-papers-en/

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

Andres Freund pushed:

- Improve code around the recently added rm_identify rmgr callback.
There are four weaknesses
in728f152e07f998d2cb4fe5f24ec8da2c3bda98f2: append_init() in
heapdesc.c was ugly and required that rm_identify return values are
only valid till the next call. Instead just add a couple more
switch() cases for the INIT_PAGE cases. Now the returned value will
always be valid. A couple rm_identify() callbacks missed masking
xl_info with ~XLR_INFO_MASK. pg_xlogdump didn't map a NULL
rm_identify to UNKNOWN or a similar string. append_init() was
called when id=NULL - which should never actually happen. But it's
better to be careful.
http://git.postgresql.org/pg/commitdiff/604f7956b9460192222dd37bd3baea24cb669a47

- Remove postgres --help blurb about the removed -A option. I missed
this in 3bdcf6a5a755503. Noticed by Merlin Moncure. Discussion:
CAHyXU0yC7uPeeVzQROwtnrOP9dxTEUPYjB0og4qUnbipMEV57w(at)mail(dot)gmail(dot)com
http://git.postgresql.org/pg/commitdiff/0926ef43c160cc8334d3e86f364bf9a7aa2da43f

- Remove most volatile qualifiers from xlog.c. For the reason
outlined in df4077cda2e also remove volatile qualifiers from xlog.c.
Some of these uses of volatile have been added after noticing
problems back when spinlocks didn't imply compiler barriers. So they
are a good test - in fact removing the volatiles breaks when done
without the barriers in spinlocks present. Several uses of volatile
remain where they are explicitly used to access shared memory
without locks. These locations are ok with slightly out of date
data, but removing the volatile might lead to the variables never
being reread from memory. These uses could also be replaced by
barriers, but that's a separate change of doubtful value.
http://git.postgresql.org/pg/commitdiff/6ba4ecbf477e0b25dd7bde1b0c4e07fc2da19348

- Fix VPATH builds of the replication parser from git for some !gcc
compilers. Some compilers don't automatically search the current
directory for included files. 9cc2c182fc2 fixed that for builds from
tarballs by adding an include to the source directory. But that
doesn't work when the scanner is generated in the VPATH directory.
Use the same search path as the other parsers in the tree. One
compiler that definitely was affected is solaris' sun cc. Backpatch
to 9.1 which introduced using an actual parser for replication
commands.
http://git.postgresql.org/pg/commitdiff/56a312aac8802c2aa01530101d2d19e63568eeda

- Add a basic atomic ops API abstracting away platform/architecture
details. Several upcoming performance/scalability improvements
require atomic operations. This new API avoids the need to splatter
compiler and architecture dependent code over all the locations
employing atomic ops. For several of the potential usages it'd be
problematic to maintain both, a atomics using implementation and one
using spinlocks or similar. In all likelihood one of the
implementations would not get tested regularly under concurrency. To
avoid that scenario the new API provides a automatic fallback of
atomic operations to spinlocks. All properties of atomic operations
are maintained. This fallback - obviously - isn't as fast as just
using atomic ops, but it's not bad either. For one of the future
users the atomics ontop spinlocks implementation was actually
slightly faster than the old purely spinlock using implementation.
That's important because it reduces the fear of regressing older
platforms when improving the scalability for new ones. The API,
loosely modeled after the C11 atomics support, currently provides
'atomic flags' and 32 bit unsigned integers. If the platform
efficiently supports atomic 64 bit unsigned integers those are also
provided. To implement atomics support for a
platform/architecture/compiler for a type of atomics 32bit compare
and exchange needs to be implemented. If available and more
efficient native support for flags, 32 bit atomic addition, and
corresponding 64 bit operations may also be provided. Additional
useful atomic operations are implemented generically ontop of these.
The implementation for various versions of gcc, msvc and sun studio
have been tested. Additional existing stub implementations for Intel
icc, HUPX acc, and IBM xlc are included but have never been tested.
These will likely require fixes based on buildfarm and user
feedback. As atomic operations also require barriers for some
operations the existing barrier support has been moved into the
atomics code. Author: Andres Freund with contributions from Oskari
Saarenmaa Reviewed-By: Amit Kapila, Robert Haas, Heikki Linnakangas
and Álvaro Herrera Discussion:
CA+TgmoYBW+ux5-8Ja=Mcyuy8=VXAnVRHp3Kess6Pn3DMXAPAEA(at)mail(dot)gmail(dot)com,
20131015123303(dot)GH5300(at)awork2(dot)anarazel(dot)de,
20131028205522(dot)GI20248(at)awork2(dot)anarazel(dot)de
http://git.postgresql.org/pg/commitdiff/b64d92f1a5602c55ee8b27a7ac474f03b7aee340

- Fix atomic ops for x86 gcc compilers that don't understand atomic
intrinsics. Per buildfarm animal locust.
http://git.postgresql.org/pg/commitdiff/88bcdf9da5aa67da11ada0921703432ef2b7c21c

- Fix atomic ops inline x86 inline assembly for older 32bit gccs.
Some x86 32bit versions of gcc apparently generate references to the
nonexistant %sil register when using when using the r input
constraint, but not with the =q constraint. The latter restricts
allocations to a/b/c/d which should all work.
http://git.postgresql.org/pg/commitdiff/f18cad944911f05ad2e876af67362e28584b3c61

- Define META_FREE in a way that doesn't cause -Wempty-body warnings.
That get rids of the only -Wempty-body warning when compiling
postgres with gcc 4.8/9. As 6550b901f shows, it's useful to be able
to use that option routinely. Without asserts there's many more
warnings, but that's food for another commit.
http://git.postgresql.org/pg/commitdiff/9b6bb9b47153e4bf6d23bef13165491ba440fc23

- Fix a couple occurrences of 'the the' in the new atomics API.
Author: Erik Rijkers
http://git.postgresql.org/pg/commitdiff/a30199b01bd4b06bd13484baefb5f2f411ce14f4

- Further atomic ops portability improvements and bug fixes. Don't
play tricks for a more efficient pg_atomic_clear_flag() in the
generic gcc implementation. The old version was broken on gcc < 4.7
on !x86 platforms. Per buildfarm member chipmunk: Make usage of
__atomic() fences depend on. HAVE_GCC__ATOMIC_INT32_CAS instead of
HAVE_GCC__ATOMIC_INT64_CAS - there's platforms with 32bit support
that don't support 64bit atomics. Blindly fix two superflous #endif
in generic-xlc.h. Check for --disable-atomics in platforms but x86.
http://git.postgresql.org/pg/commitdiff/f9f07411a5b879b232ade66fece7071bd2eb5c26

Robert Haas pushed:

- Fix mishandling of CreateEventTrigStmt's eventname field. It's a
string, not a scalar. Petr Jelinek
http://git.postgresql.org/pg/commitdiff/763ba1b0f2aa8353e3e3b02bc6bd965deb4575e0

- Fix compiler warning. It is meaningless to declare a pass-by-value
return type const.
http://git.postgresql.org/pg/commitdiff/e38da8d6b1bd494b72a6f84310d30b55b3f67c3e

- Remove volatile qualifiers from lwlock.c. Now that spinlocks
(hopefully!) act as compiler barriers, as of commit
0709b7ee72e4bc71ad07b7120acd117265ab51d0, this should be safe. This
serves as a demonstration of the new coding style, and may be
optimized better on some machines as well.
http://git.postgresql.org/pg/commitdiff/df4077cda2eae3eb4a5cf387da0c1e7616e73204

- Change locking regimen around buffer replacement. Previously, we
used an lwlock that was held from the time we began seeking a
candidate buffer until the time when we found and pinned one, which
is disastrous for concurrency. Instead, use a spinlock which is
held just long enough to pop the freelist or advance the clock sweep
hand, and then released. If we need to advance the clock sweep
further, we reacquire the spinlock once per buffer. This represents
a significant increase in atomic operations around buffer eviction,
but it still wins on many workloads. On others, it may result in no
gain, or even cause a regression, unless the number of buffer
mapping locks is also increased. However, that seems like material
for a separate commit. We may also need to consider other methods
of mitigating contention on this spinlock, such as splitting it into
multiple locks or jumping the clock sweep hand more than one buffer
at a time, but those, too, seem like separate improvements. Patch
by me, inspired by a much larger patch from Amit Kapila. Reviewed
by Andres Freund.
http://git.postgresql.org/pg/commitdiff/5d7962c6797c0baae9ffb3b5b9ac0aec7b598bc3

- Fix identify_locking_dependencies for schema-only dumps. Without
this fix, parallel restore of a schema-only dump can deadlock,
because when the dump is schema-only, the dependency will still be
pointing at the TABLE item rather than the TABLE DATA item. Robert
Haas and Tom Lane
http://git.postgresql.org/pg/commitdiff/07d46a8963ebbf69ef6e6853bb8a45623612dd34

Stephen Frost pushed:

- Process withCheckOption exprs in setrefs.c. While withCheckOption
exprs had been handled in many cases by happenstance, they need to
be handled during set_plan_references and more specifically down in
set_plan_refs for ModifyTable plan nodes. This is to ensure that
the opfuncid's are set for operators referenced in the
withCheckOption exprs. Identified as an issue by Thom Brown Patch
by Dean Rasheed Back-patch to 9.4, where withCheckOption was
introduced.
http://git.postgresql.org/pg/commitdiff/6ef8c658af2127f4e62cb24feade8b465c9e2fea

- Log ALTER SYSTEM statements as DDL. Per discussion in bug #11350,
log ALTER SYSTEM commands at the log_statement=ddl level, rather
than at the log_statement=all level. Pointed out by Tomonari
Katsumata. Back-patch to 9.4 where ALTER SYSTEM was introduced.
http://git.postgresql.org/pg/commitdiff/43bed84c320443ee7247f7ca8d609640651aab93

- Add unicode_*_linestyle to \? variables. In a2dabf0 we added the
ability to have single or double unicode linestyle for the border,
column, or header. Unfortunately, the \? variables output was not
updated for these new psql variables. This corrects that oversight.
Patch by Pavel Stehule.
http://git.postgresql.org/pg/commitdiff/a564307373089fc81a07bce49236fe2bd66de0fe

- Copy-editing of row security. Address a few typos in the row
security update, pointed out off-list by Adam Brightwell. Also
include 'ALL' in the list of commands supported, for completeness.
http://git.postgresql.org/pg/commitdiff/afd1d95f5bf0cb48af77e5897eb4c356b5371c7b

- Code review for row security. Buildfarm member tick identified an
issue where the policies in the relcache for a relation were were
being replaced underneath a running query, leading to segfaults
while processing the policies to be added to a query. Similar to
how TupleDesc RuleLocks are handled, add in a equalRSDesc() function
to check if the policies have actually changed and, if not, swap
back the rsdesc field (using the original instead of the temporairly
built one; the whole structure is swapped and then specific fields
swapped back). This now passes a CLOBBER_CACHE_ALWAYS for me and
should resolve the buildfarm error. In addition to addressing this,
add a new chapter in Data Definition under Privileges which explains
row security and provides examples of its usage, change \d to always
list policies (even if row security is disabled- but note that it is
disabled, or enabled with no policies), rework check_role_for_policy
(it really didn't need the entire policy, but it did need to be
using has_privs_of_role()), and change the field in pg_class to
relrowsecurity from relhasrowsecurity, based on Heikki's suggestion.
Also from Heikki, only issue SET ROW_SECURITY in pg_restore when
talking to a 9.5+ server, list Bypass RLS in \du, and document
--enable-row-security options for pg_dump and pg_restore. Lastly,
fix a number of minor whitespace and typo issues from Heikki,
Dimitri, add a missing #include, per Peter E, fix a few minor
variable-assigned-but-not-used and resource leak issues from
Coverity and add tab completion for role attribute bypassrls as
well.
http://git.postgresql.org/pg/commitdiff/6550b901fe7c47c03775400e0c790c6c1234a017

- Fix relcache for policies, and doc updates. Andres pointed out that
there was an extra ';' in equalPolicies, which made me realize that
my prior testing with CLOBBER_CACHE_ALWAYS was insufficient (it
didn't always catch the issue, just most of the time). Thanks to
that, a different issue was discovered, specifically in
equalRSDescs. This change corrects eqaulRSDescs to return 'true'
once all policies have been confirmed logically identical. After
stepping through both functions to ensure correct behavior, I ran
this for about 12 hours of CLOBBER_CACHE_ALWAYS runs of the
regression tests with no failures. In addition, correct a few typos
in the documentation which were pointed out by Thom Brown (thanks!)
and improve the policy documentation further by adding a flushed out
usage example based on a unix passwd file. Lastly, clean up a few
comments in the regression tests and pg_dump.h.
http://git.postgresql.org/pg/commitdiff/ff27fcfa0affe16405e801ed55fed10e7bc75216

Tom Lane pushed:

- Fix incorrect search for "x?" style matches in creviterdissect().
When the number of allowed iterations is limited (either a "?"
quantifier or a bound expression), the last sub-match has to reach
to the end of the target string. The previous coding here first
tried the shortest possible match (one character, usually) and then
gave up and back-tracked if that didn't work, typically leading to
failure to match overall, as shown in bug #11478 from Christoph
Berg. The minimum change to fix that would be to not decrement k
before "goto backtrack"; but that would be a pretty stupid solution,
because we'd laboriously try each possible sub-match length before
finally discovering that only ending at the end can work. Instead,
force the sub-match endpoint limit up to the end for even the first
shortest() call if we cannot have any more sub-matches after this
one. Bug introduced in my rewrite that added the iterdissect logic,
commit 173e29aa5deefd9e71c183583ba37805c8102a72. The shortest-first
search code was too closely modeled on the longest-first code, which
hasn't got this issue since it tries a match reaching to the end to
start with anyway. Back-patch to all affected branches.
http://git.postgresql.org/pg/commitdiff/3694b4d7e1aa02f917f9d18c550fbb49b96efa83

- Fix bogus variable-mangling in security_barrier_replace_vars().
This function created new Vars with varno different from varnoold,
which is a condition that should never prevail before setrefs.c does
the final variable-renumbering pass. The created Vars could not be
seen as equal() to normal Vars, which among other things broke
equivalence-class processing for them. The consequences of this
were indeed visible in the regression tests, in the form of failure
to propagate constants as one would expect. I stumbled across it
while poking at bug #11457 --- after intentionally disabling join
equivalence processing, the security-barrier regression tests
started falling over with fun errors like "could not find pathkey
item to sort", because of failure to match the corrupted Vars to
normal ones.
http://git.postgresql.org/pg/commitdiff/3f6f9260e308a331e6809d5309b17d1613ff900f

Andrew Dunstan pushed:

- Fix typos in descriptions of json_object functions.
http://git.postgresql.org/pg/commitdiff/b1a52872ae68ce7fe9558a75295200a0d10d1f56

- Return NULL from json_object_agg if it gets no rows. This makes it
consistent with the docs and with all other builtin aggregates apart
from count().
http://git.postgresql.org/pg/commitdiff/ecacbdbcee67e202cfcaa1180da170b9f13654bb

- Remove ill-conceived ban on zero length json object keys. We
removed a similar ban on this in json_object recently, but the ban
in datum_to_json was left, which generate4d sprutious errors in
othee json generators, notable json_build_object. Along the way,
add an assertion that datum_to_json is not passed a null key. All
current callers comply with this rule, but the assertion will catch
any possible future misbehaviour.
http://git.postgresql.org/pg/commitdiff/9111d46351e8c3d82452a7454e43ccbf1991b3dc

Heikki Linnakangas pushed:

- Add -D option to specify data directory to pg_controldata and
pg_resetxlog. It was confusing that to other commands, like initdb
and postgres, you would pass the data directory with "-D datadir",
but pg_controldata and pg_resetxlog would take just plain path,
without the "-D". With this patch, pg_controldata and pg_resetxlog
also accept "-D datadir". Abhijit Menon-Sen, with minor kibitzing
by me
http://git.postgresql.org/pg/commitdiff/b0d81adea650a4bc2b9391234345bb935b89a694

- Refactor space allocation for base64 encoding/decoding in pgcrypto.
Instead of trying to accurately calculate the space needed, use a
StringInfo that's enlarged as needed. This is just moving things
around currently
- the old code was not wrong - but this is in preparation for a patch
that adds support for extra armor headers, and would make the space
calculation more complicated. Marko Tiikkaja
http://git.postgresql.org/pg/commitdiff/1dcfb8da09c47d2a7502d1dfab06c8be4b6cf323

Peter Eisentraut pushed:

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

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Antonin Houska sent in a patch to fix an issue with
from_collapse_limit.

Pavan Deolasee sent in a patch to fix an assertion failure in
synchronous replication.

Rahila Syed sent in another revision of a patch to compress full-page
writes.

Michael Paquier sent in a patch to remove code duplication in
walsender.c and syncrep.c in order to detect what is the node with the
lowest strictly-positive priority, facilitating maintenance of this
code.

Andrew Dunstan sent in a patch to add some missing JSONB conversion
functions.

Rajeev Rastogi sent in another revision of a patch to implement an
index scan optimization.

Alvaro Herrera sent in two more revisions of a patch to implement BRIN
indexes.

Robert Haas and Ants Aasma traded patches to help scale buffer
eviction.

Peter Geoghegan sent in two more revisions of a patch to add INSERT
... ON CONFLICT {UPDATE | IGNORE}.

Robert Haas sent in another revision of a patch to add the modulo (%)
operator to pgbench.

Abhijit Menon-Sen sent in a patch to fix an issue with END_OF_RECOVERY
shutdowns and ResetUnloggedRelations().

Alvaro Herrera and Michael Paquier traded patches to enable event
triggers to fire on CREATE.

Dilip Kumar sent in another revision of a patch to allow parallel
cores to be used by vacuumdb.

Tom Lane sent in another revision of a patch to fix the poor TOAST
compression for JSONB.

Andrew Dunstan sent in a patch to add JSONB generator functions.

Robert Haas sent in another revision of a patch to implement a
pg_background contrib extension, which runs commands in a background
worker, and gets the results.

Andrew (RhodiumToad) Gierth sent in another revision of a patch to
implement GROUPING SETS.

Andres Freund and Heikki Linnakangas traded patches to fix an issue
where it was impossible to escape from a blocked send().

Emre Hasegeli sent in another revision of a patch to implement
selectivity estimation for inet operators.

Stephen Frost sent in a patch to fix an infelicity between WITH CHECK
OPTION for views and column-level privileges.

Marko (johto) Tiikkaja sent in another revision of a patch to
implement PGP armor headers in the contrib/pgcrypto extension.

Jeff Janes sent in a patch to fix dynahash logging.

Michael Paquier and Fabrízio de Royes Mello traded patches to add
missing newlines in verbose logs of pg_dump, introduced by RLS patch.

Bogdan Pilch sent in a patch to allow toggling tab completion in psql.

Simon Riggs sent in another revision of a patch to allow disabling the
HOT optimization.

Platon Malugin sent in a patch to add generate_series(numeric,
numeric).

Bogdan Pilch sent in a patch to make psql's time output format more
readable.