You are here

PostreSQL.org

Subscribe to PostreSQL.org feed
PostgreSQL news
Updated: 11 hours 49 min ago

PostgreSQL Weekly News - November 29, 2020

2020, November 30 - 01:00
PostgreSQL Weekly News - November 29, 2020 PostgreSQL Product News

Pgpool-II 4.2.0, a connection pooler and statement replication system for PostgreSQL, released. https://www.pgpool.net/docs/42/en/html/release-4-2-0.html

pgBadger v11.4, a PostgreSQL log analyzer and graph tool written in Perl, released. https://github.com/darold/pgbadger/releases

Database Lab 2.0, a tool for fast cloning of large PostgreSQL databases to build non-production environments, released: https://postgres.ai/blog/dle-2.0-release/

pgagroal 1.0.0, a high-performance protocol-native connection pool for PostgreSQL, released. https://agroal.github.io/pgagroal/release/announcement/2020/11/24/pgagroal-1.0.0.html

PostgreSQL Jobs for November

http://archives.postgresql.org/pgsql-jobs/2020-11/

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

Tom Lane pushed:

  • Allow a multi-row INSERT to specify DEFAULTs for a generated column. One can say "INSERT INTO tab(generated_col) VALUES (DEFAULT)" and not draw an error. But the equivalent case for a multi-row VALUES list always threw an error, even if one properly said DEFAULT in each row. Fix that. While here, improve the test cases for nearby logic about OVERRIDING SYSTEM/USER values. Dean Rasheed Discussion: https://postgr.es/m/9q0sgcr416t.fsf@gmx.us https://git.postgresql.org/pg/commitdiff/17958972fe3bb03454a4b53756b29d65dc285efa

  • Improve wording of two error messages related to generated columns. Clarify that you can "insert" into a generated column as long as what you're inserting is a DEFAULT placeholder. Also, use ERRCODE_GENERATED_ALWAYS in place of ERRCODE_SYNTAX_ERROR; there doesn't seem to be any reason to use the less specific errcode. Discussion: https://postgr.es/m/9q0sgcr416t.fsf@gmx.us https://git.postgresql.org/pg/commitdiff/d36228a9fcdccd57a7dc332572eb9837c7c301e6

  • Rename the "point is strictly above/below point" comparison operators. Historically these were called >^ and <^, but that is inconsistent with the similar box, polygon, and circle operators, which are named |>> and <<| respectively. Worse, the >^ and <^ names are used for not strict above/below tests for the box type. Hence, invent new operators following the more common naming. The old operators remain available for now, and are still accepted by the relevant index opclasses too. But there's a deprecation notice, so maybe we can get rid of them someday. Emre Hasegeli, reviewed by Pavel Borisov Discussion: https://postgr.es/m/24348.1587444160@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/0cc99327888840f2bf572303b68438e4caf62de9

  • Remove unnecessary #include. Justin Pryzby Discussion: https://postgr.es/m/20201123205505.GJ24052@telsasoft.com https://git.postgresql.org/pg/commitdiff/3b9b01f75d6e2d7bf9e0bf8ec958ce420aa037c2

  • Centralize logic for skipping useless ereport/elog calls. While ereport() and elog() themselves are quite cheap when the error message level is too low to be printed, some places need to do substantial work before they can call those macros at all. To allow optimizing away such setup work when nothing is to be printed, make elog.c export a new function message_level_is_interesting(elevel) that reports whether ereport/elog will do anything. Make use of that in various places that had ad-hoc direct tests of log_min_messages etc. Also teach ProcSleep to use it to avoid some work. (There may well be other places that could usefully use this; I didn't search hard.) Within elog.c, refactor a little bit to avoid having duplicate copies of the policy-setting logic. When that code was written, we weren't relying on the availability of inline functions; so it had some duplications in the name of efficiency, which I got rid of. Alvaro Herrera and Tom Lane Discussion: https://postgr.es/m/129515.1606166429@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/789b938bf2b8e38d0894261eae6bc84bbbb4714e

  • Put "inline" marker on declarations of inline functions. I'm having a hard time telling whether the letter of the C standard requires this, but we do have a couple of buildfarm members that throw warnings when this is not done. Oversight in c532d15dd. https://git.postgresql.org/pg/commitdiff/ec05bafdbbf474bf0a1416772da31f9f1f27fa1e

  • Avoid spamming the client with multiple ParameterStatus messages. Up to now, we sent a ParameterStatus message to the client immediately upon any change in the active value of any GUC_REPORT variable. This was only barely okay when the feature was designed; now that we have things like function SET clauses, there are very plausible use-cases where a GUC_REPORT variable might change many times within a query --- and even end up back at its original value, perhaps. Fortunately most of our GUC_REPORT variables are unlikely to be changed often; but there are proposals in play to enlarge that set, or even make it user-configurable. Hence, let's fix things to not generate more than one ParameterStatus message per variable per query, and to not send any message at all unless the end-of-query value is different from what we last reported. Discussion: https://postgr.es/m/5708.1601145259@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/2432b1a04087edc2fd9536c7c9aa4ca03fd1b363

  • Doc: minor improvements for section 11.2 "Index Types". Break the per-index-type discussions into <sect2>'s so as to make them more visually separate and easier to find. Improve the markup, and make a couple of small wording adjustments. This also fixes one stray reference to the now-deprecated point operators <^ and >^. Dagfinn Ilmari Mannsåker, reviewed by David Johnston and Jürgen Purtz Discussion: https://postgr.es/m/877dukhvzg.fsf@wibble.ilmari.org https://git.postgresql.org/pg/commitdiff/85b4ba73423b480902206ca04330c1cbea371c3c

  • In psql's \d commands, don't truncate attribute default values. Historically, psql has truncated the text of a column's default expression at 128 characters. This is unlike any other behavior in describe.c, and it's become particularly confusing now that the limit is only applied to the expression proper and not to the "generated always as (...) stored" text that may get wrapped around it. Excavation in our git history suggests that the original motivation for this limit was not really to limit the display width (as I'd long supposed), but to make it safe to use a fixed-width output buffer to store the result. That implementation restriction is long gone of course, but the limit remained. Let's just get rid of it. While here, rearrange the logic about when to free the output string so that it's not so dependent on unstated assumptions about the possible values of attidentity and attgenerated. Per bug #16743 from David Turon. Back-patch to v12 where GENERATED came in. (Arguably we could take it back further, but I'm hesitant to change the behavior of long-stable branches for this.) Discussion: https://postgr.es/m/16743-7b1bacc4af76e7ad@postgresql.org https://git.postgresql.org/pg/commitdiff/314fb9baeacb3426a5d9a26132aae8d828cc0ad7

  • Fix a recently-introduced race condition in LISTEN/NOTIFY handling. Commit 566372b3d fixed some race conditions involving concurrent SimpleLruTruncate calls, but it introduced new ones in async.c. A newly-listening backend could attempt to read Notify SLRU pages that were in process of being truncated, possibly causing an error. Also, the QUEUE_TAIL pointer could become set to a value that's not equal to the queue position of any backend. While that's fairly harmless in v13 and up (thanks to commit 51004c717), in older branches it resulted in near-permanent disabling of the queue truncation logic, so that continued use of NOTIFY led to queue-fill warnings and eventual inability to send any more notifies. (A server restart is enough to make that go away, but it's still pretty unpleasant.) The core of the problem is confusion about whether QUEUE_TAIL represents the "logical" tail of the queue (i.e., the oldest still-interesting data) or the "physical" tail (the oldest data we've not yet truncated away). To fix, split that into two variables. QUEUE_TAIL regains its definition as the logical tail, and we introduce a new variable to track the oldest un-truncated page. Per report from Mikael Gustavsson. Like the previous patch, back-patch to all supported branches. Discussion: https://postgr.es/m/1b8561412e8a4f038d7a491c8b922788@smhi.se https://git.postgresql.org/pg/commitdiff/9c83b54a9ccdb111ce693ada2309475197c19d70

  • Clean up after tests in src/test/locale/. Oversight in 257836a75, which added these tests. https://git.postgresql.org/pg/commitdiff/b90a7fe15f78b1e2513cbcb27a33f4adc47bcd3d

  • Doc: clarify behavior of PQconnectdbParams(). The documentation omitted the critical tidbit that a keyword-array entry is simply ignored if its corresponding value-array entry is NULL or an empty string; it will not override any previously-obtained value for the parameter. (See conninfo_array_parse().) I'd supposed that would force the setting back to default, which is what led me into bug #16746; but it doesn't. While here, I couldn't resist the temptation to do some copy-editing, both in the description of PQconnectdbParams() and in the section about connection URI syntax. Discussion: https://postgr.es/m/931505.1606618746@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/d5e2bdf7dda712a60234f08e036ec9de28085a2d

Heikki Linnakangas pushed:

  • Split copy.c into four files. Copy.c has grown really large. Split it into more manageable parts: - copy.c now contains only a few functions that are common to COPY FROM and COPY TO. - copyto.c contains code for COPY TO. - copyfrom.c contains code for initializing COPY FROM, and inserting the tuples to the correct table. - copyfromparse.c contains code for reading from the client/file/program, and parsing the input text/CSV/binary format into tuples. All of these parts are fairly complicated, and fairly independent of each other. There is a patch being discussed to implement parallel COPY FROM, which will add a lot of new code to the COPY FROM path, and another patch which would allow INSERTs to use the same multi-insert machinery as COPY FROM, both of which will require refactoring that code. With those two patches, there's going to be a lot of code churn in copy.c anyway, so now seems like a good time to do this refactoring. The CopyStateData struct is also split. All the formatting options, like FORMAT, QUOTE, ESCAPE, are put in a new CopyFormatOption struct, which is used by both COPY FROM and TO. Other state data are kept in separate CopyFromStateData and CopyToStateData structs. Reviewed-by: Soumyadeep Chakraborty, Erik Rijkers, Vignesh C, Andres Freund Discussion: https://www.postgresql.org/message-id/8e15b560-f387-7acc-ac90-763986617bfb%40iki.fi https://git.postgresql.org/pg/commitdiff/c532d15dddff14b01fe9ef1d465013cb8ef186df

  • Fix a few comments that referred to copy.c. Missed these in the previous commit. https://git.postgresql.org/pg/commitdiff/68b1a4877ef54f5ee6e05c64876655dd13d2c73b

  • Move per-agg and per-trans duplicate finding to the planner. This has the advantage that the cost estimates for aggregates can count the number of calls to transition and final functions correctly. Bump catalog version, because views can contain Aggrefs. Reviewed-by: Andres Freund Discussion: https://www.postgresql.org/message-id/b2e3536b-1dbc-8303-c97e-89cb0b4a9a48%40iki.fi https://git.postgresql.org/pg/commitdiff/0a2bc5d61e713e3fe72438f020eea5fcc90b0f0b

  • Fix expected output: the order of agg permission checks changed. Commit 0a2bc5d61e changed the order that permissions on the final and transition functions of an aggregate are checked in. That shows up as a difference in the order the LOG messages in this sepgsql regression test are printed. Adjust the expected output. Per buildfarm failure in rhinoceros. https://git.postgresql.org/pg/commitdiff/8818ad5b1557d42cd58e5196fac7084a2389bdad

Álvaro Herrera pushed:

  • Make some sanity-check elogs more verbose. A few sanity checks in funcapi.c were not mentioning all the possible clauses for failure, confusing developers who fat-fingered catalog data additions. Make the errors more detailed to avoid wasting time in pinpointing mistakes. Per complaint from Craig Ringer. Reviewed-by: Tom Lane tgl@sss.pgh.pa.us Discussion: https://postgr.es/m/CAMsr+YH7Kd87A3cU5m_wKo46HPQ46zFv5wesFNL0YWxkGhGv3g@mail.gmail.com https://git.postgresql.org/pg/commitdiff/fe051291550ab88267ce3104e9833925bf757393

  • Don't hold ProcArrayLock longer than needed in rare cases. While cancelling an autovacuum worker, we hold ProcArrayLock while formatting a debugging log string. We can make this shorter by saving the data we need to produce the message and doing the formatting outside the locked region. This isn't terribly critical, as it only occurs pretty rarely: when a backend runs deadlock detection and it happens to be blocked by a autovacuum running autovacuum. Still, there's no need to cause a hiccup in ProcArrayLock processing, which can be very high-traffic in some cases. While at it, rework code so that we only print the string when it is really going to be used, as suggested by Michael Paquier. Discussion: https://postgr.es/m/20201118214127.GA3179@alvherre.pgsql Reviewed-by: Michael Paquier michael@paquier.xyz https://git.postgresql.org/pg/commitdiff/450c8230b1f7eed1e927c44fb710d80502bea1d2

  • Avoid spurious waits in concurrent indexing. In the various waiting phases of CREATE INDEX CONCURRENTLY (CIC) and REINDEX CONCURRENTLY (RC), we wait for other processes to release their snapshots; this is necessary in general for correctness. However, processes doing CIC in other tables cannot possibly affect CIC or RC done in "this" table, so we don't need to wait for those. This commit adds a flag in MyProc->statusFlags to indicate that the current process is doing CIC, so that other processes doing CIC or RC can ignore it when waiting. Note that this logic is only valid if the index does not access other tables. For simplicity we avoid setting the flag if the index has a column that's an expression, or has a WHERE predicate. (It is possible to have expressional or partial indexes that do not access other tables, but figuring that out would require more work.) This flag can potentially also be used by processes doing REINDEX CONCURRENTLY to be skipped; and by VACUUM to ignore processes in CIC or RC for the purposes of computing an Xmin. That's left for future commits. Author: Álvaro Herrera alvherre@alvh.no-ip.org Author: Dimitry Dolgov 9erthalion6@gmail.com Reviewed-by: Michael Paquier michael@paquier.xyz Discussion: https://postgr.es/m/20200810233815.GA18970@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/c98763bf51bf610b3ee7e209fc76c3ff9a6b3163

  • Restore lock level to update statusFlags. Reverts 27838981be9d (some comments are kept). Per discussion, it does not seem safe to relax the lock level used for this; in order for it to be safe, there would have to be memory barriers between the point we set the flag and the point we set the trasaction Xid, which perhaps would not be so bad; but there would also have to be barriers at the readers' side, which from a performance perspective might be bad. Now maybe this analysis is wrong and it is safe for some reason, but proof of that is not trivial. Discussion: https://postgr.es/m/20201118190928.vnztes7c2sldu43a@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/dcfff74fb16622898a9d1df33e530f477caab095

David Rowley pushed:

Michaël Paquier pushed:

  • Use macros instead of hardcoded offsets for LWLock initialization. This makes the code slightly easier to follow, as the initialization relies on an offset that overlapped with an equivalent set of macros defined, which are used in other places already. Author: Japin Li Discussion: https://postgr.es/m/MEYP282MB1669FB410006758402F2C3A2B6E00@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM https://git.postgresql.org/pg/commitdiff/d03d7549b29236e300aceac0c22173cf19acc675

  • Remove catalog function currtid(). currtid() and currtid2() are an undocumented set of functions whose sole known user is the Postgres ODBC driver, able to retrieve the latest TID version for a tuple given by the caller of those functions. As used by Postgres ODBC, currtid() is a shortcut able to retrieve the last TID loaded into a backend by passing an OID of 0 (magic value) after a tuple insertion. This is removed in this commit, as it became obsolete after the driver began using "RETURNING ctid" with inserts, a clause supported since Postgres 8.2 (using RETURNING is better for performance anyway as it reduces the number of round-trips to the backend). currtid2() is still used by the driver, so this remains around for now. Note that this function is kept in its original shape for backward compatibility reasons. Per discussion with many people, including Andres Freund, Peter Eisentraut, Álvaro Herrera, Hiroshi Inoue, Tom Lane and myself. Bump catalog version. Discussion: https://postgr.es/m/20200603021448.GB89559@paquier.xyz https://git.postgresql.org/pg/commitdiff/7b94e999606e2e2e10d68d544d49fc5a5d5785ac

Fujii Masao pushed:

  • doc: Get rid of unnecessary space character from some index items. Previously some index items have " ," (i.e., space + comma) in the docs as follows. Since the space character before the comma is unnecessary, this commit gets rid of that for the sake of consistency with other index items. parallel_leader_participation configuration parameter , Other Planner Options Author: Fujii Masao Reviewed-by: Euler Taveira Discussion: https://postgr.es/m/e87b4ddf-1498-2850-bf55-519df3928fd4@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/e522024bd8dd28a0f13dcccfd39170698f45c939

  • doc: Add description about re-analysis and re-planning of a prepared statement. A prepared statement is re-analyzed and re-planned whenever database objects used in the statement have undergone definitional changes or the planner statistics of them have been updated. The former has been documented from before, but the latter was not previously. This commit adds the description about the latter case into the docs. Author: Atsushi Torikoshi Reviewed-by: Andy Fan, Fujii Masao Discussion: https://postgr.es/m/3ac82f4817c9fe274a905c8a38d87bd9@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/4a36eab79a193700b7b65baf6c09c795c90c02c6

  • pg_stat_statements: Track number of times pgss entries were deallocated. If more distinct statements than pg_stat_statements.max are observed, pg_stat_statements entries about the least-executed statements are deallocated. This commit enables us to track the total number of times those entries were deallocated. That number can be viewed in the pg_stat_statements_info view that this commit adds. It's useful when tuning pg_stat_statements.max parameter. If it's high, i.e., the entries are deallocated very frequently, which might cause the performance regression and we can increase pg_stat_statements.max to avoid those frequent deallocations. The pg_stat_statements_info view is intended to display the statistics of pg_stat_statements module itself. Currently it has only one column "dealloc" indicating the number of times entries were deallocated. But an upcoming patch will add other columns (for example, the time at which pg_stat_statements statistics were last reset) into the view. Author: Katsuragi Yuta, Yuki Seino Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/0d9f1107772cf5c3f954e985464c7298@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/9fbc3f318d039c3e1e8614c38e40843cf8fcffde

  • Use standard SIGHUP and SIGTERM signal handlers in worker_spi. Previously worker_spi used its custom signal handlers for SIGHUP and SIGTERM. This commit makes worker_spi use the standard signal handlers, to simplify the code. Note that die() is used as the standard SIGTERM signal handler in worker_spi instead of SignalHandlerForShutdownRequest() or bgworker_die(). Previously the exit handling was only able to exit from within the main loop, and not from within the backend code it calls. This is why die() needs to be used here, so worker_spi can respond to SIGTERM signal while it's executing a query. Maybe we can say that it's a bug that worker_spi could not respond to SIGTERM during query execution. But since worker_spi is a just example of the background worker code, we don't do the back-patch. Thanks to Craig Ringer for the report and investigation of the issue. Author: Bharath Rupireddy Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/CALj2ACXDEZhAFOTDcqO9cFSRvrFLyYOnPKrcA1UG4uZn9hUAVg@mail.gmail.com Discussion: https://postgr.es/m/CAGRY4nxsAe_1k_9g5b47orA0S011iBoHsXHFMH7cg7HV0O1bwQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2a0847720ae6fa4180bfbe404e4e6bbf2f737708

  • Use standard SIGTERM signal handler die() in test_shm_mq worker. Previously test_shm_mq worker used the stripped-down version of die() as the SIGTERM signal handler. This commit makes it use die(), instead, to simplify the code. In terms of the code, the difference between die() and the stripped-down version previously used is whether the signal handler directly may call ProcessInterrupts() or not. But this difference doesn't exist in a background worker because, in bgworker, DoingCommandRead flag will never be true and die() will never call ProcessInterrupts() directly. Therefore test_shm_mq worker can safely use die(), like other bgworker proceses (e.g., logical replication apply launcher or autoprewarm worker) currently do. Thanks to Craig Ringer for the report and investigation of the issue. Author: Bharath Rupireddy Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/CAGRY4nxsAe_1k_9g5b47orA0S011iBoHsXHFMH7cg7HV0O1bwQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ef848f4ac5a4bd072c65867186268775acfb4298

  • Fix CLUSTER progress reporting of number of blocks scanned. Previously pg_stat_progress_cluster view reported the current block number in heap scan as the number of heap blocks scanned (i.e., heap_blks_scanned). This reported number could be incorrect when synchronize_seqscans is enabled, because it allowed the heap scan to start at block in middle. This could result in wraparounds in the heap_blks_scanned column when the heap scan wrapped around. This commit fixes the bug by calculating the number of blocks from the block that the heap scan starts at to the current block in scan, and reporting that number in the heap_blks_scanned column. Also, in pg_stat_progress_cluster view, previously heap_blks_scanned could not reach heap_blks_total at the end of heap scan phase if the last pages scanned were empty. This commit fixes the bug by manually updating heap_blks_scanned to the same value as heap_blks_total when the heap scan phase finishes. Back-patch to v12 where pg_stat_progress_cluster view was introduced. Reported-by: Matthias van de Meent Author: Matthias van de Meent Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/CAEze2WjCBWSGkVfYag001Rc4+-nNLDpWM7QbyD6yPvuhKs-gYQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/3df51ca8b39f08ef19a77b9776f2547c86b70c49

Andrew Gierth pushed:

  • Properly check index mark/restore in ExecSupportsMarkRestore. Previously this code assumed that all IndexScan nodes supported mark/restore, which is not true since it depends on optional index AM support functions. This could lead to errors about missing support functions in rare edge cases of mergejoins with no sort keys, where an unordered non-btree index scan was placed on the inner path without a protecting Materialize node. (Normally, the fact that merge join requires ordered input would avoid this error.) Backpatch all the way since this bug is ancient. Per report from Eugen Konkov on irc. Discussion: https://postgr.es/m/87o8jn50be.fsf@news-spur.riddles.org.uk https://git.postgresql.org/pg/commitdiff/660b89928d18386de7755565c008439ae75d1218

Amit Kapila pushed:

Thomas Munro pushed:

Peter Eisentraut pushed:

Noah Misch pushed:

Pending Patches

Amul Sul sent in another revision of a patch to implement ALTER SYSTEM READ {ONLY|WRITE}.

Daniel Vérité sent in another revision of a patch to implement batch/pipelining in libpq.

Justin Pryzby and Tomáš Vondra traded patches to implement extended statistics on expressions.

Álvaro Herrera sent in another revision of a patch to Avoid errors in brin summarization, which can happen if an index is reindexed concurrently.

Álvaro Herrera sent in a patch to fix a bug that manifested as a walsender getting stuck during shutdown and not shut down, thus preventing postmaster from completing the shutdown cycle by checking whether XLogRecPtrIsInvalid(replicatedPtr) was true.

Zeng Wenjing sent in three more revisions of a patch to implement global temporary tables.

Bharath Rupireddy sent in two more revisions of a patch to implement postgres_fdw connection caching - cause remote sessions linger till the local session exit.

Bharath Rupireddy and Heikki Linnakangas traded patches to make it possible to use parallel inserts in CREATE TABLE AS, where it's safe to do so.

Tomáš Vondra sent in another revision of a patch to use non-volatile storage as a WAL buffer.

Takayuki Tsunakawa sent in two more revisions of a patch to add bulk inserts for foreign tables.

Justin Pryzby sent in another revision of a patch to allow INSERT SELECT to use a BulkInsertState, make INSERT SELECT use multi_insert, and dynamically switch to multi-insert mode.

Michaël Paquier sent in another revision of a patch to rework the SHA2 and crypto hash APIs, switch cryptohash_openssl.c to use EVP, and make pgcrypto use the in-core resowner facility for EVP.

Justin Pryzby sent in another revision of a patch to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly.

Keisuke Kuroda sent in a patch to fix a bug that manifested as huge memory consumption on partitioned table with FKs by reducing the size of the ri SPI plan hash.

Konstantin Knizhnik sent in two more revisions of a patch to implement custom compression for libpq.

Takamichi Osumi sent in another revision of a patch to make it possible to disable WAL logging to speed up bulk loads.

Nathan Bossart sent in two revisions of a patch to add a FAST option to CHECKPOINT.

Amit Kapila, Ajin Cherian, and Peter Smith traded patches to implement logical decoding for two-phase transactions.

David Rowley sent in a patch to define pg_attribute_cold and pg_attribute_hot to be empty macros on minGW 8.1 so as to avoid a bug in that toolchain.

Li Japin sent in another revision of a patch to allow terminating the idle sessions via a new GUC, idle_session_timeout, and call setitimer() less often.

Euler Taveira de Oliveira sent in a patch to add logical decoding messages to pgoutput, add xid to messages when streaming, explain why LOGICAL_REP_MSG_MESSAGE is ignored, simplify the parse_output_parameters function to take a whole PGOutputData instead of bits and pieces, adjust in_streaming for messages, and overhaul the tests to take account for all this.

Peter Eisentraut sent in another revision of a patch to add a result_format_auto_binary_types setting.

Michaël Paquier sent in another revision of a patch to fix a problem that manifested as vac_update_datfrozenxid will raise "wrong tuple length" if pg_database tuple contains toast attribute.

Kyotaro HORIGUCHI sent in another revision of a patch to fix handling of NaN in the geometry types.

Masahiko Sawada sent in another revision of a patch to enable two-phase commit for multiple foreign servers.

Daniel Gustafsson sent in another revision of a patch to make it possible to enable and disable data checksums online.

Alexander Korotkov sent in a patch to implement a built-in infrastructure for reproduction of concurrency issues in automated test suites. Central to this infrastructure are "stop events," which which are special places in the code, where the execution could be stopped on some condition.

Peter Eisentraut sent in another revision of a patch to implement SEARCH and CYCLE clauses in common table expressions per the SQL standard.

Thomas Munro sent in another revision of a patch to get latches to send fewer signals, use SIGURG rather than SIGUSR1 for latches, use signalfd for epoll latches, and use EVFILT_SIGNAL for kqueue latches.

Tom Lane sent in another revision of a patch to report GUC changes at query end.

Peter Smith sent in a patch to use enums for message types.

David Zhang sent in three revisions of a patch to add table access method as an option to pgbench.

Anastasia Lubennikova sent in a patch to handle negative number of tuples passed to normal_rand().

Peter Eisentraut sent in a patch to pageinspect to change the block number arguments to bigint.

Bertrand Drouvot sent in four more revisions of a patch to make it possible to log the standby recovery conflict waits via a new GUC, log_recovery_conflict_waits.

Kasahara Tatsuhito sent in three more revisions of a patch to fix a bug that manifested as an autovacuum issue with large numbers of tables.

Masahiko Sawada sent in another revision of a patch to add basic statistics to the pg_stat_wal view.

Takamichi Osumi sent in a patch to prevent a scenario that archive recovery hits WALs which come from wal_level=minimal and the server continues to work, which condition could cause data not to be replicated.

Euler Taveira de Oliveira sent in a patch to remove temporary files after a backend crash in order to avert ENOSPC conditions that could result from multiple crashes.

Pavel Borisov sent in another revision of a patch to implement covering indexes using the SP-GiST index access method.

Kirk Jamison sent in another revision of a patch to prevent invalidating blocks in smgrextend() during recovery, add a bool parameter in smgrnblocks() for cached blocks, slim down DropRelFileNodeBuffers() during recovery by avoiding scanning the whole buffer pool when the relation is small enough or the the total number of blocks to be invalidated is below the threshold of full scanning, and getting DropRelFileNodesAllBuffers() to skip the time-consuming scan of the whole buffer pool during recovery when the relation is small enough, or when the number of blocks to be invalidated is below the full scan threshold.

Krunal Bauskar and Alexander Korotkov traded patches to improve the spinlock implementation on ARM.

Arne Roland sent in three revisions of a patch to ensure that renaming a trigger on a partitioned table also renames triggers on the partitions.

Bharath Rupireddy sent in a patch to fix the error message for pg_workers shutting down so it talks about background workers instead of the non-existent connections that apply to other cases.

Stephen Frost sent in another revision of a patch to add GSS information to the connection authorized log message, if needed.

Michael Banck sent in a patch to clarify the fact that CREATEROLE roles can GRANT default roles.

Ashutosh Bapat and Alexander Korotkov traded patches to make it easy to print LSNs.

Andreas Karlsson sent in a PoC patch to fix the fact that the inet/cidr support shipped broken by throwing away netmask information in the btree_gist supplied extension.

Pavel Stěhule and Justin Pryzby traded patches to make it possible to read the tables to be dumped by pg_dump from a file.

Justin Pryzby sent in another revision of a patch to make CLUSTER ON a separate dump object in pg_dump, implement CLUSTER for partitioned tables, propagate changes to indisclustered to child/parents, invalidate parent indexes, invalidate parent index cluster on attach, and preserve indisclustered on children of clustered, partitioned indexes.

Simon Riggs sent in another revision of a patch to add a FAST_FREEZE option to VACUUM.

Simon Riggs sent in a patch to implement one_freeze then max_freeze for lazy VACUUM.

Justin Pryzby sent in another revision of a patch to make pg_ls_* show directories and shared filesets.

Justin Pryzby sent in another revision of a patch to remove references to pg_dump's pre-8.1 switch behaviour.

Justin Pryzby sent in another revision of a patch to allow CREATE INDEX CONCURRENTLY on partitioned tables, add a SKIPVALID flag for more integration, and make ReindexPartitions() set indisvalid.

Paul A Jungwirth sent in two more revisions of a patch to implement multiranges.

Dean Rasheed sent in another revision of a patch to improve estimation of OR clauses.

James Coleman sent in a patch to error if gather merge paths aren't sufficiently sorted.

James Coleman sent in another revision of a patch to ensure that generate_useful_gather_paths doesn't skip unsorted subpaths, enforce parallel safety of pathkeys in generate_useful_gather_paths, disallow SRFs in proactive sort, remove the volatile expr target search on the grounds that it's not needed then, and document find_em_expr_usable_for_sorting_rel in prepare_sort_from_pathkeys.

Categories: Informatika

WAL-G 0.2.19 released

2020, November 30 - 01:00

WAL-G team is happy to announce the release of WAL-G 0.2.19

WAL-G is an archival restoration tool for PostgreSQL (also for MySQL, MariaDB, SQL Server, FoundationDB, and MongoDB).

WAL-G releases can be found here

Notable changes in this release include:

  1. Fixes for S3 and GCP storages #656 #756.
  2. Add wal-show command to get information about WAL storage folder.
  3. Add wal-verify command. It checks the integrity of WAL history starting from the oldest backup available in current timeline history.
  4. Add wal-receive command. You can use WAL-G as a replica running on another host to ensure RPO=0. This is beta functionality: API may change in the future.
  5. Add reverse delta unpack for backup-fetch (--reverse-unpack flag).
  6. Add redundant archives skipping for backup-fetch (--skip-redundant-tars flag, designed to work in pair with reverse delta unpack).
  7. Add page checksum verification for backup-push (--verify flag).

You can find more about some of these new features in Daniil Zakhlystov's post about his GSoC.

Thanks!

Best regards, Andrey Borodin.

Categories: Informatika

Pgpool-II 4.2.0 is now released.

2020, November 27 - 01:00

Pgpool Global Development Group is pleased to announce the availability of Pgpool-II 4.2.0.

Pgpool-II is a tool to add useful features to PostgreSQL, including:

  • connection pooling
  • load balancing
  • automatic fail over and more.

For more information, please see the website.

V4.2 contains new features and enhancements, including:

  • Some items in the configuration file pgpool.conf are vastly enhanced for easier configuration and administration.
  • Implement logging_collector for easier log management.
  • Implement log_disconnections to collect disconnection logs.
  • Implement pg_enc and pg_md5 to allow to register multiple passwords at once.
  • Allow to show statistics of health check by using SHOW POOL_HEALTH_CHECK_STATS command, and also allow to show statistics of issued SQL by using SHOW POOL_BACKEND_STATS command.
  • New PCP command pcp_reload_config is added.
  • Now it is possible to omit write_function_list and read_only_function_list by looking at system catalog information.
  • Add new clustering mode snapshot_isolation_mode which guarantees not only data modifications to multiple PostgreSQL but read consistency.
  • Support LDAP authentication between clients and Pgpool-II.
  • Add ssl_crl_file and ssl_passphrase_command to SSL configuration.
  • Import PostgreSQL 13's SQL parser.

You can download it from here.

Please take a look at release notes.

Categories: Informatika

pgagroal 1.0.0

2020, November 25 - 01:00

The pgagroal community is happy to announce version 1.0.0.

New features

  • Set the process title
  • Connection tracker

Plus various improvements.

pgagroal

pgagroal is a high-performance protocol-native connection pool for PostgreSQL.

Features

  • High performance
  • Connection pool
  • Limit connections for users and databases
  • Prefill support
  • Remove idle connections
  • Perform connection validation
  • Enable / disable database access
  • Graceful / fast shutdown
  • Prometheus support
  • Remote management
  • Authentication query support
  • Failover support
  • Transport Layer Security (TLS) v1.2+ support
  • Daemon mode
  • User vault

Learn more on our web site or GitHub. Follow on Twitter.

pgagroal is released under the 3-clause BSD license, and is sponsored by Red Hat.

Categories: Informatika

pgBadger v11.4 released

2020, November 24 - 01:00

Grenoble, France - November 24th, 2020

pgBadger

pgBadger is a PostgreSQL performance analyzer, built for speed with fully detailed reports based on your PostgreSQL log files.


pgBadger 11.4 was released today, this release of pgBadger improve the support for PostgreSQL 13 new log information, fixes some issues reported by users since past four months and adds some new reports:

  • Add new report about checkpoint starting causes with a chart.
  • Add full automatic vacuum information in "Vacuums per table" report for buffer usage (hits, missed, dirtied), skipped due to pins, skipped frozen and WAL usage (records, full page images, bytes). In report "Tuples removed per table" additional autovacuum information are tuples remaining, tuples not yet removable and pages remaining. These information are only available on the "Table" tab.
  • Add detection of application name from connection authorized traces.
  • Add support to new placeholder '%b' in log_line_prefix.

For the complete list of changes, please checkout the release note on https://github.com/darold/pgbadger/blob/master/ChangeLog

Links & Credits

I would like to thank all users who submitted patches and users who reported bugs and feature requests, they are all cited the ChangeLog file.

pgBadger is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly to pgbadger@darold.net.



For a complete list of commercial support near of your place take a look at the PostgreSQL Professional Services page, they all do great job and most of them can help you.

About pgBadger

pgBagder is a new generation log analyzer for PostgreSQL, created by Gilles Darold (also author of ora2pg, the powerful migration tool). pgBadger is a fast and easy tool to analyze your SQL traffic and create HTML5 reports with dynamics graphs. pgBadger is the perfect tool to understand the behavior of your PostgreSQL servers and identify which SQL queries need to be optimized.


Docs, Download & Demo at http://pgbadger.darold.net/

Categories: Informatika

PostgreSQL Weekly News - November 22, 2020

2020, November 23 - 01:00
PostgreSQL Weekly News - November 22, 2020

Person of the week: https://postgresql.life/post/markus_wanner/

PostgreSQL Product News

PGroonga 2.2.7 a full text search platform for all languages, released. https://pgroonga.github.io/

PostGIS 3.0.3, the industry standard geographic information system package for PostgreSQL, released. http://postgis.net/2020/11/20/postgis-3.0.3/

pgtt 2.2, an extension to implement global temporary tables, released. https://github.com/darold/pgtt/releases/tag/v2.2

pgAdmin4 4.28, a web- and native GUI control center for PostgreSQL, released. https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_4_28.html

pgbouncer 1.15.0, a connection pooler and more for PostgreSQL, released. https://www.pgbouncer.org/2020/11/pgbouncer-1-15-0

pitrery 3.2, a set of Bash scripts to manage PITR backups for PostgreSQL, released. http://dalibo.github.io/pitrery/

pg_statement_rollback v1.1, an extension that adds server side transaction with rollback at statement level, released. https://github.com/lzlabs/pg_statement_rollback/releases/tag/v1.1

PostgreSQL Jobs for November

http://archives.postgresql.org/pgsql-jobs/2020-11/

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

Tom Lane pushed:

  • Fix fuzzy thinking about amcanmulticol versus amcaninclude. These flags should be independent: in particular an index AM should be able to say that it supports include columns without necessarily supporting multiple key columns. The included-columns patch got this wrong, possibly aided by the fact that it didn't bother to update the documentation. While here, clarify some text about amcanreturn, which was a little vague about what should happen when amcanreturn reports that only some of the index columns are returnable. Noted while reviewing the SP-GiST included-columns patch, which quite incorrectly (and unsafely) changed SP-GiST to claim amcanmulticol = true as a workaround for this bug. Backpatch to v11 where included columns were introduced. https://git.postgresql.org/pg/commitdiff/29d29d652f0be47dc42fa9d667dee5b8e1baa18a

  • Use "true" not "TRUE" in one ICU function call. This was evidently missed in commit 6337865f3, which generally did s/TRUE/true/ everywhere. It escaped notice up to now because ICU versions before ICU 68 provided definitions of "TRUE" and "FALSE" regardless. With ICU 68, it fails to compile. Per report from Condor. Back-patch to v11 where 6337865f3 came in. (I've not tested v10, where this call originated, but I imagine it's fine since we defined TRUE in c.h back then.) Discussion: https://postgr.es/m/7a6f3336165bfe3ca66abcda7966f9d0@stz-bg.com https://git.postgresql.org/pg/commitdiff/ad84ecc98d7e2ad81567094b8a6910b5078927a7

  • Do not return NULL for error cases in satisfies_hash_partition(). Since this function is used as a CHECK constraint condition, returning NULL is tantamount to returning TRUE, which would have the effect of letting in a row that doesn't satisfy the hash condition. Admittedly, the cases for which this is done should be unreachable in practice, but that doesn't make it any less a bad idea. It also seems like a dartboard was used to decide which error cases should throw errors as opposed to returning NULL. For the checks for NULL input values, I just switched it to returning false. There's some argument that an error would be better; but the case really should be can't-happen in a generated hash constraint, so it's likely not worth more code for. For the parent-relation-open-failure case, it seems like we might as well let relation_open throw an error, instead of having an impossible-to-diagnose constraint failure. Back-patch to v11 where this code came in. Discussion: https://postgr.es/m/24067.1605134819@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/4025e6c46620048804467d2ad29d31aa9ba50387

  • Don't Insert() a VFD entry until it's fully built. Otherwise, if FDDEBUG is enabled, the debugging output fails because it tries to read the fileName, which isn't set up yet (and should in fact always be NULL). AFAICT, this has been wrong since Berkeley. Before 96bf88d52, it would accidentally fail to crash on platforms where snprintf() is forgiving about being passed a NULL pointer for %s; but the file name intended to be included in the debug output wouldn't ever have shown up. Report and fix by Greg Nancarrow. Although this is only visibly broken in custom-made builds, it still seems worth back-patching to all supported branches, as the FDDEBUG code is pretty useless as it stands. Discussion: https://postgr.es/m/CAJcOf-cUDgm9qYtC_B6XrC6MktMPNRby2p61EtSGZKnfotMArw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2bd49b493a52f0a21bc3fc51c355963f99ac1a4e

  • Further fixes for CREATE TABLE LIKE: cope with self-referential FKs. Commit 502898192 was too careless about the order of execution of the additional ALTER TABLE operations generated by expandTableLikeClause. It just stuck them all at the end, which seems okay for most purposes. But it falls down in the case where LIKE is importing a primary key or unique index and the outer CREATE TABLE includes a FOREIGN KEY constraint that needs to depend on that index. Weird as that is, it used to work, so we ought to keep it working. To fix, make parse_utilcmd.c insert LIKE clauses between index-creation and FK-creation commands in the transformed list of commands, and change utility.c so that the commands generated by expandTableLikeClause are executed immediately not at the end. One could imagine scenarios where this wouldn't work either; but currently expandTableLikeClause only makes column default expressions, CHECK constraints, and indexes, and this ordering seems fine for those. Per bug #16730 from Sofoklis Papasofokli. Like the previous patch, back-patch to all supported branches. Discussion: https://postgr.es/m/16730-b902f7e6e0276b30@postgresql.org https://git.postgresql.org/pg/commitdiff/97390fe8a6e96a153e59b0180f4303acaeb75b84

  • Remove undocumented IS [NOT] OF syntax. This feature was added a long time ago, in 7c1e67bd5 and eb121ba2c, but never documented in any user-facing way. (Documentation added in 6126d3e70 was commented out almost immediately, in 8272fc3f7.) That's because, while this syntax is defined by SQL:99, our implementation is only vaguely related to the standard's semantics. The standard appears to intend a run-time not parse-time test, and it definitely intends that the test should understand subtype relationships. No one has stepped up to fix that in the intervening years, but people keep coming across the code and asking why it's not documented. Let's just get rid of it: if anyone ever wants to make it work per spec, they can easily recover whatever parts of this code are still of value from our git history. If there's anyone out there who's actually using this despite its undocumented status, they can switch to using pg_typeof() instead, eg. "pg_typeof(something) = 'mytype'::regtype". That gives essentially the same semantics as what our IS OF code did. (We didn't have that function last time this was discussed, or we would have ripped out IS OF then.) Discussion: https://postgr.es/m/CAKFQuwZ2pTc-DSkOiTfjauqLYkNREeNZvWmeg12Q-_69D+sYZA@mail.gmail.com Discussion: https://postgr.es/m/BAY20-F23E9F2B4DAB3E4E88D3623F99B0@phx.gbl Discussion: https://postgr.es/m/3E7CF81D.1000203@joeconway.com https://git.postgresql.org/pg/commitdiff/926fa801ac9eb54c5275472271ec63a059904698

  • On macOS, use -isysroot in link steps as well as compile steps. We previously put the -isysroot switch only into CPPFLAGS, theorizing that it was only needed to find the right copies of include files. However, it seems that we also need to use it while linking programs, to find the right stub ".tbd" files for libraries. We got away without that up to now, but apparently that was mostly luck. It may also be that failures are only observed when the Xcode version is noticeably out of sync with the host macOS version; the case that's prompting action right now is that builds fail when using latest Xcode (12.2) on macOS Catalina, even though it's fine on Big Sur. Hence, add -isysroot to LDFLAGS as well. (It seems that the more common practice is to put it in CFLAGS, whence it'd be included at both compile and link steps. However, we can't mess with CFLAGS in the platform template file without confusing configure's logic for choosing default CFLAGS.) This should be back-patched, but first let's see if the buildfarm likes it on HEAD. Report and patch by James Hilliard (some cosmetic mods by me) Discussion: https://postgr.es/m/20201120003314.20560-1-james.hilliard1@gmail.com https://git.postgresql.org/pg/commitdiff/49407dc32a2931550e4ff1dea314b6a25afdfc35

  • Extend the geometric regression test cases a little. Add another edge-case value to "point_tbl", and add a test for the line(point, point) function. Some of the behaviors exposed here are wrong, but the idea of committing this separately is to memorialize what we were getting, and to allow easier inspection of the behavior changes caused by upcoming patches. Kyotaro Horiguchi (line() test added by me) Discussion: https://postgr.es/m/CAGf+fX70rWFOk5cd00uMfa__0yP+vtQg5ck7c2Onb-Yczp0URA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/a45272b25d6fc8f96793623545fc1f836ac39d94

  • Fix FPeq() and friends to get the right answers for infinities. "FPeq(infinity, infinity)" returned false, on account of getting NaN when it subtracts the two inputs. Fix that by adding a separate check for exact equality. FPle() and FPge() similarly got the wrong answer for two like-signed infinities. In those cases, we can just rearrange the comparisons to avoid potentially subtracting infinities. While the sibling functions FPne() etc accidentally gave the right answers even with the internal NaN results, it seems best to make similar adjustments to them to avoid depending on this. FPeq() has to be converted to an inline function to avoid double evaluations of its arguments, and I did the same for the others just for consistency. In passing, make the handling of NaN cases in line_eq() and point_eq_point() simpler and easier to reason about, and perhaps faster. This results in just one visible regression test change: slope() now gives DBL_MAX for two inputs of (inf,1e300), which is consistent with what it does for (1e300,inf), so that seems like a bug fix. Discussion: https://postgr.es/m/CAGf+fX70rWFOk5cd00uMfa__0yP+vtQg5ck7c2Onb-Yczp0URA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/8597a48d01b6cc0b09ff626253ac93c67e5516d5

  • In geo_ops.c, represent infinite slope as Infinity, not DBL_MAX. Since we're assuming IEEE floats these days, there seems little reason not to do this. It has the advantage that when the slope is computed as infinite due to the presence of Inf coordinates, we get saner behavior than before from line_construct(), and thence also in some dependent operations such as finding the closest point. Also fix line_construct() to special-case slope zero. The previous coding got the right answer in most cases, but it could compute C as NaN when the point has Inf coordinates. Discussion: https://postgr.es/m/CAGf+fX70rWFOk5cd00uMfa__0yP+vtQg5ck7c2Onb-Yczp0URA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/9fe649ea295f00baf6d0f0c1f9b0cb1298f64fb9

Michaël Paquier pushed:

  • Relax INSERT privilege requirement for CTAS and matviews WITH NO DATA. When specified, WITH NO DATA does not insert any data into the relation created, so skip checking for the insert permissions. With WITH DATA or WITH NO DATA, it is always required for the user to have CREATE privileges on the schema targeted for the relation. Note that plain CREATE TABLE AS or CREATE MATERIALIZED VIEW queries have begun to work accidentally without INSERT privilege checks as of 874fe3ae, while using EXECUTE or EXPLAIN ANALYZE would fail with the ACL check, so this makes the behavior for all the command flavors consistent with each other. This is arguably a bug fix, but there have been no complaints about the current behavior either so stable branches are not changed. While on it, document properly the privileges requirements for each commands with more tests for all the scenarios possible, and avoid a useless bulk-insert allocation when using WITH NO DATA. Author: Bharath Rupireddy Reviewed-by: Anastasia Lubennikova, Michael Paquier Discussion: https://postgr.es/m/CALj2ACWc3N8j0_9nMPz9wcAUnVcdKHzFdDZJ3hVFNEbqtcyG9w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/846005e4f3829c3eafe1f8441b80ff90657d0a29

  • Add tab completion for CREATE [OR REPLACE] TRIGGER in psql. 92bf7e2 has added support for this grammar. Author: Noriyoshi Shinoda Discussion: https://postgr.es/m/TU4PR8401MB115244623CF4724DCA0D507FEEE30@TU4PR8401MB1152.NAMPRD84.PROD.OUTLOOK.COM https://git.postgresql.org/pg/commitdiff/bf0aa7c4b83bcf3116c5a3c191bbc677ab3beb59

  • Improve failure detection with array parsing in pg_dump. Similarly to 3636efa, the checks done in pg_dump when parsing array values from catalogs have been too lax. Under memory pressure, it could be possible, though very unlikely, to finish with dumps that miss some data like: - Statistics for indexes - Run-time configuration of functions - Configuration of extensions - Publication list for a subscription No backpatch is done as this is not going to be a problem in practice. For example, if an OOM causes an array parsing to fail, a follow-up code path of pg_dump would most likely complain with an allocation failure due to the memory pressure. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/20201111061319.GE2276@paquier.xyz https://git.postgresql.org/pg/commitdiff/13b58f8934e6252868231c3493d49b8c2b363e5d

  • Remove INSERT privilege check at table creation of CTAS and matview. As per discussion with Peter Eisentraunt, the SQL standard specifies that any tuple insertion done as part of CREATE TABLE AS happens without any extra ACL check, so it makes little sense to keep a check for INSERT privileges when using WITH DATA. Materialized views are not part of the standard, but similarly, this check can be confusing as this refers to an access check on a table created within the same command as the one that would insert data into this table. This commit removes the INSERT privilege check for WITH DATA, the default, that 846005e removed partially, but only for WITH NO DATA. Author: Bharath Rupireddy Discussion: https://postgr.es/m/d049c272-9a47-d783-46b0-46665b011598@enterprisedb.com https://git.postgresql.org/pg/commitdiff/878f3a19c6c8ff197e4a33f51d921a4abafcc494

Alexander Korotkov pushed:

Fujii Masao pushed:

  • Make the standby server promptly handle interrupt signals. This commit changes the startup process in the standby server so that it handles the interrupt signals after waiting for wal_retrieve_retry_interval on the latch and resetting it, before entering another wait on the latch. This change causes the standby server to promptly handle interrupt signals. Otherwise, previously, there was the case where the standby needs to wait extra five seconds to shutdown when the shutdown request arrived while the startup process was waiting for wal_retrieve_retry_interval on the latch. Author: Fujii Masao, but implementation idea is from Soumyadeep Chakraborty Reviewed-by: Soumyadeep Chakraborty Discussion: https://postgr.es/m/9d7e6ab0-8a53-ddb9-63cd-289bcb25fe0e@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/2945a488a3426838223a637cd326ec71fff61954

  • Emit log when restore_command succeeds but archived file faills to be restored. Previously, when restore_command claimed to succeed but failed to restore the file with the right name, for example, due to mis-configuration of restore_command, no log message was reported. Then the recovery failed later with an error message not directly related to the issue. This commit changes the recovery so that a log message is emitted in this error case. This would enable us to investigate what happened in this case more easily. Author: Jeff Janes, Fujii Masao Reviewed-by: Pavel Borisov, Kyotaro Horiguchi Discussion: https://postgr.es/m/CAMkU=1xkFs3Omp4JR4wMYWdam_KLuj6LXnTYfU8u3T0h=PLLMQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/a4ef0329c27156dca81714854599e7d82becb17f

Peter Eisentraut pushed:

Álvaro Herrera pushed:

Bruce Momjian pushed:

Amit Kapila pushed:

Peter Geoghegan pushed:

  • nbtree: Rename nbtinsert.c variables for consistency. Stop naming special area/opaque pointer variables 'lpageop' in contexts where it doesn't make sense. This is a holdover from a time when logic that performs tasks that are now spread across _bt_insertonpg(), _bt_findinsertloc(), and _bt_split() was more centralized. 'lpageop' denotes "left page", which doesn't make sense outside of contexts in which there isn't also a right page. Also acquire page flag variables up front within _bt_insertonpg(). This makes it closer to _bt_split() following refactoring commit bc3087b626d. This allows the page split and retail insert paths to both make use of the same variables. https://git.postgresql.org/pg/commitdiff/a034f8b60c6e9afd75f42dc420a76cb9d3875fcb

  • Deprecate nbtree's BTP_HAS_GARBAGE flag. Streamline handling of the various strategies that we have to avoid a page split in nbtinsert.c. When it looks like a leaf page is about to overflow, we now perform deleting LP_DEAD items and deduplication in one central place. This greatly simplifies _bt_findinsertloc(). This has an independently useful consequence: nbtree no longer relies on the BTP_HAS_GARBAGE page level flag/hint for anything important. We still set and unset the flag in the same way as before, but it's no longer treated as a gating condition when considering if we should check for already-set LP_DEAD bits. This happens at the point where the page looks like it might have to be split anyway, so simply checking the LP_DEAD bits in passing is practically free. This avoids missing LP_DEAD bits just because the page-level hint is unset, which is probably reasonably common (e.g. it happens when VACUUM unsets the page-level flag without actually removing index tuples whose LP_DEAD-bit was set recently, after the VACUUM operation began but before it reached the leaf page in question). Note that this isn't a big behavioral change compared to PostgreSQL 13. We were already checking for set LP_DEAD bits regardless of whether the BTP_HAS_GARBAGE page level flag was set before we considered doing a deduplication pass. This commit only goes slightly further by doing the same check for all indexes, even indexes where deduplication won't be performed. We don't completely remove the BTP_HAS_GARBAGE flag. We still rely on it as a gating condition with pg_upgrade'd indexes from before B-tree version 4/PostgreSQL 12. That makes sense because we sometimes have to make a choice among pages full of duplicates when inserting a tuple with pre version 4 indexes. It probably still pays to avoid accessing the line pointer array of a page there, since it won't yet be clear whether we'll insert on to the page in question at all, let alone split it as a result. Author: Peter Geoghegan pg@bowt.ie Reviewed-By: Victor Yegorov vyegorov@gmail.com Discussion: https://postgr.es/m/CAH2-Wz%3DYpc1PDdk8OVJDChGJBjT06%3DA0Mbv9HyTLCsOknGcUFg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/cf2acaf4dcb5e20204dcec4d698cb4478af533e7

Heikki Linnakangas pushed:

Thomas Munro pushed:

Magnus Hagander pushed:

  • Remove ability to independently select random number generator. Remove the ability to select random number generator independently from SSL library. Instead, use the random number generator from the SSL library (today only OpenSSL supported) if one is configured. If no SSL library is configured, use the platform default (which means use CryptoAPI on Win32 and /dev/urandom on Linux). This also restructures pg_strong_random.c to have three clearly separate sections, one for each implementation, with two functions in each, instead of a scattered set of ifdefs throughout the whole file. Author: Daniel Gustafsson, Magnus Hagander, Michael Paquier Discussion: https://postgr.es/m/632623.1605460616@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/16f96c74d48e65da23d28665103e2c4c9d3414cc
Pending Patches

Atsushi Torikoshi sent in another revision of a patch to Enabled pg_get_backend_memory_contexts() to collect arbitrary backend process's memory contexts. Previously, pg_get_backend_memory_contexts() could only get the local memory contexts. This patch enables to get memory contexts of the arbitrary backend process which PID is specified by the argument.

Tomáš Vondra sent in two revisions of a patch to support extended statistics on expressions.

Seino Yuki and Fujii Masao traded patches to add a new pg_stat_statements_info view to the pg_stat_statements extension.

Jacob Champion and Daniel Gustafsson traded patches to support NSS as a TLS backend for libpq.

Alexandra Pervushina sent in two more revisions of a patch to make it possible to make async replicas wait for lsn to be replayed.

Álvaro Herrera and Dmitry Dolgov traded patches to remove a spurious wait from CREATE INDEX CONCURRENTLY.

Thomas Munro sent in two revisions of a patch to track relation sizes in shared memory, and provide a lock-free fast path for smgrnblocks().

Masahiro Ikeda sent in another revision of a patch to add a track_wal_io_timing GUC and a matching pg_stat_wal view to back it.

Li Japin sent in four more revisions of a patch to make it possible to terminate idle sessions using a new GUC, idle_session_timeout, and in passing, call setitimer() less often.

Masahiko Sawada and Bertrand Drouvot traded patches to add information during standby recovery conflicts, controlled with a new GUC, log_recovery_conflict_waits.

Takamichi Osumi sent in two more revisions of a patch to make it possible to disallow WAL logging during data loads.

Simon Riggs sent in five revisions of a patch to fix a situation where the documented behavior of DISABLE_PAGE_SKIPPING doesn't match the current behavior.

Ajin Cherian and Amit Kapila traded patches to implement logical decoding of two-phase transactions.

Tatsuro Yamada sent in two more revisions of a patch to add a psql command for extended statistics, \dX.

Daniel Gustafsson sent in another revision of a patch to support enabling and disabling page checksums in a running cluster.

Justin Pryzby sent in a patch to allow composite types in bootstrap and add a column to pg_statistic_ext_data, stxdexpr.

Peter Geoghegan sent in another revision of a patch to delete older versions in unique indexes to avoid page splits.

Heikki Linnakangas sent in another revision of a patch to split copy.c into four files: copy.c, which now contains the functions that are common to COPY FROM and COPY TO, copyto.c, which contains code for COPY TO, copyfrom.c which contains code for initilizing COPY FROM, and routing tuples to the correct table, and copyfromparse.c, which contains code for reading from the client/file/program, and parsing the input text/CSV/binary format into tuples.

David G. Johnston sent in another revision of a patch to clarify the fact that signal functions have no feedback.

Simon Riggs sent in a patch to add a wal_sessioninfo GUC which when turned on adds information about the user's session onto every commit or abort record.

David G. Johnston sent in a patch to clarify the documentation for batch/pipelining support in libpq.

Dean Rasheed sent in another revision of a patch to improve estimation of OR clauses.

Heikki Linnakangas sent in a patch to make the ResourceOwner implementation more efficient and expose handier interfaces to it to external code.

Aleksey Kondratov sent in a patch to add a custom GUC to the PostgreSQL FDW which controls whether it tries to keep connections, with the surprising name of keep_connections.

Takayuki Tsunakawa and Tomáš Vondra traded patches to implement INSERT batching for the PostgreSQL FDW.

Justin Pryzby and Pavel Stěhule traded patches to add a --filter option to pg_dump, which draws from a file which specifies the filters to be used, and makes argument parsing available both to the command line and to files so described.

Bharath Rupireddy sent in another revision of a patch to use standard SIGHUP and SIGTERM handlers in the autoprewarm module.

Etsuro Fujita sent in another revision of a patch to enable Asynchronous Append on postgres_fdw nodes.

Tomáš Vondra sent in a patch to fix a bug that manifested as incremental sort changing query behavior by adding a check for is_parallel_safe to get_useful_pathkeys_for_relation().

David Pirotte sent in another revision of a patch to add xid to messages when streaming, and add a "messages" option to the pgoutput output plugin. When "messages" is true, logical decoding messages (i.e. generated via pg_logical_emit_message) are sent to the slot consumer.

Junfeng (Jerome) Yang sent in another revision of a patch to fix vacuum freeze with pg_database toast attribute by changing vac_update_datfrozenxid()'s source for the pg_database tuple for current database from the disk heap table instead of system cache.

Dmitry Dolgov sent in a patch to prevent jumbling of every element in ArrayExpr. The motivation for this is that pg_stat_statements produces multiple entries for queries like SELECT something FROM table WHERE col IN (1, 2, 3, ...) depending on number of parameters, because every element of ArrayExpr is jumbled. Make Consts contribute nothing to the jumble hash if they're part of a series and at position further that specified threshold. Do the same for similar queries with VALUES as well.

Thomas Munro sent in another revision of a patch to implement WAL prefetch.

Andrew Dunstan sent in another revision of a patch to allow matching the whole DN from a client certificate.

Pavel Stěhule sent in two revisions of a patch to add shorthand for argument and local variable references to PL/pgsql.

Atsushi Torikoshi sent in two revisions of a patch to document the fact that cached plans are invalidated when statistics used to create them are updated.

Atsushi Torikoshi sent in two revisions of a patch to update the PREPARE documentation to note that pg_prepared_statements now shows how many times each prepared statement chose generic and custom plan cumulatively in the current session.

Vigneshwaran C sent in another revision of a patch to parallelize COPY.

Thomas Munro sent in another revision of a patch to minimize latch signals.

Masahiro Ikeda sent in another revision of a patch to add statistics to pg_stat_wal view for wal related parameter tuning.

Craig Ringer sent in a patch to detect and report when a LWLockAcquire() results in a simple self-deadlock due to the caller already holding the LWLock..

Craig Ringer sent in a patch to Remove bogus lwlock__acquire tracepoint from LWLockWaitForVar, pass the target LWLock* and tranche ID to LWLock tracepoint, and add to the tracepoints in LWLock routines.

Heikki Linnakangas sent in another revision of a patch to move peragg and pertrans duplicate finding to prepagg.c.

Craig Ringer sent in a patch atop the resource owner refactoring patch above which adds some systemtap/dtrace tracepoints and provides a demo systemtap script that shows some basic stats collection done using them.

Surafel Temesgen sent in another revision of a patch to implement system versioned temporal tables.

Justin Pryzby sent in two more revisions of a patch to avoid errors in brin summarization, which can happen if an index is reindexed concurrently.

Li Japin sent in a patch to use macros for calculating LWLock offset.

Kyotaro HORIGUCHI sent in another revision of a patch to implement CatCache expiration.

Peter Eisentraut sent in another revision of a patch to add support for writing CREATE FUNCTION and CREATE PROCEDURE statements for language SQL with a function body that conforms to the SQL standard.

Greg Nancarrow sent in another revision of a patch to enable parallel SELECT for "INSERT INTO ... SELECT ..." along with parallel INSERT and/or SELECT for "INSERT INTO ... SELECT ...", where it is safe to do so.

Amit Langote sent in a patch to create foreign key triggers in partitioned tables too in order to allow subsequent commit(s) to make the foreign key related events to be fired in some cases using the parent table triggers instead of those of partitions', and enforce foreign key correctly during cross-partition updates.

Laurenz Albe sent in another revision of a patch to add session statistics to pg_stat_database.

Justin Pryzby sent in another revision of a patch to pg_restore so it parses and runs separately SQL commands, and allow child partitions to be independently restored even if the parent doesn't exist, or has missing/incompatible columns.

Peter Eisentraut sent in another revision of a patch to pause recovery for insufficient parameter settings.

Peter Eisentraut sent in another revision of a patch to add support for abstract Unix-domain sockets, and make the error hint from bind() failure more accurate by showing that hint only when errno == EADDRINUSE.

Matthias van de Meent sent in a patch to Fix CLUSTER progress reporting of number of blocks scanned.

Stephen Frost sent in a patch to replace the somewhat inaccurate "default role" term with the more precise "predefined role."

Erik Rijkers sent in a patch atop the patch to add a tutorial chapter on architecture to fix some typos and do other wordsmithing.

Bharath Rupireddy sent in another revision of a patch to avoid checking INSERT privilege in CTAS and MatView in order to comply with the standard.

Gilles Darold sent in two revisions of a patch to add a hook for plugins to get control of at end of AbortCurrentTransaction, and an extension to enable statement-level rollback based on same.

Krasiyan Andreev sent in another revision of a patch to implement <null treatment> for window functions.

Álvaro Herrera sent in a patch to fix a bug in pageinspect which manifests as messages along the lines of ERROR: invalid memory alloc request size 18446744073709551451 when pages are sufficiently broken. The fix is not to attempt to print the data rather than dying.

Justin Pryzby sent in another revision of a patch to retire pg_standby.

Michaël Paquier sent in two more revisions of a patch to remove currtid() and currtid2(), cleaning up some table AMs in the process.

James Coleman sent in a patch to enforce parallel safety of pathkeys in generate_useful_gather_paths, and ensure that generate_useful_gather_paths doesn't skip unsorted subpaths.

Vigneshwaran C sent in a PoC patch to which would make it possible to get the call stack of a PostgreSQL process by connecting to the server.

Tom Lane sent in a patch to make the "cannot insert" message be more precise about the fact that the reason is that non-default values can never be inserted.

Categories: Informatika

PgBouncer 1.15.0 released

2020, November 21 - 01:00

PgBouncer 1.15.0 has been released. This release contains a variety of enhancements and bug fixes, including more secure authentication failure reporting, less log spamming when monitoring systems probe PgBouncer, and fixed behavior of the global auth_user setting.

See

https://www.pgbouncer.org/2020/11/pgbouncer-1-15-0

for more information, the detailed changelog, and download links.

PgBouncer is a lightweight connection pooler for PostgreSQL.

Categories: Informatika

Pgpool-II 4.1.5, 4.0.12, 3.7.17, 3.6.24 and 3.5.28 are now officially released.

2020, November 19 - 01:00
What is Pgpool-II?

Pgpool-II is a tool to add useful features to PostgreSQL, including:

  • connection pooling
  • load balancing
  • automatic fail over and more.
Minor releases

Pgpool Global Development Group is pleased to announce the availability of following versions of Pgpool-II:

  • 4.1.5
  • 4.0.12
  • 3.7.17
  • 3.6.24
  • 3.5.28

Release notes for versions prior 3.6 are no longer included in the source code.

Please take a look at release notes.

You can download the source code and RPMs.

Categories: Informatika

Global Temporary Table v2.2 released

2020, November 16 - 01:00

Grenoble - November 09th, 2020

PostgreSQL Global Temporary Tables

pgtt is a PostgreSQL extension to create, manage and use Oracle-style Global Temporary Tables.

The main interest of this extension is to reproduce Oracle behavior with GTT when you can not or don't want to rewrite the application code when migrating to PostgreSQL. In all other case best is to rewrite the code to use standard PostgreSQL temporary tables.

This new release v2.2.0 is a port of the extension for PostgreSQL v12 and v13. It works now on all PostgreSQL version from v9.5 to current. It also fixes automatic creation of the underlying temporary table after a rollback.

For the complete list of changes, please checkout the release note on https://github.com/darold/pgtt/blob/master/ChangeLog

Links & Credits

pgtt is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly to gilles@darold.net.

Links
Categories: Informatika

pgAdmin 4 v4.28 Released

2020, November 16 - 01:00

The pgAdmin Development Team is pleased to announce pgAdmin 4 version 4.28. This release of pgAdmin 4 includes 19 bug fixes and new features. For more details please see the release notes.

pgAdmin is the leading Open Source graphical management tool for PostgreSQL. For more information, please see the website.

Notable changes in this release include:

  • Added support to download utility files at the client-side.
  • Added support to rename query tool and debugger tabs title.
  • Added support for dynamic tab size.
  • Added tab title placeholder for Query Tool, View/Edit Data, and Debugger.
  • Added support to compare schemas and databases in schema diff.
  • Ensure that non-superuser should be able to debug the function.
  • Ensure that query history should be listed by date/time in descending order.
  • Ensure that Grant Wizard should include foreign tables.
  • Ensure that search object functionality works with case insensitive string.

Builds for Windows and macOS are available now, along with a Python Wheel, Docker Container, RPM, DEB Package, and source code tarball from the tarball area.

Categories: Informatika

PGroonga 2.2.7 - Make PostgreSQL fast full text search platform for all languages

2020, November 16 - 01:00

Hi,

PGroonga 2.2.7 has been released!

About PGroonga
  • https://pgroonga.github.io/

PGroonga is a PostgreSQL extension that makes PostgreSQL fast full text search platform for all languages! It's released under PostgreSQL license.

There are some PostgreSQL extensions that improves full text search feature of PostgreSQL such as pg_trgm(*1).

  • (*1) https://www.postgresql.org/docs/current/static/pgtrgm.html

pg_trgm doesn't support languages that use non-alphanumerics characters such as Japanese and Chinese.

PGroonga supports all languages, provides rich full text search related features and is very fast. Because PGroonga uses Groonga(*2) that is a full-fledged full text search engine as backend.

  • (*2) https://groonga.org/

See the following benchmark results for performance:

  • Benchmark result for PGroonga, textsearch and pg_trgm with English Wikipedia

    • https://pgroonga.github.io/reference/pgroonga-versus-textsearch-and-pg-trgm.html
  • Benchmark result for PGroonga and pg_bigm with Japanese Wikipedia

    • https://pgroonga.github.io/reference/pgroonga-versus-pg-bigm.html

PGroonga also supports JSON search. You can use each value for condition. You can also perform full text search against all texts in JSON like textsearch in PostgreSQL 10 does.

Users

Here are PGroonga users:

  • Zulip: https://zulipchat.com/

    • Powerful open source group chat
  • https://pgroonga.github.io/users/

Changes

Here are highlights of PGroonga 2.2.7:

  • Support PostgreSQL 13

See the following release note URL for details:

  • https://groonga.org/en/blog/2020/11/10/pgroonga-2.2.7.html
Usage

You can use PGroonga without full text search knowledge. You just create an index and puts a condition into WHERE:

CREATE INDEX index_name ON table USING pgroonga (column); SELECT * FROM table WHERE column &@~ 'PostgreSQL';

You can also use LIKE to use PGroonga. PGroonga provides a feature that performs LIKE with index. LIKE with PGroonga index is faster than LIKE without index. It means that you can improve performance without changing your application that uses the following SQL:

SELECT * FROM table WHERE column LIKE '%PostgreSQL%';

Are you interested in PGroonga? Please install( 4) and try tutorial( 5). You can know all PGroonga features.

  • (*4) https://pgroonga.github.io/install/
  • (*5) https://pgroonga.github.io/tutorial/

You can install PGroonga easily. Because PGroonga provides packages for major platforms. There are binaries for Windows.

Thanks,

Categories: Informatika

pitrery 3.2 released

2020, November 16 - 01:00

Version 3.2 of Pitrery - the PITR made simple software - is available!

New feature
  • Support for PostgreSQL 13
  • Add CI for local tests (see section "Run test locally" in CONTRIBUTING.md).
Bug fixes
  • Abort check if backup_timestamp is not available (#130)
Getting it

Pitrery tarballs are now on GitHub releases and distribution packages are now available on Dalibo Labs YUM and APT repositories. Details are available in the downloads page.

About

Pitrery is a set of Bash scripts to manage PITR backups for PostgreSQL.

It is a Dalibo Labs project maintained by Thibaut Madelaine, Étienne Bersac and Thibaud Walkowiak.

Categories: Informatika

pg_statement_rollback v1.1 released

2020, November 16 - 01:00

Zurich, Switzerland - Nov 12th, 2020

Server side rollback at statement level for PostgreSQL

pg_statement_rollback is a PostgreSQL extension to add server side transaction with rollback at statement level like in Oracle or DB2.

Release v1.1 of pg_statement_rollback was released today. This is a maintenance release to fix the unhandled case of a writing function throwing error that was causing a failure of the extension.

See ChangeLog for a complete list of changes.

Links & Credits

pg_statement_rollback is an open project from LzLabs (https://www.lzlabs.com/). Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools.

Links :

Categories: Informatika

LLVM issues with PostgreSQL YUM repository on CentOS 8

2020, November 13 - 01:00

In the latest 3 minor releases of Red Hat Enterprise Linux 8, Red Hat have broken LLVM compatibility with the previous minor releases (like introducing LLVM 8 with RHEL 8.1, LLVM 9 with RHEL 8.2, and LLVM 10 with RHEL 8.3).

This breaks compatibility with the previous releases, and also affects PostgreSQL updates for the users who installed the llvmjit subpackage. The negative effects are:

RHEL: Users cannot update to the new LLVM until the packages are rebuilt. This issue has been solved already, by updating the build servers to the new minor release immediately, and rebuilding affected packages.

CentOS, which lags behind RHEL, is also significantly affected by this breakage, blocking PostgreSQL minor updates. This is the problem that needs to be solved.

Today, we released a new repo called “pgdg-centos8-sysupdates” for CentOS 8 users. This repo brings in the LLVM and CLANG packages from latest RHEL (of course, rebuilt and signed with our own key), which satisfy the llvmjit dependency. Please note that this is optional, because it may break other packages (if any) which depend on older versions of LLVM and CLANG. This feature is available for PostgreSQL 11 and above.

Users first need to update to the latest repo file (dnf -y update pgdg-redhat-repo) to benefit from this feature, which is 42.0.15 at the time of writing.

Here are the steps to enable this repo:

dnf -qy module disable postgresql llvm-toolset rust-toolset dnf config-manager --set-enabled pgdg-centos8-sysupdates

(Disabling rust-toolset is not a must, however dnf will otherwise throw warnings as it has a dependency to the llvm-toolset module).

When this is done, you can either update or install the llvmjit package normally.

If you have any questions/comments, please either email to pgsql-pkg-yum@lists.postgresql.org, or create an issue at PostgreSQL community RPM issue tracker (requires a community account to access)

Categories: Informatika

PostgreSQL 13.1, 12.5, 11.10, 10.15, 9.6.20, and 9.5.24 Released!

2020, November 12 - 01:00

The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 13.1, 12.5, 11.10, 10.15, 9.6.20, and 9.5.24. This release closes three security vulnerabilities and fixes over 65 bugs reported over the last three months.

Due to the nature of CVE-2020-25695, we advise you to update as soon as possible.

Additionally, this is the second-to-last release of PostgreSQL 9.5. If you are running PostgreSQL 9.5 in a production environment, we suggest that you make plans to upgrade.

For the full list of changes, please review the release notes.

Security Issues CVE-2020-25695: Multiple features escape "security restricted operation" sandbox

Versions Affected: 9.5 - 13. The security team typically does not test unsupported versions, but this problem is quite old.

An attacker having permission to create non-temporary objects in at least one schema can execute arbitrary SQL functions under the identity of a superuser.

While promptly updating PostgreSQL is the best remediation for most users, a user unable to do that can work around the vulnerability by disabling autovacuum and not manually running ANALYZE, CLUSTER, REINDEX, CREATE INDEX, VACUUM FULL, REFRESH MATERIALIZED VIEW, or a restore from output of the pg_dump command. Performance may degrade quickly under this workaround.

VACUUM without the FULL option is safe, and all commands are fine when a trusted user owns the target object.

The PostgreSQL project thanks Etienne Stalmans for reporting this problem.

CVE-2020-25694: Reconnection can downgrade connection security settings

Versions Affected: 9.5 - 13. The security team typically does not test unsupported versions, but this problem is quite old.

Many PostgreSQL-provided client applications have options that create additional database connections. Some of those applications reuse only the basic connection parameters (e.g. host, user, port), dropping others. If this drops a security-relevant parameter (e.g. channel_binding, sslmode, requirepeer, gssencmode), the attacker has an opportunity to complete a MITM attack or observe cleartext transmission.

Affected applications are clusterdb, pg_dump, pg_restore, psql, reindexdb, and vacuumdb. The vulnerability arises only if one invokes an affected client application with a connection string containing a security-relevant parameter.

This also fixes how the \connect command of psql reuses connection parameters, i.e. all non-overridden parameters from a previous connection string now re-used.

The PostgreSQL project thanks Peter Eisentraut for reporting this problem.

CVE-2020-25696: psql's \gset allows overwriting specially treated variables

Versions Affected: 9.5 - 13. The security team typically does not test unsupported versions, but this problem likely arrived with the feature's debut in version 9.3.

The \gset meta-command, which sets psql variables based on query results, does not distinguish variables that control psql behavior. If an interactive psql session uses \gset when querying a compromised server, the attacker can execute arbitrary code as the operating system account running psql. Using \gset with a prefix not found among specially treated variables, e.g. any lowercase string, precludes the attack in an unpatched psql.

The PostgreSQL project thanks Nick Cleaton for reporting this problem.

Bug Fixes and Improvements

This update also fixes over 65 bugs that were reported in the last several months. Some of these issues only affect version 13, but may also apply to other supported versions.

Some of these fixes include:

  • Fix a breakage in the replication protocol by ensuring that two "command completion" events are expected for START_REPLICATION.
  • Ensure fsync is called on the SLRU caches that PostgreSQL maintains. This prevents potential data loss due to an operating system crash.
  • Fix ALTER ROLE usage for users with the BYPASSRLS permission.
  • ALTER TABLE ONLY ... DROP EXPRESSION is disallowed on partitioned tables when there are child tables.
  • Ensure that ALTER TABLE ONLY ... ENABLE/DISABLE TRIGGER does not apply to child tables.
  • Fix for ALTER TABLE ... SET NOT NULL on partitioned tables to avoid a potential deadlock in parallel pg_restore.
  • Fix handling of expressions in CREATE TABLE LIKE with inheritance.
  • DROP INDEX CONCURRENTLY is disallowed on partitioned tables.
  • Allow LOCK TABLE to succeed on a self-referential view instead of throwing an error.
  • Several fixes around statistics collection and progress reporting for REINDEX CONCURRENTLY.
  • Ensure that GENERATED columns are updated when any columns they depend on are updated via a rule or an updatable view.
  • Support hash partitioning with text array columns as partition keys.
  • Allow the jsonpath .datetime() method to accept ISO 8601-format timestamps.
  • During a "smart" shutdown, ensure background processes are not terminated until all foreground client sessions are completed, fixing an issue that broke the processing of parallel queries.
  • Several fixes for the query planner and optimizer.
  • Ensure that data is de-toasted before being inserted into a BRIN index. This could manifest itself with errors like "missing chunk number 0 for toast value NNN". If you have seen a similar error in an existing BRIN index, you should be able to correct it by using REINDEX on the index.
  • Fix the output of EXPLAIN to have the correct XML tag nesting for incremental sort plans.
  • Several fixes for memory leaks, including ones involving RLS policies, using CALL with PL/pgSQL, SIGHUP processing a configuration parameter that cannot be applied without a restart, and an edge-case for index lookup for a partition.
  • libpq can now support arbitrary-length lines in the .pgpass file.
  • On Windows, psql now reads the output of a backtick command in text mode, not binary mode, so it can now properly handle newlines.
  • Fix how pg_dump, pg_restore, clusterdb, reindexdb, and vacuumdb use complex connection-string parameters.
  • When the \connect command of psql reuses connection parameters, ensure that all non-overridden parameters from a previous connection string are also re-used.
  • Ensure that pg_dump collects per-column information about extension configuration tables, avoiding crashes when specifying --inserts.
  • Ensure that parallel pg_restore processes foreign keys referencing partitioned tables in the correct order.
  • Several fixes for contrib/pgcrypto, including a memory leak fix.

This update also contains tzdata release 2020d for for DST law changes in Fiji, Morocco, Palestine, the Canadian Yukon, Macquarie Island, and Casey Station (Antarctica); plus historical corrections for France, Hungary, Monaco, and Palestine.

For the full list of changes available, please review the release notes.

PostgreSQL 9.5 EOL Notice

PostgreSQL 9.5 will stop receiving fixes on February 11, 2021. If you are running PostgreSQL 9.5 in a production environment, we suggest that you make plans to upgrade to a newer, supported version of PostgreSQL. Please see our versioning policy for more information.

Updating

All PostgreSQL update releases are cumulative. As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shutdown PostgreSQL and update its binaries.

Users who have skipped one or more update releases may need to run additional, post-update steps; please see the release notes for earlier versions for details.

For more details, please see the release notes.

NOTE: PostgreSQL 9.5 will stop receiving fixes on February 11, 2021. Please see our versioning policy for more information.

Links
Categories: Informatika

Call for New Member for the PostgreSQL Code of Conduct Committee

2020, November 11 - 01:00

This message is being sent from the PostgreSQL Code of Conduct Committee, with the approval of the Core Team.

The PostgreSQL Community Code of Conduct Committee is searching for a new member to fill a vacant seat.

The term runs through September 30, 2021.

We are seeking people who reflect the diversity of the PostgreSQL community, with the goal to have members from multiple countries and varied demographics.

You can view the current Committee members here:

https://www.postgresql.org/about/policies/coc_committee/

The time commitment for Committee involvement varies, based on internal administrative work and the number of active investigations. We estimate an average of 5 to 10 hours per month, but that could increase if there is an increase in the number of incident reports.

If you are interested, please complete the questionnaire below, and email your responses to the Committee at coc@postgresql.org no later than November 25, 2020, at 11:00 PM AOE. You can check your local time against AOE at https://time.is/Anywhere_on_Earth

The questionnaire:

  • Your name:
  • Current employer:
  • Current country of residence:

(We ask for employer and residence because one of the goals of the Committee is to have representation from a variety of geographical areas. We also want to avoid a concentration of members from one company.)

  1. What interests you about being on the CoC Committee?
  2. Have you been on another CoC Committee, or had a similar role at another organization? (Prior experience is not required, it's just helpful to know everyone's background.)
  3. Why do you think working on the CoC Committee would be a good experience?
  4. What else do you want to tell us about yourself that is helpful for us to know about your potential involvement with the CoC Committee?

Please be sure to send your reply to the CoC Committee email listed above. Thank you.

Categories: Informatika

PostgreSQL Weekly News - November 8, 2020

2020, November 9 - 01:00
PostgreSQL Weekly News - November 8, 2020

Congratulations to new core team members Andres Freund and Jonathan Katz! https://www.postgresql.org/developer/core/

Person of the week: https://postgresql.life/post/elein_mustain/

PostgreSQL Product News

phpPgAdmin 7.13.1, a web-based administration tool for PostgreSQL, released. https://xzilla.net//blog/2020/Nov/phpPgAdmin-7-13-1-released.html

Ajqvue Version 3.3, a java-based UI which supports PostgreSQL, released. http://ajqvue.com

pg_statement_rollback, an extension that adds server side transaction with rollback at statement level, released. https://github.com/lzlabs/pg_statement_rollback/releases/

PostgreSQL Jobs for November

http://archives.postgresql.org/pgsql-jobs/2020-11/

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

Tom Lane pushed:

  • Fix two issues in TOAST decompression. pglz_maximum_compressed_size() potentially underestimated the amount of compressed data required to produce N bytes of decompressed data; this is a fault in commit 11a078cf8. Separately from that, pglz_decompress() failed to protect itself against corrupt compressed data, particularly off == 0 in a match tag. Commit c60e520f6 turned such a situation into an infinite loop, where before it'd just have resulted in garbage output. The combination of these two bugs seems like it may explain bug #16694 from Tom Vijlbrief, though it's impossible to be quite sure without direct inspection of the failing session. (One needs to assume that the pglz_maximum_compressed_size() bug caused us to fail to fetch the second byte of a match tag, and what happened to be there instead was a zero. The reported infinite loop is hard to explain without off == 0, though.) Aside from fixing the bugs, rewrite associated comments for more clarity. Back-patch to v13 where both these commits landed. Discussion: https://postgr.es/m/16694-f107871e499ec114@postgresql.org https://git.postgresql.org/pg/commitdiff/dfc797730fc7a07c0e6bd636ad1a564aecab3161

  • Second thoughts on TOAST decompression. On detecting a corrupted match tag, pglz_decompress() should just summarily return -1. Breaking out of the loop, as I did in dfc797730, doesn't quite guarantee that will happen. Also, we can use unlikely() on that check, just in case it helps. Backpatch to v13, like the previous patch. https://git.postgresql.org/pg/commitdiff/fd2997565c6f66837440dd57f5e52b56aa964d14

  • Rethink the generation rule for fmgroids.h macros. Traditionally, the names of fmgroids.h macros for pg_proc OIDs have been constructed from the prosrc field. But sometimes the same C function underlies multiple pg_proc entries, forcing us to make an arbitrary choice of which OID to reference; the other entries are then not namable via fmgroids.h. Moreover, we could not have macros at all for pg_proc entries that aren't for C-coded functions. Instead, use the proname field, and append the proargtypes field (replacing inter-argument spaces with underscores) if proname is not unique. Special-casing unique entries such as F_OIDEQ removes the need to change a lot of code. Indeed, I can only find two places in the tree that need to be adjusted; while this changes quite a few existing entries in fmgroids.h, few of them are referenced from C code. With this patch, all entries in pg_proc.dat have macros in fmgroids.h. Discussion: https://postgr.es/m/472274.1604258384@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/8e1f37c07aafd4bb7aa6e1e1982010af11f8b5c7

  • Remove special checks for pg_rewrite.ev_qual and ev_action being NULL. make_ruledef() and make_viewdef() were coded to cope with possible null-ness of these columns, but they've been marked BKI_FORCE_NOT_NULL for some time. So there's not really any need to do more than what we do for the other columns of pg_rewrite, i.e. just Assert that we got non-null results. (There is a school of thought that says Asserts aren't the thing to do to check for corrupt data, but surely here is not the place to start if we want such a policy.) Also, remove long-dead-if-indeed-it-ever-wasn't-dead handling of an empty actions list in make_ruledef(). That's an error case and should be treated as such. (DO INSTEAD NOTHING is represented by a CMD_NOTHING Query, not an empty list; cf transformRuleStmt.) Kyotaro Horiguchi, some changes by me Discussion: https://postgr.es/m/CAEudQApoA=tMTic6xEPYP_hsNZ8XtToVThK_0x7D_aFQYowq3w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e1339bfc7a2fd4629e1c3f8f919ddd05b4745e13

  • Fix unportable use of getnameinfo() in pg_hba_file_rules view. fill_hba_line() thought it could get away with passing sizeof(struct sockaddr_storage) rather than the actual addrlen previously returned by getaddrinfo(). While that appears to work on many platforms, it does not work on FreeBSD 11: you get back a failure, which leads to the view showing NULL for the address and netmask columns in all rows. The POSIX spec for getnameinfo() is pretty clearly on FreeBSD's side here: you should pass the actual address length. So it seems plausible that there are other platforms where this coding also fails, and we just hadn't noticed. Also, IMO the fact that getnameinfo() failure leads to a NULL output is pretty bogus in itself. Our pg_getnameinfo_all() wrapper is careful to emit "???" on failure, and we should use that in such cases. NULL should only be emitted in rows that don't have IP addresses. Per bug #16695 from Peter Vandivier. Back-patch to v10 where this code was added. Discussion: https://postgr.es/m/16695-a665558e2f630be7@postgresql.org https://git.postgresql.org/pg/commitdiff/0a4b34031279d938c2e59df8df7159d6c11e39b5

  • Allow users with BYPASSRLS to alter their own passwords. The intention in commit 491c029db was to require superuserness to change the BYPASSRLS property, but the actual effect of the coding in AlterRole() was to require superuserness to change anything at all about a BYPASSRLS role. Other properties of a BYPASSRLS role should be changeable under the same rules as for a normal role, though. Fix that, and also take care of some documentation omissions related to BYPASSRLS and REPLICATION role properties. Tom Lane and Stephen Frost, per bug report from Wolfgang Walther. Back-patch to all supported branches. Discussion: https://postgr.es/m/a5548a9f-89ee-3167-129d-162b5985fcf8@technowledgy.de https://git.postgresql.org/pg/commitdiff/d907bd0543aa63e59653d7345840bed0f8b3a83b

  • Improve error messages around REPLICATION and BYPASSRLS properties. Clarify wording as per suggestion from Wolfgang Walther. No back-patch; this doesn't seem worth thrashing translatable strings in the back branches. Tom Lane and Stephen Frost Discussion: https://postgr.es/m/a5548a9f-89ee-3167-129d-162b5985fcf8@technowledgy.de https://git.postgresql.org/pg/commitdiff/17fb60387ce3fdc2bbb13d9b67bed0e4da77e173

  • Guard against core dump from uninitialized subplan. If the planner erroneously puts a non-parallel-safe SubPlan into a parallelized portion of the query tree, nodeSubplan.c will fail in the worker processes because it finds a null in es_subplanstates, which it's unable to cope with. It seems worth a test-and-elog to make that an error case rather than a core dump case. This probably should have been included in commit 16ebab688, which was responsible for allowing nulls to appear in es_subplanstates to begin with. So, back-patch to v10 where that came in. Discussion: https://postgr.es/m/924226.1604422326@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/92f87182f2c617fd420832972b6d0ae4527301c8

  • Remove useless entries for aggregate functions from fmgrtab.c. Gen_fmgrtab.pl treated aggregate functions the same as other built-in functions, which is wasteful because there is no real need to have entries for them in the fmgr_builtins[] table. Suppressing those entries saves about 3KB in the compiled table on my machine; which is not a lot but it's not nothing either, considering that that table is pretty "hot". The only outside code change needed is that ExecInitWindowAgg() can't be allowed to call fmgr_info_cxt() on a plain aggregate function. But that saves a few cycles anyway. Having done that, the aggregate_dummy() function is unreferenced and might as well be dropped. Using "aggregate_dummy" as the prosrc value for an aggregate is now just a documentation convention not something that matters. There was some discussion of using NULL instead to save a few bytes in pg_proc, but we'd have to remove prosrc's BKI_FORCE_NOT_NULL marking which doesn't seem a great idea. Anyway, it's possible there's client-side code that expects to see "aggregate_dummy" there, so I'm loath to change it without a strong reason. Discussion: https://postgr.es/m/533989.1604263665@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/f21636e5d5b8394ed076e18ddc5f4ba710c69c99

  • Improve our ability to regurgitate SQL-syntax function calls. The SQL spec calls out nonstandard syntax for certain function calls, for example substring() with numeric position info is supposed to be spelled "SUBSTRING(string FROM start FOR count)". We accept many of these things, but up to now would not print them in the same format, instead simplifying down to "substring"(string, start, count). That's long annoyed me because it creates an interoperability problem: we're gratuitously injecting Postgres-specific syntax into what might otherwise be a perfectly spec-compliant view definition. However, the real reason for addressing it right now is to support a planned change in the semantics of EXTRACT() a/k/a date_part(). When we switch that to returning numeric, we'll have the parser translate EXTRACT() to some new function name (might as well be "extract" if you ask me) and then teach ruleutils.c to reverse-list that per SQL spec. In this way existing calls to date_part() will continue to have the old semantics. To implement this, invent a new CoercionForm value COERCE_SQL_SYNTAX, and make the parser insert that rather than COERCE_EXPLICIT_CALL when the input has SQL-spec decoration. (But if the input has the form of a plain function call, continue to mark it COERCE_EXPLICIT_CALL, even if it's calling one of these functions.) Then ruleutils.c recognizes COERCE_SQL_SYNTAX as a cue to emit SQL call syntax. It can know which decoration to emit using hard-wired knowledge about the functions that could be called this way. (While this solution isn't extensible without manual additions, neither is the grammar, so this doesn't seem unmaintainable.) Notice that this solution will reverse-list a function call with SQL decoration only if it was entered that way; so dump-and-reload will not by itself produce any changes in the appearance of views. This requires adding a CoercionForm field to struct FuncCall. (I couldn't resist the temptation to rearrange that struct's field order a tad while I was at it.) FuncCall doesn't appear in stored rules, so that change isn't a reason for a catversion bump, but I did one anyway because the new enum value for CoercionForm fields could confuse old backend code. Possible future work: * Perhaps CoercionForm should now be renamed to DisplayForm, or something like that, to reflect its more general meaning. This'd require touching a couple hundred places, so it's not clear it's worth the code churn. * The SQLValueFunction node type, which was invented partly for the same goal of improving SQL-compatibility of view output, could perhaps be replaced with regular function calls marked with COERCE_SQL_SYNTAX. It's unclear if this would be a net code savings, however. Discussion: https://postgr.es/m/42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu https://git.postgresql.org/pg/commitdiff/40c24bfef92530bd846e111c1742c2a54441c62c

  • Declare lead() and lag() using anycompatible not anyelement. This allows use of a "default" expression that doesn't slavishly match the data column's type. Formerly you got something like "function lag(numeric, integer, integer) does not exist", which is not just unhelpful but actively misleading. The SQL spec suggests that the default should be coerced to the data column's type, but this implementation instead chooses the common supertype, which seems at least as reasonable. (Note: I took the opportunity to run "make reformat-dat-files" on pg_proc.dat, so this commit includes some cosmetic changes to recently-added entries that aren't related to lead/lag.) Vik Fearing Discussion: https://postgr.es/m/77675130-89da-dab1-51dd-492c93dcf5d1@postgresfriends.org https://git.postgresql.org/pg/commitdiff/5c292e6b90433c760a3e15027646c7b94afd0cdd

  • Declare assorted array functions using anycompatible not anyelement. Convert array_append, array_prepend, array_cat, array_position, array_positions, array_remove, array_replace, and width_bucket to use anycompatiblearray. This is a simple extension of commit 5c292e6b9 to hit some other places where there's a pretty obvious gain in usability from doing so. Ideally we'd also modify other functions taking multiple old-style polymorphic arguments. But most of the remainder are tied into one or more operator classes, making any such change a much larger can of worms than I desire to open right now. Discussion: https://postgr.es/m/77675130-89da-dab1-51dd-492c93dcf5d1@postgresfriends.org https://git.postgresql.org/pg/commitdiff/9e38c2bb5093ceb0c04d6315ccd8975bd17add66

  • Remove underflow error in float division with infinite divisor. float4_div and float8_div correctly produced zero for zero divided by infinity, but threw an underflow error for nonzero finite values divided by infinity. This seems wrong; at the very least it's inconsistent with the behavior recently implemented for numeric infinities. Remove the error and allow zero to be returned. This patch also removes a useless isinf() test from the overflow checks in these functions (non-Inf divided by Inf can't produce Inf). Extracted from a larger patch; this seems significant outside the context of geometric operators, so it deserves its own commit. Kyotaro Horiguchi Discussion: https://postgr.es/m/CAGf+fX70rWFOk5cd00uMfa__0yP+vtQg5ck7c2Onb-Yczp0URA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/fac83dbd6fe1ac3d4125bfa39f287f95bffe6cda

  • Don't throw an error for LOCK TABLE on a self-referential view. LOCK TABLE has complained about "infinite recursion" when applied to a self-referential view, ever since we made it recurse into views in v11. However, that breaks pg_dump's new assumption that it's okay to lock every relation. There doesn't seem to be any good reason to throw an error: if we just abandon the recursion, we've still satisfied the requirement of locking every referenced relation. Per bug #16703 from Andrew Bille (via Alexander Lakhin). Discussion: https://postgr.es/m/16703-e348f58aab3cf6cc@postgresql.org https://git.postgresql.org/pg/commitdiff/5b7bfc39726ff9f6c52dd73e337c34e74e0d1f39

  • Revert "pg_dump: Lock all relations, not just plain tables". Revert 403a3d91c, as well as the followup fix 7f4235032, in all branches. We need to think a bit harder about what the behavior of LOCK TABLE on views should be, and there's no time for that before next week's releases. We'll take another crack at this later. Discussion: https://postgr.es/m/16703-e348f58aab3cf6cc@postgresql.org https://git.postgresql.org/pg/commitdiff/d3adaabaf7d555ec8bb1d83c43f72e79f1bf0b7d

  • Revert "Accept relations of any kind in LOCK TABLE". Revert 59ab4ac32, as well as the followup fix 33862cb9c, in all branches. We need to think a bit harder about what the behavior of LOCK TABLE on views should be, and there's no time for that before next week's releases. We'll take another crack at this later. Discussion: https://postgr.es/m/16703-e348f58aab3cf6cc@postgresql.org https://git.postgresql.org/pg/commitdiff/eeda7f6338095701cfe1ba3da37070508efe019e

  • Fix ecpg's mishandling of B'...' and X'...' literals. These were broken in multiple ways: * The xbstart and xhstart lexer actions neglected to set "state_before_str_start" before transitioning to the xb/xh states, thus possibly resulting in "internal error: unreachable state" later. * The test for valid string contents at the end of xb state was flat out wrong, as it accounted incorrectly for the "b" prefix that the xbstart action had injected. Meanwhile, the xh state had no such check at all. * The generated literal value failed to include any quote marks. * The grammar did the wrong thing anyway, typically ignoring the literal value and emitting something else, since BCONST and XCONST tokens were handled randomly differently from SCONST tokens. The first of these problems is evidently an oversight in commit 7f380c59f, but the others seem to be very ancient. The lack of complaints shows that ECPG users aren't using these syntaxes much (although I do vaguely remember one previous complaint). As written, this patch is dependent on 7f380c59f, so it can't go back further than v13. Given the shortage of complaints, I'm not excited about adapting the patch to prior branches. Report and patch by Shenhao Wang (test case adjusted by me) Discussion: https://postgr.es/m/d6402f1bacb74ecba22ef715dbba17fd@G08CNEXMBPEKD06.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/1e3868ab3bef5cfa0f4d44a6937a880be7a3a482

  • Avoid re-using output variables in new ecpg test case. The buildfarm thinks this leads to memory stomps, though annoyingly I can't duplicate that here. The existing code in strings.pgc is doing something that doesn't seem to be sanctioned at all really by the documentation, but I'm disinclined to try to make that nicer right now. Let's just declare some more output variables in hopes of working around it. https://git.postgresql.org/pg/commitdiff/eed4356fad84b0fd6e3caa49c7006f401159ac9a

David Rowley pushed:

  • Allow run-time pruning on nested Append/MergeAppend nodes. Previously we only tagged on the required information to allow the executor to perform run-time partition pruning for Append/MergeAppend nodes belonging to base relations. It was thought that nested Append/MergeAppend nodes were just about always pulled up into the top-level Append/MergeAppend and that making the run-time pruning info for any sub Append/MergeAppend nodes was a waste of time. However, that was likely badly thought through. Some examples of cases we're unable to pullup nested Append/MergeAppends are: 1) Parallel Append nodes with a mix of parallel and non-parallel paths into a Parallel Append. 2) When planning an ordered Append scan a sub-partition which is unordered may require a nested MergeAppend path to ensure sub-partitions don't mix up the order of tuples being fed into the top-level Append. Unfortunately, it was not just as simple as removing the lines in createplan.c which were purposefully not building the run-time pruning info for anything but RELOPT_BASEREL relations. The code in add_paths_to_append_rel() was far too sloppy about which partitioned_rels it included for the Append/MergeAppend paths. The original code there would always assume accumulate_append_subpath() would pull each sub-Append and sub-MergeAppend path into the top-level path. While it does not appear that there were any actual bugs caused by having the additional partitioned table RT indexes recorded, what it did mean is that later in planning, when we built the run-time pruning info that we wasted effort and built PartitionedRelPruneInfos for partitioned tables that we had no subpaths for the executor to run-time prune. Here we tighten that up so that partitioned_rels only ever contains the RT index for partitioned tables which actually have subpaths in the given Append/MergeAppend. We can now Assert that every PartitionedRelPruneInfo has a non-empty present_parts. That should allow us to catch any weird corner cases that have been missed. In passing, it seems there is no longer a good reason to have the AppendPath and MergeAppendPath's partitioned_rel fields a List of IntList. We can simply have a List of Relids instead. This is more compact in memory and faster to add new members to. We still know which is the root level partition as these always have a lower relid than their children. Previously this field was used for more things, but run-time partition pruning now remains the only user of it and it has no need for a List of IntLists. Here we also get rid of the RelOptInfo partitioned_child_rels field. This is what was previously used to (sometimes incorrectly) set the Append/MergeAppend path's partitioned_rels field. That was the only usage of that field, so we can happily just remove it. I also couldn't resist changing some nearby code to make use of the newly added for_each_from macro so we can skip the first element in the list without checking if the current item was the first one on each iteration. A bug report from Andreas Kretschmer prompted all this work, however, after some consideration, I'm not personally classing this as a bug fix. So no backpatch. In Andreas' test case, it just wasn't that clear that there was a nested Append since the top-level Append just had a single sub-path which was pulled up a level, per 8edd0e794. Author: David Rowley Reviewed-by: Amit Langote Discussion: https://postgr.es/m/flat/CAApHDvqSchs%2BubdybcfFaSPB%2B%2BEA7kqMaoqajtP0GtZvzOOR3g%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/a929e17e5a8c9b751b66002c8a89fdebdacfe194

  • Fix unstable partition_prune regression tests. This was broken recently by a929e17e5. I'd failed to remember that parallel tests should have their EXPLAIN output run through the explain_parallel_append function so that the output is stable when parallel workers fail to start. fairywren was first to notice. Reported-by: Michael Paquier Discussion: https://postgr.es/m/20201102062951.GB15770@paquier.xyz https://git.postgresql.org/pg/commitdiff/90d8f1b1826ce076a502a43fe7c88423b46c6349

Amit Kapila pushed:

Michaël Paquier pushed:

Heikki Linnakangas pushed:

Thomas Munro pushed:

Magnus Hagander pushed:

Peter Eisentraut pushed:

Tomáš Vondra pushed:

Fujii Masao pushed:

Peter Geoghegan pushed:

  • Fix nbtree cleanup-only VACUUM stats inaccuracies. Logic for counting heap TIDs from posting list tuples (added by commit 0d861bbb) was faulty. It didn't count any TIDs/index tuples in the event of no callback being set. This meant that we incorrectly counted no index tuples in clean-up only VACUUMs, which could lead to pg_class.reltuples being spuriously set to 0 in affected indexes. To fix, go back to counting items from the page in cases where there is no callback. This approach isn't very accurate, but it works well enough in practice while avoiding the expense of accessing every index tuple during cleanup-only VACUUMs. Author: Peter Geoghegan pg [AT] bowt.ie Reported-By: Jehan-Guillaume de Rorthais jgdr [AT] dalibo.com https://postgr.es/m/20201023174451.69e358f1@firost Backpatch: 13-, where nbtree deduplication was introduced https://git.postgresql.org/pg/commitdiff/48e1291342dd7771cf8c67aa1d7ec1f394b95dd8

  • Fix wal_consistency_checking nbtree bug. wal_consistency_checking indicated an inconsistency in certain cases involving nbtree page deletion. The underlying issue is that there was a minor difference between the page image produced after a REDO routine ran and the corresponding page image following original execution. This harmless inconsistency has been around forever. We more or less expect total consistency among even deleted nbtree pages these days, though, so this won't do anymore. To fix, tweak the REDO routine to match original execution. Oversight in commit f47b5e13. https://git.postgresql.org/pg/commitdiff/efc5dcfd8ad4e1df633025d8a91b64cd44d93f42

  • Improve nbtree README's LP_DEAD section. The description of how LP_DEAD bit setting by index scans works following commit 2ed5b87f was rather unclear. Clean that up a bit. Also refer to LP_DEAD bit setting within _bt_check_unique() at the start of the same section. This mechanism may actually be more important than the generic kill_prior_tuple mechanism that the section focuses on, so it at least deserves to be mentioned in passing. https://git.postgresql.org/pg/commitdiff/5a2f154a2ecaf545000a3ff3cdbadc76ae1df30a

Álvaro Herrera pushed:

Pending Patches

Nikhil Benesch sent in a patch to support negative indexes in the split_part() function, those counting from the end of the array instead of the start.

Justin Pryzby sent in another revision of a patch to refactor CIC to rely on REINDEX CONCURRENTLY, refactor same to allow reindexing all index partitions at once, and make ReindexPartitions() set indisvalid so things that come by later can see they're ready to go.

Magnus Hagander sent in another revision of a patch to remove the obsolete analyze_new_cluster.sh script and things that know about it from pg_upgrade.

Anastasia Lubennikova sent in another revision of a patch to teach COPY FREEZE to set PD_ALL_VISIBLE and visibility map bits.

David G. Johnston sent in another revision of a patch to clarify the fact that signal functions have no feedback.

Heikki Linnakangas sent in four revisions of a patch to split copy.c into copyto.c and copyfrom.c, and further split copyfrom.c into copyfrom.c and copyfromparse.c. This will make working on the usually independent functionalities of the split files more convenient and easier to read.

Álvaro Herrera sent in another revision of a patch to add batch/pipelining support to libpq.

Pavel Stěhule sent in a patch to reduce overhead of execution of CALL statement in no atomic mode from PL/pgSQL.

Magnus Hagander sent in two revisions of a patch to remove the obsolete -O switch for postgres, which once allowed passing options to each server process.

Kyotaro HORIGUCHI sent in two more revisions of a patch to fix a dereference before NULL check in src/backend/storage/ipc/latch.c.

David Rowley sent in two revisions of a patch to reduce the number of special cases to build contrib modules on windows.

Konstantin Knizhnik sent in three more revisions of a patch to add custom compression to libpq.

Fabien COELHO sent in another revision of a patch to pgbench to add an option which delays queries until connections are established.

Thomas Munro and David Rowley traded patches to implement collation versioning.

Jinbao Chen sent in a patch to add a new table am 'tid_visible' to test the visibility of a tid.

Peter Geoghegan sent in another revision of a patch to add delete deduplication to nbtree.

Stephen Frost sent in two more revisions of a patch to use pre-fetching for ANALYZE.

Tomáš Vondra sent in another revision of a patch to use INT64_FORMAT to print int64 variables in sort debug.

Bharath Rupireddy sent in another revision of a patch to use multi Inserts in Create Table As.

Amit Langote sent in another revision of a patch to call BeginDirectModify from ExecInitModifyTable, and initialize result relation information lazily. This work builds infrastructure that will later be used to make writes on foreign tables more efficient.

Vigneshwaran C sent in two more revisions of a patch to improve the connection authorization message for GSS authenticated/encrypted connections by adding a log message to include GSS authentication, encryption, and principal information. This message will help the user to know whether GSS authentication or encryption was used and which GSS principal was used.

Tomáš Vondra sent in three more revisions of a patch to implement BRIN multi-range indexes.

Álvaro Herrera sent in another revision of a patch to implement ALTER TABLE ... DETACH CONCURRENTLY.

Tsutomu Yamada sent in another revision of a patch to add \dX, which lists extended statistics, to psql.

Pavel Borisov sent in two more revisions of a patch to deprecate and replace &lt;^ and &gt;^ operators for points.

Melanie Plageman sent in another revision of a patch to support parallel FULL JOIN and RIGHT JOIN.

Kyotaro HORIGUCHI sent in two more revisions of a patch to use shared memory instead of files for storage in the stats collector.

Ajin Cherian and Peter Smith traded patches to add logical decoding of two-phase transactions.

Kirk Jamison sent in another revision of a patch to make DropRelFileNodeBuffers() more efficient during recovery by avoiding scanning the whole buffer pool when the relation is small enough, or the the total number of blocks to be invalidated is below the threshold of full scanning.

Daniel Gustafsson sent in two more revisions of a patch to support NSS as a TLS backend for libpq.

Takamichi Osumi sent in three more revisions of a patch to implement CREATE OR REPLACE TRIGGER.

Fujii Masao sent in another revision of a patch to use standard SIGHUP and SIGTERM handlers in the autoprewarm process.

Justin Pryzby sent in another revision of a patch to Implement CLUSTER of partitioned table. This requires either specification of a partitioned index on which to cluster, or that an partitioned index was previously set clustered.

Kyotaro HORIGUCHI sent in another revision of a patch to fix NaN handling in some geometric operators and functions.

Michaël Paquier sent in another revision of a patch to rework the SHA2 APIs, switch sha2_openssl.c to use EVP, and make pgcrypto use the in-core resowner facility for EVP.

Yuzuko Hosoya sent in another revision of a patch to fix some infelicities between autovacuum and partitioned tables.

Justin Pryzby sent in another revision of a patch to make pg_ls_* show directories and shared filesets.

Seino Yuki sent in another revision of a patch to enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW.

Kyotaro HORIGUCHI sent in two more revisions of a patch to implement CatCache expiration.

David Pirotte sent in another revision of a patch to add logical decoding messages to pgoutput.

Masahiko Sawada sent in two more revisions of a patch to implement a transaction manager for foreign transactions.

Masahiro Ikeda sent in another revision of a patch to add statistics to the pg_stat_wal view.

Justin Pryzby sent in a patch atop the one for incremental view maintenance patch which fixes some documentation.

Michaël Paquier sent in a patch to refactor the MD5 implementations to be just one, and switch to EVP for OpenSSL.

Justin Pryzby sent in another revision of a patch to clarify the computation of min/max IO and specifically the double use and effect of correlation, avoid re-using the "pages_fetched" variable, and use the correlation statistic in costing bitmap scans as for an index scan.

Sergei Kornilov sent in another revision of a patch to allow some recovery parameters to be changed with reload.

Marina Polyakova sent in two revisions of a patch to fix a bug that manifested as pgbench no longer supporting a large number of client connections on Windows.

Andrey Borodin sent in another revision of a patch to add Sortsupport for sorting GiST build for gist_btree types.

Jürgen Purtz and Erik Rijkers traded patches to add an architecture chapter to the tutorial.

Dilip Kumar sent in another revision of a patch to implement custom table compression methods.

Tomáš Vondra sent in a patch to remove some duplicate code from brin_memtuple_initialize.

Categories: Informatika

phpPgAdmin 7.13.0 Now Available!

2020, November 9 - 01:00

I’m pleased to announce the latest release of phpPgAdmin, version 7.13.0.

This release incorporates the following changes:

  • Add support for Postgres 13
  • Add provisional support for Postgres 14
  • Upgrade Jquery library to 3.4.1 (Nirgal)
  • Allow users to see group owned databases when using “owned only”
  • Fix bug where sorting on selects dumped you to the table screen (MichaMEG)

Note this release drops support for PHP 7.1, and will be the last release to support PHP 7.2. For complete details of changes, please see the HISTORY file and/or commit logs. We hope you find this new release helpful!

phpPgAdmin is a mature web-based administration tool for PostgreSQL, for more information on phpPgAdmin, check out our project page at https://github.com/phppgadmin/phppgadmin/

You can download the release at https://github.com/phppgadmin/phppgadmin/releases/tag/REL_7-13-0

Package checksums:

  • MD5 (phpPgAdmin-7.13.0.tar.bz2) = f4e5e567fb8fae7193fb265b9c7f5b7a
  • MD5 (phpPgAdmin-7.13.0.tar.gz) = 8a38dca6bd0bcdc9481c88f1ac0c0f80
  • MD5 (phpPgAdmin-7.13.0.zip) = 46074830709655d1852c3886017ee58c

shasum 6.01:

  • 165caaf0725563b5b98dce6191e55bfdcb1a8c9f phpPgAdmin-7.13.0.tar.bz2
  • 47620834a8bb169c043f47a3eef6029b4f7169af phpPgAdmin-7.13.0.tar.gz
  • 12f4dfbaa0f866c6e48e1231f44210a0e6f2907f phpPgAdmin-7.13.0.zip
Categories: Informatika

Ajqvue Version 3.3 Released

2020, November 6 - 01:00

The Ajqvue project is pleased to release v3.3 to the public. The
release brings updates to the included File/Memory database
libraries. Modifications to image saves for sizing. Added was
support for PostgreSQL 13 along with data types money,
smallserial, macaddr8, line, and uuid. Precision is now supported
for time and timestamp types for those databases that support it.

Convert database queries to a local file/memory H2, HSQL, Derby
or SQLite database with the DB To File/Memory DB plugin.
Github DB To File/Memory DB Plugin

Dana M. Proctor
Ajqvue Project Manager

Ajqvue Website
Ajqvue Github

Ajqvue is an open source Java GUI build for users in accessing,
plotting, and analyzing data in several mainstream databases.
The application is built with a included plugin framework for
adding functionality, quickly.

Categories: Informatika

PostgreSQL Weekly News - November 1, 2020

2020, November 2 - 01:00
PostgreSQL Weekly News - November 1, 2020

The next Commitfest for PostgreSQL 14 has begun. If you are the author of a patch, please make sure to follow up on the reviews so your contribution can get ready to be committed.

PostgreSQL Product News

pgbitmap 0.93, a space-optimised, non-sparse, bitmap type, released. https://pgxn.org/dist/pgbitmap/

Pgpool-II 4.2 beta1 https://www.pgpool.net/docs/42/en/html/release-4-2-0.html

pglogical 2.3.3, a logical-WAL-based replication system for PostgreSQL, released. https://www.2ndquadrant.com/en/resources/pglogical/release-notes/

pg_activity 1.6.2, a top-like application for PostgreSQL server activity monitoring, released. https://github.com/dalibo/pg_activity/releases/tag/v1.6.2

pg_dumpbinary 2.3, a program used to dump a PostgreSQL database in binary format, released. https://github.com/lzlabs/pg_dumpbinary/releases/

pgCenter 0.6.6, a command-line admin tool for observing and troubleshooting PostgreSQL, released. https://github.com/lesovsky/pgcenter/releases/tag/v0.6.6

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

David Rowley pushed:

  • Prevent overly large and NaN row estimates in relations. Given a query with enough joins, it was possible that the query planner, after multiplying the row estimates with the join selectivity that the estimated number of rows would exceed the limits of the double data type and become infinite. To give an indication on how extreme a case is required to hit this, the particular example case reported required 379 joins to a table without any statistics, which resulted in the 1.0/DEFAULT_NUM_DISTINCT being used for the join selectivity. This eventually caused the row estimates to go infinite and resulted in an assert failure in initial_cost_mergejoin() where the infinite row estimated was multiplied by an outerstartsel of 0.0 resulting in NaN. The failing assert verified that NaN <= Inf, which is false. To get around this we use clamp_row_est() to cap row estimates at a maximum of 1e100. This value is thought to be low enough that costs derived from it would remain within the bounds of what the double type can represent. Aside from fixing the failing Assert, this also has the added benefit of making it so add_path() will still receive proper numerical values as costs which will allow it to make more sane choices when determining the cheaper path in extreme cases such as the one described above. Additionally, we also get rid of the isnan() checks in the join costing functions. The actual case which originally triggered those checks to be added in the first place never made it to the mailing lists. It seems likely that the new code being added to clamp_row_est() will result in those becoming checks redundant, so just remove them. The fairly harmless assert failure problem does also exist in the backbranches, however, a more minimalistic fix will be applied there. Reported-by: Onder Kalaci Reviewed-by: Tom Lane Discussion: https://postgr.es/m/DM6PR21MB1211FF360183BCA901B27F04D80B0@DM6PR21MB1211.namprd21.prod.outlook.com https://git.postgresql.org/pg/commitdiff/a90c950fc7fd8796daa8c7948e7046bceb272894

  • Optimize a few list_delete_ptr calls. There is a handful of places where we called list_delete_ptr() to remove some element from a List. In many of these places we know, or with very little additional effort know the index of the ListCell that we need to remove. Here we change all of those places to instead either use one of; list_delete_nth_cell(), foreach_delete_current() or list_delete_last(). Each of these saves from having to iterate over the list to search for the element to remove by its pointer value. There are some small performance gains to be had by doing this, but in the general case, none of these lists are likely to be very large, so the lookup was probably never that expensive anyway. However, some of the calls are in fairly hot code paths, e.g process_equivalence(). So any small gains there are useful. Author: Zhijie Hou and David Rowley Discussion: https://postgr.es/m/b3517353ec7c4f87aa560678fbb1034b@G08CNEXMBPEKD05.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/e7c2b95d37a2b9c01367f7ccc55703555b39c81c

  • Fix incorrect parameter name in a function header comment. Author: Zhijie Hou Discussion: https://postgr.es/m/14cd74ea00204cc8a7ea5d738ac82cd1@G08CNEXMBPEKD05.g08.fujitsu.local Backpatch-through: 12, where the mistake was introduced https://git.postgresql.org/pg/commitdiff/e83c9f913c6197586af8ac53c1d3652db15a3c91

Michaël Paquier pushed:

  • Fix potential memory leak in pgcrypto. When allocating a EVP context, it would have been possible to leak some memory allocated directly by OpenSSL, that PostgreSQL lost track of if the initialization of the context allocated failed. The cleanup can be done with EVP_MD_CTX_destroy(). Note that EVP APIs exist since OpenSSL 0.9.7 and we have in the tree equivalent implementations for older versions since ce9b75d (code removed with 9b7cd59a as of 10~). However, in 9.5 and 9.6, the existing code makes use of EVP_MD_CTX_destroy() and EVP_MD_CTX_create() without an equivalent implementation when building the tree with OpenSSL 0.9.6 or older, meaning that this code is in reality broken with such versions since it got introduced in e2838c5. As we have heard no complains about that, it does not seem worth bothering with in 9.5 and 9.6, so I have left that out for simplicity. Author: Michael Paquier Discussion: https://postgr.es/m/20201015072212.GC2305@paquier.xyz Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/ca2a12c935f75fb56c3b14527d6f2ff6f549ea85

  • Review format of code generated by PerfectHash.pm. 80f8eb7 has added to the normalization quick check headers some code generated by PerfectHash.pm that is incompatible with the settings of gitattributes for this repository, as whitespaces followed a set of tabs for the first element of a line in the table. Instead of adding a new exception to gitattributes, rework the format generated so as a right padding with spaces is used instead of a left padding. This keeps the table generated in a readable shape with its set of columns, making unnecessary an update of gitattributes. Reported-by: Peter Eisentraut Author: John Naylor Discussion: https://postgr.es/m/d601b3b5-a3c7-5457-2f84-3d6513d690fc@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/19ae53c92d5f5bdfb971d560a562e84c5f65c8b0

  • Improve performance of Unicode {de,re}composition in the backend. This replaces the existing binary search with two perfect hash functions for the composition and the decomposition in the backend code, at the cost of slightly-larger binaries there (35kB in libpgcommon_srv.a). Per the measurements done, this improves the speed of the recomposition and decomposition by up to 30~40 times for the NFC and NFKC conversions, while all other operations get at least 40% faster. This is not as "good" as what libicu has, but it closes the gap a lot as per the feedback from Daniel Verite. The decomposition table remains the same, getting used for the binary search in the frontend code, where we care more about the size of the libraries like libpq over performance as this gets involved only in code paths related to the SCRAM authentication. In consequence, note that the perfect hash function for the recomposition needs to use a new inverse lookup array back to to the existing decomposition table. The size of all frontend deliverables remains unchanged, even with --enable-debug, including libpq. Author: John Naylor Reviewed-by: Michael Paquier, Tom Lane Discussion: https://postgr.es/m/CAFBsxsHUuMFCt6-pU+oG-F1==CmEp8wR+O+bRouXWu6i8kXuqA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/783f0cc64dcc05e3d112a06b1cd181e5a1ca9099

  • Add tab completion for ALTER TABLE .. FORCE ROW LEVEL SECURITY in psql. This completes both the FORCE and NO FORCE options, NO INHERIT needing a small adjustment. Author: Li Japin Discussion: https://postgr.es/m/15B10F9F-5847-4F5E-BD66-8E25AA473C95@hotmail.com https://git.postgresql.org/pg/commitdiff/0b46e82c06b0c4b0dc6a94a890d23945ebf720fd

  • Fix issue with --enable-coverage and the new unicode {de,re}composition code. genhtml has been generating the following warning with this new code: WARNING: function data mismatch at /path/src/common/unicode_norm.c:102 HTML coverage reports care about the uniqueness of functions defined in source files, ignoring any assumptions around CFLAGS. 783f0cc introduced a duplicated definition of get_code_entry(), leading to a warning and potentially some incorrect data generated in the reports. This refactors the code so as the code has only one function declaration, fixing the warning. Oversight in 783f0cc. Reported-by: Tom Lane Author: Michael Paquier Reviewed-by: Tom Lane Discussion: https://postgr.es/m/207789.1603469272@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/2771fcee18be0b86d2e008add20f73d175e06e90

  • Extend PageIsVerified() to handle more custom options. This is useful for checks of relation pages without having to load the pages into the shared buffers, and two cases can make use of that: page verification in base backups and the online, lock-safe, flavor. Compatibility is kept with past versions using a macro that calls the new extended routine with the set of options compatible with the original version. Extracted from a larger patch by the same author. Author: Anastasia Lubennikova Reviewed-by: Michael Paquier, Julien Rouhaud Discussion: https://postgr.es/m/608f3476-0598-2514-2c03-e05c7d2b0cbd@postgrespro.ru https://git.postgresql.org/pg/commitdiff/d401c5769ef6aeef0a28c147f3fb5afedcd59984

  • Add CheckBuffer() to check on-disk pages without shared buffer loading. CheckBuffer() is designed to be a concurrent-safe function able to run sanity checks on a relation page without loading it into the shared buffers. The operation is done using a lock on the partition involved in the shared buffer mapping hashtable and an I/O lock for the buffer itself, preventing the risk of false positives due to any concurrent activity. The primary use of this function is the detection of on-disk corruptions for relation pages. If a page is found in shared buffers, the on-disk page is checked if not dirty (a follow-up checkpoint would flush a valid version of the page if dirty anyway), as it could be possible that a page was present for a long time in shared buffers with its on-disk version corrupted. Such a scenario could lead to a corrupted cluster if a host is plugged off for example. If the page is not found in shared buffers, its on-disk state is checked. PageIsVerifiedExtended() is used to apply the same sanity checks as when a page gets loaded into shared buffers. This function will be used by an upcoming patch able to check the state of on-disk relation pages using a SQL function. Author: Julien Rouhaud, Michael Paquier Reviewed-by: Masahiko Sawada Discussion: https://postgr.es/m/CAOBaU_aVvMjQn=ge5qPiJOPMmOj5=ii3st5Q0Y+WuLML5sR17w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c780a7a90a8e7b074405ea2007e34f94e227e695

  • Add pg_relation_check_pages() to check on-disk pages of a relation. This makes use of CheckBuffer() introduced in c780a7a, adding a SQL wrapper able to do checks for all the pages of a relation. By default, all the fork types of a relation are checked, and it is possible to check only a given relation fork. Note that if the relation given in input has no physical storage or is temporary, then no errors are generated, allowing full-database checks when coupled with a simple scan of pg_class for example. This is not limited to clusters with data checksums enabled, as clusters without data checksums can still apply checks on pages using the page headers or for the case of a page full of zeros. This function returns a set of tuples consisting of: - The physical file where a broken page has been detected (without the segment number as that can be AM-dependent, which can be guessed from the block number for heap). A relative path from PGPATH is used. - The block number of the broken page. By default, only superusers have an access to this function but execution rights can be granted to other users. The feature introduced here is still minimal, and more improvements could be done, like: - Addition of a start and end block number to run checks on a range of blocks, which would apply only if one fork type is checked. - Addition of some progress reporting.

  • Throttling, with configuration parameters in function input or potentially some cost-based GUCs. Regression tests are added for positive cases in the main regression test suite, and TAP tests are added for cases involving the emulation of page corruptions. Bump catalog version. Author: Julien Rouhaud, Michael Paquier Reviewed-by: Masahiko Sawada, Justin Pryzby Discussion: https://postgr.es/m/CAOBaU_aVvMjQn=ge5qPiJOPMmOj5=ii3st5Q0Y+WuLML5sR17w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f2b883969557f4572cdfa87e1a40083d2b1272e7

  • Use correct GetDatum() in pg_relation_check_pages(). UInt32GetDatum() was getting used, while the result needs Int64GetDatum(). Oversight in f2b8839. Per buildfarm member florican. Discussion: https://postgr.es/m/1226629.1603859189@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ce7f772c5e6066e0bbafea5759e652c9757c8e6b

  • Fix incorrect placement of pfree() in pg_relation_check_pages(). This would cause the function to crash when more than one page is considered as broken and reported in the SRF. Reported-by: Noriyoshi Shinoda Discussion: https://postgr.es/m/TU4PR8401MB11523D42C315AAF822E74275EE170@TU4PR8401MB1152.NAMPRD84.PROD.OUTLOOK.COM https://git.postgresql.org/pg/commitdiff/60a51c6b32960822d3987ea7d2816c65bdbcb314

  • Add error code for encryption failure in pgcrypto. PXE_DECRYPT_FAILED exists already for decryption errors, and an equivalent for encryption did not exist. There is one code path that deals with such failures for OpenSSL but it used PXE_ERR_GENERIC, which was inconsistent. This switches this code path to use the new error PXE_ENCRYPT_FAILED instead of PXE_ERR_GENERIC, making the code used for encryption more consistent with the decryption. Author: Daniel Gustafsson Discussion: https://postgr.es/m/03049139-CB7A-436E-B71B-42696D3E2EF7@yesql.se https://git.postgresql.org/pg/commitdiff/aecaa04418f39c32adb3dbf91c4aa7f6e175f01c

  • Preserve index data in pg_statistic across REINDEX CONCURRENTLY. Statistics associated to an index got lost after running REINDEX CONCURRENTLY, while the non-concurrent case preserves these correctly. The concurrent and non-concurrent operations need to be consistent for the end-user, and missing statistics would force to wait for a new analyze to happen, which could take some time depending on the activity of the existing autovacuum workers. This issue is fixed by copying any existing entries in pg_statistic associated to the old index to the new one. Note that this copy is already done with the data of the index in the stats collector. Reported-by: Fabrízio de Royes Mello Author: Michael Paquier, Fabrízio de Royes Mello Reviewed-by: Justin Pryzby Discussion: https://postgr.es/m/CAFcNs+qpFPmiHd1oTXvcPdvAHicJDA9qBUSujgAhUMJyUMb+SA@mail.gmail.com Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/b17ff07aa3eb142d2cde2ea00e4a4e8f63686f96

Amit Kapila pushed:

Peter Eisentraut pushed:

Heikki Linnakangas pushed:

Magnus Hagander pushed:

Tom Lane pushed:

  • Fix list-munging bug that broke SQL function result coercions. Since commit 913bbd88d, check_sql_fn_retval() can either insert type coercion steps in-line in the Query that produces the SQL function's results, or generate a new top-level Query to perform the coercions, if modifying the Query's output in-place wouldn't be safe. However, it appears that the latter case has never actually worked, because the code tried to inject the new Query back into the query list it was passed ... which is not the list that will be used for later processing when we execute the SQL function "normally" (without inlining it). So we ended up with no coercion happening at run-time, leading to wrong results or crashes depending on the datatypes involved. While the regression tests look like they cover this area well enough, through a huge bit of bad luck all the test cases that exercise the separate-Query path were checking either inline-able cases (which accidentally didn't have the bug) or cases that are no-ops at runtime (e.g., varchar to text), so that the failure to perform the coercion wasn't obvious. The fact that the cases that don't work weren't allowed at all before v13 probably contributed to not noticing the problem sooner, too. To fix, get rid of the separate "flat" list of Query nodes and instead pass the real two-level list that is going to be used later. I chose to make the same change in check_sql_fn_statements(), although that has no actual bug, just so that we don't need that data structure at all. This is an API change, as evidenced by the adjustments needed to callers outside functions.c. That's a bit scary to be doing in a released branch, but so far as I can tell from a quick search, there are no outside callers of these functions (and they are sufficiently specific to our semantics for SQL-language functions that it's not apparent why any extension would need to call them). In any case, v13 already changed the API of check_sql_fn_retval() compared to prior branches. Per report from pinker. Back-patch to v13 where this code came in. Discussion: https://postgr.es/m/1603050466566-0.post@n3.nabble.com https://git.postgresql.org/pg/commitdiff/c8ab9701791e22f7a8e1badf362654db179c9703

  • Fix connection string handling in src/bin/scripts/ programs. When told to process all databases, clusterdb, reindexdb, and vacuumdb would reconnect by replacing their --maintenance-db parameter with the name of the target database. If that parameter is a connstring (which has been allowed for a long time, though we failed to document that before this patch), we'd lose any other options it might specify, for example SSL or GSS parameters, possibly resulting in failure to connect. Thus, this is the same bug as commit a45bc8a4f fixed in pg_dump and pg_restore. We can fix it in the same way, by using libpq's rules for handling multiple "dbname" parameters to add the target database name separately. I chose to apply the same refactoring approach as in that patch, with a struct to handle the command line parameters that need to be passed through to connectDatabase. (Maybe someday we can unify the very similar functions here and in pg_dump/pg_restore.) Per Peter Eisentraut's comments on bug #16604. Back-patch to all supported branches. Discussion: https://postgr.es/m/16604-933f4b8791227b15@postgresql.org https://git.postgresql.org/pg/commitdiff/8e5793ab60bba65ffaa0f2237b39c9580d8972c7

  • Remove the option to build thread_test.c outside configure. Theoretically one could go into src/test/thread and build/run this program there. In practice, that hasn't worked since 96bf88d52, and probably much longer on some platforms (likely including just the sort of hoary leftovers where this test might be of interest). While it wouldn't be too hard to repair the breakage, the fact that nobody has noticed for two years shows that there is zero usefulness in maintaining this build pathway. Let's get rid of it and decree that thread_test.c is *only* meant to be built/used in configure. Given that decision, it makes sense to put thread_test.c under config/ and get rid of src/test/thread altogether, so that's what I did. In passing, update src/test/README, which had been ignored by some not-so-recent additions of subdirectories. Discussion: https://postgr.es/m/227659.1603041612@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/8a2121185b26d7eb439860c7a96a08c0aa3c8508

  • Fix connection string handling in psql's \connect command. psql's \connect claims to be able to re-use previous connection parameters, but in fact it only re-uses the database name, user name, host name (and possibly hostaddr, depending on version), and port. This is problematic for assorted use cases. Notably, pg_dump[all] emits "\connect databasename" commands which we would like to have re-use all other parameters. If such a script is loaded in a psql run that initially had "-d connstring" with some non-default parameters, those other parameters would be lost, potentially causing connection failure. (Thus, this is the same kind of bug addressed in commits a45bc8a4f and 8e5793ab6, although the details are much different.) To fix, redesign do_connect() so that it pulls out all properties of the old PGconn using PQconninfo(), and then replaces individual properties in that array. In the case where we don't wish to re-use anything, get libpq's default settings using PQconndefaults() and replace entries in that, so that we don't need different code paths for the two cases. This does result in an additional behavioral change for cases where the original connection parameters allowed multiple hosts, say "psql -h host1,host2", and the \connect request allows re-use of the host setting. Because the previous coding relied on PQhost(), it would only permit reconnection to the same host originally selected. Although one can think of scenarios where that's a good thing, there are others where it is not. Moreover, that behavior doesn't seem to meet the principle of least surprise, nor was it documented; nor is it even clear it was intended, since that coding long pre-dates the addition of multi-host support to libpq. Hence, this patch is content to drop it and re-use the host list as given. Per Peter Eisentraut's comments on bug #16604. Back-patch to all supported branches. Discussion: https://postgr.es/m/16604-933f4b8791227b15@postgresql.org https://git.postgresql.org/pg/commitdiff/85c54287af56fe351b53913ea2b81e9d6145f964

  • Clean up some unpleasant behaviors in psql's \connect command. The check for whether to complain about not having an old connection to get parameters from was seriously out of date: it had not been rethought when we invented connstrings, nor when we invented the -reuse-previous option. Replace it with a check that throws an error if reuse-previous is active and we lack an old connection to reuse. While that doesn't move the goalposts very far in terms of easing reconnection after a server crash, at least it's consistent. If the user specifies a connstring plus additional parameters (which is invalid per the documentation), the extra parameters were silently ignored. That seems like it could be really confusing, so let's throw a syntax error instead. Teach the connstring code path to re-use the old connection's password in the same cases as the old-style-syntax code path would, ie if we are reusing parameters and the values of username, host/hostaddr, and port are not being changed. Document this behavior, too, since it was unmentioned before. Also simplify the implementation a bit, giving rise to two new and useful properties: if there's a "password=xxx" in the connstring, we'll use it not ignore it, and by default (i.e., except with --no-password) we will prompt for a password if the re-used password or connstring password doesn't work. The previous code just failed if the re-used password didn't work. Given the paucity of field complaints about these issues, I don't think that they rise to the level of back-patchable bug fixes, and in any case they might represent undesirable behavior changes in minor releases. So no back-patch. Discussion: https://postgr.es/m/235210.1603321144@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/94929f1cf6cb3ea070d0919d1303379b525a72a3

  • Add documentation and tests for quote marks in ECPG literal queries. ECPG's PREPARE ... FROM and EXECUTE IMMEDIATE can optionally take the target query as a simple literal, rather than the more usual string-variable reference. This was previously documented as being a C string literal, but that's a lie in one critical respect: you can't write a data double quote as \" in such literals. That's because the lexer is in SQL mode at this point, so it'll parse double-quoted strings as SQL identifiers, within which backslash is not special, so \" ends the literal. I looked into making this work as documented, but getting the lexer to switch behaviors at just the right point is somewhere between very difficult and impossible. It's not really worth the trouble, because these cases are next to useless: if you have a fixed SQL statement to execute or prepare, you might as well write it as a direct EXEC SQL, saving the messiness of converting it into a string literal and gaining the opportunity for compile-time SQL syntax checking. Instead, let's just document (and test) the workaround of writing a double quote as an octal escape (\042) in such cases. There's no code behavioral change here, so in principle this could be back-patched, but it's such a niche case I doubt it's worth the trouble. Per report from 1250kv. Discussion: https://postgr.es/m/673825.1603223178@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/c16a1bbcf498f0aa053a3e55008f57d7f67357dd

  • Avoid premature de-doubling of quote marks in ECPG strings. If you write the literal 'abc''def' in an EXEC SQL command, that will come out the other end as 'abc'def', triggering a syntax error in the backend. Likewise, "abc""def" is reduced to "abc"def" which is wrong syntax for a quoted identifier. The cause is that the lexer thinks it should emit just one quote mark, whereas what it really should do is keep the string as-is. Add some docs and test cases, too. Although this seems clearly a bug, I fear users wouldn't appreciate changing it in minor releases. Some may well be working around it by applying an extra doubling of affected quotes, as for example sql/dyntest.pgc has been doing. Per investigation of a report from 1250kv, although this isn't exactly what he/she was on about. Discussion: https://postgr.es/m/673825.1603223178@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/3dfb1942d9b8748b93094a430289e2f7f3b3ae0d

  • Sync our copy of the timezone library with IANA release tzcode2020d. There's no functional change at all here, but I'm curious to see whether this change successfully shuts up Coverity's warning about a useless strcmp(), which appeared with the previous update. Discussion: http://mm.icann.org/pipermail/tz/2020-October/029370.html https://git.postgresql.org/pg/commitdiff/c5054da0d7ee59a93ca6d7f7fc669ef0210dbad3

  • Update time zone data files to tzdata release 2020d. DST law changes in Palestine, with a whopping 120 hours' notice. Also some historical corrections for Palestine. https://git.postgresql.org/pg/commitdiff/7d6d6bce43c60bb7b77237e2cc6ab845646b911f

  • Fix broken XML formatting in EXPLAIN output for incremental sorts. The ExplainCloseGroup arguments for incremental sort usage data didn't match the corresponding ExplainOpenGroup. This only matters for XML-format output, which is probably why we'd not noticed. Daniel Gustafsson, per bug #16683 from Frits Jalvingh Discussion: https://postgr.es/m/16683-8005033324ad34e9@postgresql.org https://git.postgresql.org/pg/commitdiff/87a174c0e77eed0bec7d53ef6d470d60335f6444

  • Fix portability issues in new amcheck test. The tests added by commit 866e24d47 failed on big-endian machines due to lack of attention to endianness considerations. Fix that. While here, improve a few small cosmetic things, such as running it through perltidy. Mark Dilger Discussion: https://postgr.es/m/30B8E99A-2D9C-48D4-A55C-741C9D5F1563@enterprisedb.com https://git.postgresql.org/pg/commitdiff/860593ec3bd15e8969effdfcb5cbd98c561dd722

  • Allow psql to re-use connection parameters after a connection loss. Instead of immediately PQfinish'ing a dead connection, save it aside so that we can still extract its parameters for \connect attempts. (This works because PQconninfo doesn't care whether the PGconn is in CONNECTION_BAD state.) This allows developers to reconnect with just \c after a database crash and restart. It's tempting to use the same approach instead of closing the old connection after a failed non-interactive \connect command. However, that would not be very safe: consider a script containing \c db1 user1 live_server \c db2 user2 dead_server \c db3 The script would be expecting to connect to db3 at dead_server, but if we re-use parameters from the first connection then it might successfully connect to db3 at live_server. This'd defeat the goal of not letting a script accidentally execute commands against the wrong database. Discussion: https://postgr.es/m/38464.1603394584@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/1b62d0fb3e50ede570d0d4e4a2be69d5645b48a7

  • Fix more portability issues in new amcheck code. verify_heapam() wasn't being careful to sanity-check tuple line pointers before using them, resulting in SIGBUS on alignment-picky architectures. Fix that, add some more test coverage. Mark Dilger, some tweaking by me Discussion: https://postgr.es/m/30B8E99A-2D9C-48D4-A55C-741C9D5F1563@enterprisedb.com https://git.postgresql.org/pg/commitdiff/321633e17b07968e68ca5341429e2c8bbf15c331

  • Fix ancient bug in ecpg's pthread_once() emulation for Windows. We must not set the "done" flag until after we've executed the initialization function. Otherwise, other threads can fall through the initial unlocked test before initialization is really complete. This has been seen to cause rare failures of ecpg's thread/descriptor test, and it could presumably cause other sorts of misbehavior in threaded ECPG-using applications, since ecpglib relies on pthread_once() in several places. Diagnosis and patch by me, based on investigation by Alexander Lakhin. Back-patch to all supported branches (the bug dates to 2007). Discussion: https://postgr.es/m/16685-d6cd241872c101d3@postgresql.org https://git.postgresql.org/pg/commitdiff/21d36747d4fafe16539a0c55ebb91a01e4053e3c

  • Fix corner case for a BEFORE ROW UPDATE trigger returning OLD. If the old row has any "missing" attributes that are supposed to be retrieved from an associated tuple descriptor, the wrong things happened because the trigger result is shoved directly into an executor slot that lacks the missing-attribute data. Notably, CHECK-constraint verification would incorrectly see those columns as NULL, and so would RETURNING-list evaluation. Band-aid around this by forcibly expanding the tuple before passing it to the trigger function. (IMO it was a fundamental misdesign to put the missing-attribute data into tuple constraints, which so much of the system considers to be optional. But we're probably stuck with that now, and will have to continue to apply band-aids as we find other places with similar issues.) Back-patch to v12. v11 would also have the issue, except that commit 920311ab1 already applied a similar band-aid. That forced expansion in more cases than seem really necessary, though, so this isn't a directly equivalent fix. Amit Langote, with some cosmetic changes by me Discussion: https://postgr.es/m/16644-5da7ef98a7ac4545@postgresql.org https://git.postgresql.org/pg/commitdiff/ba9f18abd3650e385e9a35df7145a7c38af17e92

  • In INSERT/UPDATE, use the table's real tuple descriptor as target. Previously, ExecInitModifyTable relied on ExecInitJunkFilter, and thence ExecCleanTypeFromTL, to build the target descriptor from the query tlist. While we just checked (in ExecCheckPlanOutput) that the tlist produces compatible output, this is not a great substitute for the relation's actual tuple descriptor that's available from the relcache. For one thing, dropped columns will not be correctly marked attisdropped; it's a bit surprising that we've gotten away with that this long. But the real reason for being concerned with this is that using the table's descriptor means that the slot will have correct attrmissing data, allowing us to revert the klugy fix of commit ba9f18abd. (This commit undoes that one's changes in trigger.c, but keeps the new test case.) Thus we can solve the bogus-trigger-tuple problem with fewer cycles rather than more. No back-patch, since this doesn't fix any additional bug, and it seems somewhat more likely to have unforeseen side effects than ba9f18abd's narrow fix. Discussion: https://postgr.es/m/16644-5da7ef98a7ac4545@postgresql.org https://git.postgresql.org/pg/commitdiff/20d3fe9009ddbbbb3da3a2da298f922054b43f8c

  • Doc: improve explanation of how to use our code coverage infrastructure. The reference to running "make coverage" in a subdirectory was a bit obscure, so clarify what happens when you do that. Do a little desultory copy-editing, too. Per a question from Peter Smith. Discussion: https://postgr.es/m/CAHut+Pu0r3AjRSyu5E0v2-zRj8r24OSrkWs3fEBxOuaw1i8DKA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/8d132b2850d4c95ed5666a09b295dea645bbc884

  • Fix foreign-key selectivity estimation in the presence of constants. get_foreign_key_join_selectivity() looks for join clauses that equate the two sides of the FK constraint. However, if we have a query like "WHERE fktab.a = pktab.a and fktab.a = 1", it won't find any such join clause, because equivclass.c replaces the given clauses with "fktab.a = 1 and pktab.a = 1", which can be enforced at the scan level, leaving nothing to be done for column "a" at the join level. We can fix that expectation without much trouble, but then a new problem arises: applying the foreign-key-based selectivity rule produces a rowcount underestimate, because we're effectively double-counting the selectivity of the "fktab.a = 1" clause. So we have to cancel that selectivity out of the estimate. To fix, refactor process_implied_equality() so that it can pass back the new RestrictInfo to its callers in equivclass.c, allowing the generated "fktab.a = 1" clause to be saved in the EquivalenceClass's ec_derives list. Then it's not much trouble to dig out the relevant RestrictInfo when we need to adjust an FK selectivity estimate. (While at it, we can also remove the expensive use of initialize_mergeclause_eclasses() to set up the new RestrictInfo's left_ec and right_ec pointers. The equivclass.c code can set those basically for free.) This seems like clearly a bug fix, but I'm hesitant to back-patch it, first because there's some API/ABI risk for extensions and second because we're usually loath to destabilize plan choices in stable branches. Per report from Sigrid Ehrenreich. Discussion: https://postgr.es/m/1019549.1603770457@sss.pgh.pa.us Discussion: https://postgr.es/m/AM6PR02MB5287A0ADD936C1FA80973E72AB190@AM6PR02MB5287.eurprd02.prod.outlook.com https://git.postgresql.org/pg/commitdiff/ad1c36b0709e47cdb3cc4abd6c939fe64279b63f

  • Don't use custom OID symbols in pg_proc.dat. We have a perfectly good convention for OID macros for built-in functions already, so making custom symbols is just introducing unnecessary deviation from the convention. Remove the one case that had snuck in, and add an error check in genbki.pl to discourage future instances. Although this touches pg_proc.dat, there's no need for a catversion bump since the actual catalog data isn't changed. John Naylor Discussion: https://postgr.es/m/CAFBsxsHpCbjfoddNGpnnnY5pHwckWfiYkMYSF74PmP1su0+ZOw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/36b93121436cbbf357974144068c23bac75154fa

  • Calculate extraUpdatedCols in query rewriter, not parser. It's unsafe to do this at parse time because addition of generated columns to a table would not invalidate stored rules containing UPDATEs on the table ... but there might now be dependent generated columns that were not there when the rule was made. This also fixes an oversight that rewriteTargetView failed to update extraUpdatedCols when transforming an UPDATE on an updatable view. (Since the new calculation is downstream of that, rewriteTargetView doesn't actually need to do anything; but before, there was a demonstrable bug there.) In v13 and HEAD, this leads to easily-visible bugs because (since commit c6679e4fc) we won't recalculate generated columns that aren't listed in extraUpdatedCols. In v12 this bitmap is mostly just used for trigger-firing decisions, so you'd only notice a problem if a trigger cared whether a generated column had been updated. I'd complained about this back in May, but then forgot about it until bug #16671 from Michael Paul Killian revived the issue. Back-patch to v12 where this field was introduced. If existing stored rules contain any extraUpdatedCols values, they'll be ignored because the rewriter will overwrite them, so the bug will be fixed even for existing rules. (But note that if someone were to update to 13.1 or 12.5, store some rules with UPDATEs on tables having generated columns, and then downgrade to a prior minor version, they might observe issues similar to what this patch fixes. That seems unlikely enough to not be worth going to a lot of effort to fix.) Discussion: https://postgr.es/m/10206.1588964727@sss.pgh.pa.us Discussion: https://postgr.es/m/16671-2fa55851859fb166@postgresql.org https://git.postgresql.org/pg/commitdiff/ad77039fad0f4128b0e4a05ddbf5dbc3ab5f3fa4

  • Use mode "r" for popen() in psql's evaluate_backtick(). In almost all other places, we use plain "r" or "w" mode in popen() calls (the exceptions being for COPY data). This one has been overlooked (possibly because it's buried in a ".l" flex file?), but it's using PG_BINARY_R. Kensuke Okamura complained in bug #16688 that we fail to strip \r when stripping the trailing newline from a backtick result string. That's true enough, but we'd also fail to convert embedded \r\n cleanly, which also seems undesirable. Fixing the popen() mode seems like the best way to deal with this. It's been like this for a long time, so back-patch to all supported branches. Discussion: https://postgr.es/m/16688-c649c7b69cd7e6f8@postgresql.org https://git.postgresql.org/pg/commitdiff/66f8687a8ff867f656de81e367314604d29dbd59

  • Doc: clean up verify_heapam() documentation. I started with the intention of just suppressing a PDF build warning by removing the example output, but ended up doing more: correcting factual errors in the function's signature, moving a bunch of generalized handwaving into the "Using amcheck Effectively" section which seemed a better place for it, and improving wording and markup a little bit. Discussion: https://postgr.es/m/732904.1603728748@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/4c49d8fc15eeb1dc69b0ddb2d986a1884a5d7f5f

  • Doc: clean up pg_relation_check_pages() documentation. Commit f2b883969 did not get the memo about the new formatting style for tables documenting built-in functions. I noticed because of a PDF build warning about an overwidth table. https://git.postgresql.org/pg/commitdiff/b787d4ce6d910080065025bcd5f968544997271f

  • Don't use custom OID symbols in pg_type.dat, either. On the same reasoning as in commit 36b931214, forbid using custom oid_symbol macros in pg_type as well as pg_proc, so that we always rely on the predictable macro names generated by genbki.pl. We do continue to grant grandfather status to the names CASHOID and LSNOID, although those are now considered deprecated aliases for the preferred names MONEYOID and PG_LSNOID. This is because there's likely to be client-side code using the old names, and this bout of neatnik-ism doesn't quite seem worth breaking client code. There might be a case for grandfathering EVTTRIGGEROID, too, since externally-maintained PLs may reference that symbol. But renaming such references to EVENT_TRIGGEROID doesn't seem like a particularly heavy lift --- we make far more significant backend API changes in every major release. For now I didn't add that, but we could reconsider if there's pushback. The other names changed here seem pretty unlikely to have any outside uses. Again, we could add alias macros if there are complaints, but for now I didn't. As before, no need for a catversion bump. John Naylor Discussion: https://postgr.es/m/CAFBsxsHpCbjfoddNGpnnnY5pHwckWfiYkMYSF74PmP1su0+ZOw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f90149e6285aaae6b48559afce1bd638ee26c33e

  • Stabilize timetz test across DST transitions. The timetz test cases I added in commit a9632830b were unintentionally sensitive to whether or not DST is active in the PST8PDT time zone. Thus, they'll start failing this coming weekend, as reported by Bernhard M. Wiedemann in bug #16689. Fortunately, DST-awareness is not significant to the purpose of these test cases, so we can just force them all to PDT (DST hours) to preserve stability of the results. Back-patch to v10, as the prior patch was. Discussion: https://postgr.es/m/16689-57701daa23b377bf@postgresql.org https://git.postgresql.org/pg/commitdiff/4a071afbd056282746a5bc9362e87f579a56402d

  • Doc: clarify description for pg_constraint.convalidated. Jimmy Angelakos Discussion: https://postgr.es/m/CABgVKCW_zPnvFXn24FTF0299_yU6+1p6JRUc0xpiZFWEXH1_jg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/b401fa206d446f224ec4760f21e0a351816c97b3

  • Fix assertion failure in check_new_partition_bound(). Commit 6b2c4e59d was overly confident about not being able to see a negative cmpval result from partition_range_bsearch(). Adjust the code to cope with that. Report and patch by Amul Sul; some additional cosmetic changes by me Discussion: https://postgr.es/m/CAAJ_b97WCO=EyVA7fKzc86kKfojHXLU04_zs7-7+yVzm=-1QkQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/970c05057593c2f5919a69b43fd917c4fa86f51c

  • Avoid null pointer dereference if error result lacks SQLSTATE. Although error results received from the backend should always have a SQLSTATE field, ones generated by libpq won't, making this code vulnerable to a crash after, say, untimely loss of connection. Noted by Coverity. Oversight in commit 403a3d91c. Back-patch to 9.5, as that was. https://git.postgresql.org/pg/commitdiff/7f4235032f0d75ea1ad29b192d57fee3d8fe533e

Álvaro Herrera pushed:

Robert Haas pushed:

Bruce Momjian pushed:

Andres Freund pushed:

  • Centralize horizon determination for temp tables, fixing bug due to skew. This fixes a bug in the edge case where, for a temp table, heap_page_prune() can end up with a different horizon than heap_vacuum_rel(). Which can trigger errors like "ERROR: cannot freeze committed xmax ...". The bug was introduced due to interaction of a7212be8b9e "Set cutoff xmin more aggressively when vacuuming a temporary table." with dc7420c2c92 "snapshot scalability: Don't compute global horizons while building snapshots.". The problem is caused by lazy_scan_heap() assuming that the only reason its HeapTupleSatisfiesVacuum() call would return HEAPTUPLE_DEAD is if the tuple is a HOT tuple, or if the tuple's inserting transaction has aborted since the heap_page_prune() call. But after a7212be8b9e that was also possible in other cases for temp tables, because heap_page_prune() uses a different visibility test after dc7420c2c92. The fix is fairly simple: Move the special case logic for temp tables from vacuum_set_xid_limits() to the infrastructure introduced in dc7420c2c92. That ensures that the horizon used for pruning is at least as aggressive as the one used by lazy_scan_heap(). The concrete horizon used for temp tables is slightly different than the logic in dc7420c2c92, but should always be as aggressive as before (see comments). A significant benefit to centralizing the logic procarray.c is that now the more aggressive horizons for temp tables does not just apply to VACUUM but also to e.g. HOT pruning and the nbtree killtuples logic. Because isTopLevel is not needed by vacuum_set_xid_limits() anymore, I undid the the related changes from a7212be8b9e. This commit also adds an isolation test ensuring that the more aggressive vacuuming and pruning of temp tables keeps working. Debugged-By: Amit Kapila amit.kapila16 [AT] gmail.com Debugged-By: Tom Lane tgl [AT] sss.pgh.pa.us Debugged-By: Ashutosh Sharma ashu.coek88 [AT] gmail.com Author: Andres Freund andres [AT] anarazel.de Discussion: https://postgr.es/m/20201014203103.72oke6hqywcyhx7s@alap3.anarazel.de Discussion: https://postgr.es/m/20201015083735.derdzysdtqdvxshp@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/94bc27b57680b4e757577e3f5b65dc32f96d33c1

  • Fix wrong data table horizon computation during backend startup. When ComputeXidHorizons() was called before MyDatabaseOid is set, e.g. because a dead row in a shared relation is encountered during InitPostgres(), the horizon for normal tables was computed too aggressively, ignoring all backends connected to a database. During subsequent pruning in a data table the too aggressive horizon could end up still being used, possibly leading to still needed tuples being removed. Not good. This is a bug in dc7420c2c92, which the test added in 94bc27b5768 made visible, if run with force_parallel_mode set to regress. In that case the bug is reliably triggered, because "pruning_query" is run in a parallel worker and the start of that parallel worker is likely to encounter a dead row in pg_database. The fix is trivial: Compute a more pessimistic data table horizon if MyDatabaseId is not yet known. Author: Andres Freund Discussion: https://postgr.es/m/20201029040030.p4osrmaywhqaesd4@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/1c7675a7a4265064a2c8e1ed02b2c042c2521664

Noah Misch pushed:

Pending Patches

Peter Eisentraut sent in a patch to add a default_result_formats GUC, which specifies the default result formats by data type for rows returned in the extended query protocol when no result formats are specified in the Bind message.

Álvaro Herrera sent in another revision of a patch to add tracing to libpq.

Julien Rouhaud sent in two revisions of a patch to handle = operator in pg_trgm.

Álvaro Herrera sent in another revision of a patch to add batch and pipelining support to libpq.

Craig Ringer sent in a patch to report libpq version and configuration.

Jakub Wartak sent in two revisions of a patch to add "IO read time" log message to autoanalyze.

Alexander Korotkov and Andrey Borodin traded patches to use a shared lock, as opposed to an exclusive control lock, in GetMultiXactIdMembers for offsets and members, make the MultiXact local cache size configurable, add a conditional variable to wait for next MultXact offset in corner case, and add GUCs to tune MultiXact SLRUs.

Pavel Borisov sent in another revision of a patch to make GROUP BY more efficient by making it possible to reorder the columns.

Peter Geoghegan sent in another revision of a patch to add sort_template.h for making fast sort functions, and add delete deduplication to nbtree.

Michaël Paquier sent in another revision of a patch to fix checksum verification in base backups for zero page headers.

Ajin Cherian and Peter Smith traded patches to implement logical decoding for two-phase transactions.

Zhenghua Lyu sent in a patch to consider the case when stanullfrac is 1.0 in get_variable_numdistinct.

Konstantin Knizhnik sent in five more revisions of a patch to implement compression in libpq.

Justin Pryzby sent in a patch to remove deprecated v8.2 containment operators.

Peter Eisentraut sent in another revision of a patch to support for writing CREATE FUNCTION and CREATE PROCEDURE statements for language SQL with a function body that conforms to the SQL standard and is portable to other implementations.

Ibrar Ahmed sent in another revision of a patch to implement temporal PK/FK deletes and updates.

Justin Pryzby sent in another revision of a patch to pg_dump to output DISABLE/ENABLE for child triggers if their state does not match their parent.

Arseny Sher sent in a patch to fix a use-after-free.

Amit Kapila sent in a patch to make some minor improvements to the description of spilled counters in pg_stat_replication_slots view.

Dilip Kumar sent in three more revisions of a patch to implement custom compression methods for tables and ways to change same.

John Naylor sent in two revisions of a patch to use the standard symbol for the builtin function heap_tableam_handler().

Michaël Paquier sent in another revision of a patch to enable online checksum verification in the backend.

Greg Nancarrow sent in two more revisions of a patch to enable parallel SELECT for "INSERT INTO ... SELECT ...", where it is safe to do so, and enable parallel INSERT and/or SELECT for "INSERT INTO ... SELECT ...", where it is safe to do so.

Masahiko Sawada and Bertrand Drouvot traded patches to log the standby recovery conflict waits.

Vigneshwaran C sent in another revision of a patch to parallelize parts of COPY.

Daniel Gustafsson sent in four more revisions of a patch to support NSS as a libpq TLS backend.

Michaël Paquier, Fabrízio de Royes Mello, and Nikolay Samokhvalov traded patches to add important information about ANALYZE after creating an expressional index.

Justin Pryzby sent in a patch to implement CLUSTER for partitioned tables, reserve indisclustered on children of clustered, partitioned indexes, and invalidate indisclustered when attaching unclustered indexes.

Justin Pryzby sent in a WIP patch to implement DROP INDEX CONCURRENTLY for partitioned indexes.

Hayato Kuroda sent in a patch to fix an issue in pgbench where queries were firing before connections were established by adding a call to pthread_barrier_wait().

Hou Zhijie sent in a patch to enhance libpq to support multiple host for non hot standby.

Pavel Borisov sent in a patch to fix for bug when running Valgrind on OSX.

Amul Sul sent in another revision of a patch to implement ALTER SYSTEM READ {ONLY|WRITE}.

Justin Pryzby sent in another revision of a patch to ensure that all the pg_ls_* functions show directories and shared filesets.

Tsutomu Yamada sent in four revisions of a patch to add list extended stats to psql's \dX and \dX+.

Kirk Jamison sent in another revision of a patch to make dropping relation buffers more efficient using dlist.

Bruce Momjian sent in two more revisions of a patch to implement a key management system.

Xiao Bai Yan sent in another revision of a patch to extend the shared tuple store and add batch store module, and in passing, use atomic operations instead of LWLock for shared tuple stores when getting the next read page, and use this to implement parallel distinct union aggregates and grouping sets support using batch hash aggregate.

Amit Kapila sent in another revision of a patch to track statistics for streaming of changes from ReorderBuffer.

John Naylor sent in a patch to forbid custom pg type symbols.

Heikki Linnakangas and Andres Freund traded patches to deduplicate aggregates and transition functions in the planner.

Amit Langote sent in another revision of a patch to rearrange partition routing layering in nodeModifyTable.c.

Takamichi Osumi make it possible to disable WAL logging to speed up data loading.

John Naylor sent in four revisions of a patch to document the fact that pg_settings view doesn't display custom options.

Bharath Rupireddy sent in two more revisions of a patch to adds two new functions: pg_terminate_backend(pid, wait, timeout), which terminates and waits or times out for a given backend, and pg_wait_backend(pid, timeout), which checks for the existence of the backend with a given PID and waits or times out until it goes away.

Euler Taveira de Oliveira sent in a patch to control temporary file removal after a crash.

Vigneshwaran C sent in four revisions of a patch to add a log message to include GSS authentication, encryption & principal information.

Noah Misch sent in another revision of a patch to prevent excess SimpleLruTruncate() deletion, and unlink less in SimpleLruTruncate(), as insurance against bugs.

Takamichi Osumi sent in another revision of a patch to implement CREATE OR REPLACE TRIGGER.

Justin Pryzby sent in another revision of a patch to pg_dump: Allow child partitions to be independently restored, even if the parent doesn't exist, or has missing/incompatible columns.

Justin Pryzby sent in a patch to retire pg_standby and pg_archivecleanup.

Masahiko Sawada sent in a PoC patch that adds some xx_vacuum GUC parameters to control the method of recording TIDs.

Michaël Paquier sent in another revision of a patch to fix page verifications in base backups.

Masahiro Ikeda sent in another revision of a patch to add statistics to the pg_stat_wal view.

Justin Pryzby sent in another revision of a patch to allow INSERT SELECT to use a BulkInsertState, and use same to make INSERT SELECT use multi_insert.

Daniel Gustafsson sent in another revision of a patch to clean up contrib/sslinfo and OpenSSL error handling.

Amit Langote sent in another revision of a patch to make update/delete of inheritance trees scale better.

Tomáš Vondra and James Coleman traded patches to fix get_useful_pathkeys_for_relation for volatile exprs below joins, and add comments explaining where projections aren't necessary.

Thomas Munro sent in two more revisions of a patch to implement collation versioning.

Amit Langote sent in another revision of a patch to call BeginDirectModify from ExecInitModifyTable, and initialize result relation information lazily.

Amit Langote sent in another revision of a patch to fix a bug with RETURNING when UPDATE moves tuples.

Ashutosh Bapat sent in two more revisions of a patch to enumize logical replication message actions.

Jimmy Angelakos sent in a patch to clarify the wording for convalidated in pg_constraint.

John Naylor sent in a patch to document deviation from the standard on REVOKE ROLE.

Andrey Borodin sent in another revision of a patch to add Sortsupport for sorting GiST build for gist_btree types.

Tomáš Vondra sent in another revision of a patch to extend range type operators to cope with elements.

Heikki Linnakangas sent in a WIP patch to find all line-endings in COPY in chunks.

Jürgen Purtz, Erik Rijkers, and Justin Pryzby traded patches to add a new chapter to the tutorial.

Tomáš Vondra sent in another revision of a patch to add extra statistics to explain for Nested Loop.

Justin Pryzby sent in another revision of a patch to fix typos.

Justin Pryzby sent in another revision of a patch to allow CREATE INDEX CONCURRENTLY on partitioned table, add a SKIPVALID flag for more integration, and make eindexPartitions() set indisvalid.

Andrey V. Lepikhov sent in another revision of a patch to remove unneeded self-joins.

Ranier Vilela sent in a patch to fix a dereference before NULL check in src/backend/storage/ipc/latch.c.

Ranier Vilela sent in a patch to fix an explicit NULL dereference in src/backend/utils/adt/ruleutils.c.

Justin Pryzby sent in another revision of a patch to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly.

Tom Lane sent in a patch to add a new CoercionForm variant, COERCE_SQL_SYNTAX.

Greg Sabino Mullane sent in another revision of a patch to help psql's \df choose functions by their arguments.

Tom Lane sent in a patch to get Gen_fmgrtab.pl to generate macros for all pg_proc entries.

Tom Lane sent in a patch to get rid of aggregate_dummy().

Categories: Informatika

Pages

Theme by me