Skip to content

Wöchentlicher PostgreSQL Newsletter - 21. November 2010

Der Originalartikel befindet sich unter:

== Wöchentlicher PostgreSQL Newsletter - 21. November 2010 ==

== PostgreSQL Produkt Neuigkeiten ==

DiffKit 0.8.2, ein Vergleichswerkzeug für Tabellen mit Support
für PostgreSQL, ist erschienen.

PostgreDAC 2.6.0, ein Delphi/C++ Builder für PostgreSQL,
ist erschienen.

== PostgreSQL Jobs im November ==

== PostgreSQL Lokal ==

Der Call für Projekte des PostgreSQLFr hat begonnen. Projekte müssen
sich um PostgreSQL drehen und die französisch-sprechende Community
betreffen. Maile Projektvorschläge an appel-projets-2010
AT postgresql DOT fr.

Registrierung für PGDay.EU 2010 vom 6. bis 8. Dezember in
Stuttgart, Deutschland ist jetzt offen.

Frühbucherregistrierung für PGDay.IT am 10. Dezember 2010 in Rom,
Italien ist jetzt offen und der Vortragsplan ist online.

FOSDEM ist eine der größten Freien und Open Source Veranstaltungen
und findet jährlich in Brüssel, Belgien statt, nächstes Jahr am
5. und 6. Februar 2011. Ungefähr 4.000 Besucher finden sich dort ein.
Sende deine Vorträge an fosdem AT postgresql DOT eu.

PGDay findet dieses Jahr während der südkalifornischen Linux Ausstellung
(SCALE) im LAX Hilton Hotel in Los Angeles, Kalifornien am Freitag dem
25. Februar 2011 statt. Vorträge kannst du unter pgday-submissions AT
googlegroups DOT com einreichen.

PGCon will be May 19-20, 2011 at the University of Ottawa, preceded by
two days of tutorials on May 17-18.

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

== Reviews ==

Steve Singer reviewed the patch to extend ALTER TABLE to allow
creating a primary key from an extant index, suggesting some changes
in the documentation and error handling.

Robert Haas reviewed the KNN-GiST patch, pointing out places where it
re-uses machinery with now-misleading names, along with some cases it
doesn't handle.

Robert Haas reviewed the patch for ALTER OBJECT ... SET SCHEMA, noting
a lack of regression tests and tab completion support for psql.

== Applied Patches ==

Simon Riggs pushed:

- Avoid spurious Hot Standby conflicts from btree delete records.
  Similar conflicts were already avoided for related record types.
  Massive over-caution resulted in a usability bug. Clear theoretical
  basis for doing this is now confirmed by me.  Request to remove from
  Heikki (twice), over-caution by me.;a=commitdiff;h=52010027efc8757fdd830a4d0113763a501259bc

Alvaro Herrera pushed:

- Fix copy-and-pasteo.;a=commitdiff;h=ae4b17edeebc6670ba222a8cfbb1658d02203d29

- Add pg_describe_object function.  This function is useful to obtain
  textual descriptions of objects as stored in pg_depend.;a=commitdiff;h=6cc2deb86e9183262493a6537700ee305fb3e096

Robert Haas pushed:

- Fix copy-and-pasteo a little more completely.  copydir.c is no
  longer in src/port;a=commitdiff;h=20cf8ae478c2712d4c211b49868e334357f05356

- Remove outdated comments from the regression test files.  Since
  2004, int2 and int4 operators do detect overflow; this was fixed by
  commit 4171bb869f234281a13bb862d3b1e577bf336242.  Extracted from a
  larger patch by Andres Freund.;a=commitdiff;h=8d70ed84ba577abf9e985518024fb92e2081dac9

- Add new buffers_backend_fsync field to pg_stat_bgwriter.  This new
  field counts the number of times that a backend which writes a
  buffer out to the OS must also fsync() it.  This happens when the
  bgwriter fsync request queue is full, and is generally detrimental
  to performance, so it's good to know when it's happening.  Along the
  way, log a new message at level DEBUG1 whenever we fail to hand off
  an fsync, so that the problem can also be seen in examination of log
  files (if the logging level is cranked up high enough).  Greg Smith,
  with minor tweaks by me.;a=commitdiff;h=3134d8863e8473e3ed791e27d484f9e548220411

- Document the dummy_seclabel contrib module.  KaiGai Kohei, with
  editing and markup fixes by me.;a=commitdiff;h=45768d10e3abd513b4c959efeb5907798f2fac3f

- Minor corrections to dummy_seclabel documentation.  Problems noted
  by Thom Brown.;a=commitdiff;h=1fc2d60d8c9d8096373e94a8f8c9b28d2082b7d3

- Speed up conversion of signed integers to C strings.  A hand-coded
  implementation turns out to be much faster than calling printf().
  In passing, add a few more regresion tests.  Andres Freund, with
  assorted, mostly cosmetic changes.;a=commitdiff;h=4fc115b2e981f8c63165ca86a23215380a3fda66

- Attempt to fix breakage caused by signed integer conversion patch.
  Use INT_MIN rather than INT32_MIN as we do elsewhere in the code,
  and try to work around nonexistence of INT64_MIN if necessary.
  Adjust the new regression tests to something hopefully saner, per
  observation by Tom Lane.;a=commitdiff;h=815810ed315a6d21203ec75a11f742f5ed655418

- Update int8-exp-three-digits.out to match new contents of int8.out.;a=commitdiff;h=e8bf683fbee935181e207d7171630194ed01f6a1

- Expose quote_literal_cstr() from core.  This eliminates the need for
  inefficient implementions of this functionality in both
  contrib/dblink and contrib/tablefunc, so remove them.  The upcoming
  patch implementing an in-core format() function will also require
  this functionality.  In passing, add some regression tests.;a=commitdiff;h=4343c0e546b216ab38a3397a4f0f7476d557b352

- Minor cleanup of indxpath.c.  Eliminate some superfluous notational
  complexity around match_clause_to_indexcol(), and rip out the
  DoneMatchingIndexKeys crock.;a=commitdiff;h=99bc012d5198bdfd2aa67336f645dbf887bb0ec9

- Add new SQL function, format(text).  Currently, three conversion
  format specifiers are supported: %s for a string, %L for an SQL
  literal, and %I for an SQL identifier.  The latter two are
  deliberately designed not to overlap with what sprintf() already
  supports, in case we want to add more of sprintf()'s functionality
  here later.  Patch by Pavel Stehule, heavily revised by me.
  Reviewed by Jeff Janes and, in earlier versions, by Itagaki Takahiro
  and Tom Lane.;a=commitdiff;h=750487077802861a2accd94164166747ec311fa4

- Bump catversion.  Should have done this as part of format(text)

- Put back accidentally-deleted quote_literal() regression tests.;a=commitdiff;h=95dacf85939b213584ae5d50cbddf87eb687e582

Tom Lane pushed:

- Fix aboriginal mistake in plpython's set-returning-function support.
  We must stay in the function's SPI context until done calling the
  iterator that returns the set result.  Otherwise, any attempt to
  invoke SPI features in the python code called by the iterator will
  malfunction.  Diagnosis and patch by Jan Urbanski, per bug report
  from Jean-Baptiste Quenot.  Back-patch to 8.2; there was no support
  for SRFs in previous versions of plpython.;a=commitdiff;h=add0ea88e7722b48d1f3a7c38e3cfd7f1e854674

- Make TRUNCATE ... RESTART IDENTITY restart sequences
  transactionally.  In the previous coding, we simply issued ALTER
  SEQUENCE RESTART commands, which do not roll back on error.  This
  meant that an error between truncating and committing left the
  sequences out of sync with the table contents, with potentially bad
  consequences as were noted in a Warning on the TRUNCATE man page.
  To fix, create a new storage file (relfilenode) for a sequence that
  is to be reset due to RESTART IDENTITY.  If the transaction aborts,
  we'll automatically revert to the old storage file.  This acts just
  like a rewriting ALTER TABLE operation.  A penalty is that we have
  to take exclusive lock on the sequence, but since we've already got
  exclusive lock on its owning table, that seems unlikely to be much
  of a problem.  The interaction of this with usual nontransactional
  behaviors of sequence operations is a bit weird, but it's hard to
  see what would be completely consistent.  Our choice is to discard
  cached-but-unissued sequence values both when the RESTART is
  executed, and at rollback if any; but to not touch the currval()
  state either time.  In passing, move the sequence reset operations
  to happen before not after any AFTER TRUNCATE triggers are fired.
  The previous ordering was not logically sensible, but was forced by
  the need to minimize inconsistency if the triggers caused an error.
  Transactional rollback is a much better solution to that.  Patch by
  Steve Singer, rather heavily adjusted by me.;a=commitdiff;h=511e902b51c2a1c0d012426ceb6486b1202120f3

- Further fallout from the MergeAppend patch.  Fix things so that
  top-N sorting can be used in child Sort nodes of a MergeAppend node,
  when there is a LIMIT and no intervening joins or grouping.
  Actually doing this on the executor side isn't too bad, but it's a
  bit messier to get the planner to cost it properly.  Per gripe from
  Robert Haas.  In passing, fix an oversight in the original
  top-N-sorting patch: query_planner should not assume that a LIMIT
  can be used to make an explicit sort cheaper when there will be
  grouping or aggregation in between.  Possibly this should be
  back-patched, but I'm not sure the mistake is serious enough to be a
  real problem in practice.;a=commitdiff;h=6fbc323c8042303a737028f9da7616896bccc517

- Dept of second thoughts: don't try to push LIMIT below a SRF.  If we
  have Limit->Result->Sort, the Result might be projecting a tlist
  that contains a set-returning function.  If so, it's possible for
  the SRF to sometimes return zero rows, which means we could need to
  fetch more than N rows from the Sort in order to satisfy LIMIT N.
  So top-N sorting cannot be used in this scenario.;a=commitdiff;h=48c348f86ce09c668af7cf271757c3f156f28344

- Improve plpgsql's error reporting for no-such-column cases.  Given a
  column reference, where there is a composite plpgsql
  variable foo but it doesn't contain a column bar, the pre-9.0 coding
  would immediately throw a "record foo has no field bar" error.  In
  9.0 the parser hook instead falls through to let the core parser see
  if it can resolve the reference.  If not, you get a complaint about
  "missing FROM-clause entry for table foo", which while in some sense
  correct isn't terribly helpful.  Complicate things a bit so that we
  can throw the old error message if neither the core parser nor the
  hook are able to resolve the column reference, while not changing
  the behavior in any other case.  Per bug #5757 from Andrey Galkin.;a=commitdiff;h=fe24d781612700646bfb3e08925e34c43926f9df

- Improve relation width estimation for subqueries.  As per the
  ancient comment for set_rel_width, it really wasn't much good for
  relations that aren't plain tables: it would never find any stats
  and would always fall back on datatype-based estimates, which are
  often pretty silly.  Fix that by copying up width estimates from the
  subquery planning process.  At some point we might want to do this
  for CTEs too, but that would be a significantly more invasive patch
  because the sub-PlannerInfo is no longer accessible by the time it's
  needed.  I refrained from doing anything about that, partly for fear
  of breaking the unmerged CTE-related patches.  In passing, also
  generate less bogus width estimates for whole-row Vars.  Per a gripe
  from Jon Nelson.;a=commitdiff;h=0f61d4dd1b4f95832dcd81c9688dac56fd6b5687

- Fix leakage of cost_limit when multiple autovacuum workers are
  active.  When using default autovacuum_vac_cost_limit,
  autovac_balance_cost relied on VacuumCostLimit to contain the
  correct global value ... but after the first time through in a
  particular worker process, it didn't, because we'd trashed it in
  previous iterations.  Depending on the state of other autovac
  workers, this could result in a steady reduction of the effective
  cost_limit setting as a particular worker processed more and more
  tables, causing it to go slower and slower.  Spotted by Simon Poole
  (bug #5759).  Fix by saving and restoring the GUC variables in the
  loop in do_autovacuum.  In passing, improve a few comments.
  Back-patch to 8.3 ... the cost rebalancing code has been buggy since
  it was put in.;a=commitdiff;h=b58c25055ef6d7097618c680f6768689a110d529

- Assorted further cleanup for integer-conversion patch.  Avoid
  depending on LL notation, which is likely to not work in pre-C99
  compilers; don't pointlessly use INT32_MIN/INT64_MIN in code that
  has the numerical value hard-wired into it anyway; remove some
  gratuitous style inconsistencies between pg_ltoa and pg_lltoa; fix
  int2 test case so it actually tests int2.;a=commitdiff;h=d1d8462d99620cd4384d77b0908d50a49c38eb31

- Further cleanup of indxpath logic related to IndexOptInfo.opfamily
  array.  We no longer need the terminating zero entry in opfamily[],
  so get rid of it.  Also replace assorted ad-hoc looping logic with
  simple for and foreach constructs.  This code is now noticeably more
  readable than it was an hour ago; credit to Robert for seeing that
  it could be simplified.;a=commitdiff;h=89a368418c56125f79f46a81229e824d519f9718

Heikki Linnakangas pushed:

- The GiST scan algorithm uses LSNs to detect concurrent pages splits,
  but temporary indexes are not WAL-logged.  We used a constant LSN
  for temporary indexes, on the assumption that we don't need to worry
  about concurrent page splits in temporary indexes because they're
  only visible to the current session.  But that assumption is wrong,
  it's possible to insert rows and split pages in the same session,
  while a scan is in progress.  For example, by opening a cursor and
  fetching some rows, and INSERTing new rows before fetching some
  more.   Fix by generating fake increasing LSNs, used in place of
  real LSNs in temporary GiST indexes.;a=commitdiff;h=2edc5cd493ce3d7834026970e9d3cd00e203f51a

- Remove unused parameter. Patch by Shigeru Hanada.;a=commitdiff;h=ecf70b916b480ce26c710942f9111115d9e63e88

Magnus Hagander pushed:

- Send paramHandle to subprocesses as 64-bit on Win64.  The handle to
  the shared memory segment containing startup parameters was sent as
  32-bit even on 64-bit systems.  Since HANDLEs appear to be allocated
  sequentially this shouldn't be a problem until we reach 2^32 open
  handles in the postmaster, but a 64-bit value should be sent across
  as 64-bit, and not zero out the top 32 bits.  Noted by Tom Lane.;a=commitdiff;h=4acf99b2f3bb665d2febef183ede5f08b8f7545d

Andrew Dunstan pushed:

- Require VALUE keyword when extending an enum type. Based on a patch
  from Alvaro Herrera.;a=commitdiff;h=b7fcf68e8632e757a50352c9567bb630ed285201

Peter Eisentraut pushed:

- Additional fixes for parallel make.  Add some additional
  dependencies to constrain the build order to prevent parallel make
  from failing.  In the case of src/Makefile, this is likely to be too
  complicated to be worth maintaining, so just add .NOTPARALLEL to get
  the old for-loop-like behavior.  More fine-tuning might be necessary
  for some platforms or configurations.;a=commitdiff;h=cfad144f894b306fc300f5d03ea52a32d4624db0

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Alexander Korotkov sent in two revisions of a patch to fix the
picksplit function for contrib/seg.

Robert Haas sent in another revision of the patch to add custom code
for int(32|64)-to-text conversions.  These are much faster than the
older code.

Peter Eisentraut sent in a patch intended to fix some misbehavior in
parallel make on Darwin.

Peter Eisentraut sent in another revision of the patch to add
per-column collation.

Greg Sabino Mullane sent in a patch to fix an infelicity with the
clang compiler, which doesn't like to see gcc directives.

Robert Haas sent in another set of WIP patches to implement unlogged

Heikki Linnakangas sent in a patch to rewrite how GiST does its
insertions for better behavior with WAL logging.

KaiGai Kohei sent in another revision of the patch to allow switching
security labels.

Alvaro Herrera sent in a patch to add a describe_object function.

ITAGAKI Takahiro sent in another WIP patch to implement MULTISETs and
add some new functions for ARRAYs.

Fujii Masao sent in a patch to fix the documentation of libpq for
synchronous replication.

Marko (johto) Tiikkaja sent in a patch to have EXPLAIN ANALYZE show
the nubmer of rows a plan qual filtered in from a node's input.

Pavel Stehule sent in another revision of the patch to add formatting

Shigeru HANADA sent in another revision of the SQL/MED patch.

Bruce Momjian sent in another revision of the patch to fix some errors
in libpq's handling of host and addresses for IPv4 and IPv6.

Radoslaw Smogura sent in an experimental JDBC driver.

Dimitri Fontaine sent in another revision of the patch to add ALTER
OBJECT ... SET SCHEMA to those objects where this is not already

Joachim Wieland sent in two more patches in support of parallel
pg_dump, one for modular I/O compression in pg_dump, the other for a
directory-structured dump format.


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