== PostgreSQL Weekly News - January 12 2014 ==

== PostgreSQL Weekly News - January 12 2014 ==

The schedule for FOSDEM and FOSDEM/PGDay has been published.
http://www.postgresql.eu/events/schedule/fosdem2014/

Nordic PGDay 2014 will be held in Stockholm, Sweden, at the Hilton
Stockholm Hotel, on March 20, 2014. The CfP is open until February 2,
2014.
http://2014.nordicpgday.org/

The Open Data Summit will be held Friday April 11, 2014 in Denver,
Colorado, USA.
http://www.opendatasummit.com

== PostgreSQL Product News ==

psycopg2 2.5.2, a Python connector for PostgreSQL, released.
http://initd.org/psycopg/articles/2014/01/07/psycopg-252-released/

== PostgreSQL Jobs for January ==

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

== PostgreSQL Local ==

FOSDEM PGDay, a one day conference held before FOSDEM in Brussels,
Belgium, will be on Jan 31st, 2014. Details:
http://fosdem2014.pgconf.eu/
http://fosdem2014.pgconf.eu/registration/

The 7th annual "Prague PostgreSQL Developers Day" (P2D2) conference
organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be
held on February 6, 2014 at Faculty of Mathematics and Physics,
Charles University (Malostranske namesti 25, Prague). Czech language
info below. The CfP will be open through 2014/01/03.
http://www.p2d2.cz/

The CfP for PGConf NYC 2014 is open through January 10, 2014.
Notifications will go out on January 15, 2014.
http://nyc.pgconf.us/2014/

The CfP for PGCon 2014 is open through January 19, 2014.
http://www.pgcon.org/2014/

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

- Remove bogus -K option from pg_dump. I added it to the getopt call
by accident in commit 691e595dd9c7786d37d73ccd327f8c2b6f0dace6.
Amit Kapila
http://git.postgresql.org/pg/commitdiff/10a82cda67731941c18256e009edad4a784a2994

- Silence compiler warning on MSVC. MSVC doesn't know that
elog(ERROR) doesn't return, and gives a warning about missing
return. Silence that. Amit Kapila
http://git.postgresql.org/pg/commitdiff/f68220df92cb56f0452919f51eeef16262ec8f3b

- Fix bug in determining when recovery has reached consistency. When
starting WAL replay from an online checkpoint, the last replayed WAL
record variable was initialized using the checkpoint record's
location, even though the records between the REDO location and the
checkpoint record had not been replayed yet. That was noted as
"slightly confusing" but harmless in the comment, but in some cases,
it fooled CheckRecoveryConsistency to incorrectly conclude that we
had already reached a consistent state immediately at the beginning
of WAL replay. That caused the system to accept read-only
connections in hot standby mode too early, and also PANICs with
message "WAL contains references to invalid pages". Fix by
initializing the variables to the REDO location instead. In 9.2 and
above, change CheckRecoveryConsistency() to use
lastReplayedEndRecPtr variable when checking if backup end location
has been reached. It was inconsistently using EndRecPtr for that
check, but lastReplayedEndRecPtr when checking min recovery point.
It made no difference before this patch, because in all the places
where CheckRecoveryConsistency was called the two variables were the
same, but it was always an accident waiting to happen, and would
have been wrong after this patch anyway. Report and analysis by
Tomonari Katsumata, bug #8686. Backpatch to 9.0, where hot standby
was introduced.
http://git.postgresql.org/pg/commitdiff/d59ff6c110162fc6f3f62b160ff451bfda871af0

- Fix pause_at_recovery_target + recovery_target_inclusive
combination. If pause_at_recovery_target is set, recovery pauses
*before* applying the target record, even if
recovery_target_inclusive is set. If you then continue with
pg_xlog_replay_resume(), it will apply the target record before
ending recovery. In other words, if you log in while it's paused and
verify that the database looks OK, ending recovery changes its state
again, possibly destroying data that you were tring to salvage with
PITR. Backpatch to 9.1, this has been broken since
pause_at_recovery_target was added.
http://git.postgresql.org/pg/commitdiff/3739e5ab93afb21b69da2e42f6e161ef63aa95c8

- If multiple recovery_targets are specified, use the latest one. The
docs say that only one of recovery_target_xid, recovery_target_time,
or recovery_target_name can be specified. But the code actually did
something different, so that a name overrode time, and xid overrode
both time and name. Now the target specified last takes effect,
whether it's an xid, time or name. With this patch, we still accept
multiple recovery_target settings, even though docs say that only
one can be specified. It's a general property of the recovery.conf
file parser that you if you specify the same option twice, the last
one takes effect, like with postgresql.conf.
http://git.postgresql.org/pg/commitdiff/815d71deed5df2a91b06da76edbe5bc64965bfea

- Refactor checking whether we've reached the recovery target. Makes
the replay loop slightly more readable, by separating the concerns
of whether to stop and whether to delay, and how to extract the
timestamp from a record. This has the user-visible change that the
timestamp of the last applied record is now updated after actually
applying it. Before, it was updated just before applying it. That
meant that pg_last_xact_replay_timestamp() could return the
timestamp of a commit record that is in process of being replayed,
but not yet applied. Normally the difference is small, but if
min_recovery_apply_delay is set, there could be a significant delay
between reading a record and applying it. Another behavioral change
is that if you recover to a restore point, we stop after the restore
point record, not before it. It makes no difference as far as
running queries on the server is concerned, as applying a restore
point record changes nothing, but if examine the timeline history
you will see that the new timeline branched off just after the
restore point record, not before it. One practical consequence is
that if you do PITR to the new timeline, and set recovery target to
the same named restore point again, it will find and stop recovery
at the same restore point. Conceptually, I think it makes more sense
to consider the restore point as part of the new timeline's history
than not. In principle, setting the last-replayed timestamp before
actually applying the record was a bug all along, but it doesn't
seem worth the risk to backpatch, since min_recovery_apply_delay was
only added in 9.4.
http://git.postgresql.org/pg/commitdiff/c945af80cfdaf72adb91d6688fb3a4c4f17c0757

Peter Eisentraut pushed:

- Add more use of psprintf()
http://git.postgresql.org/pg/commitdiff/edc43458d797a5956f4bf39af18cf62abb0077db

- pg_upgrade: Fix fatal error handling. Restore exiting when
pg_log(PG_FATAL) is called directly instead of calling pg_fatal().
Fault introduced in 264aa14a2f687eba8c8cc2a5b6cbd6397973da98.
http://git.postgresql.org/pg/commitdiff/ca607b155e86ce529fc9ac322a232f264cda9ab6

- pgcrypto: Make header files stand alone. pgp.h used to require
including mbuf.h and px.h first. Include those in pgp.h, so that it
can be used without prerequisites. Remove mbuf.h inclusions in .c
files where mbuf.h features are not used directly. (px.h was always
used.)
http://git.postgresql.org/pg/commitdiff/10a3b165a32e9dd116ba340dd2d2f8c77895418e

Magnus Hagander pushed:

- Avoid including tablespaces inside PGDATA twice in base backups. If
a tablespace was crated inside PGDATA it was backed up both as part
of the PGDATA backup and as the backup of the tablespace. Avoid this
by skipping any directory inside PGDATA that contains one of the
active tablespaces. Dimitri Fontaine and Magnus Hagander
http://git.postgresql.org/pg/commitdiff/b168c5ef2730d0ecaa7462f0b90345b0a3798c16

- Move permissions check from do_pg_start_backup to pg_start_backup.
And the same for do_pg_stop_backup. The code in do_pg_* is not
allowed to access the catalogs. For manual base backups, the
permissions check can be handled in the calling function, and for
streaming base backups only users with the required permissions can
get past the authentication step in the first place. Reported by
Antonin Houska, diagnosed by Andres Freund
http://git.postgresql.org/pg/commitdiff/9544cc0d657ea09d27667c8c70302b06fbe0121b

Tom Lane pushed:

- Fix LATERAL references to target table of UPDATE/DELETE. I failed
to think much about UPDATE/DELETE when implementing LATERAL :-(.
The implemented behavior ended up being that subqueries in the FROM
or USING clause (respectively) could access the update/delete target
table as though it were a lateral reference; which seems fine if
they said LATERAL, but certainly ought to draw an error if they
didn't. Fix it so you get a suitable error when you omit LATERAL.
Per report from Emre Hasegeli.
http://git.postgresql.org/pg/commitdiff/0c051c90082da0b7e5bcaf9aabcbd4f361137cdc

- Save a few cycles in advance_transition_function(). Keep a
pre-initialized FunctionCallInfoData in AggStatePerAggData, and
re-use that at each row instead of doing InitFunctionCallInfoData
each time. This saves only half a dozen assignments and maybe some
stack manipulation, and yet that seems to be good for a percent or
two of the overall query run time for simple aggregates such as
count(*). The cost is that the FunctionCallInfoData (which is about
a kilobyte, on 64-bit machines) stays allocated for the duration of
the query instead of being short-lived stack data. But we're
already paying an equivalent space cost for each regular FuncExpr or
OpExpr node, so I don't feel bad about paying it for aggregate
functions. The code seems a little cleaner this way too, since the
number of things passed to advance_transition_function decreases.
http://git.postgresql.org/pg/commitdiff/e6336b8b5772b9856d65ef967e0b9f748f0f7b0b

- Avoid extra AggCheckCallContext() checks in ordered-set aggregates.
In the transition functions, we don't really need to recheck this
after the first call. I had been feeling paranoid about possibly
getting a non-null argument value in some other context; but it's
probably game over anyway if we have a non-null "internal" value
that's not what we are expecting. In the final functions, the
general convention in pre-existing final functions seems to be that
an Assert() is good enough, so do it like that here too. This seems
to save a few tenths of a percent of overall query runtime, which
isn't much, but still it's just overhead if there's not a plausible
case where the checks would fire.
http://git.postgresql.org/pg/commitdiff/847e46abc92333a5a948d8fa886604832c1db238

- Fix "cannot accept a set" error when only some arms of a CASE return
a set. In commit c1352052ef1d4eeb2eb1d822a207ddc2d106cb13, I
implemented an optimization that assumed that a function's argument
expressions would either always return a set (ie multiple rows), or
always not. This is wrong however: we allow CASE expressions in
which some arms return a set of some type and others just return a
scalar of that type. There may be other examples as well. To fix,
replace the run-time test of whether an argument returned a set with
a static precheck (expression_returns_set). This adds a little bit
of query startup overhead, but it seems barely measurable. Per bug
#8228 from David Johnston. This has been broken since 8.0, so patch
all supported branches.
http://git.postgresql.org/pg/commitdiff/080b7db72ebbec22580237631d6b07d0e1147b01

- We don't need to include pg_sema.h in s_lock.h anymore. Minor
improvement to commit daa7527afc2274432094ebe7ceb03aa41f916607:
s_lock.h no longer has any need to mention PGSemaphoreData, so we
can rip out the #include that supplies that. In a
non-HAVE_SPINLOCKS build, this doesn't really buy much since we
still need the #include in spin.h --- but everywhere else, this
reduces #include footprint by some trifle, and helps keep the
different locking facilities separate.
http://git.postgresql.org/pg/commitdiff/220b34331f77effdb46798ddd7cca0cffc1b2858

- Remove unnecessary local variables to work around an icc
optimization bug. Buildfarm member dunlin has been crashing since
commit 8b49a60, but other machines seem fine with that code. It
turns out that removing the local variables in ordered_set_startup()
that are copies of fields in "qstate" dodges the problem. This
might cost a few cycles on register-rich machines, but it's probably
a wash on others, and in any case this code isn't
performance-critical. Thanks to Jeremy Drake for off-list
investigation.
http://git.postgresql.org/pg/commitdiff/faab7a957d31389f4abfd83784f622c91d076f49

- Add another regression test cross-checking operator and function
comments. Add a query that lists all the functions that are
operator implementation functions and have a SQL comment that
doesn't just say "implementation of XYZ operator". (Note that the
preceding test checks that such functions' comments exactly match
the corresponding operators' comments.) While it's not forbidden to
add more functions to this list, that should only be done when we're
encouraging users to use either the function or operator syntax for
the functionality, which is a fairly rare situation.
http://git.postgresql.org/pg/commitdiff/28233ffaa436852218113d34aa79b7e54f470ed7

- Fix compute_scalar_stats() for case that all values exceed
WIDTH_THRESHOLD. The standard typanalyze functions skip over values
whose detoasted size exceeds WIDTH_THRESHOLD (1024 bytes), so as to
limit memory bloat during ANALYZE. However, we (I think I, actually
:-() failed to consider the possibility that *every* non-null value
in a column is too wide. While compute_minimal_stats() seems to
behave reasonably anyway in such a case, compute_scalar_stats() just
fell through and generated no pg_statistic entry at all. That's
unnecessarily pessimistic: we can still produce valid stanullfrac
and stawidth values in such cases, since we do include too-wide
values in the average-width calculation. Furthermore, since the
general assumption in this code is that too-wide values are probably
all distinct from each other, it seems reasonable to set stadistinct
to -1 ("all distinct"). Per complaint from Kadri Raudsepp. This
has been like this since roughly neolithic times, so back-patch to
all supported branches.
http://git.postgresql.org/pg/commitdiff/6286526207d53e5b31968103adb89b4c9cd21499

- Fix possible crashes due to using elog/ereport too early in startup.
Per reports from Andres Freund and Luke Campbell, a server failure
during set_pglocale_pgservice results in a segfault rather than a
useful error message, because the infrastructure needed to use
ereport hasn't been initialized; specifically, MemoryContextInit
hasn't been called. One known cause of this is starting the server
in a directory it doesn't have permission to read. We could try to
prevent set_pglocale_pgservice from using anything that depends on
palloc or elog, but that would be messy, and the odds of future
breakage seem high. Moreover there are other things being called in
main.c that look likely to use palloc or elog too --- perhaps those
things shouldn't be there, but they are there today. The best
solution seems to be to move the call of MemoryContextInit to very
early in the backend's real main() function. I've verified that an
elog or ereport occurring immediately after that is now capable of
sending something useful to stderr. I also added code to elog.c to
print something intelligible rather than just crashing if
MemoryContextInit hasn't created the ErrorContext. This could
happen if MemoryContextInit itself fails (due to malloc failure),
and provides some future-proofing against someone trying to sneak in
new code even earlier in server startup. Back-patch to all
supported branches. Since we've only heard reports of this type of
failure recently, it may be that some recent change has made it more
likely to see a crash of this kind; but it sure looks like it's
broken all the way back.
http://git.postgresql.org/pg/commitdiff/910bac5953012198e210848660ea31f27ab08abc

- Disallow LATERAL references to the target table of an UPDATE/DELETE.
On second thought, commit 0c051c90082da0b7e5bcaf9aabcbd4f361137cdc
was over-hasty: rather than allowing this case, we ought to reject
it for now. That leaves the field clear for a future feature that
allows the target table to be re-specified in the FROM (or USING)
clause, which will enable left-joining the target table to something
else. We can then also allow LATERAL references to such an
explicitly re-specified target table. But allowing them right now
will create ambiguities or worse for such a feature, and it isn't
something we documented 9.3 as supporting. While at it, add a
convenience subroutine to avoid having several copies of the ereport
for disalllowed-LATERAL-reference cases.
http://git.postgresql.org/pg/commitdiff/158b7fa6a34006bdc70b515e14e120d3e896589b

Bruce Momjian pushed:

- Update copyright for 2014. Update all files in head, and files
COPYRIGHT and legal.sgml in all back branches.
http://git.postgresql.org/pg/commitdiff/7e04792a1cbd1763edf72474f6b1fbad2cd0ad31

- Move username lookup functions from /port to /common. Per
suggestion from Peter E and Alvaro
http://git.postgresql.org/pg/commitdiff/111022eac64579cc12d20e33146ce01717562b29

- Adjust pg_upgrade for move of username lookup functions to /common
http://git.postgresql.org/pg/commitdiff/850ade3e32ea12e0c6b2d10c6b628772bf851727

- docs: remove undocumented assign syntax in plpgsql examples. Pavel
Stehule
http://git.postgresql.org/pg/commitdiff/fd2ace802811c333b0b4e1a28b138fd4774745f3

- Fix pg_dumpall on pre-8.1 servers. rolname did not exist in
pg_shadow. Backpatch to 9.3 Report by Andrew Gierth via IRC
http://git.postgresql.org/pg/commitdiff/bb953ad164ade6ece0c8b8d02ae155980c967d8b

Robert Haas pushed:

- Reduce the number of semaphores used under --disable-spinlocks.
Instead of allocating a semaphore from the operating system for
every spinlock, allocate a fixed number of semaphores (by default,
1024) from the operating system and multiplex all the spinlocks that
get created onto them. This could self-deadlock if a process
attempted to acquire more than one spinlock at a time, but since
processes aren't supposed to execute anything other than short
stretches of straight-line code while holding a spinlock, that
shouldn't happen. One motivation for this change is that, with the
introduction of dynamic shared memory, it may be desirable to create
spinlocks that last for less than the lifetime of the server.
Without this change, attempting to use such facilities under
--disable-spinlocks would quickly exhaust any supply of available
semaphores. Quite apart from that, it's desirable to contain the
quantity of semaphores needed to run the server simply on
convenience grounds, since using too many may make it harder to get
PostgreSQL running on a new platform, which is mostly the point of
--disable-spinlocks in the first place. Patch by me; review by Tom
Lane.
http://git.postgresql.org/pg/commitdiff/daa7527afc2274432094ebe7ceb03aa41f916607

Michael Meskes pushed:

- Changed regression test to ecpg test suite for alignment problem
just with last commit.
http://git.postgresql.org/pg/commitdiff/192b4aacad45c16a8a9341644479125977366dab

- Fix descriptor output in ECPG. While working on most platforms the
old way sometimes created alignment problems. This should fix it.
Also the regresion tests were updated to test for the reported case.
Report and fix by MauMau <maumau307(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/d685e2424967509f004d9eb6d005dfb58e21d837

Alvaro Herrera pushed:

- Accept pg_upgraded tuples during multixact freezing. The new
MultiXact freezing routines introduced by commit 8e9a16ab8f7
neglected to consider tuples that came from a pg_upgrade'd database;
a vacuum run that tried to freeze such tuples would die with an
error such as ERROR: MultiXactId 11415437 does no longer exist --
apparent wraparound To fix, ensure that GetMultiXactIdMembers is
allowed to return empty multis when the infomask bits are right, as
is done in other callsites. Per trouble report from F-Secure. In
passing, fix a copy&paste bug reported by Andrey Karpov from VIVA64
from their PVS-Studio static checked, that instead of setting
relminmxid to Invalid, we were setting relfrozenxid twice. Not an
important mistake because that code branch is about relations for
which we don't use the frozenxid/minmxid values at all in the first
place, but seems to warrants a fix nonetheless.
http://git.postgresql.org/pg/commitdiff/423e1211a86df0d0dd8914223137edbfd4d52400

Andrew Dunstan pushed:

- Remove DESCR entries for json operator functions. Per -hackers
discussion.
http://git.postgresql.org/pg/commitdiff/11829ff8b2c877469845c08e5966a704b814ca5f

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Robert Haas sent in a patch to keep lwlocks in a hash table for
dynamic shared memory.

Rajeev Rastogi sent in a patch to fix a case where PostgreSQL as win32
service does not start.

Dean Rasheed sent in three more revisions of a patch to make
updateable security barrier views.

Christian Ullrich sent in a patch to prevent Windows from stopping
unnecessarily when running in console and a random control-C gets
sent.

Ronan Dunklau sent in two revisions of a patch to implement triggers
on foreign tables.

Tom Lane sent in a patch to speed up nodeAgg by avoiding redoing
InitFunctionCallInfoData for each row.

Dilip Kumar sent in a patch to implement a machine-readable
pg_controldata.

Gabriele Bartolini sent in two more revisions of a patch to implement
a pg_stat_archiver view.

Peter Geoghegan and Heikki Linnakangas traded patches to implement
INSERT ... ON DUPLICATE KEY LOCK FOR UPDATE.

Pavel Raiskup sent in another revision of a patch to make the locale
comparison more tolerant.

Andreas Karlsson sent in another revision of a patch to include
planning time in EXPLAIN [ANALYZE].

Alexander Korotkov sent in another revision of a patch to improve GIN
by having it store more information.

Peter Eisentraut sent in a patch to integrate pg_upgrade
analyze_new_cluster.sh into vacuumdb.

Oleg Bartunov sent in another revision of a patch to implement an
efficient on-disk representation of nested hstores. Andrew Dunstan
used this to add those efficiencies to JSONB. Erik Rijkers pitched in
some documentation fixes.

Oskari SaarEnmaa sent in a patch allow the default log_min_error_statement
to be overridden per SQLSTATE.

Marko (johto) Tiikkaja sent in a patch to display oprcode and its
volatility in \do+.

Etsuro Fujita sent in another revision of a patch to show lossy heap
block info in EXPLAIN ANALYZE for bitmap heap scan.

Amit Kapila sent in a patch to fixes both the display of wrong file
name and usage of PG_AUTOCONF_FILENAME in ALTER SYSTEM.

Amit Kapila sent in another revision of a patch to improve performance
of update operations by reducing the WAL they generate.

Fabrízio de Royes Mello sent in two revisions of a patch to remove the
newlines at end of generated SQL in src/bin/scripts.

Heikki Linnakangas sent in a patch to add a recovery target called
'immediate' which stops recovery at the earliest time it can do so
successfully.

Pavel Stehule sent in two revisions of a patch to allow multiple
PL/pgsql plugins.

Oskari Saarenmaa sent in a patch to implement gen_random_uuid().

Steeve Lennmark sent in three revisions of a patch to enable
relocating tablespaces in pg_basebackup.

Robert Haas sent in a patch to create a LWLock array for dynamic
shared memory.

Bruce Momjian sent in a patch to update pg_resetxlog.c in light of the
earlier update to Autoconf 2.69.

David Rowley sent in another revision of a patch to implement inverse
transition functions for aggregates.

Craig Ringer sent in a patch to fix double-inclusion of pg_config_os.h
when building extensions with Visual Studio.

Amit Kapila sent in a patch to enable retaining dynamic shared memory
segments for postmaster lifetime.

Pavel Stehule sent in another revision of a patch to implement an
efficient make_timestamp() function which only takes numeric inputs.

Marko (johto) Tiikkaja sent in a patch to make the PL/pgsql version of
SELECT ... INTO error out instead of producing unexpected results
silently when fed more than one row.