== PostgreSQL Weekly News - November 02 2008 ==

== PostgreSQL Weekly News - November 02 2008 ==

US Citizens: don't forget to vote by Tuesday. Democracy is use-it-or-lose-it.

The final CommitFest for 8.4 began yesterday. We have over 50 pending
patches, including SEPostgres, column-level permissions, Hot Standby,
Windowing Functions, SQL/MED, and more. We need reviewers! If
there's a patch you care about, try it out soon and report your
results to pgsql-hackers. If you don't know what to review but have
time, join pgsql-rrreviewers, speak up, and we'll assign you
something.

Patches which get sent back from this CommitFest will not be in
version 8.4, and will have to wait until next year. So don't let that
be from lack of review and testing.

CommitFest
http://wiki.postgresql.org/wiki/CommitFest_2008-11
How to Review
http://wiki.postgresql.org/wiki/Reviewing_a_Patch
http://wiki.postgresql.org/wiki/RRReviewers
Lists
http://archives.postgresql.org/pgsql-hackers/
http://archives.postgresql.org/pgsql-rrreviewers/

Replicator developer meeting logs:
https://projects.commandprompt.com/public/replicator/wiki/developermeetinglogs

Latest Ubuntu UK podcast has an interview with Simon Riggs at LinuxLive
in London. The Postgres interview starts about 11 minutes in.
http://podcast.ubuntu-uk.org/2008/10/29/s01e17-talk-about-the-good-times/

== PostgreSQL Product News ==

ora2pg 4.9 released.
http://pgfoundry.org/projects/ora2pg/

== PostgreSQL Jobs for November ==

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

== PostgreSQL Local ==

PgDay Rio de la Plata is looking for sponsors. Interested parties
should contact admin AT postgres-arg DOT org.

Dickson Guedes is looking for volunteers to help with a PgMeeting in
Florianópolis. Write to guediz AT gmail DOT com if you want to help.

David Fetter and Robert Treat will be speaking at the Beijing Perl
Workshop on November 8.
http://conference.perlchina.org/bjpw2008/schedule

David Fetter will be speaking at PGDay Rio de la Plata Buenos Aires
November 22.
http://pgday.postgres-arg.org/

David Fetter will be speaking at OSDC Sydney December 3-5.
http://www.osdc.com.au/2008/index.html

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

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

Peter Eisentraut committed:

- In pgsql/src/backend/catalog/sql_features.txt, feature list update.

- Allow EXPLAIN on CREATE TABLE AS.

- SQL:2008 syntax CURRENT_CATALOG, CURRENT_SCHEMA, SET CATALOG, SET
SCHEMA.

- Add WITH [NO] DATA clause to CREATE TABLE AS, per SQL. Also, since
WITH is now a reserved word, simplify the token merging code to only
deal with WITH_TIME. by Tom Lane and myself

- Unicode escapes in strings and identifiers

- Use Autoconf provided AS_HELP_STRING macro to automatically format
and align strings in the --help output. Do this through our
abstraction layer to eliminate redundancy and randomness in
configure.in.

- Since SQL:2003, the array size specification in the SQL ARRAY syntax
has been optional.

- In pgsql/src/backend/catalog/sql_features.txt, update on array
features support.

- Support for Sun Studio compiler on Linux. This basically takes some
build system code that was previously labeled "Solaris" and ties it
to the compiler rather than the operating system. Julius Stroffek.

- Move forgotten comment closer to where it matters.

- In pgsql/src/bin/initdb/initdb.c, improve new message.

- Missing space in error message in psql's configure script.

- Translation updates.

- In pgsql/src/bin/initdb/initdb.c, message improvement (also
backported to 8.3).

- In pgsql/doc/src/sgml/Makefile, the conversion rule from
postgres.sgml to postgres.xml didn't work with BSD sed. So write it
in Perl, which is more portable and a bit faster, too. We already
use Perl for standard documentation builds, so this imposes no
additional requirement.

Magnus Hagander committed:

- Update standalong libpq makefiles for msvc and bcc to work with the
new libpq events code. Hiroshi Saito.

- Add support for multiple error messages from libpq, by simply
appending them after each other (since we already add a newline on
each, this makes them multiline). Previously a new error would just
overwrite the old one, so for example any error caused when trying
to connect with SSL enabled would be overwritten by the error
message form the non-SSL connection when using sslmode=prefer.

- In pgsql/src/backend/libpq/hba.c, make hba parsing error messages
more specific.

- Remove support for (insecure) crypt authentication. This breaks
compatibility with pre-7.2 versions.

- In pgsql/src/backend/port/win32_shmem.c, revert previous patch to
put the shared memory segment on win32 in the Global\ namespace,
because it caused permission errors on a lot of platforms. We need
to come up with something better for 8.4, but for now revert to the
pre-8.3.4 behaviour.

Tom Lane committed:

- Install a more robust solution for the problem of infinite
error-processing recursion when we are unable to convert a localized
error message to the client's encoding. We've been over this ground
before, but as reported by Ibrar Ahmed, it still didn't work in the
case of conversion failures for the conversion-failure message
itself :-(. Fix by installing a "circuit breaker" that disables
attempts to localize this message once we get into recursion
trouble. Patch all supported branches, because it is in fact broken
in all of them; though I had to add some missing translations to the
older branches in order to expose the failure in the particular test
case I was using.

- Arrange to squeeze out the MINIMAL_TUPLE_PADDING in the tuple
representation written to temp files by tuplesort.c and
tuplestore.c. This saves 2 bytes per row for 32-bit machines, and 6
bytes per row for 64-bit machines, which seems worth the slight
additional uglification of the tuple read/write routines.

- Change WorkTableScan to not support backward scan. The apparent
support didn't actually work, because nodeRecursiveunion.c creates
the underlying tuplestore with backward scan disabled; which is a
decision that we shouldn't reverse because of performance cost. We
could imagine adding signaling from WorkTableScan to RecursiveUnion
about whether backward scan is needed ... but in practice it'd be a
waste of effort, because there simply isn't any current or plausible
future scenario where WorkTableScan would be called on to scan
backward. So just dike out the code that claims to support it.

- Extend ExecMakeFunctionResult() to support set-returning functions
that return via a tuplestore instead of value-per-call. Refactor a
few things to reduce ensuing code duplication with
nodeFunctionscan.c. This represents the reasonably noncontroversial
part of my proposed patch to switch SQL functions over to returning
tuplestores. For the moment, SQL functions still do things the old
way. However, this change enables PL SRFs to be called in
targetlists (observe changes in plperl regression results).

- Be more tense about not creating tuplestores with randomAccess =
true unless backwards scan could actually happen. In particular,
pass a flag to materialize-mode SRFs that tells them whether they
need to require random access. In passing, also suppress unneeded
backward-scan overhead for a Portal's holdStore tuplestore. Per my
proposal about reducing I/O costs for tuplestores.

- In pgsql/src/backend/access/transam/xlog.c, fix recoveryLastXTime
logic so that it actually does what one would expect. Per gripe
from Kevin Grittner. Backpatch to 8.3, where the bug was
introduced.

- Update time zone data files to tzdata release 2008i (DST law changes
in Argentina, Brazil, Mauritius, Syria).

- In pgsql/doc/src/sgml/release.sgml, update back-branch release
notes.

- Allow SQL-language functions to return the output of an
INSERT/UPDATE/DELETE RETURNING clause, not just a SELECT as
formerly. A side effect of this patch is that when a set-returning
SQL function is used in a FROM clause, performance is improved
because the output is collected into a tuplestore within the
function, rather than using the less efficient value-per-call
mechanism.

- Simplify ExecutorRun's API and save some trivial number of cycles by
having it just return void instead of sometimes returning a
TupleTableSlot. SQL functions don't need that anymore, and noplace
else does either. Eliminating the return value also means one less
hassle for the ExecutorRun hook functions that will be supported
beginning in 8.4.

- Remove all uses of the deprecated functions heap_formtuple,
heap_modifytuple, and heap_deformtuple in favor of the newer
functions heap_form_tuple et al (which do the same things but use
bool control flags instead of arbitrary char values). Eliminate the
former duplicate coding of these functions, reducing the deprecated
functions to mere wrappers around the newer ones. We can't get rid
of them entirely because add-on modules probably still contain many
instances of the old coding style. Kris Jurka.

- Remove the last vestiges of the MAKE_PTR/MAKE_OFFSET mechanism. We
haven't allowed different processes to have different addresses for
the shmem segment in quite a long time, but there were still a few
places left that used the old coding convention. Clean them up to
reduce confusion and improve the compiler's ability to detect
pointer type mismatches. Kris Jurka.

- Change the pgstat logic so that the stats collector writes the stats
file only upon requests from backends, rather than on a fixed
500msec cycle. (There's still throttling logic to ensure it writes
no more often than once per 500msec, though.) This should result in
a significant reduction in stats file write traffic in typical
scenarios where the stats are demanded only infrequently. This
approach also means that the former difficulty with changing
stats_temp_directory on-the-fly has gone away, so remove the caution
about that as well as the thrashing we did to minimize the trouble
window. In passing, also fix pgstat_report_stat() so that we will
send a stats message if we have function call stats but not table
stats to report; this fixes a bug in the recent patch to support
function-call stats. Martin Pihlak.

Alvaro Herrera committed:

- In pgsql/src/backend/access/heap/heapam.c, no need for extra code to
log freezing zero tuples. Callers already check that they are
freezing a nonzero amount anyway.

- In pgsql/src/backend/utils/time/snapmgr.c, downgrade can't-happen
error reports to elog().

Bruce Momjian committed:

- In pgsql/doc/src/sgml/syntax.sgml, remove tab from sgml file.

Marc Fournier committed:

- Tag 8.3.5, 8.2.11, 8.1.15, 8.0.19, 7.4.23.

Heikki Linnakangas committed:

- Add support for user-defined I/O conversion casts.

- In pgsql/src/test/regress/parallel_schedule, add test case for
CREATE CAST.

- Unite ReadBufferWithFork, ReadBufferWithStrategy, and
ZeroOrReadBuffer functions into one ReadBufferExtended function,
that takes the strategy and mode as argument. There's three modes,
RBM_NORMAL which is the default used by plain ReadBuffer(),
RBM_ZERO, which replaces ZeroOrReadBuffer, and a new mode
RBM_ZERO_ON_ERROR, which allows callers to read corrupt pages
without throwing an error. The FSM needs the new mode to recover
from corrupt pages, which could happend if we crash after extending
an FSM file, and the new page is "torn". Add fork number to some
error messages in bufmgr.c, that still lacked it.

- Update FSM on WAL replay. This is a bit limited; the FSM is only
updated on non-full-page-image WAL records, and quite arbitrarily,
only if there's less than 20% free space on the page after the
insert/update (not on HOT updates, though). The 20% cutoff should
avoid most of the overhead, when replaying a bulk insertion, for
example, while ensuring that pages that are full are marked as full
in the FSM. This is mostly to avoid the nasty worst case scenario,
where you replay from a PITR archive, and the FSM information in the
base backup is really out of date. If there was a lot of pages that
the outdated FSM claims to have free space, but don't actually have
any, the first unlucky inserter after the recovery would traverse
through all those pages, just to find out that they're full. We
didn't have this problem with the old FSM implementation, because we
simply threw the FSM information away on a non-clean shutdown.

Michael Meskes committed:

- In pgsql/src/backend/parser/gram.y, added missing ';'

- In ecpg, do not eat memory even in case of an out-of-memory error.

- In ecpg, use string component in index structure.

- In pgsql/src/interfaces/ecpg/ecpglib/misc.c, move from strcmp to
strncmp to be more tolerant for changes to the parser.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

ITAGAKI Takahiro sent in another revision of his
contrib/pg_stat_statements module.

Heikki Linnakangas sent in a WIP patch for dead space map.

Hitoshi Harada sent in another revision of his OLAP and Windowing
Functions patch.

Simon Riggs sent in two more revisions of his Hot Standby patch.

Koichi Suzuki sent in a patch to improve PITR performance.

Kris Jurka sent in two revisions of a patch which changes everything
to use the new API for heaptuples.

Simon Riggs sent in a patch to the subcommit infrastructure which uses
the InRecovery flag to keep an Assert in a smaller set of cases.

Emanuel CALVO FRANCO sent in a Spanish version of the Solaris FAQ.

Magnus Hagander sent in a patch to implement regexp support in the
usermaps (pg_ident.conf).

Kris Jurka sent in a patch to make shared memory pointers just like
regular pointers.

Martin Pihlak and ITAGAKI Takahiro sent in three revisions of a patch
for pg_stat_statements.

Alvaro Herrera sent in a patch to implement block-level CRC checks.

Ron Mayer sent in three revisions of a patch to let PostgreSQL output
SQL standard intervals.

Stephen Frost sent in three revisions of a patch to attach
column-level permissions.

Teodor Sigaev sent in another revision of his fast-insertion patch for
GIN.

Jonah Harris sent in a patch to use Bloom filters to make hash joins
faster.

Hiroshi Saito sent in a patch to fix LC_TIME on Windows.

Simon Riggs sent in a patch to implement Hot Standby.

Hannu Krossing sent in two revisions of a patch to enable PL/PythonU
to returns records based on multiple OUT parameters.

Zdenek Kotala sent in a patch to implement in-place upgrade.

Teodor Sigaev sent in a patch to emulate B-Trees in GIN.

Hitoshi Harada sent in another revision of his Window Functions patch.

Peter Eisentraut sent in two revisions of a patch to implement SQL
standard "distinct types."

Fujii Masao sent in a patch to implement synchronous log-shipping
replication.

Bernd Helmle sent in a WIP patch for automatic VIEW updates.

Gregory Stark sent in an update version of the posx fadvise patch.

Zdenek Kotala sent in a patch which adds version information to
heaptuples.

Heikki Linnakangas sent in a patch to improve COPY FROM performance by
replacing CopyReadLineText with memchr().

David Fetter sent in two revisions of a pre-MED patch to expose WHERE
clauses to functions.

Andrew Dunstan sent in another WIP patch for parallel restore.

Zdenek Kotala sent in two revisions of a patch to clean up htup.h and
bufpage.[ch].

Martin Pihlak sent in two revisions of a patch to add an
SQL/MED-compatible connection manager.

Jeff Davis sent in another revision of his array_accum() patch.

Robert Haas sent in two revisions of his patch to improve
BufferAccessStrategy for bulk inserts.

Nikhil Sontakke sent in another revision of his auto-partitioning
patch.

Gianni Ciolli sent in a patch to implement bitmap indexes.

Andrew Dunstan sent in two revisions of a patch to turn no-op UPDATEs
into actual no-ops.

Vladimir Sitnikov sent in a patch to add buffer pool statistics to
EXPLAIN ANALYZE's output.

KaiGai Kohei sent in another revision of his SE-PostgreSQL patches.