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

Der Originalartikel befindet sich unter:
http://people.planetpostgresql.org/dfetter/index.php?/archives/164-Postgres-Weekly-News-March-23-2008.html

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

PostgreSQL 8.3.1 und 8.2.7 sind erschienen. Schnell upgraden!

Summer of PostgreSQL: Google Summer of Code beginnt am 25. März,
Anmeldungen von Studenten, die an PostgreSQL Projekten arbeiten, zu
akzeptieren. Bereitet diese Projekte jetzt vor!

== PostgreSQL Produkt Neuigkeiten ==

MyJSQLView 2.86 erschienen.
http://myjsqlview.sourceforge.net/

Registration für PGCon 2008 ist eröffnet.
http://www.pgcon.org/2008/registration.php

ptop 3.6.2-beta2 erschienen.
http://ptop.projects.postgresql.org/

phpPgAdmin 4.2-Beta-2 erschienen. Bitte testen!
http://phppgadmin.sourceforge.net/?page=download

Pagila 0.10.1 erschienen.
http://pgfoundry.org/frs/?group_id=1000150&release_id=998

check_postgres 1.3.0 erschienen.
http://bucardo.org/nagios/

== PostgreSQL Jobs im März ==

http://archives.postgresql.org/pgsql-jobs/2008-03/threads.php

== PostgreSQL Lokal ==

Die LAPUG trifft sich am 28. März um 19:00 Uhr im City of Garden Grove
Trainingsraum.
http://pugs.postgresql.org/lapug

PostgreSQL Conference East '08 ist am 29. und 30. März an der
Universität von Maryland, College Park. Registrierung endet am 26. März.
http://www.postgresqlconference.org/

Der PG UK Tag wird am 2. April in Birmingham sein.
http://www.postgresql.org.uk/

Die FISL wird vom 17. bis 19. April 2008 auf der PUCRS in Porto Alegre,
Brasilien, stattfinden.
https://fisl.softwarelivre.org/9.0/

PGCon 2008 wird vom 20. bis 23. Mai in Ottawa sein.
http://www.pgcon.org/2008/

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.
http://2008.utosc.com/

== PostgreSQL in den News ==

Planet PostgreSQL: http://www.planetpostgresql.org/

General Bits, Archive und gelegentliche News Artikel:
http://www.varlena.com/GeneralBits/

Dieser wöchentliche PostgreSQL Newsletter wurde erstellt von David
Fetter und Josh Berkus.

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:

- When creating a large hash index, pre-sort the index entries by
estimated bucket number, so as to ensure locality of access to the
index during the insertion step. Without this, building an index
significantly larger than available RAM takes a very long time
because of thrashing. On the other hand, sorting is just useless
overhead when the index does fit in RAM. We choose to sort when the
initial index size exceeds effective_cache_size. This is a revised
version of work by Tom Raney and Shreya Bhargava.

- In pgsql/doc/src/sgml/ref/create_index.sgml, add a note to the
CREATE INDEX reference page about the impact of maintenance_work_mem
and effective_cache_size on index creation speed.

- Fix TransactionIdIsCurrentTransactionId() to use binary search
instead of linear search when checking child-transaction XIDs. This
makes for an important speedup in transactions that have large
numbers of children, as in a recent example from Craig Ringer. We
can also get rid of an ugly kluge that represented lists of
TransactionIds as lists of OIDs. Heikki Linnakangas

- In pgsql/src/backend/utils/sort/tuplesort.c, grab some low-hanging
fruit in the new hash index build code. oprofile shows that a
nontrivial amount of time is being spent in repeated calls to
index_getprocinfo, which really only needs to be called once. So do
that, and inline _hash_datum2hashkey to make it work.

- In pgsql/src/backend/utils/adt/selfuncs.c, revert thinko introduced
into prefix_selectivity() by my recent patch: make_greater_string
needs the < procedure not the >= one. Spotted by Peter Eisentraut.

- In pgsql/src/port/snprintf.c, fix our printf implementation to
follow spec: if a star parameter value for a precision is negative,
act as though precision weren't specified at all, that is the whole
.* part of the format spec should be ignored. Our previous coding
took it as .0 which is certainly wrong. Per report from Kris Jurka
and local testing. Possibly this should be back-patched, but it
would be good to get some more testing first; in any case there are
no known cases where there's really a problem on the backend side.

- In pgsql/src/backend/executor/nodeIndexscan.c, advance multiple
array keys rightmost-first instead of leftmost-first during a bitmap
index scan. This cannot affect the query results (since we're just
dumping the TIDs into a bitmap) but it might offer some advantage in
locality of access to the index. Per Greg Stark.

- Arrange to "inline" SQL functions that appear in a query's FROM
clause, are declared to return set, and consist of just a single
SELECT. We can replace the FROM-item with a sub-SELECT and then
optimize much as if we were dealing with a view. Patch from Richard
Rowell, cleaned up by me.

- In pgsql/src/backend/utils/adt/regexp.c, fix regexp substring
matching (substring(string from pattern)) for the corner case where
there is a match to the pattern overall but the user has specified a
parenthesized subexpression and that subexpression hasn't got a
match. An example is substring('foo' from 'foo(bar)?'). This
should return NULL, since (bar) isn't matched, but it was mistakenly
returning the whole-pattern match instead (ie, 'foo'). Per bug
#4044 from Rui Martins. This has been broken since the beginning;
patch in all supported versions. The old behavior was sufficiently
inconsistent that it's impossible to believe anyone is depending on
it.

- Support ALTER TYPE RENAME. Petr Jelinek

- Support a --no-tablespaces option in pg_dump/pg_dumpall/pg_restore,
so that dumps can be loaded into databases without the same
tablespaces that the source had. The option acts by suppressing all
"SET default_tablespace" commands, and also CREATE TABLESPACE
commands in pg_dumpall's case. Gavin Roy, with documentation and
minor fixes by me.

- In pgsql/src/bin/pg_dump/pg_dumpall.c, dept of second thoughts:
--no-tablespaces had better also prevent pg_dumpall from attaching
TABLESPACE options to CREATE DATABASE commands.

- Arrange for an explicit cast applied to an ARRAY[] constructor to be
applied directly to all the member expressions, instead of the
previous implementation where the ARRAY[] constructor would infer a
common element type and then we'd coerce the finished array after
the fact. This has a number of benefits, one being that we can
allow an empty ARRAY[] construct so long as its element type is
specified by such a cast. Brendan Jurd, minor fixes by me.

- Get rid of a bunch of #ifdef HAVE_INT64_TIMESTAMP conditionals by
inventing a new typedef TimeOffset to represent an intermediate time
value. It's either int64 or double as appropriate, and in most
usages will be measured in microseconds or seconds the same as
Timestamp. We don't call it Timestamp, though, since the value
doesn't necessarily represent an absolute time instant. Warren
Turkal

- Adjust pgstatindex() to give correct answers for indexes larger than
2^31 blocks. Also fix pg_relpages() for the same case. Tatsuhito
Kasahara.

- Report the current queries of all backends involved in a deadlock
(if they'd be visible to the current user in pg_stat_activity).
This might look like it's subject to race conditions, but it's
actually pretty safe because at the time DeadLockReport() is
constructing the report, we haven't yet aborted our transaction and
so we can expect that everyone else involved in the deadlock is
still blocked on some lock. (There are corner cases where that
might not be true, such as a statement timeout triggering in another
backend before we finish reporting; but at worst we'd report a
misleading activity string, so it seems acceptable considering the
usefulness of reporting the queries.) Original patch by Itagaki
Takahiro, heavily modified by me.

- In pgsql/src/backend/parser/parse_utilcmd.c, give an explicit error
for serial[], rather than silently ignoring the array decoration as
the code had been doing.

- Remove TypeName struct's timezone flag, which has been write-only
storage for a very long time --- in current usage it's entirely
redundant with the name field.

- In pgsql/src/backend/utils/adt/formatting.c, refactor
to_char/to_date formatting code; primarily, replace DCH_processor
with two new functions DCH_to_char and DCH_from_char that have less
confusing APIs. Brendan Jurd.

- Create a function quote_nullable(), which works the same as
quote_literal() except that it returns the string 'NULL', rather
than a SQL null, when called with a null argument. This is often a
much more useful behavior for constructing dynamic queries. Add
more discussion to the documentation about how to use these
functions. Brendan Jurd

- In pgsql/src/backend/executor/nodeMaterial.c, avoid a useless tuple
copy within nodeMaterial. Neil Conway.

Bruce Momjian committed:

- Mark TODO as done: "During index creation, pre-sort the tuples to
improve build speed."

- Add to TODO: "Allow Kerberos to disable stripping of realms so we
can check the username(at)realm against multiple realms."

- Add URL for TODO: "Consider increasing the number of default
statistics target, and reduce statistics target overhead."

- Adjust TODO spacing.

- Add to TODO: "Fix server restart problem when the server was
shutdown during a PITR backup."

- Add to TODO: "Improve text search error messages" and "Fix
/contrib/ltree operator."

- Add URL for TODO: "Improve text search error messages."

- Add to TODO: "Remove pre-7.3 pg_dump code that assumes pg_depend
does not exit."

- Add URL for TODO: "Add SQL:2003 WITH RECURSIVE (hierarchical)
queries to SELECT."

- Add to TODO: "Consider if CommandCounterIncrement() can avoid its
AcceptInvalidationMessages() call."

- Add to TODO: "Reduce file system activity overhead of statistics
file pgstat.stat."

- Add to TODO: "Reduce BIT data type overhead using short varlena
headers."

- Add to TODO: "Allow SSL key file permission checks to be optionally
disabled when sharing SSL keys with other applications."

- Add URL for TODO: "Allow COPY to report error lines and continue."

- Add to TODO: "Consider Cartesian joins when both relations are
needed to form an indexscan qualification for a third relation."

- Add to TODO: "Recreate pg_xlog/archive_status/ if it doesn't exist
after restoring from a PITR backup."

- Add URLs for TODO: "Speed WAL recovery by allowing more than one
page to be prefetched."

- Add URLs for TODO: "Allow UPDATE tab SET ROW (col, ...) = (SELECT...)"

- Add to TODO: "Consider not storing a NULL bitmap on disk if all the
NULLs are trailing."

- In pgsql/src/tools/find_typedef, add Linux support to find_typedefs,
with help from Alvaro.

- In pgsql/src/tools/find_typedef, add find_typedef comment.

- In pgsql/src/tools/find_typedef, add find_typedef comments for
Linux.

- Update TODO: "Speed WAL recovery by allowing more than one page to
be prefetched."

- Split TODO entries into: "Experiment with multi-threaded backend
better I/O utilization" and "Experiment with multi-threaded backend
better CPU utilization" per suggestion by Heikki Linnakangas.

- Make source code READMEs more consistent. Add CVS tags to all
README files.

- More README src cleanups.

- In pgsql/doc/src/sgml/runtime.sgml, document that soft-mounting NFS
is not recommended.

- Add to TODO: "Prevent SSL from sending network packets to avoid
interference with Win32 signal emulation."

- Add to TODO: "Fix inconsistent precedence of =, >, and < compared to
<>, >=, and <=."

- Add to TODO: "Convert single quotes to apostrophes in the PDF
documentation."

- Add URLs for TODO: "Simplify ability to create partitioned tables."

- Add to TODO: "Improve performance of shared invalidation queue for
multiple CPUs."

- Add URLs for TODO: "Simplify ability to create partitioned tables."

- Add to TODO: "Add checks to prevent a CREATE RULE views on inherited
tables."

- Add URL for TODO: "Add checks to prevent a CREATE RULE views on
inherited tables."

- Add to TODO: "Avoid tuple some tuple copying in sort routines."

- Add to Win32 TODO: "Support pgxs."

- Add to TODO: "Improve WAL concurrency by increasing lock
granularity."

- Add to Win32 TODO: "Fix MSVC NLS support, like for to_char()."

- Add to pg_dump TODO: "Allow pre/data/post files when dumping a
single object, for performance reasons."

- Mark TODO as done: "Avoid tuple some tuple copying in sort
routines."

- Add URL for TODO: "Do async I/O for faster random read-ahead of
data."

- Add to TODO: "Sort large UPDATE/DELETEs so it is done in heap
order."

- Re-add to TODO: "Avoid tuple some tuple copying in sort routines."

- Add URL for TODO: "Avoid tuple some tuple copying in sort routines."

- Update Win32 TODO to read: "Support pgxs when using MSVC."

Alvaro Herrera committed:

- Move ProcState definition into sinvaladt.c from sinvaladt.h, since
it's not needed anywhere after my previous patch. Noticed by Tom
Lane. Also, remove #include <signal.h> from sinval.c.

- In pgsql/src/backend/storage/ipc/sinvaladt.c, move elog(DEBUG4) call
outside the locked area, per suggestion from Tom Lane.

- In pgsql/src/backend/commands/vacuum.c, we no longer need a snapshot
set after opening the finishing transaction: this is redundant
because autovacuum now always analyzes a single table per
transaction.

- In pgsql/src/backend/postmaster/autovacuum.c, remove another useless
snapshot creation.

- Add a couple of missing FreeQueryDesc calls. Noticed while testing
a framework to keep track of snapshots in use.

Magnus Hagander committed:

- Fix postgres --describe-config for guc enums, breakage noted by
Alvaro Herrera. While at it, rename option lookup functions to make
names clearer, per discussion with Tom Lane.

- In pgsql/doc/src/sgml/cvs.sgml, wiki page about cvs now lives in the
main wiki, the one on developer.postgresql.org is going away.

- In pgsql/doc/src/sgml/acronyms.sgml, cvsweb lives on
anoncvs.postgresql.org these days.

Peter Eisentraut committed:

- In pgsql/src/backend/common.mk, we need to rebuild objfiles.txt when
one of the subdirectories' objfiles.txt changed in case a new file
got added.

- Enable probes to work with Mac OS X Leopard and other OSes that will
support DTrace in the future. Switch from using DTRACE_PROBEn
macros to the dynamically generated macros. Use "dtrace -h" to
create a header file that contains the dynamically generated macros
to be used in the source code instead of the DTRACE_PROBEn macros.
A dummy header file is generated for builds without DTrace support.
Robert Lor.

- Catch all errors in for and while loops in makefiles. Don't ignore
any errors in any commands, including in various clean targets that
have so far been handled inconsistently. make -i is available to
ignore all errors in a consistent and official way.

- In pgsql/src/interfaces/ecpg/preproc/Makefile, don't need -Wno-error
anymore, because flex is no longer producing warnings.

Tatsuo Ishii committed:

- In pgsql/contrib/pgbench/pgbench.c, fix tps calculation when -C
supplied. Per Yoshiyuki Asaba. Change Copyright owner from mine to
PostgreSQL Global Development Group Fix minor message typo.

- Add libpq new API lo_import_with_oid() which is similar to
lo_import() except that lob's oid can be specified.

- Add -M (query mode) option to pgbench per ITAGAKI Takahiro.

- Add server side lo_import(filename, oid) function.

Heikki Linnakangas committed:

- In
pgsql/src/backend/utils/mb/conversion_procs/cyrillic_and_mic/cyrillic_and_mic.c,
add the missing cyrillic "Yo" characters ('e' and 'E' with two dots) to
the ISO_8859-5 <-> MULE_INTERNAL conversion tables. This was
discovered when trying to convert a string containing those characters
from ISO_8859-5 to Windows-1251, because we use MULE_INTERNAL/KOI8R as
an intermediate encoding between those two. While the missing "Yo" was
just an omission in the conversion tables, there are a few other
characters like the "Numero" sign ("No" as a single character) that
exists in all the other cyrillic encodings (win1251, ISO_8859-5 and
cp866), but not in KOI8R. Added comments about that. Patch by Sergey
Burladyan. Back-patch to 7.4.

Michael Meskes committed:

- In ecpg, changed statement escaping to not escape continuation line
markers.

- In HEAD's ecpg, added ECPGget_PGconn() function to ecpglib, courtesy
of Mike Aubury. Removed one include file from connect-test1.

- In 8.3's ecpg, added ECPGget_PGconn() function to ecpglib, courtesy
of Mike Aubury. Bumped library version to 6.1.

- In pgsql/src/interfaces/ecpg/ecpglib/Makefile, corrected version
number.

Andrew Dunstan committed:

- In pgsql/src/tools/msvc/Solution.pm, generate dummy probes.h for
MSVC builds.

== Abgelehnte Patches (bis jetzt) ==

Bruce Momjian's patch to pg_dump -i wording. The wording got less
scary rather than the intended more scary.

== Eingesandte Patches ==

ITAGAKI Takahiro sent another revision of his patch to suppress
compiler warnings on mingw.

Kohei KaiGai sent in a set of four patches intended to push parts of
SE-PostgreSQL into 8.4.

ITAGAKI Takahiro sent in another revision of his patch to add query
modes to pgbench to measure the performance of the simple protocol,
the extended protocol and prepared statements with it.

Alvaro Herrera sent in a patch which adds a new module to the snapshot
code which stashes used snapshots and refcounts them.

Simon Riggs sent in another revision of his WIP patch for tuning bulk
inserts.

Martin Pihlak sent in a patch which enables tracking function calls
through the stats subsystem per discussion below:
http://archives.postgresql.org/pgsql-hackers/2007-07/msg00377.php

--
Andreas 'ads' Scherbaum
Deutsche PostgreSQL User Group: http://www.pgug.de
DPWN: http://ads.wars-nicht.de/blog/categories/18-PWN