== Postgres Weekly News - March 16 2008 ==
ITPUG will meet to plan PGDay.IT, which is happening October 17 and
18, 2008 in Prato. The meeting will be in Italian and will be on
Tuesday, March 18 at 2130 CET via IRC.
United States PostgreSQL Association launched.
Dave Page has set up a user documentation wiki.
== Postgres Product News ==
Italian full-text search for PostgreSQL 8.3 released.
AM Lightning Debugger Client 1.0 released.
Devrim GUNDUZ has uploaded a new livecd with updates to PostgreSQL
8.3.1, plruby and ptop, now including pg_filedump, ruby-pg and
OpenRPT 2.3.0 released.
pgloader 2.3.0 released.
PgWorksheet 1.9 released.
ptop 3.6.2-beta1 released.
SE-PostgreSQL v8.3 released.
== Postgres Jobs for March ==
== Postgres Local ==
Atlanta PUG's first meeting will be March 18, 6:30pm.
Jon Asher will talk about PostORM at SFPUG on March 18 at 7:30pm.
Morocco PUG's first meeting will be March 22, 9:00am in Tangier.
LAPUG will be meeting March 28, 7:00pm in the City of Garden Grove
PostgreSQL Conference East '08 talks are March 29 and 30 at the
University of Maryland, College Park.
PG UK day will be April 2 in Birmingham.
FISL 9.0 will be April 17-19 at PUCRS in Porto Alegre, RS, Brazil.
PGCon 2008 will be May 20-23 in Ottawa.
Utah Open Source Conference 2008's CfP is open through June 1.
This 2nd annual conference is August 28-30, 2008 in Salt Lake City, UT
== Postgres in the News ==
Planet PostgreSQL: http://www.planetpostgresql.org/
General Bits, Archives and occasional new articles:
Postgres 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)
== Applied Patches ==
Tom Lane committed:
- In pgsql/contrib/pgbench/pgbench.c, fix pgbench's getrand() function
so that min and max have approximately the same chance of being
selected as do numbers between them. Problem noted by Greg Stark;
fix by Alexey Klyukin.
- Reduce memory consumption during VACUUM of large relations, by using
FSMPageData (6 bytes) instead of PageFreeSpaceInfo (8 or 16 bytes)
for the temporary array of page-free-space information. Itagaki
- In pgsql/src/backend/access/transam/xlog.c, remove no-longer-used
XLogCacheByte field of XLogCtl. Itagaki Takahiro.
- Add a CaseSensitive parameter to synonym dictionaries. Simon Riggs.
- Increase the default value of log_min_messages to WARNING, so that
NOTICE-grade messages are not logged by default. Per pgsql-hackers
discussion back on 21-Nov-2007.
- Throw an error for negative LIMIT or OFFSET values, instead of
silently treating them as zero. Simon Riggs
- Document and enforce that the usable range of setseed() arguments is
-1 to 1, not 0 to 1. The actual behavior for values within this
range does not change. Kris Jurka
- In pgsql/src/backend/utils/adt/tsvector.c, fix unportable coding of
new error message, per Kris Jurka.
- Provide a build-time option to store large relations as single
files, rather than dividing them into 1GB segments as has been our
longtime practice. This requires working support for large files in
the operating system; at least for the time being, it won't be the
default. Zdenek Kotala
- Use -fwrapv in CFLAGS if we are using a version of gcc that accepts
this flag. This prevents compiler optimizations that assume
overflow won't occur, which breaks numerous overflow tests that we
need to have working. It is known that gcc 4.3 causes problems and
possible that 4.1 does. Per my proposal of some time ago and a
recent report from Kris Jurka. Backpatch as far as 8.0, which is as
far as the patch conveniently goes. 7.x was pretty short of
overflow tests anyway, so it may not matter there, even assuming
that anyone cares whether 7.x builds on recent gcc.
- Make TransactionIdIsInProgress check transam.c's single-item XID
status cache before it goes groveling through the ProcArray. In
situations where the same recently-committed transaction ID is
checked repeatedly by tqual.c, this saves a lot of shared-memory
searches. And it's cheap enough that it shouldn't hurt noticeably
when it doesn't help. Concept and patch by Simon, some minor
tweaking and comment-cleanup by Tom.
- In pgsql/contrib/pgbench/pgbench.c, add a comment explaining one of
the ways that pgbench fails to fully comply with TPC-B. Per Itagaki
Takahiro and discussion of bug#3681.
- Fix LISTEN/NOTIFY race condition reported by Laurent Birtz, by
postponing pg_listener modifications commanded by LISTEN and
UNLISTEN until the end of the current transaction. This allows us
to hold the ExclusiveLock on pg_listener until after commit, with no
greater risk of deadlock than there was before. Aside from fixing
the race condition, this gets rid of a truly ugly kludge that was
there before, namely having to ignore HeapTupleBeingUpdated failures
during NOTIFY. There is a small potential incompatibility, which is
that if a transaction issues LISTEN or UNLISTEN and then looks into
pg_listener before committing, it won't see any resulting row
insertion or deletion, where before it would have. It seems
unlikely that anyone would be depending on that, though. This patch
also disallows LISTEN and UNLISTEN inside a prepared transaction.
That case had some pretty undesirable properties already, such as
possibly allowing pg_listener entries to be made for PIDs no longer
present, so disallowing it seems like a better idea than trying to
maintain the behavior.
- In pgsql/src/backend/tcop/postgres.c, fix pg_plan_queries() to
restore the previous setting of ActiveSnapshot (probably NULL)
before exiting. Up to now it's just left the variable as it set it,
which means that after we're done processing the current client
message, ActiveSnapshot is probably pointing at garbage (because
this function is typically run in MessageContext which will get
reset). There doesn't seem to have been any code path in which that
mattered before 8.3, but now the plancache module might try to use
the stale value if the next client message is a Bind for a prepared
statement that is in need of replanning. Per report from Alex
- Fix heap_page_prune's problem with failing to send cache
invalidation messages if the calling transaction aborts later on.
Collapsing out line pointer redirects is a done deal as soon as we
complete the page update, so syscache *must* be notified even if the
VACUUM FULL as a whole doesn't complete. To fix, add some
functionality to inval.c to allow the pending inval messages to be
sent immediately while heap_page_prune is still running. The
implementation is a bit chintzy: it will only work in the context of
VACUUM FULL. But that's all we need now, and it can always be
extended later if needed. Per my trouble report of a week ago.
- In pgsql/src/backend/utils/adt/varlena.c, fix varstr_cmp's special
case for UTF8 encoding on Windows so that strings that are reported
as "equal" by wcscoll() are checked to see if they really are
bitwise equal, and are sorted per strcmp() if not. We made this
happen a couple of years ago in the regular code path, but it
unaccountably got left out of the Windows/UTF8 case (probably brain
fade on my part at the time). As in the prior set of changes,
affected users may need to reindex indexes on textual columns.
Backpatch as far as 8.2, which is the oldest release we are still
supporting on Windows.
- Update to tzdata 2008a distribution (Chilean DST law change).
- In pgsql/doc/src/sgml/release.sgml, update release notes for 8.3.1
and 8.2.7 releases.
- Stamp version 8.3.1, except for configure.in/configure.
- Stamp version 8.2.7, except for configure.in/configure.
- In pgsql/src/backend/postmaster/autovacuum.c, fix inappropriately-timed
memory context switch in autovacuum_do_vac_analyze. This accidentally
failed to fail before 8.3, because the context we were switching back
to was long-lived anyway; but it sure looks risky as can be now.
Well spotted by Pavan Deolasee.
- Change hash index creation so that rather than always establishing
exactly two buckets at the start, we create a number of buckets
appropriate for the estimated size of the table. This avoids a lot
of expensive bucket-split actions during initial index build on an
already-populated table. This is one of the two core ideas of Tom
Raney and Shreya Bhargava's patch to reduce hash index build time.
I'm committing it separately to make it easier for people to test
the effects of this separately from the effects of their other core
idea (pre-sorting the index entries by bucket number).
Magnus Hagander committed:
- Implement enum type for guc parameters, and convert a couple of
existing variables to it. More need to be converted, but I wanted to
get this in before it conflicts with too much... Other than just
centralising the text-to-int conversion for parameters, this allows
the pg_settings view to contain a list of available options and
allows an error hint to show what values are allowed.
- Bump catversion from guc enum patch, which I forgot. Sorry!
- Some cleanups of enum-guc code, per comments from Tom.
Bruce Momjian committed:
- Add to TODO: "Consider increasing the minimum allowed number of
- Add URL for TODO: "Allow administrators to safely terminate
individual sessions either via an SQL function or SIGTERM."
- Add URL for TODO: "Optimize referential integrity checks."
- Add to TODO: "Set up autovacuum to ignore statement_timeout set in
- In pgsql/doc/src/sgml/config.sgml, document that statement_timeout
is not recommended in postgresql.conf because it affects all
sessions, including autovacuum.
- Add to TODO: "Consider adding buffers the BGW finds reusable to the
free list" and "Automatically tune bgwriter_delay based on activity
rather then using a fixed interval."
- Remove from TODO: "Set up autovacuum to ignore statement_timeout set
in postgresql.conf." per Alvaro Herrera.
- In pgsql/doc/src/sgml/config.sgml, back out doc addition that
statement_timeout affects autovacuum.
- Add to TODO: "Allow statistics last vacuum/analyze execution times
to be displayed without requiring stats_row_level to be enabled."
- In pgsql/doc/src/sgml/config.sgml, add "Setting statement_timeout in
postgresql.conf is not recommended because it affects all sessions."
Backpatch to 8.3.X.
- Add to TODO: "Prevent pg_dump/pg_restore from being affected by
statement_timeout. Using psql to restore a pg_dump dump is also
- Add to TODO for PL/PgSQL: "Review handling of MOVE and FETCH."
- Add to TODO: "Add temporal versions of generate_series()."
- Add URL for TODO: "Fix problem when multiple subtransactions of the
same outer transaction hold different types of locks, and one
- Add to TODO for VACUUM: "Consider a more compact data representation
for dead tuples."
- Add to TODO: "Consider using a hash for joining to a large IN
(VALUES ...) list."
- Add to TODO: "Consider increasing the number of default statistics
target, and reduce statistics target overhead. Also consider having
a larger statistics target for indexed columns and expression
- Add URL for TODO: "Consider increasing the number of default
statistics target, and reduce statistics target overhead. Also
consider having a larger statistics target for indexed columns and
- Add another URL for TODO: "Consider increasing the number of default
statistics target, and reduce statistics target overhead. Also
consider having a larger statistics target for indexed columns and
- Add to TODO: "Change memory allocation for multi-byte functions so
memory is allocated inside conversion functions."
- Add to TODO: "Prefix command-line utilities like createuser with
- Add to TODO: "Consider sorting writes during checkpoint."
- Add to TODO: "Consider normalizing fractions in postgresql.conf,
perhaps using '%'."
- Add URL for TODO: "Reduce storage space for small NUMERICs."
- Update TODO to read: "Consider adding buffers the background writer
finds reusable to the free list. Consider whether increasing
BM_MAX_USAGE_COUNT improves performance."
- Add to TODO: "Test to see if calling PreallocXlogFiles() from the
background writer will help with WAL segment creation latency."
- Add to TODO: "Research use of signals and sleep wake ups."
- Add to TODO: "Add automated check for invalid C++ source code
- Add to TODO for PL/PgSQL: "Improve logic of determining if an
identifier is a variable or column name."
- Add to TODO for COPY: "Allow COPY in CSV mode to control whether ""
is treated as NULL."
- Update the above TODO to read: "Allow COPY in CSV mode to control
whether a quoted zero-length string is treated as NULL. Currently
this is always treated as a zero-length string, which generates an
error when loading into an integer column."
- In pgsql/src/bin/psql/copy.c, prevent psql \copy from accepting
duplicate string parameters. Per report from Stephen Frost.
- Add URL for TODO: "Consider increasing NUM_CLOG_BUFFERS."
- Add URL for TODO: "Allow user configuration of TOAST thresholds."
- Add to TODO: "Consider simplifying how memory context resets handle
- Add to TODO: "Allow domains to be cast."
- Add URL for TODO: "Improve speed with indexes. For large table
adjustments during VACUUM FULL, it is faster to cluster or reindex
rather than update the index. Also, index updates can bloat the
- Add to TODO: "Add array_accum() and array_to_set() functions for
- Add to TODO: "Remove use of MAKE_PTR and MAKE_OFFSET macros."
- Add to TODO for Win32: "Diagnose problem where shared memory can
sometimes not be attached by postmaster children."
- Add URL for TODO: "Add REINDEX CONCURRENTLY, like CREATE INDEX
- Add to TODO for Win32: "Convert MSVC build system to remove most
- Add URL for TODO: "Consider a simplified API for full text
- Update TODO: "Add array_accum() and array_to_set() functions for
arrays" add "The standards specify array_agg() and UNNEST."
- Back out text search change to TODO.
- Add to TODO: "Consider a special data type for regular expressions."
- Add to TODO: "Expire published xmin for read-only and idle
- Add to TODO: "Include the symbolic SQLSTATE name in verbose error
- Add URL for TODO: "Do async I/O for faster random read-ahead of
data. Async I/O allows multiple I/O requests to be sent to the disk
with results coming back asynchronously."
Marc Fournier commited:
- Update configure for 8.3.1 prior to tag.
- Update configure for 8.2.7 prior to tag.
Peter Eisentraut committed:
- Translation updates.
Alvaro Herrera committed:
- Fix vacuum so that autovacuum is really not cancelled when doing an
emergency job (i.e. to prevent Xid wraparound problems.) Bug
reported by ITAGAKI Takahiro in 20080314103837(dot)63D3(dot)52131E4D(at)oss(dot)ntt(dot)co(dot)jp,
though I didn't use his patch.
- In pgsql/doc/src/sgml/install-win32.sgml, fix duplicate word, per
- Modify interactions between sinval.c and sinvaladt.c. The code that
actually deals with the queue, including locking etc, is all in
sinvaladt.c. This means that the struct definition of the queue,
and the queue pointer, are now internal "implementation details"
inside sinvaladt.c. Per my proposal dated 25-Jun-2007 and followup
== Rejected Patches (for now) ==
No one was disappointed this week :-)
== Pending Patches ==
Zoltan Boszormenyi sent in a WIP patch to allow 64-bit integers and
floats to get passed by value on 64-bit platforms, and to change
32-bit floats to be passed by value along the way.
Heikki Linnakangas sent in two revisions of a patch which converts
some linked lists into expandable arrays.
Bruce Momjian sent in a patch which rejects conflicting options given
to COPY...CSV in psql.
ITAGAKI Takahiro sent in a patch intended to suppress compiler
warnings in the mingw build.
pgsql-announce by date
|Next:||From: Dan Langille||Date: 2008-03-18 03:00:34|
|Subject: PGCon 2008 - registration open|
|Previous:||From: Henri Michelon||Date: 2008-03-14 13:23:20|
|Subject: PgWorksheet 1.9|