== PostgreSQL Weekly News - March 24 2013 ==

== PostgreSQL Weekly News - March 24 2013 ==

The CfPs for Char(13) and PGday UK, July 11 and 12, 2013,
respectively, are out and close April 19, 2013. For Char(13), write
speakers AT char13 DOT info; for PGday UK, speakers AT
postgresqlusergroup DOT org DOT uk.

== PostgreSQL Product News ==

MJSQLView Version 3.44, a Java-based UI which supports PostgreSQL, released.
http://dandymadeproductions.com/projects/MyJSQLView/

== PostgreSQL Jobs for March ==

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

== PostgreSQL Local ==

PostgreSQL Session will be held on March 28th, 2013 in Paris,
France. The Call for Papers is open.
http://www.postgresql-sessions.org/en/5/

PGCon 2013 will be held May 23-24 2013, in Ottawa at the University of
Ottawa.
http://www.pgcon.org/2013/

The 6th annual "Prague PostgreSQL Developers Day" conference,
organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be
held on May 30, 2013 at Faculty of Mathematics and Physics, Charles
University (Malostranske namesti 25, Prague). The CfP is open until
April 14, 2013 <info AT p2d2 DOT cz>. More information in Czech is at
http://www.p2d2.cz/

PG Day France is the major French-speaking PostgreSQL community event.
It will be held June 13, 2013 in Nantes, France.
http://pgday.fr/

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

Save The Date!
Postgres Open 2013 will be in Chicago, IL, USA, September 16-18.
Hotel Sax:
https://reservations.ihotelier.com/crs/g_reservation.cfm?groupID=888761&hotelID=6865
Early Bird registration:
http://postgresopen-eac2.eventbrite.com/

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

Simon Riggs pushed:

- Remove PageSetTLI and rename pd_tli to pd_checksum. Remove use of
PageSetTLI() from all page manipulation functions and adjust README
to indicate change in the way we make changes to pages. Repurpose
those bytes into the pd_checksum field and explain how that works in
comments about page header. Refactoring ahead of actual feature
patch which would make use of the checksum field, arriving later.
Jeff Davis, with comments and doc changes by Simon Riggs Direction
suggested by Robert Haas; many others providing review comments.
http://git.postgresql.org/pg/commitdiff/bb7cc2623f242ffafae404f8ebbb331b9a7f2b68

- Add pageinspect--1.1.sql for checksum changes
http://git.postgresql.org/pg/commitdiff/ef04cb745fc3167264f369a2710f237de4db0bf4

- Add pageinspect--1.0--1.sql for checksum changes
http://git.postgresql.org/pg/commitdiff/e016b72411caa7027f9c980fb2c0560cdab2a129

- Add reliability docs about storage/memory corruptions. Add section
to the Reliability section about what is and is not protected for
various file types. Further edits welcome. Designed to allow 1-2
line change when/if checksums are committed. Inspired by docs
written by Jeff Davis, though completely different from his patch.
http://git.postgresql.org/pg/commitdiff/2266db392cb825eccb39518e7b652e7f336fff6c

- Correction that 2pc state files use CRC-32. Jeff Davis
http://git.postgresql.org/pg/commitdiff/8c3b87ca10a54daf06e41986d763e12538c06ab9

- Clarify assumption of filesystem metadata integrity. Jeff Davis
http://git.postgresql.org/pg/commitdiff/1a091002cfa2180a908b36d56c58749a175b4be7

- Change commit_delay to be SUSET for 9.3+. Prior to 9.3 the
commit_delay affected only the current user, whereas now only the
group leader waits while holding the WALWriteLock. Deliberate or
accidental settings to a poor value could seriously degrade
performance for all users. Privileges may be delegated by SECURITY
DEFINER functions for anyone that needs per-user settings in real
situations. Request for change from Peter Geoghegan
http://git.postgresql.org/pg/commitdiff/13fe298ca06f5390df5edf073cf401f9f0b67458

- Allow I/O reliability checks using 16-bit checksums. Checksums are
set immediately prior to flush out of shared buffers and checked
when pages are read in again. Hint bit setting will require full
page write when block is dirtied, which causes various
infrastructure changes. Extensive comments, docs and README.
WARNING message thrown if checksum fails on non-all zeroes page;
ERROR thrown but can be disabled with ignore_checksum_failure = on.
Feature enabled by an initdb option, since transition from option
off to option on is long and complex and has not yet been
implemented. Default is not to use checksums. Checksum used is WAL
CRC-32 truncated to 16-bits. Simon Riggs, Jeff Davis, Greg Smith
Wide input and assistance from many community members. Thank you.
http://git.postgresql.org/pg/commitdiff/96ef3b8ff1cf1950e897fd2f766d4bd9ef0d5d56

- Add new README file for pages/checksums
http://git.postgresql.org/pg/commitdiff/9df56f6d91c3d67b23925b8f8b70365636995b71

Tom Lane pushed:

- Improve documentation of EXTRACT(WEEK). The docs showed that
early-January dates can be considered part of the previous year for
week-counting purposes, but failed to say explicitly that
late-December dates can also be considered part of the next year.
Fix that, and add a cross-reference to the "isoyear" field. Per bug
#7967 from Pawel Kobylak.
http://git.postgresql.org/pg/commitdiff/e39feb1006ac5c64fd804597088bc7f40ff7b635

- Bump up timeout delays some more in timeouts isolation test. The
buildfarm members using -DCLOBBER_CACHE_ALWAYS still don't like this
test. Some experimentation shows that on my machine,
isolationtester's query to check for "waiting" state takes 2 to 2.5
seconds to bind+execute under -DCLOBBER_CACHE_ALWAYS. Set the
timeouts to 5 seconds to leave some headroom for possibly-slower
buildfarm critters. Really we ought to fix the "waiting" query,
which is not only horridly slow but outright wrong in detail; and
then maybe we can back off these timeouts. But right now I'm just
trying to get the buildfarm green again.
http://git.postgresql.org/pg/commitdiff/a7921f71a3c747141344d8604f6a6d7b4cddb2a9

- Redo postgres_fdw's planner code so it can handle parameterized
paths. I wasn't going to ship this without having at least some
example of how to do that. This version isn't terribly bright; in
particular it won't consider any combinations of multiple join
clauses. Given the cost of executing a remote EXPLAIN, I'm not sure
we want to be very aggressive about doing that, anyway. In support
of this, refactor generate_implied_equalities_for_indexcol so that
it can be used to extract equivalence clauses that aren't
necessarily tied to an index.
http://git.postgresql.org/pg/commitdiff/9cbc4b80ddc10b36c64514104caa69747c386dcf

- Avoid retrieving dummy NULL columns in postgres_fdw. This should
provide some marginal overall savings, since it surely takes many
more cycles for the remote server to deal with the NULL columns than
it takes for postgres_fdw not to emit them. But really the reason
is to keep the emitted queries from looking quite so silly ...
http://git.postgresql.org/pg/commitdiff/e690b9515072fd7767fdeca5c54166f6a77733bc

- Update commit_delay documentation. Commit
13fe298ca06f5390df5edf073cf401f9f0b67458 changed this GUC to be
PGC_SUSET, but neglected to update the documentation to match.
While at it, edit and rearrange the text a little for clarity.
http://git.postgresql.org/pg/commitdiff/82b945c0979350c87ddc52adefe9f0a36dd5b4c8

- Suppress uninitialized-variable warning in new checksum code. Some
compilers understand that this coding is safe, and some don't.
http://git.postgresql.org/pg/commitdiff/4912385b56afe68ef76e47d38df1d61ada0fde2f

- Fix contrib/dblink to handle inconsistent DateStyle/IntervalStyle
safely. If the remote database's settings of these GUCs are
different from ours, ambiguous datetime values may be read
incorrectly. To fix, temporarily adopt the remote server's settings
while we ingest a query result. This is not a complete fix, since
it doesn't do anything about ambiguous values in commands sent to
the remote server; but there seems little we can do about that end
of it given dblink's entirely textual API for transmitted commands.
Back-patch to 9.2. The hazard exists in all versions, but this
patch would need more work to apply before 9.2. Given the lack of
field complaints about this issue, it doesn't seem worth the effort
at present. Daniel Farina and Tom Lane
http://git.postgresql.org/pg/commitdiff/8a3b6772aedbd95557ab1fc489ddf007ac9d405d

- Document cross-version compatibility issues for
contrib/postgres_fdw. One of the use-cases for postgres_fdw is
extracting data from older PG servers, so cross-version
compatibility is important. Document what we can do here, and
further annotate some of the coding choices that create
compatibility constraints. In passing, remove one unnecessary
incompatibility with old servers, namely assuming that we didn't
need to quote the timezone name 'UTC'.
http://git.postgresql.org/pg/commitdiff/5b86fedfb57ea943f883a13c6d50c5a9e2a1cc57

- Don't put <indexterm> before <term> in <varlistentry> items. Doing
that results in a broken index entry in PDF output. We had only a
few like that, which is probably why nobody noticed before.
Standardize on putting the <term> first. Josh Kupershmidt
http://git.postgresql.org/pg/commitdiff/cdc67938c086104ef7a0e2f1e6912e9ee0ba4c85

- Update time zone abbreviation lists for changes missed since 2006.
Most (all?) of Russia has moved to what's effectively year-round
daylight savings time, so that the "standard" zone names now mean an
hour later than they used to. Update that, notably changing MSK as
per recent complaint from Sergey Konoplev, but also CHOT, GET, IRKT,
KGT, KRAT, MAGT, NOVT, OMST, VLAT, YAKT, YEKT. The corresponding
DST abbreviations are presumably now obsolete, but I left them in
place with their old definitions, just to reduce any possible
breakage from this change. Also add VOLT (Europe/Volgograd), which
for some reason we never had before, as well as MIST
(Antarctica/Macquarie), and fix obsolete definitions of MAWT, TKT,
and WST.
http://git.postgresql.org/pg/commitdiff/3b91fe185a71c05ac4528f93a39ba27232acc9e0

- Semi-automatically detect changes in timezone abbreviations. Add an
option to zic.c to dump out all non-obsolete timezone abbreviations
defined in the Olson database. Comparing this list to its previous
state will clue us in when something happens that we may need to
account for in the tznames/ time zone abbreviation lists. The
README file's previous exhortation to "just grep for differences"
was completely useless advice, in my now-considerable experience;
but maybe this will be a bit more useful. As a starting point I
built the same list from the tzdata files as they existed in 2006,
which is committed here as known_abbrevs.txt. Comparison indeed
turned up quite a few changes we had neglected to account for, which
I will commit separately.
http://git.postgresql.org/pg/commitdiff/69602772700e62b7b03e3f0ac7b10aa651c0c998

- Fix some unportable constructs in parallel pg_dump code. Didn't
compile on semi-obsolete gcc, and probably not on not-gcc-at-all
either.
http://git.postgresql.org/pg/commitdiff/846681fdd574548d4f430f2ff7ab44d77b4c79fe

Kevin Grittner pushed:

- Use ORDER BY on matview definitions were needed for stable plans.
Per report from Hadi Moshayedi of matview regression test failure
with optimization of aggregates. A few ORDER BY clauses improve
code coverage for matviews while solving that problem.
http://git.postgresql.org/pg/commitdiff/241139ae4baf6264c3c2cd053a547833c6e49a59

- Eliminate trivial whitespace inconsistency in docs sample code.
http://git.postgresql.org/pg/commitdiff/08af1a0a2ae8fbbd9949d9fa0f7583f6c09cf4ec

- Fix problems with incomplete attempt to prohibit OIDS with MVs.
Problem with assertion failure in restoring from pg_dump output
reported by Joachim Wieland. Review and suggestions by Tom Lane and
Robert Haas.
http://git.postgresql.org/pg/commitdiff/549dae0352a06a43ec664dc158556e12ec2d30e5

Alvaro Herrera pushed:

- Allow extracting machine-readable object identity Introduce
pg_identify_object(oid,oid,int4), which is similar in spirit to
pg_describe_object but instead produces a row of machine-readable
information to uniquely identify the given object, without resorting
to OIDs or other internal representation. This is intended to be
used in the event trigger implementation, to report objects being
operated on; but it has usefulness of its own. Catalog version
bumped because of the new function.
http://git.postgresql.org/pg/commitdiff/f8348ea32ec8d713cd6e5d5e16f15edef22c4d03

Heikki Linnakangas pushed:

- Fix "element <@ range" cost estimation. The statistics-based cost
estimation patch for range types broke that, by incorrectly assuming
that the left operand of all range oeprators is a range. That lead
to a "type x is not a range type" error. Because it took so long for
anyone to notice, add a regression test for that case. We still
don't do proper statistics-based cost estimation for that, so you
just get a default constant estimate. We should look into
implementing that, but this patch at least fixes the regression.
Spotted by Tom Lane, when testing query from Josh Berkus.
http://git.postgresql.org/pg/commitdiff/f897c4744fea221dfc7bbd277081edad41d1d58d

Andrew Dunstan pushed:

- Silence compiler warnings about unused values. Per gripe from Kevin
Grittner.
http://git.postgresql.org/pg/commitdiff/e4a05c7512b23c8f48c186e685f2ef186374a20a

- Avoid renaming data directory during MSVC upgrade testing. This
appears to cause some intermittent file system problems on Windows
8. Instead, set up the old data directory in its intended final
location to start with.
http://git.postgresql.org/pg/commitdiff/b7f8465cc6c95da5b1af1de19bc7b7b1b2621276

- Add parallel pg_dump option. New infrastructure is added which
creates a set number of workers (threads on Windows, forked
processes on Unix). Jobs are then handed out to these workers by the
master process as needed. pg_restore is adjusted to use this new
infrastructure in place of the old setup which created a new worker
for each step on the fly. Parallel dumps acquire a snapshot clone
in order to stay consistent, if available. The parallel option is
selected by the -j / --jobs command line parameter of pg_dump.
Joachim Wieland, lightly editorialized by Andrew Dunstan.
http://git.postgresql.org/pg/commitdiff/9e257a181cc1dc5e19eb5d770ce09cc98f470f5f

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Robins Tharakan sent in three more revisions of a patch to add
regression tests for SCHEMA-related commands.

Pavel Stehule and Hadi Moshayedi traded patches to optimize avg() and
friends on NUMERIC.

Hadi Moshayedi sent in a patch to add infrastructure which helps the
above optimization, namely an aggtransspace parameter used in creating
aggregate functions which approximates the size of the aggregate
function's internal transition (a.k.a. state) data.

Steve Singer and Bruce Momjian traded patches to fix an infelicity in
the handling of wrong PGSERVICE entries.

Nicholas White sent in four more revisions of a patch to add the
ability to ignore NULLs in windowing functions per the SQL standard.

Michael Paquier sent in three revisions of a patch to ensure that
pg_dump only dumps valid indexes.

Zoltan Boszormenyi sent in another revision of a patch to fix an
infelicity in lock_timeout on Windows.

Ants Aasma sent in three revisions of a patch to implement slice-by-8
checksums on x86_64 CPUs.

KaiGai Kohei sent in another revision of a patch to implement
OAT_POST_ALTER object access hooks.

Robins Tharakan sent in a patch to increase the regression tests'
coverage of the ROLE code.

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

Daniele Varazzo sent in some patches to fix strings for error messages
in git master.

Alvaro Herrera sent in another revision of the dropped_objects patch
vs. event triggers.

Brendan Jurd sent in a patch to disallow 0-dimensional arrays.

Andrew Dunstan sent in another revision of a patch to fix some hstore
compiler warnings.

Alvaro Herrera sent in another revision of a patch to ensure that
autovacuum sets priority on vacuums intended to prevent XID
wraparound.

Michael Paquier sent in two revisions of a patch to ensure that custom
bgworkers receive SIGHUP if the postmaster is notified.

Alexander Korotkov sent in another revision of a patch to make certain
(DFA) regexep searches indexable.

Kevin Grittner sent in a patch intended to correct certain situations
where pg_dump produced different results on subsequent runs from the
first after a reload.

Adriano Lange sent in a patch to add a new optimizer, Sampling and
Dynamic Programming (SDP), to PostgreSQL.

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

Xi Wang sent in a patch to avoid a buffer underflow in errfinish().

Michael Paquier sent in another revision of a patch to overhaul
recovery.conf.