== PostgreSQL Weekly News - June 07 2015 ==
От | David Fetter |
---|---|
Тема | == PostgreSQL Weekly News - June 07 2015 == |
Дата | |
Msg-id | 20150608005708.GA17798@fetter.org обсуждение исходный текст |
Список | pgsql-announce |
== PostgreSQL Weekly News - June 07 2015 == Bug fix releases 9.4.3, 9.3.8, 9.2.12, 9.1.17, and 9.0.21 released. Upgrade! https://wiki.postgresql.org/wiki/May_2015_Fsync_Permissions_Bug == PostgreSQL Product News == BDR 0.9.1, a replication system based on logical WALs, released. http://bdr-project.org/docs/stable/release-0.9.1.html PGBR2015 (The Brazilian PostgreSQL Conference) will take place in Porto Alegre, Rio Grande do Sul, on November 18, 19 and 20. The CfP is open through Jul 15. http://pgbr.postgresql.org.br/2015/en/#call-for-papers Postgres Toolkit 0.2.1 released. https://github.com/uptimejp/postgres-toolkit StakePoint technical preview, a Portfolio Project Management solution using PostgreSQL, released. http://stakepoint.com/ tds_fdw 1.0.2, a foreign data wrapper for MS-SQL Server and Sybase, released. https://github.com/GeoffMontee/tds_fdw/releases == PostgreSQL Jobs for June == http://archives.postgresql.org/pgsql-jobs/2015-06/threads.php == PostgreSQL Local == PGCon 2015 is June 16-20 in Ottawa, Canada. http://www.pgcon.org/2015/ The second Swiss Postgres Conference will be held June 25-26, 2015 at HSR Rapperswil. http://www.postgres-conference.ch/ PGDay UK, Conference will be taking place on 7th July 2015 – it is aimed at the UK PostgreSQL Community. The CfP is open until 13 April 2015. http://www.postgresqlusergroup.org.uk PGDay Campinas 2015 will take place in Campinas on August 7. The CfP is open through May 31. http://pgdaycampinas.com.br/english/ The Call For Papers for PostgresOpen 2015, being held in Dallas, Texas from September 16th to 18th, is now open. http://2015.postgresopen.org/callforpapers/ The CfP for PostgreSQL Session #7, September 24th, 2015 in Paris, France, is open until June 15, 2015. call-for-paper <AT> postgresql-sessions <DOT> org. http://www.postgresql-sessions.org/7/about PostgreSQL Conference Europe 2015 will be held on October 27-30 in the Vienna Marriott Hotel, in Vienna, Austria. The CfP is open until August 7. http://2015.pgconf.eu/ PGConf Silicon Valley 2015 is November 17-18 at the South San Francisco Convention Center. The CfP is open through June 15. http://www.pgconfsv.com == 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. Spanish language to pwn@arpug.com.ar. == Applied Patches == Andrew Dunstan pushed: - Add a couple of missing JsonbValue type initialisers. http://git.postgresql.org/pg/commitdiff/28b29f7e44534339f88ea914794f8b64e13bc528 - Rename jsonb_replace to jsonb_set and allow it to add new values. The function is given a fourth parameter, which defaults to true. When this parameter is true, if the last element of the path is missing in the original json, jsonb_set creates it in the result and assigns it the new value. If it is false then the function does nothing unless all elements of the path are present, including the last. Based on some original code from Dmitry Dolgov, heavily modified by me. Catalog version bumped. http://git.postgresql.org/pg/commitdiff/37def4224505f3a23a5eef000f0d05daea59c5b5 - Avoid naming a variable "new", and remove bogus initializer. Per gripe from Tom Lane. http://git.postgresql.org/pg/commitdiff/50ab76d3c19c95589f4eb19683e25cb88a2506e2 Bruce Momjian pushed: - pgindent: add typedef blog URL http://git.postgresql.org/pg/commitdiff/ab959cc0ea7ee143e017e18fae23e4269a1ba435 Tom Lane pushed: - Release notes for 9.4.3, 9.3.8, 9.2.12, 9.1.17, 9.0.21. Also sneak entries for commits 97ff2a564 et al into the sections for the previous releases in the relevant branches. Those fixes did go out in the previous releases, but missed getting documented. http://git.postgresql.org/pg/commitdiff/82ec7d28211b97a2c9917b7a71edbe6b019578da - Fix planner's cost estimation for SEMI/ANTI joins with inner indexscans. When the inner side of a nestloop SEMI or ANTI join is an indexscan that uses all the join clauses as indexquals, it can be presumed that both matched and unmatched outer rows will be processed very quickly: for matched rows, we'll stop after fetching one row from the indexscan, while for unmatched rows we'll have an indexscan that finds no matching index entries, which should also be quick. The planner already knew about this, but it was nonetheless charging for at least one full run of the inner indexscan, as a consequence of concerns about the behavior of materialized inner scans --- but those concerns don't apply in the fast case. If the inner side has low cardinality (many matching rows) this could make an indexscan plan look far more expensive than it actually is. To fix, rearrange the work in initial_cost_nestloop/final_cost_nestloop so that we don't add the inner scan cost until we've inspected the indexquals, and then we can add either the full-run cost or just the first tuple's cost as appropriate. Experimentation with this fix uncovered another problem: add_path and friends were coded to disregard cheap startup cost when considering parameterized paths. That's usually okay (and desirable, because it thins the path herd faster); but in this fast case for SEMI/ANTI joins, it could result in throwing away the desired plain indexscan path in favor of a bitmap scan path before we ever get to the join costing logic. In the many-matching-rows cases of interest here, a bitmap scan will do a lot more work than required, so this is a problem. To fix, add a per-relation flag consider_param_startup that works like the existing consider_startup flag, but applies to parameterized paths, and set it for relations that are the inside of a SEMI or ANTI join. To make this patch reasonably safe to back-patch, care has been taken to avoid changing the planner's behavior except in the very narrow case of SEMI/ANTI joins with inner indexscans. There are places in compare_path_costs_fuzzily and add_path_precheck that are not terribly consistent with the new approach, but changing them will affect planner decisions at the margins in other cases, so we'll leave that for a HEAD-only fix. Back-patch to 9.3; before that, the consider_startup flag didn't exist, meaning that the second aspect of the patch would be too invasive. Per a complaint from Peter Holzer and analysis by Tomas Vondra. http://git.postgresql.org/pg/commitdiff/3f59be836c555fa679bbe0ec76de50a8b5cb23e0 - Fix some questionable edge-case behaviors in add_path() and friends. add_path_precheck was doing exact comparisons of path costs, but it really needs to do them fuzzily to be sure it won't reject paths that could survive add_path's comparisons. (This can only matter if the initial cost estimate is very close to the final one, but that turns out to often be true.) Also, it should ignore startup cost for this purpose if and only if compare_path_costs_fuzzily would do so. The previous coding always ignored startup cost for parameterized paths, which is wrong as of commit 3f59be836c555fa6; it could result in improper early rejection of paths that we care about for SEMI/ANTI joins. It also always considered startup cost for unparameterized paths, which is just as wrong though the only effect is to waste planner cycles on paths that can't survive. Instead, it should consider startup cost only when directed to by the consider_startup/ consider_param_startup relation flags. Likewise, compare_path_costs_fuzzily should have symmetrical behavior for parameterized and unparameterized paths. In this case, the best answer seems to be that after establishing that total costs are fuzzily equal, we should compare startup costs whether or not the consider_xxx flags are on. That is what it's always done for unparameterized paths, so let's make the behavior for parameterized paths match. These issues were noted while developing the SEMI/ANTI join costing fix of commit 3f59be836c555fa6, but we chose not to back-patch these fixes, because they can cause changes in the planner's choices among nearly-same-cost plans. (There is in fact one minor change in plan choice within the core regression tests.) Destabilizing plan choices in back branches without very clear improvements is frowned on, so we'll just fix this in HEAD. http://git.postgresql.org/pg/commitdiff/3b0f77601b9f9f3a2e36a813e4cd32c00e0864d6 - Stabilize query plans in rowsecurity regression test. Some recent buildfarm failures can be explained by supposing that autovacuum or autoanalyze fired on the tables created by this test, resulting in plan changes. Do a proactive VACUUM ANALYZE on the test's principal tables to try to forestall such changes. http://git.postgresql.org/pg/commitdiff/5cdf25e16843dff33dbc2ddc02941458032e3ad4 - Stabilize results of brin regression test. This test used seqscans on tenk1, with LIMIT, to build test data. That works most of the time, but if the synchronized-seqscan logic kicks in, we get varying test data. This seems likely to explain the erratic test failures on buildfarm member chipmunk, which uses smaller-than-default shared_buffers. To fix, add ORDER BY clauses to force the ordering to be what it was implicitly being assumed to be. Peter Geoghegan had noticed this with respect to one of the trouble spots, though not the ones actually causing the chipmunk issue. http://git.postgresql.org/pg/commitdiff/bac99475eb6e9e6d69a91fee30b5420b8e0115be - Fix brin "char" test to actually test what it meant to test. Casting to char, without quotes, does not give the same results as casting to "char". That meant we were not testing the brin "char" paths at all, since we ended up with a text operator not a "char" operator. http://git.postgresql.org/pg/commitdiff/78e72794a76fef3233c06800c6046aaad0704a22 - Tighten the per-operator testing done in brin regression test. Verify that the number of matches is exactly what it should be, not just that it not be zero. This should help us detect any environment-dependent issues. Also, verify that we're getting the expected type of scan plan (either bitmap or seqscan as appropriate). Right now, this is failing on the cidrcol test cases, as shown in the output file. I'll look into that in a bit, but it seems good to commit this as-is temporarily to verify that it behaves as expected on the buildfarm. http://git.postgresql.org/pg/commitdiff/79454c696bd3346a9f00f5e940398fb01a337fad - Fix brin regression test so it actually tests cidr. The problem noted in my previous commit was simpler than I thought: we weren't getting an index plan because the column wasn't indexed. http://git.postgresql.org/pg/commitdiff/1676e4381f48f7bf211f0965ad23abe10a683818 - Second try at stabilizing query plans in rowsecurity regression test. This reverts commit 5cdf25e16843dff33dbc2ddc02941458032e3ad4, which was almost immediately proven insufficient by the buildfarm. On second thought, the tables involved are not large enough that autovacuum or autoanalyze would notice them; what seems far more likely to be the culprit is the database-wide "vacuum analyze" in the concurrent gist test. That thing has given us one headache too many, so get rid of it in favor of targeted vacuuming of that test's own tables only. http://git.postgresql.org/pg/commitdiff/1d27842519999cbac7e1cca8beaef053be9c7825 - Fix incorrect order of database-locking operations in InitPostgres(). We should set MyProc->databaseId after acquiring the per-database lock, not beforehand. The old way risked deadlock against processes trying to copy or delete the target database, since they would first acquire the lock and then wait for processes with matching databaseId to exit; that left a window wherein an incoming process could set its databaseId and then block on the lock, while the other process had the lock and waited in vain for the incoming process to exit. CountOtherDBBackends() would time out and fail after 5 seconds, so this just resulted in an unexpected failure not a permanent lockup, but it's still annoying when it happens. A real-world example of a use-case is that short-duration connections to a template database should not cause CREATE DATABASE to fail. Doing it in the other order should be fine since the contract has always been that processes searching the ProcArray for a database ID must hold the relevant per-database lock while searching. Thus, this actually removes the former race condition that required an assumption that storing to MyProc->databaseId is atomic. It's been like this for a long time, so back-patch to all active branches. http://git.postgresql.org/pg/commitdiff/ac23b711dd6ccb82fb70ca0f153fe755fd809a46 - Get rid of a //-style comment. Not sure how "//XXX" got into a committed patch in the first place, as it's both content-free and against project style. pgindent made a bit of a hash of it, too. Going forward, we should have at least one buildfarm member using "gcc -ansi" to catch such things, at least till such time as we decide the project target language isn't C90 any more. I've turned this option on on dromedary. http://git.postgresql.org/pg/commitdiff/1497369e5df8bb129256f677a85327f80d3767d3 - Use a safer method for determining whether relcache init file is stale. When we invalidate the relcache entry for a system catalog or index, we must also delete the relcache "init file" if the init file contains a copy of that rel's entry. The old way of doing this relied on a specially maintained list of the OIDs of relations present in the init file: we made the list either when reading the file in, or when writing the file out. The problem is that when writing the file out, we included only rels present in our local relcache, which might have already suffered some deletions due to relcache inval events. In such cases we correctly decided not to overwrite the real init file with incomplete data --- but we still used the incomplete initFileRelationIds list for the rest of the current session. This could result in wrong decisions about whether the session's own actions require deletion of the init file, potentially allowing an init file created by some other concurrent session to be left around even though it's been made stale. Since we don't support changing the schema of a system catalog at runtime, the only likely scenario in which this would cause a problem in the field involves a "vacuum full" on a catalog concurrently with other activity, and even then it's far from easy to provoke. Remarkably, this has been broken since 2002 (in commit 786340441706ac1957a031f11ad1c2e5b6e18314), but we had never seen a reproducible test case until recently. If it did happen in the field, the symptoms would probably involve unexpected "cache lookup failed" errors to begin with, then "could not open file" failures after the next checkpoint, as all accesses to the affected catalog stopped working. Recovery would require manually removing the stale "pg_internal.init" file. To fix, get rid of the initFileRelationIds list, and instead consult syscache.c's list of relations used in catalog caches to decide whether a relation is included in the init file. This should be a tad more efficient anyway, since we're replacing linear search of a list with ~100 entries with a binary search. It's a bit ugly that the init file contents are now so directly tied to the catalog caches, but in practice that won't make much difference. Back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/f3b5565dd4e59576be4c772da364704863e6a835 Fujii Masao pushed: - Minor improvement to txid_current() documentation. Michael Paquier, reviewed by Christoph Berg and Naoya Anzai http://git.postgresql.org/pg/commitdiff/37013621f3b0e296aa71b812ca9d46871ead95e2 - Fix some issues in pg_class.relminmxid and pg_database.datminmxid documentation. Correct the name of directory which those catalog columns allow to be shrunk. Correct the name of symbol which is used as the value of pg_class.relminmxid when the relation is not a table. Fix "ID ID" typo. Backpatch to 9.3 where those cataog columns were introduced. http://git.postgresql.org/pg/commitdiff/38d500ac2e5d4d4f3468b505962fb85850c1ff4b - Remove -i/--ignore-version option from pg_dump, pg_dumpall and pg_restore. The commit c22ed3d523782c43836c163c16fa5a7bb3912826 turned the -i/--ignore-version options into no-ops and marked as deprecated. Considering we shipped that in 8.4, it's time to remove all trace of those switches, per discussion. We'd still have to wait a couple releases before it'd be safe to use -i for something else, but it'd be a start. http://git.postgresql.org/pg/commitdiff/232cd63b1f26e2ee3b3e03da8fc7348f4b067946 Peter Eisentraut pushed: - doc: Fix PDF build with FOP. Because of a bug in the DocBook XSL FO style sheet, an xref to a varlistentry whose term includes an indexterm fails to build. One such instance was introduced in commit 5086dfceba79ecd5d1eb28b8f4ed5221838ff3a6. Fix by adding the upstream bug fix to our customization layer. http://git.postgresql.org/pg/commitdiff/afae1f78547b8ff02cd2d07fe845a28e37a3b272 Robert Haas pushed: - docs: Fix list of object types pg_table_is_visible() can handle. Materialized views and foreign tables were missing from the list, probably because they are newer than the other object types that were mentioned. Etsuro Fujita http://git.postgresql.org/pg/commitdiff/1c645da8ebb5532105481ad77bb1d9a671b1f086 - doc: Session identifiers truncate, not round, the backend start time. Joel Jacobson http://git.postgresql.org/pg/commitdiff/99cfd5e136e2a20c77021390a1378d18a24b7388 - Cope with possible failure of the oldest MultiXact to exist. Recent commits, mainly b69bf30b9bfacafc733a9ba77c9587cf54d06c0c and 53bb309d2d5a9432d2602c93ed18e58bd2924e15, introduced mechanisms to protect against wraparound of the MultiXact member space: the number of multixacts that can exist at one time is limited to 2^32, but the total number of members in those multixacts is also limited to 2^32, and older code did not take care to enforce the second limit, potentially allowing old data to be overwritten while it was still needed. Unfortunately, these new mechanisms failed to account for the fact that the code paths in which they run might be executed during recovery or while the cluster was in an inconsistent state. Also, they failed to account for the fact that users who used pg_upgrade to upgrade a PostgreSQL version between 9.3.0 and 9.3.4 might have might oldestMultiXid = 1 in the control file despite the true value being larger. To fix these problems, first, avoid unnecessarily examining the mmembers of MultiXacts when the cluster is not known to be consistent. TruncateMultiXact has done this for a long time, and this patch does not fix that. But the new calls used to prevent member wraparound are not needed until we reach normal running, so avoid calling them earlier. (SetMultiXactIdLimit is actually called before InRecovery is set, so we can't rely on that; we invent our own multixact-specific flag instead.) Second, make failure to look up the members of a MultiXact a non-fatal error. Instead, if we're unable to determine the member offset at which wraparound would occur, postpone arming the member wraparound defenses until we are able to do so. If we're unable to determine the member offset that should force autovacuum, force it continuously until we are able to do so. If we're unable to deterine the member offset at which we should truncate the members SLRU, log a message and skip truncation. An important consequence of these changes is that anyone who does have a bogus oldestMultiXid = 1 value in pg_control will experience immediate emergency autovacuuming when upgrading to a release that contains this fix. The release notes should highlight this fact. If a user has no pg_multixact/offsets/0000 file, but has oldestMultiXid = 1 in the control file, they may wish to vacuum any tables with relminmxid = 1 prior to upgrading in order to avoid an immediate emergency autovacuum after the upgrade. This must be done with a PostgreSQL version 9.3.5 or newer and with vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age set to 0. This patch also adds an additional log message at each database server startup, indicating either that protections against member wraparound have been engaged, or that they have not. In the latter case, once autovacuum has advanced oldestMultiXid to a sane value, the message indicating that the guards have been engaged will appear at the next checkpoint. A few additional messages have also been added at the DEBUG1 level so that the correct operation of this code can be properly audited. Along the way, this patch fixes another, related bug in TruncateMultiXact that has existed since PostgreSQL 9.3.0: when no MultiXacts exist at all, the truncation code looks up NextMultiXactId, which doesn't exist yet. This can lead to TruncateMultiXact removing every file in pg_multixact/offsets instead of keeping one around, as it should. This in turn will cause the database server to refuse to start afterwards. Patch by me. Review by Álvaro Herrera, Andres Freund, Noah Misch, and Thomas Munro. http://git.postgresql.org/pg/commitdiff/068cfadf9e2190bdd50a30d19efc7c9f0b825b5e == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == Peter Geoghegan sent in a patch to illustrate a possible security bug in RLS. Dean Rasheed sent in a patch to refactor the implementation of RLS. Laurenz Albe sent in a patch to allow using literal tabs in psql. Etsuro Fujita sent in a doc patch to add materialized views and foreign tables to database objects that pg_table_is_visible() can be used with. Michael Paquier sent in two more revisions of a patch to fix a memory leak in XLogFileCopy. Fabien COELHO sent in two revisions of a patch to enable flushing while writing during checkpoints. Andreas Seltenreich sent in a patch to add error handling to byteaout. Michael Paquier sent in a patch to remove the use of %.*s in several parts of the psql code to make the code more solid when facing non-ASCII strings. Craig Ringer sent in another revision of a patch to allow sampling of only some queries by auto_explain. Peter Geoghegan sent in a patch to desupport jsonb subscript deletion on objects, which was causing surprising outcomes. Jeevan Chalke sent in another revision of a patch to implement a two-argument version of current_setting() with fallback. Kaigai Kouhei sent in another revision of a patch to allow custom-join children. Amit Kapila and Andrew Dunstan traded patches to remove only symlinks during recovery. Julien Rouhaud sent in a patch fix an issue where when archiver aborts, pg_stat_archiver doesn't report those failed attempts. Petr Korobeinikov sent in another revision of a patch to implement \ev and \sv (edit view, and show view, respectively) in psql. Peter Geoghegan sent in a patch to add a regression test in cases where RLS again fails to play nicely with UPDATE ... WHERE CURRENT OF. Thomas Munro sent in a patch to fix a bogus subtrans wraparound error.
В списке pgsql-announce по дате отправления:
Предыдущее
От: Geoff MonteeДата:
Сообщение: tds_fdw 1.0.2 - Foreign Data Wrapper for MS SQL Server and Sybase