|Тема:||== PostgreSQL Weekly News - November 21 2010 ==|
(см: обсуждение, исходный текст)
== PostgreSQL Weekly News - November 21 2010 == == PostgreSQL Product News == DiffKit 0.8.2, a table comparison tool with support for PostgreSQL, released. http://www.diffkit.org PostgreDAC 2.6.0, a Delphi/C++ builder for PostgreSQL, released. http://microolap.com/products/connectivity/postgresdac/download/ == PostgreSQL Jobs for November == http://archives.postgresql.org/pgsql-jobs/2010-11/threads.php == PostgreSQL Local == The PostgreSQLFr Call for Projects has begun. Projects must target PostgreSQL and the French-speaking community. Mail appel-projets-2010 AT postgresql DOT fr. http://www.postgresql.fr/appel_a_projets_2010:call_for_projects Registration for PGDay.EU 2010 held on December 6-8 in Stuttgart, Germany is open. http://2010.pgday.eu/register Early bird registration for PGDay.IT, December 10, 2010 in Rome, Italy, is open and the schedule is online. http://2010.pgday.it/ FOSDEM is one of the biggest Free and Open Source event held annually in Brussels, Belgium, on February 5-6, 2011, and attended by around 4000 people. Send in your proposal to fosdem AT postgresql DOT eu. http://andreas.scherbaum.la/blog/archives/749-PostgreSQLFOSDEM-2011-Call-for-talks.html PGDay is scheduled at this year's Southern California Linux Exposition (SCALE) held in the LAX Hilton Hotel in the city of Los Angeles, California, on Friday February 25th, 2011. Post your talk submission to pgday-submissions AT googlegroups DOT com. PGCon will be May 19-20, 2011 at the University of Ottawa, preceded by two days of tutorials on May 17-18. http://www.pgcon.org/2011/ == 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 , German language to , Italian language to . Spanish language to . == 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. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=52010027efc8757fdd830a4d0113763a501259bc Alvaro Herrera pushed: - Fix copy-and-pasteo. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=ae4b17edeebc6670ba222a8cfbb1658d02203d29 - Add pg_describe_object function. This function is useful to obtain textual descriptions of objects as stored in pg_depend. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=6cc2deb86e9183262493a6537700ee305fb3e096 Robert Haas pushed: - Fix copy-and-pasteo a little more completely. copydir.c is no longer in src/port http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=3134d8863e8473e3ed791e27d484f9e548220411 - Document the dummy_seclabel contrib module. KaiGai Kohei, with editing and markup fixes by me. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=45768d10e3abd513b4c959efeb5907798f2fac3f - Minor corrections to dummy_seclabel documentation. Problems noted by Thom Brown. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=815810ed315a6d21203ec75a11f742f5ed655418 - Update int8-exp-three-digits.out to match new contents of int8.out. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=750487077802861a2accd94164166747ec311fa4 - Bump catversion. Should have done this as part of format(text) patch. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=506070be34dcdf35fd91c12c6745c745716de1d4 - Put back accidentally-deleted quote_literal() regression tests. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=48c348f86ce09c668af7cf271757c3f156f28344 - Improve plpgsql's error reporting for no-such-column cases. Given a column reference foo.bar, 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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=2edc5cd493ce3d7834026970e9d3cd00e203f51a - Remove unused parameter. Patch by Shigeru Hanada. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=4acf99b2f3bb665d2febef183ede5f08b8f7545d Andrew Dunstan pushed: - Require VALUE keyword when extending an enum type. Based on a patch from Alvaro Herrera. http://git.postgresql.org/gitweb?p=postgresql.git;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. http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=cfad144f894b306fc300f5d03ea52a32d4624db0 == Rejected Patches (for now) == No one was disappointed this week :-) == Pending 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 tables. 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 functions. 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 possible. 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.