# Querying Aurora PostgreSQL Limitless Database
Querying Limitless Database

Aurora PostgreSQL Limitless Database is compatible with PostgreSQL syntax for queries. You can query your Limitless Database using `psql` or any other connection utility that works with PostgreSQL. To run queries, you connect to the limitless endpoint as shown in [Connecting to your Aurora PostgreSQL Limitless Database DB cluster](limitless-shard.md#limitless-endpoint).

All PostgreSQL `SELECT` queries are supported in Aurora PostgreSQL Limitless Database. However, queries are performed on two layers:

1. Router to which the client sends the query

1. Shards where the data is located

Performance depends on querying the database in a way that allows it to achieve a high degree of simultaneous processing of different queries on different shards. Queries are first parsed in the distributed transaction layer (router). Before planning the query execution, there's an analysis phase to identify the location for all relations participating in the query. If all relations are sharded tables with a filtered shard key on the same shard, or reference tables, then query planning is skipped on the router layer and completely pushed down to the shard for planning and execution. This process reduces the number of round trips between different nodes (router and shard) and results in better performance in most cases. For more information, see [Single-shard queries in Aurora PostgreSQL Limitless Database](limitless-query.single-shard.md).

**Note**  
There can be specific cases, such as a [Cartesian product](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-FROM) (cross join), where the query performs better by retrieving data separately from the shard.

For more information on query execution plans, see [EXPLAIN](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.EXPLAIN) in the [Aurora PostgreSQL Limitless Database referenceLimitless Database reference](limitless-reference.md). For general information on queries, see [Queries](https://www.postgresql.org/docs/current/queries-overview.html) in the PostgreSQL documentation.

**Topics**
+ [

# Single-shard queries in Aurora PostgreSQL Limitless Database
](limitless-query.single-shard.md)
+ [

# Distributed queries in Aurora PostgreSQL Limitless Database
](limitless-query.distributed.md)
+ [

# Distributed query tracing in PostgreSQL logs in Aurora PostgreSQL Limitless Database
](limitless-query.tracing.md)
+ [

# Distributed deadlocks in Aurora PostgreSQL Limitless Database
](limitless-query.deadlocks.md)

# Single-shard queries in Aurora PostgreSQL Limitless Database
Single-shard queries

A *single-shard query* is a query that can be run directly on a shard while maintaining SQL [ACID](https://en.wikipedia.org/wiki/ACID) semantics. When such a query is encountered by the query planner on the router, the planner detects it and proceeds to push down the entire SQL query to the corresponding shard.

This optimization reduces the number of network round trips from the router to the shard, improving the performance. Currently this optimization is performed for `INSERT`, `SELECT`, `UPDATE`, and `DELETE` queries.

**Topics**
+ [

## Single-shard query examples
](#limitless-query.single-shard.examples)
+ [

## Restrictions for single-shard queries
](#limitless-query.single-shard.restrictions)
+ [

## Fully qualified (explicit) joins
](#limitless-query.single-shard.fq)
+ [

## Setting an active shard key
](#limitless-query.single-shard.active)

## Single-shard query examples


In the following examples, we have the sharded table `customers`, with the shard key `customer_id`, and the reference table `zipcodes`.

**SELECT**  

```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM customers WHERE customer_id = 100;

                       QUERY PLAN                        
---------------------------------------------------------
 Foreign Scan
   Output: customer_id, other_id, customer_name, balance
   Remote SQL:  SELECT customer_id,
     other_id,
     customer_name,
     balance
    FROM public.customers
   WHERE (customer_id = 100)
 Single Shard Optimized
(9 rows)
```

```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM orders
    LEFT JOIN zipcodes ON orders.zipcode_id = zipcodes.zipcode_id
    WHERE customer_id = 11;

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: customer_id, order_id, zipcode_id, customer_name, balance, zipcodes.zipcode_id, zipcodes.city
   Remote SQL:  SELECT orders.customer_id,
     orders.order_id,
     orders.zipcode_id,
     orders.customer_name,
     orders.balance,
     zipcodes.zipcode_id,
     zipcodes.city
    FROM (public.orders
      LEFT JOIN public.zipcodes ON ((orders.zipcode_id = zipcodes.zipcode_id)))
   WHERE (orders.customer_id = 11)
 Single Shard Optimized
(13 rows)
```

**INSERT**  

```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO customers
    (customer_id, other_id, customer_name, balance)
    VALUES (1, 10, 'saikiran', 1000);

                      QUERY PLAN                       
-------------------------------------------------------
 Insert on public.customers
   ->  Result
         Output: 1, 10, 'saikiran'::text, '1000'::real
 Single Shard Optimized
(4 rows)
```

**UPDATE**  

```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) UPDATE orders SET balance = balance + 100
    WHERE customer_id = 100;

                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Update on public.orders
   Foreign Update on public.orders_fs00002 orders_1
   ->  Foreign Update
         Remote SQL:  UPDATE public.orders SET balance = (balance + (100)::double precision)
   WHERE (customer_id = 100)
 Single Shard Optimized
(6 rows)
```

**DELETE**  

```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM orders
    WHERE customer_id = 100 and balance = 0;

                             QUERY PLAN                              
---------------------------------------------------------------------
 Delete on public.orders
   Foreign Delete on public.orders_fs00002 orders_1
   ->  Foreign Delete
         Remote SQL:  DELETE FROM public.orders
   WHERE ((customer_id = 100) AND (balance = (0)::double precision))
 Single Shard Optimized
(6 rows)
```

## Restrictions for single-shard queries


Single-shard queries have the following restrictions:

**Functions**  
If a single-shard query contains a function, the query qualifies for single-shard optimization only if one of the following conditions applies:  
+ The function is immutable. For more information, see [Function volatility](limitless-reference.DDL-limitations.md#limitless-function-volatility).
+ The function is mutable, but is registered in the `rds_aurora.limitless_distributed_functions` view. For more information, see [Function distribution](limitless-reference.DDL-limitations.md#limitless-function-distribution).

**Views**  
If a query contains one or more views, single-shard optimization is disabled for the query if it has one of the following conditions:  
+ Any view has the `security_barrier` attribute.
+ Objects used in the query require multiple user privileges. For example, a query contains two views, and the views are run under two different users.

```
CREATE VIEW v1 AS SELECT customer_name FROM customers c WHERE c.customer_id =  1;
CREATE VIEW v2 WITH (security_barrier) AS SELECT customer_name FROM customers c WHERE c.customer_id =  1;

postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v1;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0)
   Output: customer_name
   Remote Plans from Shard postgres_s3:
         Seq Scan on public.customers_ts00001 c  (cost=0.00..24.12 rows=6 width=32)
           Output: c.customer_name
           Filter: (c.customer_id = 1)
         Query Identifier: -6005737533846718506
   Remote SQL:  SELECT customer_name
    FROM ( SELECT c.customer_name
            FROM public.customers c
           WHERE (c.customer_id = 1)) v1
 Query Identifier: -5754424854414896228
(12 rows)


postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v2;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Foreign Scan on public.customers_fs00001 c  (cost=100.00..128.41 rows=7 width=32)
   Output: c.customer_name
   Remote Plans from Shard postgres_s3:
         Seq Scan on public.customers_ts00001 customers  (cost=0.00..24.12 rows=6 width=32)
           Output: customers.customer_name
           Filter: (customers.customer_id = 1)
         Query Identifier: 4136563775490008117
   Remote SQL: SELECT customer_name FROM public.customers WHERE ((customer_id = 1))
 Query Identifier: 5056054318010163757
(9 rows)
```

**PREPARE and EXECUTE statements**  
Aurora PostgreSQL Limitless Database supports single-shard optimization for prepared `SELECT`, `UPDATE`, and `DELETE` statements.  
However, if you use prepared statements for `PREPARE` and `EXECUTE` with `plan_cache_mode` set to `'force_generic_plan'`, the query planner rejects single-shard optimization for that query. 

**PL/pgSQL**  
Queries with PL/pgSQL variables are run as implicitly prepared statements. If a query contains any PL/pgSQL variables, the query planner rejects single-shard optimization.  
Optimization is supported in the PL/pgSQL block if the statement doesn't contain any PL/pgSQL variables.

## Fully qualified (explicit) joins


Single-shard optimization is based on partition eliminations. The PostgreSQL optimizer eliminates partitions based on constant conditions. If Aurora PostgreSQL Limitless Database finds that all of the remaining partitions and tables are on the same shard, it marks the query eligible for single-shard optimization. All filter conditions must be explicit for partition elimination to work. Aurora PostgreSQL Limitless Database can't eliminate partitions without one or more join predicates or filter predicates on the shard keys of every sharded table in the statement.

Assume that we've partitioned the `customers`, `orders`, and `order_details` tables based on the `customer_id` column. In this schema, the application tries to keep all of the data for a customer on a single shard.

Consider the following query:

```
SELECT * FROM 
    customers c, orders o, order_details od 
WHERE c.customer_id = o.customer_id
    AND od.order_id = o.order_id
    AND c.customer_id = 1;
```

This query retrieves all of the data for a customer (`c.customer_id = 1`). Data for this customer is on a single shard, but Aurora PostgreSQL Limitless Database doesn't qualify this query as a single-shard query. The optimizer process for the query is as follows:

1. The optimizer can eliminate partitions for `customers` and `orders` based on the following condition:

   ```
   c.customer_id = 1
   c.customer_id = o.customer_id
   o.customer_id =  1 (transitive implicit condition)
   ```

1. The optimizer can't eliminate any partitions for `order_details`, because there's no constant condition on the table.

1. The optimizer concludes that it has read all of the partitions from `order_details`. Therefore, the query can't be qualified for single-shard optimization.

To make this a single-shard query, we add the following explicit join condition:

```
o.customer_id = od.customer_id
```

The changed query looks like this:

```
SELECT * FROM 
    customers c, orders o,  order_details od 
WHERE c.customer_id = o.customer_id
     AND o.customer_id = od.customer_id
     AND od. order_id = o. order_id
 AND c.customer_id =  1;
```

Now the optimizer can eliminate partitions for `order_details`. The new query becomes a single-shard query and qualifies for optimization.

## Setting an active shard key


This feature allows you to set a single shard key while querying the database, causing all `SELECT` and DML queries to be appended with the shard key as a constant predicate. This feature is useful if you've migrated to Aurora PostgreSQL Limitless Database and have denormalized the schema by adding shard keys to tables.

You can append a shard key predicate automatically to the existing SQL logic, without changing the semantics of the queries. Appending an active shard key predicate is done only for [compatible tables](#active-shard-key-compatible-tables).

The active shard key feature uses the `rds_aurora.limitless_active_shard_key` variable, which has the following syntax:

```
SET [session | local] rds_aurora.limitless_active_shard_key = '{"col1_value", "col2_value", ...}';
```

Some considerations about active shard keys and foreign keys:
+ A sharded table can have a foreign key constraint if the parent and child tables are collocated and the foreign key is a superset of the shard key.
+ A sharded table can have a foreign key constraint to a reference table.
+ A reference table can have a foreign key constraint to another reference table.

Assume that we have a `customers` table that is sharded on the `customer_id` column.

```
BEGIN;
SET local rds_aurora.limitless_create_table_mode='sharded';
SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}';
CREATE TABLE customers(customer_id int PRIMARY KEY, name text , email text);
COMMIT;
```

 With an active shard key set, queries have the following transformations.

**SELECT**  

```
SET rds_aurora.limitless_active_shard_key = '{"123"}';
SELECT * FROM customers;

-- This statement is changed to:
SELECT * FROM customers WHERE customer_id = '123'::int;
```

**INSERT**  

```
SET rds_aurora.limitless_active_shard_key = '{"123"}';
INSERT INTO customers(name, email) VALUES('Alex', 'alex@example.com');

-- This statement is changed to:
INSERT INTO customers(customer_id, name, email) VALUES('123'::int, 'Alex', 'alex@example.com');
```

**UPDATE**  

```
SET rds_aurora.limitless_active_shard_key = '{"123"}';
UPDATE customers SET email = 'alex_new_email@example.com';

-- This statement is changed to:
UPDATE customers SET email = 'alex_new_email@example.com' WHERE customer_id = '123'::int;
```

**DELETE**  

```
SET rds_aurora.limitless_active_shard_key = '{"123"}';
DELETE FROM customers;

-- This statement is changed to:
DELETE FROM customers WHERE customer_id = '123'::int;
```

**Joins**  
When performing join operations on tables with an active shard key, the shard key predicate is automatically added to all tables involved in the join. This automatic addition of the shard key predicate occurs only when all tables in the query belong to the same collocation group. If the query involves tables from different collocation groups, an error is raised instead.  
Assume that we also have `orders` and `order_details` tables that are collocated with the `customers` table.  

```
SET local rds_aurora.limitless_create_table_mode='sharded';
SET local rds_aurora.limitless_create_table_collocate_with='customers';
SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}';
CREATE TABLE orders (id int , customer_id int, total_amount int, date date);
CREATE TABLE order_details (id int , order_id int, customer_id int, product_name VARCHAR(100), price int);
COMMIT;
```
Retrieve the last 10 order invoices for a customer whose customer ID is 10.  

```
SET rds_aurora.limitless_active_shard_key = '{"10"}';
SELECT * FROM customers, orders, order_details WHERE
    orders.customer_id = customers.customer_id AND
    order_details.order_id = orders.order_id AND
    customers.customer_id = 10
    order by order_date limit 10;
```
This query is transformed into the following:  

```
SELECT * FROM customers, orders, order_details WHERE
    orders.customer_id = customers.customer_id AND
    orders.order_id = order_details.order_id AND
    customers.customer_id = 10 AND
    order_details.customer_id = 10 AND
    orders.customer_id = 10 AND
    ORDER BY "order_date" LIMIT 10;
```

**Active shard key compatible tables**  
The shard key predicate is added only to tables that are compatible with the active shard key. A table is considered compatible if it has the same number of columns in its shard key as specified in the `rds_aurora.limitless_active_shard_key` variable. If the query involves tables that are incompatible with the active shard key, the system raises an error instead of proceeding with the query.  
For example:  

```
-- Compatible table
SET rds_aurora.limitless_active_shard_key = '{"10"}';

-- The following query works because the customers table is sharded on one column.
SELECT * FROM customers;
  
-- Incompatible table
SET rds_aurora.limitless_active_shard_key = '{"10","20"}';

-- The following query raises a error because the customers table isn't sharded on two columns.
 SELECT * FROM customers;
```

# Distributed queries in Aurora PostgreSQL Limitless Database
Distributed queries

Distributed queries run on a router and more than one shard. The query is received by one of the routers. The router creates and manages the distributed transaction, which is sent to the participating shards. The shards create a local transaction with the context provided by the router, and the query is run.

When the transaction is committed, the router uses an optimized two-phase commit protocol if needed, and time-based Multi Version Concurrency Control (MVCC) to provide [ACID](https://en.wikipedia.org/wiki/ACID) semantics in a distributed database system.

Time-based MVCC records the commit time for each transaction and uses the transaction start time to generate the data snapshot time. To identify whether a transaction is committed (visible) given a reader’s snapshot, the database compares its commit time with the snapshot time. If its commit time is less than the reader’s snapshot time, it is visible; otherwise, invisible. Under this protocol, you will always expect to see strongly consistent data on Aurora PostgreSQL Limitless Database.

# Distributed query tracing in PostgreSQL logs in Aurora PostgreSQL Limitless Database
Distributed query tracing

Distributed query tracing is a tool to trace and correlate queries in PostgreSQL logs across Aurora PostgreSQL Limitless Database. In Aurora PostgreSQL, you use the transaction ID to identify a transaction. But in Aurora PostgreSQL Limitless Database, the transaction ID can repeat across various routers. Therefore, we recommend that you use the tracing ID instead in Limitless Database.

The key use cases are the following:
+ Use the `rds_aurora.limitless_get_last_trace_id()` function to find the unique tracing ID of the last query run in the current session. Then search the DB cluster log group in Amazon CloudWatch Logs using that tracing ID to find all the related logs.

  You can use the `log_min_messages` and `log_min_error_statement` parameters together to control the volume of logs printed and print a statement that contains the tracing ID.
+ Use the `log_min_duration_statement` parameter to determine the run time above which all of the queries print their run duration and tracing ID. This run time can then be searched in the DB cluster log group in CloudWatch Logs to help determine the bottleneck nodes and planner optimization efforts.

  The `log_min_duration_statement` parameter enables the tracing ID for all nodes regardless of the values of `log_min_messages` and `log_min_error_statement` parameters.

**Topics**
+ [

## Tracing ID
](#limitless-query.tracing.ID)
+ [

## Using query tracing
](#limitless-query.tracing.using)
+ [

## Log examples
](#limitless-query.tracing.examples)

## Tracing ID


Central to this feature is a unique identifier known as the *tracing ID*. The tracing ID is a 31-digit string appended to the STATEMENT log lines of PostgreSQL logs, acting as a precise identifier for correlating logs related to a specific query execution. Examples are `1126253375719408504000000000011` and `1126253375719408495000000000090`.

The tracing ID is composed of the following:
+ Transaction identifier – The first 20 digits, uniquely identifying the transaction.
+ Command identifier – The first 30 digits, indicating an individual query within a transaction.

  If more than 4,294,967,294 queries are run inside an explicit transaction block, the command Identifier wraps around to `1`. When this happens, you're notified by the following `LOG` message in the PostgreSQL log:

  ```
  wrapping around the tracing ID back to 1 after running 4294967294 (4.2 billion or 2^32-2) queries inside of an explicit transaction block
  ```
+ Node type identifier – The last digit, indicating whether the node is functioning as a coordinator router (`1`) or a participant node (`0`).

The following examples illustrate the components of the tracing ID:
+ `1126253375719408504000000000011`:
  + Transaction identifier – `1126253375719408504`
  + Command identifier – `112625337571940850400000000001` indicates the first command in the transaction block
  + Node type identifier – `1` indicates a coordinator router
+ `1126253375719408495000000000090`:
  + Transaction identifier – `1126253375719408495`
  + Command identifier – `112625337571940849500000000009` indicates the ninth command in the transaction block
  + Node type identifier – `0` indicates a participant node

## Using query tracing


Perform the following tasks to use query tracing:

1. Make sure that tracing is enabled.

   You can check using the following command:

   ```
   SHOW rds_aurora.limitless_log_distributed_trace_id;
   ```

   It's enabled by default (`on`). If it's not enabled, set it using the following command:

   ```
   SET rds_aurora.limitless_log_distributed_trace_id = on;
   ```

1. Control the volume of logs printed by configuring the log severity level.

   The log volume is controlled by the `log_min_messages` parameter. The `log_min_error_statement` parameter is used to print the `STATEMENT` line with the tracing ID. Both are set to `ERROR` by default. You can check using the following commands:

   ```
   SHOW log_min_messages;
   SHOW log_min_error_statement;
   ```

   To update the severity level and print the `STATEMENT` line for the current session, use the following commands with one of these severity levels:

   ```
   SET log_min_messages = 'DEBUG5 | DEBUG4 | DEBUG3 | DEBUG2 | DEBUG1 | INFO | NOTICE | WARNING | ERROR | LOG | FATAL | PANIC';
   SET log_min_error_statement = 'DEBUG5 | DEBUG4 | DEBUG3 | DEBUG2 | DEBUG1 | INFO | NOTICE | WARNING | ERROR | LOG | FATAL | PANIC';
   ```

   For example:

   ```
   SET log_min_messages = 'WARNING';
   SET log_min_error_statement = 'WARNING';
   ```

1. Enable printing the tracing ID in the logs above a specific run time.

   The `log_min_duration_statement` parameter can be changed to the minimum query run time above which the query prints a log line with the run duration, along with the tracing IDs across the DB cluster. This parameter is set to `-1` by default, which means it's disabled. You can check using the following command:

   ```
   SHOW log_min_duration_statement;
   ```

   Changing it to `0` prints the duration and tracing ID in the logs for every query across the DB cluster. You can set it to `0` for the current session by using the following command:

   ```
   SET log_min_duration_statement = 0;
   ```

1. Obtain the tracing ID.

   After running a query (even inside an explicit transaction block), call the `rds_aurora.limitless_get_last_trace_id` function to obtain the tracing ID of the last query run:

   ```
   SELECT * FROM rds_aurora.limitless_get_last_trace_id();
   ```

   This function returns the transaction identifier and the command identifier. It doesn't return the node type identifier.

   ```
   => SELECT * FROM customers;
    customer_id | fname | lname 
   -------------+-------+-------
   (0 rows)
   
   => SELECT * FROM rds_aurora.limitless_get_last_trace_id();
    transaction_identifier |       command_identifier       
   ------------------------+--------------------------------
    10104661421959001813   | 101046614219590018130000000001
   (1 row)
   ```

   The function returns a blank line for nondistributed queries, as they don't have a tracing ID.

   ```
   => SET search_path = public;
   SET
   
   => SELECT * FROM rds_aurora.limitless_get_last_trace_id();
    transaction_identifier | command_identifier 
   ------------------------+--------------------
                           | 
   (1 row)
   ```
**Note**  
For VACUUM and ANALYZE queries, the duration statement isn't logged with the tracing ID. Therefore, `limitless_get_last_trace_id()` doesn't return the tracing ID. If VACUUM or ANALYZE is a long-running operation, you can use the following query to obtain the tracing ID for that operation:  

   ```
   SELECT * FROM rds_aurora.limitless_stat_activity 
   WHERE distributed_tracing_id IS NOT NULL;
   ```
If the server stops before you can find the last tracing ID, you will have to search the PostgreSQL logs manually to find the tracing IDs in the logs from right before the failure.

1. Search for the tracing ID across the DB cluster logs using CloudWatch.

   Use [CloudWatch Insights](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/AnalyzingLogData.html) to query the DB cluster's log group, as shown in the following examples.
   + Query for a particular transaction identifier and all of the commands run inside it:

     ```
     fields @timestamp, @message
     | filter @message like /10104661421959001813/
     | sort @timestamp desc
     ```
   + Query for a particular command identifier:

     ```
     fields @timestamp, @message
     | filter @message like /101046614219590018130000000001/
     | sort @timestamp desc
     ```

1. Examine all of the logs across the DB cluster produced by the distributed query.

## Log examples


The following examples show the use of query tracing.

### Correlating logs for error-prone queries


In this example the `TRUNCATE` command is run on the `customers` table when that table doesn't exist.

**Without query tracing**  
PostgreSQL log file on the coordinator router:  

```
2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[27503]:ERROR: failed to execute remote query
2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[27503]:DETAIL: relation "public.customers" does not exist
2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[27503]:CONTEXT: remote SQL command: truncate public.customers;
2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[27503]:STATEMENT: truncate customers;
```
PostgreSQL log file on a participant shard:  

```
2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[ 27503]:ERROR: failed to execute remote query
2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[ 27503]:STATEMENT: truncate customers;
```
These logs are typical. They lack the precise identifiers needed to easily correlate queries across the DB cluster.

**With query tracing**  
PostgreSQL log file on the coordinator router:  

```
2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:ERROR: failed to execute remote query
2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:DETAIL: relation "public.customers" does not exist
2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:CONTEXT: remote SQL command: truncate public.customers;
2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:STATEMENT: /* tid = 1126253375719408502700000000011 */ truncate customers;
```
PostgreSQL log file on a participant shard:  

```
2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:ERROR:  failed to execute remote query
2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:STATEMENT:  /* tid = 1126253375719408502700000000010 */ truncate customers;
```
In the presence of query tracing, each log line is appended with a 31-digit unique identifier. Here, `1126253375719408502700000000011` and `1126253375719408502700000000010` represent the tracing IDs for the coordinator and participant nodes, respectively.  
+ Transaction identifier – `11262533757194085027`
+ Command identifier: –`112625337571940850270000000001`
+ Node type identifier – The last digit, `1` or `0`, indicates a coordinator router and participant node, respectively.

### Correlating logs to find the query run time on various nodes


In this example, the `log_min_duration_statement` parameter has been updated to `0` to print the duration for all queries.

**Without query tracing**  

```
2024-01-15 07:28:46 UTC:[local]:postgres@postgres_limitless:[178322]:LOG: duration: 12.779 ms statement: select * from customers;
```

**With query tracing**  
PostgreSQL log file on the coordinator router:  

```
2024-01-15 07:32:08 UTC:[local]:postgres@postgres_limitless:[183877]:LOG: duration: 12.618 ms statement: /* tid = 0457669566240497088400000000011 */ select * from customers;
```
PostgreSQL log file on a participant shard:  

```
2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.279 ms statement: /* tid = 0457669566240497088400000000010 */ START TRANSACTION ISOLATION LEVEL READ COMMITTED
2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.249 ms parse <unnamed>: SELECT customer_id, fname, lname FROM public.customers
2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.398 ms bind <unnamed>/c1: SELECT customer_id, fname, lname FROM public.customers
2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.019 ms execute <unnamed>/c1: SELECT customer_id, fname, lname FROM public.customers
2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.073 ms statement: /* tid = 0457669566240497088400000000010 */ COMMIT TRANSACTION
```

# Distributed deadlocks in Aurora PostgreSQL Limitless Database
Distributed deadlocks

In a DB shard group, deadlocks can occur between transactions that are distributed among different routers and shards. For example, two concurrent distributed transactions that span two shards are run, as shown in the following figure.

![\[Distributed deadlock on two distributed transactions.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_distributed_transaction_deadlock.png)


The transactions lock tables and create wait events in the two shards as follows:

1. Distributed transaction 1:

   ```
   UPDATE table SET value = 1 WHERE key = 'shard1_key';
   ```

   This holds a lock on shard 1.

1. Distributed transaction 2:

   ```
   UPDATE table SET value = 2 WHERE key = 'shard2_key';
   ```

   This holds a lock on shard 2.

1. Distributed transaction 1:

   ```
   UPDATE table SET value = 3 WHERE key = 'shard2_key';
   ```

   Distributed transaction 1 is waiting on shard 2.

1. Distributed transaction 2:

   ```
   UPDATE table SET value = 4 WHERE key = 'shard1_key';
   ```

   Distributed transaction 2 is waiting on shard 1.

In this scenario, neither shard 1 nor shard 2 sees the problem: transaction 1 is waiting for transaction 2 on shard 2, and transaction 2 is waiting for transaction 1 on shard 1. From a global view, transaction 1 is waiting for transaction 2, and transaction 2 is waiting for transaction 1. This situation where two transactions on two different shards are waiting for each other is called a distributed deadlock.

Aurora PostgreSQL Limitless Database can detect and resolve distributed deadlocks automatically. A router in the DB shard group is notified when a transaction is waiting too long to acquire a resource. The router receiving the notification starts to collect the necessary information from all of the routers and shards within the DB shard group. The router then proceeds to end transactions that are participating in a distributed deadlock, until the rest of the transactions in the DB shard group can proceed without being blocked by each other.

You receive the following error when your transaction was part of a distributed deadlock, and was then ended by the router:

```
ERROR: aborting transaction participating in a distributed deadlock
```

The `rds_aurora.limitless_distributed_deadlock_timeout` DB cluster parameter sets the time for each transaction to wait on a resource before notifying the router to check for a distributed deadlock. You can increase the parameter value if your workload is less prone to deadlock situations. The default is `1000` milliseconds (1 second).

The distributed deadlock cycle is published to the PostgreSQL logs when a cross-node deadlock is found and resolved. The information about each process that's part of the deadlock includes the following:
+ Coordinator node that started the transaction
+ Virtual transaction ID (xid) of the transaction on the coordinator node, in the format `backend_id/backend_local_xid`
+ Distributed session ID of the transaction