== PostgreSQL Weekly News - July 15 2018 ==
| От | David Fetter |
|---|---|
| Тема | == PostgreSQL Weekly News - July 15 2018 == |
| Дата | |
| Msg-id | 20180715221915.GA5480@fetter.org обсуждение исходный текст |
| Список | pgsql-announce |
== PostgreSQL Weekly News - July 15 2018 == == PostgreSQL Product News == JDBC 42.2.4 released https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.4 pg_chameleon 2.0.8, a tool for replicating from MySQL to PostgreSQL, released. https://pypi.python.org/pypi/pg_chameleon == PostgreSQL Jobs for July == http://archives.postgresql.org/pgsql-jobs/2018-07/ == PostgreSQL Local == PGConf.Brazil 2018 will take place in São Paulo, Brazil on August 3-4 2018. http://pgconf.com.br PostgreOpen Silicon Valley 2018 will be held in San Francisco on September 5-7, 2018. https://2018.postgresopen.org/ The Portland PostgreSQL Users Group will be holding a PGDay on September 10, 2018 in Portland, OR. The CfP is open at https://goo.gl/forms/E0CiUQGSZGMYwh922 https://pdx.postgresql.us/pdxpgday2018 PostgresConf South Africa 2018 will take place in Johannesburg on October 9, 2018 https://postgresconf.org/conferences/SouthAfrica2018 PostgreSQL Conference Europe 2018 will be held on October 23-26, 2018 at the Lisbon Marriott Hotel in Lisbon, Portugal. The CfP is open through August 6, 2018 midnight CET at https://2018.pgconf.eu/callforpapers https://2017.pgconf.eu/ 2Q PGConf will be on December 4-5, 2018 in Chicago, IL. The CfP is open through August 27, 2018 at midnight Pacific Time at http://www.2qpgconf.com/#cfp http://www.2qpgconf.com/ PGConf.ASIA 2018 will take place on December 10-12, 2018 in Akihabara, Tokyo, Japan. The CfP is open until midnight, July 31, 2018, Japan time at pgconf-asia-2018-submission(at)pgconf(dot)asia http://www.pgconf.asia/EN/2018/ == 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 == Jeff Davis pushed - Fix WITH CHECK OPTION on views referencing postgres_fdw tables. If a view references a foreign table, and the foreign table has a BEFORE INSERT trigger, then it's possible for a tuple inserted or updated through the view to be changed such that it violates the view's WITH CHECK OPTION constraint. Before this commit, postgres_fdw handled this case inconsistently. A RETURNING clause on the INSERT or UPDATE statement targeting the view would cause the finally-inserted tuple to be read back, and the WITH CHECK OPTION violation would throw an error. But without a RETURNING clause, postgres_fdw would not read the final tuple back, and WITH CHECK OPTION would not throw an error for the violation (or may throw an error when there is no real violation). AFTER ROW triggers on the foreign table had a similar effect as a RETURNING clause on the INSERT or UPDATE statement. To fix, this commit retrieves the attributes needed to enforce the WITH CHECK OPTION constraint along with the attributes needed for the RETURNING clause (if any) from the remote side. Thus, the WITH CHECK OPTION constraint is always evaluated against the final tuple after any triggers on the remote side. This fix may be considered inconsistent with CHECK constraints declared on foreign tables, which are not enforced locally at all (because the constraint is on a remote object). The discussion concluded that this difference is reasonable, because the WITH CHECK OPTION is a constraint on the local view (not any remote object); therefore it only makes sense to enforce its WITH CHECK OPTION constraint locally. Author: Etsuro Fujita Reviewed-by: Arthur Zakirov, Stephen Frost Discussion: https://www.postgresql.org/message-id/7eb58fab-fd3b-781b-ac33-f7cfec96021f%40lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/a45adc747e271556eb9443973264bf3353c86524 Michaël Paquier pushed: - Rework order of end-of-recovery actions to delay timeline history write. A critical failure in some of the end-of-recovery actions before the end-of-recovery record is written can cause PostgreSQL to react inconsistently with the rest of the cluster in the event of a crash before the final record is written. Two such failures are for example an error while processing a two-phase state files or when operating on recovery.conf. With this commit, the failures are still considered FATAL, but the write of the timeline history file is delayed as much as possible so as the window between the moment the file is written and the end-of-recovery record is generated gets minimized. This way, in the event of a crash or a failure, the new timeline decided at promotion will not seem taken by other nodes in the cluster. It is not really possible to reduce to zero this window, hence one could still see failures if a crash happens between the history file write and the end-of-recovery record, so any future code should be careful when adding new end-of-recovery actions. The original report from Magnus Hagander mentioned a renamed recovery.conf as original end-of-recovery failure which caused a timeline to be seen as taken but the subsequent processing on the now-missing recovery.conf cause the startup process to issue stop on FATAL, which at follow-up startup made the system inconsistent because of on-disk changes which already happened. Processing of two-phase state files still needs some work as corrupted entries are simply ignored now. This is left as a future item and this commit fixes the original complain. Reported-by: Magnus Hagander Author: Heikki Linnakangas Reviewed-by: Alexander Korotkov, Michael Paquier, David Steele Discussion: https://postgr.es/m/CABUevEz09XY2EevA2dLjPCY-C5UO4Hq=XxmXLmF6ipNFecbShQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/cbc55da556bbcb649e059804009c38100ee98884 - Fix table format in documentation for I/O wait events. This is an oversight from c55de5e. Author: Julien Rouhaud https://git.postgresql.org/pg/commitdiff/cccf81d259747f5198b55f51820b382ff5229a48 - Simplify logic to sync target directory at the end of pg_rewind. The previous sync logic relied on looking for and then launching externally initdb -S, which is a simple wrapper on top of fsync_pgdata. There is nothing preventing pg_rewind to directly call this routine, so remove the dependency to initdb and just call it directly. Author: Michael Paquier Reviewed-by: Heikki Linnakangas Discussion: https://postgr.es/m/20180325122607.GB3707@paquier.xyz https://git.postgresql.org/pg/commitdiff/9a4059d4ff1100e10d2aaadb136a04a407351249 - Add pg_rewind --no-sync. This is an option consistent with what pg_dump and pg_basebackup provide which is useful for leveraging the I/O effort when testing things, not to be used in a production environment. Author: Michael Paquier Reviewed-by: Heikki Linnakangas Discussion: https://postgr.es/m/20180325122607.GB3707@paquier.xyz https://git.postgresql.org/pg/commitdiff/8a00b96aa994b5c2c1a03c70e7a4719c919e8798 - Block replication slot advance for these not yet reserving WAL. Such replication slots are physical slots freshly created without WAL being reserved, which is the default behavior, which have not been used yet as WAL consumption resources to retain WAL. This prevents advancing a slot to a position older than any WAL available, which could falsify calculations for WAL segment recycling. This also cleans up a bit the code, as ReplicationSlotRelease() would be called on ERROR, and improves error messages. Reported-by: Kyotaro Horiguchi Author: Michael Paquier Reviewed-by: Andres Freund, Álvaro Herrera, Kyotaro Horiguchi Discussion: https://postgr.es/m/20180626071305.GH31353@paquier.xyz https://git.postgresql.org/pg/commitdiff/56a714721343e6f52c0b1b911c0f160592348e84 - Make logical WAL sender report streaming state appropriately. WAL senders sending logically-decoded data fail to properly report in "streaming" state when starting up, hence as long as one extra record is not replayed, such WAL senders would remain in a "catchup" state, which is inconsistent with the physical cousin. This can be easily reproduced by for example using pg_recvlogical and restarting the upstream server. The TAP tests have been slightly modified to detect the failure and strengthened so as future tests also make sure that a node is in streaming state when waiting for its catchup. Backpatch down to 9.4 where this code has been introduced. Reported-by: Sawada Masahiko Author: Simon Riggs, Sawada Masahiko Reviewed-by: Petr Jelinek, Michael Paquier, Vaishnavi Prabakaran Discussion: https://postgr.es/m/CAD21AoB2ZbCCqOx=bgKMcLrAvs1V0ZMqzs7wBTuDySezTGtMZA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/9a7b7adc130a197e5c993a99e6aaa981f9341a35 - Add assertion in expand_vacuum_rel() for non-autovacuum path. The code path where the assertion is added helps to check that autovacuum always includes a relation OID when doing a vacuum on it. Extracted from a larger patch set to add support for SKIP LOCKED with manual VACUUM commands. Author: Nathan Bossart Discussion: https://postgr.es/m/9EF7EBE4-720D-4CF1-9D0E-4403D7E92990@amazon.com https://git.postgresql.org/pg/commitdiff/6551f3daa2567ea9b2ded9b467aa9d876cc4e77f - Rename VACOPT_NOWAIT to VACOPT_SKIP_LOCKED. When it comes to SELECT ... FOR or LOCK, NOWAIT means to not wait for something to happen, and issue an error. SKIP LOCKED means to not wait for something to happen but to move on without issuing an error. The internal option of autovacuum and autoanalyze mentioned above, used only when wraparound is not involved was named NOWAIT, but behaves like SKIP LOCKED which is confusing. Author: Nathan Bossart Discussion: https://postgr.es/m/20180307050345.GA3095@paquier.xyz https://git.postgresql.org/pg/commitdiff/edc6b41bd4a80ea6aebacbd86ebe7c3a01939789 - Clean up temporary WAL segments after an instance crash. Temporary WAL segments are created in pg_wal and named as xlogtemp.pid before being renamed to the real deal when creating a new segment. If an instance crashes after the temporary segment is created and before the rename is done, then the server would finish with unremovable data. After an instance crash, scan pg_wal and remove any such segments. With repetitive unlucky crashes this would contribute to disk bloat and presents risks of ENOSPC especially with max_wal_size close to the maximum allowed. Author: Michael Paquier Reviewed-by: Yugo Nagata, Heikki Linnakangas Discussion: https://postgr.es/m/20180514054955.GF1528@paquier.xyz https://git.postgresql.org/pg/commitdiff/5fc1008e8a8b9e96ac75b4db5dd9ad2b99a9c8b2 - Fix argument of pg_create_logical_replication_slot for slot name. All attributes and arguments using a slot name map to the data type "name", but this function has been using "text". This is cosmetic, as even if text is used then the slot name would be truncated to 64 characters anyway and stored as such. The documentation already said so and the function already assumed that the argument was of this type when fetching its value. Bump catalog version. Author: Sawada Masahiko Discussion: https://postgr.es/m/CAD21AoADYz_-eAqH5AVFaCaojcRgwpo9PW=u8kgTMys63oB8Cw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ce89ad0fa07d98e20380867811a5269ee36d45c7 Peter Eisentraut pushed: - Add UtilityReturnsTuples() support for CALL. This ensures that prepared statements for CALL can return tuples. https://git.postgresql.org/pg/commitdiff/ec67b89816012ad753ebbd3489c7e7d0fe80d4ca - Fix typos. https://git.postgresql.org/pg/commitdiff/1486f7f981c0052988891677d4e734b14317816c - Remove dynamic_shared_memory_type=none. PostgreSQL nowadays offers some kind of dynamic shared memory feature on all supported platforms. Having the choice of "none" prevents us from relying on DSM in core features. So this patch removes the choice of "none". Author: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp> https://git.postgresql.org/pg/commitdiff/bcbd940806a2011d6f99ae72ea5897e8a94c6093 - Add regression test for system catalog toast tables. For the moment, this just records which system catalogs have toast tables right now. Future patches will possibly change that set. from Tom Lane via Joe Conway Discussion: https://www.postgresql.org/message-id/flat/84ddff04-f122-784b-b6c5-3536804495f8@joeconway.com/ https://git.postgresql.org/pg/commitdiff/ecd6b4342ad9f867e62979e85efa8fdb5fc904e5 - Improve two error messages. https://git.postgresql.org/pg/commitdiff/8e599897ca20fe31cb58fad0b401d7e317235024 - Reset shmem_exit_inprogress after shmem_exit(). In ad9a274778d2d88c46b90309212b92ee7fdf9afe, shmem_exit_inprogress was introduced. But we need to reset it after shmem_exit(), because unlike the similar proc_exit(), shmem_exit() can also be called for cleanup when the process will not exit. Reported-by: Andrew Gierth <andrew@tao11.riddles.org.uk> https://git.postgresql.org/pg/commitdiff/5e6e2c8773dd8e3037d9195452dc5e423367e1b0 - Remove obsolete documentation build tools for Windows. The scripts and instructions have been nonfunctional at least since PostgreSQL 10 (commit 510074f9f0131a04322d6a3d2a51c87e6db243f9) and nobody has stepped up to fix them. So right now just remove them until someone wants to resurrect them. Discussion: https://www.postgresql.org/message-id/flat/B74C0219-6BA9-46E1-A524-5B9E8CD3BDB3%40yesql.se https://git.postgresql.org/pg/commitdiff/1f4ec8945967a75f711d721860752985624a0957 - Prohibit transaction commands in security definer procedures. Starting and aborting transactions in security definer procedures doesn't work. StartTransaction() insists that the security context stack is empty, so this would currently cause a crash, and AbortTransaction() resets it. This could be made to work by reorganizing the code, but right now we just prohibit it. Reported-by: amul sul <sulamul@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b96Gupt_LFL7uNyy3c50-wbhA68NUjiK5%3DrF6_w%3Dpq_T%3DQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/3884072329bd1ad7d41bf7582c5d60e969365634 - Update documentation editor setup instructions. Now that the documentation sources are in XML rather than SGML, some of the documentation about the editor, or more specifically Emacs, setup needs updating. The updated instructions recommend using nxml-mode, which works mostly out of the box, with some small tweaks in emacs.samples and .dir-locals.el. Also remove some obsolete stuff in .dir-locals.el. I did, however, leave the sgml-mode settings in there so that someone using Emacs without emacs.samples gets those settings when editing a *.sgml file. https://git.postgresql.org/pg/commitdiff/333224c99ed107a4e73dc7768879c2a37c6f99ab Bruce Momjian pushed: - rel notes: mention enabling of parallelism in PG 10. Reported-by: Justin Pryzby Discussion: https://postgr.es/m/20180525010025.GT30060@telsasoft.com Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/6abad00585b7d3b9bc36e10d5937c27fb6878774 - docs: Remove "New" description of the libpqxx interface. Backpatch-through: 9.3 https://git.postgresql.org/pg/commitdiff/394811501cbef1259a544f8957160ca7153013ec Tom Lane pushed: - Fix yet more problems with incorrectly-constructed zero-length arrays. Commit 716ea626a attempted to fix the problem of building 1-D zero-size arrays once and for all. But it turns out that contrib/intarray has some code that doesn't use construct_array() but just builds arrays by hand, so it didn't get the memo. This appears to affect all of subarray(), intset_subtract(), inner_int_union(), inner_int_inter(), and intarray_concat_arrays(). Back-patch into v11. In the past we've not back-patched this type of change, but since v11 is still in beta it seems all right to include this fix in it. Besides it's more consistent to make the fix in v11 where 716ea626a appeared. Report and patch by Alexey Kryuchkov, some cosmetic adjustments by me Report: https://postgr.es/m/153053285112.13258.434620894305716755@wrigleys.postgresql.org Discussion: https://postgr.es/m/CAN85JcYphDLYt4CpMDLZjjNVqGDrFJ5eS3YF=wLAhFoDQuBsyg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/01783ac36de1aac9bf8d19f6c2814778188d4523 - Avoid emitting a bogus WAL record when recycling an all-zero btree page. Commit fafa374f2 caused _bt_getbuf() to possibly emit a WAL record for a page that it was about to recycle. However, it failed to distinguish all-zero pages from dead pages, which is important because only the latter have valid btpo.xact values, or indeed any special space at all. Recycling an all-zero page with XLogStandbyInfoActive() enabled therefore led to an Assert failure, or to emission of a WAL record containing a bogus cutoff XID, which might lead to unnecessary query cancellations on hot standby servers. Per reports from Antonin Houska and 自己. Amit Kapila was first to propose this fix, and Robert Haas, myself, and Kyotaro Horiguchi reviewed it at various times. This is an old bug, so back-patch to all supported branches. Discussion: https://postgr.es/m/2628.1474272158@localhost Discussion: https://postgr.es/m/48875502.f4a0.1635f0c27b0.Coremail.zoulx1982@163.com https://git.postgresql.org/pg/commitdiff/0905fe8911ea06df17a3ba3f086e98ca5c7b560c - Fix bugs with degenerate window ORDER BY clauses in GROUPS/RANGE mode. nodeWindowAgg.c failed to cope with the possibility that no ordering columns are defined in the window frame for GROUPS mode or RANGE OFFSET mode, leading to assertion failures or odd errors, as reported by Masahiko Sawada and Lukas Eder. In RANGE OFFSET mode, an ordering column is really required, so add an Assert about that. In GROUPS mode, the code would work, except that the node initialization code wasn't in sync with the execution code about when to set up tuplestore read pointers and spare slots. Fix the latter for consistency's sake (even though I think the changes described below make the out-of-sync cases unreachable for now). Per SQL spec, a single ordering column is required for RANGE OFFSET mode, and at least one ordering column is required for GROUPS mode. The parser enforced the former but not the latter; add a check for that. We were able to reach the no-ordering-column cases even with fully spec compliant queries, though, because the planner would drop partitioning and ordering columns from the generated plan if they were redundant with earlier columns according to the redundant-pathkey logic, for instance "PARTITION BY x ORDER BY y" in the presence of a "WHERE x=y" qual. While in principle that's an optimization that could save some pointless comparisons at runtime, it seems unlikely to be meaningful in the real world. I think this behavior was not so much an intentional optimization as a side-effect of an ancient decision to construct the plan node's ordering-column info by reverse-engineering the PathKeys of the input path. If we give up redundant-column removal then it takes very little code to generate the plan node info directly from the WindowClause, ensuring that we have the expected number of ordering columns in all cases. (If anyone does complain about this, the planner could perhaps be taught to remove redundant columns only when it's safe to do so, ie *not* in RANGE OFFSET mode. But I doubt anyone ever will.) With these changes, the WindowAggPath.winpathkeys field is not used for anything anymore, so remove it. The test cases added here are not actually very interesting given the removal of the redundant-column-removal logic, but they would represent important corner cases if anyone ever tries to put that back. Tom Lane and Masahiko Sawada. Back-patch to v11 where RANGE OFFSET and GROUPS modes were added. Discussion: https://postgr.es/m/CAD21AoDrWqycq-w_+Bx1cjc+YUhZ11XTj9rfxNiNDojjBx8Fjw@mail.gmail.com Discussion: https://postgr.es/m/153086788677.17476.8002640580496698831@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/ff4f8891648c804c7c126fdf438257b1e9dc7ca4 - Fix create_scan_plan's handling of sortgrouprefs for physical tlists. We should only run apply_pathtarget_labeling_to_tlist if CP_LABEL_TLIST was specified, because only in that case has use_physical_tlist checked that the labeling will succeed; otherwise we may get an "ORDER/GROUP BY expression not found in targetlist" error. (This subsumes the previous test about gating_clauses, because we reset "flags" to zero earlier if there are gating clauses to apply.) The only known case in which a failure can occur is with a ProjectSet path directly atop a table scan path, although it seems likely that there are other cases or will be such in future. This means that the failure is currently only visible in the v10 branch: 9.6 didn't have ProjectSet, while in v11 and HEAD, apply_scanjoin_target_to_paths for some weird reason is using create_projection_path not apply_projection_to_path, masking the problem because there's a ProjectionPath in between. Nonetheless this code is clearly wrong on its own terms, so back-patch to 9.6 where this logic was introduced. Per report from Regina Obe. Discussion: https://postgr.es/m/001501d40f88$75186950$5f493bf0$@pcorp.us https://git.postgresql.org/pg/commitdiff/57cd2b6e6dc571cf65983d2aa86065d6d006f152 - Mark built-in btree comparison functions as leakproof where it's safe. Generally, if the comparison operators for a datatype or pair of datatypes are leakproof, the corresponding btree comparison support function can be considered so as well. But we had not originally worried about marking support functions as leakproof, reasoning that they'd not likely be used in queries so the marking wouldn't matter. It turns out there's at least one place where it does matter: calc_arraycontsel() finds the target datatype's default btree comparison function and tries to use that to estimate selectivity, but it will be blocked in some cases if the function isn't leakproof. This leads to unnecessarily poor selectivity estimates and bad plans, as seen in bug #15251. Hence, run around and apply proleakproof markings where the corresponding btree comparison operators are leakproof. (I did eyeball each function to verify that it wasn't doing anything surprising, too.) This isn't a full solution to bug #15251, and it's not back-patchable because of the need for a catversion bump. A more useful response probably is to consider whether we can check permissions on the parent table instead of the child. However, this change will help in some cases where that won't, and it's easy enough to do in HEAD, so let's do so. Discussion: https://postgr.es/m/3876.1531261875@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/39a96512b3ed72de7b24b2667d9575d7e9fcb326 - Doc: clarify release note text about v11's new window function features. Jonathan S. Katz Discussion: https://postgr.es/m/30468663-E67D-4753-8269-7E6A4001A281@excoventures.com https://git.postgresql.org/pg/commitdiff/11a3aeeb5e174a3681f674e7e5ec9bbbc6430394 - Doc: update documentation for requirement of ORDER BY in GROUPS mode. Commit ff4f88916 adjusted the code to enforce the SQL spec's requirement that a window using GROUPS mode must have an ORDER BY clause. But I missed that the documentation explicitly said you didn't have to have one. Also minor wordsmithing in the window-function section of select.sgml. Per Masahiko Sawada, though I didn't use his patch. https://git.postgresql.org/pg/commitdiff/e0cd0ea4f93c3644f09f7723206d8d003a97ee4a - Doc: minor improvement in pl/pgsql FETCH/MOVE documentation. Explain that you can use any integer expression for the "count" in pl/pgsql's versions of FETCH/MOVE, unlike the SQL versions which only allow a constant. Remove the duplicate version of this para under MOVE. I don't see a good reason to maintain two identical paras when we just said that MOVE works exactly like FETCH. Per Pavel Stehule, though I didn't use his text. Discussion: https://postgr.es/m/CAFj8pRAcvSXcNdUGx43bOK1e3NNPbQny7neoTLN42af+8MYWEA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/632b4ae92dc6fb92149e94290760b50744e0bd35 - Fix inadequate buffer locking in FSM and VM page re-initialization. When reading an existing FSM or VM page that was found to be corrupt by the buffer manager, the code applied PageInit() to reinitialize the page, but did so without any locking. There is thus a hazard that two backends might concurrently do PageInit, which in itself would still be OK, but the slower one might then zero over subsequent data changes applied by the faster one. Even that is unlikely to be fatal; but it's not desirable, so add locking to prevent it. This does not add any locking overhead in the normal code path where the page is OK. It's not immediately obvious that that's safe, but I believe it is, for reasons explained in the added comments. Problem noted by R P Asim. It's been like this for a long time, so back-patch to all supported branches. Discussion: https://postgr.es/m/CANXE4Te4G0TGq6cr0-TvwP0H4BNiK_-hB5gHe8mF+nz0mcYfMQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/130beba36d6dd46b8c527646f9f2433347cbfb11 - Fix crash in json{b}_populate_recordset() and json{b}_to_recordset(). As of commit 37a795a60, populate_recordset_worker() tried to pass back (as rsi.setDesc) a tupdesc that it also had cached in its fn_extra. But the core executor would free the passed-back tupdesc, risking a crash if the function were called again in the same query. The safest and least invasive way to fix that is to make an extra tupdesc copy to pass back. While at it, I failed to resist the temptation to get rid of unnecessary get_fn_expr_argtype() calls here and in populate_record_worker(). Per report from Dmitry Dolgov; thanks to Michael Paquier and Andrew Gierth for investigation and discussion. Discussion: https://postgr.es/m/CA+q6zcWzN9ztCfR47ZwgTr1KLnuO6BAY6FurxXhovP4hxr+yOQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4984784f836a061985b356c52253b5d83a0cbe65 - Fix crash in contrib/ltree's lca() function for empty input array. lca_inner() wasn't prepared for the possibility of getting no inputs. Fix that, and make some cosmetic improvements to the code while at it. Also, I thought the documentation of this function as returning the "longest common prefix" of the paths was entirely misleading; it really returns a path one shorter than the longest common prefix, for the typical definition of "prefix". Don't use that term in the docs, and adjust the examples to clarify what really happens. This has been broken since its beginning, so back-patch to all supported branches. Per report from Hailong Li. Thanks to Pierre Ducroquet for diagnosing and for the initial patch, though I whacked it around some and added test cases. Discussion: https://postgr.es/m/5b0d8e4f-f2a3-1305-d612-e00e35a7be66@qunar.com https://git.postgresql.org/pg/commitdiff/28a1ae5342fe39b7c4057d3f872cb6057f5f33bf - Fix hashjoin costing mistake introduced with inner_unique optimization. In final_cost_hashjoin(), commit 9c7f5229a allowed inner_unique cases to follow a code path previously used only for SEMI/ANTI joins; but it neglected to fix an if-test within that path that assumed SEMI and ANTI were the only possible cases. This resulted in a wrong value for hashjointuples, and an ensuing bad cost estimate, for inner_unique normal joins. Fortunately, for inner_unique normal joins we can assume the number of joined tuples is the same as for a SEMI join; so there's no need for more code, we just have to invert the test to check for ANTI not SEMI. It turns out that in two contrib tests in which commit 9c7f5229a changed the plan expected for a query, the change was actually wrong and induced by this estimation error, not by any real improvement. Hence this patch also reverts those changes. Per report from RK Korlapati. Backpatch to v10 where the error was introduced. David Rowley Discussion: https://postgr.es/m/CA+SNy03bhq0fodsfOkeWDCreNjJVjsdHwUsb7AG=jpe0PtZc_g@mail.gmail.com https://git.postgresql.org/pg/commitdiff/1007b0a126c608b530fd2914825f1e6d133cb649 Álvaro Herrera pushed: - Flip argument order in XLogSegNoOffsetToRecPtr. Commit fc49e24fa69a added an input argument after the existing output argument. Flip those. Author: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Andres Freund <andres@anarazel.de> Discussion: https://postgr.es/m/20180708182345.imdgovmkffgtihhk@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/a22445ff0be2e4a008b2dd1aaa963f8f7c70cab9 - Better handle pseudotypes as partition keys. We fail to handle polymorphic types properly when they are used as partition keys: we were unnecessarily adding a RelabelType node on top, which confuses code examining the nodes. In particular, this makes predtest.c-based partition pruning not to work, and ruleutils.c to emit expressions that are uglier than needed. Fix it by not adding RelabelType when not needed. In master/11 the new pruning code is separate so it doesn't suffer from this problem, since we already fixed it (in essentially the same way) in e5dcbb88a15d, which also added a few tests; back-patch those tests to pg10 also. But since UPDATE/DELETE still uses predtest.c in pg11, this change improves partitioning for those cases too. Add tests for this. The ruleutils.c behavior change is relevant in pg11/master too. Co-authored-by: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/54745d13-7ed4-54ac-97d8-ea1eec95ae25@lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/b6e3a3a492dbf2043e4b149221007716ba9e364e - Rethink how to get float.h in old Windows API for isnan/isinf. We include <float.h> in every place that needs isnan(), because MSVC used to require it. However, since MSVC 2013 that's no longer necessary (cf. commit cec8394b5ccd), so we can retire the inclusion to a version-specific stanza in win32_port.h, where it doesn't need to pollute random .c files. The header is of course still needed in a few places for other reasons. I (Álvaro) removed float.h from a few more files than in Emre's original patch. This doesn't break the build in my system, but we'll see what the buildfarm has to say about it all. Author: Emre Hasegeli Discussion: https://postgr.es/m/CAE2gYzyc0+5uG+Cd9-BSL7NKC8LSHLNg1Aq2=8ubjnUwut4_iw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f2c587067a8eb9cf1c8f009262381a6576ba3dd0 - Fix FK checks of TRUNCATE involving partitioned tables. When truncating a table that is referenced by foreign keys in partitioned tables, the check to ensure the referencing table are also truncated spuriously failed. This is because it was relying on relhastriggers as a proxy for the table having FKs, and that's wrong for partitioned tables. Fix it to consider such tables separately. There may be a better way ... but this code is pretty inefficient already. Author: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Michael Paquiër <michael@paquier.xyz> Discussion: https://postgr.es/m/20180711000624.zmeizicibxeehhsg@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/cd073d8f7043d047c024f0f213c0e9c31b9c0505 - Dump foreign keys on partitioned tables. The patch that ended up as commit 3de241dba86f ("Foreign keys on partitioned tables") lacked pg_dump tests, so the pg_dump code that was there to support it inadvertently stopped working when in later development I modified the backend code not to emit pg_trigger rows for the partitioned table itself. Bug analysis and code fix is by Michaël. I (Álvaro) added the test. Reported-by: amul sul <sulamul@gmail.com> Co-authored-by: Michaël Paquier <michael@paquier.xyz> Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Discussion: https://postgr.es/m/CAAJ_b94n=UsNVhgs97vCaWEZAMe-tGDRVuZ73oePQH=eaJKGSA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/93ad00c968ae93e93f4ff238068a92625f41b6ff Heikki Linnakangas pushed: - Add test case for EEOP_INNER_SYSVAR/EEOP_OUTER_SYSVAR executor opcodes. The EEOP_INNER_SYSVAR and EEOP_OUTER_SYSVAR executor opcodes are not exercised by normal queries, because setrefs.c will resolve the references to system columns in the scan nodes already. Join nodes refer to them by their position in the child node's target list, like user columns. The only place where those opcodes are used, is in evaluating a trigger's WHEN condition that references system columns. Trigger evaluation abuses the INNER/OUTER Vars to refer to the OLD and NEW tuples. The code to handle the opcodes is pretty straightforward, but it seems like a good idea to have some test coverage for them, anyway, so that they don't get removed or broken by accident. Author: Ashutosh Bapat, with some changes by me. Discussion: https://www.postgresql.org/message-id/CAFjFpRerUFX=T0nSnCoroXAJMoo-xah9J+pi7+xDUx86PtQmew@mail.gmail.com https://git.postgresql.org/pg/commitdiff/17b715c634d451da3edb221c55c614a7d993c289 - Improve performance of tuple conversion map generation. Previously convert_tuples_by_name_map naively performed a search of each outdesc column starting at the first column in indesc and searched each indesc column until a match was found. When partitioned tables had many columns this could result in slow generation of the tuple conversion maps. For INSERT and UPDATE statements that touched few rows, this could mean a very large overhead indeed. We can do a bit better with this loop. It's quite likely that the columns in partitioned tables and their partitions are in the same order, so it makes sense to start searching for each column outer column at the inner column position 1 after where the previous match was found (per idea from Alexander Kuzmenkov). This makes the best case search O(N) instead of O(N^2). The worst case is still O(N^2), but it seems unlikely that would happen. Likewise, in the planner, make_inh_translation_list's search for the matching column could often end up falling back on an O(N^2) type search. This commit also improves that by first checking the column that follows the previous match, instead of the column with the same attnum. If we fail to match here we fallback on the syscache's hashtable lookup. Author: David Rowley Reviewed-by: Alexander Kuzmenkov Discussion: https://www.postgresql.org/message-id/CAKJS1f9-wijVgMdRp6_qDMEQDJJ%2BA_n%3DxzZuTmLx5Fz6cwf%2B8A%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/42f70cd9c3dbfcdfbeea4e24d5921173d0eaab66 Thomas Munro pushed: - Use signals for postmaster death on FreeBSD. Use FreeBSD 11.2's new support for detecting parent process death to make PostmasterIsAlive() very cheap, as was done for Linux in an earlier commit. Author: Thomas Munro Discussion: https://postgr.es/m/7261eb39-0369-f2f4-1bb5-62f3b6083b5e@iki.fi https://git.postgresql.org/pg/commitdiff/f98b8476cd4a19dfc602ab95642ce08e53877d65 - Use signals for postmaster death on Linux. Linux provides a way to ask for a signal when your parent process dies. Use that to make PostmasterIsAlive() very cheap. Based on a suggestion from Andres Freund. Author: Thomas Munro, Heikki Linnakangas Reviewed-By: Michael Paquier Discussion: https://postgr.es/m/7261eb39-0369-f2f4-1bb5-62f3b6083b5e%40iki.fi Discussion: https://postgr.es/m/20180411002643.6buofht4ranhei7k%40alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/9f09529952ac41a10e5874cba745c1c24e67ac79 - Add pg_dump --on-conflict-do-nothing option. When dumping INSERT statements, optionally add ON CONFLICT DO NOTHING. Author: Surafel Temesgen Reviewed-by: Takeshi Ideriha, Nico Williams, Dilip Kumar Discussion: https://postgr.es/m/CALAY4q-PQ9cOEzs2%2BQHK5ObfF_4QbmBaYXbZx6BGGN66Q-n8FA%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/387a5cfb94f041ffe18d03ccc4063c626bb4b73e - Accept invalidation messages in InitializeSessionUserId(). If the authentication method modified the system catalogs through a separate database connection (say, to create a new role on the fly), make sure syscache sees the changes before we try to find the user. Author: Thomas Munro Reviewed-by: Tom Lane, Andres Freund Discussion: https://postgr.es/m/CAEepm%3D3_h0_cgmz5PMyab4xk_OFrg6G5VCN%3DnF4chFXM9iFOqA%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/e8d9caa436db2931613d7cb4a4a8b3933d5f6ded Alexander Korotkov pushed: - Fix wrong file path in header comment. Header comment of shm_mq.c was mistakenly specifying path to shm_mq.h. It was introduced in ec9037df. So, theoretically it could be backpatched to 9.4, but it doesn't seem to worth it. https://git.postgresql.org/pg/commitdiff/a01d0fa1d889cc2003e1941e8b98707c4d701ba9 - Fix more wrong paths in header comments. It appears that there are more files, whose header comment paths are wrong. So, fix those paths. No backpatching per proposal of Tom Lane. Discussion: https://postgr.es/m/CAPpHfdsJyYbOj59MOQL%2B4XxdcomLSLfLqBtAvwR%2BpsCqj3ELdQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/edf59c40dd6f477ca867c8958d9665ab1bff0265 Amit Kapila pushed: - Allow using the updated tuple while moving it to a different partition. An update that causes the tuple to be moved to a different partition was missing out on re-constructing the to-be-updated tuple, based on the latest tuple in the update chain. Instead, it's simply deleting the latest tuple and inserting a new tuple in the new partition based on the old tuple. Commit 2f17844104 didn't consider this case, so some of the updates were getting lost. In passing, change the argument order for output parameter in ExecDelete and add some commentary about it. Reported-by: Pavan Deolasee Author: Amit Khandekar, with minor changes by me Reviewed-by: Dilip Kumar, Amit Kapila and Alvaro Herrera Backpatch-through: 11 Discussion: https://postgr.es/m/CAJ3gD9fRbEzDqdeDq1jxqZUb47kJn+tQ7=Bcgjc8quqKsDViKQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/40ca70ebcc9d0bc1c02937b27c44b2766617e790 == Pending Patches == David Rowley sent in a patch to skip any quals that are implied by any partition bound. Michaël Paquier sent in a patch to fail hard when facing corrupted two-phase state files at recovery. Aleksandr Parfenov sent in another revision of a patch to create a more flexible way of configuring full-text search. Aleksandr Parfenov sent in another revision of a patch to add a range phrase operator to tsquery. Haribabu Kommi sent in three more revisions of a patch to revoke pg_stat_statements_reset() permissions. Michaël Paquier sent in another revision of a patch to document fast_forward in logical replication. David Rowley sent in another revision of a patch to improve performance of tuple conversion map generation. Haribabu Kommi sent in another revision of a patch to add a pg_stat_statements_reset() to reset specific query/user/db statistics. Peter Eisentraut sent in a patch to add some int128 atomics support and use same to reduce WAL spinlock contention by replacing insertpos_lck with a 128-bit compare-and-swap. Lars Kanis sent in another revision of a patch to add a function PQresultMemsize() to libpq which retrieves the number of bytes allocated for a given result. Alexander Kuzmenkov sent in another revision of a patch to implement inequality merge join. Simon Muller sent in another revision of a patch to enable COPY's text format to emit a header row. Kyotaro HORIGUCHI sent in another revision of a patch to implement a shared-memory-based stats collector. Emre Hasegeli sent in two more revisions of a patch to improve geometric types by fixing the floating point handling they depend on. Imai Yoshikazu sent in two more revisions of a patch to lock B-tree leaves immediately in exclusive mode. Andrey V. Lepikhov sent in a patch to add a timestamp into a XLOG_BACKUP_END WAL-record. Kefan Yang sent in a patch to use IntroSort to implement pg_qsort and only check whether the array is preordered once on the whole array. Adrien Nayrat sent in another revision of a patch to add a new GUC, log_sample_rate, to log a fraction of queries. Thomas Munro sent in four revisions of a patch to track the next xid using 64 bits. Haribabu Kommi sent in another revision of a patch to libpq to allow target-session-attrs to accept prefer-read option. Heikki Linnakangas sent in a patch to add support for EXECUTE <stmt> USING <params> syntax. Kuntal Ghosh sent in a patch to pageinspect which drops the table after testing gin-related functions. Nico Williams sent in another revision of a patch to add an ALWAYS DEFERRED option for constraints. Michaël Paquier sent in another revision of a patch to optimize WAL truncation for some cases with TRUNCATE and COPY. Yugo Nagata sent in a patch to prefer index-only scans in cases where the cost comes out equal. Dilip Kumar, Amit Langote, and Álvaro Herrera traded patches to generate a prunestep for isnull conditions. Yugo Nagata sent in a patch to enable specifying an index name to ANALYZE. Masahiko Sawada sent in a patch to ensure that the upper limit arguments of pg_logical_slot_xxx_changes functions accept only valid values. Thomas Munro sent in a patch to use pread()/pwrite() instead of lseek() + read()/write(). Yugo Nagata sent in a patch to ensure that included columns do not intersect with key columns in INCLUDE for indexes. Pavel Stěhule sent in another revision of a patch to add a plancache_mode GUC which helps force custom (or generic) plans. Masahiko Sawada sent in another revision of a patch to implement copying functions for replication slots, both logical and physical. Teodor Sigaev sent in two more revisions of a patch to make some improvements to cost_sort(). Nikita Glukhov sent in another revision of a patch to Add missing type conversion functions for PLPython. Fabien COELHO sent in another revision of a patch to add progress reports to pgbench's TAP test. Amit Langote sent in another revision of a patch to fix a bug regarding partition column option inheritance where the coding pattern in MergeAttributes caused the NOT NULL constraint and default value of a column from not being properly inherited from the parent's definition. Andrey Borodin sent in four more revisions of a patch to implement GiST VACUUM. Pavel Stěhule sent in another revision of a patch to add plpgsql.extra_warnings and plpgsql.extra_errors. Fabien COELHO sent in a patch to detect pgbench overflows on int and double constants, and on integer operators. Yugo Nagata sent in a patch to allow a column to appear more than once in a RANGE partition. Ashutosh Bapat sent in another revision of a patch to add a TupleTableSlot abstraction. Oliver Ford sent in a patch to adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM FIRST/LAST to the non-aggregate window functions. Don Seiler sent in a patch to add application_name to libpq's Port struct to make it possible to display the application name in "connection authorized" log messages. Pierre Ducroquet sent in a patch to check for the hasnulls attribute before checking individual fields, introduce opt1 in LLVM/JIT, and force it with deforming, and skip alignment code blocks when they are not needed. Michaël Paquier sent in two more revisions of a patch to add an interface to read/write/fsync with transient files. Nikita Glukhov sent in another revision of a patch to implement kNN for SP-GiST. Peter Eisentraut sent in a patch to expand psql's \df to include PROCEDUREs. John Naylor sent in a patch to add TOAST tables to most system catalogs. Julian Markwort sent in another revision of a patch to pg_hba.conf to add a new auth option: clientcert=verify-full Tom Lane sent in a patch to fix a race condition detected in 001_stream_rep.pl. David Rowley sent in another revision of a patch to allow LEFT JOINs to be removed in more cases. Tom Lane sent in a patch to verify executor locks are already held in partition pruning.
В списке pgsql-announce по дате отправления: