== PostgreSQL Weekly News - August 25 2013 ==
The Hyderabad, India PostgreSQL Users' Group has started meeting.
The PostgreSQL Conference China for 2013 will be held October 26-27,
2013 in Hangzhou. Chinese language information here:
Release Candidate 1 of the next version of PostgreSQL is now
Your invaluable bug reports go to or here:
== PostgreSQL Product News ==
MicroOLAP Database Designer for PostgreSQL 1.9.1 released.
Postgres-XC 1.1, a write-scalable multi-master symmetric cluster
based on PostgreSQL, released.
== PostgreSQL Jobs for August ==
== PostgreSQL Local ==
Postgres Open 2013 will be in Chicago, IL, USA, September 16-18.
The list of talks has been posted on the site.
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:
pgconf.EU 2013 will be held on Oct 29-Nov 1, 2013 at the Conrad Hotel
in downtown Dublin, Ireland. Registration is open.
PGConf.DE 2013 will be held November 8th, 2013, at the Rhineland
Industrial Museum in Oberhausen. The CfP is open through September
== 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 [email protected], German language
to [email protected], Italian language to [email protected] Spanish language
to [email protected]
== Applied Patches ==
Peter Eisentraut pushed:
- Translation updates
Bruce Momjian pushed:
- release notes: remove username from 9.3 major item. Etsuro Fujita
- release notes: update link to 9.3 PL/pgSQL constraint error info.
Backpatch to 9.3. Pavel Stehule
- docs: Remove second 'trim' index reference. Per suggestion from Vik
Alvaro Herrera pushed:
- Fix pg_upgrade failure from servers older than 9.3. When upgrading
from servers of versions 9.2 and older, and MultiXactIds have been
used in the old server beyond the first page (that is, 2048 multis
or more in the default 8kB-page build), pg_upgrade would set the
next multixact offset to use beyond what has been allocated in the
new cluster. This would cause a failure the first time the new
cluster needs to use this value, because the pg_multixact/offsets/
file wouldn't exist or wouldn't be large enough. To fix, ensure
that the transient server instances launched by pg_upgrade extend
the file as necessary. Per report from Jesse Denardo in
- Fix removal of files in pgstats directories. Instead of deleting
all files in stats_temp_directory and the permanent directory on a
crash, only remove those files that match the pattern of files we
actually write in them, to avoid possibly clobbering existing
unrelated contents of the temporary directory. Per complaint from
Jeff Janes, and subsequent discussion, starting at message
Also, fix a bug in the same routine to avoid removing files from the
permanent directory twice (instead of once from that directory and
then from the temporary directory), also per report from Jeff Janes,
Tom Lane pushed:
- Fix qual-clause-misplacement issues with pulled-up LATERAL
subqueries. In an example such as SELECT * FROM i LEFT JOIN LATERAL
(SELECT * FROM j WHERE i.n = j.n) j ON true; it is safe to pull up
the LATERAL subquery into its parent, but we must then treat the
"i.n = j.n" clause as a qual clause of the LEFT JOIN. The previous
coding in deconstruct_recurse mistakenly labeled the clause as
"is_pushed_down", resulting in wrong semantics if the clause were
applied at the join node, as per an example submitted awhile ago by
Jeremy Evans. To fix, postpone processing of such clauses until we
return back up to the appropriate recursion depth in
deconstruct_recurse. In addition, tighten the is-safe-to-pull-up
checks in is_simple_subquery; we previously missed the possibility
that the LATERAL subquery might itself contain an outer join that
makes lateral references in lower quals unsafe. A regression test
case equivalent to Jeremy's example was already in my commit of
yesterday, but was giving the wrong results because of this bug.
This patch fixes the expected output for that, and also adds a test
case for the second problem.
- Be more wary of unwanted whitespace in pgstat_reset_remove_files().
sscanf isn't the easiest thing to use for exact pattern checks ...
also, don't use strncmp where strcmp will do.
- Fix hash table size estimation error in choose_hashed_distinct().
We should account for the per-group hashtable entry overhead when
considering whether to use a hash aggregate to implement DISTINCT.
The comparable logic in choose_hashed_grouping() gets this right,
but I think I omitted it here in the mistaken belief that there
would be no overhead if there were no aggregate functions to be
evaluated. This can result in more than 2X underestimate of the
hash table size, if the tuples being aggregated aren't very wide.
Per report from Tomas Vondra. This bug is of long standing, but per
discussion we'll only back-patch into 9.3. Changing the estimation
behavior in stable branches seems to carry too much risk of
destabilizing plan choices for already-tuned applications.
- In locate_grouping_columns(), don't expect an exact match of Var
typmods. It's possible that inlining of SQL functions (or perhaps
other changes?) has exposed typmod information not known at parse
time. In such cases, Vars generated by query_planner might have
valid typmod values while the original grouping columns only have
typmod -1. This isn't a semantic problem since the behavior of
grouping only depends on type not typmod, but it breaks
locate_grouping_columns' use of tlist_member to locate the matching
entry in query_planner's result tlist. We can fix this without an
excessive amount of new code or complexity by relying on the fact
that locate_grouping_columns only gets called when
make_subplanTargetList has set need_tlist_eval == false, and that
can only happen if all the grouping columns are simple Vars.
Therefore we only need to search the sub_tlist for a matching Var,
and we can reasonably define a "match" as being a match of the Var
identity fields varno/varattno/varlevelsup. The code still Asserts
that vartype matches, but ignores vartypmod. Per bug #8393 from
Evan Martin. The added regression test case is basically the same
as his example. This has been broken for a very long time, so
back-patch to all supported branches.
- Account better for planning cost when choosing whether to use custom
plans. The previous coding in plancache.c essentially used 10% of
the estimated runtime as its cost estimate for planning. This can
be pretty bogus, especially when the estimated runtime is very
small, such as in a simple expression plan created by plpgsql, or a
simple INSERT ... VALUES. While we don't have a really good handle
on how planning time compares to runtime, it seems reasonable to use
an estimate based on the number of relations referenced in the
query, with a rather large multiplier. This patch uses 1000 *
cpu_operator_cost * (nrelations + 1), so that even a trivial query
will be charged 1000 * cpu_operator_cost for planning. This should
address the problem reported by Marc Cousin and others that 9.2 and
up prefer custom plans in cases where the planning time greatly
exceeds what can be saved.
Heikki Linnakangas pushed:
- Rename the "fast_promote" file to just "promote". This keeps the
usual trigger file name unchanged from 9.2, avoiding nasty issues if
you use a pre-9.3 pg_ctl binary with a 9.3 server or vice versa.
The fallback behavior of creating a full checkpoint before starting
up is now triggered by a file called "fallback_promote". That can be
useful for debugging purposes, but we don't expect any users to have
to resort to that and we might want to remove that in the future,
which is why the fallback mechanism is undocumented.
Andrew Dunstan pushed:
- Unconditionally use the WSA equivalents of Socket error constants.
This change will only apply to mingw compilers, and has been found
necessary by late versions of the mingw-w64 compiler. It's the same
as what is done elsewhere for the Microsoft compilers. If this
doesn't upset older compilers in the buildfarm, it will be
backpatched to 9.1. Problem reported by Michael Cronenworth,
although not his patch.
Magnus Hagander pushed:
- Don't crash when pg_xlog is empty and pg_basebackup -x is used. The
backup will not work (without a logarchive, and that's the whole
point of -x) in this case, this patch just changes it to throw an
error instead of crashing when this happens. Noticed and diagnosed
by TAKATSUKA Haruka
== Rejected Patches (for now) ==
No one was disappointed this week :-)
== Pending Patches ==
Laurenz Albe sent in another revision of a patch to fix some bugs in
the LDAP implementation.
Fabrízio de Royes Mello sent in another revision of a patch to add
support of "IF NOT EXISTS" to other "CREATE" statements where it makes
sense to do so.
Karol Trzcionka sent in six more revisions of a patch to extend the
RETURNING syntax in the case of UPDATE to allow both the BEFORE and
Zoltan Boszormenyi sent in another revision of a patch to add
Sawada Masahiko sent in a patch to disallow parsing "of" as a boolean
value. Parsing "off" as "false" was the code involved.
Christoph Pettus sent in a patch to note in the documentation that
ereport does not return if the error severity is greater than or equal
Alvaro Herrera sent in a flock of patches to make the process of using
a temp directory safer.
Tarvi Pillessaar sent in a patch to add detail part for the "process x
is still waiting for" lock message to show information about the lock
holder and also show what the lock holder is actually doing.
MauMau sent in a patch to clarify which Solaris versions are
Pavel Stehule sent in another revision of a patch to add a
check_function() function for PL/pgsql.
Vik Fearing sent in a patch to add a pg_system_identifier() function
for finding out which node you're talking to.
Nicholas White sent in two more revisions of a patch to add
IGNORE/RESPECT NULLS to lead() and lag() window functions.
Dean Rasheed sent in another revision of a patch to implement SQL
Feature T111, "Updatable joins, unions, and columns".
Pavel Stehule sent in a patch to match PL/pgsql's verbosity with the
one set in libpq-based applications like psql.
Blake Smith sent in a patch to speed up GIN index query performance on
hstores by prepending the values in the index with their keys.
Heikki Linnakangas sent in another revision of a patch to redesign
checkpoint segments, adding two new GUCs in the process.
Pavel Stehule sent in a patch to add a conversion facility between
the bytea and large object types.
Sent via pgsql-announce mailing list ([email protected])
To make changes to your subscription: