PostgreSQL Weekly News – August 15 2010

== PostgreSQL Weekly News – August 15 2010 ==

== PostgreSQL Product News ==

Npgsql 2.0.10, a .NET data provider for PostgreSQL, released.
http://www.npgsql.org

== PostgreSQL Local ==

FrOSCon 2010 will take place in St. Augustin, Germany on August 21-22,
2010. The German PostgreSQL User Group got his own devroom and some
interesting talks. More info:
http://psoos.blogspot.com/2010/08/postgresql-at-froscon-2010.html

The Call for Papers for West is open until September 5, 2010. Details at:
http://www.postgresqlconference.org/

== PostgreSQL in the News ==

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

PostgreSQL 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@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org. Spanish language
to pwn@arpug.com.ar.

== Applied Patches ==

Tom Lane committed:

– Modify the handling of RAISE without parameters so that the error it
throws can be caught in the same places that could catch an ordinary
RAISE ERROR in the same location. The previous coding insisted on
throwing the error from the block containing the active exception
handler; which is arguably more surprising, and definitely unlike
Oracle’s behavior. Not back-patching, since this is a pretty
obscure corner case. The risk of breaking somebody’s code in a
minor version update seems to outweigh any possible benefit. Piyush
Newe, reviewed by David Fetter.

– Fix incorrect logic in plpgsql for cleanup after evaluation of
non-simple expressions. We need to deal with this when handling
subscripts in an array assignment, and also when catching an
exception. In an Assert-enabled build these omissions led to Assert
failures, but I think in a normal build the only consequence would
be short-term memory leakage; which may explain why this wasn’t
reported from the field long ago. Back-patch to all supported
versions. 7.4 doesn’t have exceptions, but otherwise these bugs go
all the way back. Heikki Linnakangas and Tom Lane.

– Add three-parameter forms of array_to_string and string_to_array, to
allow better handling of NULL elements within the arrays. The third
parameter is a string that should be used to represent a NULL
element, or should be translated into a NULL element, respectively.
If the third parameter is NULL it behaves the same as the
two-parameter form. There are two incompatible changes in the
behavior of the two-parameter form of string_to_array. First, it
will return an empty (zero-element) array rather than NULL when the
input string is of zero length. Second, if the field separator is
NULL, the function splits the string into individual characters,
rather than returning NULL as before. These two changes make this
form fully compatible with the behavior of the new three-parameter
form. Pavel Stehule, reviewed by Brendan Jurd.

– Remove the arbitrary (and undocumented) limit on the number of
parameter=value pairs that can be handled by xslt_process(). There
is much else to do here, but this patch seems useful in its own
right for as long as this code survives. Pavel Stehule, reviewed by
Mike Fowler.

– Extend psql’s \e and \ef commands so that a line number can be
specified, and the editor’s cursor will be initially placed on that
line. In \e the lines are counted with respect to the query buffer,
while in \ef they are counted with line 1 = first line of function
body. These choices are useful for positioning the cursor on the
line of a previously-reported error. To avoid assumptions about
what switch the user’s editor takes for this purpose, invent a new
psql variable EDITOR_LINENUMBER_SWITCH with (at present) no default
value. One incompatibility from previous behavior is that “\e 1234″
will now take “1234” as a line number not a file name. There are at
least two ways to select a numerically-named file if you really want
to. Pavel Stehule, reviewed by Jan Urbanski, with further editing
by Robert Haas and Tom Lane.

– Add the ability to compute per-statement latencies (ie, average
execution times) to pgbench. Florian Pflug, reviewed by Greg Smith.

– In pgsql/contrib/pgbench/pgbench.c, get rid of a bunch of dubious
error handling code in pgbench by just erroring out immediately on
any out-of-memory condition. It’s rather pointless to imagine that
pgbench will be able to continue usefully after a malloc failure,
and in any case there were a number of unchecked mallocs.

– Fix pg_restore to complain if any arguments remain after parsing the
switches and input file name, per bug #5617 from Leo Shklovskii.
Rearrange the corresponding code in pg_dump and pg_dumpall so that
all three programs handle this in a consistent, straightforward
fashion. Back-patch to 9.0, but no further. Although this is
certainly a bug, it’s possible that people have scripts that will be
broken by the added error check, so it seems better not to change
the behavior in stable branches.

– Fix Assert failure in PushOverrideSearchPath when trying to restore
a search path that specifies useTemp, but there is no active temp
schema in the current session. (This can happen if the path was
saved during a transaction that created a temp schema and was later
rolled back.) For existing callers it’s sufficient to ignore the
useTemp flag in this case, though we might later want to offer an
option to create a fresh temp schema. So far as I can tell this is
just an Assert failure: in a non-assert build, the code would push a
zero onto the new search path, which is useless but not very
harmful. Per bug report from Heikki. Back-patch to 8.3; prior
versions don’t have this code.

– Add xml_is_well_formed, xml_is_well_formed_document,
xml_is_well_formed_content functions to the core XML code. Per
discussion, the former depends on XMLOPTION while the others do not.
These supersede a version previously offered by contrib/xml2. Mike
Fowler, reviewed by Pavel Stehule.

– Minor #include cleanup. I just noticed that libpq’s pqsignal.h was
violating our general inclusion style guidelines by explicitly
including postgres_fe.h. Remove that, and put it in pqsignal.c
where it belongs.

– In pgsql/src/bin/psql/help.c, clean up failure to use ClosePager()
where appropriate. Also prevent possible repeat opening of pager in
helpSQL().

– In pgsql/src/include/storage/backendid.h, myBackendId now needs to
be PGDLLIMPORT, so that contrib modules can access it on Windows.
Per buildfarm.

– Add a \sf (show function) command to psql, for those times when you
need to look at a function but don’t wish to fire up an editor.
Pavel Stehule, reviewed by Jan Urbanski

– Fix \ef and \sf to not fail on functions with nonnull probin.
Update comment about them in pg_get_functiondef.

– In pgsql/src/backend/optimizer/util/clauses.c, fix planner to make a
reasonable assumption about the amount of memory space used by
array_agg(), string_agg(), and similar aggregate functions that use
“internal” as their transition datatype. The previous coding
thought this took *no* extra space, since “internal” is
pass-by-value; but actually these aggregates typically consume a
great deal of space. Per bug #5608 from Itagaki Takahiro, and fix
suggestion from Hitoshi Harada. Back-patch to 8.4, where array_agg
was introduced.

– In pgsql/doc/src/sgml/pgbench.sgml, avoid unnecessary use of TPS
acronym. Robert Haas, per gripe from Erik Rijkers.

– In pgsql/doc/src/sgml/pgarchivecleanup.sgml, improve
pgarchivecleanup documentation, per comments from Satoshi Nagayasu.

– In pgsql/doc/src/sgml/func.sgml, clarify bit numbering in
get_bit/set_bit etc. Per gripe from Zoltan Boszormenyi.

– In pgsql/doc/src/sgml/backup.sgml, assorted improvements to
backup/restore documentation, per Thom Brown.

– In pgsql/src/bin/psql/mbprint.c, fix psql’s copy of utf2ucs() to
match the backend’s copy exactly; in particular, propagate a fix in
the test to see whether a UTF8 character has length 4 bytes. This
is likely of little real-world consequence because 5-or-more-byte
UTF8 sequences are not supported by Postgres nor seen anywhere in
the wild, but still we may as well get it right. Problem found by
Joseph Adams. Bug is aboriginal, so back-patch all the way.

Robert Haas committed:

– In the libpq docs, provide stable target anchors for libpq
functions. Daniele Varrazzo.

– Use double quotes rather than double quotes for libpq target
anchors. Per observation from Tom Lane that the previous patch to
these files was not consistent with what is done elsewhere in the
docs.

– In pgsql/doc/src/sgml/ecpg.sgml, fix incorrect errno definitions in
ECPG manual. ecpgerrno.h hasn’t materially changed since PostgreSQL
7.4, so this has been wrong for a very long time. Back-patch all
the way. Satoshi Nagayasu.

– In pgsql/doc/src/sgml/ecpg.sgml, fix one more incorrect errno
definition in the ECPG manual. Again, back-patch all the way to
7.4.

– Correct sundry errors in Hot Standby-related comments. Fujii Masao.

– In pgsql/doc/src/sgml/syntax.sgml, reorder docs on lexical structure
slightly for clarity. Thom Brown.

– In pgsql/src/backend/access/transam/xact.c, make RecordTransactionCommit()
respect wal_level. Since the only purpose of WAL-logging
SharedInvalidationMessages is to support Hot Standby operation, they
needn’t be included when wal_level < hot_standby. Back-patch to
9.0. Review by Heikki Linnakanagas and Fujii Masao.

– Include the backend ID in the relpath of temporary relations. This
allows us to reliably remove all leftover temporary relation files
on cluster startup without reference to system catalogs or WAL;
therefore, we no longer include temporary relations in
XLOG_XACT_COMMIT and XLOG_XACT_ABORT WAL records. Since these
changes require including a backend ID in each SharedInvalSmgrMsg,
the size of the SharedInvalidationMessage.id field has been reduced
from two bytes to one, and the maximum number of connections has
been reduced from INT_MAX / 4 to 223-1. It would be possible to
remove these restrictions by increasing the size of
SharedInvalidationMessage by 4 bytes, but right now that doesn’t
seem like a good trade-off. Review by Jaime Casanova and Tom Lane.

– Fix assorted dtrace breakage caused by patch to include backend IDs
in temp relpaths. Per buildfarm.

– Further dtrace adjustments for the backend-IDs-in-relpath patch.
Update the documentation, and back out a few ill-considered changes
whose folly I failed to realize for failure to read the
documentation.

– Add doc link and additional index reference to pgcrypto. Kevin
Grittner, with markup adjustments.

– In pgsql/src/backend/storage/lmgr/lmgr.c, make LockDatabaseObject()
AcceptInvalidationMessages(). This is appropriate for the same
reasons we already do it in LockSharedObject(): things might have
changed while we were waiting for the lock. There doesn’t seem to
be a live bug here at the moment, but that’s mostly because it isn’t
currently used for very much.

Bruce Momjian committed:

– In pgsql/doc/src/sgml/ref/explain.sgml, add EXPLAIN documentation
example. Gabrielle Roth. Backpatch to 9.0.X.

– In pgsql/doc/src/sgml/syntax.sgml, properly lowercase identifiers,
uppercase keywords, in doc examples.

– In pgsql/doc/src/sgml/syntax.sgml, back out syntax case changes —
seems they were intentional.

Peter Eisentraut committed:

– In pgsql/doc/src/sgml/datatype.sgml, <example> is a floating
element, so it’s use is inappropriate when the surrounding text
refers to the example inline.

– Add some links to tables in the docs.

Heikki Linnakangas committed:

– In pgsql/src/backend/utils/adt/arrayfuncs.c, the sanity check added
to array_recv() wa a bit too tight; we must continue to accept an
empty array with dimension information. array_send() can output
such arrays. Per report from Vladimir Shakhov.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Erik Rijkers sent in a doc patch for pgbench defining TPS to mean
“transactions per second.”

Kris Jurka sent in another revision of the patch to fix COPY IN.

Peter Eisentraut sent in a patch to allow specifying hosts by name in
pg_hba.conf.

Pavel Stehule sent in another revision of the GROUPING SETS patch.

Robert Haas sent in a patch to reduce the size of the id for
SharedInvalidationMessages to one byte.

Boxuan Zhai sent in four more revisions of the MERGE patch, and Heikki
Linnakangas responded with a cleaned-up one.

Joseph Adams sent in two more WIP patches to add the JSON type, the
second adding it as a core type rather than the previous contrib.

Fujii Masao sent in a patch to align the docs and code for
trace_recovery_messages.

Greg Smith sent in another revision of the patch to add individual
statement latencies to pgbench.

Joseph Adams sent in a patch to fix utf8-to-unicode.

Joseph Adams sent in some general utility functions for, among other
things, enum handling in C stored procedures.

Zoltan Boszormenyi sent in a WIP patch to add replication to the SQL
syntax.

Dean Rasheed sent in another WIP patch to implement triggers on views.

Robert Haas sent in another revision of the patch to refactor
comment.c.

Comments are closed.