|Тема:||== PostgreSQL Weekly News - December 15 2013 ==|
(см: обсуждение, исходный текст)
== PostgreSQL Weekly News - December 15 2013 == The CfP for PGCon 2014 is open through January 19, 2014. http://www.pgcon.org/2014/ The first meeting of PUGS, the PostgreSQL Users' Group of Singapore, will take place 19th December, 2013. http://www.meetup.com/PUGS-Postgres-Users-Group-Singapore/events/154707822/ == PostgreSQL Product News == devart dotConnect 7.2 for PostgreSQL, an ADO.NET provider, released. http://www.devart.com/dotconnect/postgresql/ Npgsql2 2.0.14, a .NET connector for PostgreSQL, released. http://fxjr.blogspot.com/2013/12/npgsql-2014-released.html == PostgreSQL Jobs for December == http://archives.postgresql.org/pgsql-jobs/2013-12/threads.php == PostgreSQL Local == FOSDEM PGDay, a one day conference held before FOSDEM in Brussels, Belgium, will be on Jan 31st, 2014. Details: http://fosdem2014.pgconf.eu/ The 7th annual "Prague PostgreSQL Developers Day" (P2D2) conference organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be held on February 6, 2014 at Faculty of Mathematics and Physics, Charles University (Malostranske namesti 25, Prague). Czech language info below. The CfP will be open through 2014/01/03. http://www.p2d2.cz/ == 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 . == Applied Patches == Robert Haas pushed: - Fixups for dsm.c's file descriptor handling. Per complaint from Tom Lane. http://git.postgresql.org/pg/commitdiff/d9250da032e723d80bb0150b9276cc544df6a087 - Add new wal_level, logical, sufficient for logical decoding. When wal_level=logical, we'll log columns from the old tuple as configured by the REPLICA IDENTITY facility added in commit 07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65. This makes it possible a properly-configured logical replication solution to correctly follow table updates even if they change the chosen key columns, or, with REPLICA IDENTITY FULL, even if the table has no key at all. Note that updates which do not modify the replica identity column won't log anything extra, making the choice of a good key (i.e. one that will rarely be changed) important to performance when wal_level=logical is configured. Each insert, update, or delete to a catalog table will also log the CMIN and/or CMAX values of stamped by the current transaction. This is necessary because logical decoding will require access to historical snapshots of the catalog in order to decode some data types, and the CMIN/CMAX values that we may need in order to judge row visibility may have been overwritten by the time we need them. Andres Freund, reviewed in various versions by myself, Heikki Linnakangas, KONDO Mitsumasa, and many others. http://git.postgresql.org/pg/commitdiff/e55704d8b2fe522fbc9435acbb5bc59033478bd5 - Add a new reloption, user_catalog_table. When this reloption is set and wal_level=logical is configured, we'll record the CIDs stamped by inserts, updates, and deletes to the table just as we would for an actual catalog table. This will allow logical decoding to use historical MVCC snapshots to access such tables just as they access ordinary catalog tables. Replication solutions built around the logical decoding machinery will likely need to set this operation for their configuration tables; it might also be needed by extensions which perform table access in their output functions. Andres Freund, reviewed by myself and others. http://git.postgresql.org/pg/commitdiff/66abc2608c7c00fcd449e00a9e23f13f02e65d04 - Add a new option, -g, to createuser, to add membership in a role. Chistopher Browne, reviewed by Sameer Thakur, Amit Kapila, and Peter Eisentraut. http://git.postgresql.org/pg/commitdiff/6bea96dd49619122bdb3fe9ddfd559a82b26ff73 - Under wal_level=logical, when saving old tuples, always save OID. There's no real point in not doing this. It doesn't cost anything in performance or space. So let's go wild. Andres Freund, with substantial editing as to style by me. http://git.postgresql.org/pg/commitdiff/60dd40bbda92cb1818d5a2651b9d2ba49afd804c Bruce Momjian pushed: - pgcrypto docs: update encryption timings and add relative times. Miles Elam http://git.postgresql.org/pg/commitdiff/d6464fdc0a591662e5e5ee1b0303932e89cb027c - pgcrypto docs: update cpu type used in duration testing http://git.postgresql.org/pg/commitdiff/01cc1fecfdecc37649b3996100ab1f03ccc8ab7e Noah Misch pushed: - Rename TABLE() to ROWS FROM(). SQL-standard TABLE() is a subset of UNNEST(); they deal with arrays and other collection types. This feature, however, deals with set-returning functions. Use a different syntax for this feature to keep open the possibility of implementing the standard TABLE(). http://git.postgresql.org/pg/commitdiff/53685d79813a46c7e3a46ae4b1210f608d41d6ab Tom Lane pushed: - Fix possible crash with nested SubLinks. An expression such as WHERE (... x IN (SELECT ...) ...) IN (SELECT ...) could produce an invalid plan that results in a crash at execution time, if the planner attempts to flatten the outer IN into a semi-join. This happens because convert_testexpr() was not expecting any nested SubLinks and would wrongly replace any PARAM_SUBLINK Params belonging to the inner SubLink. (I think the comment denying that this case could happen was wrong when written; it's certainly been wrong for quite a long time, since very early versions of the semijoin flattening logic.) Per report from Teodor Sigaev. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/9ec6199d18d6235cc4b4d5e4e8986e73b55b14d8 - Tweak placement of explicit ANALYZE commands in the regression tests. Make the COPY test, which loads most of the large static tables used in the tests, also explicitly ANALYZE those tables. This allows us to get rid of various ad-hoc, and rather redundant, ANALYZE commands that had gotten stuck into various test scripts over time to ensure we got consistent plan choices. (We could have done a database-wide ANALYZE, but that would cause stats to get attached to the small static tables too, which results in plan changes compared to the historical behavior. I'm not sure that's a good idea, so not going that far for now.) Back-patch to 9.0, since 9.0 and 9.1 are currently sometimes failing regression tests for lack of an "ANALYZE tenk1" in the subselect test. There's no need for this in 8.4 since we didn't print any plans back then. http://git.postgresql.org/pg/commitdiff/b5e0a2a38401273d3d11b81f4424681db9845882 - Add a regression test case for plpython function returning setof RECORD. We had coverage for functions returning setof a named composite type, but not for anonymous records, which is a somewhat different code path. In view of recent crash report from Sergey Konoplev, this seems worth testing, though I doubt there's any deterministic bug here today. http://git.postgresql.org/pg/commitdiff/6bff0e7d92133d50c064d9f652f538891873aa32 - Remove bogus executable permissions on xlog.c. Apparently fat-fingered in 1a3d104475ce01326fc00601ed66ac4d658e37e5. Noted by Peter Geoghegan. http://git.postgresql.org/pg/commitdiff/22310b808d45f67db4037a74aa47f44d716a4a83 - Improve EXPLAIN to print the grouping columns in Agg and Group nodes. Per request from Kevin Grittner. http://git.postgresql.org/pg/commitdiff/f26099057a2818d85edc2a16e2d3161f4bd96bdc - Fix ancient docs/comments thinko: XID comparison is mod 2^32, not 2^31. Pointed out by Gianni Ciolli. http://git.postgresql.org/pg/commitdiff/ccca6f56f5e027f28b033e5e2754e9ad18e8c9ae - Don't let timeout interrupts happen unless ImmediateInterruptOK is set. Serious oversight in commit 16e1b7a1b7f7ffd8a18713e83c8cd72c9ce48e07: we should not allow an interrupt to take control away from mainline code except when ImmediateInterruptOK is set. Just to be safe, let's adopt the same save-clear-restore dance that's been used for many years in HandleCatchupInterrupt and HandleNotifyInterrupt, so that nothing bad happens if a timeout handler invokes code that tests or even manipulates ImmediateInterruptOK. Per report of "stuck spinlock" failures from Christophe Pettus, though many other symptoms are possible. Diagnosis by Andres Freund. http://git.postgresql.org/pg/commitdiff/e8312b4f03315a98a987997e05a6e9d6d8d84ddf - Add HOLD/RESUME_INTERRUPTS in HandleCatchupInterrupt/HandleNotifyInterrupt. This prevents a possible longjmp out of the signal handler if a timeout or SIGINT occurs while something within the handler has transiently set ImmediateInterruptOK. For safety we must hold off the timeout or cancel error until we're back in mainline, or at least till we reach the end of the signal handler when ImmediateInterruptOK was true at entry. This syncs these functions with the logic now present in handle_sig_alarm. AFAICT there is no live bug here in 9.0 and up, because I don't think we currently can wait for any heavyweight lock inside these functions, and there is no other code (except read-from-client) that will turn on ImmediateInterruptOK. However, that was not true pre-9.0: in older branches ProcessIncomingNotify might block trying to lock pg_listener, and then a SIGINT could lead to undesirable control flow. It might be all right anyway given the relatively narrow code ranges in which NOTIFY interrupts are enabled, but for safety's sake I'm back-patching this. http://git.postgresql.org/pg/commitdiff/2efc6dc256dc71ee876304b51dcad29fafbc37d3 - Fix inherited UPDATE/DELETE with UNION ALL subqueries. Fix an oversight in commit b3aaf9081a1a95c245fd605dcf02c91b3a5c3a29: we do indeed need to process the planner's append_rel_list when copying RTE subqueries, because if any of them were flattenable UNION ALL subqueries, the append_rel_list shows which subquery RTEs were pulled up out of which other ones. Without this, UNION ALL subqueries aren't correctly inserted into the update plans for inheritance child tables after the first one, typically resulting in no update happening for those child table(s). Per report from Victor Yegorov. Experimentation with this case also exposed a fault in commit a7b965382cf0cb30aeacb112572718045e6d4be7: if an inherited UPDATE/DELETE was proven totally dummy by constraint exclusion, we might arrive at add_rtes_to_flat_rtable with root->simple_rel_array being NULL. This should be interpreted as not having any RelOptInfos. I chose to code the guard as a check against simple_rel_array_size, so as to also provide some protection against indexing off the end of the array. Back-patch to 9.2 where the faulty code was added. http://git.postgresql.org/pg/commitdiff/c03ad5602f529787968fa3201b35c119bbc6d782 - Allow empty target list in SELECT. This fixes a problem noted as a followup to bug #8648: if a query has a semantically-empty target list, e.g. SELECT * FROM zero_column_table, ruleutils.c will dump it as a syntactically-empty target list, which was not allowed. There doesn't seem to be any reliable way to fix this by hacking ruleutils (note in particular that the originally zero-column table might since have had columns added to it); and even if we had such a fix, it would do nothing for existing dump files that might contain bad syntax. The best bet seems to be to relax the syntactic restriction. Also, add parse-analysis errors for SELECT DISTINCT with no columns (after *-expansion) and RETURNING with no columns. These cases previously produced unexpected behavior because the parsed Query looked like it had no DISTINCT or RETURNING clause, respectively. If anyone ever offers a plausible use-case for this, we could work a bit harder on making the situation distinguishable. Arguably this is a bug fix that should be back-patched, but I'm worried that there may be client apps or PLs that expect "SELECT ;" to throw a syntax error. The issue doesn't seem important enough to risk changing behavior in minor releases. http://git.postgresql.org/pg/commitdiff/1b4f7f93b4693858cb983af3cd557f6097dab67b Peter Eisentraut pushed: - doc: Fix DocBook table column count declaration. This was broken in d6464fdc0a591662e5e5ee1b0303932e89cb027c. http://git.postgresql.org/pg/commitdiff/a06af43695cd51184dacc260aa394685991fe15f - PL/Perl: Add event trigger support. From: Dimitri Fontaine <> http://git.postgresql.org/pg/commitdiff/e5dc4cc24d2e1e94ac572a2c64103710bf15d21e - configure: Allow adding a custom string to PG_VERSION. This can be used to mark custom built binaries with an extra version string such as a git describe identifier or distribution package release version. From: Oskari Saarenmaa <> http://git.postgresql.org/pg/commitdiff/46328916eefc5f9eaf249518e96f68afcd35923b Kevin Grittner pushed: - Add table name to VACUUM statement in matview.c. The test only needs the one table to be vacuumed. Vacuuming the database may affect other tests. Per gripe from Tom Lane. Back-patch to 9.3, where the test was was added. http://git.postgresql.org/pg/commitdiff/09df854b8acbba5c1d1dc5cdf30c8e84f59145e2 Simon Riggs pushed: - Regression tests for ALTER TABLESPACE RENAME,OWNER. Hari Babu Kommi reviewed by David Rowley http://git.postgresql.org/pg/commitdiff/b921a26fb89cf52ec32b6c19a8528524883beeb5 - Regression tests for SCHEMA commands. Hari Babu Kommi reviewed by David Rowley http://git.postgresql.org/pg/commitdiff/cf589c9c1f5f7f89018684b763d74d7b02a2504b - Allow time delayed standbys and recovery. Set min_recovery_apply_delay to force a delay in recovery apply for commit and restore point WAL records. Other records are replayed immediately. Delay is measured between WAL record time and local standby time. Robert Haas, Fabrízio de Royes Mello and Simon Riggs Detailed review by Mitsumasa Kondo http://git.postgresql.org/pg/commitdiff/36da3cfb457b77a55582f68208d815f11ac1399e - New autovacuum_work_mem parameter. If autovacuum_work_mem is set, autovacuum workers now use this parameter in preference to maintenance_work_mem. Peter Geoghegan http://git.postgresql.org/pg/commitdiff/8693559cacf1765697c32fc38574af3c19ce61c1 Heikki Linnakangas pushed: - Display old and new values in pg_resetxlog -n output. For extra clarity. Rajeev Rastogi, reviewed by Amit Kapila http://git.postgresql.org/pg/commitdiff/108e3992cdae890c9b5b4778c3598fce35eea4ca - Fix WAL-logging of setting the visibility map bit. The operation that removes the remaining dead tuples from the page must be WAL-logged before the setting of the VM bit. Otherwise, if you replay the WAL to between those two records, you end up with the VM bit set, but the dead tuples are still there. Backpatch to 9.3, where this bug was introduced. http://git.postgresql.org/pg/commitdiff/a49633d8dc6c66f6b2e92c8598d3dfa2cc72a259 - Add GUC to enable WAL-logging of hint bits, even with checksums disabled. WAL records of hint bit updates is useful to tools that want to examine which pages have been modified. In particular, this is required to make the pg_rewind tool safe (without checksums). This can also be used to test how much extra WAL-logging would occur if you enabled checksums, without actually enabling them (which you can't currently do without re-initdb'ing). Sawada Masahiko, docs by Samrat Revagade. Reviewed by Dilip Kumar, with further changes by me. http://git.postgresql.org/pg/commitdiff/50e547096c4858a68abf09894667a542cc418315 - Fix more instances of "the the" in comments. Plus one instance of "to to" in the docs. http://git.postgresql.org/pg/commitdiff/dde6282500ac650cd0257fc24aeb5688962fddf9 Tatsuo Ishii pushed: - Fix progress logging when scale factor is large. Integer overflow showed minus percent and minus remaining time something like this. 239300000 of 3800000000 tuples (-48%) done (elapsed 226.86 s, remaining -696.10 s). http://git.postgresql.org/pg/commitdiff/841a65482d6e198b71a0df987b8b9822608d5ef4 - Add "SHIFT_JIS" as an accepted encoding name for locale checking. When locale is "ja_JP.SJIS", nl_langinfo(CODESET) returns "SHIFT_JIS" on some platforms, at least on RedHat Linux. So the encoding/locale match table (encoding_match_list) needs the entry. Otherwise client encoding is set to SQL_ASCII. Back patch to all supported branches. http://git.postgresql.org/pg/commitdiff/1f0626ee40f2decd93ca96c6f9e9a9b5edff57ba Magnus Hagander pushed: - Fix double "the" in the documentation. Erik Rijkers http://git.postgresql.org/pg/commitdiff/56afe8509ec3057e06ebe244e51272511f05decb Alvaro Herrera pushed: - Rework MultiXactId cache code. The original performs too poorly; in some scenarios it shows way too high while profiling. Try to make it a bit smarter to avoid excessive cosst. In particular, make it have a maximum size, and have entries be sorted in LRU order; once the max size is reached, evict the oldest entry to avoid it from growing too large. Per complaint from Andres Freund in connection with new tuple freezing code. http://git.postgresql.org/pg/commitdiff/d881dd6233f4eec6404f003bb08312e9e650e0e2 - Fix typo http://git.postgresql.org/pg/commitdiff/60eea3780c40fe7ac0332d83173947199e59f5cd == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == MauMau sent in a patch to enable recovery to a backup point. Peter Geoghegan sent in a patch to store pg_stat_statements query texts externally and move pg_stat_statements into core. Euler Taveira de Oliveira sent in a patch to implement a json output plugin for logical decoding of the WAL stream. Heikki Linnakangas and Alexander Korotkov traded patches adding information to GIN indexes. Heikki Linnakangas sent in a patch to use posix_fadvise in ANALYZE. Antonin Houska sent in another revision of a patch to implement backup throttling. Peter Eisentraut sent in another revision of a patch to add TRANSFORMS. Fujii Masao sent in a patch to fix a bug in libpq's PQhost(). Mitsumasa KONDO sent in another revision of a patch to optimize kernel readahead using buffer access strategy. Haribabu Kommi sent in another revision of a patch to allow specifying a separate xlog directory in pg_basebackup. Andres Freund sent in a patch to implement replication node identifiers including an SQL API. Sergey Muraviov sent in another revision of a patch to improve the look of psql in the case of wide output. Kyotaro HORIGUCHI sent in a patch to fix an issue where recovery can fail in 9.3+. Haribabu Kommi sent in another revision of a patch to fix a race condition between high-volume writing and VACUUM. Tatsuo Ishii sent in two revisions of a patch to allow pgbench to take large scale factors. Andres Freund sent in a patch always to include oids in WAL-logged replica identities. Amit Kapila sent in another revision of a patch to reduce the WAL for update operations. KaiGai Kohei sent in another revision of a patch to implement custom scans and use same to implement custom TID scans as a contrib module, and add join pushdowns in the PostgreSQL FDW. Rajeev Rastogi sent in another revision of a patch to ensure that COPY ... FROM STDIN shows a count tag. Peter Geoghegan sent in another revision of a patch to implement INSERT ... ON DUPLICATE KEY LOCK FOR UPDATE. Pavel Stehule sent in two more revisions of a patch to create make_timestamp functions. Marko Kreen sent in another revision of a patch to have SSL choose from an updated list of cypher suites. MauMau sent in another revision of a patch to fix a bug in psql on Windows where pg_ctl always uses the same event source. David Rowley sent in a patch to quiet a compiler warning in relcache. Etsuro Fujita sent in a patch to Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan. Etsuro Fujita sent in a patch to fix a comment in src/backend/commands/copy.c. Pavel Stehule sent in another revision of a patch to enable an --if-exists switch in pg_dump. MauMau sent in a patch to fix an issue where multibyte messages are displayed incorrectly on the client during startup. Craig Ringer sent in another revision of a patch to implement updateable security barrier views. Greg Smith sent in another revision of a patch to implement row-based access control on the read side. Andres Freund sent in a patch to fix some issues pointed out to by clang. Alexander Korotkov sent in a PoC patch to implement partial sorting. David Rowley sent in two revisions of a patch to allow aggregates with negative (or reverse, or inverse) transition functions.