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

What's new in this version:

- Fix mis-planning of repeated application of a projection step
- The planner could create an incorrect plan in cases where two ProjectionPaths were stacked on top of each other. The only known way to trigger that situation involves parallel sort operations, but there may be other instances. The result would be crashes or incorrect query results. Disclosure of server memory contents is also possible. (CVE-2021-3677)
- Disallow SSL renegotiation more completely
- SSL renegotiation has been disabled for some time, but the server would still cooperate with a client-initiated renegotiation request. A maliciously crafted renegotiation request could result in a server crash (see OpenSSL issue CVE-2021-3449). Disable the feature altogether on OpenSSL versions that permit doing so, which are 1.1.0h and newer.
- Restore the Portal-level snapshot after COMMIT or ROLLBACK within a procedure
- This change fixes cases where an attempt to fetch a toasted value immediately after COMMIT/ROLLBACK would fail with errors like “no known snapshots” or “missing chunk number 0 for toast value”.
- Some extensions may attempt to execute SQL code outside of any Portal. They are responsible for ensuring that an outer snapshot exists before doing so. Previously, not providing a snapshot might work or it might not; now it will consistently fail with “cannot execute SQL without an outer snapshot or portal”.
- Avoid misbehavior when persisting the output of a cursor that's reading a non-stable query
- Previously, we'd always rewind and re-read the whole query result, possibly getting results different from the earlier execution, causing great confusion later. For a NO SCROLL cursor, we can fix this by only storing the not-yet-read portion of the query output, which is sufficient since a NO SCROLL cursor can't be backed up. Cursors with the SCROLL option remain at hazard, but that was already documented to be an unsafe option to use with a non-stable query. Make those documentation warnings stronger.
- Also force NO SCROLL mode for the implicit cursor used by a PL/pgSQL FOR-over-query loop, to avoid this type of problem when persisting such a cursor during an intra-procedure commit.
- Reject SELECT ... GROUP BY GROUPING SETS (()) FOR UPDATE
- This should be disallowed, just as FOR UPDATE with a plain GROUP BY is disallowed, but the test for that failed to handle empty grouping sets correctly. The end result would be a null-pointer dereference in the executor.
- Reject cases where a query in WITH rewrites to just NOTIFY
- Such cases previously crashed.
- In numeric multiplication, round the result rather than failing if it would have more than 16383 digits after the decimal point (Dean Rasheed)
- Fix corner-case errors and loss of precision when raising numeric values to very large powers (Dean Rasheed)
- Fix division-by-zero failure in to_char() with EEEE format and a numeric input value less than 10^(-1001) (Dean Rasheed)
- Fix pg_size_pretty(bigint) to round negative values consistently with the way it rounds positive ones (and consistently with the numeric version) (Dean Rasheed, David Rowley)
- Make pg_filenode_relation(0, 0) return NULL rather than failing (Justin Pryzby)
- Make ALTER EXTENSION lock the extension when adding or removing a member object
- The previous coding allowed ALTER EXTENSION ADD/DROP to occur concurrently with DROP EXTENSION, leading to a crash or corrupt catalog entries.
- Fix ALTER SUBSCRIPTION to reject an empty slot name (Japin Li)
- When cloning a partitioned table's triggers to a new partition, ensure that their enabled status is copied (Álvaro Herrera)
- Avoid alias conflicts in queries generated for REFRESH MATERIALIZED VIEW CONCURRENTLY (Tom Lane, Bharath Rupireddy)
- This command failed on materialized views containing columns with certain names, notably mv and newdata.
- Fix PREPARE TRANSACTION to check correctly for conflicting session-lifespan and transaction-lifespan locks
- A transaction cannot be prepared if it has both session-lifespan and transaction-lifespan locks on the same advisory-lock ID value. This restriction was not fully checked, which could lead to a PANIC during PREPARE TRANSACTION.
- Fix misbehavior of DROP OWNED BY when the target role is listed more than once in an RLS policy
- Skip unnecessary error tests when removing a role from an RLS policy during DROP OWNED BY
- Notably, this fixes some cases where it was necessary to be a superuser to use DROP OWNED BY.
- Re-allow old-style Windows locale names in CREATE COLLATION commands
- Previously we were failing because the operating system can't provide version information for such locales. At some point we may decide to require version information, but no such policy exists yet, so re-allow the case for now.
- Disallow whole-row variables in GENERATED expressions
- Use of a whole-row variable clearly violates the rule that a generated column cannot depend on itself, so such cases have no well-defined behavior. The actual behavior frequently included a crash.
- Fix usage of tableoid in GENERATED expressions
- Some code paths failed to provide a valid value for this system column while evaluating a GENERATED expression.
- Don't store a “fast default” when adding a column to a foreign table
- The fast default is useless since no local heap storage exists for such a table, but it confused subsequent operations. In addition to suppressing creation of such catalog entries in ALTER TABLE commands, adjust the downstream code to cope when one is incorrectly present.
- Allow index state flags to be updated transactionally (Michael Paquier, Andrey Lepikhov)
- This avoids failures when dealing with index predicates that aren't really immutable. While that's not considered a supported case, the original reason for using a non-transactional update here is long gone, so we may as well change it.
- Avoid corrupting the plan cache entry when CREATE DOMAIN or ALTER DOMAIN appears in a cached plan
- Make walsenders show their latest replication commands in pg_stat_activity
- Previously, a walsender would show its latest SQL command, which was confusing if it's now doing some replication operation instead. Now we show replication-protocol commands on the same footing as SQL commands.
- Make pg_settings.pending_restart show as true when the pertinent entry in postgresql.conf has been removed (Álvaro Herrera)
- Pending_restart correctly showed the case where an entry that cannot be changed without a postmaster restart has been modified, but not where the entry had been removed altogether.
- On 64-bit Windows, allow the effective value of work_mem times hash_mem_multiplier to exceed 2GB
- This allows hash_mem_multiplier to be used for its intended purpose of preventing large hash aggregations from spilling to disk, even when “large” means multiple gigabytes.
- Fix mis-planning of queries involving regular tables that are inheritance children of foreign tables
- SELECT FOR UPDATE and related commands would fail with assertion failures or “could not find junk column” errors in such cases.
- Fix pullup of constant function-in-FROM results when the FROM item is marked LATERAL
- Fix corner-case failure of a new standby to follow a new primary
- Under a narrow combination of conditions, the standby could wind up trying to follow the wrong WAL timeline.
- Update minimum recovery point when WAL replay of a transaction abort record causes file truncation (Fujii Masao)
- File truncation is irreversible, so it's no longer safe to stop recovery at a point earlier than that record. The corresponding case for transaction commit was fixed years ago, but this one was overlooked.
- Advance oldest-required-WAL-segment horizon properly after a replication slot is invalidated
- If all slots were invalidated, the horizon would not move again, eventually allowing the server's WAL storage to run out of space.
- In walreceivers, avoid attempting catalog lookups after an error (Masahiko Sawada, Bharath Rupireddy)
- Ensure that a standby server's startup process will respond to a shutdown signal promptly while waiting for WAL to arrive (Fujii Masao, Soumyadeep Chakraborty)
- Correctly clear shared state after failing to become a member of a transaction commit group
- Given the right timing, this could cause an assertion failure when some later session re-uses the same PGPROC object.
- Add locking to avoid reading incorrect relmapper data in the face of a concurrent write from another process (Heikki Linnakangas)
- Improve progress reporting for the sort phase of a parallel btree index build (Matthias van de Meent)
- Improve checks for violations of replication protocol
- Logical replication workers frequently used Asserts to check for cases that could be triggered by invalid or out-of-order replication commands. This seems unwise, so promote these tests to regular error checks.
- Fix assorted crash cases in logical replication of partitioned-table updates
- Fix potential crash when firing AFTER triggers of partitioned tables in logical replication workers
- Fix deadlock when multiple logical replication workers try to truncate the same table
- Fix error cases and memory leaks in logical decoding of speculative insertions
- Fix memory leak in logical replication output
- Avoid leaving an invalid record-type hash table entry behind after an error
- This could lead to later crashes or memory leakage.
- Fix plan cache reference leaks in some error cases in CREATE TABLE ... AS EXECUTE
- Fix race condition in code for sharing tuple descriptors across parallel workers
- Given the right timing, a crash could result.
- Fix race condition when invalidating an obsolete replication slot concurrently with an attempt to drop or update it
- Fix possible race condition when releasing BackgroundWorkerSlots
- It's likely that this doesn't fix any observable bug on Intel hardware, but machines with weaker memory ordering rules could have problems.Fix latent crash in sorting code
- One code path could attempt to free a null pointer. The case appears unreachable in the core server's use of sorting, but perhaps it could be triggered by extensions.
- Harden B-tree posting list split code against corrupt data
- Throw an error, rather than crashing, for an attempt to insert an item with a TID identical to an existing entry. While that shouldn't ever happen, it has been reported to happen when the index is inconsistent with its table.
- Prevent infinite loops in SP-GiST index insertion
- In the event that INCLUDE columns take up enough space to prevent a leaf index tuple from ever fitting on a page, the text_ops operator class would get into an infinite loop vainly trying to make the tuple fit. While pre-v11 versions don't have INCLUDE columns, back-patch this anti-looping fix to them anyway, as it seems like a good defense against bugs in operator classes.
- Ensure that SP-GiST index insertion can be terminated by a query cancel request
- Fix uninitialized-variable bug that could cause PL/pgSQL to act as though an INTO clause specified STRICT, even though it didn't
- Don't abort the process for an out-of-memory failure in libpq's printing functions
- In ecpg, allow the numeric value INT_MIN (usually -2147483648) to be converted to integer
- In psql and other client programs, avoid overrunning the ends of strings when dealing with invalidly-encoded data
- An incorrectly-encoded multibyte character near the end of a string could cause various processing loops to run past the string's terminating NUL, with results ranging from no detectable issue to a program crash, depending on what happens to be in the following memory. This is reminiscent of CVE-2006-2313, although these particular cases do not appear to have interesting security consequences.
- Fix pg_dump to correctly handle triggers on partitioned tables whose enabled status is different from their parent triggers' status
- Avoid “invalid creation date in header” warnings observed when running pg_restore on an archive file created in a different time zone
- Make pg_upgrade carry forward the old installation's oldestXID value
- Previously, the new installation's oldestXID was set to a value old enough to (usually) force immediate anti-wraparound autovacuuming. That's not desirable from a performance standpoint; what's worse, installations using large values of autovacuum_freeze_max_age could suffer unwanted forced shutdowns soon after an upgrade.
- Extend pg_upgrade to detect and warn about extensions that should be upgraded
- A script file is now produced containing the ALTER EXTENSION UPDATE commands needed to bring extensions up to the versions that are considered default in the new installation.
- Avoid problems when switching pg_receivewal between compressed and non-compressed WAL storage
- Fix contrib/postgres_fdw to work usefully with generated columns
- Postgres_fdw will now behave reasonably with generated columns, so long as a generated column in a foreign table represents a generated column in the remote table. IMPORT FOREIGN SCHEMA will now import generated columns that way by default.
- In contrib/postgres_fdw, avoid attempting catalog lookups after an error
- While this usually worked, it's not very safe since the error might have been one that made catalog access nonfunctional. A side effect of the fix is that messages about data conversion errors will now mention the query's table and column aliases (if used) rather than the true underlying name of a foreign table or column.
- Improve the isolation-test infrastructure
- Allow isolation test steps to be annotated to show the expected completion order. This allows getting stable results from otherwise-racy test cases, without the long delays that we previously used (not entirely successfully) to fend off race conditions. Allow non-quoted identifiers as isolation test session/step names (formerly, all such names had to be double-quoted). Detect and warn about unused steps in isolation tests. Improve display of query results in isolation tests. Remove isolationtester's “dry-run” mode. Remove memory leaks in isolationtester itself.
- Reduce overhead of cache-clobber testing
- Fix PL/Python's regression tests to pass with Python 3.10
- Make printf("%s", NULL) print (null) instead of crashing
- This should improve server robustness in corner cases, and it syncs our printf implementation with common libraries.
- Fix incorrect log message when point-in-time recovery stops at a ROLLBACK PREPARED record
- Improve ALTER TABLE's messages for wrong-relation-kind errors
- Clarify error messages referring to “non-negative” values
- Fix configure to work with OpenLDAP 2.5, which no longer has a separate libldap_r library
- If there is no libldap_r library, we now silently assume that libldap is thread-safe.
- Add new make targets world-bin and install-world-bin
- These are the same as world and install-world respectively, except that they do not build or install the documentation.
- Fix make rule for TAP tests (prove_installcheck) to work in PGXS usage
- Adjust JIT code to prepare for forthcoming LLVM API change
- LLVM 13 has made an incompatible API change that will cause crashing of our previous JIT compiler.
- Avoid assuming that strings returned by GSSAPI libraries are null-terminated
- The GSSAPI spec provides for a string pointer and length. It seems that in practice the next byte after the string is usually zero, so that our previous coding didn't actually fail; but we do have a report of AddressSanitizer complaints.
- Enable building with GSSAPI on MSVC
- Fix various incompatibilities with modern Kerberos builds.
- In MSVC builds, include --with-pgport in the set of configure options reported by pg_config, if it had been specified