Skip site navigation (1) Skip section navigation (2)

== Wöchentlicher PostgreSQL Newsletter - 16. März 2008 ==

From: Andreas 'ads' Scherbaum <adsmail(at)wars-nicht(dot)de>
To: pgsql-de-allgemein(at)postgresql(dot)org <pgsql-de-allgemein(at)postgresql(dot)org>
Cc: pgusers(at)postgres(dot)de <pgusers(at)postgres(dot)de>
Subject: == Wöchentlicher PostgreSQL Newsletter - 16. März 2008 ==
Date: 2008-03-17 10:32:09
Message-ID: 20080317113209.25e3124f@iridium.home (view raw, whole thread or download thread mbox)
Lists: pgsql-de-allgemein
Der Originalartikel befindet sich unter:

== Wöchentlicher PostgreSQL Newsletter - 16. März 2008 ==

Die ITPUG trifft sich, um den PGDay.IT zu planen, welcher am 17. und
18. Oktober in Prato stattfindet. Das Meeting wird in Italien sein und
am 18. März um 21:30 CET via IRC stattfinden.

United States PostgreSQL Association gegründet.

Dave Page hat ein Wiki für User Dokumentation aufgesetzt.

== PostgreSQL Produkt Neuigkeiten ==

Italian full-text search für PostgreSQL 8.3 erschienen.

AM Lightning Debugger Client 1.0 erschienen.

devrim GUNDUZ hat eine neue Live CD mit Updates für PostgreSQL 8.3.1,
plruby, und ptop hochgeladen. Jetzt sind pg_filedump, ruby-pg und
pgbouncer enthalten.

OpenRPT 2.3.0 erschienen.

pgloader 2.3.0 erschienen.

PgWorksheet 1.9 erschienen.

ptop 3.6.2-beta1 erschienen.

SE-PostgreSQL v8.3 erschienen.

== PostgreSQL Jobs im März ==

== PostgreSQL Lokal ==

Das erste Meeting der Atlanta PUG wird am 11. März 2008 um 18:30 Uhr

Jon Asher wird über PostORM auf dem SFPUG Treffen am 18. März um 19:30

Das erste Treffen der Marokkos PUG wird am 22. März um 9:00 in Tangier

Die LAPUG trifft sich am 28. März um 19:00 Uhr im City of Garden Grove

PostgreSQL Conference East '08 ist am 29. und 30. März an der
Universität von Maryland, College Park.

Der PG UK Tag wird am 2. April in Birmingham sein.

Die FISL wird vom 17. bis 19. April 2008 auf der PUCRS in Porto Alegre,
Brasilien, stattfinden.

PGCon 2008 wird vom 20. bis 23. Mai in Ottawa sein.

Der Call for Papers für die Utah Open Source Konferenz 2008 ist bis zum
1. Juni offen. Diese 2. jährliche Konferenz wird vom 28. bis 30. August
2008 in Salt Lake City, UT stattfinden.

== PostgreSQL in den News ==

Planet PostgreSQL:

General Bits, Archive und gelegentliche News Artikel:

Dieser wöchentliche PostgreSQL Newsletter wurde erstellt von David

Sende Neuigkeiten und Ankündigungen bis Sonntag, 15 Uhr Pazifischer
Zeit. Bitte sende englische Beiträge an david(at)fetter(dot)org, deutsche an
pwn(at)pgug(dot)de, italienische an pwn(at)itpug(dot)org(dot)

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

- Stamp version 8.2.7, except for

- 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

- 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
  shared buffers."

- 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
  subtransaction aborts."

- 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
  expression indexes."

- 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
  expression indexes."

- 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
  child contexts."

- 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 to TODO for Win32: "Convert MSVC build system to remove most
  batch files."

- 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

- In pgsql/doc/src/sgml/install-win32.sgml, fix duplicate word, per
  Guillaume Lelarge.

- 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

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte 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.

		Andreas 'ads' Scherbaum
Deutsche PostgreSQL User Group:

pgsql-de-allgemein by date

Next:From: Michael ProchaskaDate: 2008-03-17 14:28:22
Subject: viele kleine queries vs wenige große queries
Previous:From: Marc HanischDate: 2008-03-12 13:42:46
Subject: Re: Dynamisches Select Statement in Funktion

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group