feat: support multiple PostgreSQL transaction options #1949

olavloite · 2022-07-16T18:21:20Z

PostgreSQL allows BEGIN and other transaction statements to set multiple transaction
options at once (e.g. both 'read only' and 'isolation level serializable'). This was
not supported by the Connection API, which only allowed one option at a time. The
Python psycopg2 driver generates statements that include multiple transaction options
in one statement.

This PR adds support for:

Begin transaction statements including both access mode and isolation level (these may be separated by both comma's and spaces, see https://www.postgresql.org/docs/current/sql-begin.html):
- begin read only isolation level serializable
- begin isolation level serializable, read write
Set transaction statements using the same transaction modes as for begin:
- set transaction read only, isolation level serializable
- set transaction isolation level serializable read write
Setting the DEFAULT_TRANSACTION_ISOLATION and DEFAULT_TRANSACTION_READ_ONLY properties

PostgreSQL allows BEGIN and other transaction statements to set multiple transaction options at once (e.g. both 'read only' and 'isolation level serializable'). This was not supported by the Connection API, which only allowed one option at a time. The Python psycopg2 driver generates statements that include multiple transaction options in one statement.

ansh0l

Have a couple of queries, but otherwise looks good.

ansh0l · 2022-07-19T19:06:11Z

google-cloud-spanner/src/main/java/com/google/cloud/spanner/connection/PgTransactionMode.java

+     * @return a string representation of this {@link TransactionMode} that can be used in a SQL
+     *     statement to set the transaction mode.
+     */
+    public String getStatementString() {


Where does this AccessMode.getStatementString get called?

It could be used to generate the SQL string that should be used (i.e. if there was a difference between toString() and this method), but it is not needed for these enums. Removed.

ansh0l · 2022-07-19T19:08:15Z

...-spanner/src/main/resources/com/google/cloud/spanner/connection/PG_ClientSideStatements.json

      "method": "statementBeginPgTransaction",
      "exampleStatements": [
        "begin", "start", "begin transaction", "start transaction", "begin work", "start work",
        "begin read only", "start read only", "begin transaction read only", "start transaction read only", "begin work read only", "start work read only",
        "begin read write", "start read write", "begin transaction read write", "start transaction read write", "begin work read write", "start work read write",
        "begin isolation level default", "start isolation level default", "begin transaction isolation level default", "start transaction isolation level default", "begin work isolation level default", "start work isolation level default",
-        "begin isolation level serializable", "start isolation level serializable", "begin transaction isolation level serializable", "start transaction isolation level serializable", "begin work isolation level serializable", "start work isolation level serializable"


We seem to be generating a bunch of these permutations on our own for exampleStatements, how do we track if we are not missing any specific permutation?

I guess the short answer is 'we don't', or at best; we do so during code reviews. The examples are based on the PostgreSQL documentation here: https://www.postgresql.org/docs/current/sql-start-transaction.html and on the part of that statement that we support. That is; we don't include example statements for read committed, as we don't support that isolation level.

See https://github.com/googleapis/repo-automation-bots/blob/main/packages/owl-bot/README.md

gauravsnj · 2022-07-21T10:46:15Z

...er/src/main/java/com/google/cloud/spanner/connection/ClientSideStatementValueConverters.java

+      }
+      if ("false".equalsIgnoreCase(value)
+          || "fals".equalsIgnoreCase(value)
+          || "fal".equalsIgnoreCase(value)


Can we add support for 'ON', 'OFF', 'yes', 'no', 0 and 1 while converting the boolean values? For the Psycopg2, adding only 'ON' and 'OFF' will do but we can add 'yes', 'no', 0 and 1 to make it fully generic.

Good point, added for:

on/off

1/0

yes/no

Including all unique prefixes (i.e. ye is also valid as true).

gauravsnj · 2022-07-21T10:50:58Z

...-spanner/src/main/resources/com/google/cloud/spanner/connection/PG_ClientSideStatements.json

+        "set default_transaction_read_only = t",
+        "set default_transaction_read_only = f",
+        "set default_transaction_read_only to 't'",
+        "set default_transaction_read_only to \"f\""


We can add a couple of example statements here related to ON and OFF

gauravsnj · 2022-07-21T11:05:20Z

...er/src/main/java/com/google/cloud/spanner/connection/ClientSideStatementValueConverters.java

+        return Boolean.TRUE;
+      }
+      if ("false".equalsIgnoreCase(value)
+          || "fals".equalsIgnoreCase(value)


I don't think that the values like "tru", "fal", "tr" are supported without single quotes. So, tru or fal shouldn't work

Strangely enough, they are. I've tried a multitude of different statements with a real PG instance. See below for the results:

knut-test-db=# set default_transaction_read_only to "on"; SET knut-test-db=# set default_transaction_read_only to tr; SET knut-test-db=# set default_transaction_read_only to ' true '; ERROR: parameter "default_transaction_read_only" requires a Boolean value knut-test-db=# set default_transaction_read_only to "true"; SET knut-test-db=# set default_transaction_read_only to 'true'; SET knut-test-db=# set default_transaction_read_only to ye; SET knut-test-db=# set default_transaction_read_only to 'ye'; SET

So TLDR:

Both single and double quotes are allowed (which is strange, as double quotes would normally be used to quote identifiers)

Unique prefixes like tr and n are allowed both with and without quotes.

Spaces inside quotes are not allowed, so ' true ' is not a valid value.

That's strange because once I tested inserting boolean values in some table and the results were totally opposite :) Btw thanks for clarifying

gauravsnj · 2022-07-21T11:07:20Z

...er/src/main/java/com/google/cloud/spanner/connection/ClientSideStatementValueConverters.java

+              || (value.startsWith("\"") && value.endsWith("\"")))) {
+        value = value.substring(1, value.length() - 1);
+      }
+      if ("true".equalsIgnoreCase(value)


I think we should also trim the string to consider the cases like ' true '

(See also above). PostgreSQL does not consider that to be a valid boolean value. When the value is quoted with either single or double quotes, it may not contain any spaces.

gauravsnj · 2022-07-21T11:11:16Z

...er/src/main/java/com/google/cloud/spanner/connection/ClientSideStatementValueConverters.java

+      }
+      if (value.length() > 1
+          && ((value.startsWith("'") && value.endsWith("'"))
+              || (value.startsWith("\"") && value.endsWith("\"")))) {


I think Double Quotes("") are used to identify objects inside the database like column names, table names etc. So, "true" shouldn't work

I agree that it strange, but it actually allowed (see above)

gauravsnj

LGTM

Source-Link: googleapis/synthtool@4b49307 Post-Processor: gcr.io/cloud-devrel-public-resources/owlbot-java:latest@sha256:25b384ee1674eda3984ec41c15b514a63bbeb5eda4d57c73c7e6f5adef2fd2f1

…3013) Source-Link: googleapis/synthtool@4b49307 Post-Processor: gcr.io/cloud-devrel-public-resources/owlbot-java:latest@sha256:25b384ee1674eda3984ec41c15b514a63bbeb5eda4d57c73c7e6f5adef2fd2f1 Co-authored-by: Owl Bot <gcf-owl-bot[bot]@users.noreply.github.com>

olavloite added 2 commits July 16, 2022 20:05

fix: remove duplicated statement

8868723

product-auto-label bot added size: xl Pull request size is extra large. api: spanner Issues related to the googleapis/java-spanner API. labels Jul 16, 2022

chore: run code formatter

2cc7ac2

olavloite marked this pull request as ready for review July 16, 2022 18:30

olavloite requested review from a team as code owners July 16, 2022 18:30

olavloite requested a review from gauravsnj July 16, 2022 18:30

ansh0l approved these changes Jul 19, 2022

View reviewed changes

ansh0l requested a review from rajatbhatta July 19, 2022 19:15

olavloite and others added 4 commits July 20, 2022 10:28

fix: support 'to' in set statements

6ff3a60

🦉 Updates from OwlBot post-processor

9e0581d

See https://github.com/googleapis/repo-automation-bots/blob/main/packages/owl-bot/README.md

Merge branch 'main' into pg-transaction-options

5521db4

🦉 Updates from OwlBot post-processor

e27c841

See https://github.com/googleapis/repo-automation-bots/blob/main/packages/owl-bot/README.md

gauravsnj reviewed Jul 21, 2022

View reviewed changes

fix: support on/off yes/no 1/0

93f6f5a

gauravsnj approved these changes Jul 21, 2022

View reviewed changes

rajatbhatta approved these changes Jul 22, 2022

View reviewed changes

olavloite merged commit 8b99f30 into main Jul 22, 2022

olavloite deleted the pg-transaction-options branch July 22, 2022 19:36

release-please bot mentioned this pull request Jul 22, 2022

chore(main): release 6.28.0 #1958

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat: support multiple PostgreSQL transaction options #1949

feat: support multiple PostgreSQL transaction options #1949

olavloite commented Jul 16, 2022

ansh0l left a comment

ansh0l Jul 19, 2022

olavloite Jul 20, 2022

ansh0l Jul 19, 2022

olavloite Jul 20, 2022

gauravsnj Jul 21, 2022 •

edited

Loading

olavloite Jul 21, 2022

gauravsnj Jul 21, 2022

olavloite Jul 21, 2022

gauravsnj Jul 21, 2022 •

edited

Loading

olavloite Jul 21, 2022

gauravsnj Jul 21, 2022

gauravsnj Jul 21, 2022 •

edited

Loading

olavloite Jul 21, 2022

gauravsnj Jul 21, 2022

olavloite Jul 21, 2022

gauravsnj left a comment

feat: support multiple PostgreSQL transaction options #1949

feat: support multiple PostgreSQL transaction options #1949

Conversation

olavloite commented Jul 16, 2022

ansh0l left a comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

gauravsnj Jul 21, 2022 • edited Loading

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

gauravsnj Jul 21, 2022 • edited Loading

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

gauravsnj Jul 21, 2022 • edited Loading

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

gauravsnj left a comment

Choose a reason for hiding this comment

gauravsnj Jul 21, 2022 •

edited

Loading

gauravsnj Jul 21, 2022 •

edited

Loading

gauravsnj Jul 21, 2022 •

edited

Loading