AWS prescriptive-guidance documentation change
Summary
Updated prerequisites, Oracle version, added Troubleshooting section, and Additional Information. Changed terminology from Oracle to PostgreSQL in loop references.
Security assessment
Changes are documentation improvements without security context. Troubleshooting focuses on array handling errors (index bounds and NULL values), not vulnerabilities. No security features, vulnerabilities, or mitigations are mentioned.
Diff
diff --git a/prescriptive-guidance/latest/patterns/emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql.md b/prescriptive-guidance/latest/patterns/emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql.md index 9554255cf..753b3ed02 100644 --- a//prescriptive-guidance/latest/patterns/emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql.md +++ b//prescriptive-guidance/latest/patterns/emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql.md @@ -7 +7 @@ -SummaryPrerequisites and limitationsArchitectureToolsBest practicesEpicsRelated resources +SummaryPrerequisites and limitationsArchitectureToolsBest practicesEpicsTroubleshootingRelated resourcesAdditional information @@ -19 +19 @@ We provide a PostgreSQL alternative to using `aws_oracle_ext` functions for hand -**Oracle** +_Oracle_ @@ -25 +25 @@ This flexibility allows for sparse array structures where elements can be popula -**PostgreSQL (Amazon Aurora and Amazon RDS)** +_PostgreSQL (Amazon Aurora and Amazon RDS)_ @@ -29 +29 @@ PostgreSQL handles empty values differently from `NULL` values. It stores empty -**AWS Schema Conversion Tool** +_AWS Schema Conversion Tool_ @@ -37 +37 @@ The [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaCon - * An active AWS account. + * An active AWS account @@ -39 +39 @@ The [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaCon - * An AWS Identity and Access Management (IAM) user with administrator permissions. + * Administrator permissions in AWS Identity and Access Management (IAM) @@ -41 +41 @@ The [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaCon - * An instance that’s compatible with Amazon RDS or Aurora PostgreSQL. + * An instance that’s compatible with Amazon RDS or Aurora PostgreSQL @@ -43 +43 @@ The [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaCon - * A basic understanding of relational databases. + * Database architect or developer skills with Oracle and PostgreSQL @@ -65 +65 @@ This pattern was tested with the following versions: - * Oracle 12c EE 12.2 + * Oracle 19c EE @@ -211 +211 @@ Create a target PL/pgSQL block in PostgreSQL.| Create a target PL/pgSQL block in -Run the PL/pgSQL block.| Run the target PL/pgSQL block in PostgreSQL. If there are gaps between the index values of an associative array, no data is stored in those gaps. This allows the Oracle loop to iterate only through the index positions.| DBA +Run the PL/pgSQL block.| Run the target PL/pgSQL block in PostgreSQL. If there are gaps between the index values of an associative array, no data is stored in those gaps. This allows the PostgreSQL loop to iterate only through the index positions.| DBA @@ -272 +272 @@ Create a target PL/pgSQL block with an array and user-defined type.| To optimize -Run the PL/pgSQL block.| Run the target PL/pgSQL block. If there are gaps between the index values of an associative array, no data is stored in those gaps. This allows the Oracle loop to iterate only through the index positions.| DBA +Run the PL/pgSQL block.| Run the target PL/pgSQL block. If there are gaps between the index values of an associative array, no data is stored in those gaps. This allows the PostgreSQL loop to iterate only through the index positions.| DBA @@ -284,0 +285,17 @@ Review the output.| As shown in the following output, the user-defined type stor +## Troubleshooting + +Issue| Solution +---|--- +**Index out of bounds error** + + * **Issue** : `ERROR: array subscript out of bounds` when accessing array elements + * **Cause** : Attempting to access an index position that doesn't exist in the user-defined type array + +| You can validate index existence before access by using a `WHERE` clause filter on the `idx` column when unnesting the array, or you can implement boundary checks in your PL/pgSQL code. +**NULL value handling** + + * **Issue** : Unexpected NULL values appearing in query results when iterating through the array + * **Cause** : Array elements not properly initialized with both `idx` and `val` fields before appending + +| Make sure that both fields of the user-defined type are populated before using `array_append()`. Add explicit NULL checks as follows: `IF cc_append.val IS NOT NULL THEN cc := array_append(cc, cc_append); END IF;` + @@ -306,0 +324,20 @@ Review the output.| As shown in the following output, the user-defined type stor +## Additional information + + _Performance considerations_ + + * This approach reduces iteration overhead by 50% or more for sparse arrays compared to native PostgreSQL arrays with NULL placeholders. + + * Storage efficiency improves because only actual data is stored, not empty index positions. + + + + +_Compatibility notes_ + + * This pattern maintains Oracle's sparse array semantics while using PostgreSQL's native array capabilities. + + * The solution is compatible with all PostgreSQL versions that support user-defined composite types. + + + +