== PostgreSQL Weekly News - June 16, 2019 ==
От | David Fetter |
---|---|
Тема | == PostgreSQL Weekly News - June 16, 2019 == |
Дата | |
Msg-id | 20190616170427.GA3308@fetter.org обсуждение исходный текст |
Список | pgsql-announce |
== PostgreSQL Weekly News - June 16, 2019 == Security releases 11.4, 10.9, 9.6.14, 9.5.18, 9.4.23 coming soon. Get ready to upgrade! https://www.postgresql.org/message-id/98EFF6DC-805B-4309-AD89-6F3223CBDA7F%40postgresql.org == PostgreSQL Product News == psycopg2 2.8.3, a Python connector for PostgreSQL, released. http://initd.org/psycopg/articles/2019/06/14/psycopg-283-released/ == PostgreSQL Jobs for June == http://archives.postgresql.org/pgsql-jobs/2019-06/ == PostgreSQL Local == pgibz will be held in Ibiza, Spain on June 19-23, 2019. The CfP is open. https://pgibz.io/ Swiss PGDay 2019 will take place in Rapperswil (near Zurich) on June 28, 2019. Registration is open. http://www.pgday.ch/2019/ PostgresLondon 2019 will be July 2-3, 2019 with an optional training day on July 1. http://postgreslondon.org PGConf.Brazil 2019 will take place August 1-3, 2019 in São Paulo. http://pgconf.com.br The first Austrian pgDay, will take place September 6, 2019 at the Hilton Garden Inn in Wiener Neustadt. https://pgday.at/en/ PostgresOpen will be September 11th - 13th, 2019 in Orlando, Florida at the Rosen Centre Hotel. The CfP is open at https://2019.postgresopen.org/callforpapers/ https://2019.postgresopen.org/ PostgresConf South Africa 2019 will take place in Johannesburg on October 8-9, 2019 The Call for Papers is open through June 30, 2019. https://postgresconf.org/conferences/SouthAfrica2019 == 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 PST8PDT to david@fetter.org. == Applied Patches == Noah Misch pushed: - Reconcile nodes/*funcs.c with PostgreSQL 12 work. One would have needed out-of-tree code to observe the defects. Remove unreferenced fields instead of completing their support functions. Since in-tree code can't reach _readIntoClause(), no catversion bump. https://git.postgresql.org/pg/commitdiff/44982e7d09cf9b1a08fded7fb0ca9a60dae869a5 - MSVC: Reconcile clean.bat with PostgreSQL 12 work. https://git.postgresql.org/pg/commitdiff/ae78a9456c8986a8869ec470b40e177b5617a2f1 Alexander Korotkov pushed: - Fix docs indentation in pgtrgm.sgml. 5871b884 introduced pg_trgm.word_similarity_threshold GUC, but its documentation contains wrong indentation. This commit fixes that. Backpatch for easier backpatching of other documentation fixes. Discussion: https://postgr.es/m/4c735d30-ab59-fc0e-45d8-f90eb5ed3855%402ndquadrant.com Author: Ian Barwick Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/852ddfbbe2121c905f2ecd750b1ec4cbfbea9f76 - Add docs of missing GUC to pgtrgm.sgml. be8a7a68 introduced pg_trgm.strict_word_similarity_threshold GUC, but missed docs for that. This commit fixes that. Discussion: https://postgr.es/m/fc907f70-448e-fda3-3aa4-209a59597af0%402ndquadrant.com Author: Ian Barwick Reviewed-by: Masahiko Sawada, Michael Paquier Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/ba3783e0244e3502d1b08896ffc72b3a88dbdb83 - Fix operator naming in pg_trgm GUC option descriptions. Descriptions of pg_trgm GUC options have % replaced with %% like it was a printf-like format. But that's not needed since they are just plain strings. This commit fixed that. Backpatch to last supported version since this error present from the beginning. Reported-by: Masahiko Sawada Discussion: https://postgr.es/m/CAD21AoAgPKODUsu9gqUFiNqEOAqedStxJ-a0sapsJXWWAVp%3Dxg%40mail.gmail.com Backpatch-through: 9.4 https://git.postgresql.org/pg/commitdiff/b6987a885ba9216e1f4319441303ee115556922c Álvaro Herrera pushed: - Make pg_dump emit ATTACH PARTITION instead of PARTITION OF (reprise). Using PARTITION OF can result in column ordering being changed from the database being dumped, if the partition uses a column layout different from the parent's. It's not pg_dump's job to editorialize on table definitions, so this is not acceptable; back-patch all the way back to pg10, where partitioned tables where introduced. This change also ensures that partitions end up in the correct tablespace, if different from the parent's; this is an oversight in ca4103025dfe (in pg12 only). Partitioned indexes (in pg11) don't have this problem, because they're already created as independent indexes and attached to their parents afterwards. This change also has the advantage that the partition is restorable from the dump (as a standalone table) even if its parent table isn't restored. The original commits (3b23552ad8bb in branch master) failed to cover subsidiary column elements correctly, such as NOT NULL constraint and CHECK constraints, as reported by Rushabh Lathia (initially as a failure to restore serial columns). They were reverted. This recapitulation commit fixes those problems. Add some pg_dump tests to verify these things more exhaustively, including constraints with legacy-inheritance tables, which were not tested originally. In branches 10 and 11, add a local constraint to the pg_dump test partition that was added by commit 2d7eeb1b1492 to master. Author: Álvaro Herrera, David Rowley Reviewed-by: Álvaro Herrera Discussion: https://postgr.es/m/CAKJS1f_1c260nOt_vBJ067AZ3JXptXVRohDVMLEBmudX1YEx-A@mail.gmail.com Discussion: https://postgr.es/m/20190423185007.GA27954@alvherre.pgsql Discussion: https://postgr.es/m/CAGPqQf0iQV=PPOv2Btog9J9AwOQp6HmuVd6SbGTR_v3Zp2XT1w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/33a53130a89447e171a8268ae0b221bb48af6468 - Fix order of steps in DISCARD ALL documentation. The docs have always been slightly inaccurate, but got particularly so in a874fe7b4c89, which made DISCARD ALL occur before everything else; reorder. Author: Jan Chochol Discussion: https://postgr.es/m/CAEASf_3TzBbnXm64HpnD5zCZEh8An9jN8ubMR=De-vOXHMHGeA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/9f05c44ba4a4a6a857b957734bb369a2bb4dd62b - Fix double-word typos. Discussion: https://postgr.es/m/20190612184527.GA24266@alvherre.pgsql Reviewed-by: Michaël Paquier https://git.postgresql.org/pg/commitdiff/b9768458154de6c001af23efc02514f120c6094f - Avoid spurious deadlocks when upgrading a tuple lock. When two (or more) transactions are waiting for transaction T1 to release a tuple-level lock, and transaction T1 upgrades its lock to a higher level, a spurious deadlock can be reported among the waiting transactions when T1 finishes. The simplest example case seems to be: T1: select id from job where name = 'a' for key share; Y: select id from job where name = 'a' for update; -- starts waiting for X Z: select id from job where name = 'a' for key share; T1: update job set name = 'b' where id = 1; Z: update job set name = 'c' where id = 1; -- starts waiting for X T1: rollback; At this point, transaction Y is rolled back on account of a deadlock: Y holds the heavyweight tuple lock and is waiting for the Xmax to be released, while Z holds part of the multixact and tries to acquire the heavyweight lock (per protocol) and goes to sleep; once X releases its part of the multixact, Z is awakened only to be put back to sleep on the heavyweight lock that Y is holding while sleeping. Kaboom. This can be avoided by having Z skip the heavyweight lock acquisition. As far as I can see, the biggest downside is that if there are multiple Z transactions, the order in which they resume after X finishes is not guaranteed. Backpatch to 9.6. The patch applies cleanly on 9.5, but the new tests don't work there (because isolationtester is not smart enough), so I'm not going to risk it. Author: Oleksii Kliukin Discussion: https://postgr.es/m/B9C9D7CD-EB94-4635-91B6-E558ACEC0EC3@hintbits.com https://git.postgresql.org/pg/commitdiff/de87a084c0a5ac927017cd0834b33a932651cfc9 - Silence compiler warning. Introduced in de87a084c0a5. https://git.postgresql.org/pg/commitdiff/3da73d6839dc47f1f47ca57974bf28e5abd9b572 - Tweak libpq's PQhost, PQhostaddr, and psql's \connect. Fixes some problems introduced by 6e5f8d489acc: * When reusing conninfo data from the previous connection in \connect, the host address should only be reused if it was specified as hostaddr; if it wasn't, then 'host' is resolved afresh. We were reusing the same IP address, which ignores a possible DNS change as well as any other addresses that the name resolves to than the one that was used in the original connection. * PQhost, PQhostaddr: Don't present user-specified hostaddr when we have an inet_net_ntop-produced equivalent address. The latter has been put in canonical format, which is cleaner (so it produces "127.0.0.1" when given "host=2130706433", for example). * Document the hostaddr-reusing aspect of \connect. * Fix some code comments Author: Fabien Coelho Reported-by: Noah Misch Discussion: https://postgr.es/m/20190527203713.GA58392@gust.leadboat.com https://git.postgresql.org/pg/commitdiff/313f56ce2d1b9dfd3483e4f39611baa27852835a - Add pg_dumpall --rows-per-insert. Commit 7e413a0f82c8 added that option to pg_dump, but neglected to teach pg_dumpall how to pass it along. Repair. Author: Fabien Coelho Reported-by: Peter Eisentraut Reviewed-by: David Rowley Discussion: https://postgr.es/m/45f50c59-ddbb-8cf2-eedb-81003f603528@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/a193cbec1199e4e529c46645b7aecbf38956048b Robert Haas pushed: - tableam: Fix index_build_range_scan parameter name. All of the other code thinks that the 8th parameter is the number of blocks, but this declaration thinks that it's the ending block number. Repair this inconsistency. Patch by me, reviewed by Andres Freund. Discussion: http://postgr.es/m/CA+TgmoY49ManQWnJtiwkuytXBkmyTuDFqb74Pr4Zn2Nq9TuNBQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/132a1c101a205ee52ec7d731abcb8593a6354097 Michaël Paquier pushed: - Fix documentation of ALTER TABLE for stored values. Author: Masahiko Sawada Discussion: https://postgr.es/m/CAD21AoAA_gvZ002U6kovOHu0FsM7ieoCzdSqWBd7_KaQL0UMKg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/727e45c8a457ab7378494ff33bf3b50607d303a8 - Fix handling of COMMENT for domain constraints. For a non-superuser, changing a comment on a domain constraint was leading to a cache lookup failure as the code tried to perform the ownership lookup on the constraint OID itself, thinking that it was a type, but this check needs to happen on the type the domain constraint relies on. As the type a domain constraint relies on can be guessed directly based on the constraint OID, first fetch its type OID and perform the ownership on it. This is broken since 7eca575, which has split the handling of comments for table constraints and domain constraints, so back-patch down to 9.5. Reported-by: Clemens Ladisch Author: Daniel Gustafsson, Michael Paquier Reviewed-by: Álvaro Herrera Discussion: https://postgr.es/m/15833-808e11904835d26f@postgresql.org Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/ceac4505d3428a8414b4f3d6708ea81506811a5f - Use OpenSSL-specific ifdefs in sha2.h. In order to separate OpenSSL's SHA symbols, this header has been using USE_SSL, which is equivalent to USE_OPENSSL. There is now only one SSL implementation included in the tree, so this works fine, but when adding a new SSL implementation this would run into failures. Author: Daniel Gustafsson Discussion: https://postgr.es/m/0DF29010-CE26-4F51-85A6-9C8ABF5536F9@yesql.se https://git.postgresql.org/pg/commitdiff/96719e52b1abd3a8c712d06a809b7e6c97c2e3a0 - Fix typos and inconsistencies in code comments. Author: Alexander Lakhin Discussion: https://postgr.es/m/dec6aae8-2d63-639f-4d50-20e229fb83e3@gmail.com https://git.postgresql.org/pg/commitdiff/f43608bda2111a1fda514d1bed4df313ee2bbec3 Etsuro Fujita pushed: - postgres_fdw: Reorder C includes. Reorder header files in postgres_fdw.c and connection.c in alphabetical order. Author: Etsuro Fujita Reviewed-by: Alvaro Herrera Discussion: https://postgr.es/m/CAPmGK17ZmNb-EELqu8LmMh2t2uFdbfWNVDEfDO5-bpejHPONMQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/92a88644d2b7208e98d9e913c3a574c5c2fe7b78 - postgres_fdw: Account for triggers in non-direct remote UPDATE planning. Previously, in postgresPlanForeignModify, we planned an UPDATE operation on a foreign table so that we transmit only columns that were explicitly targets of the UPDATE, so as to avoid unnecessary data transmission, but if there were BEFORE ROW UPDATE triggers on the foreign table, those triggers might change values for non-target columns, in which case we would miss sending changed values for those columns. Prevent optimizing away transmitting all columns if there are BEFORE ROW UPDATE triggers on the foreign table. This is an oversight in commit 7cbe57c34 which added triggers on foreign tables, so apply the patch all the way back to 9.4 where that came in. Author: Shohei Mochizuki Reviewed-by: Amit Langote Discussion: https://postgr.es/m/201905270152.x4R1q3qi014550@toshiba.co.jp https://git.postgresql.org/pg/commitdiff/8b6da83d162cb0ac9f6d21082727bbd45c972c53 - postgres_fdw: Fix costing of pre-sorted foreign paths with local stats. Commit aa09cd242 modified estimate_path_cost_size() so that it reuses cached costs of a basic foreign path for a given foreign-base/join relation when costing pre-sorted foreign paths for that relation, but it incorrectly re-computed retrieved_rows, an estimated number of rows fetched from the remote side, which is needed for costing both the basic and pre-sorted foreign paths. To fix, handle retrieved_rows the same way as the cached costs: store in that relation's fpinfo the retrieved_rows estimate computed for costing the basic foreign path, and reuse it when costing the pre-sorted foreign paths. Also, reuse the rows/width estimates stored in that relation's fpinfo when costing the pre-sorted foreign paths, to make the code consistent. In commit ffab494a4, to extend the costing mentioned above to the foreign-grouping case, I made a change to add_foreign_grouping_paths() to store in a given foreign-grouped relation's RelOptInfo the rows estimate for that relation for reuse, but this patch makes that change unnecessary since we already store the row estimate in that relation's fpinfo, which this patch reuses when costing a foreign path for that relation with the sortClause ordering; remove that change. In passing, fix thinko in commit 7012b132d: in estimate_path_cost_size(), the width estimate for a given foreign-grouped relation to be stored in that relation's fpinfo was reset incorrectly when costing a basic foreign path for that relation with local stats. Apply the patch to HEAD only to avoid destabilizing existing plan choices. Author: Etsuro Fujita Discussion: https://postgr.es/m/CAPmGK17jaJLPDEkgnP2VmkOg=5wT8YQ1CqssU8JRpZ_NSE+dqQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/08d2d58a2a1c8ef8d39e8132d39ee14a1d029500 Andres Freund pushed: - Don't access catalogs to validate GUCs when not connected to a DB. Vignesh found this bug in the check function for default_table_access_method's check hook, but that was just copied from older GUCs. Investigation by Michael and me then found the bug in further places. When not connected to a database (e.g. in a walsender connection), we cannot perform (most) GUC checks that need database access. Even when only shared tables are needed, unless they're nailed (c.f. RelationCacheInitializePhase2()), they cannot be accessed without pg_class etc. being present. Fix by extending the existing IsTransactionState() checks to also check for MyDatabaseOid. Reported-By: Vignesh C, Michael Paquier, Andres Freund Author: Vignesh C, Andres Freund Discussion: https://postgr.es/m/CALDaNm1KXK9gbZfY-p_peRFm_XrBh1OwQO1Kk6Gig0c0fVZ2uw%40mail.gmail.com Backpatch: 9.4- https://git.postgresql.org/pg/commitdiff/fff2a7d7bd09db38e1bafc1303c29b10a9805dc0 Tom Lane pushed: - Fix conversion of JSON strings to JSON output columns in json_to_record(). json_to_record(), when an output column is declared as type json or jsonb, should emit the corresponding field of the input JSON object. But it got this slightly wrong when the field is just a string literal: it failed to escape the contents of the string. That typically resulted in syntax errors if the string contained any double quotes or backslashes. jsonb_to_record() handles such cases correctly, but I added corresponding test cases for it too, to prevent future backsliding. Improve the documentation, as it provided only a very hand-wavy description of the conversion rules used by these functions. Per bug report from Robert Vollmert. Back-patch to v10 where the error was introduced (by commit cf35346e8). Note that PG 9.4 - 9.6 also get this case wrong, but differently so: they feed the de-escaped contents of the string literal to json[b]_in. That behavior is less obviously wrong, so possibly it's being depended on in the field, so I won't risk trying to make the older branches behave like the newer ones. Discussion: https://postgr.es/m/D6921B37-BD8E-4664-8D5F-DB3525765DCD@vllmrt.net https://git.postgresql.org/pg/commitdiff/6f34fcbbd5ad5a6546710e7b90e6331cacfd36dc - Fix ALTER COLUMN TYPE failure with a partial exclusion constraint. ATExecAlterColumnType failed to consider the possibility that an index that needs to be rebuilt might be a child of a constraint that needs to be rebuilt. We missed this so far because usually a constraint index doesn't have a direct dependency on its table, just on the constraint object. But if there's a WHERE clause, then dependency analysis of the WHERE clause results in direct dependencies on the column(s) mentioned in WHERE. This led to trying to drop and rebuild both the constraint and its underlying index. In v11/HEAD, we successfully drop both the index and the constraint, and then try to rebuild both, and of course the second rebuild hits a duplicate-index-name problem. Before v11, it fails with obscure messages about a missing relation OID, due to trying to drop the index twice. This is essentially the same kind of problem noted in commit 20bef2c31: the possible dependency linkages are broader than what ATExecAlterColumnType was designed for. It was probably OK when written, but it's certainly been broken since the introduction of partial exclusion constraints. Fix by adding an explicit check for whether any of the indexes-to-be-rebuilt belong to any of the constraints-to-be-rebuilt, and ignoring any that do. In passing, fix a latent bug introduced by commit 8b08f7d48: in get_constraint_index() we must "continue" not "break" when rejecting a relation of a wrong relkind. This is harmless today because we don't expect that code path to be taken anyway; but if there ever were any relations to be ignored, the existing coding would have an extremely undesirable dependency on the order of pg_depend entries. Also adjust a couple of obsolete comments. Per bug #15835 from Yaroslav Schekin. Back-patch to all supported branches. Discussion: https://postgr.es/m/15835-32d9b7a76c06a7a9@postgresql.org https://git.postgresql.org/pg/commitdiff/e76de886157b7f974d4d247908b242607cfbf043 - Fix incorrect printing of queries with duplicated join names. Given a query in which multiple JOIN nodes used the same alias (which'd necessarily be in different sub-SELECTs), ruleutils.c would assign the JOIN nodes distinct aliases for clarity ... but then it forgot to print the modified aliases when dumping the JOIN nodes themselves. This results in a dump/reload hazard for views, because the emitted query is flat-out incorrect: Vars will be printed with table names that have no referent. This has been wrong for a long time, so back-patch to all supported branches. Philip Dubé Discussion: https://postgr.es/m/CY4PR2101MB080246F2955FF58A6ED1FEAC98140@CY4PR2101MB0802.namprd21.prod.outlook.com https://git.postgresql.org/pg/commitdiff/3d99a81397abb8bb7b95aee794d6644e174e174f - Doc: improve description of allowed spellings for Boolean input. datatype.sgml failed to explain that boolin() accepts any unique prefix of the basic input strings. Indeed it was actively misleading because it called out a few minimal prefixes without mentioning that there were more valid inputs. I also felt that it wasn't doing anybody any favors by conflating SQL key words, valid Boolean input, and string literals containing valid Boolean input. Rewrite in hopes of reducing the confusion. Per bug #15836 from Yuming Wang, as diagnosed by David Johnston. Back-patch to supported branches. Discussion: https://postgr.es/m/15836-656fab055735f511@postgresql.org https://git.postgresql.org/pg/commitdiff/9729c9360886bee7feddc6a1124b0742de4b9f3d - Doc: fix bogus example. This wasn't incorrect SQL, but it was doing cm-to-inch conversion backward, so it might confuse readers. Per bug #15849 from TAKATSUKA Haruka. Discussion: https://postgr.es/m/15849-37ad0c561a836107@postgresql.org https://git.postgresql.org/pg/commitdiff/7dc6ae37def50b5344c157eee5e029a09359f8ee - Mark ReplicationSlotCtl as PGDLLIMPORT. Also MyReplicationSlot, in branches where it wasn't already. This was discussed in the thread that resulted in c572599c6, but for some reason nobody pulled the trigger. Now that we have another request for the same thing, we should just do it. Craig Ringer Discussion: https://postgr.es/m/CAMsr+YFTsq-86MnsNng=mPvjjh5EAbzfMK0ptJPvzyvpFARuRg@mail.gmail.com Discussion: https://postgr.es/m/345138875.20190611151943@cybertec.at https://git.postgresql.org/pg/commitdiff/3c8f8f6ebefb3c2debd1cad1ba445a729290dac7 - Avoid combinatorial explosion in add_child_rel_equivalences(). If an EquivalenceClass member expression includes variables from multiple appendrels, then instead of producing one substituted expression per child relation as intended, we'd create additional child expressions for combinations of children of different appendrels. This happened because the child expressions generated while considering the first appendrel were taken as sources during substitution of the second appendrel, and so on. The extra expressions are useless, and are harmless unless there are too many of them --- but if you have several appendrels with a thousand or so members each, it gets bad fast. To fix, consider only original (non-em_is_child) EC members as candidates to be expanded. This requires the ability to substitute directly from a top parent relation's Vars to those of an indirect descendant relation, but we already have that in adjust_appendrel_attrs_multilevel(). Per bug #15847 from Feike Steenbergen. This is a longstanding misbehavior, but it's only worth worrying about when there are more appendrel children than we've historically considered wise to use. So I'm not going to take the risk of back-patching this. Discussion: https://postgr.es/m/15847-ea3734094bf8ae61@postgresql.org https://git.postgresql.org/pg/commitdiff/d25ea0127598dd0ad27a029215172f396fbd1009 - Further fix privileges on pg_statistic_ext[_data]. We don't need to restrict column privileges on pg_statistic_ext; all of that data is OK to read publicly. What we *do* need to do, which was overlooked by 6cbfb784c, is revoke public read access on pg_statistic_ext_data; otherwise we still have the same security hole we started with. Catversion bump to ensure that installations calling themselves beta2 will have this fix. Diagnosis/correction by Dean Rasheed and Tomas Vondra, but I'm going to go ahead and push this fix ASAP so we get more buildfarm cycles on it. Discussion: https://postgr.es/m/8833.1560647898@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/6973b058bc8d32e104bed99c134a4fab4b5dfe13 David Rowley pushed: - doc: Add best practises section to partitioning docs. A few questionable partitioning designs have been cropping up lately around the mailing lists. Generally, these cases have been partitioning using too many partitions which have caused performance or OOM problems for the users. Since we have very little else to guide users into good design, here we add a new section to the partitioning documentation with some best practise guidelines for good design. Reviewed-by: Justin Pryzby, Amit Langote, Alvaro Herrera Discussion: https://postgr.es/m/CAKJS1f-2rx+E9mG3xrCVHupefMjAp1+tpczQa9SEOZWyU7fjEA@mail.gmail.com Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/e788e849addd56007a0e75f3b5514f294a0f3bca - doc: Fix grammatical error in partitioning docs. Reported-by: Amit Langote Discussion: https://postgr.es/m/CA+HiwqGZFkKi0TkBGYpr2_5qrRAbHZoP47AP1BRLUOUkfQdy_A@mail.gmail.com Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/ddc053dc503a773267a4d035418e0c81ce3b4851 Bruce Momjian pushed: - doc: PG 12 relnotes, merge new SQL partition function items. Reported-by: Andres Freund Discussion: https://postgr.es/m/20190528155823.3e4cezblxjxotq2q@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/6a631a454664c145b7da88c9ef3976f8ac3ab0b1 - doc: PG 12 relnotes, list added snowball/FTS languages. Reported-by: Adrien Nayrat Discussion: https://postgr.es/m/9d6a7515-bcd8-05be-d2a5-e81dc11023cd@anayrat.info https://git.postgresql.org/pg/commitdiff/968072837173a0c0fa426114b17177f997b85473 - docs: PG 12 relnotes, update btree items. Reported-by: Peter Geoghegan Discussion: https://postgr.es/m/CAH2-Wzn-aH4ToZEWR05ELSSp7bO_JMn=sMPfUhNruwVmCXKh-w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4bfb79ff6b1fd400b3dbc761921472173675a968 - doc: PG 12 relnotes: update wording on truncate/vacuum item. This item prevents unauthorized locking of relations, and the previous wording was unclear. Reported-by: Michael Paquier Discussion: https://postgr.es/m/20190522072651.GC1278@paquier.xyz https://git.postgresql.org/pg/commitdiff/4f41a7227511a3f96148b7f3d4ae6f0c350bc7b2 - docs: PG 12 relnotes, move vacuumdb to the client app section. It was previously incorrectly placed in the server application section. Reported-by: Tatsuo Ishii Discussion: https://postgr.es/m/20190526.215341.1023150119477784132.t-ishii@sraoss.co.jp https://git.postgresql.org/pg/commitdiff/18ef7a34fe616dc63f7de672a5ab7dd88137dc5c - docs: PG 12 relnotes, update self-join item to mention ctid. Reported-by: Robert Haas Discussion: https://postgr.es/m/CA+TgmoY==TZwk-4cM3Usebq1f=j7Hpm1brFbk1v48h45wsWzzg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/be2e024bd6527b050da4ad02c921869fed84bb5d - doc: PG 12 relnotes, add mention of single-child optimization. Add mention of single-child optimization for partitions and UNION ALL. Reported-by: David Rowley Discussion: https://postgr.es/m/CAKJS1f8R8riwBXw==7ijV=UZNuhP+3qXgDBKSiM+=_cTf4mXXw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/b9a0724cf7a3ecf19aaaa098ffd26e52bb800c5c Andrew Gierth pushed: - Prefer timezone name "UTC" over alternative spellings. tzdb 2019a made "UCT" a link to the "UTC" zone rather than a separate zone with its own abbreviation. Unfortunately, our code for choosing a timezone in initdb has an arbitrary preference for names earlier in the alphabet, and so it would choose the spelling "UCT" over "UTC" when the system is running on a UTC zone. Commit 23bd3cec6 was backpatched in order to address this issue, but that code helps only when /etc/localtime exists as a symlink, and does nothing to help on systems where /etc/localtime is a copy of a zone file (as is the standard setup on FreeBSD and probably some other platforms too) or when /etc/localtime is simply absent (giving UTC as the default). Accordingly, add a preference for the spelling "UTC", such that if multiple zone names have equally good content matches, we prefer that name before applying the existing arbitrary rules. Also add a slightly lower preference for "Etc/UTC"; lower because that preserves the previous behaviour of choosing the shorter name, but letting us still choose "Etc/UTC" over "Etc/UCT" when both exist but "UTC" does not (not common, but I've seen it happen). Backpatch all the way, because the tzdb change that sparked this issue is in those branches too. https://git.postgresql.org/pg/commitdiff/e3846a00c2f87402dcedf7f07950ab2d89cf5827 Tomáš Vondra pushed: - Rework the pg_statistic_ext catalog. Since extended statistic got introduced in PostgreSQL 10, there was a single catalog pg_statistic_ext storing both the definitions and built statistic. That's however problematic when a user is supposed to have access only to the definitions, but not to user data. Consider for example pg_dump on a database with RLS enabled - if the pg_statistic_ext catalog respects RLS (which it should, if it contains user data), pg_dump would not see any records and the result would not define any extended statistics. That would be a surprising behavior. Until now this was not a pressing issue, because the existing types of extended statistic (functional dependencies and ndistinct coefficients) do not include any user data directly. This changed with introduction of MCV lists, which do include most common combinations of values. The easiest way to fix this is to split the pg_statistic_ext catalog into two - one for definitions, one for the built statistic values. The new catalog is called pg_statistic_ext_data, and we're maintaining a 1:1 relationship with the old catalog - either there are matching records in both catalogs, or neither of them. Bumped CATVERSION due to changing system catalog definitions. Author: Dean Rasheed, with improvements by me Reviewed-by: Dean Rasheed, John Naylor Discussion: https://postgr.es/m/CAEZATCUhT9rt7Ui%3DVdx4N%3D%3DVV5XOK5dsXfnGgVOz_JhAicB%3DZA%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/6cbfb784c3c91146148a76d50cda6f69ae6a79fb - Add pg_stats_ext view for extended statistics. Regular per-column statistics are stored in pg_statistics catalog, which is however rather difficult to read, so we also have pg_stats view with a human-reablable version of the data. For extended statistic the catalog was fairly easy to read, so we did not have such human-readable view so far. Commit 9b6babfa2d however did split the catalog into two, which makes querying harder. Furthermore, we want to show the multi-column MCV list in a way similar to per-column stats (and not as a bytea value). This commit introduces pg_stats_ext view, joining the two catalogs and massaging the data to produce human-readable output similar to pg_stats. It also considers RLS and access privileges - the data is shown only when the user has access to all columns the extended statistic is defined on. Bumped CATVERSION due to adding new system view. Author: Dean Rasheed, with improvements by me Reviewed-by: Dean Rasheed, John Naylor Discussion: https://postgr.es/m/CAEZATCUhT9rt7Ui%3DVdx4N%3D%3DVV5XOK5dsXfnGgVOz_JhAicB%3DZA%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/aa087ec64f703a52f3c48c70117bb02e578f1802 - Fix incorrect CREATE STATISTICS example in docs. The example was incorrectly using parantheses around the list of columns, so just drop them. Reported-By: Robert Haas Discussion: https://postgr.es/m/CA%2BTgmoZZEMAqWMAfvLHZnK57SoxOutgvE-ALO94WsRA7zZ7wyQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/7f44efa10b6ea3e5fd125e2800f357f38ab1068c - Fix privileges on pg_statistic_ext.tableoid. The GRANT in system_views allowed SELECT privileges on various columns in the pg_statistic_ext catalog, but tableoid was not included in the list. That made pg_dump fail because it's accessing this column when building the list of extended statistics to dump. Discussion: https://postgr.es/m/8833.1560647898%40sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/fc8cf3df478e054b892f6698b6d336e72f3a7328 == Pending Patches == Ian Barwick sent in a patch to clarify "pg_signal_backend" default role. Jie Zhang sent in a patch to fix potential memoryleak in guc.c. Noah Misch sent in a patch to fix testing on msys when builddir is under /c mount point. Jie Zhang sent in a patch to fix a memory leak in ecpglib. Kyotaro HORIGUCHI sent in a patch to change the format of prep status in pg_upgrade. Robert Haas sent in another revision of a patch to tableam to provide helper functions for relation sizing. Konstantin Knizhnik sent in a patch to do adaptive query optimization. Etsuro Fujita sent in another revision of a patch to fix estimate_path_cost_size. Andres Freund sent in a patch to reimplement background workers. Peter Eisentraut sent in a patch to add a gen_random_uuid() function. Robert Haas sent in another revision of a patch to split tuptoaster.c into three separate files, create an API for inserting and deleting rows in TOAST tables, and allow TOAST tables to be implemented using table AMs other than heap. Tomáš Vondra sent in another revision of a patch to implemenent BRIN multi-range indexes. Tomáš Vondra sent in another revision of a patch to add opclass parameters. Daniel Gustafsson sent in another revision of a patch to use heap_multi_insert() for pg_attribute/depend insertions. Peter Eisentraut and Sergei Kornilov traded patches to remove explicit error handling for obsolete date/time values and avoid calling data type input functions in GUC check hooks. Kirk Jamison sent in a patch to speed up truncates of relation forks. David Cramer sent in two revisions of a patch to add binary support for the pgoutput plugin. Kyotaro HORIGUCHI sent in a patch to refactor pg_upgrade_controldata errors. Peter Eisentraut sent in a patch to simplify the catalog files. Peter Eisentraut sent in a patch to remove support for non-ELF FreeBSD systems. Etsuro Fujita sent in another revision of a patch to fix BEFORE UPDATE TRIGGER behavior for the PostgreSQL foreign data wrapper. Álvaro Herrera sent in a patch to remove some instances of "the the". Richard Guo sent in two revisions of a patch to implement parallel grouping sets. Timur Birsh sent in two revisions of a patch to vacuumlo to print the number of large objects to be removed. Amit Khandekar sent in two more revisions of a patch to implement minimal logical decoding on standbys. Fabien COELHO sent in two more revisions of a patch to fix psql \conninfo & \connect when using hostaddr. Iwata Aya sent in another revision of a patch to implement a trace output for libpq. Michaël Paquier sent in another revision of a patch to stabilize the synchronous replication tests. Peter Eisentraut sent in another revision of a patch to pg_upgrade to improve invalid option handling. Jesper Pedersen sent in another revision of a patch to implement index skip scan. Thomas Munro sent in another revision of a patch to remove obsolete comments about sempahores from proc.c. Peter Eisentraut sent in another revision of a patch to update the list of combining characters. Thomas Munro sent in another revision of a patch to clean up orphaned files using undo logs. Zhang Wenjie sent in a patch to check the number of potential synchronous standbys. Heikki Linnakangas sent in a patch to to make it possible for table AMs to put information in relcache. Kyotaro HORIGUCHI and Alexander Korotkov traded patches to fix some SQL/JSON path issues. Zheng Li sent in another revision of a patch to change NOT IN to anti-JOIN where possible. Ian Barwick sent in a patch to prevent ALTER SYSTEM from making bad assumptions. David Rowley sent in a patch to fix an issue where custom table AMs needed to include heapam.h because of BulkInsertState. David Rowley sent in another revision of a patch to shrink bloated locallocktables. David Rowley sent in another revision of a patch to fix a performance issue in foreign-key-aware join estimation.
В списке pgsql-announce по дате отправления:
Следующее
От: Stephen FrostДата:
Сообщение: PostgreSQL 11.4, 10.9, 9.6.14, 9.5.18, 9.4.23, and 12 Beta 2Released!