== PostgreSQL Weekly News - January 10 2010 == - Mailing list pgsql-announce
From | David Fetter |
---|---|
Subject | == PostgreSQL Weekly News - January 10 2010 == |
Date | |
Msg-id | 20100111035225.GI25810@fetter.org Whole thread Raw |
Responses |
Re: == PostgreSQL Weekly News - January 10 2010 ==
|
List | pgsql-announce |
== PostgreSQL Weekly News - January 10 2010 == Congratulations to Alvaro Herrera on the birth of his daughter. Hot Standby User's Group now taking place remotely on 13 Jan at 1600UTC, rather than 6 Jan as originally advertised. More details here: http://www.postgresql.org/about/event.1013 == PostgreSQL Product News == Bucardo, an asynchronous replication system with master-master capability, is now packaged for Fedora, and on CentOS and RHEL using EPEL. https://fedoraproject.org/wiki/EPEL/FAQ#howtouse pgfincore, a set of functions to handle low-level management of relations using mincore to explore cache memory, released. http://pgfoundry.org/projects/pgfincore/ == PostgreSQL 8.5 Feature of the Week == You can now log SQL state for queries, errors, etc., using %e in your log_line_prefix. == PostgreSQL Jobs for January == http://archives.postgresql.org/pgsql-jobs/2010-01/threads.php == PostgreSQL Local == The New York Capital District PUG's first meeting will be January 7, 2010. http://nycdpug.x10hosting.com/ Stefan Keller will be teach a course called, "Introduction to PostGIS/PostgreSQL" at the University of Applied Sciences Rapperswil (HSR) on January 14, 2010 in Zurich, Switzerland. Details on this and other upcoming events below. http://www.gis.hsr.ch/wiki/Agenda Seeking booth staffers for Open Day Linux Conf AU in Wellington, NZ January 23, 2010, 11-2pm, Contact selena -at- postgresql.org to volunteer. The Call for Papers for East is open until January 30, 2010. Details at: http://www.postgresqlconference.org/ PGDay-Cuba 2010 will be held 4-6 February 2010, in La Havana at the University of Science Informatic. Attendees from Cuba, the Dominican Republic and Venezuela are invited. FOSDEM 2010 will be in Brussels, Belgium on February 6-7, 2010. http://www.fosdem.org/ The German PostgreSQL User Group has a stand at the Open Source Park at Cebit 2010 from March 02 to March 06, 2010 in Hannover, Germany. http://www.cebit.de/ Chemnitzer Linuxtage will be in Chemnitz, Germany on March 13-14, 2010. http://chemnitzer.linux-tage.de/ PgCon 2010 will be held May 20-21 2010 in Ottawa with tutorials before on the 18th and 19th. The RfP is open! http://www.pgcon.org/2010/papers.php The Call for Proposals for OSCON is open. OSCON will take place in Portland, Oregon July 19-23, 2010. http://post.oreilly.com/f2f/9z1zqmm5lhkab0uogt3avlvc4u59bro6f917re423d8 == PostgreSQL in the News == Planet PostgreSQL: http://planet.postgresql.org/ PostgreSQL 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 david@fetter.org, German language to pwn@pgug.de, Italian language to pwn@itpug.org. == Reviews == == Applied Patches == Tom Lane committed: - When estimating the selectivity of an inequality "column > constant" or "column < constant", and the comparison value is in the first or last histogram bin or outside the histogram entirely, try to fetch the actual column min or max value using an index scan (if there is an index on the column). If successful, replace the lower or upper histogram bound with that value before carrying on with the estimate. This limits the estimation error caused by moving min/max values when the comparison value is close to the min or max. Per a complaint from Josh Berkus. It is tempting to consider using this mechanism for mergejoinscansel as well, but that would inject index fetches into main-line join estimation not just endpoint cases. I'm refraining from that until we can get a better handle on the costs of doing this type of lookup. - Improve PGXS makefile system to allow the module's makefile to specify where to install DATA and DOCS files. This is mainly intended to allow versioned installation, eg, install into contrib/fooM.N/ rather than directly into contrib/. Mark Cave-Ayland. - Get rid of the need for manual maintenance of the initial contents of pg_attribute, by having genbki.pl derive the information from the various catalog header files. This greatly simplifies modification of the "bootstrapped" catalogs. This patch finally kills genbki.sh and Gen_fmgrtab.sh; we now rely entirely on Perl scripts for those build steps. To avoid creating a Perl build dependency where there was not one before, the output files generated by these scripts are now treated as distprep targets, ie, they will be built and shipped in tarballs. But you will need a reasonably modern Perl (probably at least 5.6) if you want to build from a CVS pull. The changes to the MSVC build process are untested, and may well break --- we'll soon find out from the buildfarm. John Naylor, based on ideas from Robert Haas and others. - In pgsql/src/backend/Makefile, fix parallel-make timing problem. - Remove too-smart-for-its-own-good optimization of not overwriting the output files when they haven't changed. This confuses make because the build fails to update the file timestamps, and so it keeps on doing the action over again. - Fix a few places where we needed -I. in CPPFLAGS to work properly in VPATH builds. We had this already in several places, but not all. - In pgsql/src/backend/catalog/genbki.pl, further code review for genbki.pl. Improve comments, fix some rather random code choices, don't slavishly duplicate the original pg_attribute.h's failure to put an OID into Schema_pg_index entries. - Fix genbki.pl and Gen_fmgrtab.pl to use PID-specific temp file names, so that it's safe if a parallel make chooses to run two concurrent copies. Also, work around a memory leak in some versions of Perl. - Add support for doing FULL JOIN ON FALSE. While this is really a rather peculiar variant of UNION ALL, and so wouldn't likely get written directly as-is, it's possible for it to arise as a result of simplification of less-obviously-silly queries. In particular, now that we can do flattening of subqueries that have constant outputs and are underneath an outer join, it's possible for the case to result from simplification of queries of the type exhibited in bug #5263. Back-patch to 8.4 to avoid a functionality regression for this type of query. - In pgsql/contrib/auto_explain/auto_explain.c, add missing 'static' keywords. - Fix missing <manvolnum> markers in dblink reference entries. Silences complaints from "make man", and allows these man pages to be included in man3/. - Make the makefile pass $MAJORVERSION to genbki.pl, not $VERSION which is overridden in the snapshot build script. $MAJORVERSION is what it really wanted anyway, so we can tighten up the parsing of --set-version's argument. - In pgsql/src/backend/catalog/genbki.pl, make error messages for bad --set-version argument more useful. Per Stefan Kaltenbrunner. - In pgsql/src/backend/utils/cache/spccache.c, access hash entry before freeing it, not after. - In pgsql/src/backend/utils/cache/spccache.c, fix spccache.c to not suppose that a cache entry will live across database access, per testing with CLOBBER_CACHE_ALWAYS. Minor other editorialization. - Alter the configure script to fail immediately if the C compiler does not provide a working 64-bit integer datatype. As recently noted, we've been broken on such platforms since early in the 8.4 development cycle. Since it took nearly two years for anyone to even notice, it seems that the rationale for continuing to support such platforms has reached the point of non-existence. Rather than thrashing around to try to make it work again, we'll just admit up front that this no longer works. Back-patch to 8.4 since that branch is also broken. We should go around to remove INT64_IS_BUSTED support, but just in HEAD, so that seems like material for a separate commit. - Make configure check the version of Perl we're building with, and reject versions < 5.8. Also, if there's no Perl, emit a warning informing the user that he won't be able to build from a CVS pull. This is exactly the same treatment we give Bison and Perl, and for the same reasons. - Fail in a nicer way if we have --with-perl and no Perl. - Remove all the special-case code for INT64_IS_BUSTED, per decision that we're not going to support that anymore. I did keep the 64-bit-CRC-with-32-bit-arithmetic code, since it has a performance excuse to live. It's a bit moot since that's all ifdef'd out, of course. - Fix (some of the) breakage introduced into query-cancel processing by Hot Standby. It is absolutely not okay to throw an ereport(ERROR) in any random place in the code just because DoingCommandRead is set; interrupting, say, OpenSSL in the midst of its activities is guaranteed to result in heartache. Instead of that, undo the original optimizations that threw away QueryCancelPending anytime we were starting or finishing a command read, and instead discard the cancel request within ProcessInterrupts if we find that there is no Hiroshi Saito reason for forcing a cancel and we are DoingCommandRead. In passing, may I once again condemn the practice of changing the code and not fixing the adjacent comment that you just turned into a lie? - In pgsql/src/backend/utils/adt/varbit.c, make bit/varbit substring() treat any negative length as meaning "all the rest of the string". The previous coding treated only -1 that way, and would produce an invalid result value for other negative values. We ought to fix it so that 2-parameter bit substring() is a different C function and the 3-parameter form throws error for negative length, but that takes a pg_proc change which is impractical in the back branches; and in any case somebody might be relying on -1 working this way. So just do this as a back-patchable fix. - Fix 3-parameter form of bit substring() to throw error for negative length, as required by SQL standard. - In pgsql/src/backend/executor/execMain.c, fix oversight in EvalPlanQualFetch: after failing to lock a tuple because someone else has just updated it, we have to set priorXmax to that tuple's xmax (ie, the XID of the other xact that updated it) before looping back to examine the next tuple. Obviously, the next tuple in the update chain should have that XID as its xmin, not the same xmin as the preceding tuple that we had been trying to lock. The mismatch would cause the EvalPlanQual logic to decide that the tuple chain ended in a deletion, when actually there was a live tuple that should have been found. I inserted this error when recently adding logic to EvalPlanQual to make it lock tuples before returning them (as opposed to the old method in which the lock would occur much later, causing a great deal of work to be wasted if we only then discover someone else updated it). Sigh. Per today's report from Takahiro Itagaki of inconsistent results during pgbench runs. - In pgsql/src/pl/plperl/GNUmakefile, fix makefile so it works for VPATH case. - In pgsql/src/backend/executor/execQual.c, make ExecEvalFieldSelect throw a more intelligible error if it's asked to extract a system column, and remove a couple of lines that are useless in light of the fact that we aren't ever going to support this case. There isn't much point in trying to make this work because a tuple Datum does not carry many of the system columns. Per experimentation with a case reported by Dean Rasheed; we'll have to fix his problem somewhere else. - Improve plpgsql's handling of record field references by forcing all potential field references in SQL expressions to have RECFIELD datum-array entries at parse time. If it turns out that the reference is actually to a SQL column, the RECFIELD entry is useless, but it costs little. This allows us to get rid of the previous use of FieldSelect applied to a whole-row Param for the record variable; which was not only slower than a direct RECFIELD reference, but failed for references to system columns of a trigger's NEW or OLD record. Per report and fix suggestion from Dean Rasheed. - In pgsql/src/pl/plpgsql/src/gram.y, improve plpgsql parsing to report "foo is not a known variable", rather than a generic syntax error, when seeing "foo := something" and foo isn't recognized. This buys back most of the helpfulness discarded in my previous patch by not throwing errors when a qualified name appears to match a row variable but the last component doesn't match any field of the row. It covers other cases where our error messages left something to be desired, too. - In pgsql/src/pl/plperl/GNUmakefile, remove unnecessary use of temp file. Tim Bunce. - In pgsql/src/include/utils/rel.h, some trivial adjustments in comments for struct RelationData. Heikki Linnakangas committed: - Write an end-of-backup WAL record at pg_stop_backup(), and wait for it at recovery instead of reading the backup history file. This is more robust, as it stops you from prematurely starting up an inconsisten cluster if the backup history file is lost for some reason, or if the base backup was never finished with pg_stop_backup(). This also paves the way for a simpler streaming replication patch, which doesn't need to care about backup history files anymore. The backup history file is still created and archived as before, but it's not used by the system anymore. It's just for informational purposes now. Bump PG_CONTROL_VERSION as the location of the backup startpoint is now written to a new field in pg_control, and catversion because initdb is required Original patch by Fujii Masao per Simon's idea, with further fixes by Heikki Linnakangas. Magnus Hagander committed: - In pgsql/src/include/storage/s_lock.h, add a Win64-specific spin_delay() function. We can't use the same as before, since MSVC on Win64 doesn't support inline assembly. - In pgsql/src/include/storage/s_lock.h, use _mm_pause() for win64 spin_delay(), per note from Tsutomu Yamada. - In pgsql/src/tools/msvc/Project.pm, disable linker warning 4197 on Win64, that is caused by the fact that we export symbols both using __declspec(dllexport) (via the PGDLLIMPORT macro) and using full-dll-export. This works without warning on Win32, but not on Win64. In passing, fix the fact that the framework could never deal with more than one disbled linker warning - because MSVC wants commas between linker warnings, and semicolons between compiler warnings... - Move the default configuration for the MSVC build system to config_default.pl, and allow using config.pl to override the defaults. config.pl is removed from the repository, so changes there will no longer show up when doing diff, and will not prevent switching branches and such things. config.pl would normally be used to override single values, but if an old-style config.pl is read, it will override the entire default configuration, making it backwards compatible. - Create typedef pgsocket for storing socket descriptors. This silences some warnings on Win64. Not using the proper SOCKET datatype was actually wrong on Win32 as well, but didn't cause any warnings there. Also create define PGINVALID_SOCKET to indicate an invalid/non-existing socket, instead of using a hardcoded -1 value. - In pgsql/doc/src/sgml/installation.sgml, update Windows installation notes. pginstaller isn't used anymore, in favor of the one-click installers. Make it clear that we support Windows 2000 and newer with the native port, instead of first saying we support NT4 and then saying we don't. Andrew Dunstan committed: - In pgsql/src/pl/plperl/plperl.c, check values passed back from PLPerl to the database, via function return, trigger tuple modification or SPI call, to ensure they are valid in the server encoding. Along the way, replace uses of SvPV(foo, PL_na) with SvPV_nolen(foo) as recommended in the perl docs. Bug report from Hannu Krosing. - In pgsql/src/backend/utils/mb/wchar.c, remove sometimes inaccurate error hint about source of wrongly encoded data. - Tidy up and refactor plperl.c. 1.Changed MULTIPLICITY check from runtime to compiletime. No loads the large Config module. 2.Changed plperl_init_interp() to return new interp and not alter the global interp_state 3. Moved plperl_safe_init() call into check_interp(). 4. Removed plperl_safe_init_done state variable as interp_state now covers that role. 5. Changed plperl_create_sub() to take a plperl_proc_desc argument. 6. Simplified return value handling in plperl_create_sub. 7. Changed perl.com link in the docs to perl.org and tweaked wording to clarify that require, not use, is what's blocked. 8. Moved perl code in large multi-line C string literal macros out to plc_*.pl files. 9. Added a test2macro.pl utility to convert the plc_*.pl files to macros in a perlchunks.h file which is #included 10. Simplifed plperl_safe_init() slightly 11. Optimized pg_verifymbstr calls to avoid unneeded strlen()s. Patch from Tim Bunce, with minor editing from me. - In pgsql/src/tools/msvc/Mkvcbuild.pm, build perlchunks.h for plperl on MSVC. - Provide regression testing for plperlu, and for plperl+plperlu interaction. The latter are only run if the platform can run both interpreters in the same backend. ITAGAKI Takahiro committed: - In pgsql/src/backend/utils/adt/genfile.c, verify input in pg_read_file(). - In pgsql/contrib/pgbench/pgbench.c, add verification of variable names in pgbench. Variables must consist of only alphabets, numerals and underscores. We had allowed to set variables with invalid names, but could not refer them in queries. Thanks to Robert Haas for the review. - In pgsql/contrib/pgbench/pgbench.c, adjust headers to allow pgbench to be built with PGXS. - Support rewritten-based full vacuum as VACUUM FULL. Traditional VACUUM FULL was renamed to VACUUM FULL INPLACE. Also added a new option -i, --inplace for vacuumdb to perform FULL INPLACE vacuuming. Since the new VACUUM FULL uses CLUSTER infrastructure, we cannot use it for system tables. VACUUM FULL for system tables always fall back into VACUUM FULL INPLACE silently. Itagaki Takahiro, reviewed by Jeff Davis and Simon Riggs. - In pgsql/src/backend/commands/cluster.c, silence compiler warning about uninitialized variables. This initialization is not necessary needed, but some compilers complain about it. - Add buffer access counters to pg_stat_statements. This uses the same infrastructure with EXPLAIN BUFFERS to support {shared|local}_blks_{hit|read|written} andtemp_blks_{read|written} columns in the pg_stat_statements view. The dumped file format also updated. Thanks to Robert Haas for the review. - In pgsql/src/include/executor/instrument.h, pgBufferUsage needs PGDLLIMPORT for pg_stat_statements on Windows. Michael Meskes committed: - Applied patch by Zoltan Boszormenyi to add sqlda support to ecpg in both native and compatiblity mode. - Adjusted CVS headers for ECPG. - ECPG still not correct. - In ECPG, third try. It seems my two checkouts need some cleaning up. - Ah, should read the file completely. I got so used to git that I almost forgot about the cvs stuff. - And then forgot to commit the fixed files. ARGH! - And another one of the same problem. - And another one. - Applied Zoltan Boszormenyi's patch to remove hardware dependant offset logging and superfluous include files. - In ECPG, removed more inttypes.h stuff. - In ECPG, removed test case using nan as float value because printf's output for nan is OS specific with some distinguishing between signaling and quiet nans. It's not really importnat for us here anyway. - In ECPG, remove __FUNCTION__ keyword that is not recognized by some compilers. - In pgsql/src/interfaces/ecpg/ecpglib/sqlda.c, replaced int64_t with int64 as Andrew Dunstan suggested. - In pgsql/src/interfaces/ecpg/ChangeLog, add Zoltan Boszormenyi's recent ECPG cleanup patch. - Backported fix for protecting ecpg against applications freeing strings to 8.4. Robert Haas committed: - Support ALTER TABLESPACE name SET/RESET ( tablespace_options ). This patch only supports seq_page_cost and random_page_cost as parameters, but it provides the infrastructure to scalably support many more. In particular, we may want to add support for effective_io_concurrency, but I'm leaving that as future work for now. Thanks to Tom Lane for design help and Alvaro Herrera for the review. - Improve a couple of comments relating to large object snapshot management. - Further fixes for per-tablespace options patch. Add missing varlena header to TableSpaceOpts structure. And, per Tom Lane, instead of calling tablespace_reloptions in CacheMemoryContext, call it in the caller's memory context and copy the value over afterwards, to reduce the chances of a session-lifetime memory leak. - In pgsql/src/backend/utils/cache/relcache.c, document why we copy reloptions into CacheMemoryContext after-the-fact. - In pgsql/doc/src/sgml/catalogs.sgml, document pg_tablespace.spcoptions. This should have been part of the original tablespace-options patch, but I overlooked the need to update this portion of the documentation. - Remove partial, broken support for NULL pointers when fetching attributes. Previously, fastgetattr() and heap_getattr() tested their fourth argument against a null pointer, but any attempt to use them with a literal-NULL fourth argument evaluated to *(void *)0, resulting in a compiler error. Remove these NULL tests to avoid leading future readers of this code to believe that this has a chance of working. Also clean up related legacy code in nocachegetattr(), heap_getsysattr(), and nocache_index_getattr(). The new coding standard is that any code which calls a getattr-type function or macro which takes an isnull argument MUST pass a valid boolean pointer. Per discussion with Bruce Momjian, Tom Lane, Alvaro Herrera. Bruce Momjian committed: - In pgsql/src/backend/commands/tablespace.c, use OIDCHARS for oid character length, rather than '10', in tablespace code. - Remove tabs in SGML. Move OIDCHARS to proper include file. - Implement pg_dump --only-analyze for use by pg_migrator to only analyze all databases. - Preserve relfilenodes: Add support to pg_dump --binary-upgrade to preserve all relfilenodes, for use by pg_migrator. - In pgsql/src/include/catalog/catversion.h, update catalog version for recent relfilenode patch, so pg_migrator can identify the new API. - In pgsql/src/bin/pg_dump/pg_dumpall.c, remove erroneous comma added to pg_dumpall tablespace query when running with PG version 8.2-8.4. - binary upgrade: Preserve relfilenodes for views and composite types --- even though we don't store data in, them, they do consume relfilenodes. Bump catalog version. - In pgsql/src/bin/scripts/vacuumdb.c, corrected CVS entry: --only-analyze mode is for _vacuumdb_, not pg_dump. - PG_MAJORVERSION: For simplicity, use PG_MAJORVERSION rather than PG_VERSION for creation of the PG_VERSION file. - In pgsql/src/backend/commands/tablespace.c, clarify tablespace.c::TablespaceCreateDbspace() comments. - In pgsql/src/backend/commands/tablespace.c, more tablespace.c comment improvements. - Rename new vacuumdb option to --analyze-only from --only-analyze. - Use -Z for vacuumdb --analyze-only, rather than -o. Simon Riggs committed: - During Hot Standby, set DatabasePath correctly during relcache init file deletion, so that we attempt to unlink the correct filepath. unlink() errors are ignorable there, so lack of a DatabasePath initialization step did not cause visible problems until a related bug showed up on Solaris. Code refactored from xact_redo_commit() to ProcessCommittedInvalidationMessages() in inval.c. Recovery may replay shared invalidation messages for many databases, so we cannot SetDatabasePath() once as we do in normal backends. Read the databaseid from the shared invalidation messages, then set DatabasePath temporarily before calling RelationCacheInitFileInvalidate(). Problem report by Robert Treat, analysis and fix by me. - During Hot Standby, fix drop database when sessions idle. Previously we only cancelled sessions that were in-transaction. Simple fix is to just cancel all sessions without waiting. Doing it this way avoids complicating common code paths, which would not be worth the trouble to cover this rare case. Problem report and fix by Andres Freund, edited somewhat by me - In pgsql/doc/src/sgml/backup.sgml, docs for behaviour change of drop database during Hot Standby Adding missing docs for previous Hot Standby patch. == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == KaiGai Kohei sent in another revision of the patch to disallow ALTER TABLE...ALTER COLUMN...RENAME TO... in situations where the column originates from more than one distinct parent in its inheritance hierarchy. Hitoshi Harada sent in another revision of the patch to extend windowing functions to include (ROWS|RANGE) (PRECEDING|FOLLOWING). Zoltan Boszormenyi sent in another revision of the patch to add DESCRIBE OUTPUT to ECPG. Guillaume Lelarge sent in two approaches to the patch which lets libpq have named parameters. Robert Haas sent in two WIP patches to add attribute options, following the model of reloptions and tablespace options. Pavel Stehule sent in another revision of the patch to add quote_literal and quote_ident behavior to psql variables. Fujii Masao sent in another revision of the patch to add a new transaction log record which indicates that a WAL has been skipped. Marko (johto) Tiikkaja sent in another revision of the writeable CTE patch, this time allowing INSERT, UPDATE or DELETE at the top level. Magnus Hagander sent in a patch which fixes an issue in the win32 socket definition. Arie Bikker sent in three revisions of a patch to improve xpath queries. Fujii Masao sent in a patch to add a "replication" keyword to pg_hba.conf. John Naylor sent in another patch to fix the BKI refactor patch. Simon Riggs sent in a patch to fix an issue in hot standby where cancelling queries misbehaved. Alex Hunsaker sent in a patch to set oom to -17 in the startup script so the OOM killer won't kill. ITAGAKI Takahiro sent in a patch to fix an issue where paths have non-ASCII characters. Leonardo F sent in a patch to add substring and replace() to bit values. Alex Hunsaker sent in a patch to avoid the Linux OOM killer. Joachim Wieland sent in another revision of the patch to fix cases in LISTEN/NOTIFY when the queue is full Kevin Grittner sent in a patch intended to be infrastructure for a better implementation of SERIALIZABLE isolation. Tim Bunce sent in a patch which adds a flock of features to PL/Perl. Joachim Wieland sent in a patch which implements synchronized snapshots, which will among other things allow pg_dump to work in parallel. Heikki Linnakangas sent in another revsion of the streaming replication patch. Magnus Hagander sent in a patch to enable RADIUS authentication. Peter Eisentraut sent in a patch to implement typed tables per SQL:2008.
pgsql-announce by date: