== PostgreSQL Weekly News - December 16 2012 ==

== PostgreSQL Weekly News - December 16 2012 ==

There will be a PgDay at PyCon on March 13th, 2012 in the Santa Clara
convention center. More details once we have things organized.
Please put it on your calendar.

== PostgreSQL Product News ==

Barman 1.1.2, a backup and recovery manager for PostgreSQL, released.
http://www.pgbarman.org

psycopg2 2.4.6, a Python connector for PostgreSQL, released.
http://initd.org/psycopg/articles/2012/12/12/psycopg-246-released/

== PostgreSQL Jobs for December ==

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

== PostgreSQL Local ==

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 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. The Call for Papers is open.
http://www.pgcon.org/2013/

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

- Fix the tracking of min recovery point timeline. Forgot to update
it at the right place. Also, consider checkpoint record that
switches to new timelne to be on the new timeline. This fixes
erroneous "requested timeline 2 does not contain minimum recovery
point" errors, pointed out by Amit Kapila while testing another
patch.
http://git.postgresql.org/pg/commitdiff/6be799664aa89a473c15af7a015f4c2b2794da2e

- Update minimum recovery point on truncation. If a file is
truncated, we must update minRecoveryPoint. Once a file is
truncated, there's no going back; it would not be safe to stop
recovery at a point earlier than that anymore. Per report from
Kyotaro HORIGUCHI. Backpatch to 8.4. Before that, minRecoveryPoint
was not updated during recovery at all.
http://git.postgresql.org/pg/commitdiff/7bffc9b7bf9e09ddeddc65117e49829f758e500d

- Consistency check should compare last record replayed, not last
record read. EndRecPtr is the last record that we've read, but not
necessarily yet replayed. CheckRecoveryConsistency should compare
minRecoveryPoint with the last replayed record instead. This caused
recovery to think it's reached consistency too early. Now that we
do the check in CheckRecoveryConsistency correctly, we have to move
the call of that function to after redoing a record. The current
place, after reading a record but before replaying it, is wrong. In
particular, if there are no more records after the one ending at
minRecoveryPoint, we don't enter hot standby until one extra record
is generated and read by the standby, and CheckRecoveryConsistency
is called. These two bugs conspired to make the code appear to work
correctly, except for the small window between reading the last
record that reaches minRecoveryPoint, and replaying it. In the
passing, rename recoveryLastRecPtr, which is the last record
replayed, to lastReplayedEndRecPtr. This makes it slightly less
confusing with replayEndRecPtr, which is the last record read that
we're about to replay. Original report from Kyotaro HORIGUCHI,
further diagnosis by Fujii Masao. Backpatch to 9.0, where Hot
Standby subtly changed the test from "minRecoveryPoint < EndRecPtr"
to "minRecoveryPoint <= EndRecPtr". The former works because where
the test is performed, we have always read one more record than
we've replayed.
http://git.postgresql.org/pg/commitdiff/970fb12de121941939e777764d6e0446c974bba3

- In multi-insert, don't go into infinite loop on a huge tuple and
fillfactor. If a tuple is larger than page size minus space
reserved for fillfactor, heap_multi_insert would never find a page
that it fits in and repeatedly ask for a new page from
RelationGetBufferForTuple. If a tuple is too large to fit on any
page, taking fillfactor into account, RelationGetBufferForTuple will
always expand the relation. In a normal insert, heap_insert will
accept that and put the tuple on the new page. heap_multi_insert,
however, does a fillfactor check of its own, and doesn't accept the
newly-extended page RelationGetBufferForTuple returns, even though
there is no other choice to make the tuple fit. Fix that by making
the logic in heap_multi_insert more like the heap_insert logic. The
first tuple is always put on the page RelationGetBufferForTuple
gives us, and the fillfactor check is only applied to the subsequent
tuples. Report from David Gould, although I didn't use his patch.
http://git.postgresql.org/pg/commitdiff/6264cd3d69b519b6e6f2470e3c23ad1ef1ddff66

- Make xlog_internal.h includable in frontend context. This makes
unnecessary the ugly hack used to #include postgres.h in
pg_basebackup. Based on Alvaro Herrera's patch
http://git.postgresql.org/pg/commitdiff/527668717a660e67c2a6cfd4e85f7a513f99f6f2

- Allow a streaming replication standby to follow a timeline switch.
Before this patch, streaming replication would refuse to start
replicating if the timeline in the primary doesn't exactly match the
standby. The situation where it doesn't match is when you have a
master, and two standbys, and you promote one of the standbys to
become new master. Promoting bumps up the timeline ID, and after
that bump, the other standby would refuse to continue. There's
significantly more timeline related logic in streaming replication
now. First of all, when a standby connects to primary, it will ask
the primary for any timeline history files that are missing from the
standby. The missing files are sent using a new replication command
TIMELINE_HISTORY, and stored in standby's pg_xlog directory. Using
the timeline history files, the standby can follow the latest
timeline present in the primary (recovery_target_timeline='latest'),
just as it can follow new timelines appearing in an archive
directory. START_REPLICATION now takes a TIMELINE parameter, to
specify exactly which timeline to stream WAL from. This allows the
standby to request the primary to send over WAL that precedes the
promotion. The replication protocol is changed slightly (in a
backwards-compatible way although there's little hope of streaming
replication working across major versions anyway), to allow
replication to stop when the end of timeline reached, putting the
walsender back into accepting a replication command. Many thanks to
Amit Kapila for testing and reviewing various versions of this
patch.
http://git.postgresql.org/pg/commitdiff/abfd192b1b5ba5216ac4b1f31dcd553106304b19

Peter Eisentraut pushed:

- doc: Remove blastwave.org link. Apparently, this service has been
dead since 2008.
http://git.postgresql.org/pg/commitdiff/8e48d77cab8483fb381db9051225b46a0e4c2bc2

- doc: Improve search_path mentions in index. Karl O. Pinc
http://git.postgresql.org/pg/commitdiff/a301eb99c9537186f7dd46ba418e84d755227a94

- NLS: Use msgmerge --previous option. It provides some additional
help to translators.
http://git.postgresql.org/pg/commitdiff/fdb67eb2b69fe035339c7461c6323dc69010db18

- doc: Add pg_stat_reset and related functions to index
http://git.postgresql.org/pg/commitdiff/79a457dca7a8b35709a0faad723ddffa25caed57

- doc: Add oid columns to system catalog documentation. Karl O. Pinc
and Jeff Davis
http://git.postgresql.org/pg/commitdiff/160701f6a935d5b0440751c3cb3f70bb59cf5eb1

- doc: Remove extra table column. Not all system catalog description
tables have the same number of columns, and the patch to add oid
columns did one bit too much copy-and-pasting.
http://git.postgresql.org/pg/commitdiff/c2e32d5a6c7c01a1b8e69ca39465a31d2ab0c6e9

Bruce Momjian pushed:

- Fix pg_upgrade -O/-o options. Fix previous commit that added
synchronous_commit=off, but broke -O/-o due to missing space in
argument passing. Backpatch to 9.2.
http://git.postgresql.org/pg/commitdiff/acdb8c225955bbf582bb050e6217f79493f031eb

- Fix pg_upgrade for invalid indexes All versions of pg_upgrade
upgraded invalid indexes caused by CREATE INDEX CONCURRENTLY
failures and marked them as valid. The patch adds a check to all
pg_upgrade versions and throws an error during upgrade or --check.
Backpatch to 9.2, 9.1, 9.0. Patch slightly adjusted.
http://git.postgresql.org/pg/commitdiff/e95c4bd1133acf7fc58a52212253129ef2dc9d12

Andrew Dunstan pushed:

- Add mode where contrib installcheck runs each module in a separately
named database. Normally each module is tested in a database named
contrib_regression, which is dropped and recreated at the beginhning
of each pg_regress run. This new mode, enabled by adding
USE_MODULE_DB=1 to the make command line, runs most modules in a
database with the module name embedded in it. This will make
testing pg_upgrade on clusters with the contrib modules a lot
easier. Second attempt at this, this time accomodating make
versions older than 3.82. Still to be done: adapt to the MSVC build
system. Backpatch to 9.0, which is the earliest version it is
reasonably possible to test upgrading from.
http://git.postgresql.org/pg/commitdiff/ad69bd052f8ac1edfd579ed0e32da1c33a775f78

- Provide Assert() for frontend code. Per discussion on-hackers. psql
is converted to use the new code. Follows a suggestion from Heikki
Linnakangas.
http://git.postgresql.org/pg/commitdiff/1c382655ad90b7cd224230452f7056040337facf

- Tidy up from frontend Assert change. Quiet compiler warnings noted
by Peter Eisentraut.
http://git.postgresql.org/pg/commitdiff/3717f0837b79bc37947896d9068ccff0825e916d

Kevin Grittner pushed:

- Fix performance problems with autovacuum truncation in busy
workloads. In situations where there are over 8MB of empty pages at
the end of a table, the truncation work for trailing empty pages
takes longer than deadlock_timeout, and there is frequent access to
the table by processes other than autovacuum, there was a problem
with the autovacuum worker process being canceled by the deadlock
checking code. The truncation work done by autovacuum up that point
was lost, and the attempt tried again by a later autovacuum worker.
The attempts could continue indefinitely without making progress,
consuming resources and blocking other processes for up to
deadlock_timeout each time. This patch has the autovacuum worker
checking whether it is blocking any other thread at 20ms intervals.
If such a condition develops, the autovacuum worker will persist the
work it has done so far, release its lock on the table, and sleep in
50ms intervals for up to 5 seconds, hoping to be able to re-acquire
the lock and try again. If it is unable to get the lock in that
time, it moves on and a worker will try to continue later from the
point this one left off. While this patch doesn't change the rules
about when and what to truncate, it does cause the truncation to
occur sooner, with less blocking, and with the consumption of fewer
resources when there is contention for the table's lock. The only
user-visible change other than improved performance is that the
table size during truncation may change incrementally instead of
just once. This problem exists in all supported versions but is
infrequently reported, although some reports of performance problems
when autovacuum runs might be caused by this. Initial commit is just
the master branch, but this should probably be backpatched once the
build farm and general developer usage confirm that there are no
surprising effects. Jan Wieck
http://git.postgresql.org/pg/commitdiff/b19e4250b45e91c9cbdd18d35ea6391ab5961c8d

Tom Lane pushed:

- Disable event triggers in standalone mode. Per discussion, this
seems necessary to allow recovery from broken event triggers, or
broken indexes on pg_event_trigger. Dimitri Fontaine
http://git.postgresql.org/pg/commitdiff/cd3413ec3683918c9cb9cfb39ae5b2c32f231e8b

- Add defenses against integer overflow in dynahash numbuckets
calculations. The dynahash code requires the number of buckets in a
hash table to fit in an int; but since we calculate the desired hash
table size dynamically, there are various scenarios where we might
calculate too large a value. The resulting overflow can lead to
infinite loops, division-by-zero crashes, etc. I (tgl) had
previously installed some defenses against that in commit
299d1716525c659f0e02840e31fbe4dea3, but that covered only one call
path. Moreover it worked by limiting the request size to work_mem,
but in a 64-bit machine it's possible to set work_mem high enough
that the problem appears anyway. So let's fix the problem at the
root by installing limits in the dynahash.c functions themselves.
Trouble report and patch by Jeff Davis.
http://git.postgresql.org/pg/commitdiff/691c5ebf79bb011648fad0e6b234b94a28177e3c

- Fix filling of postmaster.pid in bootstrap/standalone mode. We
failed to ever fill the sixth line (LISTEN_ADDR), which caused the
attempt to fill the seventh line (SHMEM_KEY) to fail, so that the
shared memory key never got added to the file in standalone mode.
This has been broken since we added more content to our lock files
in 9.1. To fix, tweak the logic in CreateLockFile to add an empty
LISTEN_ADDR line in standalone mode. This is a tad grotty, but
since that function already knows almost everything there is to know
about the contents of lock files, it doesn't seem that it's any
better to hack it elsewhere. It's not clear how significant this
bug really is, since a standalone backend should never have any
children and thus it seems not critical to be able to check the
nattch count of the shmem segment externally. But I'm going to
back-patch the fix anyway. This problem had escaped notice because
of an ancient (and in hindsight pretty dubious) decision to suppress
LOG-level messages by default in standalone mode; so that the
elog(LOG) complaint in AddToDataDirLockFile that should have warned
of the problem didn't do anything. Fixing that is material for a
separate patch though.
http://git.postgresql.org/pg/commitdiff/c299477229559d4ee7db68720d86d3fb391db761

Robert Haas pushed:

- Update comment in heapgetpage() regarding PD_ALL_VISIBLE vs. Hot
Standby. Pavan Deolasee, slightly modified by me
http://git.postgresql.org/pg/commitdiff/75758a6ff01156e163779f5f7386fa36720fb38f

Magnus Hagander pushed:

- Properly copy fmgroids.h after clean on Win32. Craig Ringer
http://git.postgresql.org/pg/commitdiff/c1f856a17fc52a15cd6169146b66664291b0ca5f

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Peter Geoghegan sent in another revision of a patch to enhance error
fields.

Noah Misch sent in a patch fixing some comments in
src/backend/utils/cache/relcache.c

Amit Kapila sent in another revision of a patch to allow changing
values in postgresql.conf via SQL.

Karl O. Pinc sent in another revision of a patch to elucidate the
nature of the permission system.

Dimitri Fontaine sent in two more revisions of a patch to add
information to event triggers.

Laurenz Albe and KaiGai Kohei traded patch sets to enable writes to
foreign tables.

Josh Kupershmidt and Karl O. Pinc traded patches to allow multiple
--tables options for utilities other than pg_dump, where the feature
originated.

Andres Freund and Heikki Linnakangas traded patches to add an
XLogReader generic WAL-reading facility.

Mikko Tiihonen sent in a patch to use gcc built-in atomic inc/dec in
lock.c where available.

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

Alexander Korotkov sent in another revision of a patch to improve
statistics and selectivity estimation for ranges.

Alexander Korotkov sent in another revision of a patch to implement
SP-GiST for ranges based on 2d-mapping and quad-tree.

Alexander Korotkov and Jeff Davis traded patches to de-bloat
gistchoose.

Andres Freund sent in a patch to add GetOldestXminNoLock as a variant
(and implementation) of GetOldestXmin, along with a patch to do wal
decoding by using GetOldestXminNoLock to compute the initial logical
xmin safely.

Pavan Deolasee sent in another revision of a patch to set a visibility
map bit after a HOT prune operation.

Alexander Korotkov sent in another revision of a patch to help support
DFA regex searches with indexes.

Tomas Vondra sent in another revision of a patch to optimizing
dropping multiple tables within a transaction.

Tomas Vondra sent in another revision of a patch to allow trimming
down the amount of log output pgbench generates.

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