...

Additionally lightweight client tx coordination [2] need to be applicable together with partition aware request mapping. Lets use simple approach at first time: if affinity meta mapping is found from cache - direct mode is used and proxy otherwise. Another words: if modified rows are belongs to only one node - direct mode is used.

Protocol changes:  if server side client request handler detects that PA feature supported it need to prepare PA structure (if possible) and send it back to the client.

Client protocol changes:

• New feature flag SQL_PARTITION_AWARENESS

Let`s consider the scope of use cases this improvement can be applied to:

...

//  Colocation column values passed via params
INSERT INTO T (id, col1, col2) VALUES(1, ?, ?);

// Colocation column values partially passed via literals
INSERT INTO T (id, col1, col2) VALUES(1, ?, 100);
INSERT INTO T (id, col2) VALUES(?, 100), (?, 200);

Risks and Assumptions

// Describe project risks, such as API or binary compatibility issues, major protocol changes, etc.

Discussion Links

...

// Pure dynamic parameters
SELECT * FROM T WHERE col1 = ? AND col2 = ?;

// Colocation column values partially passed via literals
SELECT * FROM T WHERE col1 = ? AND col2 = 100;
SELECT * FROM T WHERE col1 = ? AND col2 = (SELECT 1);

// Colocation column values completely passed via literals
SELECT * FROM T WHERE col1 = 0 AND col2 = 100;

Applicabilityfor Delete, Update and Merge statements - need to be the same as for SELECT above.

Not applicable cases:

1. 
   

   Code Block
   language sql title All cases where calculations for colocation keys are requires additional data demand
   SELECT * FROM T WHERE col1 = ? AND col2 = (SELECT MIN(a) FROM T2);

   
   

2. 
   

   Code Block
   language sql title Insertion with explicit casts:
   INSERT INTO T (id, col2) VALUES(0, '100'::INTEGER);

   
   

3. 
   

   Code Block
   language sql title Forms of INSERT INTO SELECT where condition is not covers all colocation columns
   INSERT INTO T SELECT * FROM T2 WHERE condition;

   
   

4.  All forms of SELECT\DELETE\UPDATE\MERGE statements with IN, ANY, ALL, OR, Inner sub-queries, functions calls, functional defaults related to colocation keys.
5. Multi-statement and explicit transaction statements also need to bypass partition awareness and fall back into default implementation.
6. Probably no need to calculate partition aware information for Insert queries with pure literals defined for colocation columns.

Reference Links

[1] IEP-95: Client Partition Awareness

...