== Wöchentlicher PostgreSQL Newsletter - 13. September 2009 ==

Der Originalartikel befindet sich unter:
http://www.postgresql.org/community/weeklynews/pwn20090913

== Wöchentlicher PostgreSQL Newsletter - 13. September 2009 ==

Sicherheitsupdates für 8.4.1, 8.3.8, 8.2.14, 8.1.18, 8.0.22 und 7.4.26
sind erschienen. Schnell updaten!

== PostgreSQL Produkt Neuigkeiten ==

Bucardo 4.0.1, ein Replikationssystem mit Dual-Master Kapazitäten, ist
erschienen.
http://bucardo.org/wiki/Bucardo

== PostgreSQL Jobs im September ==

http://archives.postgresql.org/pgsql-jobs/2009-09/threads.php

== PostgreSQL Lokal ==

Es findet eine Konferenz in Seattle, Washington, USA am 16.-18.
Oktober 2009 statt.
http://www.postgresqlconference.org/2009/west

PGCon Brasilien wird am 23.-24. Oktober 2009 auf dem Unicamp in
Campinas, Sao Paulo, stattfinden. Der CfP ist eröffnet!
http://pgcon.postgresql.org.br/2009/

PGDay.EU 2009 wird an der Telecom ParisTech Universität in Paris,
Frankreich, am 6. und 7. November 2009 stattfinden.
http://www.pgday.eu/

OpenSQL Camp in Portland sucht Sponsoren. Bereite deine Reisepläne
jetzt vor! :)
http://www.chesnok.com/daily/2009/07/29/opensql-camp-comes-to-portland-november-14-15-2009/

Die 10. jährliche JPUG Konferenz findet am 20-21. November 2009 in
Tokio, Japan, statt.
http://archives.postgresql.org/pgsql-announce/2009-05/msg00018.php

FOSDEM 2010 wird am 6./7. Februar 2010 in Brüssel stattfinden.
http://www.fosdem.org/

Die Chemnitzer Linuxtage finden am 13. und 14. März 2010 in Chemnitz,
Deutschland statt.
http://chemnitzer.linuxtage.de/

== PostgreSQL in den News ==

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

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

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:

- Disallow RESET ROLE and RESET SESSION AUTHORIZATION inside
security-definer. functions. This extends the previous patch that
forbade SETting these variables inside security-definer functions.
RESET is equally a security hole, since it would allow regaining
privileges of the caller; furthermore it can trigger Assert failures
and perhaps other internal errors, since the code is not expecting
these variables to change in such contexts. The previous patch did
not cover this case because assign hooks don't really have enough
information, so move the responsibility for preventing this into
guc.c. Problem discovered by Heikki Linnakangas. Security: no CVE
assigned yet, extends CVE-2007-6600

- Make LOAD of an already-loaded library into a no-op, instead of
attempting to unload and re-load the library. The difficulty with
unloading a library is that we haven't defined safe protocols for
doing so. In particular, there's no safe mechanism for getting out
of a "hook" function pointer unless libraries are unloaded in
reverse order of loading. And there's no mechanism at all for
undefining a custom GUC variable, so GUC would be left with a
pointer to an old value that might or might not still be valid, and
very possibly wouldn't be in the same place anymore. While the
unload and reload behavior had some usefulness in easing development
of new loadable libraries, it's of no use whatever to normal users,
so just disabling it isn't giving up that much. Someday we might
care to expend the effort to develop safe unload protocols; but even
if we did, there'd be little certainty that every third-party
loadable module was following them, so some security restrictions
would still be needed. Back-patch to 8.2; before that, LOAD was
superuser-only anyway. Security: unprivileged users could crash
backend. CVE not assigned yet

- Remove outside-the-scanner references to "yyleng". It seems the
flex developers have decided to change yyleng from int to size_t.
This has already happened in the latest release of OS X, and will
start happening elsewhere once the next release of flex appears.
Rather than trying to divine how it's declared in any particular
build, let's just remove the one existing not-very-necessary
external usage. Back-patch to all supported branches; not so much
because users in the field are likely to care about building old
branches with cutting-edge flex, as to keep OSX-based buildfarm
members from having problems with old branches.

- Replace use of the long-deprecated Bonjour API
DNSServiceRegistrationCreate with the not-so-deprecated
DNSServiceRegister. This patch shouldn't change any user-visible
behavior, it just gets rid of a deprecation warning in
--with-bonjour builds. The new code will fail on OS X releases
before 10.3, but it seems unlikely that anyone will want to run
Postgres 8.5 on 10.2.

- Add a boolean GUC parameter "bonjour" to control whether a
Bonjour-enabled build actually attempts to advertise itself via
Bonjour. Formerly it always did so, which meant that packagers had
to decide for their users whether this behavior was wanted or not.
The default is "off" to be on the safe side, though this represents
a change in the default behavior of a Bonjour-enabled build. Per
discussion.

- Remove any -arch switches given in ExtUtils::Embed's ldopts from our
perl_embed_ldflags setting. On OS X it seems that ExtUtils::Embed
is trying to force a universal binary to be built, but you need to
specify that a lot further upstream if you want Postgres built that
way; the only result of including -arch in perl_embed_ldflags is
some warnings at the plperl.so link step. Per my complaint and Jan
Otto's suggestion.

- Fix bug with WITH RECURSIVE immediately inside WITH RECURSIVE. 99%
of the code was already okay with this, but the hack that obtained
the output column types of a recursive union in advance of doing
real parse analysis of the recursive union forgot to handle the case
where there was an inner WITH clause available to the non-recursive
term. Best fix seems to be to refactor so that we don't need the
"throwaway" parse analysis step at all. Instead, teach the
transformSetOperationStmt code to set up the CTE's output column
information after it's processed the non-recursive term normally.
Per report from David Fetter.

- Increase the maximum value of extra_float_digits to 3, and have
pg_dump use that value when the backend is new enough to allow it.
This responds to bug report from Keh-Cheng Chu pointing out that
although 2 extra digits should be sufficient to dump and restore
float8 exactly, it is possible to need 3 extra digits for float4
values.

- Fix assertion failure when a SELECT DISTINCT ON expression is
volatile. In this case we generate two PathKey references to the
expression (one for DISTINCT and one for ORDER BY) and they really
need to refer to the same EquivalenceClass. However
get_eclass_for_sort_expr was being overly paranoid and creating two
different Emmanuel Cecchet's. Correct behavior is to use the
SortGroupRef index to decide whether two references to volatile
expressions that are equal() (ie textually equivalent) should be
considered the same. Backpatch to 8.4. Possibly this should be
changed in 8.3 as well, but I'll refrain in the absence of evidence
of a visible failure in that branch. Per bug #5049.

- In pgsql/src/backend/commands/tablespace.c, install a
hopefully-temporary workaround for Snow Leopard readdir() bug. If
Apple doesn't fix that reasonably soon, we'll have to consider
back-patching a workaround; but for now, just hack it in HEAD so
that we can get buildfarm reports on HEAD from OS X machines. Per
Jan Otto.

- In pgsql/doc/src/sgml/ref/grant.sgml, improve GRANT reference page's
description of object ownership privileges by mentioning the
possibility of granting membership in the owning role.

- Rewrite the planner's handling of materialized plan types so that
there is an explicit model of rescan costs being different from
first-time costs. The costing of Material nodes in particular now
has some visible relationship to the actual runtime behavior, where
before it was essentially fantasy. This also fixes up a couple of
places where different materialized plan types were treated
differently for no very good reason (probably just oversights). A
couple of the regression tests are affected, because the planner now
chooses to put the other relation on the inside of a
nestloop-with-materialize. So far as I can see both changes are
sane, and the planner is now more consistently following the
expectation that it should prefer to materialize the smaller of two
relations. Per a recent discussion with Robert Haas.

- Write psql's ~/.psql_history file using history_truncate_file() and
append_history(), if libreadline is new enough to have those
functions (they seem to be present at least since 4.2; but libedit
may not have them). This gives significantly saner behavior when
two or more sessions overlap in their use of the history file;
although having two sessions exit at just the same time is still
perilous to your history. The behavior of \s remains unchanged, ie,
overwrite whatever was there. Per bug #5052 from Marek W?jtowicz.

Magnus Hagander committed:

- Change our WIN32 API version to be 5.01 (Windows XP), to bring in
the proper IPV6 headers in newer SDKs.

Peter Eisentraut committed:

- Fix/improve bytea and boolean support in PL/Python. Before,
PL/Python converted data between SQL and Python by going through a C
string representation. This broke for bytea in two ways: 1. On
input (function parameters), you would get a Python string that
contains bytea's particular external representation with backslashes
etc., instead of a sequence of bytes, which is what you would expect
in a Python environment. This problem is exacerbated by the new
bytea output format. 2. On output (function return value), null
bytes in the Python string would cause truncation before the data
gets stored into a bytea datum. This is now fixed by converting
directly between the PostgreSQL datum and the Python representation.
The required generalized infrastructure also allows for other
improvements in passing: 1. When returning a boolean value, the SQL
datum is now true if and only if Python considers the value that was
passed out of the PL/Python function to be true. Previously, this
determination was left to the boolean data type input function. So,
now returning 'foo' results in true, because Python considers it
true, rather than false because PostgreSQL considers it false. 2.
On input, we can convert the integer and float types directly to
their Python equivalents without having to go through an
intermediate string representation. Original patch by Caleb Welton,
with updates by myself.

- In pgsql/doc/src/sgml/dml.sgml, remove claim that this chapter
discusses rules and triggers. Per Bruno Guimaraes Carneiro.

- Remove PL/Python TODO file; it has been added to the main Todo list
in the wiki.

- Add Unicode support in PL/Python. PL/Python now accepts Unicode
objects where it previously only accepted string objects (for
example, as return value). Unicode objects are converted to the
PostgreSQL server encoding as necessary. This change is also
necessary for future Python 3 support, which treats all strings as
Unicode objects. Since this removes the error conditions that the
plpython_unicode test file tested for, the alternative result files
are no longer necessary.

- In pgsql/doc/src/sgml/config.sgml, document that
autovacuum_max_workers can only be set at server start. Per Joshua
Tolley.

- Fix Unicode support in PL/Python. Check calls of
PyUnicode_AsEncodedString() for NULL return, probably because the
encoding name is not known. Add special treatment for SQL_ASCII,
which Python definitely does not know. Since using SQL_ASCII
produces errors in the regression tests when non-ASCII characters
are involved, we have to put back various regression test result
variants.

Heikki Linnakangas committed:

- In pgsql/src/backend/access/transam/xlog.c, on Windows, when a file
is deleted and another process still has an open file handle on it,
the file goes into "pending deletion" state where it still shows up
in directory listing, but isn't accessible otherwise. That confuses
RemoveOldXLogFiles(), making it think that the file hasn't been
archived yet, while it actually was, and it was deleted along with
the .done file. Fix that by renaming the file with ".deleted"
extension before deleting it. Also check the return value of
rename() and unlink(), so that if the removal fails for any reason
(e.g another process is holding the file locked), we don't delete
the .done file until the WAL file is really gone. Backpatch to 8.2,
which is the oldest version supported on Windows.

- Don't error out if recycling or removing an old WAL segment fails at
the end of checkpoint. Although the checkpoint has been written to
WAL at that point already, so that all data is safe, and we'll retry
removing the WAL segment at the next checkpoint, if such a failure
persists we won't be able to remove any other old WAL segments
either and will eventually run out of disk space. It's better to
treat the failure as non-fatal, and move on to clean any other WAL
segment and continue with any other end-of-checkpoint cleanup. We
don't normally expect any such failures, but on Windows it can
happen with some anti-virus or backup software that lock files
without FILE_SHARE_DELETE flag. Also, the loop in pgrename() to
retry when the file is locked was broken. If a file is locked on
Windows, you get ERROR_SHARE_VIOLATION, not ERROR_ACCESS_DENIED, at
least on modern versions. Fix that, although I left the check for
ERROR_ACCESS_DENIED in there as well (presumably it was correct in
some environment), and added ERROR_LOCK_VIOLATION to be consistent
with similar checks in pgwin32_open(). Reduce the timeout on the
loop from 30s to 10s, on the grounds that since it's been broken,
we've effectively had a timeout of 0s and no-one has complained, so
a smaller timeout is actually closer to the old behavior. A longer
timeout would mean that if recycling a WAL file fails because it's
locked for some reason, InstallXLogFileSegment() will hold
ControlFileLock for longer, potentially blocking other backends, so
a long timeout isn't totally harmless. While we're at it, set errno
correctly in pgrename(). Backpatch to 8.2, which is the oldest
version supported on Windows. The xlog.c changes would make sense
on other platforms and thus on older versions as well, but since
there's no such locking issues on other platforms, it's not worth
it.

Tatsuo Ishii committed:

- In pgsql/contrib/pgbench/pgbench.c, pgbench has #defines for number
of branches, tellers, and accounts. There are used to populate the
tables with -i, but when running actual benchmark it has values
separately hard-coded in the query metacommands. This patch makes
the metacommands obtain their values from the relevant #defines.
Patch provided by Jeff Janes.

Alvaro Herrera committed:

- In pgsql/doc/src/sgml/config.sgml, add note that the logging
collector can block backends in high load situations.

Joe Conway committed:

- In pgsql/contrib/dblink/dblink.c, plug dblink resource leak. dblink
generates orphaned connections when called with a connection string,
fail_on_error = true, and an ERROR occurs. Discovery and patch by
Tatsuhito Kasahara. Introduced in 8.4.

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

ITAGAKI Takahiro sent in two revisions of a patch to implement CREATE
TABLE LIKE...INCLUDING (COMMENTS|STORAGE).

ITAGAKI Takahiro sent in two more revisions of the per-column trigger
patch.

Zoltan Boszormenyi sent in a small patch to fix a typo in an earlier
ECPG patch he sent.

Marko Kreen sent in another revision of the patch to add Unicode
escapes for strings in UTF8 databases.

Emmanuel Cecchet sent in a patch to log errors and do
auto-partitioning.

Heikki Linnakangas sent in another revision of the patch to fix
WAL issues in non-renamable files on Windows.

Josh Tolley sent in a doc patch to mention that autovacuum_max_workers
can only be set on server start.

Robert Haas sent in a proof-of-concept patch to add generic COPY
options.

Jeff Janes sent in a patch to improve XLogInsert.

Andrew Dunstan sent in a patch to allow for ragged CSV input in COPY.

Peter Eisentraut sent in a patch to make translating psql help easier.

ITAGAKI Takahiro sent in a patch to add a GUC parameter
syslog_line_prefix for syslog and eventlog.

Jeff Janes sent in a patch to improve tools/fsync.

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