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

What's new in this version:

- A dump/restore is not required for those running 12.X
- E.1.2. Changes
- Fix crash when ALTER TABLE adds a column without a default value along with making other changes that require a table rewrite (Andres Freund)
- Fix lock handling in REINDEX CONCURRENTLY (Michael Paquier)
- REINDEX CONCURRENTLY neglected to take a session-level lock on the new index version, potentially allowing other sessions to manipulate it too soon. Also, a query-cancel or session-termination interrupt arriving at the wrong time could result in failure to release the session-level locks that REINDEX CONCURRENTLY does hold
- Avoid crash due to race condition when reporting the progress of a CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY command
- Avoid creating duplicate dependency entries during REINDEX CONCURRENTLY
- This bug resulted in bloat in pg_depend, but no worse consequences than that
- Prevent VACUUM from trying to freeze an old multixact ID involving a still-running transaction
- This case would lead to VACUUM failing until the old transaction terminates
- Fix “wrong type of slot” error when trying to CLUSTER on an expression index
- SET CONSTRAINTS ... DEFERRED failed on partitioned tables, incorrectly complaining about lack of triggers
- Fix failure when creating indexes for a partition, if the parent partitioned table contains any dropped columns
- Fix dropping of indexed columns in partitioned tables
- Previously this might fail with an error message complaining about the dependencies of the indexes. It should automatically drop the indexes, instead
- Ensure that a partition index can be dropped after a failure to reindex it concurrently
- The index's pg_class.relispartition flag was left in the wrong state in such a case, causing DROP INDEX to fail
- Fix handling of equivalence class members for partition-wise joins
- This oversight could lead either to failure to use a feasible partition-wise join plan, or to a “could not find pathkey item to sort” planner failure
- Ensure that offset expressions in WINDOW clauses are processed when a query's expressions are manipulated
- This oversight could result in assorted failures when the offsets are nontrivial expressions. One example is that a function parameter reference in such an expression would fail if the function was inlined
- Avoid postmaster failure if a parallel query requests a background worker when no postmaster child process array slots remain free
- Fix crash triggered by an EvalPlanQual recheck on a table with a BEFORE UPDATE trigger
- Fix “unexpected relkind” error when a query tries to access a TOAST table
- The error should say that permission is denied, but this case got broken during code refactoring
- Provide a relevant error context line when an error occurs while setting GUC parameters during parallel worker startup
- Ensure that fsync() is applied only to files that are opened read/write
- Some code paths tried to do this after opening a file read-only, but on some platforms that causes “bad file descriptor” or similar errors
- Allow encoding conversion to succeed on longer strings than before
- Previously, there was a hard limit of 0.25GB on the input string, but now it will work as long as the converted output is not over 1GB
- Avoid creating unnecessarily-bulky tuple stores for window functions
- In some cases the tuple storage would include all columns of the source table(s), not just the ones that are needed by the query
- Allow repalloc() to give back space when a large chunk is reduced in size
- Ensure that temporary WAL and history files are removed at the end of archive recovery
- Avoid failure in archive recovery if recovery_min_apply_delay is enabled
- recovery_min_apply_delay is not typically used in this configuration, but it should work
- Ignore restore_command, recovery_end_command, and recovery_min_apply_delay settings during crash recovery
- Now that these settings can be specified in postgresql.conf, they could be turned on during crash recovery, but honoring them then is undesirable. Ignore these settings until crash recovery is complete
- Fix logical replication failure when publisher and subscriber have different ideas about a table's replica identity columns
- Declaring a column as part of the replica identity on the subscriber, when it does not exist at all on the publisher, led to “negative bitmapset member not allowed” errors
- Avoid unwanted delay during shutdown of a logical replication walsender
- Fix timeout handling in logical replication walreceiver processes
- Erroneous logic prevented wal_receiver_timeout from working in logical replication deployments
- Correctly time-stamp replication messages for logical decoding
- This oversight resulted, for example, in pg_stat_subscription.last_msg_send_time usually reading as NULL
- Fix race condition during backend exit, when the backend process has previously waited for synchronous replication to occur
- Avoid logging complaints about abandoned connections when using PAM authentication
- libpq-based clients will typically make two connection attempts when a password is required, since they don't prompt their user for a password until their first connection attempt fails. Therefore the server is coded not to generate useless log spam when a client closes the connection upon being asked for a password. However, the PAM authentication code hadn't gotten that memo, and would generate several messages about a phantom authentication failure.
Fix misbehavior of bitshiftright()
- The bitstring right shift operator failed to zero out padding space that exists in the last byte of the result when the bitstring length is not a multiple of 8. While invisible to most operations, any nonzero bits there would result in unexpected comparison behavior, since bitstring comparisons don't bother to ignore the extra bits, expecting them to always be zero
- If you have inconsistent data as a result of saving the output of bitshiftright() in a table, it's possible to fix it with something like
UPDATE mytab SET bitcol = ~(~bitcol) WHERE bitcol != ~(~bitcol)
- Fix result of text position() function (also known as strpos()) for an empty search string
- Historically, and per the SQL standard, the result should be one in such cases, but 12.0 returned zero
- Fix detection of edge-case integer overflow in interval multiplication
- Avoid crashes if ispell text search dictionaries contain wrong affix data
- Avoid memory leak while vacuuming a GiST index
- On Windows, recognize additional spellings of the “Norwegian (Bokmål)” locale name
- Fix libpq to allow trailing whitespace in the string values of integer parameters
- Version 12 tightened libpq's validation of integer parameters, but disallowing trailing whitespace seems undesirable
- In libpq, correctly report CONNECTION_BAD connection status after a failure caused by a syntactically invalid connect_timeout parameter value
- Avoid compile failure if an ECPG client includes ecpglib.h while having ENABLE_NLS defined
- This risk was created by a misplaced declaration: ecpg_gettext() should not be visible to client code
- Fix scheduling of parallel restore of a foreign key constraint on a partitioned table
pg_dump failed to emit full dependency information for partitioned tables' foreign keys. This could allow parallel pg_restore to try to recreate a foreign key constraint too soon
- In pg_dump, ensure stable output order for similarly-named triggers and row-level-security policy objects
- Previously, if two triggers on different tables had the same names, they would be sorted in OID-based order, which is less desirable than sorting them by table name. Likewise for RLS policies
- In pg_upgrade, reject tables with columns of type sql_identifier, as that has changed representation in version 12
- Improve pg_upgrade's checks for the use of a data type that has changed representation, such as line
- The previous coding could be fooled by cases where the data type of interest underlies a stored column of a domain or composite type
- In pg_rewind with the --dry-run option, avoid updating pg_control
- This could lead to failures in subsequent pg_rewind attempts
- Fix failure in pg_waldump with the -s option, when a continuation WAL record ends exactly at a page boundary
- In pg_waldump with the --bkp-details option, avoid emitting extra newlines for WAL records involving full-page writes
- Fix small memory leak in pg_waldump
- Put back pqsignal() as an exported libpq symbol
- This function was removed on the grounds that no clients should be using it, but that turns out to break usage of current libpq with very old versions of psql, and perhaps other applications
- Fix configure's test for presence of libperl so that it works on recent Red Hat releases
- Previously, it could fail if the user sets CFLAGS to -O0
- Ensure correct code generation for spinlocks on PowerPC
- The previous spinlock coding allowed the compiler to select register zero for use with an assembly instruction that does not accept that register, causing a build failure. We have seen only one long-ago report that matches this bug, but it could cause problems for people trying to build modified PostgreSQL code or use atypical compiler options
- On AIX, don't use the compiler option -qsrcmsg
- This avoids an internal compiler error with xlc v16.1.0, with little consequence other than changing the format of compiler error messages
- Fix MSVC build process to cope with spaces in the file path of OpenSSL