== Postgres Weekly News - March 23 2008 ==

== Postgres Weekly News - March 23 2008 ==

PostgreSQL 8.3.1 and 8.2.7 are out. Upgrade ASAP!

Summer of PostgreSQL: Google Summer of Code starts accepting
applications for students working on PostgreSQL projects on March
25th. Prepare those projects now!

== Postgres Product News ==

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

Registration for PGCon 2008 is open.
http://www.pgcon.org/2008/registration.php

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

phpPgAdmin 4.2-Beta-2 released. Please test!
http://phppgadmin.sourceforge.net/?page=download

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

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

== Postgres Jobs for March ==

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

== Postgres Local ==

LAPUG will be meeting March 28, 7:00pm in the City of Garden Grove
Training Room.
http://pugs.postgresql.org/lapug

PostgreSQL Conference East '08 talks are March 29 and 30 at the
University of Maryland, College Park. Registration closes March 26th.
http://www.postgresqlconference.org/

PG UK day will be April 2 in Birmingham.
http://www.postgresql.org.uk/

FISL 9.0 will be April 17-19 at PUCRS in Porto Alegre, RS, Brazil.
https://fisl.softwarelivre.org/9.0/

PGCon 2008 will be May 20-23 in Ottawa.
http://www.pgcon.org/2008/

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

== Postgres in the News ==

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

General Bits, Archives and occasional new articles:
http://www.varlena.com/GeneralBits/

Postgres Weekly News is brought to you this week by David Fetter and
Josh Berkus.

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:

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

== Rejected Patches (for now) ==

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

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