== PostgreSQL Weekly News - November 09 2014 ==

== PostgreSQL Weekly News - November 09 2014 ==

== PostgreSQL Product News ==

Bucardo 5.2.0, a multi-source, multi-target replication system, released.
http://bucardo.org/wiki/Bucardo

pgpool-II 3.3.4, a connection pooler and replication system for
PostgreSQL, and pgpoolAdmin 3.4, a web-based UI for adminstering
pgpool, released.
http://pgpool.net/mediawiki/index.php/Downloads

PostgreSQL PHP Generator 14.10, a GUI CRUD web application generator for PostgreSQL, released.
http://www.sqlmaestro.com/products/postgresql/phpgenerator/

== PostgreSQL Jobs for November ==

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

== PostgreSQL Local ==

PG-Cuba will be November 25 and 26, 2014
http://postgresql.uci.cu/?p=1087#more-1087

The CfP for FOSDEM PGDay (January 30, 2015) and the FOSDEM PostgreSQL
Dev Room (January 31-February 1, 2015) are open until November 24th,
2014.
http://fosdem2015.pgconf.eu/callforpapers/

The CfP for PGConf US 2015 is open through December 17th, 2014
Notifications will go out on January 10, 2014. The event takes place
March 25-27, 2015 in NYC.
http://nyc.pgconf.us/2015/

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

Tom Lane pushed:

- Docs: fix incorrect spelling of contrib/pgcrypto option.
pgp_sym_encrypt's option is spelled "sess-key", not
"enable-session-key". Spotted by Jeff Janes. In passing, improve a
comment in pgp-pgsql.c to make it clearer that the debugging options
are intentionally undocumented.
http://git.postgresql.org/pg/commitdiff/f443de873e500de999a2d165731a0356b79a6ed7

- Drop no-longer-needed buffers during ALTER DATABASE SET TABLESPACE.
The previous coding assumed that we could just let buffers for the
database's old tablespace age out of the buffer arena naturally.
The folly of that is exposed by bug #11867 from Marc Munro: the user
could later move the database back to its original tablespace, after
which any still-surviving buffers would match lookups again and
appear to contain valid data. But they'd be missing any changes
applied while the database was in the new tablespace. This has been
broken since ALTER SET TABLESPACE was introduced, so back-patch to
all supported branches.
http://git.postgresql.org/pg/commitdiff/33f80f8480434f02beb940b8f0627d534f3fd3af

- Fix volatility markings of some contrib I/O functions. In general,
datatype I/O functions are supposed to be immutable or at worst
stable. Some contrib I/O functions were, through oversight, not
marked with any volatility property at all, which made them
VOLATILE. Since (most of) these functions actually behave
immutably, the erroneous marking isn't terribly harmful; but it can
be user-visible in certain circumstances, as per a recent bug report
from Joe Van Dyk in which a cast to text was disallowed in an
expression index definition. To fix, just adjust the declarations
in the extension SQL scripts. If we were being very fussy about
this, we'd bump the extension version numbers, but that seems like
more trouble (for both developers and users) than the problem is
worth. A fly in the ointment is that chkpass_in actually is
volatile, because of its use of random() to generate a fresh salt
when presented with a not-yet-encrypted password. This is bad
because of the general assumption that I/O functions aren't
volatile: the consequence is that records or arrays containing
chkpass elements may have input behavior a bit different from a bare
chkpass column. But there seems no way to fix this without breaking
existing usage patterns for chkpass, and the consequences of the
inconsistency don't seem bad enough to justify that. So for the
moment, just document it in a comment. Since we're not bumping
version numbers, there seems no harm in back-patching these fixes;
at least future installations will get the functions marked
correctly.
http://git.postgresql.org/pg/commitdiff/66c029c842629958b3ae0d389f24ea3407225723

- Make CREATE TYPE print warnings if a datatype's I/O functions are
volatile. This is a followup to commit
43ac12c6e6e397fd9142ed908447eba32d3785b2, which added regression
tests checking that I/O functions of built-in types are not marked
volatile. Complaining in CREATE TYPE should push developers of
add-on types to fix any misdeclared functions in their types. It's
just a warning not an error, to avoid creating upgrade problems for
what might be just cosmetic mis-markings. Aside from adding the
warning code, fix a number of types that were sloppily created in
the regression tests.
http://git.postgresql.org/pg/commitdiff/465d7e1882bc1f316c7cb2a68e751c34b403e8d7

- Remove the last vestige of server-side autocommit. Long ago we
briefly had an "autocommit" GUC that turned server-side autocommit
on and off. That behavior was removed in 7.4 after concluding that
it broke far too much client-side logic, and making clients cope
with both behaviors was impractical. But the GUC variable was left
behind, so as not to break any client code that might be trying to
read its value. Enough time has now passed that we should remove
the GUC completely. Whatever vestigial backwards-compatibility
benefit it had is outweighed by the risk of confusion for newbies
who assume it ought to do something, as per a recent complaint from
Wolfgang Wilhelm. In passing, adjust what seemed to me a rather
confusing documentation reference to libpq's autocommit behavior.
libpq as such knows nothing about autocommit, so psql is probably
what was meant.
http://git.postgresql.org/pg/commitdiff/525a489915cad1c5b2fc39e43e8005025afe63b2

- Fix normalization of numeric values in JSONB GIN indexes. The
default JSONB GIN opclass (jsonb_ops) converts numeric data values
to strings for storage in the index. It must ensure that numeric
values that would compare equal (such as 12 and 12.00) produce
identical strings, else index searches would have behavior different
from regular JSONB comparisons. Unfortunately the function charged
with doing this was completely wrong: it could reduce distinct
numeric values to the same string, or reduce equivalent numeric
values to different strings. The former type of error would only
lead to search inefficiency, but the latter type of error would
cause index entries that should be found by a search to not be
found. Repairing this bug therefore means that it will be necessary
for 9.4 beta testers to reindex GIN jsonb_ops indexes, if they care
about getting correct results from index searches involving numeric
data values within the comparison JSONB object. Per report from
Thomas Fanghaenel.
http://git.postgresql.org/pg/commitdiff/4875931938b27924fe8d6f91bbdb09e2e5a29d0a

- Cope with more than 64K phrases in a thesaurus dictionary.
dict_thesaurus stored phrase IDs in uint16 fields, so it would get
confused and even crash if there were more than 64K entries in the
configuration file. It turns out to be basically free to widen the
phrase IDs to uint32, so let's just do so. This was complained of
some time ago by David Boutin (in bug #7793); he later submitted an
informal patch but it was never acted on. We now have another
complaint (bug #11901 from Luc Ouellette) so it's time to make
something happen. This is basically Boutin's patch, but for
future-proofing I also added a defense against too many words per
phrase. Note that we don't need any explicit defense against
overflow of the uint32 counters, since before that happens we'd hit
array allocation sizes that repalloc rejects. Back-patch to all
supported branches because of the crash risk.
http://git.postgresql.org/pg/commitdiff/d6e37b35cda9a88dfd938dd61e9986dd93cc6dd3

Robert Haas pushed:

- pqmq.h needs to include something that defines StringInfo. Reported
by Peter Eisentraut.
http://git.postgresql.org/pg/commitdiff/585e0b9b277ad3b17a582b20daa8d09bd0ace8bd

- Fix thinko in commit 2bd9e412f92bc6a68f3e8bcb18e04955cc35001d.
Obviously, every translation unit should not be declaring this
separately. It needs to be PGDLLIMPORT as well, to avoid breaking
third-party code that uses any of the functions that the commit
mentioned above changed to macros.
http://git.postgresql.org/pg/commitdiff/c30be9787bd9808631b72843b0a93f06ce775dd0

- Update pg_xlogdump's .gitignore for brindesc.c.
http://git.postgresql.org/pg/commitdiff/99e8f08fab6fb8a10cb5ef060b9e4973b671d27d

- Use the sortsupport infrastructure in more cases. This removes some
fmgr overhead from cases such as btree index builds. Peter
Geoghegan, reviewed by Andreas Karlsson and me.
http://git.postgresql.org/pg/commitdiff/5ea86e6e65dd2da3e9a3464484985d48328e7fe3

- Introduce custom path and scan providers. This allows extension
modules to define their own methods for scanning a relation, and get
the core code to use them. It's unclear as yet how much use this
capability will find, but we won't find out if we never commit it.
KaiGai Kohei, reviewed at various times and in various levels of
detail by Shigeru Hanada, Tom Lane, Andres Freund, Álvaro Herrera,
and myself.
http://git.postgresql.org/pg/commitdiff/0b03e5951bf0a1a8868db13f02049cf686a82165

Heikki Linnakangas pushed:

- Switch to CRC-32C in WAL and other places. The old algorithm was
found to not be the usual CRC-32 algorithm, used by Ethernet et al.
We were using a non-reflected lookup table with code meant for a
reflected lookup table. That's a strange combination that AFAICS
does not correspond to any bit-wise CRC calculation, which makes it
difficult to reason about its properties. Although it has worked
well in practice, seems safer to use a well-known algorithm. Since
we're changing the algorithm anyway, we might as well choose a
different polynomial. The Castagnoli polynomial has better
error-correcting properties than the traditional CRC-32 polynomial,
even if we had implemented it correctly. Another reason for picking
that is that some new CPUs have hardware support for calculating
CRC-32C, but not CRC-32, let alone our strange variant of it. This
patch doesn't add any support for such hardware, but a future patch
could now do that. The old algorithm is kept around for tsquery and
pg_trgm, which use the values in indexes that need to remain
compatible so that pg_upgrade works. While we're at it, share the
old lookup table for CRC-32 calculation between hstore, ltree and
core. They all use the same table, so might as well.
http://git.postgresql.org/pg/commitdiff/5028f22f6eb0579890689655285a4778b4ffc460

- Remove support for 64-bit CRC. It hasn't been used for anything for
a long time.
http://git.postgresql.org/pg/commitdiff/404bc51cde9dce1c674abe4695635612f08fe27e

- Remove obsolete cases from GiST update redo code. The code that
generated a record to clear the F_TUPLES_DELETED flag hasn't existed
since we got rid of old-style VACUUM FULL. I kept the code that sets
the flag, although it's not used for anything anymore, because it
might still be interesting information for debugging purposes that
some tuples have been deleted from a page. Likewise, the code to
turn the root page from non-leaf to leaf page was removed when we
got rid of old-style VACUUM FULL. Remove the code to replay that
action, too.
http://git.postgresql.org/pg/commitdiff/2effb72e682a7dbdc9a8a60a80c22ec1fa9d8079

- Fix generation of SP-GiST vacuum WAL records. I broke these in
8776faa81cb651322b8993422bdd4633f1f6a487. Backpatch to 9.4, where
that was done.
http://git.postgresql.org/pg/commitdiff/1961b1c131e4211f3dc86ff2be971e430ab2a23d

- Fix building with WAL_DEBUG. Now that the backup blocks are
appended to the WAL record in xloginsert.c, XLogInsert doesn't see
them anymore and cannot remove them from the version reconstructed
for xlog_outdesc. This makes running with wal_debug=on more
expensive, as we now make (unnecessary) temporary copies of the
backup blocks, but it doesn't seem worth convoluting the code to
keep that optimization. Reported by Alvaro Herrera.
http://git.postgresql.org/pg/commitdiff/7250d8535b11d6443a9b27299e586c3df0654302

- Move the backup-block logic from XLogInsert to a new file,
xloginsert.c. xlog.c is huge, this makes it a little bit smaller,
which is nice. Functions related to putting together the WAL record
are in xloginsert.c, and the lower level stuff for managing WAL
buffers and such are in xlog.c. Also move the definition of
XLogRecord to a separate header file. This causes churn in the
#includes of all the files that write WAL records, and redo
routines, but it avoids pulling in xlog.h into most places.
Reviewed by Michael Paquier, Alvaro Herrera, Andres Freund and Amit
Kapila.
http://git.postgresql.org/pg/commitdiff/2076db2aea766c4c828dccc34ae35f614129000d

Peter Eisentraut pushed:

- doc: Move misplaced paragraph
http://git.postgresql.org/pg/commitdiff/e809fa2c80e051ed49a3ed2206dc055a10d588a4

- doc: Update pg_receivexlog note. The old note about how to use
pg_receivexlog as an alternative to archive_command was obsoleted by
replication slots.
http://git.postgresql.org/pg/commitdiff/552faefd68f1cec6bbaf0a038d8d410b59edb17f

- pg_basebackup: Adjust tests for long file name issues. Work around
accidental test failures because the working directory path is too
long by creating a temporary directory in the (hopefully shorter)
system location, symlinking that to the working directory, and
creating the tablespaces using the shorter path.
http://git.postgresql.org/pg/commitdiff/926f5cea472676b8378f02cd80c2c5f86226d981

Bruce Momjian pushed:

- C comment: mention why the Gregorian calendar is used pre-1582
http://git.postgresql.org/pg/commitdiff/171c377a0abe12d1418540dc154feccd9355995e

Fujii Masao pushed:

- Implement IF NOT EXIST for CREATE INDEX. Fabrízio de Royes Mello,
reviewed by Marti Raudsepp, Adam Brightwell and me.
http://git.postgresql.org/pg/commitdiff/08309aaf74ee879699165ec8a2d53e56f2d2e947

- Fix typo in comment. Etsuro Fujita
http://git.postgresql.org/pg/commitdiff/d2b8a2c7ec1098e7b98160ccdc0e3a513964fb08

- Prevent the unnecessary creation of .ready file for the timeline
history file. Previously .ready file was created for the timeline
history file at the end of an archive recovery even when WAL
archiving was not enabled. This creation is unnecessary and causes
.ready file to remain infinitely. This commit changes an archive
recovery so that it creates .ready file for the timeline history
file only when WAL archiving is enabled. Backpatch to all supported
versions.
http://git.postgresql.org/pg/commitdiff/5332b8cec54192c5d7b0ad67dc5668ccb917eeef

Álvaro Herrera pushed:

- Fix serial schedule. Test misc depends on brin, but it was earlier
in the serial schedule file. I didn't notice this because I only
run the parallel schedule, but the buildfarm exposed my folly ...
http://git.postgresql.org/pg/commitdiff/0e892e04efdc92abf53260e0bb0092cc48060e22

- BRIN: Block Range Indexes. BRIN is a new index access method
intended to accelerate scans of very large tables, without the
maintenance overhead of btrees or other traditional indexes. They
work by maintaining "summary" data about block ranges. Bitmap index
scans work by reading each summary tuple and comparing them with the
query quals; all pages in the range are returned in a lossy TID
bitmap if the quals are consistent with the values in the summary
tuple, otherwise not. Normal index scans are not supported because
these indexes do not store TIDs. As new tuples are added into the
index, the summary information is updated (if the block range in
which the tuple is added is already summarized) or not; in the
latter case, a subsequent pass of VACUUM or the
brin_summarize_new_values() function will create the summary
information. For data types with natural 1-D sort orders, the
summary info consists of the maximum and the minimum values of each
indexed column within each page range. This type of operator class
we call "Minmax", and we supply a bunch of them for most data types
with B-tree opclasses. Since the BRIN code is generalized, other
approaches are possible for things such as arrays, geometric types,
ranges, etc; even for things such as enum types we could do
something different than minmax with better results. In this commit
I only include minmax. Catalog version bumped due to new builtin
catalog entries. There's more that could be done here, but this is
a good step forwards. Loosely based on ideas from Simon Riggs; code
mostly by Álvaro Herrera, with contribution by Heikki Linnakangas.
Patch reviewed by: Amit Kapila, Heikki Linnakangas, Robert Haas.
Testing help from Jeff Janes, Erik Rijkers, Emanuel Calvo. p.s.:
The research leading to these results has received funding from the
European Union's Seventh Framework Programme (FP7/2007-2013) under
grant agreement n° 318633.
http://git.postgresql.org/pg/commitdiff/7516f5259411c02ae89e49084452dc342aadb2ae

- Fix some coding issues in BRIN. Reported by David Rowley: variadic
macros are a problem. Get rid of them using a trick suggested by
Tom Lane: add extra parentheses where needed. In the future we
might decide we don't need the calls at all and remove them, but it
seems appropriate to keep them while this code is still new. Also
from David Rowley: brininsert() was trying to use a variable before
initializing it. Fix by moving the brin_form_tuple call (which
initializes the variable) to within the locked section. Reported by
Peter Eisentraut: can't use "new" as a struct member name, because
C++ compilers will choke on it, as reported by cpluspluscheck.
http://git.postgresql.org/pg/commitdiff/b89ee54e20e722bb91f388667586a2e0986f197b

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Abhijit Menon-Sen sent in another revision of a patch to implement
pg_audit.

Alexey Vasiliev sent in two revisions of a patch to add a
recovery_timeout option to control the timeout of restore_command
nonzero status code.

Rahila Syed sent in two more revisions of a patch to allow compressing
full-page writes.

Andrew Dunstan sent in a patch to add a utility function to look up
the cast function for a from/to pair of types.

Heikki Linnakangas sent in another revision of a patch to change the
WAL format and APIs.

Ali Akbar sent in another revision of a patch to fix xpath() to return
namespace definitions.

Peter Geoghegan sent in another revision of a patch to implement
INSERT ... ON CONFLICT ...

Adam Brightwell sent in another revision of a patch to replace some of
the superuser() shortcuts with more specific role checks.

Michael Paquier sent in another revision of a patch to implement
REINDEX CONCURRENTLY.

Robert Haas sent in a PoC patch to implement group locking.

Abhijit Menon-Sen sent in another revision of a patch to fsync PGDATA
recursively in the case of crash recovery.

Fujii Masao sent in another revision of a patch to add a GIN GUC
pending_list_cleanup_size.

Jim Nasby sent in a patch to retry failed attempts to obtain the
cleanup lock on a buffer during VACUUM.

Etsuro Fujita sent in another revision of a patch to allow foreign
tables to be part of table inheritance hierarchies.

Dimitri Fontaine sent in another revision of a patch to add event
triggers for table rewrites.

Fujii Masao sent in another revision of a patch to add fsync feedback
to pg_receivexlog --status-interval.

Michael Paquier sent in a patch to add a dedicated macro to grab a
relation's persistence.

Ad sent in another revision of a patch to add jsonb generator
functions.

Michael Paquier sent in another revision of a patch to move all
quote-related functions into a single header quote.h.

Michael Banck sent in a patch to add a log_min_duration_transaction
GUC.

Tom Lane sent in two revisions of a patch to fix an infelicity between
index-only scans and row_to_json.

Magnus Hagander sent in a patch to re-order the views in the stats
docs for clarity.