== Postgres Weekly News - April 06 2008 == == Postgres Product News ==
Benetl 1.7 released. http://www.benetl.net check_postgres 1.4.1 released. http://bucardo.org/nagios/ phpPgAdmin 4.2 released. http://phppgadmin.sourceforge.net/ == Postgres Jobs for April == http://archives.postgresql.org/pgsql-jobs/2008-04/threads.php == Postgres Local == FISL 9.0 will be April 17-19 at PUCRS in Porto Alegre, RS, Brazil. https://fisl.softwarelivre.org/9.0/ PostgreSQL Day Unicamp 2008 will be on April 22 in Campinas, SP, Brazil. http://www.dextra.com.br/dia-postgresql.htm PGCon 2008 will be May 20-23 in Ottawa. http://www.pgcon.org/2008/ Utah Open Source Conference 2008's CfP is open through June 1. This 2nd annual conference is August 28-30, 2008 in Salt Lake City, UT http://2008.utosc.com/ == Postgres in the News == Planet PostgreSQL: http://www.planetpostgresql.org/ General Bits, Archives and occasional new articles: http://www.varlena.com/GeneralBits/ Postgres Weekly News is brought to you this week by David Fetter Submit news and announcements by Sunday at 3:00pm Pacific time. Please send English language ones to [EMAIL PROTECTED], German language to [EMAIL PROTECTED], Italian language to [EMAIL PROTECTED] == Applied Patches == Tom Lane committed: - Apply my original fix for Taiki Yamaguchi's bug report about DISTINCT MAX(). Add some regression tests for plausible failures in this area. - Fix an oversight I made in a cleanup patch over a year ago: eval_const_expressions needs to be passed the PlannerInfo ("root") structure, because in some cases we want it to substitute values for Param nodes. (So "constant" is not so constant as all that ...) This mistake partially disabled optimization of unnamed extended-Query statements in 8.3: in particular the LIKE-to-indexscan optimization would never be applied if the LIKE pattern was passed as a parameter, and constraint exclusion depending on a parameter value didn't work either. - Add SPI-level support for executing SQL commands with one-time-use plans, that is commands that have out-of-line parameters but the plan is prepared assuming that the parameter values are constants. This is needed for the plpgsql EXECUTE USING patch, but will probably have use elsewhere. This commit includes the SPI functions and documentation, but no callers nor regression tests. The upcoming EXECUTE USING patch will provide regression-test coverage. I thought committing this separately made sense since it's logically a distinct feature. - Support EXECUTE USING in plpgsql. Pavel Stehule, with some improvements by myself. - Revert my bad decision of about a year ago to make PortalDefineQuery responsible for copying the query string into the new Portal. Such copying is unnecessary in the common code path through exec_simple_query, and in this case it can be enormously expensive because the string might contain a large number of individual commands; we were copying the entire, long string for each command, resulting in O(N^2) behavior for N commands. (This is the cause of bug #4079.) A second problem with it is that PortalDefineQuery really can't risk error, because if it elog's before having set up the Portal, we will leak the plancache refcount that the caller is trying to hand off to the portal. So go back to the design in which the caller is responsible for making sure everything is copied into the portal if necessary. - Teach ANALYZE to distinguish dead and in-doubt tuples, which it formerly classed all as "dead"; also get it to count DEAD item pointers as dead rows, instead of ignoring them as before. Also improve matters so that tuples previously inserted or deleted by our own transaction are handled nicely: the stats collector's live-tuple and dead-tuple counts will end up correct after our transaction ends, regardless of whether we end in commit or abort. While there's more work that could be done to improve the counting of in-doubt tuples in both VACUUM and ANALYZE, this commit is enough to alleviate some known bad behaviors in 8.3; and the other stuff that's been discussed seems like research projects anyway. Pavan Deolasee and Tom Lane - Remove heap_release_fetch, which is no longer used anywhere; this simplifies heap_fetch a little. - Add a variant of the Levenshtein string-distance function that lets the user specify the cost values to use, instead of always using 1's. Volkan Yazici. In passing, remove fuzzystrmatch.h, which contained a bunch of stuff that had no business being in a .h file; fold it into its only user, fuzzystrmatch.c. - In pgsql/src/backend/utils/misc/guc.c, remove no-longer-used function assign_backslash_quote(). - Re-implement division for numeric values using the traditional "schoolbook" algorithm. This is a good deal slower than our old roundoff-error-prone code for long inputs, so we keep the old code for use in the transcendental functions, where everything is approximate anyway. Also create a user-accessible function div(numeric, numeric) to provide access to the exact result of trunc(x/y) --- since the regular numeric / operator will round off its result, simply computing that expression in SQL doesn't reliably give the desired answer. This fixes bug #3387 and various related corner cases, and improves the usefulness of PG for high-precision integer arithmetic. - Defend against JOINs having more than 32K columns altogether. We cannot currently support this because we must be able to build Vars referencing join columns, and varattno is only 16 bits wide. Perhaps this should be improved in future, but considering that it never came up before, I'm not sure the problem is worth much effort. Per bug #4070 from Marcello Ceschia. The problem seems largely academic in 8.0 and 7.4, because they have (different) O(N^2) performance issues with such wide joins, but back-patch all the way anyway. - In pgsql/src/bin/psql/mainloop.c, a small visit from the portability and localization police. - Improve hash_any() to use word-wide fetches when hashing suitably aligned data. This makes for a significant speedup at the cost that the results now vary between little-endian and big-endian machines; which forces us to add explicit ORDER BYs in a couple of regression tests to preserve machine-independent comparison results. Also, force initdb by bumping catversion, since the contents of hash indexes will change (at least on big-endian machines). Kenneth Marshall and Tom Lane, based on work from Bob Jenkins. This commit does not adopt Bob's new faster mix() algorithm, however, since we still need to convince ourselves that that doesn't degrade the quality of the hashing. - Make plpgsql support FOR over a query specified by a cursor declaration, for improved compatibility with Oracle. Pavel Stehule, with some fixes by me. Bruce Momjian committed: - Remove from TODO due to survey/discussion: "Prefix command-line utilities like createuser with 'pg_'" - Add URLs for TODO: "Add SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT." - Add to TODO: "Improve how ANALYZE computes in-doubt tuples." - Add Wiki URLs for TODO: "Allow encoding on a per-column basis optionally using the ICU library." - Implement current_query(), that shows the currently executing query. At the same time remove dblink/dblink_current_query() as it is no longer necessary *BACKWARD COMPATIBILITY ISSUE* for dblink. Tomas Doran - In pgsql/contrib/dblink/dblink.c, remove unneed #include now that current_query() has moved to the backend. - Allow 'help' in psql to show \? help, for novice assistance. Greg Sabino Mullane. - Have psql command 'help' suggest the use of \?, updated version. Greg Sabino Mullane - Have pg_stop_backup() wait for all archive files to be sent, rather than returing right away. This guarantees that when pg_stop_backup() returns, you have a valid backup. Simon Riggs - Re-add dblink_current_query() for backward compatibility. - Make dblink_current_query() reference pg_catalog.current_query(), per Tom. - In pgsql/doc/src/sgml/func.sgml, add documentation clarification for IS [NOT] NULL and row-valued expressions. - Add to TODO: "Add ability to obfuscate function bodies." Magnus Hagander committed: - Convert three more guc settings to enum type: default_transaction_isolation, session_replication_role and regex_flavor. - In pgsql/src/backend/utils/misc/guc.c, convert syslog_facility guc to enum type. - In pgsql/src/backend/utils/misc/guc.c, oops, add proper #ifdef for systems without support for syslog. Per buildfarm member mastodon. - Turn xmlbinary and xmloption GUC variables into enums. - Convert backslash_quote guc to use enum. - In pgsql/src/backend/parser/scan.l, oops, change should go in scan.l to survive a clean checkout and not just a make clean... Peter Eisentraut committed: - In pgsql/doc/src/sgml/cvs.sgml, remove -C from rsync call, because it omits directories named "core". == Rejected Patches (for now) == Theo Schlossnagle's patch which exposes the start and finish checkpoint times to SQL on grounds of design, completeness and correctness. == Pending Patches == NikhilS sent in another revision of his patch for the TODO items "Add logic to disallow ADD CONSTRAINT ONLY to parent of an inheritance hierarchy," "Add logic to mark inherited constraints in the children," "Add logic to disallow dropping inherited constraints directly on children" and "Modify the pg_dump logic to use the new pg_constraint based attributes logic." Pavel Stehule sent in a patch to add CASE to PL/PgSQL. Laurenz Albe sent in a patch intended to improve shutdown behavior during online backup. Bernd Helmle sent in a patch to psql to implement command aliases. Simon Riggs sent in a patch to implement a waiting pg_stop_backup(). Chris Johnson sent in a patch to add -x <auxiliary command> to pg_standby. Peter Eisentraut sent in a patch which changes how shared libraries and dynamically loadable modules are built. Dawid Kuroczko sent in a patch to add \G to psql. This works like \g does, only it forces \x formatting. Robert Hell sent in a patch which adds a GUC parameter for tuple_fraction of cursors. Fujii Masao sent in a patch which replace offnum++ by OffsetNumberNext. Pavel Stehule sent in three revisions of a patch which adds CASE to PL/PgSQL. Teodor Sigaev sent in a patch which adds a partial match to GIN. Nobuhiro Iwamatsu sent in a patch to port PostgreSQL to Reneas SuperH. Pavel Stehule sent in a patch which adds RETURN QUERY EXECUTE ... USING... to PL/PgSQL. Tom Dunstan sent in a WIP patch which implements [UN]INSTALL MODULE. ---------------------------(end of broadcast)--------------------------- -To unsubscribe from this list, send an email to: [EMAIL PROTECTED]
