Notiziario settimanale PostgreSQL – 19 luglio 2009

(Traduzione parziale in lingua italiana delle PostgreSQL Weekly News a cura di Gianni Ciolli gianni.ciolli@2ndquadrant.it)

È iniziata la prima Commitfest del ciclo di sviluppo per la 8.5! http://commitfest.postgresql.org/

L’associazione ITPUG – Italian PostgreSQL Users Group si è unita al progetto di traduzione di PostgreSQL. Un gruppo di lavoro di 10 persone ha dato inizio all’impresa di "rendere più verde" la colonna di babel.postgresql.org dedicata alla lingua italiana. Ulteriori informazioni in italiano a http://traduzioni.itpug.org/

Novità sul prodotto PostgreSQL

Rilasciata la versione 3.1.2 di Archiveopteryx, un sistema di gestione per la posta elettronica basato su PostgreSQL. http://www.archiveopteryx.org/3.1.2

Rilasciata la versione 2.9 di Benetl, uno strumento ETL per files che usa PostgreSQL. http://www.benetl.net

Rilasciata la versione 2.0.6 di Npgsql2, un provider di dati .NET per PostgreSQL. http://www.npgsql.org

Rilasciata la versione 2.5.2 di PostgreDAC. http://microolap.com/products/connectivity/postgresdac/download/

Offerte di lavoro su PostgreSQL per luglio 2009

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

Notizie locali su PostgreSQL

Nei giorni 22 e 23 agosto 2009, a St. Augustin, in Germania, vicino a Bonn e Colonia, si terrà OpenSQL Camp. La scadenza per presentare comunicazioni è il 19 luglio 2009. Inviate le vostre proposte! http://opensqlcamp.org/Events/2009/Call_for_Participation

Domenica 23 agosto 2009 il PostgreSQL User Group tedesco avrà una dev room a FrOSCon. È aperto il Call for Papers: http://andreas.scherbaum.la/blog/archives/573-Call-for-Papers-fuer-den-PostgreSQL-Devroom-auf-der-FrOSCon-2009.html

PyCon Argentina si terrà in Buenos Aires nei giorni 4 e 5 settembre 2009. http://ar.pycon.org/2009/rfp/

Andreas (ads) Scherbaum terrà un corso "PostgreSQL for Corporate Use" al centro di formazione per adulti in Magdeburgo, Germania, dal 7 all’11 settembre 2009. Trovate i dettagli qua: http://andreas.scherbaum.la/blog/archives/574-PostgreSQL-als-Bildungsurlaub.html

Si terrà una conferenza in Athens, Georgia, USA il 19 settembre 2009. È aperto il CfP. http://www.postgresqlconference.org/2009/pgday/athens

Si terrà una conferenza a Seattle, Washington, USA nei giorni 16-18 ottobre 2009. È aperto il CfP. http://www.postgresqlconference.org/2009/west

È aperto il CfP per PGCon Brazil, che si svolgerà Il 23 e 24 ottobre 2009 presso Unicamp, nella città di Campinas, stato di San Paolo. http://pgcon.postgresql.org.br/2009/chamadas.en.php

Il PGDay.EU 2009 si terrà a Telecom ParisTech in Parigi nei giorni 6 e 7 novembre 2009. http://www.pgday.eu/

Gli organizzatori della conferenza per il decimo anniversario di JPUG stanno accettando proposte. La conferenza si terrà i giorni 20 e 21 novembre 2009 a Tokio. http://archives.postgresql.org/pgsql-announce/2009-05/msg00018.php

Rassegna stampa su PostgreSQL

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

Questo notiziario settimanale PostgreSQL è stato realizzato da David Fetter e Josh Berkus; traduzione parziale in lingua italiana a cura di Gianni Ciolli.

Notizie o annunci destinati a questo notiziario dovranno pervenire entro la mezzanotte di domenica (le 15 nel fuso orario della California). I comunicati in lingua italiana dovranno essere inviati a pwn@itpug.org; per le lingue inglese o tedesca, si scriva rispettivamente a david@fetter.org o a pwd@pgug.de.

Patch applicate

Peter Eisentraut committed:

• Update information schema to SQL:2008: Add yes_or_no domain for "boolean" data. Add new columns for VIEWS view. Slight section renumbering.

• Rearrangement of the HTML docs build rules. Set up proper makefile dependencies in the documentation build rules, especially around the HTML/index build. The problem we’ve had with all previous solutions is that we have used the same file name, such as HTML.index or bookindex.sgml, to mean different things at different stages of the build, and make can’t distinguish that. The solution here is that the first jade run produces HTML.index, but does not require bookindex.sgml at all, and produces no other html output (the latter an idea from Alvaro). The second jade run includes bookindex.sgml, but does not recreate HTML.index. That way, when you change an sgml file, jade is run twice and at the end all dependencies are satisfied. Omitting the html output in the first stage also makes the full build a lot faster. When you run one of the print format targets, only the first jade run is run, then the print target-specific commands. If an HTML build has completed previously, the first jade run is skipped because the dependencies have already been satisfied. The draft and check targets for quick builds and syntax verification are still there.

• Make backend header files C++ safe. This alters various incidental uses of C++ key words to use other similar identifiers, so that a C++ compiler won’t choke outright. You still (probably) need extern "C" { }; around the inclusion of backend headers. Based on a patch by Kurt Harriman. Also add a script cpluspluscheck to check for C++ compatibility in the future. As of right now, this passes without error for me.

Bruce Momjian committed:

• In pgsql/contrib/vacuumlo/vacuumlo.c, vacuumlo query cleanups. No need for VACUUM ANAYZE of newly created/populated temp table, just use analyze. No need to apologize for using subquery in DELETE anymore.

Tom Lane committed:

• In pgsql/src/backend/utils/cache/plancache.c, do a conditional SPI_push/SPI_pop when replanning a query in RevalidateCachedPlan. This is to avoid a "SPI_ERROR_CONNECT" failure when the planner calls a SPI-using function and we are already inside one. The alternative fix is to expect callers of RevalidateCachedPlan to do this, which seems likely to result in additional hard-to-detect bugs of omission. Per reports from Frank van Vugt and Marek Lewczuk. Back-patch to 8.3. It’s much harder to trigger the bug in 8.3, due to a smaller set of cases in which plans can be invalidated, but it could happen. (I think perhaps only a SI reset event could make 8.3 fail here, but that’s certainly within the realm of possibility.)

• Tweak the core scanner so that it can be used by plpgsql too. Changes: Pass in the keyword lookup array instead of having it be hardwired. (This incidentally allows elimination of some duplicate coding in ecpg.) Re-order the token declarations in gram.y so that non-keyword tokens have numbers that won’t change when keywords are added or removed. Add ".." and ":=" to the set of tokens recognized by scan.l. (Since these combinations are nowhere legal in core SQL, this does not change anything except the precise wording of the error you get when you write this.)

• In pgsql/src/interfaces/ecpg/preproc/ecpg.tokens, remove duplicate definition of TYPECAST token. (Apparently, some but not all versions of Bison will warn about this.)

• Add erand48() to the set of functions supported by our src/port/ library, and extend configure to test for it properly instead of hard-wiring an assumption that everybody but Windows has the rand48 functions. (We do cheat to the extent of assuming that probing for erand48 will do for the entire rand48 family.) erand48() is unused as of this commit, but a followon patch will cause GEQO to depend on it. Andres Freund, additional hacking by Tom Lane.

• Make GEQO’s planning deterministic by having it start from a predictable random number seed each time. This is how it used to work years ago, but we got rid of the seed reset because it was resetting the main random() sequence and thus having undesirable effects on the rest of the system. To fix, establish a private random number state for each execution of geqo(), and initialize the state using the new GUC variable geqo_seed. People who want to experiment with different random searches can do so by changing geqo_seed, but you’ll always get the same plan for the same value of geqo_seed (if holding all other planner inputs constant, of course). The new state is kept in PlannerInfo by adding a "void *" field reserved for use by join_search hooks. Most of the rather bulky code changes in this commit are just arranging to pass PlannerInfo around to all the GEQO functions (many of which formerly didn’t receive it). Andres Freund, with some editorialization by Tom Lane.

• Repair bug #4926 "too few pathkeys for mergeclauses". This example shows that the sanity checking I added to create_mergejoin_plan() in 8.3 was a few bricks shy of a load: the mergeclauses could reference pathkeys in a noncanonical order such as x,y,x, not only cases like x,x,y which is all that the code had allowed for. The odd cases only turn up when using redundant clauses in an outer join condition, which is why no one had noticed before.

• Fix error cleanup failure caused by 8.4 changes in plpgsql to try to avoid memory leakage in error recovery. We were calling FreeExprContext, and therefore invoking ExprContextCallback callbacks, in both normal and error exits from subtransactions. However this isn’t very safe, as shown in recent trouble report from Frank van Vugt, in which releasing a tupledesc refcount failed. It’s also unnecessary, since the resources that callbacks might wish to release should be cleaned up by other error recovery mechanisms (ie the resource owners). We only really want FreeExprContext to release memory attached to the exprcontext in the error-exit case. So, add a bool parameter to FreeExprContext to tell it not to call the callbacks. A more general solution would be to pass the isCommit bool parameter on to the callbacks, so they could do only safe things during error exit. But that would make the patch significantly more invasive and possibly break third-party code that registers ExprContextCallback callbacks. We might want to do that later in HEAD, but for now I’ll just do what seems reasonable to back-patch.

• Fix a thinko in join_is_legal: when we decide we can implement a semijoin by unique-ifying the RHS and then inner-joining to some other relation, that is not grounds for violating the RHS of some other outer join. Noticed while regression-testing new GEQO code, which will blindly follow any path that join_is_legal says is legal, and then complain later if that leads to a dead end. I’m not certain that this can result in any visible failure in 8.4: the mistake may always be masked by the fact that subsequent attempts to join the rest of the RHS of the other join will fail. But I’m not certain it can’t, either, and it’s definitely not operating as intended. So back-patch. The added regression test depends on the new no-failures-allowed logic that I’m about to commit in GEQO, so no point back-patching that.

• Rewrite GEQO’s gimme_tree function so that it always finds a legal join sequence, even when the input "tour" doesn’t lead directly to such a sequence. The stack logic that was added in 2004 only supported cases where relations that had to be joined to each other (due to join order restrictions) were adjacent in the tour. However, relying on a random search to figure that out is tremendously inefficient in large join problems, and could even fail completely (leading to "failed to make a valid plan" errors) if random_init_pool ran out of patience. It seems better to make the tour-to-plan transformation a little bit fuzzier so that every tour can form a legal plan, even though this means that apparently different tours will sometimes yield the same plan. In the same vein, get rid of the logic that knew that tours (a,b,c,d,…) are the same as tours (b,a,c,d,…), and therefore insisted the latter are invalid. The chance of generating two tours that differ only in this way isn’t that high, and throwing out 50% of possible tours to avoid such duplication seems more likely to waste valuable genetic- refinement generations than to do anything useful. This leaves us with no cases in which geqo_eval will deem a tour invalid, so get rid of assorted kluges that tried to deal with such cases, in particular the undocumented assumption that DBL_MAX is an impossible plan cost. This is all per testing of Robert Haas’ lets-remove-the-collapse-limits patch. That idea has crashed and burned, at least for now, but we still got something useful out of it. It’s possible we should back-patch this change, since the "failed to make a valid plan" error can happen in existing releases; but I’d rather not until it has gotten more testing.

• Teach simplify_boolean_equality to simplify the forms foo <> true and foo <> false, along with its previous duties of simplifying foo = true and foo = false. (All of these are equivalent to just foo or NOT foo as the case may be.) It’s not clear how often this is really useful; but it costs almost nothing to do, and it seems some people think we should be smart about such cases. Per recent bug report.

• In pgsql/src/bin/psql/describe.c, remove unnecessary and version-sensitive dependence on the exact set of column names to be found in a sequence. Per gripe from Bruce Momjian.

Heikki Linnakangas committed:

• In pgsql/contrib/pg_standby/Makefile, pg_standby doesn’t need to be linked with libpq. Fujii Masao.

Andrew Dunstan committed:

• DROP IF EXISTS for columns and constraints. Andres Freund.

Patch rifiutate (per adesso)

No one was disappointed this week :-)

Patch in coda

Heikki Linnakangas sent in two revisions of a patch which helps set up index-only scans.

Robert Haas sent in an updated patch for Hot Standby.

Petr (PJMODOS) Jelinek sent in three revisions of a patch to add DefaultACLs http://wiki.postgresql.org/wiki/DefaultACL

ITAGAKI Takahiro sent in two revisions of a patch to add FORCE QUOTE to COPY…CSV.

KaiGai Kohei sent in three more revisions of his SE-PostgreSQL patch.

Jeff Davis sent in another revision of his patch to make more generalized constraints.

Andres Freund sent in a patch to refactor *_collapse_limit and geqo_threshold.

Jaime Casanova sent in a revision of Taro (higepon) Minowa’s patch to allow pg_dump* to dump comments on commposite-type and index columns.

ITAGAKI Takahiro sent in an update for the sampling profiler patch.

Tsutomu Yamada sent in two revisions of a patch to fix the ""could not reattach to shared memory" bug on Windows.

Fujii Masao sent in two more revisions of the Synchronous Replication patch.

Oleg Bartunov sent in a patch which adds prefix support for synonym dictionaries in full-text search.

Oleg Bartunov sent in a patch to add filtering dictionaries and a new contrib module, unaccent, which removes accents, using same.

Sergey V. Karpov sent in a patch which extends the functionality of dict_xsyn extended synonym dictionary.

Fernando Ike de Oliveira sent in two revisions of a patch to add dL for PL languages to psql.

Zoltan Boszormenyi sent in another revision of his patch set to improve ECPG’s Informix compatibility layer.

Tom Lane sent in a WIP patch to make PL/pgsql use the core lexer.

Caleb Welton sent in another revision of his patch to add bytea to PL/PythonU.

Andrew (RhodiumToad) Gierth sent in another revision of the hstore patch with fixes and new tests for some edge case bugs.

Kevin Grittner sent in a patch to revise parallel pg_restore’s scheduling heuristic.

Steve Prentice sent in another revision of the patch to make PL/pgsql treat any word following AS as a label.

Petr (PJMODOS) Jelinek sent in two revisions of a patch to implement GRANT ON ALL IN schema.

Richard Huxton sent in a patch to create a navigation menu for the documentation.

Fujii Masao sent in another revision of the patch to multiplex SIGUSR1, which is infrastructure for the Synchronous Replication feature.

Zoltan Boszormenyi sent in a patch to add struct support for SELECT INTO to ECPG.

Peter Eisentraut sent in a patch to make PL/Python use errcontext() instead of passing the function names around to use in error messages.

Alan Li sent in a patch to optimize MIN and MAX queries on partitioned tables.

Andreas Wenk sent in a patch to include the du+ option in ? output in psql.

Bernd Helmle sent in a revision of Pavel Stehule’s patch to allow mixed and named notation support for PL/pgsql functions.

Dean Rasheed sent in another revision of the deferrable UNIQUE constraints patch.

Jeremy Kerr sent in another revision of the patch to avoid manual shift-and-test logic in AllocSetFreeIndex.

Andreas Freund sent in another revision of his patch to allow machine-readable EXPLAIN output.

Andreas Freund sent in two more revisions of his generic EXPLAIN options patch.