== PostgreSQL Weekly News - September 04 2011 ==
The fifth edition of the Italian PostgreSQL Day (PGDay.IT 2011) will
be held on November 25, 2011 in Prato, Italy.
== PostgreSQL Product News ==
PostgreSQL Maestro 11.8, a Windows admin tool for PostgreSQL, released.
== PostgreSQL 9.1 Feature of the Week ==
== PostgreSQL Tip of the Week ==
== PostgreSQL Jobs for September ==
== PostgreSQL Local ==
Postgres Open 2011, a conference focused on disruption of the database
industry through PostgreSQL, will take place September 14-16, 2011 in
Chicago, Illinois at the Westin Michigan Avenue hotel.
PG-Day Denver 2011 will be held on Friday, October 21st, 2011 at
the Auraria Campus near downtown Denver, Colorado.
PostgreSQL Conference West (#PgWest) will be held September 27th-30th,
2011 at the San Jose Convention center in San Jose, California, USA.
PostgreSQL Conference Europe 2011 will be held on October 18-21 in
pgbr will be in Sao Paulo, Brazil November 3-4, 2011.
PGConf.DE 2011 is the German-speaking PostgreSQL Conference and will
take place on November 11th in the Rheinisches Industriemuseum in
Oberhausen, Germany. Call for Papers is open.
== 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
== Reviews ==
== Applied Patches ==
Robert Haas pushed:
- Improve spinlock performance for HP-UX, ia64, non-gcc. At least on
this architecture, it's very important to spin on a non-atomic
instruction and only retry the atomic once it appears that it will
succeed. To fix this, split TAS() into two macros: TAS(), for
trying to grab the lock the first time, and TAS_SPIN(), for spinning
until we get it. TAS_SPIN() defaults to same as TAS(), but we can
override it when we know there's a better way. It's likely that
some of the other cases in s_lock.h require similar treatment, but
this is the only one we've got conclusive evidence for at present.
- Remove some tabs from README file. Some of the ASCII art expected
8-space tab stops, and some of it expected 4-space tab stops. Per
report from YAMAMOTO Takashi.
- Fix parsing of time string followed by yesterday/today/tomorrow.
Previously, 'yesterday 04:00:00'::timestamp didn't do the same thing
as '04:00:00 yesterday'::timestamp, and the return value from the
latter was midnight rather than the specified time. Dean Rasheed,
with some stylistic changes
- Add --if-exists option to dropdb and dropuser. Josh Kupershmidt,
with some further editing by me.
- Repair brain fade in previous commit, per Josh Kupershmidt.
- Fix "is db labeled test?" in chkselinuxenv script. Don't test
whether the number of labels is numerically equal to zero; count(*)
isn't going return zero anyway, and the current coding blows up if
it returns an empty string or an error.
- Userspace access vector cache for contrib/sepgsql. KaiGai Kohei
- Minor improvements to mbregress.sh script. 1. Use new dropdb
--if-exists option, to avoid alarming the user if the database being
dropped doesn't already exist. 2. Bail out if createdb fails. 3.
exit 1 if the checks fail. 4. Make it executable. Josh
Kupershmidt, with some kibitzing by me.
Tom Lane pushed:
- Use a non-locking test in TAS_SPIN() on all IA64 platforms. Per my
testing, this works just as well with gcc as it does with HP's
compiler; and there is no reason to think that the effect doesn't
occur with icc, either. Also, rewrite the header comment about
enforcing sequencing around spinlock operations, per Robert Haas's
gripe that it was misleading.
- Fix concat_ws() to not insert a separator after leading NULL
argument(s). Per bug #6181 from Itagaki Takahiro. Also do some
marginal code cleanup and improve error handling.
- Replace obsolete AC_LANG_FUNC_LINK_TRY autoconf macro. The version
of this macro used in autoconf 2.59 is capable of incorrectly
succeeding (ie, reporting that a library function is available when
it isn't), if the compiler performs link-time optimization and
decides that it can optimize the function reference away entirely.
Replace it with the coding used in autoconf 2.61 and later, which
forces the program result to depend on the function's result so that
it cannot be optimized away. This should fix build failures
currently being seen on buildfarm member anchovy. This patch
affects the 8.2 and 8.3 branches only, since later branches are
using autoconf versions that don't have this problem.
- Fix a missed case in code for "moving average" estimate of
reltuples. It is possible for VACUUM to scan no pages at all, if
the visibility map shows that all pages are all-visible. In this
situation VACUUM has no new information to report about the
relation's tuple density, so it wasn't changing pg_class.reltuples
... but it updated pg_class.relpages anyway. That's wrong in
general, since there is no evidence to justify changing the density
ratio reltuples/relpages, but it's particularly bad if the previous
state was relpages=reltuples=0, which means "unknown tuple density".
We just replaced "unknown" with "zero". ANALYZE would eventually
recover from this, but it could take a lot of repetitions of ANALYZE
to do so if the relation size is much larger than the maximum number
of pages ANALYZE will scan, because of the moving-average behavior
introduced by commit b4b6923e03f4d29636a94f6f4cc2f5cf6298b8c8. The
only known situation where we could have relpages=reltuples=0 and
yet the visibility map asserts everything's visible is immediately
following a pg_upgrade. It might be advisable for pg_upgrade to try
to preserve the relpages/reltuples statistics; but in any case this
code is wrong on its own terms, so fix it. Per report from Sergey
Koposov. Back-patch to 8.4, where the visibility map was
introduced, same as the previous change.
- Fix not-backwards-compatible pg_upgrade test for prepared
transactions. There's no reason for this test to use the
undocumented pg_prepared_xact() function, when it can use the stable
API pg_prepared_xacts instead. Fixes breakage against 8.3, as
reported by Justin Arnold.
- Improve eqjoinsel's ndistinct clamping to work for multiple levels
of join. This patch fixes an oversight in my commit
7f3eba30c9d622d1981b1368f2d79ba0999cdff2 of 2008-10-23. That patch
accounted for baserel restriction clauses that reduced the number of
rows coming out of a table (and hence the number of
possibly-distinct values of a join variable), but not for join
restriction clauses that might have been applied at a lower level of
join. To account for the latter, look up the sizes of the
min_lefthand and min_righthand inputs of the current join, and clamp
with those in the same way as for the base relations. Noted while
investigating a complaint from Ben Chobot, although this in itself
doesn't seem to explain his report. Back-patch to 8.4; previous
versions used different estimation methods for which this heuristic
- Further repair of eqjoinsel ndistinct-clamping logic. Examination
of examples provided by Mark Kirkwood and others has convinced me
that actually commit 7f3eba30c9d622d1981b1368f2d79ba0999cdff2 was
quite a few bricks shy of a load. The useful part of that patch was
clamping ndistinct for the inner side of a semi or anti join, and
the reason why that's needed is that it's the only way that
restriction clauses eliminating rows from the inner relation can
affect the estimated size of the join result. I had not clearly
understood why the clamping was appropriate, and so mis-extrapolated
to conclude that we should clamp ndistinct for the outer side too,
as well as for both sides of regular joins. These latter actions
were all wrong, and are reverted with this patch. In addition, the
clamping logic is now made to affect the behavior of both paths in
eqjoinsel_semi, with or without MCV lists to compare. When we have
MCVs, we suppose that the most common values are the ones that are
most likely to survive the decimation resulting from a lower
restriction clause, so we think of the clamping as eliminating
non-MCV values, or potentially even the least-common MCVs for the
inner relation. Back-patch to 8.4, same as previous fixes in this
- Put back improperly removed #include.
- Teach ANALYZE to clear pg_class.relhassubclass when appropriate. In
the past, relhassubclass always remained true if a relation had ever
had child relations, even if the last subclass was long gone. While
this had only marginal performance implications in most cases, it
was annoying, and I'm now considering some planner changes that
would raise the cost of a false positive. It was previously
impractical to fix this because of race condition concerns.
However, given the recent change that made tablecmds.c take
ShareExclusiveLock on relations that are gaining a child (commit
fbcf4b92aa64d4577bcf25925b055316b978744a), we can now allow ANALYZE
to clear the flag when it's no longer relevant. There is no
additional locking cost to do so, since ANALYZE takes
- Rearrange planner to save the whole PlannerInfo (subroot) for a
subquery. Formerly, set_subquery_pathlist and other creators of
plans for subqueries saved only the rangetable and rowMarks lists
from the lower-level PlannerInfo. But there's no reason not to
remember the whole PlannerInfo, and indeed this turns out to
simplify matters in a number of places. The immediate reason for
doing this was so that the subroot will still be accessible when
we're trying to extract column statistics out of an already-planned
subquery. But now that I've done it, it seems like a good
code-beautification effort in its own right. I also chose to get
rid of the transient subrtable and subrowmark fields in SubqueryScan
nodes, in favor of having setrefs.c look up the subquery's
RelOptInfo. That required changing all the APIs in setrefs.c to
pass PlannerInfo not PlannerGlobal, which was a large but quite
mechanical transformation. One side-effect not foreseen at the
beginning is that this finally broke inheritance_planner's
assumption that replanning the same subquery RTE N times would
necessarily give interchangeable results each time. That assumption
was always pretty risky, but now we really have to make a separate
RTE for each instance so that there's a place to carry the separate
- Fix typo in pg_srand48 (srand48 in older branches). ">" should be
">>". This typo results in failure to use all of the bits of the
provided seed. This might rise to the level of a security bug if we
were relying on srand48 for any security-critical purposes, but we
are not --- in fact, it's not used at all unless the platform lacks
srandom(), which is improbable. Even on such a platform the
exposure seems minimal. Reported privately by Andres Freund.
- Remove unnecessary and circular #include. storage/proc.h should not
include replication/syncrep.h, especially not when the latter
includes storage/proc.h; but in any case this was a pretty poor
thing from a modular layering standpoint.
- Clean up the #include mess a little. walsender.h should depend on
xlog.h, not vice versa. (Actually, the inclusion was circular until
a couple hours ago, which was even sillier; but Bruce broke it in
the expedient rather than logically correct direction.) Because of
that poor decision, plus blind application of pgrminclude, we had a
situation where half the system was depending on xlog.h to include
such unrelated stuff as array.h and guc.h. Clean up the header
inclusion, and manually revert a lot of what pgrminclude had done so
things build again. This episode reinforces my feeling that
pgrminclude should not be run without adult supervision. Inclusion
changes in header files in particular need to be reviewed with great
care. More generally, it'd be good if we had a clearer notion of
module layering to dictate which headers can sanely include which
others ... but that's a big task for another day.
- Can't print PlannerGlobal's subroots list in outfuncs. Since the
subroots will surely link back to the same glob struct, this
necessarily leads to infinite recursion. Doh. Found while trying
to debug some other code.
- Dig down into sub-selects to look for column statistics. If a
sub-select's output column is a simple Var, recursively look for
statistics applying to that Var, and use them if available. The
need for this was foreseen ages ago, but we didn't have enough
infrastructure to do it with reasonable speed until just now. We
punt and stick with default estimates if the subquery uses set
operations, GROUP BY, or DISTINCT, since those operations would
change the underlying column statistics (particularly, the relative
frequencies of different values) beyond recognition. This means
that the types of sub-selects for which this improvement applies are
fairly limited, since most subqueries satisfying those restrictions
would have gotten flattened into the parent query anyway. But it
does help for some cases, such as subqueries with ORDER BY or LIMIT.
- Change get_variable_numdistinct's API to flag default estimates
explicitly. Formerly, callers tested for DEFAULT_NUM_DISTINCT,
which had the problem that a perfectly solid estimate might be
mistaken for a content-free default.
- Fix #include problems in 9.1 branch. Remove unnecessary and
circular #include of syncrep.h from proc.h. Add htup.h to
tablecmds.h so it will compile without prerequisites.
- Guard against using plperl's Makefile without specifying
--with-perl. The $(PERL) macro will be set by configure if it finds
perl at all, but $(perl_privlibexp) isn't configured unless you said
--with-perl. This results in confusing error messages if someone
cd's into src/pl/plperl and tries to build there despite the
configure omission, as reported by Tomas Vondra in bug #6198. Add
simple checks to provide a more useful report, while not disabling
other use of the makefile such as "make clean". Back-patch to 9.0,
which is as far as the patch applies easily.
Andrew Dunstan pushed:
- Detect out of date flex in MSVC builds. Per recent discussion,
following a report from Quan Zongliang. The same logic is used as
- Supply missing brace omitted by commit 12613cb6b83cac1aa1e7882e84902c445fce3e74.
Peter Eisentraut pushed:
- Some markup cleanup to deconfuse the find_gt_lt tool. Josh
- Clean up pg_regress --help output. Put options listing in a less
random order, fix capitalization, and some typos.
- Whitespace adjustment for consistency in the file
- Add archive_command example
Heikki Linnakangas pushed:
- The replication status values in pg_stat_replication was changed to
lowercase earlier, but documentation was not updated. Update the
docs. Fujii Masao
- Move the line to undefine setlocale() macro on Win32 outside
USE_REPL_SNPRINTF ifdef block. It has nothing to do with whether the
replacement snprintf function is used. It caused no live bug,
because the replacement snprintf function is always used on Win32,
but it was nevertheless misplaced.
- setlocale() on Windows doesn't work correctly if the locale name
contains dots. I previously worked around this in initdb, mapping
the known problematic locale names to aliases that work, but Hiroshi
Inoue pointed out that that's not enough because even if you use one
of the aliases, like "Chinese_HKG", setlocale(LC_CTYPE, NULL)
returns back the long form, ie. "Chinese_Hong Kong S.A.R.". When we
try to restore an old locale value by passing that value back to
setlocale(), it fails. Note that you are affected by this bug also
if you use one of those short-form names manually, so just reverting
the hack in initdb won't fix it. To work around that, move the
locale name mapping from initdb to a wrapper around setlocale(), so
that the mapping is invoked on every setlocale() call. Also, add a
few checks for failed setlocale() calls in the backend. These calls
shouldn't fail, and if they do there isn't much we can do about it,
but at least you'll get a warning. Backpatch to 9.1, where the
initdb hack was introduced. The Windows bug affects older versions
too if you set locale manually to one of the aliases, but given the
lack of complaints from the field, I'm hesitent to backpatch.
- Fix MinGW build, broken by my previous patch to add a setlocale()
wrapper on Windows. ecpglib doesn't link with libpgport, but picks
and compiles the .c files it needs individually. To cope with that,
move the setlocale() wrapper from chklocale.c to a separate
setlocale.c file, and include that in ecpglib.
- libpq compiles various pgport files like ecpg does, and needs
similar Makefile changes for the win32 setlocale() wrapper I put
into ecpg, to make it compile on MinGW.
- Remove spurious comma. Spotted by Tom Lane.
Bruce Momjian pushed:
- Fix pg_upgrade to preserve toast relfrozenxids for old 8.3 servers.
This fixes a pg_upgrade bug that could lead to query errors when
clog files are improperly removed. Backpatch to 8.4, 9.0, 9.1.
- Remove "fmgr.h" include in cube contrib --- caused crash on a Gentoo
- Remove unnecessary #include references, per pgrminclude script.
- In SGML we only need to worry about "<", not ">"; update scripts.
- Add missing hba.h include for NetBSD.
- Add C comment about necessary NetBSD include.
- Add C comment about needed include.
- Improve method of avoiding fcinfo compile errors. Fix pgrminclude C
- Remove find_lt sgml tool, as it is not needed. Per suggestion from
- Move AllowCascadeReplication() define from xlog.h to replication
include file. Per suggestion from Alvaro Herrera.
- walsender.h doesn't need xlog.h, per Tom Lane.
Michael Meskes pushed:
- In ecpglib restore LC_NUMERIC in case of an error.
- Fix brace indentation of commit 63d06ef59156719efd0208c62e764a69611b3f12
to fit PostgreSQL style.
== Rejected Patches (for now) ==
No one was disappointed this week :-)
== Pending Patches ==
Peter Eisentraut sent in another revision of the patch to test
Tom Lane sent in a WIP patch to install regress.so and related
libraries into $libdir.
Oleg Bartunov sent in two more revisions of a patch to enable
space-partitioned GiST indexes.
Marti Raudsepp sent in a patch to label subquery expressions with
better column names.
Jeff Davis sent in another revision of the patch to add range types.
Tomas Vondra sent in two more revisions of a patch to log checkpoint
Magnus Hagander sent in a patch to implements a "low watermark wal
location" in the walsender shmem array.
Shigeru HANADA sent in another revision of the patch to add a
force_not_null attribute to the file FDW.
pgsql-announce by date
|Next:||From: SQL Maestro Group||Date: 2011-09-05 10:58:33|
|Subject: Re: == PostgreSQL Weekly News - September 04 2011 ==|
|Previous:||From: Gabriele Bartolini||Date: 2011-09-02 10:02:03|
|Subject: Italian PGDay 2011, Call for papers is now open|