== PostgreSQL Weekly News - July 28 2013 ==

== PostgreSQL Weekly News - July 28 2013 ==

The CfP for Italian PostgreSQL day has been extended until August 4, 2013.
http://2013.pgday.it/index.php/programma/call-for-papers-english/

== PostgreSQL Jobs for July ==

http://archives.postgresql.org/pgsql-jobs/2013-07/threads.php

== PostgreSQL Local ==

PostgreSQL Brazil will be held August 15-17, 2013 in Porto Velho, RO,
Brazil.
http://pgbr.postgresql.org.br/2013/chamada.en.php

Postgres Open 2013 will be in Chicago, IL, USA, September 16-18.
The list of talks has been posted on the site.
http://postgresopen.org/

The Italian PGDay (PGDay.IT) will be held on October the 25th in
Prato, Tuscany, Italy, at Monash University Center.
The International Call For Papers is open:
http://2013.pgday.it/index.php/programma/call-for-papers-english/

pgconf.EU 2013 will be held on Oct 29-Nov 1, 2013 at the Conrad Hotel
in downtown Dublin, Ireland. The CfP is open.
http://2013.pgconf.eu/

PGConf.DE 2013 will be held November 8th, 2013, at the Rhineland
Industrial Museum in Oberhausen. The CfP is open through September
15, 2013.
http://2013.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 ==

Robert Haas pushed:

- Add infrastructure for mapping relfilenodes to relation OIDs.
Future patches are expected to introduce logical replication that
works by decoding WAL. WAL contains relfilenodes rather than
relation OIDs, so this infrastructure will be needed to find the
relation OID based on WAL contents. If logical replication does not
make it into this release, we probably should consider reverting
this, since it will add some overhead to DDL operations that create
new relations. One additional index insert per pg_class row is not
a large overhead, but it's more than zero. Another way of meeting
the needs of logical replication would be to the relation OID to
WAL, but that would burden DML operations, not only DDL. Andres
Freund, with some changes by me. Design review, in earlier
versions, by Álvaro Herrera.
http://git.postgresql.org/pg/commitdiff/f01d1ae3a104019d6d68aeff85c4816a275130b3

- Adjust HeapTupleSatisfies* routines to take a HeapTuple.
Previously, these functions took a HeapTupleHeader, but upcoming
patches for logical replication will introduce new a new snapshot
type under which the tuple's TID will be used to lookup (CMIN, CMAX)
for visibility determination purposes. This makes that information
available. Code churn is minimal since HeapTupleSatisfiesVisibility
took the HeapTuple anyway, and deferenced it before calling the
satisfies function. Independently of logical replication, this
allows t_tableOid and t_self to be cross-checked via assertions in
tqual.c. This seems like a useful way to make sure that all callers
are setting these values properly, which has been previously put
forward as desirable. Andres Freund, reviewed by Álvaro Herrera
http://git.postgresql.org/pg/commitdiff/0518eceec3a1cc2b71da04e839f05f555fdd8567

- Remove bgw_sighup and bgw_sigterm. Per discussion on pgsql-hackers,
these aren't really needed. Interim versions of the background
worker patch had the worker starting with signals already unblocked,
which would have made this necessary. But the final version does
not, so we don't really need it; and it doesn't work well with the
new facility for starting dynamic background workers, so just rip it
out. Also per discussion on pgsql-hackers, back-patch this change
to 9.3. It's best to get the API break out of the way before we do
an official release of this facility, to avoid more pain for
extension authors later.
http://git.postgresql.org/pg/commitdiff/f40a318eeaed0c66fcb2ddd442006e54bf49c634

- pgrowlocks: Use GetActiveSnapshot() rather than SnapshotNow. Per
discussion, it's desirable to eliminate all remaining uses of
SnapshotNow, because it has unpleasant semantics: race conditions
can result in seeing multiple versions of a concurrently updated
row, or none at all. By using GetActiveSnapshot() here, callers
will see exactly those rows that would have been visible if the
invoking query had scanned the table using, for example, a SELECT
statement. This is slightly different from the old behavior,
because commits that happen concurrently with the scan will not
affect the results. In REPEATABLE READ or SERIALIZABLE modes, where
transaction snapshots are used, commits that have happened since the
start of the transaction will also not affect the results. It is
hoped that this minor incompatibility will be thought an
improvement, or at least no worse than what we did before.
http://git.postgresql.org/pg/commitdiff/2e44770fa39051f404f7d94fed557b359b1dba3c

- Fix cache flush hazard in ExecRefreshMatView. Andres Freund
http://git.postgresql.org/pg/commitdiff/21e28e4531e761e7cdf1b9a0bbf0c06f6107686a

- Additional regression tests for ALTER OPERATOR FAMILY. Robins
Tharakan, reviewed by Szymon Guz
http://git.postgresql.org/pg/commitdiff/e6055061c524698918ab0b7a0c51b822c03ef1fa

- Use InvalidSnapshot, now SnapshotNow, as the default snapshot. As
far as I can determine, there's no code in the core distribution
that fails to explicitly set the snapshot of a scan or executor
state. If there is any such code, this will probably cause it to
seg fault; friendlier suggestions were discussed on pgsql-hackers,
but there was no consensus that anything more than this was needed.
This is another step towards the hoped-for complete removal of
SnapshotNow.
http://git.postgresql.org/pg/commitdiff/765ad89be36f699e2d65238c1f0458a1ab7e4d8b

- Don't use SnapshotNow in get_actual_variable_range. Instead, use
the active snapshot. Per Tom Lane, this function is most interested
in knowing the range of tuples our scan will actually see. This is
another step towards full removal of SnapshotNow.
http://git.postgresql.org/pg/commitdiff/3483f4332de73e038db64be0614219b64fd3c971

- pgstattuple: Use SnapshotDirty, not SnapshotNow. Tuples belonging
to uncommitted transactions should not be counted as dead. This is
arguably a bug fix that should be back-patched, but as no one ever
noticed until it came time to try to get rid of SnapshotNow, I'm
only doing this in master for now.
http://git.postgresql.org/pg/commitdiff/80c79ab2a8d63bc6c83269b29c1ba993c8077d3a

- pgstattuple: Doc update for previous commit. In my previous change
to make pgstattuple use SnapshotDirty rather than SnapshotNow, I
failed to notice that the documenation also needed to be updated to
match. Fix.
http://git.postgresql.org/pg/commitdiff/fd27b999196959bd20d777a1010c786feb1586c2

- Change currtid functions to use an MVCC snapshot, not SnapshotNow.
This has a slight performance cost, but the only known consumers of
these functions, known at the SQL level as currtid and currtid2, is
pgsql-odbc; whose usage, we hope, is not sufficiently intensive to
make this a problem. Per discussion.
http://git.postgresql.org/pg/commitdiff/ed93feb80891b131e9676e962256cc2b18aa5e78

Alvaro Herrera pushed:

- Silence compiler warning on an unused variable. Also, tweak wording
in comments (per Andres) and documentation (myself) to point out
that it's the database's default tablespace that can be passed as 0,
not DEFAULTTABLESPACE_OID. Robert Haas noticed the bug in the code,
but didn't update the accompanying prose.
http://git.postgresql.org/pg/commitdiff/0aeb5ae2041520f02cabbc7083aec46733689bce

- Tweak FOR UPDATE/SHARE error message wording (again). In commit
0ac5ad5134 I changed some error messages from "FOR UPDATE/SHARE" to
a rather long gobbledygook which nobody liked. Then, in commit
cb9b66d31 I changed them again, but the alternative chosen there was
deemed suboptimal by Peter Eisentraut, who in message
1373937980(dot)20441(dot)8(dot)camel(at)vanquo(dot)pezone(dot)net proposed an alternative
involving a dynamically-constructed string based on the actual
locking strength specified in the SQL command. This patch
implements that suggestion.
http://git.postgresql.org/pg/commitdiff/c359a1b0823f798fc419adea5da7991845c915aa

- Check for NULL result from strdup. Per Coverity Scan
http://git.postgresql.org/pg/commitdiff/bb686c9a865dc15a704e6a96367b3d7bfe79df6f

Tatsuo Ishii pushed:

- Add --rate option. This controls the target transaction rate to
certain tps, rather than maximum. Patch contributed by Fabien
COELHO, reviewed by Greg Smith, and slight editing by me.
http://git.postgresql.org/pg/commitdiff/fc9f4e9f8c981bbc050e5566cf558112c938da2c

Peter Eisentraut pushed:

- ecpg: Move function prototype into header file.
PGTYPEStimestamp_defmt_scan() was declared twice inside different .c
files, with slightly different prototypes. Move it into a header
file and correct the prototype.
http://git.postgresql.org/pg/commitdiff/9feeef92fb9b73371e3f299ba444f86b4ef7e26f

- doc: Remove tab from SGML file
http://git.postgresql.org/pg/commitdiff/2f1fa75a0c9327f1202b80c78ece1d33534bfd10

- Message style improvements
http://git.postgresql.org/pg/commitdiff/626092a2e1784479b17810956b4654acf5b5ec11

Tom Lane pushed:

- Change post-rewriter representation of dropped columns in
joinaliasvars. It's possible to drop a column from an input table
of a JOIN clause in a view, if that column is nowhere actually
referenced in the view. But it will still be there in the JOIN
clause's joinaliasvars list. We used to replace such entries with
NULL Const nodes, which is handy for generation of RowExpr expansion
of a whole-row reference to the view. The trouble with that is that
it can't be distinguished from the situation after subquery pull-up
of a constant subquery output expression below the JOIN. Instead,
replace such joinaliasvars with null pointers (empty expression
trees), which can't be confused with pulled-up expressions.
expandRTE() still emits the old convention, though, for convenience
of RowExpr generation and to reduce the risk of breaking extension
code. In HEAD and 9.3, this patch also fixes a problem with some
new code in ruleutils.c that was failing to cope with
implicitly-casted joinaliasvars entries, as per recent report from
Feike Steenbergen. That oversight was because of an inadequate
description of the data structure in parsenodes.h, which I've now
corrected. There were some pre-existing oversights of the same ilk
elsewhere, which I believe are now all fixed.
http://git.postgresql.org/pg/commitdiff/a7cd853b75d01666135ca87353cee83b99d06b9b

- Further hacking on ruleutils' new column-alias-assignment code.
After further thought about implicit coercions appearing in a
joinaliasvars list, I realized that they represent an additional
reason why we might need to reference the join output column
directly instead of referencing an underlying column. Consider
SELECT x FROM t1 LEFT JOIN t2 USING (x) where t1.x is of type date
while t2.x is of type timestamptz. The merged output variable is of
type timestamptz, but it won't go to null when t2 does, therefore
neither t1.x nor t2.x is a valid substitute reference. The code in
get_variable() actually gets this case right, since it knows it
shouldn't look through a coercion, but we failed to ensure that the
unqualified output column name would be globally unique. To fix,
modify the code that trawls for a dangerous situation so that it
actually scans through an unnamed join's joinaliasvars list to see
if there are any non-simple-Var entries.
http://git.postgresql.org/pg/commitdiff/ef655663c5069231e054c3514c3ee9b15b8a4f13

- Move strip_implicit_coercions() from optimizer to nodeFuncs.c. Use
of this function has spread into the parser and rewriter, so it
seems like time to pull it out of the optimizer and put it into the
more central nodeFuncs module. This eliminates the need to #include
optimizer/clauses.h in most of the calling files, demonstrating that
this function was indeed a bit outside the normal code reference
patterns.
http://git.postgresql.org/pg/commitdiff/10a509d82956dee14eb2011bd266cd3c728ae188

- Fix booltestsel() for case where we have NULL stats but not MCV
stats. In a boolean column that contains mostly nulls, ANALYZE
might not find enough non-null values to populate the
most-common-values stats, but it would still create a pg_statistic
entry with stanullfrac set. The logic in booltestsel() for this
situation did the wrong thing for "col IS NOT TRUE" and "col IS NOT
FALSE" tests, forgetting that null values would satisfy these tests
(so that the true selectivity would be close to one, not close to
zero). Per bug #8274. Fix by Andrew Gierth, some comment-smithing
by me.
http://git.postgresql.org/pg/commitdiff/b32a25c3d5292c800c0468097b9a63e931a0af0f

- Improve ilist.h's support for deletion of slist elements during
iteration. Previously one had to use slist_delete(), implying an
additional scan of the list, making this infrastructure considerably
less efficient than traditional Lists when deletion of element(s) in
a long list is needed. Modify the slist_foreach_modify() macro to
support deleting the current element in O(1) time, by keeping a
"prev" pointer in addition to "cur" and "next". Although this makes
iteration with this macro a bit slower, no real harm is done, since
in any scenario where you're not going to delete the current list
element you might as well just use slist_foreach instead. Improve
the comments about when to use each macro. Back-patch to 9.3 so
that we'll have consistent semantics in all branches that provide
ilist.h. Note this is an ABI break for callers of
slist_foreach_modify(). Andres Freund and Tom Lane
http://git.postgresql.org/pg/commitdiff/fa2fad3c06bfde03594ff38d53acdf9a60c56bb2

- Fix configure probe for sys/ucred.h. The configure script's test
for <sys/ucred.h> did not work on OpenBSD, because on that platform
<sys/param.h> has to be included first. As a result, socket peer
authentication was disabled on that platform. Problem introduced in
commit be4585b1c27ac5dbdd0d61740d18f7ad9a00e268. Andres Freund,
slightly simplified by me.
http://git.postgresql.org/pg/commitdiff/1b09630fce1dfd4116eaaf154766a8a435168193

- Prevent leakage of SPI tuple tables during subtransaction abort.
plpgsql often just remembers SPI-result tuple tables in local
variables, and has no mechanism for freeing them if an
ereport(ERROR) causes an escape out of the execution function whose
local variable it is. In the original coding, that wasn't a problem
because the tuple table would be cleaned up when the function's SPI
context went away during transaction abort. However, once plpgsql
grew the ability to trap exceptions, repeated trapping of errors
within a function could result in significant intra-function-call
memory leakage, as illustrated in bug #8279 from Chad Wagner. We
could fix this locally in plpgsql with a bunch of PG_TRY/PG_CATCH
coding, but that would be tedious, probably slow, and prone to bugs
of omission; moreover it would do nothing for similar risks
elsewhere. What seems like a better plan is to make SPI itself
responsible for freeing tuple tables at subtransaction abort. This
patch attacks the problem that way, keeping a list of live tuple
tables within each SPI function context. Currently, such freeing is
automatic for tuple tables made within the failed subtransaction.
We might later add a SPI call to mark a tuple table as not to be
freed this way, allowing callers to opt out; but until someone
exhibits a clear use-case for such behavior, it doesn't seem worth
bothering. A very useful side-effect of this change is that
SPI_freetuptable() can now defend itself against bad calls, such as
duplicate free requests; this should make things more robust in many
places. (In particular, this reduces the risks involved if a
third-party extension contains now-redundant SPI_freetuptable()
calls in error cleanup code.) Even though the leakage problem is of
long standing, it seems imprudent to back-patch this into stable
branches, since it does represent an API semantics change for SPI
users. We'll patch this in 9.3, but live with the leakage in older
branches.
http://git.postgresql.org/pg/commitdiff/3d13623d75d3206c8f009353415043a191ebab39

Bruce Momjian pushed:

- pg_upgrade: fix parallel/-j crash on Windows. This fixes the
problem of passing the wrong function pointer when doing parallel
copy/link operations on Windows. Backpatched to 9.3beta. Found and
patch supplied by Andrew Dunstan
http://git.postgresql.org/pg/commitdiff/d7de6a4790a8e2d61e985b7273557ee8fb7ffbc0

- pg_upgrade: more Windows parallel/-j fixes More fixes to handle
Windows thread parameter passing. Backpatch to 9.3 beta. Patch
originally from Andrew Dunstan
http://git.postgresql.org/pg/commitdiff/910d3a458c15c1b4cc518ba480be2f712f42f179

- pg_upgrade: fix initialization of thread argument. Reorder
initialization of thread argument marker to it happens before
reap_child() is called. Backpatch to 9.3.
http://git.postgresql.org/pg/commitdiff/e4c6cccd8cbb96e0f64d81bde2136041492d4312

- pg_upgrade: adjust umask() calls. Since pg_upgrade -j on Windows
uses threads, calling umask() before/after opening a file via
fopen_priv() is no longer possible, so set umask() as we enter the
thread-creating loop, and reset it on exit. Also adjust internal
fopen_priv() calls to just use fopen(). Backpatch to 9.3beta.
http://git.postgresql.org/pg/commitdiff/76a7650c40efeeb35be7a60f27ce151ad4e473a2

- pg_upgrade docs: don't use cluster for binary/lib. In a few cases,
pg_upgrade said old/new cluster location when it meant old/new
Postgres install location, so fix those. Per private email report
http://git.postgresql.org/pg/commitdiff/5691de6c95836e0cef3a2fbb3ce61ce80ddecc69

- pg_upgrade: fix -j race condition on Windows. Pg_Upgrade cannot
write the command string to the log file and then call system() to
write to the same file without causing occasional file-share errors
on Windows. So instead, write the command string to the log file
after system(), in those cases. Backpatch to 9.3.
http://git.postgresql.org/pg/commitdiff/815fcd050fbe18976c51af59116d60a6be5f3e41

Stephen Frost pushed:

- Improvements to GetErrorContextStack(). As GetErrorContextStack()
borrowed setup and tear-down code from other places, it was less
than clear that it must only be called as a top-level entry point
into the error system and can't be called by an exception handler
(unlike the rest of the error system, which is set up to be
reentrant-safe). Being called from an exception handler is outside
the charter of GetErrorContextStack(), so add a bit more protection
against it, improve the comments addressing why we have to set up an
errordata stack for this function at all, and add a few more
regression tests. Lack of clarity pointed out by Tom Lane; all bugs
are mine.
http://git.postgresql.org/pg/commitdiff/9bd0feeba85fae411e01798d5a5d76b70333e38e

- Add GET DIAGNOSTICS ... PG_CONTEXT in PL/PgSQL. This adds the
ability to get the call stack as a string from within a PL/PgSQL
function, which can be handy for logging to a table, or to include
in a useful message to an end-user. Pavel Stehule, reviewed by
Rushabh Lathia and rather heavily whacked around by Stephen Frost.
http://git.postgresql.org/pg/commitdiff/831283256796d1c20858862b568d73e505eb4a84

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Greg Smith sent in another revision of a patch to add --throttle to
pgbench.

Karol Trzcionka sent in another revision of a patch to implement
UPDATE ... RETURNING BEFORE/AFTER.

Satoshi Nagayasu sent in a PoC patch to add a newpgstattuple function
to allow block sampling.

Alvaro Herrera sent in a patch to improve the error messages for
row-level locks.

David Fetter sent in two more revisions of a patch to add ORDINALITY
for set-returning functions.

Amit Kapila sent in another revision of a patch to implement ALTER
SYSTEM.

Antonin Houska sent in a patch to allow throttling pg_basebackup.

Vik Fearing sent in a patch to fix a bug where the reported number of
rows overflows the 32-bit integer assigned to it by making the integer
64-bit instead.

Robert Haas sent in another revision of a patch to add dynamic
background workers.

Fabrízio de Royes Mello and Abhijit Menon-Sen traded patches to add IF
NOT EXISTS to extant CREATE DDLs where it made sense and hadn't yet
been implemented.

Ronan Dunklau sent in a patch to add "make coverage" targets for the
additional supplied modules.

Robert Haas sent in another revision of a patch to remove SnapshotNow.

Andrew Dunstan sent in a patch to install libpq.dll in the bin
directory on Windows when compiling under Cygwin.

Stephen Frost sent in a patch to