== PostgreSQL Weekly News - November 18 2012 ==

== PostgreSQL Weekly News - November 18 2012 ==

The FOSDEM PGDay conference that will be held before FOSDEM in
Brussels, Belgium, on Feb 1st, 2013. The CfP for both this event and
for the PG track of FOSDEM are open.
http://fosdem2013.pgconf.eu/callforpapers/

PGDay NYC 2013 will be held on March 22, 2013 in New York City. The
CfP submission deadline is January 7th, 2013 at noon eastern time.
papers AT nycpug DOT org.
http://pgday.nycpug.org/speakers

== PostgreSQL Product News ==

pgBadger 2.2, a PostgreSQL log analyzer written in Perl, released:
https://github.com/dalibo/pgbadger/downloads

== PostgreSQL Jobs for November ==

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

== PostgreSQL Local ==

The sixth edition of the Italian PostgreSQL Day (PGDay.IT 2012) will
be held on November 23, 2012 in Prato, Tuscany. The International
Call for Papers is now open. Talks and presentations in English are
accepted. Information in English for papers submission is available
at:
http://2012.pgday.it/call-for-papers/international-call-for-papers/

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

Heikki Linnakangas pushed:

- Silence "expression result unused" warnings in
AssertVariableIsOfTypeMacro. At least clang 3.1 generates those
warnings. Prepend (void) to avoid them, like we have in AssertMacro.
http://git.postgresql.org/pg/commitdiff/c9d44a75d48ed8a9b9275b95be1fadaa562f3826

- Use correct text domain for translating errcontext() messages.
errcontext() is typically used in an error context callback
function, not within an ereport() invocation like e.g errmsg and
errdetail are. That means that the message domain that the
TEXTDOMAIN magic in ereport() determines is not the right one for
the errcontext() calls. The message domain needs to be determined by
the C file containing the errcontext() call, not the file containing
the ereport() call. Fix by turning errcontext() into a macro that
passes the TEXTDOMAIN to use for the errcontext message.
"errcontext" was used in a few places as a variable or struct field
name, I had to rename those out of the way, now that errcontext is a
macro. We've had this problem all along, but this isn't doesn't
seem worth backporting. It's a fairly minor issue, and turning
errcontext from a function to a macro requires at least a recompile
of any external code that calls errcontext().
http://git.postgresql.org/pg/commitdiff/dbdf9679d7d61b03a3bf73af9b095831b7010eb5

- Oops, have to rename local variables called 'errcontext' in contrib,
too. As pointed out by Alvaro Herrera.
http://git.postgresql.org/pg/commitdiff/d092d116ed047ebb923ada112235a8866571731c

Peter Eisentraut pushed:

- Use a stamp file for the XSLT HTML doc build. This way it works
more like the DSSSL build, and dependencies are tracked better by
make. Also copy the CSS stylesheet to the html directory. This was
forgotten when the output directory was changed.
http://git.postgresql.org/pg/commitdiff/9b3ac49e5afb54c222095dcaf6a73b634ab2c072

- doc: Add link to CREATE TABLE AS on CREATE TABLE reference page.
Karl O. Pinc
http://git.postgresql.org/pg/commitdiff/8f40ad1f4e75d3def4e86b665c8048a4c9676427

- doc: Put commas in the right place on pg_restore reference page.
Karl O. Pinc
http://git.postgresql.org/pg/commitdiff/817c186ea31ca4e304a1b55540433a1287f9fb7a

- Add -Wlogical-op to standard compiler flags, if supported.
http://git.postgresql.org/pg/commitdiff/67c03c6f3ca3726fd99488b497432dea21ab8e2a

- doc: Put pg_temp into documentation index. Karl O. Pinc
http://git.postgresql.org/pg/commitdiff/6b6633ad6cf663c81b5e0bc0c40709bf9d8d0299

Tom Lane pushed:

- Fix multiple problems in WAL replay. Most of the replay functions
for WAL record types that modify more than one page failed to ensure
that those pages were locked correctly to ensure that concurrent
queries could not see inconsistent page states. This is a hangover
from coding decisions made long before Hot Standby was added, when
it was hardly necessary to acquire buffer locks during WAL replay at
all, let alone hold them for carefully-chosen periods. The key
problem was that RestoreBkpBlocks was written to hold lock on each
page restored from a full-page image for only as long as it took to
update that page. This was guaranteed to break any WAL replay
function in which there was any update-ordering constraint between
pages, because even if the nominal order of the pages is the right
one, any mixture of full-page and non-full-page updates in the same
record would result in out-of-order updates. Moreover, it wouldn't
work for situations where there's a requirement to maintain lock on
one page while updating another. Failure to honor an update
ordering constraint in this way is thought to be the cause of bug
#7648 from Daniel Farina: what seems to have happened there is that
a btree page being split was rewritten from a full-page image before
the new right sibling page was written, and because lock on the
original page was not maintained it was possible for hot standby
queries to try to traverse the page's right-link to the
not-yet-existing sibling page. To fix, get rid of RestoreBkpBlocks
as such, and instead create a new function RestoreBackupBlock that
restores just one full-page image at a time. This function can be
invoked by WAL replay functions at the points where they would
otherwise perform non-full-page updates; in this way, the physical
order of page updates remains the same no matter which pages are
replaced by full-page images. We can then further adjust the logic
in individual replay functions if it is necessary to hold buffer
locks for overlapping periods. A side benefit is that we can
simplify the handling of concurrency conflict resolution by moving
that code into the record-type-specfic functions; there's no more
need to contort the code layout to keep conflict resolution in front
of the RestoreBkpBlocks call. In connection with that, standardize
on zero-based numbering rather than one-based numbering for
referencing the full-page images. In HEAD, I removed the macros
XLR_BKP_BLOCK_1 through XLR_BKP_BLOCK_4. They are still there in
the header files in previous branches, but are no longer used by the
code. In addition, fix some other bugs identified in the course of
making these changes: spgRedoAddNode could fail to update the parent
downlink at all, if the parent tuple is in the same page as either
the old or new split tuple and we're not doing a full-page image: it
would get fooled by the LSN having been advanced already. This
would result in permanent index corruption, not just transient
failure of concurrent queries. Also, ginHeapTupleFastInsert's
"merge lists" case failed to mark the old tail page as a candidate
for a full-page image; in the worst case this could result in
torn-page corruption. heap_xlog_freeze() was inconsistent about
using a cleanup lock or plain exclusive lock: it did the former in
the normal path but the latter for a full-page image. A plain
exclusive lock seems sufficient, so change to that. Also, remove
gistRedoPageDeleteRecord(), which has been dead code since VACUUM
FULL was rewritten. Back-patch to 9.0, where hot standby was
introduced. Note however that 9.0 had a significantly different
WAL-logging scheme for GIST index updates, and it doesn't appear
possible to make that scheme safe for concurrent hot standby
queries, because it can leave inconsistent states in the index even
between WAL records. Given the lack of complaints from the field,
we won't work too hard on fixing that branch.
http://git.postgresql.org/pg/commitdiff/3bbf668de9f1bc172371681e80a4e769b6d014c8

- Fix memory leaks in record_out() and record_send(). record_out()
leaks memory: it fails to free the strings returned by the
per-column output functions, and also is careless about detoasted
values. This results in a query-lifespan memory leakage when
returning composite values to the client, because printtup() runs
the output functions in the query-lifespan memory context. Fix it
to handle these issues the same way printtup() does. Also fix a
similar leakage in record_send(). (At some point we might want to
try to run output functions in shorter-lived memory contexts, so
that we don't need a zero-leakage policy for them. But that would
be a significantly more invasive patch, which doesn't seem like
material for back-patching.) In passing, use
appendStringInfoCharMacro instead of appendStringInfoChar in the
innermost data-copying loop of record_out, to try to shave a few
cycles from this function's runtime. Per trouble report from Carlos
Henrique Reimer. Back-patch to all supported versions.
http://git.postgresql.org/pg/commitdiff/273986bf0d39e5166eb15ba42ebff4803e23a688

- Fix the int8 and int2 cases of (minimum possible integer) % (-1).
The correct answer for this (or any other case with arg2 = -1) is
zero, but some machines throw a floating-point exception instead of
behaving sanely. Commit f9ac414c35ea084ff70c564ab2c32adb06d5296f
dealt with this in int4mod, but overlooked the fact that it also
happens in int8mod (at least on my Linux x86_64 machine). Protect
int2mod as well; it's not clear whether any machines fail there
(mine does not) but since the test is so cheap it seems better safe
than sorry. While at it, simplify the original guard in int4mod: we
need only check for arg2 == -1, we don't need to check arg1
explicitly. Xi Wang, with some editing by me.
http://git.postgresql.org/pg/commitdiff/a235b85a0ba06666dbbfdb9249a65dbfa9b42ebd

- Improve check_partial_indexes() to consider join clauses in proof
attempts. Traditionally check_partial_indexes() has only looked at
restriction clauses while trying to prove partial indexes usable in
queries. However, join clauses can also be used in some cases;
mainly, that a strict operator on "x" proves an "x IS NOT NULL"
index predicate, even if the operator is in a join clause rather
than a restriction clause. Adding this code fixes a regression in
9.2, because previously we would take join clauses into account when
considering whether a partial index could be used in a nestloop
inner indexscan path. 9.2 doesn't handle nestloop inner indexscans
in the same way, and this consideration was overlooked in the
rewrite. Moving the work to check_partial_indexes() is a better
solution anyway, since the proof applies whether or not we actually
use the index in that particular way, and we don't have to do it
over again for each possible outer relation. Per report from Dave
Cramer.
http://git.postgresql.org/pg/commitdiff/1746ba92565d130c3b5c6fc285ac57f00cf6bf75

- Assert that WaitLatch's timeout is not more than INT_MAX
milliseconds. The behavior with larger values is unspecified by the
Single Unix Spec. It appears that BSD-derived kernels report
EINVAL, although Linux does not. If waiting for longer intervals is
desired, the calling code has to do something to limit the delay; we
can't portably fix it here since "long" may not be any wider than
"int" in the first place. Part of response to bug #7670, though
this change doesn't fix that (in fact, it converts the problem from
an ERROR into an Assert failure). No back-patch since it's just an
assertion addition.
http://git.postgresql.org/pg/commitdiff/14ddff44c22cb358775d5aad6953f0ce0fdb64cf

- Fix syslogger to not fail when log_rotation_age exceeds 2^31
milliseconds. We need to avoid calling WaitLatch with timeouts
exceeding INT_MAX. Fortunately a simple clamp will do the trick,
since no harm is done if the wait times out before it's really time
to rotate the log file. Per bug #7670 (probably bug #7545 is the
same thing, too). In passing, fix bogus definition of
log_rotation_age's maximum value in guc.c --- it was numerically
right, but only because MINS_PER_HOUR and SECS_PER_MINUTE have the
same value. Back-patch to 9.2. Before that, syslogger wasn't using
WaitLatch.
http://git.postgresql.org/pg/commitdiff/d038966ddb918872700f9f21affbc84d6bc2c029

- Limit values of archive_timeout, post_auth_delay,
auth_delay.milliseconds. The previous definitions of these GUC
variables allowed them to range up to INT_MAX, but in point of fact
the underlying code would suffer overflows or other errors with
large values. Reduce the maximum values to something that won't
misbehave. There's no apparent value in working harder than this,
since very large delays aren't sensible for any of these. (Note:
the risk with archive_timeout is that if we're late checking the
state, the timestamp difference it's being compared to might
overflow. So we need some amount of slop; the choice of INT_MAX/2
is arbitrary.) Per followup investigation of bug #7670. Although
this isn't a very significant fix, might as well back-patch.
http://git.postgresql.org/pg/commitdiff/b6e3798f3aa2747db145f25e03a8d34f2e5ec8c8

Simon Riggs pushed:

- Clarify docs on hot standby lock release. Andres Freund and Simon
Riggs
http://git.postgresql.org/pg/commitdiff/68f7fe140bebc2b9bd2953934539cedb603fd01b

- Skip searching for subxact locks at commit. At commit all standby
locks are released for the top-level transaction, so searching for
locks for each subtransaction is both pointless and costly (N^2) in
the presence of many AccessExclusiveLocks.
http://git.postgresql.org/pg/commitdiff/d9fad1076da4f3686b16e7c9f137118a3156a43a

Bruce Momjian pushed:

- Adjust find_status for newer Linux 'nm' output format.
http://git.postgresql.org/pg/commitdiff/3bdfd9cb9e3731a5c27f31db5330b81f170a5db6

- Mark pg_upgrade's free_db_and_rel_infos() as a static function.
http://git.postgresql.org/pg/commitdiff/dec10ba4c5dc3ce09de12a799712313b9d854d6c

- In pg_upgrade, add third meaningless parameter to open().
http://git.postgresql.org/pg/commitdiff/546d65d55fd67c40e6f31d16cc8fa6e9afd3a403

- In pg_upgrade, copy fsm, vm, and extent files by checking for file
existence via open(), rather than collecting a directory listing and
looking up matching relfilenode files with sequential scans of the
array. This speeds up pg_upgrade by 2x for a large number of
tables, e.g. 16k. Per observation by Ants Aasma.
http://git.postgresql.org/pg/commitdiff/29add0de4920e4f448a30bfc35798b939c211d97

== Rejected Patches (for now) ==

Karl O. Pinc's patch to document the automatically created names of
constraints and indexes. We're not guaranteeing these will remain
stable.

Nicholas White's patch to fix a mis-compilation on cygwin and mingw.
Turned out to be a misconfiguration.

== Pending Patches ==

Kyotaro HORIGUCHI sent in another revision of the patch to reduce
palloc's in numeric operations.

Kyotaro HORIGUCHI sent in another revision of the identity projection
patch.

Asif Rehman sent in a patch intended to fix a problem in PL/pgsql
where functions can't return a row expression.

Karl O. Pinc sent in another revision of the patch to add a
--truncate-tables option to pg_restore.

Andrew Dunstan sent in a patch to make vacuumlo use a cursor.

Dimitri Fontaine sent in a RFC patch to implement pg_dump
--extension-script.

Peter Eisentraut sent in a patch to enable using LDAP URLs in
pg_hba.conf.

Karl O. Pinc sent in a patch to document when search_path is used in
secure functions.

Peter Eisentraut sent in a patch to add CREATE RECURSIVE VIEW.

Peter Geoghegan sent in a patch to recommend settings for the
commit_delay parameter.

Abhijit Menon-Sen sent in two revisions of a patch to create a binary
heap implementation.

Etsuro Fujita sent in another revision of the patch to add PRE- and
POST-processor options to COPY.

Atri Sharma sent in another revision of a patch to mitigate hint bit
I/O.

Alvaro Herrera sent in another revision of the patch to implement
foreign key locks.

Kevin Grittner sent in a patch to implement materialized views.

Jeff Davis sent in two more revisions of a patch to add disk page
checksums.

Andres Freund sent in a placeholder patch, to be replaced by Abhijit
Menon-Sen's above, for a binary heap implementation.

Andres Freund sent in another revision of the patch to a add new
function pg_relation_by_filenode to look up a relation given the
tablespace and the filenode OIDs.

Andres Freund sent in another revision of a patch to add a new
RELFILENODE syscache to fetch a pg_class entry via (reltablespace,
relfilenode).

Andres Freund sent in new revisions of replication design document
and a snapshot building design document.

Andres Freund sent in another revision of a patch to introduce
InvalidCommandId and declare that to be the new maximum for
CommandCounterIncrement.

Andres Freund sent in another revision of a patch to allow walsenders
to connect to a specific database.

Andres Freund sent in another revision of a patch to introduce
pg_receivellog, the pg_receivexlog equivalent for logical changes.
Michael Paquier sent in a follow-on patch adding the installer for
pg_receivellog.

Andres Freund sent in another revision of a patch to add a simple
decoding module in contrib named 'test_decoding'.

Andres Freund sent in another revision of a patch to Introduce wal
decoding via catalog timetravel.

Andres Freund sent in another revision of a patch to store the number
of subtransactions in xl_running_xacts separately from toplevel xids.

Andres Freund sent in another revision of a patch to add simple
xlogdump tool.

Andres Freund and Alvaro Herrera traded patches to add support for a
generic wal reading facility dubbed XLogReader.

Andres Freund sent in another revision of a patch to adjust all
*Satisfies routines to take a HeapTuple instead of a HeapTupleHeader.

Andres Freund sent in another revision of a patch to add a new
relmapper.c function RelationMapFilenodeToOid that acts as a reverse
of RelationMapOidToFilenode.

Amit Kapila sent in two more revisions of a patch to allow
postgresql.conf values to be changed via SQL.

Shigeru HANADA sent in another revision of a patch to add a FDW for
PostgreSQL.

KaiGai Kohei sent in another revision of a patch to refactor the ALTER
commands.

Karl O. Pinc sent in a patch to enable controlling the destination of
psql's error stream.

KaiGai Kohei sent in another revision of a patch to implement
row-level access control.

KaiGai Kohei sent in two more revisions of a patch to implement
OAT_POST_ALTER object access hooks.

Greg Smith sent in a WIP patch saving Timing Events via a new hook.

Amit Kapila sent in a patch to make pg_basebackup and pg_receivexlog
non-blocking.

Heikki Linnakangas sent in two more revisions of a patch to enable
switching timelines over streaming replication.

Robert Haas sent in another revision of a patch to deal with tuplesort
memory usage via grow_memtuples.

KaiGai Kohei sent in another revision of a patch to enable writing to
foreign tables.

Fujii Masao sent in a patch extending pg_trgm to be able to use a GIN
index in partial matches.

Pavel Stehule sent in a patch to add a width specification for s
specifier (format function) and fix behavior when both positional and
ordered placeholders are used.

Amit Kapila sent in another revision of a patch to compute the max LSN of
data pages.

Euler Taveira de Oliveira sent in another revision of a patch to
enable changing global configuration settings from SQL.

Phil Sorber sent in another revision of a patch to implement a pg_ping
utility.

Simon Riggs sent in a partial WIP patch implementing part of Jeff
Davis's suggestion as to how to reduce the number of hint bits.

Alexander Korotkov sent in a WIP patch to enable GIN indexes to store
additional information with item pointers in posting lists and trees.

Tomas Vondra sent in another revision of a patch to allow reducing the
logging load via not writing all logs.

JiangGuiqing sent in a patch to fix ecpg library installations on
Windows/MSVC.