Amazon Aurora PostgreSQL Limitless Database updates

Added support for CREATE INDEX IF NOT EXISTS.

Added support for SEQUENCES in the pgstattuple extension.

Added support for the hstore extension. Note that hstore columns cannot be used as shard keys in sharded tables.

Added support for the auto_explain extension.

Reduced waiting period required between DROP DATABASE and CREATE DATABASE operations when using the same database name.

Improved performance for DELETE and UPDATE operations involving semi-join and anti-join.

Improved performance for INSERT INTO ... SELECT FROM statements with expressions or projections that can be pushed down to shards

Improved error messages for detected deadlocks with involved queries listed in the DETAIL section.

Fixed an issue where the background worker pool could be completely exhausted by database internal processes.

Fixed an issue where non-immutable functions in extensions unexpectedly failed with permission denied errors.

Fixed commit time is invalid errors.

Fixed an issue where INSERT INTO failed after the ROLLBACK of a READ ONLY transaction with the error ERROR: failed to execute remote query with message: transaction read-write mode must be set before any query.

Fixed an issue where SELECT queries failed with ERROR: failed to execute remote query with message: collations are not supported by type integer.

Fixed an issue where DELETE FROM failed with ERROR: failed to execute remote query.

Fixed an issue where expressions in DEFAULT/CHECK constraints might generate incorrect backfill data for existing rows. This issue is seen specifically when the expression in the constraint relies on parenthesis.

Fixed an issue with PREPARE and EXECUTE statements which failed with bind message supplies 2 parameters, but prepared statement requires 1 when the PREPARE statement used fewer parameters than it declared.

Fixed an issue where RESET of rds_aurora.limitless_explain_options setting was not correctly reflected in EXPLAIN output.

Fixed an issue where tables weren't deleted on shards when CREATE, ALTER, and DROP statements were repeatedly executed using prepared statements through JDBC or ODBC drivers (for example, when using preferQueryMode=extendedCacheEverything in JDBC), or when CREATE, ALTER, and DROP statements were executed as part of PROCEDURE or FUNCTION.

Fixed incorrect results when querying tables using UNIQUE INDEX scans while the table had broken Heap-Only Tuple (HOT) chains from UPDATE statements.

Fixed an issue where healthy nodes were unnecessarily replaced, causing system unavailability.

Improved error messages for detected deadlocks, now listing the involved queries in the DETAIL section.

Added support for sub-query containing tableoid as target list.

Fixed the ANALYZE SQL query result to also include foreign tables.

Fixed multiple issues which might lead to a database restart.

Fixed an issue with PREPARE and EXECUTE statements which failed incorrectly with bind message supplies 2 parameters, but prepared statement requires 1 when the PREPARE statement used less parameters than declared.

Fixed multiple issues in vacuum operations leading to increased space consumption.

Fixed an issue with INSERT statements after rolling back a READ_ONLY transaction.

Fixed an issue where DELETE statements with RETURNING clause fails.

Added support for the ltree extension. ltree datatype columns cannot be used as shard keys in sharded tables.

Added support for the btree_gist extension in Aurora PostgreSQL Limitless Database. Exclusion constraints using GIST index are not supported. For example, the following command is not supported:

CREATE TABLE zoo (
    cage INTEGER,
    animal TEXT,
    EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);

Improved query performance in these scenarios:

Enhanced async foreign scan nodes with prefetch capability.

Strengthened deadlock detection fault tolerance.

Fixed an issue where database backends stopped responding to system signals, which could cause DROP DATABASE operations to hang.

Fixed a race condition in commit log (clog) background processing that causes commit time is invalid errors.

Fixed an issue that could cause unexpected crashes during automatic table analysis operations.

Fixed an issue where tables weren't deleted on shards when CREATE, ALTER, and DROP statements were repeatedly executed using prepared statements through JDBC or ODBC drivers (for example, when using preferQueryMode=extendedCacheEverything in JDBC), or when CREATE, ALTER, and DROP statements were executed as part of PROCEDURE or FUNCTION.

Fixed incorrect results when querying tables using UNIQUE INDEX scans while the table had broken Heap-Only Tuple (HOT) chains from UPDATE statements.

Fixed an issue where healthy nodes were unnecessarily replaced, causing system unavailability.

Improved error messages for detected deadlocks now list the involved queries in the DETAIL.

Improved the distribute deadlock detection algorithm to be arbitrary resilient to network or node failures.

Added support for sub-query containing tableoid as target list.

Fixed the ANALYZE SQL query result to include foreign tables.

Fixed multiple issues which might lead to a database restart.

Fixed an issue with PREPARE and EXECUTE statements which failed incorrectly with bind message supplies 2 parameters, but prepared statement requires 1 when the PREPARE statement uses less parameters than declared.

Fixed multiple issues in vacuum operations leading to increased space consumption.

Fixed an issue with INSERT statements after rolling back a READ_ONLY transaction.

Fixed an issue where DELETE statements with RETURNING clause failed.

Fixed cases where expressions in DEFAULT/CHECK constraints can generate incorrect results, specifically when the expression in the constraint relies on parenthesis for generating the correct result.

Fixed multiple issues that might lead to brief periods of unavailability.

Fixed a correctness issue when limitless_minimum_adaptive_fetch_size is set to lower than the default value (100).

Enhanced error handling for querying tableoid attribute of a relation in Aurora PostgreSQL Limitless Database, considering it might have different values across nodes in DBShardGroup.

Fixed a permission issue that can occur when using rds_aurora.limitless_active_shard_key. This resolves permission errors when retrieving data.

Fixed a race condition in clog background operation whcih may cause the client to recieve the commit time is invalid error.

Optimized execution of distributed functions with variable length shard key. Previously, routers performed distributed functions but they are performed locally now on a single shard as per shard key.

Fixed an error when executing update/delete on standard table joining reference tables.

Fixed an issue where any user could invoke specific internal limitless functions.

Fixed an issue that lead to incorrect rollback for subtransaction during node crash.

Fixed issues where expressions with nested parentheses in DEFAULT/CHECK constraints might generate incorrect results.

Aurora PostgreSQL Limitless Database now supports Aurora PostgreSQL version 16.6.

You can now install and use the btree_gin extension.

The DISCARD Data Manipulation Language (DML) command is supported.

Improved query performance with better pushdown capabilities for queries that include reference tables within subqueries. This enhancement optimizes query execution, potentially resulting in faster performance for complex queries involving reference table joins in subqueries.

Enhanced the error handling for pg_advisory_lock. Previously, attempts to use this unsupported feature didn't generate an explicit error message, potentially leading to unexpected behavior. Now, users will receive a clear error notification when attempting to use pg_advisory_lock, ensuring better clarity and preventing unintended consequences.

Fixed an issue where certain ALTER TABLE and CREATE TABLE commands fail with the error: Unable to deparse the given statement.

Fixed an issue where an ALTER TABLE command with a NOT NULL or DEFAULT constraint fails when it contains functions unsafe to push down to shards (such as volatile functions), requiring backfilling data in existing rows.

Setting the rds_aurora.limitless_minimum_adaptive_fetch_size variable can cause incorrect results.

Fixed an issue where tables weren't deleted on shards when CREATE, ALTER, and DROP statements were repeatedly executed using prepared statements through JDBC or ODBC drivers (for example, when using preferQueryMode=extendedCacheEverything in JDBC), or when CREATE, ALTER, and DROP statements were executed as part of PROCEDURE or FUNCTION.

Fixed incorrect results when querying tables using UNIQUE INDEX scans while the table had broken Heap-Only Tuple (HOT) chains from UPDATE statements.

Fixed an issue where healthy nodes were unnecessarily replaced, causing system unavailability.

Fixed multiple issues that could lead to brief periods of unavailability.

Fixed an issue resulting in incorrect results for anti-join queries containing a predicate on an outer relation.

Added pushdown capability for the following system functions:

Enhanced error handling for pg_advisory_lock in Aurora PostgreSQL Limitless Database. With this change, you receive a clear error notification when attempting to use pg_advisory_lock.

Fixed an issue where adding an expression with nested parentheses in DEFAULT/CHECK constraints might generate incorrect results.

Fixed an issue where deadlocks can occur in processing Data Definition Language (DDL) statements when CREATE INDEX CONCURRENTLY runs.

Fixed an issue where an error in distributed deadlock resolution can block the detection of subsequent distributed deadlocks.

Fixed an issue where DDL statements with underlying Data Manipulation Language (DML) statements (such as CREATE TABLE AS or calling for a table with data) result in errors if the rds_aurora.limitless_active_shard_key variable is used in a session.

Fixed an issue where a shard split operation results in an error.

Fixed an issue where the rds_aurora.limitless_alter_table_type_sharded procedure for converting the table type to sharded fails if the rds_aurora.limitless_alter_table_type_sharded variable is set.

Fixed an issue in the deparsing logic of polymorphic objects that resulted in invalid queries not producing errors when the implicit cast of passing data caused incorrect object versions to be used.

An issue can occur when part of a query is converted to a left or anti-join condition. In these cases, if the inner relation has no results a null row is used.

An issue can occur when adding a column with a NOT NULL or DEFAULT constraint, that contains functions unsafe to push down to shards (such as volatile functions), requires backfilling data in existing rows.

An issue can occur when the deparsing logic for preparing queries to shards doesn't use parentheses where possible. This can lead to errors when COLLATE is used in queries.