Skip to content

Wöchentlicher PostgreSQL Newsletter - 27. November 2011

Der Originalartikel befindet sich unter:

== Wöchentlicher PostgreSQL Newsletter - 27. November 2011 ==

FOSDEM 2012 - PostgreSQL Devroom: Call for Speakers ist bis zum
20. Dezember 2011 offen.

== PostgreSQL Jobs im November ==

== PostgreSQL Lokal ==

Der Call for Papers ist offen für PostgreSQL Session #3,
welche am 2. Februar 2012 in Paris stattfindet. Der Einsendeschluß
ist der 30. November 2011, die ausgewählten Sprecher werden bis
zum 14. Dezember 2011 informiert. Vorträge (in Französisch oder
Englisch) sollen an call-for-paper AT postgresql-sessions DOT org
geschickt werden. Mehr Informationen unter:

FOSDEM 2012 - PostgreSQL Devroom: Call for Speakers ist bis zum
20. Dezember 2011 offen.

Die fünfte jährliche "Prager PostgreSQL Entwickler Tag" Konferenz,
welche von CSPUG (Tschechische und Slovakische PostgreSQL
Anwendergruppe) organisiert wird, findet am 9. Februar 2012 in Prag
statt. Der Call for Papers ist jetzt offen.
Bitte senden Sie ihre Einreichungen zusammen mit Kontaktinformationen,
Topic und vorgesehener Länge des Vortrags an info AT p2d2 DOT cz.

Der Call for Papers für die FLOSS UK ist offen, die Veranstaltung
findet in Edinburgh vom 20. bis 22. März 2012 statt. Der
Einsendeschluß für Einreichungen ist der 18. November 2011,
ausgewählte Sprecher werden bis zum 25. November benachrichtigt.
Einreichungen sollen an postgresql2012 AT flossuk DOT org gehen.
Mehr Informationen:

== PostgreSQL in den News ==

Planet PostgreSQL:

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, deutsche an, italienische an, spanische an

== Reviews ==

== Angewandte Patches ==

Tom Lane pushed:

- Fix citext upgrade script to update derived copies of
  pg_type.typcollation.  If the existing citext type has not merely
  been created, but used in any tables, then the upgrade script wasn't
  doing enough.  We have to update attcollation for each citext table
  column, and indcollation for each citext index column, as well.  Per
  report from Rudolf van der Leeden.

- More code review for rangetypes patch.  Fix up some infelicitous
  coding in DefineRange, and add some missing error checks.  Rearrange
  operator strategy number assignments for GiST anyrange opclass so
  that they don't make such a mess of opr_sanity's table of operator
  names associated with different strategy numbers.  Assign
  hopefully-temporary selectivity estimators to range operators that
  didn't have one --- poor as the estimates are, they're still a lot
  better than the default 0.5 estimate, and they'll shut up the
  opr_sanity test that wants to see selectivity estimators on all
  built-in operators.

- Still more review for range-types patch.  Per discussion, relax the
  range input/construction rules so that the only hard error is lower
  bound > upper bound.  Cases where the lower bound is <=3D upper bound,
  but the range nonetheless normalizes to empty, are now permitted.
  Fix core dump in range_adjacent when bounds are infinite.  Marginal
  cleanup of regression test cases, some more code commenting.

- Improve implementation of range-contains-element tests.  Implement
  these tests directly instead of constructing a singleton range and
  then applying range-contains.  This saves a range
  serialize/deserialize cycle as well as a couple of redundant
  bound-comparison steps, and adds very little code on net.  Remove
  elem_contained_by_range from the GiST opclass: it doesn't belong
  there because there is no way to use it in an index clause (where
  the indexed column would have to be on the left).  Its commutator is
  in the opclass, and that's what counts.

- Remove zero- and one-argument range constructor functions.  Per
  discussion, the zero-argument forms aren't really worth the catalog
  space (just write 'empty' instead).  The one-argument forms have
  some use, but they also have a serious problem with looking too much
  like functional cast notation; to the point where in many real
  use-cases, the parser would misinterpret what was wanted.
  Committing this as a separate patch, with the thought that we might
  want to revert part or all of it if we can think of some way around
  the cast ambiguity.

- Remove user-selectable ANALYZE option for range types.  It's not
  clear that a per-datatype typanalyze function would be any more
  useful than a generic typanalyze for ranges.  What is clear is
  that letting unprivileged users select typanalyze functions is a
  crash risk or worse.  So remove the option from CREATE TYPE AS
  RANGE, and instead put in a generic typanalyze function for ranges.
  The generic function does nothing as yet, but hopefully we'll
  improve that before 9.2 release.

- Creator of a range type must have permission to call support
  functions.  Since range types can be created by non-superusers, we
  need to consider their permissions.  Ideally we'd check this when
  the type is used, not when it's created, but that seems like much
  more trouble than it's worth.  The existing restriction that the
  support functions be immutable already prevents most cases where an
  unauthorized call to a function might be thought a security issue,
  and the fact that the user has no access to the results of the
  system's calls to subtype_diff closes off the other plausible reason
  for concern.  So this check is basically pro-forma, but let's make
  it anyway.

- Adjust range_adjacent to support different canonicalization rules.
  The original coding would not work for discrete ranges in which the
  canonicalization rule is to produce symmetric boundaries (either []
  or () style), as noted by Jeff Davis.  Florian Pflug pointed out
  that we could fix that by invoking the canonicalization function to
  see if the range "between" the two given ranges normalizes to empty.
  This implementation of Florian's idea is a tad slower than the
  original code, but only in the case where there actually is a
  canonicalization function --- if not, it's essentially the same
  logic as before.

- Some more editing of the range-types documentation.  Be more
  thorough about specifying the expectations for canonical and
  subtype_diff functions, and move that info to the same place.

- Fix unsupported options in CREATE TABLE ... AS EXECUTE.  The WITH
  [NO] DATA option was not supported, nor the ability to specify
  replacement column names; the former limitation wasn't even
  documented, as per recent complaint from Naoya Anzai.  Fix by moving
  the responsibility for supporting these options into the executor.
  It actually takes less code this way ...  catversion bump due to
  change in representation of IntoClause, which might affect stored

- Fix erroneous replay of GIN_UPDATE_META_PAGE WAL records.  A simple
  thinko in ginRedoUpdateMetapage, namely failing to increment a loop
  counter, led to inserting records into the last pending-list page in
  the wrong order (the opposite of that intended).  So far as I can
  tell, this would not upset the code that eventually flushes pending
  items into the main part of the GIN index.  But it did break the
  code that searched the pending list for matches, resulting in
  transient failure to find matching entries during index lookups, as
  illustrated in bug #6307 from Maksym Boguk.  Back-patch to 8.4 where
  the incorrect code was introduced.

- Fix overly-aggressive and inconsistent quoting in OS X start script.
  Sidar Lopez, per bug #6310, with some additional improvements by me.
  Back-patch to 9.0, where the issue was introduced.

- Make GiST index searches smarter about queries against empty ranges.
  In the cases where the result of the called proc is negated, we
  should explicitly test both inputs for empty, to ensure we'll never
  return "true" for an unsatisfiable query.  In other cases we can
  rely on the called proc to say the right thing.

- Use the proper macro to convert a bool to a Datum.  The original
  coding was var->value =3D (Datum) state; which is bogus, and then in
  commit 2f0f7b4bce13e68394543728801ef011fd82fac6 it was "corrected"
  to var->value =3D PointerGetDatum(state); which is a faithful
  translation but still wrong.  This seems purely cosmetic, though, so
  no need for a back-patch.  Pavel Stehule

- Improve GiST range-contained-by searches by adding a flag for empty
  ranges.  In the original implementation, a range-contained-by search
  had to scan the entire index because an empty range could be lurking
  anywhere.  Improve that by adding a flag to upper GiST entries that
  says whether the represented subtree contains any empty ranges.
  Also, make a simple mod to the penalty function to discourage empty
  ranges from getting pushed into subtrees without any.  This needs
  more work, and the picksplit function should be taught about it too,
  but that code can be improved without causing an on-disk
  compatibility break; so we'll leave it for another day.  Since we're
  breaking on-disk compatibility of range values anyway, I took the
  opportunity to reorganize the range flags bits; the unused
  RANGE_xB_NULL bits are now adjacent, which might open the door for
  using them in some other way later.  In passing, remove the GiST
  range opclass entry for <>, which doesn't seem like it can really be
  indexed usefully.  Alexander Korotkov, with some editorializing by
  Tom Lane.

- Use IEEE infinity, not 1e10, for null-and-not-null case in
  gistpenalty().  Use of a randomly chosen large value was never
  exactly graceful, and now that there are penalty functions that are
  intentionally using infinity, it doesn't seem like a good idea for
  null-vs-not-null to be using something less.

- Ensure that whole-row junk Vars are always of composite type.  The
  EvalPlanQual machinery assumes that whole-row Vars generated for the
  outputs of non-table RTEs will be of composite types.  However, for
  the case where the RTE is a function call returning a scalar type,
  we were doing the wrong thing, as a result of sharing code with a
  parser case where the function's scalar output is wanted.  (Or at
  least, that's what that case has done historically; it does seem a
  bit inconsistent.) To fix, extend makeWholeRowVar's API so that it
  can support both use-cases.  This fixes Belinda Cussen's report of
  crashes during concurrent execution of UPDATEs involving joins to
  the result of UNNEST() --- in READ COMMITTED mode, we'd run the
  EvalPlanQual machinery after a conflicting row update commits, and
  it was expecting to get a HeapTuple not a scalar datum from the
  "wholerowN" variable referencing the function RTE.  Back-patch to
  9.0 where the current EvalPlanQual implementation appeared.  In 9.1
  and up, this patch also fixes failure to attach the correct
  collation to the Var generated for a scalar-result case.  An
  example: regression=3D# select upper(x.*) from textcat('ab', 'cd') x;
  ERROR:  could not determine which collation to use for upper()

Simon Riggs pushed:

- Continue to allow VACUUM to mark last block of index dirty even when
  there is no work to do. Further analysis required.  Revert of patch

Peter Eisentraut pushed:

- Small markup and wording improvement

- Add pg_upgrade test suite.  It runs the regression tests, runs
  pg_upgrade on the populated database, and compares the before and
  after dumps.  While not actually a cross-version upgrade, this does
  detect omissions and bugs in the involved tools from time to time.
  It's also possible to do a cross-version upgrade by manually
  supplying parameters.

Robert Haas pushed:

- Check for INSERT privileges in SELECT INTO / CREATE TABLE AS.  In
  the normal course of events, this matters only if ALTER DEFAULT
  PRIVILEGES has been used to revoke default INSERT permission.
  Whether or not the new behavior is more or less likely to be what
  the user wants when dealing only with the built-in privilege
  facilities is arguable, but it's clearly better when using a
  loadable module such as sepgsql that may use the hook in
  ExecCheckRTPerms to enforce additional permissions checks.  KaiGai
  Kohei, reviewed by Laurenz Albe

- Move "hot" members of PGPROC into a separate PGXACT array.  This
  speeds up snapshot-taking and reduces ProcArrayLock contention.
  Also, the PGPROC (and PGXACT) structures used by two-phase commit
  are now allocated as part of the main array, rather than in a
  separate array, and we keep ProcArray sorted in pointer order.
  These changes are intended to minimize the number of cache lines
  that must be pulled in to take a snapshot, and testing shows a
  substantial increase in performance on both read and write workloads
  at high concurrencies.  Pavan Deolasee, Heikki Linnakangas, Robert

Bruce Momjian pushed:

- Add pg_upgrade ENABLE_SAME_CATVERSION_UPGRADES macro for testing to
  allow upgrades of the same catalog version.  (Doesn't work for
  tablespaces, as indicated by C comment.)

- Allow pg_upgrade to upgrade clusters that use exclusion contraints
  by fixing pg_dump to properly preserve such indexes.  Backpatch to
  9.1 and 9.0 (where the bug was introduced).

- Modify pg_dump to use error-free memory allocation macros.  This
  avoids ignoring errors and call-site error checking.


- Fix join_path_components() to not add a leading slash when joining
  to an initial null string.  Per report from Robert Haas in testing
  psql \ir.

- Move pg_dump memory routines into pg_dumpmem.c/h and restore
  common.c with its original functions.  The previous function
  migration would cause too many difficulties in back-patching.

Heikki Linnakangas pushed:

- Preserve SQLSTATE when an SPI error is propagated through PL/python
  exception handler. This was a regression in 9.1, when the capability
  to catch specific SPI errors was added, so backpatch to 9.1.  Mika
  Eloranta, with some editing by Jan Urba=C5=84ski.

- Take fillfactor into account in the new COPY bulk heap insert code.
  Jeff Janes

Alvaro Herrera pushed:

- Dump an unvalidated constraint separately from its table.  This
  allows possibly violating data to be imported before the constraint
  is installed.  Bug reported by Thom Brown

- Improve logging of autovacuum I/O activity.  This adds some I/O
  stats to the logging of autovacuum (when the operation takes long
  enough that log_autovacuum_min_duration causes it to be logged), so
  that it is easier to tune.  Notably, it adds buffer I/O counts
  (hits, misses, dirtied) and read and write rate.  Authors: Greg
  Smith and Noah Misch

- Fix unvalidated check constraints on domains, too.  Same bug as
  reported by Thom Brown for check constraints on tables: the
  constraint must be dumped separately from the domain, otherwise it
  is restored before the data and thus prevents potentially-violating
  data from being loaded in the first place.  Per Dean Rasheed

Andrew Dunstan pushed:

- Ensure plperl strings are always correctly UTF8 encoded.  Amit
  Khandekar and Alex Hunsaker.  Backpatched to 9.1 where the problem
  first occurred.

- Use the preferred version of xsubpp, not necessarily the one that
  came with the distro version of perl.  David Wheeler and Alex
  Hunsaker.  Backpatch to 9.1 where it applies cleanly. A simple
  workaround is available for earlier branches, and further effort
  doesn't seem warranted.

- Use the right interpreter for encoding test.

- Fix MSVC builds broken by xsubpp change

- Make pg_dumpall build with the right object files under MSVC.  This
  fixes a longstanding but up to now benign bug in the way pg_dumpall
  was built. The bug was exposed by recent code adjustments. The
  Makefile does not use $(OBJS) to build pg_dumpall, so this fix
  removes their source files from the pg_dumpall object and adds in
  the one source file it consequently needs.

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Mark Kirkwood sent in two revisions of a patch to allow renaming a
database which has backends connected to it.

Peter Geoghegan sent in four more revisions of a patch to inline
comparators as a performance optimization.

Alexander Korotkov sent in a WIP patch to allow index support for
regex operators.

Jan Urbanski sent in another revision of the patch to add cursor
support to PL/PythonU.

Pavel Stehule sent in a WIP patch to enable better support for
debugging overloaded functions in PL/pgsql.

Lars Kanis sent in two revisions of a patch to fix some infelicities
in certain versions of MSVC.

Pavel Stehule sent in a PoC patch to use errcontext for custom
exceptions in PL/pgsql.

Andrew Dunstan sent in another revision of a patch to add a \setenv
command to psql.

Dimitri Fontaine sent in another revision of the patch to add command

Peter Eisentraut sent in a patch to fix error reports in vpath builds.

Pavel Stehule sent in another revision of the patch to add CHECK
FUNCTION and CHECK TRIGGER functionality.

Andres Freund and Pavan Deolasee traded patches to avoid unneeded
computation of snapshots.

Peter Eisentraut sent in a patch to allow psql to report the line
number on which an error occurred when reading from stdin.

Ants Aasma sent in a patch to implement timing of shared buffer fills
and per relation stats collection of said timings.  Buffer flushes are
timed as well but aren't exposed per table because of difficulty of
correctly attributing them.


No Trackbacks


Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options