Skip to content

Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007


Der Originalartikel befindet sich unter:
http://people.planetpostgresql.org/dfetter/

== Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 ==

Der PostgreSQL Fond bei SPI sponsorte diesen Monat David Fetters Reise zur Consili in Brasilien, wo er eine Konferenz Keynote überbrachte und Neil Conways nächste zwei Monate der Patch Review Arbeiten für PostgreSQL 8.3.

Eine lebhafte Diskussion über Sourcecode Management ist auf -hackers in gange.

Das Seventh Framework Programm (FP7) ist offen für Vorschläge. EU PostgreSQL Organisationen, erwägt das Einsenden von Vorschlägen.
http://cordis.europa.eu/fp7/home_en.html

Du kannst nun search-postgresql.org mit einem Plugin für die Firefox Suche nutzen-
http://www.gunduz.org/postgresql/searchpostgresqlorg.html

== PostgreSQL Produkt Neuigkeiten ==

Cybertek in Österreich hat eine synchrone Multi-Master Replikation als Produkt für PostgreSQL vorgestellt.
http://www.cybertec.at/en/ctools.html

PGCluster-1.5.0rc15 und 1.7.0rc4 erschienen.
http://pgfoundry.org/projects/pgcluster/

== PostgreSQL Jobs im Februar ==

http://archives.postgresql.org/pgsql-jobs/2007-02/threads.php

== PostgreSQL Lokal ==

Es wird einen PostgreSQL Stand auf dem CLT am 3.-4. März in Chemnitz/Deutschland geben. Einige der üblichen Verdächtigen der deutschsprachigen PostgreSQl Community werden dort sein. Kontaktiere info@pgug.de für mehr Informationen.
http://chemnitzer.linux-tage.de/2007/info/

Die italienische PostgreSQL Community ist auf der Suche nach Sponsoren für ihren PostgreSQL Tag in Prato, Italien im Sommer. Mehr Informationen unter untenstehendem Link.
http://www.pgday.it

== PostgreSQL in den News ==

Microsoft Technet veröffentlicht ein PostgreSQL HOWTO:
http://port25.technet.com/archive/2007/02/22/postgresql-on-windows-a-primer.aspx

OmniTI Fallstudien veröffentlicht:
http://www.arnnet.com.au/index.php/id;413111662;fp;4194304;fpid;1

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,
Josh Berkus und Devrim GUNDUZ.

Um deine Beiträge für die nächste Ausgabe zu übermitteln, sende diese an david@fetter.org bis Sonntag, 15:00 Uhr Pazifischer Zeit.

== Angewandte Patches ==

Andrew Dunstan committed:

- Allow pltcl args to spi_prepare and plpython args to plpy.prepare to
be standard type aliases as well as those known in pg_type. Similar
to recent change in plperl.

Peter Eisentraut committed:

- Bernd Helmle's patch which identifies the schema of inherited tables
in psql \d when necessary.

- Add missing OIDs from xml support to pg_proc, bump catversion.

Bruce Momjian committed:

- Move test for BLCKSZ < 1024 to guc.c.

- Spelling fix in Solaris FAQ.

- Update Solaris FAQ per Peter Eisentraut.

- Updated FAQ on upgrading.

- Zdenek Kotala's patch to the Solaris FAQ.

- Chad Wagner's patch to psql which adds \prompt capability.

- Remove extra tab from pgsql/doc/src/sgml/ref/psql-ref.sgml.

- Remove tabs from SGML reference files so their addition can be
detected in the future.

- Update message wording in FAQ.

- Update new optional VACUUM FULL hint for translations, per Alvaro
Herrera.

- Simon Riggs's patch which moves increase FSM warning to after
lazy_truncate_heap() because the function might reduce the number of
free pages in the table. Recommend VACUUM FULL only if 20% free.

- Heikki Linnakangas's patch to clean up the btree source code.

- Update FAQ about minor updates.

- Jun Kuwamura's update to the Japanese FAQ.

- Update URL in German FAQ for bug form, per Schima, Fabian

- Daojing Zhou's update of the Chinese FAQs to have two versions, a
traditional Chinese version (Taiwan) and a Simplified version (China
(PRC)). Backpatch to 8.2.X.

- Update minor release text in FAQ.

- Add configure --enable-profiling to enable GCC profiling. Patches
from Korry Douglas and Nikhil S

- Update upgrade text in FAQ.

- Update URL for set-returning functions in FAQ.

- Add URL for "Allow row and record variables to be set to NULL
constants, and allow NULL tests on such variables" in the TODO list.

- Prevent BLCKSZ < 1024, and have initdb test shared buffers based on
the BLCKSZ value.

- Add to TODO: "Consider decreasing the amount of memory used by
PrivateRefCount."

- Fix markup in pgsql/doc/src/sgml/information_schema.sgml.

- Change $(CC) to $(COMPILER) on Solaris gcc so -m64 is passed into
the shared link line.

- Add to TODO: "Increase locking when DROPing objects so dependent
objects cannot get dropped while the DROP operation is happening."

- Add URL for "Allow UPDATE/DELETE WHERE CURRENT OF cursor" in TODO
list.

- Add to TODO: "Add missing operators for geometric data types. Some
geometric types do not have the full suite of geometric operators,
e.g. box @> point."

- Update "encode" documentation to mention that 'escape' only changes
null bytes and backslashes, remove "ASCII" mention. Backpatch to
8.2.X.

- Update pgpass Win32 wording.

- Update information_schema documentation to match system tables.
Backpatch to 8.2.X.

- Improve wording on Julian dates in pgsql/doc/src/sgml/func.sgml.

- More clearly document that most PostgreSQL utilities support libpq
environment variables. Backpatch to 8.2.X.

- Remove from TODO for Win32: "Check .pgpass file permissions." It is
not needed.

- In pgsql/src/interfaces/libpq/fe-connect.c, add comment that on
Win32, we don't need to check the .pgpass file permission, per
Magnus Hagander.

- Add to TODO for Win32: "Check .pgpass file permissions."

- Update array slice documentation to be clearer.

- Add to TODO: "Fix IS OF so it matches the ISO specification, and add
documentation."

- Comment-out documentation for IS OF because it doesn't conform to
the ISO SQL behavior. Backpatch removal to 8.2.X.

- Remove TODO item: "ARRAY[[1,2],[3,4]])[1] should return the same
values as ARRAY[[1,2],[3,4]])[1:1]." It actually shouldn't.

- Add text about Makefile.custom to FAQ_DEV.

- Document that to_char('J'/Julian) is midnight-based, per report that
Julian technically is noon to noon.

- Improve wording in isodow documentation.

- Update PQfree() documentation to be clearer, backpatch to 8.2.X.

- Add to TODO: "Allow user configuration of TOAST thresholds."

- Add newlines to TODO.

- Add to TODO: "Allow UPDATEs on only non-referential integrity
columns not to conflict with referential integrity locks."

- Add to TODO: "Allow INSERT/UPDATE ... RETURNING inside a SELECT
'FROM' clause."

- Clarify documentation that initdb -A or editing pg_hba.conf is
required if you do not trust local users.

- Clarify documentation for "day of the week" handling for to_char()
and EXTRACT().

- Mark TODO as done: "Add ISO day of week format 'ID' to to_char()
where Monday = 1."

- Add "isodow" option to EXTRACT() and date_part() where Sunday = 7.

- Mark TODO as done: "Add a field 'isoyear' to extract(), based on the
ISO week."

- Mark TODO as done: "Add long file support for binary pg_dump
output."

Magnus Hagander committed:

- In pgsql/src/tools/msvc/Solution.pm, revert changes to process
pg_proc.h entries without OIDs. We're not supposed to have such
entries, and want to be notified when we do... Leave the plain
bug fix in genbki.

- Fix pg_dump on Win32 so that it properly dumps files larger than 2GB
when using binary dump formats.

- Parse pg_proc.h with entries without OIDs for the MSVC build.

Tom Lane committed:

- Update 7.x variant horology files to match the new US DST rules. It
seems likely that anyone wanting to run the regression tests in the
future will have up-to-date system timezone files, so this is more
likely to work than the old contents.

- Put back copyObject() call I removed in a fit of brain fade. This
one is still needed despite cleanups in setrefs.c, because the point
is to let the inserted Result node compute a different tlist than
its input node does. Per example from Jeremy Drake.

- Now that plans have flat rangetable lists, it's a lot easier to get
EXPLAIN to drill down into subplan targetlists to print the referent
expression for an OUTER or INNER var in an upper plan node. Hence,
make it do that always, and banish the old hack of showing
"?columnN?" when things got too complicated. Along the way, fix an
EXPLAIN bug I introduced by suppressing subqueries from
execution-time range tables: get_name_for_var_field() assumed it
could look at rte->subquery to find out the real type of a RECORD
var. That doesn't work anymore, but instead we can look at the
input plan of the SubqueryScan plan node.

- Change Agg and Group nodes so that Vars contained in their
targetlists and quals have varno OUTER, rather than zero, to
indicate a reference to an output of their lefttree subplan. This
is consistent with the way that every other upper-level node type
does it, and allows some simplifications in setrefs.c and EXPLAIN.

- Fix bug I introduced in recent patch to make hash joins discard null
tuples immediately: ExecHashGetHashValue failed to restore the
caller's memory context before taking the failure exit.

- Turn the rangetable used by the executor into a flat list, and avoid
storing useless substructure for its RangeTblEntry nodes. (I chose
to keep using the same struct node type and just zero out the link
fields for unneeded info, rather than making a separate
ExecRangeTblEntry type --- it seemed too fragile to have two
different rangetable representations.) Along the way, put subplans
into a list in the toplevel PlannedStmt node, and have SubPlan nodes
refer to them by list index instead of direct pointers. Vadim
wanted to do that years ago, but I never understood what he was on
about until now. It makes things a whole lot more robust, because
we can stop worrying about duplicate processing of subplans during
expression tree traversals. That's been a constant source of bugs,
and it's finally gone. There are some consequent simplifications
yet to be made, like not using a separate EState for subplans in the
executor, but I'll tackle that later.

- Adjust user-facing documentation to explain why we don't check
pgpass file permissions on Windows.

- Remove the Query structure from the executor's API. This allows us
to stop storing mostly-redundant Query trees in prepared statements,
portals, etc. To replace Query, a new node type called PlannedStmt
is inserted by the planner at the top of a completed plan tree; this
carries just the fields of Query that are still needed at runtime.
The statement lists kept in portals etc. now consist of intermixed
PlannedStmt and bare utility-statement nodes --- no Query. This
incidentally allows us to remove some fields from Query and Plan
nodes that shouldn't have been there in the first place. Still to
do: simplify the execution-time range table; at the moment the range
table passed to the executor still contains Query trees for
subqueries. initdb forced due to change of stored rules.

- Get rid of some old and crufty global variables in the planner.
When this code was last gone over, there wasn't really any
alternative to globals because we didn't have the PlannerInfo struct
being passed all through the planner code. Now that we do, we can
restructure things to avoid non-reentrancy. I'm fooling with this
because otherwise I'd have had to add another global variable for
the planned compact range table list.

== Abgelehnte Patches (bis jetzt) ==

Mateo Beccati's patch which gets PostgreSQL to compile on Irix 6.5,
but the patch would break most Linux machines.

== Eingesandte Patches ==

Zoltan Boszormenyi sent in two more iterations of his
IDENTITY/GENERATED patch.

Pavan Deolasee sent in two more versions of his work-in-progress HOT
patch.

Gregory Stark sent in a patch to shorten varlena headers.

Guillaume Smet sent in a first implementation of GIN for pg_trgm.

Nikolay Samokhvalov sent in a patch to implement xpath_array with
namespaces support.

Darcy Buskermolen sent in a patch which provides for logging in the
event that -k is unable to clean up an old WAL file and makes the
"failed to remove file" error message consistent for the trigger file.

Greg Sabino Mullane sent in a documentation patch which warns about
some strange behavior in LISTEN/NOTIFY.

Pavel Stehule sent in a patch to add timestamp support for XSD-type
timestamps.

Simon Riggs sent in another version of his patch to avoid deadlocks in
pg_dump.

Joachim Wieland sent in a patch which makes GUC values fall back to
their default values when they got removed (or commented) from the
configuration file.

Kris Jurka sent in a patch which implements lo_truncate for truncating
large objects to a given length. This is required for implementing
Blob.truncate in the JDBC driver and rounds out filesystem like
functionality for large objects.

Simon Riggs sent a bug fix for his recent optimization of
COPY-after-truncate.
  • Twitter
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007
  • Bloglines Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007
  • Technorati Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007
  • Fark this: Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 at YahooMyWeb
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 at Furl.net
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 at reddit.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 at blinklist.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 at Spurl.net
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 at Simpy.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 at blogmarks
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 25. Februar 2007 with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007


Der Originalartikel befindet sich unter:
http://people.planetpostgresql.org/dfetter/

== Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 ==

Version 3.4 des Open Source SYMBOL Australian Accountingprogramms enthält erweiterte Skalierbarkeit für PostgreSQL.
http://www.pcworld.idg.com.au/index.php/id;1504289875

IGN, das Französische Nationale Geografie Institut, hat PostgreSQL und PostGIS aufgrund der Skalierbarkeit, der Geschwindugkeit und der Transaktionssicherheit gewählt.
http://postgis.refractions.net/documentation/casestudies/ign/

== PostgreSQL Produkt Neuigkeiten ==

pgmemcache 1.1 erschienen. Fortlaufende Entwicklung wird von der Opten Technology Group, Inc. gesponsert.
http://pgfoundry.org/projects/pgmemcache/

pgchem::tigress 7.0 erschienen.
http://pgfoundry.org/projects/pgchem/

pgpool-II 1.0.2 erschienen.
http://pgfoundry.org/projects/pgpool/

PL/PgPSM 0.2.0 erschienen, jetzt mit voller SQL/PSM Unterstützung. Es ist wie jedes andere contrib Paket mit PGXS installierbar.
http://pgfoundry.org/projects/plpsm/

== PostgreSQL Jobs im Februar ==

http://archives.postgresql.org/pgsql-jobs/2007-02/threads.php

== PostgreSQL Lokal ==

Es wird einen PostgreSQL Stand auf dem CLT am 3.-4. März in Chemnitz/Deutschland geben. Einige der üblichen Verdächtigen der deutschsprachigen PostgreSQl Community werden dort sein. Kontaktiere info@pgug.de für mehr Informationen.
http://chemnitzer.linux-tage.de/2007/info/

Es wird einen PostgreSQL Stand auf der FOSDEM am 24. und 25. Februar in Brüssel, Belgien sein. Viele der üblichen Verdächtigen von den EU PostgreSQL Communities werden dort sein. Kontaktiere de@postgresql.org für mehr Informationen.
http://www.fosdem.org/2007/

Die italienische PostgreSQL Community ist auf der Suche nach Sponsoren für ihren PostgreSQL Tag in Prato, Italien im Sommer. Mehr Informationen unter untenstehendem Link.
http://www.pgday.it

== 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, Jean-Paul Argudo and Josh Berkus.

Um deine Beiträge für die nächste Ausgabe zu übermitteln, sende diese an david@fetter.org bis Sonntag, 15:00 Uhr Pazifischer Zeit.

== Angewandte Patches ==

Alvaro Herrera committed:

- Install a more correct fix in the timestamp and timestamptz
regression tests: remove duplicated tests in timestamp, and complete
timestamptz with the tests that were missing to more closely mirror
timestamp.

- Fix the timestamptz test problem, by moving the tests that use the
timestamp_tbl table into the timestamp test. Also, restore a test
that used to exist as a valid test in the timestamptz test.

- Remove useless database name from bootstrap argument processing
(including startup and bgwriter processes), and the -y flag. It's
not used anywhere.

- Restructure autovacuum in two processes: a dummy process, which runs
continuously, and requests vacuum runs of "autovacuum workers" to
postmaster. The workers do the actual vacuum work. This allows for
future improvements, like allowing multiple autovacuum jobs running
in parallel. For now, the code keeps the original behavior of
having a single autovac process at any time by sleeping until the
previous worker has finished.

Peter Eisentraut committed:

- Better fix for determining minimum and maximum int64 values that
doesn't require stdint.h and works for "busted" int64.

- Remove naughty // comment from pgsql/src/backend/utils/adt/formatting.c

- Add stdint.h header to pgsql/src/backend/utils/adt/xml.c

- Add functions for mapping table data and table schemas to XML
(a.k.a. XML export)

- Fix typo in pgsql/doc/src/sgml/func.sgml.

Neil Conway committed:

- Fix capitalization and punctuation of two more GUC description
strings.

Magnus Hagander committed:

- Make it possible to build with integer datetimes in msvc, and enable
by default.

- Un-break build on ANSI compilers (like msvc) by moving Assert to
position after variable declarations.

- Add script to run regression tests under vc++ without mingw. Update
clean script to properly clean up the result of it.

- One more fix for makefile := to : change.

- Properly parse Makefile after change from := to =.

Bruce Momjian committed:

- In pgsql/doc/src/sgml/spi.sgml, document that when sending multiple
commands in a single string to SPI_execute(), the later commands
cannot depend on the creation of objects earlier in the string.

- Update wording in TODO with some commas.

- Add to TODO: "Allow SQL-language functions to reference parameters
by parameter name." currently SQL-language functions can only refer
to parameters via $1, etc

- Update "Conflicting lock modes" to show as conflict, add
current/requested headings, add link to table from text.

- Remove rint() for to_char MS and US output. We can't us rint()
because we can't overflow to the next higher units, and we might
print the lower units for MS.

- Add to TODO: "Allow holdable cursors in SPI."

- Cleanup of Brendan Jurd's to_char() patch.

- Add to TODO: "Allow row and record variables to be set to NULL
constants, and allow NULL tests on such variables Because a row is
not scalar, do not allow assignment from NULL-valued scalars.

- Remove installation mention that integer timestamps is less stable
that floating point.

- Mark Kirkwood's patch to create AVG() aggregates for int8 and
NUMERIC which do not compute X^2, as a performance enhancement.

- Add to TODO: "Consider reducing on-disk varlena length from four to
two because a heap row cannot be more than 64k in length

- Reduce the amount of memory "clobbered" for every process title
change, on platforms that need this. This is done by only writing
past the previously stored message, if it was longer.

- In pgsql/doc/src/sgml/ecpg.sgml, remove extra character erroneously
added.

- Remove tabs from SGML files to help tag alingment and improve
detection of tabs are added in the future.

- Brendan Jurd's patch which adds two new format fields for use with
to_char(), to_date() and to_timestamp() "ID" for day-of-week and
"IDDD" for day-of-year This makes it possible to convert ISO week
dates to and from text fully represented in either week
('IYYY-IW-ID') or day-of-year ('IYYY-IDDD') format. It also adds an
'isoyear' field for use with extract / date_part.

- Victor B. Wagner's SSL improvements which allow reading a global SSL
configuration file, add GUC "ssl_ciphers" to control allowed
ciphers, and add libpq environment variable PGSSLKEY to control SSL
hardware keys

- Add to TODO: "Use LC_TIME for localized weekday/month names, rather
than LC_MESSAGES."

- Fix to_date()/to_timestamp() 'D' field for day of week, was off by
one. Converting from char using 'D' doesn't make lots of sense, of
course. Report from Brendan Jurd.

- Move fsync method macro defines into /include/access/xlogdefs.h so
they can be used by src/tools/fsync/test_fsync.c.

- Add to TODO: "Clean up casting in /contrib/isn."

- Add to TODO: "Improve logging of prepared statements recovered
during startup."

- In TODO, add URL for, "Allow SQL-language functions to return
results from RETURNING queries."

- In TODO, add URL for, "Allow accurate statistics to be collected on
indexes with more than one column or expression indexes, perhaps
using per-index statistics."

- In TODO, add URL for, "Update Bonjour to work with newer
cross-platform SDK"

- Add to TODO: "Improve failure message when DROP DATABASE is used on
a database that has prepared transactions."

- In TODO, add URL for, "Update Bonjour to work with newer
cross-platform SDK."

- In clusterdb and reindexdb, add code so database scans are done in
an order consistent with pg_dumpall.

- Update /contrib/fuzzystrmatch error message to mention bytes, not
just 'length', which can be characters.

- Add ORDER BY to vacummdb so databases are scaned in the same order
as pg_dumpall.

- Add to TODO: "Update our code to handle 64-bit timezone files to
match the zic source code, which now uses them"

- In pgsql/src/port/open.c, add comment to explain why O_EXCL and
O_TRUNC can be ignored in openFlagsToCreateFileFlags() in certain
cases.

- In pgsql/src/backend/utils/adt/formatting.c, add comment that
to_char() for broken glibc pt_BR might cause a problem.

Tom Lane committed:

- Improve plpgsql's error message when a datatype declaration is
omitted. Per example from Jeff Ross.

- Put function expressions and values lists into FunctionScan and
ValuesScan plan nodes, so that the executor does not need to get
these items from the range table at runtime. This will avoid
needing to include these fields in the compact range table I'm
expecting to make the executor use.

- Fix portal management code to support non-default command completion
tags for portals using PORTAL_UTIL_SELECT strategy. This is
currently significant only for FETCH queries, which are supposed to
include a count in the tag. Seems it's been broken since 7.4, but
nobody noticed before Knut Lehre.

- Add code so that when COPY_PARSE_PLAN_TREES is defined, the copy and
equal functions are checked for raw parse trees as well as
post-analysis trees. This was never very important before, but the
upcoming plan cache control module will need to be able to do
copyObject() on raw parse trees.

- Teach find_nonnullable_rels to handle OR cases: if every arm of an
OR forces a particular relation nonnullable, then we can say that
the OR does. This is worth a little extra trouble since it may
allow reduction of outer joins to plain joins.

- Fix new RI operator selection code to do the right thing when
working with an opclass for a generic type such as ANYARRAY. The
original coding failed to check that PK and FK columns were of the
same array type. Per discussion with Tom Dunstan. Also, make the
code a shade more readable by not trying to economize on variables.

- Adjust the definition of is_pushed_down so that it's always true for
INNER JOIN quals, just like WHERE quals, even if they reference
every one of the join's relations. Now that we can reorder outer
and inner joins, it's possible for such a qual to end up being
assigned to an outer join plan node, and we mustn't have it treated
as a join qual rather than a filter qual for the node. (If it were,
the join could produce null-extended rows that it shouldn't.) Per
bug report from Pelle Johansson.

- Code review for SSLKEY patch.

- Fix markup, spelling, grammar, and explanations for SSLKEY patch.

- Fix another problem in 8.2 changes that allowed "one-time" qual
conditions to be checked at plan levels below the top; namely, we
have to allow for Result nodes inserted just above a nestloop inner
indexscan. Should think about using the general Param mechanism to
pass down outer-relation variables, but for the moment we need a
back-patchable solution. Per report from Phil Frost.

- Restructure code that is responsible for ensuring that clauseless
joins are considered when it is necessary to do so because of a
join-order restriction (that is, an outer-join or IN-subselect
construct). The former coding was a bit ad-hoc and inconsistent,
and it missed some cases, as exposed by Mario Weilguni's recent bug
report. His specific problem was that an IN could be turned into a
"clauseless" join due to constant-propagation removing the IN's
joinclause, and if the IN's subselect involved more than one
relation and there was more than one such IN linking to the same
upper relation, then the only valid join orders involve "bushy"
plans but we would fail to consider the specific paths needed to get
there. (See the example case added to the join regression test.)
On examining the code I wonder if there weren't some other problem
cases too; in particular it seems that GEQO was defending against a
different set of corner cases than the main planner was. There was
also an efficiency problem, in that when we did realize we needed a
clauseless join because of an IN, we'd consider clauseless joins
against every other relation whether this was sensible or not. It
seems a better design is to use the outer-join and in-clause lists
as a backup heuristic, just as the rule of joining only where there
are joinclauses is a heuristic: we'll join two relations if they
have a usable joinclause *or* this might be necessary to satisfy an
outer-join or IN-clause join order restriction. I refactored the
code to have just one place considering this instead of three, and
made sure that it covered all the cases that any of them had been
considering. Backpatch as far as 8.1 (which has only the IN-clause
form of the disease). By rights 8.0 and 7.4 should have the bug
too, but they accidentally fail to fail, because the joininfo
structure used in those releases preserves some memory of there
having once been a joinclause between the inner and outer sides of
an IN, and so it leads the code in the right direction anyway. I'll
be conservative and not touch them.

- Add ORDER BY to a query on information_schema.views, to avoid
possible platform-specific result ordering. Per buildfarm results.

- Repair oversight in 8.2 change that improved the handling of
"pseudoconstant" WHERE clauses. createplan.c is now willing to
stick a gating Result node almost anywhere in the plan tree, and in
particular one can wind up directly underneath a MergeJoin node.
This means it had better be willing to handle Mark/Restore.
Fortunately, that's trivial in such cases, since we can just pass
off the call to the input node (which the planner has previously
ensured can handle Mark/Restore). Per report from Phil Frost.

- Add some discussion of sort ordering to indices.sgml, which
curiously had never touched the subject before.

- Heikki Linnakangas's patch which throws an error if you try to
COMMIT/ROLLBACK PREPARED from a database other than the one where
the transaction was originally prepared.

- Minor editorialization on operator-family documentation: put some
copied-and-pasted text in a more useful location.

- Fix up foreign-key mechanism so that there is a sound semantic basis
for the equality checks it applies, instead of a random dependence
on whatever operators might be named "=". The equality operators
will now be selected from the opfamily of the unique index that the
FK constraint depends on to enforce uniqueness of the referenced
columns; therefore they are certain to be consistent with that
index's notion of equality. Among other things this should fix the
problem noted awhile back that pg_dump may fail for foreign-key
constraints on user-defined types when the required operators aren't
in the search path. This also means that the former warning
condition about "foreign key constraint will require costly
sequential scans" is gone: if the comparison condition isn't
indexable then we'll reject the constraint entirely. All per past
discussions. Along the way, make the RI triggers look into
pg_constraint for their information, instead of using
pg_trigger.tgargs; and get rid of the always error-prone fixed-size
string buffers in ri_triggers.c in favor of building up the RI
queries in StringInfo buffers. This forces initdb due to columns
added to pg_constraint and pg_trigger.

- Disallow committing a prepared transaction unless we are in the same
database it was executed in. Someday it might be nice to allow
cross-DB commits, but work would be needed in NOTIFY and perhaps
other places. Per Heikki Linnakangas.

- Improve postmaster's behavior if an accept() call fails. Because
the server socket is still read-ready, the code was a tight loop,
wasting lots of CPU. We can't do anything to clear the failure,
other than wait, but we should give other processes more chance to
finish and release FDs; so insert a small sleep. Also, avoid bogus
"close(-1)" in this case. Per report from Jim Nasby.

- Repair bug in 8.2's new logic for planning outer joins: we have to
allow joins that overlap an outer join's min_righthand but aren't
fully contained in it, to support joining within the RHS after
having performed an outer join that can commute with this one.
Aside from the direct fix in make_join_rel(), fix
has_join_restriction() and GEQO's desirable_join() to consider this
possibility. Per report from Ian Harding.

- Avoid infinite recursion when dumping new planner EquivalenceClass
trees.

Teodor Sigaev committed:

- Fix backend crash in parsing incorrect tsquery. Per report from Jon
Rosebaugh.

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Pavel Stehule sent in a patch which implements TABLE functions per
SQL:2003.

Greg Smith sent in a patch which adds two command-line switches to
pgbench: -x, which generates extended detail in the latency log, and
-X, which does extra cleanup work after the run.

Pavan Deolasee sent in another version of the HOT-update patch.

Gregory Stark sent in a patch to replace the VARATT_SIZEP with
SET_VARLENA_LEN. This is part of the implementation of the
variable-length varlena type per discussion on -hackers.

Tom Dunstan sent in an updated version of his enums patch.

Magnus Hagander sent in a patch to support MOVE for cursors in
PL/PgSQL.

Arul Shaji sent in a patch to implement the TODO item, "Allow server
log information to be output as INSERT statements
This would allow server log information to be easily loaded into a
database for analysis." Tom Lane suggested that he refactor this as
COPY statements.

Alvaro Herrera sent in two more patches to clean up the bootstrap
code.

Chad Wagner sent in a patch which allows psql's \pset to take an
argument for boolean variables. The old toggling behavior still works
when no argument is passed.

Bruce Momjian sent in a patch implementing isodow in date_part.
  • Twitter
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007
  • Bloglines Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007
  • Technorati Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007
  • Fark this: Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 at YahooMyWeb
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 at Furl.net
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 at reddit.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 at blinklist.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 at Spurl.net
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 at Simpy.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 at blogmarks
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 18. Februar 2007 with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Wöchentlicher PostgreSQL Newsletter - 11. Februar 2007


Der Originalartikel befindet sich unter:
http://people.planetpostgresql.org/dfetter/

== Wöchentlicher PostgreSQL Newsletter - 11. Februar 2007 ==

Magnus Hagander und Joachim Wieland haben eine Doxygen Seite für den PostgreSQL Sourcecode erstellt.
http://doxygen.postgresql.org/

Magnus Hagander ist jetzt ein Commiter. Glückwünsche!

Simon Riggs fing eine neue Diskussion über HOT-Tables -- Tabellen, die für extrem frequentierte Updates optimiert sind -- mit seinem vereinfachten Antrag für das Design und die Implementierung derselben an.

== PostgreSQL Product News ==

GNUmed Version 0.2.4.2 erschienen.

Pgpool 3.2 ist draussen:
http://pgfoundry.org/projects/pgpool

Peter Eisentraut hat die Funktionen sql_to_xml(TEXT) table_to_xmlschema(TEXT) implementiert, welche die zugrundeliegenden XML Datentypen in CVS TIP benutzen.

MicroOLAP Database Designer 1.2.0 BETA 1 für PostgreSQL erschienen.
http://microolap.com/products/database/postgresql-designer/

ht://Miner 0.50 erschienen.
http://sourceforge.net/projects/htminer/

== PostgreSQL Jobs im Februar ==

http://archives.postgresql.org/pgsql-jobs/2007-02/threads.php

== PostgreSQL Lokal ==

Es wird einen PostgreSQL Stand auf dem CLT am 3.-4. März in Chemnitz/Deutschland geben. Einige der üblichen Verdächtigen der deutschsprachigen PostgreSQl Community werden dort sein. Kontaktiere info@pgug.de für mehr Informationen.
http://chemnitzer.linux-tage.de/2007/info/

Es wird einen PostgreSQL Stand auf der FOSDEM am 24. und 25. Februar in Brüssel, Belgien sein. Viele der üblichen Verdächtigen von den EU PostgreSQL Communities werden dort sein. Kontaktiere de@postgresql.org für mehr Informationen.
http://www.fosdem.org/2007/

Die italienische PostgreSQL Community ist auf der Suche nach Sponsoren für ihren PostgreSQL Tag in Prato, Italien im Sommer. Mehr Informationen unter untenstehendem Link.
http://www.pgday.it

== 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, Devrim GUNDUZ und Stefan Kaltenbrunner

Um deine Beiträge für die nächste Ausgabe zu übermitteln, sende diese an david@fetter.org bis Sonntag, 15:00 Uhr Pazifischer Zeit.

== Angewandte Patches ==

Magnus Hagander (kudos!) committed:

- Fix pg_standby to build on msvc.

- Fix for early log messages during postmaster startup getting lost
when running as a service on Win32. Per report from Harald Armin
Massa. Backpatched to PostgreSQL 8.1

Neil Conway committed:

- Unbreak the SGML doc build: ":=" is needed to assign to variables if
the RHS of the assignment expands to a reference to the LHS.

Alvaro Herrera committed:

- Heikki Linnakangas's patch which fixes reference-after-free in the
new btree page split code, as reported by the buildfarm via Stefan
Kaltenbrunner.

- Magnus Hagander's patch which the VC++ build needs to compile the
new strlcat.c file.

Michael Meskes committed:

- Fixed multibyte handling in ecpg per report from HARADA Toshi.

- Backported regression test changes from HEAD so the buildfarm
hopefully gets green again.

- Backported va_list handling cleanup for ecpg.

Peter Eisentraut committed:

- Add support for optionally escaping periods when converting SQL
identifiers to XML names, which will be required for supporting XML
export.

- Add proper mapping of boolean type data to XML Schema.

- StrNCpy -> strlcpy (not complete)

- Remove useless CPPFLAGS.

- Add $PostgreSQL$ marker to contrib makefiles.

- Replace useless uses of := by = in makefiles.

- Normalize fgets() calls to use sizeof() for calculating the buffer
size where possible, and fix some sites that apparently thought that
fgets() will overwrite the buffer by one byte. Also add some
strlcpy() to eliminate some weird memory handling.

- Replace some strncpy() with strlcpy().

- Use memcpy() instead of strncpy() for copying into varlena
structures.

- Add strlcat() from OpenBSD, to be used for replacing strncat and
other strange coding practices.

- Move NAMEDATALEN definition from postgres_ext.h to
pg_config_manual.h. It used to be part of libpq's exported interface
many releases ago, but now it's no longer necessary to make it
accessible to clients.

Bruce Momjian committed:

- Update FAQ for new 24-byte header, down from 28.

- Mark TODO as done: "Merge xmin/xmax/cmin/cmax back into three header
fields."

- In pgsql/src/backend/utils/adt/formatting.c, remove blank line from
source code.

- Add to TODO: "Consider placing all sequences in a single table or
create a system view, now that system tables are full transactional"

- Add URL for "Add support for SQL-standard GENERATED/IDENTITY
columns" to TODO list.

- Pavel Stehule's patch which fixes a bug when localized to_char() day
or month names were incorectly transformed to lower or upper string.

- Korry Douglas's patch which fixes shared_preload_libraries on
Windows hosts. It forces each backend to re-load all
shared_preload_libraries.

- Teodor Sigaev's patch which adds a lock matrix to documentation.

- Magnus Hagander's Win32 regression test fixes. For win32 in
general, this makes it possible to run the regression tests as an
admin user by using the same restricted token method that's used by
pg_ctl and initdb. For vc++, it adds building of pg_regress.exe,
adds a resultmap, and fixes how it runs the install.

- Simon Riggs's patch which adds /contrib/pg_standby. pg_standby is a
production-ready program that can be used to create a Warm Standby
server. Other configuration is required as well, all of which is
described in the main server manual.

- Heikki Linnakangas's patch which reduces WAL activity for page
splits.

- Gavin Sherry' patch which checks whether the role exists before
doing more complex ident and Kerberos authentication checks in the
backend.

- Update URL for "Generalized Partial Indexes" paper to point to a
cached version. Backpatch to 8.2.X.

- Document that wal_sync_method open_* methods use O_DIRECT, if
available. Backpatch to 8.2.X.

- Fix bug in our code when using to_timestamp() or to_date() without
"TM". Assume "TM" when input fields are variable-length, like month
or day names. This matches Oracle behavior.

- Stamp bug template for 8.3.

- Stamp releases notes for 8.2.3, 8.1.8, 8.0.12.

- Stamp releases 8.2.3, 8.1.8, 8.0.12. No release notes yet.

- Update FAQ for 8.2.3.

- Backpatch FAQs to 8.2.X branch.

- Update FAQ to show 8.2.2 as the most recent release.

- Update timezone FAQ.

- In TODO, split NAMEDATALEN entry into two parts. The one still on
the TODO list is, "Consider making NAMEDATALEN more configurable in
future releases."

- Trim down environment variable instructions for Win32, backpatch to
8.2.X.

- Add TODO item: "Add a \set variable to control whether \s displays
line numbers."

- Clarify TODO item for # feature: "Add # to list command history
like \s, but with line numbers"

Andrew Dunstan committed:

- Pass modern COPY syntax to backend, since copy (query) does not
accept old syntax. Per complaint from Michael Fuhr.

Tom Lane committed:

- Fix another erroneous =-for-:= substitution.

- Improve documentation for CREATE CONSTRAINT TRIGGER.

- Put back some not-so-unnecessary-as-all-that := usages. Per
buildfarm.

- Hm, seems my hack on rowtypes regression test has made its output
row order platform-specific. Add an ORDER BY clause to stop
buildfarm failures.

- Minor tweak to make rowtypes regression test run faster. We don't
currently have any better strategy for this query than re-running
the sub-select over and over; it seems unlikely that doing so 10000
times is a more useful test than doing it a few dozen times.

- Call pgstat_drop_database during DROP DATABASE, so that any stats
file entries for the victim database go away sooner rather than
later. We already did the equivalent thing at the per-relation
level, not sure why it's not been done for whole databases. With
this change, pgstat_vacuum_tabstat should usually not find anything
to do; though we still need it as a backstop in case DROPDB or
TABPURGE messages get lost under load.

- Combine cmin and cmax fields of HeapTupleHeaders into a single
field, by keeping private state in each backend that has inserted
and deleted the same tuple during its current top-level transaction.
This is sufficient since there is no need to be able to determine
the cmin/cmax from any other transaction. This gets us back down to
23-byte headers, removing a penalty paid in 8.0 to support
subtransactions. Patch by Heikki Linnakangas, with minor revisions
by moi, following a design hashed out awhile back on the pghackers
list.

- Add missing #define for mingw, per Magnus.

- Fix an ancient logic error in plpgsql's exec_stmt_block: it thought
it could get away with not (re)initializing a local variable if the
variable is marked "isconst" and not "isnull". Unfortunately it
makes this decision after having already freed the old value,
meaning that something like

for i in 1..10 loop
declare c constant text := 'hi there';

leads to subsequent accesses to freed memory, and hence probably
crashes. (In particular, this is why Asif Ali Rehman's bug leads to
crash and not just an unexpectedly-NULL value for SQLERRM: SQLERRM
is marked CONSTANT and so triggers this error.) The whole thing
seems wrong on its face anyway: CONSTANT means that you can't change
the variable inside the block, not that the initializer expression
is guaranteed not to change value across successive block entries.
Hence, remove the "optimization" instead of trying to fix it.

- Rearrange use of plpgsql_add_initdatums() so that only the parsing
of a DECLARE section needs to know about it. Formerly, everyplace
besides DECLARE that created variables needed to do
"plpgsql_add_initdatums(NULL)" to prevent those variables from being
sucked up as part of a subsequent DECLARE block. This is obviously
error-prone, and in fact the SQLSTATE/SQLERRM patch had failed to do
it for those two variables, leading to the bug recently exhibited by
Asif Ali Rehman: a DECLARE within an exception handler tried to
reinitialize SQLERRM. Although the SQLSTATE/SQLERRM patch isn't in
any pre-8.1 branches, and so I can't point to a demonstrable failure
there, it seems wise to back-patch this into the older branches
anyway, just to keep the logic similar to HEAD.

- Add a function pg_stat_clear_snapshot() that discards any statistics
snapshot already collected in the current transaction; this allows
plpgsql functions to watch for stats updates even though they are
confined to a single transaction. Use this instead of the previous
kluge involving pg_stat_file() to wait for the stats collector to
update in the stats regression test. Internally, decouple storage
of stats snapshots from transaction boundaries; they'll now stick
around until someone calls pgstat_clear_snapshot --- which xact.c
still does at transaction end, to maintain the previous behavior.
This makes the logic a lot cleaner, at the price of a couple dozen
cycles per transaction exit.

- Modify the stats regression test to delay until the stats file
actually changes (with an upper limit of 30 seconds), and record the
delay time in the postmaster log. This should give us some info
about what's happening with the intermittent stats failures in
buildfarm. After an idea of Andrew Dunstan's.

- Remove the xlog-centric "database system is ready" message and
replace it with "database system is ready to accept connections",
which is issued by the postmaster when it really is ready to accept
connections. Per proposal from Markus Schiltknecht and subsequent
discussion.

- Fix PG_VERSION_NUM too.

- Fix wrong spacing (I think Bruce tried to hand-edit this instead of
letting autoconf do it)

- Fix an error in the original coding of holdable cursors:
PersistHoldablePortal thought that it didn't have to reposition the
underlying tuplestore if the portal is atEnd. But this is not so,
because tuplestores have separate read and write cursors ... and the
read cursor hasn't moved from the start. This mistake explains bug
#2970 from William Zhang. Note: the coding here is pretty
inefficient, but given that no one has noticed this bug until now,
I'd say hardly anyone uses the case where the cursor has been
advanced before being persisted. So maybe it's not worth worrying
about.

- Remove typmod checking from the recent security-related patches. It
turns out that ExecEvalVar and friends don't necessarily have access
to a tuple descriptor with correct typmod: it definitely can contain
-1, and possibly might contain other values that are different from
the Var's value. Arguably this should be cleaned up someday, but
it's not a simple change, and in any case typmod discrepancies don't
pose a security hazard. Per reports from numerous people :-( I'm
not entirely sure whether the failure can occur in 8.0 --- the
simple test cases reported so far don't trigger it there. But
back-patch the change all the way anyway.

- In pgsql/src/backend/port/sysv_shmem.c, fix typo in comment.

- Heikki Linnakangas's patch to remove some dead code in
src/backend/access/nbtree/nbtinsert.c

- Fix a performance regression in 8.2: optimization of MIN/MAX into
indexscans had stopped working for tables buried inside views or
sub-selects. This is because I had gotten rid of the
simplify_jointree() preprocessing step, and
optimize_minmax_aggregates() wasn't smart enough to deal with a
non-canonical FromExpr. Per gripe from Bill Howe.

- Docs updates for cross-type hashing.

- Come to think of it, we should check that commutator pairs have the
same merges/hashes property settings.

- Not only did we agree that this 'hint' doesn't belong here, but the
markup's broken. So just remove it...

- Add support for cross-type hashing in hashed subplans (hashed IN/NOT
IN cases that aren't turned into true joins). Since this is the
last missing bit of infrastructure, go ahead and fill out the hash
integer_ops and float_ops opfamilies with cross-type operators. The
operator family project is now DONE...er, except for documentation...

- Repair failure to check that a table is still compatible with a
previously made query plan. Use of ALTER COLUMN TYPE creates a
hazard for cached query plans: they could contain Vars that claim a
column has a different type than it now has. Fix this by checking
during plan startup that Vars at relation scan level match the
current relation tuple descriptor. Since at that point we already
have at least AccessShareLock, we can be sure the column type will
not change underneath us later in the query. However, since a
backend's locks do not conflict against itself, there is still a
hole for an attacker to exploit: he could try to execute ALTER
COLUMN TYPE while a query is in progress in the current backend.
Seal that hole by rejecting ALTER TABLE whenever the target relation
is already open in the current backend. This is a significant
security hole: not only can one trivially crash the backend, but
with appropriate misuse of pass-by-reference datatypes it is
possible to read out arbitrary locations in the server process's
memory, which could allow retrieving database content the user
should not be able to see. Our thanks to Jeff Trout for the initial
report. Security: CVE-2007-0556

- Repair insufficiently careful type checking for SQL-language
functions: we should check that the function code returns the
claimed result datatype every time we parse the function for
execution. Formerly, for simple scalar result types we assumed the
creation-time check was sufficient, but this fails if the function
selects from a table that's been redefined since then, and even more
obviously fails if check_function_bodies had been OFF. This is a
significant security hole: not only can one trivially crash the
backend, but with appropriate misuse of pass-by-reference datatypes
it is possible to read out arbitrary locations in the server
process's memory, which could allow retrieving database content the
user should not be able to see. Our thanks to Jeff Trout for the
initial report. Security: CVE-2007-0555

- Rename MaxTupleSize to MaxHeapTupleSize to clarify that it's not
meant to describe the maximum size of index tuples (which is
typically AM-dependent anyway); and consequently remove the bogus
deduction for "special space" that was built into it. Adjust
TOAST_TUPLE_THRESHOLD and TOAST_MAX_CHUNK_SIZE to avoid wasting two
bytes per toast chunk, and to ensure that the calculation correctly
tracks any future changes in page header size. The computation had
been inaccurate in a way that didn't cause any harm except space
wastage, but future changes could have broken it more drastically.
Fix the calculation of BTMaxItemSize, which was formerly computed as
1 byte more than it could safely be. This didn't cause any harm in
practice because it's only compared against maxalign'd lengths, but
future changes in the size of page headers or btree special space
could have exposed the problem. initdb forced because of change in
TOAST_MAX_CHUNK_SIZE, which alters the storage of toast tables.

== Abgelehnte Patches (bis jetzt) ==

Chris Marcellino's patch to add POSIX shared memory support because it
doesn't actually detect whether a shared memory segment is in use.

== Eingesandte Patches ==

Two versions of Simon Riggs's patch which moves a warning in VACUUM to
a spot after lazy_truncate_heap(). Both move the test and WARNING.
The second also adjusts the hint according to relation size.

Guido Goldstein's revised patch which fixes PL/PythonU's handling of
BOOLEAN return functions and, unlike the previous patch, is designed
to work with Python 2.3 as well as later versions.

Gregory Stark sent in a patch to speed up LIMIT/SORT operations, as
typically used in paging out result sets.

Simon Riggs sent in a patch to speed up CLUSTER by avoiding WAL when
archive_command is not set.

Gregory Stark sent in another work-in-progress patch which implements
WITH RECURSIVE.

Chad Wagner sent in a patch which lets psql prompt for variables.

Chris Marcellino sent in a patch to make OS/X use SysV semaphors.

Heikki Linnakangas sent in a patch which refactors findsplitloc and
checksplitloc so that the division of labor is clearer. All the space
calculation inside the loop is now in checksplitloc.

Jeremy Drake sent in a patch which implements regexp_replace with
multiple atoms, regexp_matches, a set-returning function, and
regexp_split. Perl weenies rejoice!
  • Twitter
  • Bookmark Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007 at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007
  • Bloglines Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007
  • Technorati Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007
  • Fark this: Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007
  • Bookmark Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007 at YahooMyWeb
  • Bookmark Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007 at Furl.net
  • Bookmark Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007 at reddit.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007 at blinklist.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007 at Spurl.net
  • Bookmark Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007 at Simpy.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007 at blogmarks
  • Bookmark Wöchentlicher PostgreSQL Newsletter  - 11. Februar 2007 with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007

Der Originalartikel befindet sich unter:

http://people.planetpostgresql.org/dfetter/



== Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 ==

Sicherheitsupdates für 8.2.2, 8.1.7, 8.0.11, 7.4.16 und 7.3.18 werden
morgen, am 5. Februar 2007 erscheinen. Fertigmachen zum Updaten.

== PostgreSQL Produkt Neuigkeiten ==

PGCon, die PostgreSQL Konferenz, ist auf der Suche nach führenden
Unternehmen um die Veranstaltung zu sponsoren. Details unter:
http://www.pgcon.org/2007/sponsorship.php

Red Hat hat seinen Application Stack mit PostgreSQL herausgegeben.
http://www.redhat.com/solutions/rhappstack/

== PostgreSQL Jobs im Februar ==


http://archives.postgresql.org/pgsql-jobs/2007-02/threads.php

== PostgreSQL Lokal ==


Hauptbeitragender Gavin Sherry und Postgres-R Leiter Markus werden auf
dem SFPUG Meeting in San Francisco am 6. Februar um 19:00 Uhr im
Hyperic LCCs Hauptquartier sprechen. Reservierungen unter
untenstehender URL um kostenloses Thai Essen zu erhalten.
http://postgresql.meetup.com/1/calendar/5380885/

Es wird einen PostgreSQL Stand auf der Scale 5x in Los Angeles vom
10.-11. Februar auf dem Westin Los Angeles Airport geben. Kontaktiere
josh at agliodbs dot com, wenn du nicht schon teilnimmst.
http://www.socallinuxexpo.org/scale5x/

Es wird einen PostgreSQL Stand auf dem CLT am 3.-4. März in
Chemnitz/Deutschland geben. Einige der üblichen Verdächtigen der
deutschsprachigen PostgreSQl Community werden dort sein. Kontaktiere
info@pgug.de für mehr Informationen.
http://chemnitzer.linux-tage.de/2007/info/

Es wird einen PostgreSQL Stand auf der FOSDEM am 24. und 25. Februar in
Brüssel, Belgien sein. Viele der üblichen Verdächtigen von den EU
PostgreSQL Communities werden dort sein. Kontaktiere de@postgresql.org
für mehr Informationen.
http://www.fosdem.org/2007/

Die italienische PostgreSQL Community ist auf der Suche nach Sponsoren
für ihren PostgreSQL Tag in Prato, Italien im Sommer. Mehr Informationen unter untenstehendem Link.

http://www.pgday.it



== 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 Devrim GUNDUZ.

Um deine Beiträge für die nächste Ausgabe zu übermitteln, sende diese
an david@fetter.org bis Sonntag, 15:00 Uhr Pazifischer Zeit.

== Angewandte Patches ==

Michael Meskes committed:

- Magnus Hagander's patch which removes some compiler warnings.

- Cleaned up va_list handling in ecpg. This should now work on all
archs.

Neil Conway committed:

- Magnus Hagander's patch which changes the installscript for vcbuild
actually to parse the generated solution files for what to install,
instead of blindly copying everything as it previously did. For
example, in the previous quick-n-dirty version, it would copy old
DLLs if you reconfigured in a way that didn't include subprojects
like a PL.

- Reword suggestion that libpq.dll be installed in WINNT\SYSTEM32
under Windows. Per Magnus Hagander, this is not recommended.

- Fix two more regression tests whose expected outputs were not
updated for the recent may/might cleanup.

- Update some of the "expected" regression test results for Bruce's
recent may/might cleanup, in the hopes that this will unbreak the
buildfarm. Per report from Stefan Kaltenbrunner.

- Backpatch last night's fix for broken markup to the 8.2 branch.

- Fix broken markup in pgsql/doc/src/sgml/release.sgml

- This patch adds documentation for the long-version parameters
--username and --password for pg_dump, pg_dumpall and pg_restore,
per complaint by Michael Schmidt. Patch from Magnus Hagander.

- Fix a few typos in comments in GiN.

- Add missing Makefile dependencies for the "bigtest" and "bigcheck"
targets, and refactor the tablespace setup code into a separate
Makefile target.

- Rewrite uuid input and output routines to avoid dependency on the
nonportable "hh" sprintf(3) length modifier. Instead, do the parsing
and output by hand. The code to do this isn't ideal, but this is an
interim measure anyway: the uuid type should probably use the
in-memory struct layout specified by RFC 4122. For now, this patch
should hopefully rectify the buildfarm failures for the uuid test.
Along the way, re-add pg_cast entries for uuid <-> varchar, which I
mistakenly removed earlier, and bump the catversion.

Teodor Sigaev committed:

- Revert gincostestimate changes.

- Update FreeBSD DocBook installation notes.

- Allow GIN's extractQuery method to signal that nothing can satisfy
the query. In this case extractQuery should returns -1 as nentries.
This changes prototype of extractQuery method to use int32* instead
of uint32* for nentries argument. Based on that gincostestimate may
see two corner cases: nothing will be found or seqscan should be
used. Per proposal at
http://archives.postgresql.org/pgsql-hackers/2007-01/msg01581.php

Tom Lane committed:

- Don't MAXALIGN in the checks to decide whether a tuple is over
TOAST's threshold for tuple length. On 4-byte-MAXALIGN machines,
the toast code creates tuples that have t_len exactly
TOAST_TUPLE_THRESHOLD ... but this number is not itself maxaligned,
so if heap_insert maxaligns t_len before comparing to
TOAST_TUPLE_THRESHOLD, it'll uselessly recurse back to tuptoaster.c,
wasting cycles. (It turns out that this does not happen on
8-byte-MAXALIGN machines, because for them the outer MAXALIGN in the
TOAST_MAX_CHUNK_SIZE macro reduces TOAST_MAX_CHUNK_SIZE so that
toast tuples will be less than TOAST_TUPLE_THRESHOLD in size. That
MAXALIGN is really incorrect, but we can't remove it now, see
below.) There isn't any particular value in maxaligning before
comparing to the thresholds, so just don't do that, which saves a
small number of cycles in itself. These numbers should be
rejiggered to minimize wasted space on toast-relation pages, but we
can't do that in the back branches because changing
TOAST_MAX_CHUNK_SIZE would force an initdb (by changing the contents
of toast tables). We can move the toast decision thresholds a bit,
though, which is what this patch effectively does. Thanks to Pavan
Deolasee for discovering the unintended recursion. Back-patch into
8.2, but not further, pending more testing. (HEAD is about to get a
further patch modifying the thresholds, so it won't help much for
testing this form of the patch.)

- Use -Wl for linker switches on freebsd --- back-port of an 8.0
change. I think this will make buildfarm member herring go green in
this branch.

- Fix typo in pgsql/doc/src/sgml/sources.sgml

- Fix plpgsql so that when a local variable has no initial-value
expression, an error will be thrown correctly if the variable is of
a NOT NULL domain. Report and almost-correct fix from Sergiy
Vyshnevetskiy (bug #2948).

- Fix initdb to not generate misleading error messages when
postgres.bki or other share-directory files are inaccessible for
some reason other than not existing. Inspired by trouble report
from Simon Kinsella.

- Repair oversights in the mechanism used to store compiled plpgsql
functions. The original coding failed (tried to access deallocated
memory) if there were two active call sites (fn_extra pointers) for
the same function and the function definition was updated. Also, if
an update of a recursive function was detected upon nested entry to
the function, the existing compiled version was summarily
deallocated, resulting in crash upon return to the outer instance.
Problem observed while studying a bug report from Sergiy
Vyshnevetskiy. Bug does not exist before 8.1 since older versions
just leaked the memory of obsoleted compiled functions, rather than
trying to reclaim it.

- Add SPI_push/SPI_pop calls so that datatype input and output
functions called by plpgsql can themselves use SPI --- possibly
indirectly, as in the case of domain_in() invoking plpgsql functions
in a domain check constraint. Per bug #2945 from Sergiy
Vyshnevetskiy. Somewhat arbitrarily, I've chosen to back-patch this
as far as 8.0. Given the lack of prior complaints, it doesn't seem
critical for 7.x.

- Add support for cross-type hashing in hash index searches and hash
joins. Hashing for aggregation purposes still needs work, so it's
not time to mark any cross-type operators as hashable for general
use, but these cases work if the operators are so marked by hand in
the system catalogs.

- Add comment noting that hashm_procid in a hash index's metapage
isn't actually used for anything.

Bruce Momjian committed:

- Add to TODO: "Allow custom variable classes that can restrict who
can set the values"
http://archives.postgresql.org/pgsql-hackers/2006-11/msg00911.php

- Update wording in pgsql/doc/src/sgml/libpq.sgml on how to set an
environment variable on Windows.

- Add documentation for Windows on how to set an environment variable.
Backpatch to 8.2.X.

- In pgsql/src/backend/commands/vacuumlazy.c, change vacuum lazy
"compacting" warning message to: errhint("Consider using VACUUM FULL
on this relation or increasing the configuration parameter
+\"max_fsm_pages\".")));

- Update URL for the "Allow sequential scans to take advantage of
other concurrent sequential scans, also called 'Synchronised
Scanning'" TODO.

- Add to TODO: "Add # to list command history like \s, but with line
numbers"
http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php

- Add URLs to the "Allow sequential scans to take advantage of other
concurrent sequential scans, also called 'Synchronised Scanning'"
TODO.

- Document that a client-only install using "gmake -C src/bin install"
does install a few server-only binaries.

- Add to TODO: "Allow recovery.conf to allow the same syntax as
postgresql.conf, including quoting."

- Albe Laurenz's patch which fixes configure detection code when using
both --with-ldap and --enable-thread-safety.

- Add -patches URL for the "Reduce checkpoint performance degredation
by forcing data to disk more evenly" TODO item.

- Add URL for the "Allow sequential scans to take advantage of other
concurrent sequential scans, also called 'Synchronised Scanning'"
TODO item.

- Add to TODO: "Reduce checkpoint performance degredation by forcing
data to disk more evenly" with associated URLs.

- Add URL for the "Fix RENAME to work on variables other than OLD/NEW"
TODO item.

- Add to TODO: "Allow column display reordering by recording a
display, storage, and permanent id for every column?"

- In pgsql/doc/src/sgml/sources.sgml, add "May/Can/Might" section to
error message style guidlines, and a "can't" -> "cannot" section.

- In pgsql/doc/src/sgml/high-availability.sgml, mention file system
replication as a high availability solution in the shared hardware
section, and mention DRBD as a popular solution.

- Add URL to the "Add long file support for binary pg_dump output"
TODO item.

- Wording cleanup for error messages. Also change can't -> cannot.

- Add 8.2.0 "Incomatibilities" documentation that pg_dump's -n and -t
behavior has changed.

- Backpatch FAQs to stable branch.

- Daojing Zhou's update to the Chinese FAQ.

- In TODO, update URL for function permission checks.

- Add to TODO: "Tighten function permission checks."

- Add to TODO: "Tighten trigger permission checks."

- Add to TODO: Fix problem when multiple subtransactions of the same
outer transaction hold different types of locks, and one
subtransaction aborts.

- Update CREATE SEQUENCE documentation to show the same sequence being
created and increments. The old docs created the sequence, then
showed a nextval() of 114.

- In TODO, add URL for, "Fix RENAME to work on variables other than
OLD/NEW."

- Use colons consistently before '<programlisting>' blocks, where
appropriate.

- Revert error message change for may/can/might --- needs discussion.

- Update documentation on may/can/might.

- Add to TODO, in "Fix RENAME to work on variables other than
OLD/NEW", add URL.

- Document need for periodic REINDEX in VACUUM FULL cases.

- In pgsql/doc/src/sgml/array.sgml, add missing colon.

- Add to TODO: "Add REINDEX CONCURRENTLY" with caveats.

- Update TODO for VACUUM FULL by adding a mention that index updates
can bloat the index.

- Added to TODO: "ARRAY[[1,2],[3,4]])[1] should return the same values
as ARRAY[[1,2],[3,4]])[1:1]"

- Add FAQ item about timezones and daylight savings time.

- Update documentation for backslashes to mention escape string syntax
more, and standard_conforming_strings less, because in the future
non-E strings will not treat backslashes specially. Also use E''
strings where backslashes are used in examples. (The existing
examples would have drawn warnings.) Backpatch to 8.2.X.

- Clarify paramater handling for pg_get_serial_sequence().

- Update documentation for pg_get_serial_sequence() function.

- Update wording for minor release upgrades, per Alvaro Herrera.

- In TODO, mark as done: "Add Globally/Universally Unique Identifier
(GUID/UUID)"

- In pgsql/src/port/exec.c, use sizeof() for snprintf() buffer length.

- Update process termination message to display signal number and name
from exec.c and postmaster.c.

Peter Eisentraut committed:

- Update SQL conformance information about XML features.

- Implement XMLSERIALIZE for real. Analogously, make the xml to text
cast observe the xmloption. Reorganize the representation of the
XML option in the parse tree and the API to make it easier to manage
and understand. Add regression tests for parsing back XML
expressions.

- Translation updates

- Update documentation on XML functions

== Abgelehnte Patches (bis jetzt) ==

Niemand wurde diese Woche enttäuscht :-)

== Eingesandte Patches ==

Magnus Hagander sent in a patch which adds documentation for the
long-version parameters --username and --password for pg_dump,
pg_dumpall and pg_restore, per complaint by Michael Schmidt.

Pavan Deolasee sent in another version of his performance patch.

Pursuant to Alvaro Herrera's work in progress autovacuum launcher
patch, Markus Schiltknecht sent in a patch to find current imessages.

Korry Douglas sent in a patch to fix the fixes shared_preload_libraries
on Windows hosts. It forces each backend to re-load all
shared_preload_libraries. Per discussion below:
http://archives.postgresql.org/pgsql-hackers/2007-01/msg01498.php

Heikki Linnakangas sent in another version of his phantom command ids
patch.

Pavan Deolasee sent in a patch which adds the matrix of lock
(in)compatibilies to the source code.

Korry Douglas sent in a patch which lets gprof nab a non-overwritten
profile on Linux.

Koichi Suzuki sent in a patch intended to improve full-page write
performance.

Jeremy Drake sent in a patch which allows regular expression matches
to return an array of matches, along with (optionally) the whole
match, the pre-match, and the post-match. Perl fans, rejoice!

ITAGAKI Takahiro sent in a new version of his n_dead_tuples patch.

Markus Schiltknecht sent in a patch which fixes a small "database is
ready" race condition in StartupXLOG() in xact.c.

Andrew Dunstan sent in a patch which fixes the "\copy (query)
delimiter" syntax error.

Pavel Stehule sent in a patch which fixes a localization bug in time
zone formatting.

  • Twitter
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007
  • Bloglines Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007
  • Technorati Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007
  • Fark this: Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 at YahooMyWeb
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 at Furl.net
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 at reddit.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 at blinklist.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 at Spurl.net
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 at Simpy.com
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 at blogmarks
  • Bookmark Wöchentlicher PostgreSQL Newsletter - 4. Februar 2007 with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca