PostgreSQL 是什麼，跟 MySQL 有什麼差別

What's new in this version:

- Make the server reject extraneous data after an SSL or GSS encryption handshake
- A man-in-the-middle with the ability to inject data into the TCP connection could stuff some cleartext data into the start of a supposedly encryption-protected database session. This could be abused to send faked SQL commands to the server, although that would only work if the server did not demand any authentication data. (However, a server relying on SSL certificate authentication might well not do so.)
- The PostgreSQL Project thanks Jacob Champion for reporting this problem.
- Make libpq reject extraneous data after an SSL or GSS encryption handshake
- A man-in-the-middle with the ability to inject data into the TCP connection could stuff some cleartext data into the start of a supposedly encryption-protected database session. This could probably be abused to inject faked responses to the client's first few queries, although other details of libpq's behavior make that harder than it sounds. A different line of attack is to exfiltrate the client's password, or other sensitive data that might be sent early in the session. That has been shown to be possible with a server vulnerable to CVE-2021-23214.
- The PostgreSQL Project thanks Jacob Champion for reporting this problem.
- Fix physical replication for cases where the primary crashes after shipping a WAL segment that ends with a partial WAL record
- If the primary did not survive long enough to finish writing the rest of the incomplete WAL record, then the previous crash-recovery logic had it back up and overwrite WAL starting from the beginning of the incomplete WAL record. This is problematic since standby servers may already have copies of that WAL segment. They will then see an inconsistent next segment, and will not be able to recover without manual intervention. To fix, do not back up over a WAL segment boundary when restarting after a crash. Instead write a new type of WAL record at the start of the next WAL segment, informing readers that the incomplete WAL record will never be finished and must be disregarded.
- When applying this update, it's best to update standby servers before the primary, so that they will be ready to handle this new WAL record type if the primary happens to crash.
- Ensure that parallel VACUUM doesn't miss any indexes (Peter Geoghegan, Masahiko Sawada)
- A parallel VACUUM would fail to process indexes that are below the min_parallel_index_scan_size cutoff, if the table also has at least two indexes that are above that size. This could result in those indexes becoming corrupt, since they'd still contain references to any heap entries removed by the VACUUM; subsequent queries using such indexes would be likely to return rows they shouldn't. This problem does not affect autovacuum, since it doesn't use parallel vacuuming. However, it is advisable to reindex any manually-vacuumed tables that have the right mix of index sizes.
- Fix CREATE INDEX CONCURRENTLY to wait for the latest prepared transactions (Andrey Borodin)
- Rows inserted by just-prepared transactions might be omitted from the new index, causing queries relying on the index to miss such rows. The previous fix for this type of problem failed to account for PREPARE TRANSACTION commands that were still in progress when CREATE INDEX CONCURRENTLY checked for them. As before, in installations that have enabled prepared transactions (max_prepared_transactions > 0), it's recommended to reindex any concurrently-built indexes in case this problem occurred when they were built.
- Avoid race condition that can cause backends to fail to add entries for new rows to an index being built concurrently
- While it's apparently rare in the field, this case could potentially affect any index built or reindexed with the CONCURRENTLY option. It is recommended to reindex any such indexes to make sure they are correct.
- Fix REINDEX CONCURRENTLY to preserve operator class parameters that were attached to the target index (Michael Paquier)
- Fix incorrect creation of shared dependencies when cloning a database that contains non-builtin objects
- The effects of this error are probably limited in practice. In principle, it could allow a role to be dropped while it still owns objects; but most installations would never want to drop a role that had been used for objects they'd added to template1.
- Ensure that the relation cache is invalidated for a table being attached to or detached from a partitioned table
- This oversight could allow misbehavior of subsequent inserts/updates addressed directly to the partition, but only in currently-existing sessions.
- Fix corruption of parse tree while creating a range type
- CREATE TYPE incorrectly freed an element of the parse tree, which could cause problems for a later event trigger, or if the CREATE TYPE command was stored in the plan cache and used again later.
- Fix updates of element fields in arrays of domain over composite
- A command such as UPDATE tab SET fld[1].subfld = val failed if the array's elements were domains rather than plain composites.
- Disallow the combination of FETCH FIRST WITH TIES and FOR UPDATE SKIP LOCKED
- FETCH FIRST WITH TIES necessarily fetches one more row than requested, since it cannot stop until it finds a row that is not a tie. In our current implementation, if FOR UPDATE is used then that row will also get locked even though it is not returned. That results in undesirable behavior if the SKIP LOCKED option is specified. It's difficult to change this without introducing a different set of undesirable behaviors, so for now, forbid the combination.
- Disallow ALTER INDEX index ALTER COLUMN col SET (options)
- While the parser accepted this, it's undocumented and doesn't actually work.
- Fix corner-case loss of precision in numeric power()
- The result could be inaccurate when the first argument is very close to 1.
- Avoid choosing the wrong hash equality operator for Memoize plans
- This error could result in crashes or incorrect query results.
- Fix planner error with pulling up subquery expressions into function rangetable entries
- If a function in FROM laterally references the output of some sub-SELECT earlier in the FROM clause, and we are able to flatten that sub-SELECT into the outer query, the expression(s) copied into the function expression were not fully processed. This could lead to crashes at execution.
- Avoid using MCV-only statistics to estimate the range of a column
- There are corner cases in which ANALYZE will build a most-common-values (MCV) list but not a histogram, even though the MCV list does not account for all the observed values. In such cases, keep the planner from using the MCV list alone to estimate the range of column values.
- Fix restoration of a Portal's snapshot inside a subtransaction
- If a procedure commits or rolls back a transaction, and then its next significant action is inside a new subtransaction, snapshot management went wrong, leading to a dangling pointer and probable crash. A typical example in PL/pgSQL is a COMMIT immediately followed by a BEGIN ... EXCEPTION block that performs a query.
- Clean up correctly if a transaction fails after exporting its snapshot
- This oversight would only cause a problem if the same session attempted to export a snapshot again. The most likely scenario for that is creation of a replication slot (followed by rollback) and then creation of another replication slot.
- Prevent wraparound of overflowed-subtransaction tracking on standby servers
- This oversight could cause significant performance degradation (manifesting as excessive SubtransSLRU traffic) on standby servers.
- Ensure that prepared transactions are properly accounted for during promotion of a standby server (Michael Paquier, Andres Freund)
- There was a narrow window where a prepared transaction could be omitted from a snapshot taken by a concurrently-running session. If that session then used the snapshot to perform data updates, erroneous results or data corruption could occur.
- Fix “could not find RecursiveUnion” error when EXPLAIN tries to print a filter condition attached to a WorkTableScan node
- Ensure that the correct lock level is used when renaming a table
- For historical reasons, ALTER INDEX ... RENAME can be applied to any sort of relation. The lock level required to rename an index is lower than that required to rename a table or other kind of relation, but the code got this wrong and would use the weaker lock level whenever the command is spelled ALTER INDEX.
- Avoid null-pointer-dereference crash when dropping a role that owns objects being dropped concurrently
- Prevent “snapshot reference leak” warning when lo_export() or a related function fails
- Fix inefficient code generation for CoerceToDomain expression nodes
- Avoid O(N^2) behavior in some list-manipulation operations
- These changes fix slow processing in several scenarios, including: when a standby replays a transaction that held many exclusive locks on the primary; when many files are due to be unlinked after a checkpoint; when hash aggregation involves many batches; and when pg_trgm extracts indexable conditions from a complex regular expression. Only the first of these scenarios has actually been reported from the field, but they all seem like plausible consequences of inefficient list deletions.
- Add more defensive checks around B-tree posting list splits
- This change should help detect index corruption involving duplicate table TIDs
- Avoid assertion failure when inserting NaN into a BRIN float8 or float4 minmax_multi_ops index
- In production builds, such cases would result in a somewhat inefficient, but not actually incorrect, index.
- Allow the autovacuum launcher process to respond to pg_log_backend_memory_contexts() requests more quickly
- Fix memory leak in HMAC hash calculations
- Disallow setting huge_pages to on when shared_memory_type is sysv
- Previously, this setting was accepted, but it did nothing for lack of any implementation
- Fix checking of query type in PL/pgSQL's RETURN QUERY statement
- RETURN QUERY should accept any query that can return tuples, e.g. UPDATE RETURNING. v14 accidentally disallowed anything but SELECT; moreover, the RETURN QUERY EXECUTE variant failed to apply any query-type check at all.
- Fix pg_dump to dump non-global default privileges correctly
- If a global (unrestricted) ALTER DEFAULT PRIVILEGES command revoked some present-by-default privilege, for example EXECUTE for functions, and then a restricted ALTER DEFAULT PRIVILEGES command granted that privilege again for a selected role or schema, pg_dump failed to dump the restricted privilege grant correctly.
- Make pg_dump acquire shared lock on partitioned tables that are to be dumped
- This oversight was usually pretty harmless, since once pg_dump has locked any of the leaf partitions, that would suffice to prevent significant DDL on the partitioned table itself. However problems could ensue when dumping a childless partitioned table, since no relevant lock would be held.
- Fix crash in pg_dump when attempting to dump trigger definitions from a pre-8.3 server
- Fix incorrect filename in pg_restore's error message about an invalid large object TOC file
- Ensure that pgbench exits with non-zero status after a socket-level failure
- The desired behavior is to finish out the run but then exit with status 2. Also, fix the reporting of such errors.
- Prevent pg_amcheck from checking temporary relations, as well as indexes that are invalid or not ready
- This avoids unhelpful checks of relations that will almost certainly appear inconsistent
- Make contrib/amcheck skip unlogged tables when running on a standby server
- It's appropriate to do this since such tables will be empty, and unlogged indexes were already handled similarly
- Change contrib/pg_stat_statements to read its “query texts” file in units of at most 1GB
- Such large query text files are very unusual, but if they do occur, the previous coding would fail on Windows 64 (which rejects individual read requests of more than 2GB).
- Fix null-pointer crash when contrib/postgres_fdw tries to report a data conversion error
- Ensure that GetSharedSecurityLabel() can be used in a newly-started session that has not yet built its critical relation cache entries
- When running a TAP test, include the module's own directory in PATH
- This allows tests to find built programs that are not installed, such as custom test drivers.
- Use the CLDR project's data to map Windows time zone names to IANA time zones
- When running on Windows, initdb attempts to set the new cluster's timezone parameter to the IANA time zone matching the system's prevailing time zone. We were using a mapping table that we'd generated years ago and updated only fitfully; unsurprisingly, it contained a number of errors as well as omissions of recently-added zones. It turns out that CLDR has been tracking the most appropriate mappings, so start using their data. This change will not affect any existing installation, only newly-initialized clusters.
- Update time zone data files to tzdata release 2021e for DST law changes in Fiji, Jordan, Palestine, and Samoa, plus historical corrections for Barbados, Cook Islands, Guyana, Niue, Portugal, and Tonga.