Repository: GoogleCloudPlatform/pgadapter
Pull request #336 (2022-08-27 15:03:01)

feat: allow setting the server version number in startup msg
Make the server_version setting a setting with 'backend' context. This
means that it may be set in the connection request, so a connection can
determine which version PGAdapter should claim to be.

codecov[bot]: # [Codecov](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/336?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) Report
> Merging [#336](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/336?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) (47afaf6) into [postgresql-dialect](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/commit/4b01399e5f127900d4d9f9039f5972e07577c39e?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) (4b01399) will **decrease** coverage by `0.01%`.
> The diff coverage is `91.30%`.

```diff
@@                   Coverage Diff                    @@
##             postgresql-dialect     #336      +/-   ##
========================================================
- Coverage                 83.95%   83.94%   -0.02%     
- Complexity                 1593     1595       +2     
========================================================
  Files                       114      114              
  Lines                      5417     5437      +20     
  Branches                    727      728       +1     
========================================================
+ Hits                       4548     4564      +16     
- Misses                      643      645       +2     
- Partials                    226      228       +2     
```

| Flag | Coverage Δ | |
|---|---|---|
| all_tests | `83.94% <91.30%> (-0.02%)` | :arrow_down: |

Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#carryforward-flags-in-the-pull-request-comment) to find out more.

| [Impacted Files](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/336?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) | Coverage Δ | |
|---|---|---|
| [...ud/spanner/pgadapter/metadata/OptionsMetadata.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/336/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL21ldGFkYXRhL09wdGlvbnNNZXRhZGF0YS5qYXZh) | `77.64% <75.00%> (+0.08%)` | :arrow_up: |
| [...gle/cloud/spanner/pgadapter/session/PGSetting.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/336/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3Nlc3Npb24vUEdTZXR0aW5nLmphdmE=) | `91.32% <90.32%> (-1.80%)` | :arrow_down: |
| [.../cloud/spanner/pgadapter/session/SessionState.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/336/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3Nlc3Npb24vU2Vzc2lvblN0YXRlLmphdmE=) | `99.07% <100.00%> (+0.03%)` | :arrow_up: |
| [...panner/pgadapter/statements/BackendConnection.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/336/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3N0YXRlbWVudHMvQmFja2VuZENvbm5lY3Rpb24uamF2YQ==) | `90.69% <100.00%> (+0.02%)` | :arrow_up: |
| [...anner/pgadapter/wireprotocol/BootstrapMessage.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/336/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3dpcmVwcm90b2NvbC9Cb290c3RyYXBNZXNzYWdlLmphdmE=) | `95.55% <100.00%> (+0.10%)` | :arrow_up: |
| [...spanner/pgadapter/wireprotocol/StartupMessage.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/336/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3dpcmVwcm90b2NvbC9TdGFydHVwTWVzc2FnZS5qYXZh) | `87.75% <100.00%> (ø)` | |

:mega: We’re building smart automated test selection to slash your CI/CD build times. [Learn more](https://about.codecov.io/iterative-testing/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform)


Patch:
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadata.java b/src/main/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadata.java
index 19bf81cb..3176485f 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadata.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadata.java
@@ -794,15 +794,19 @@ public class OptionsMetadata {
   }
 
   public String getServerVersionNum() {
-    String[] components = serverVersion.split("\\.");
+    return toServerVersionNum(this.serverVersion);
+  }
+
+  public static String toServerVersionNum(String version) {
+    String[] components = version.split("\\.");
     if (components.length >= 2) {
       int major = tryParseInt(components[0]);
-      int minor = tryParseInt(components[1]);
+      int minor = tryParseInt(components[1].split("\\s+")[0]);
       if (major > -1 && minor > -1) {
         return String.valueOf(major * 10000 + minor);
       }
     }
-    return serverVersion;
+    return version;
   }
 
   private static int tryParseInt(String value) {
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/session/PGSetting.java b/src/main/java/com/google/cloud/spanner/pgadapter/session/PGSetting.java
index 840d8a07..7d3f79cd 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/session/PGSetting.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/session/PGSetting.java
@@ -14,6 +14,7 @@
 
 package com.google.cloud.spanner.pgadapter.session;
 
+import com.google.api.client.util.Strings;
 import com.google.api.core.InternalApi;
 import com.google.cloud.spanner.ErrorCode;
 import com.google.cloud.spanner.SpannerException;
@@ -32,6 +33,20 @@ import javax.annotation.Nonnull;
 /** Represents a row in the pg_settings table. */
 @InternalApi
 public class PGSetting {
+  public enum Context {
+    USER,
+    SUPERUSER,
+    BACKEND,
+    SUPERUSER_BACKEND,
+    SIGHUP,
+    POSTMASTER,
+    INTERNAL;
+
+    String getSqlValue() {
+      return name().toLowerCase().replace('_', '-');
+    }
+  }
+
   private static final int NAME_INDEX = 0;
   private static final int SETTING_INDEX = 1;
   private static final int UNIT_INDEX = 2;
@@ -75,7 +90,7 @@ public class PGSetting {
   private final String category;
   private final String shortDesc;
   private final String extraDesc;
-  private final String context;
+  private final Context context;
   private final String vartype;
   private final String minVal;
   private final String maxVal;
@@ -151,10 +166,10 @@ public class PGSetting {
   PGSetting(String extension, String name) {
     this.extension = extension;
     this.name = name;
+    this.context = Context.USER;
     this.category = null;
     this.shortDesc = null;
     this.extraDesc = null;
-    this.context = null;
     this.vartype = null;
     this.minVal = null;
     this.maxVal = null;
@@ -186,7 +201,7 @@ public class PGSetting {
     this.category = category;
     this.shortDesc = shortDesc;
     this.extraDesc = extraDesc;
-    this.context = context;
+    this.context = tryParseContext(context);
     this.vartype = vartype;
     this.minVal = minVal;
     this.maxVal = maxVal;
@@ -209,7 +224,7 @@ public class PGSetting {
         category,
         shortDesc,
         extraDesc,
-        context,
+        context.name(),
         vartype,
         minVal,
         maxVal,
@@ -224,6 +239,16 @@ public class PGSetting {
         pendingRestart);
   }
 
+  static Context tryParseContext(String value) {
+    try {
+      return Strings.isNullOrEmpty(value)
+          ? Context.USER
+          : Context.valueOf(value.toUpperCase().replace('-', '_'));
+    } catch (Exception ignore) {
+      return Context.USER;
+    }
+  }
+
   /** Returns this setting as a SELECT statement that can be used in a query or CTE. */
   String getSelectStatement() {
     return "select "
@@ -239,7 +264,7 @@ public class PGSetting {
         + " as short_desc, "
         + toSelectExpression((String) null)
         + " as extra_desc, "
-        + toSelectExpression(context)
+        + toSelectExpression(context.getSqlValue())
         + " as context, "
         + toSelectExpression(vartype)
         + " as vartype, "
@@ -330,7 +355,7 @@ public class PGSetting {
 
   /** Initializes the value of the setting at connection startup. */
   void initConnectionValue(String value) {
-    setSetting(value);
+    setSetting(Context.BACKEND, value);
     this.resetVal = value;
   }
 
@@ -338,9 +363,9 @@ public class PGSetting {
    * Sets the value for this setting. Throws {@link SpannerException} if the value is not valid, or
    * if the setting is not settable.
    */
-  void setSetting(String value) {
+  void setSetting(Context context, String value) {
     if (this.context != null) {
-      checkValidContext();
+      checkValidContext(context);
     }
     if (this.vartype != null) {
       // Check validity of the value.
@@ -349,34 +374,39 @@ public class PGSetting {
     this.setting = value;
   }
 
-  boolean isSettable() {
-    // Consider all users as superuser.
-    return "user".equals(this.context) || "superuser".equals(this.context);
+  boolean isSettable(Context context) {
+    Preconditions.checkNotNull(context);
+    return this.context.ordinal() <= context.ordinal();
   }
 
-  private void checkValidContext() {
-    if (!isSettable()) {
+  private void checkValidContext(Context context) {
+    if (!isSettable(context)) {
       throw invalidContextError(getCasePreservingKey(), this.context);
     }
   }
 
-  static SpannerException invalidContextError(String key, String context) {
-    if ("internal".equals(context)) {
-      return SpannerExceptionFactory.newSpannerException(
-          ErrorCode.INVALID_ARGUMENT, String.format("parameter \"%s\" cannot be changed", key));
-    }
-    if ("postmaster".equals(context)) {
-      return SpannerExceptionFactory.newSpannerException(
-          ErrorCode.INVALID_ARGUMENT,
-          String.format("parameter \"%s\" cannot be changed without restarting the server", key));
-    }
-    if ("sighup".equals(context)) {
-      return SpannerExceptionFactory.newSpannerException(
-          ErrorCode.INVALID_ARGUMENT, String.format("parameter \"%s\" cannot be changed now", key));
+  static SpannerException invalidContextError(String key, Context context) {
+    switch (context) {
+      case INTERNAL:
+        return SpannerExceptionFactory.newSpannerException(
+            ErrorCode.INVALID_ARGUMENT, String.format("parameter \"%s\" cannot be changed", key));
+      case POSTMASTER:
+        return SpannerExceptionFactory.newSpannerException(
+            ErrorCode.INVALID_ARGUMENT,
+            String.format("parameter \"%s\" cannot be changed without restarting the server", key));
+      case SIGHUP:
+        return SpannerExceptionFactory.newSpannerException(
+            ErrorCode.INVALID_ARGUMENT,
+            String.format("parameter \"%s\" cannot be changed now", key));
+      case SUPERUSER_BACKEND:
+      case BACKEND:
+      case SUPERUSER:
+      case USER:
+      default:
+        return SpannerExceptionFactory.newSpannerException(
+            ErrorCode.INVALID_ARGUMENT,
+            String.format("parameter \"%s\" cannot be set after connection start", key));
     }
-    return SpannerExceptionFactory.newSpannerException(
-        ErrorCode.INVALID_ARGUMENT,
-        String.format("parameter \"%s\" cannot be set after connection start", key));
   }
 
   private void checkValidValue(String value) {
@@ -429,7 +459,7 @@ public class PGSetting {
     return extraDesc;
   }
 
-  public String getContext() {
+  public Context getContext() {
     return context;
   }
 
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java b/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
index d48c1e27..82172086 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
@@ -21,6 +21,7 @@ import com.google.cloud.spanner.SpannerExceptionFactory;
 import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
 import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata.DdlTransactionMode;
 import com.google.cloud.spanner.pgadapter.parsers.BooleanParser;
+import com.google.cloud.spanner.pgadapter.session.PGSetting.Context;
 import com.google.common.annotations.VisibleForTesting;
 import com.google.common.base.MoreObjects;
 import com.google.common.collect.ImmutableSet;
@@ -66,7 +67,7 @@ public class SessionState {
           "spanner.ddl_transaction_mode",
           "spanner.replace_pg_catalog_tables");
 
-  private static final Map<String, PGSetting> SERVER_SETTINGS = new HashMap<>();
+  static final Map<String, PGSetting> SERVER_SETTINGS = new HashMap<>();
 
   static {
     for (PGSetting setting : PGSetting.read()) {
@@ -170,6 +171,13 @@ public class SessionState {
     }
     try {
       setting.initConnectionValue(value);
+      // Also update server_version_num if the server_version is set in the connection startup
+      // message. This is something that is not supported by PostgreSQL, but for PGAdapter the
+      // minimum context needed for setting the server_version is BACKEND.
+      if (key.equals(toKey(null, "server_version"))) {
+        setting = this.settings.get(toKey(null, "server_version_num"));
+        setting.setSetting(Context.INTERNAL, OptionsMetadata.toServerVersionNum(value));
+      }
     } catch (Exception ignore) {
       // ignore errors in startup values to prevent unknown or invalid settings from stopping a
       // connection from being made.
@@ -223,7 +231,8 @@ public class SessionState {
     if (setting == null) {
       setting = newSetting.getResetVal();
     }
-    newSetting.setSetting(setting);
+    // Consider all users as SUPERUSER.
+    newSetting.setSetting(Context.SUPERUSER, setting);
     currentSettings.put(key, newSetting);
   }
 
@@ -273,7 +282,8 @@ public class SessionState {
   /** Resets all values to their 'reset' value. */
   public void resetAll() {
     for (PGSetting setting : getAll()) {
-      if (setting.isSettable() && !Objects.equals(setting.getSetting(), setting.getResetVal())) {
+      if (setting.isSettable(Context.SUPERUSER)
+          && !Objects.equals(setting.getSetting(), setting.getResetVal())) {
         set(setting.getExtension(), setting.getName(), setting.getResetVal());
       }
     }
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/BackendConnection.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/BackendConnection.java
index 5c1d7cc6..6f550f9c 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/statements/BackendConnection.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/statements/BackendConnection.java
@@ -480,7 +480,7 @@ public class BackendConnection {
           if (key == null) {
             continue;
           }
-          this.sessionState.setConnectionStartupValue(key.schema, key.name, keyValue[1]);
+          this.sessionState.setConnectionStartupValue(key.schema, key.name, keyValue[1].trim());
         }
       }
     } else {
@@ -508,6 +508,11 @@ public class BackendConnection {
     return this.databaseId.getDatabase();
   }
 
+  /** Returns the session state of this connection. */
+  public SessionState getSessionState() {
+    return this.sessionState;
+  }
+
   /**
    * Flushes all buffered statements to Cloud Spanner and commits/rolls back the transaction at the
    * end if isSync=true.
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/BootstrapMessage.java b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/BootstrapMessage.java
index d8894ca3..32c9c1bc 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/BootstrapMessage.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/BootstrapMessage.java
@@ -16,7 +16,7 @@ package com.google.cloud.spanner.pgadapter.wireprotocol;
 
 import com.google.api.core.InternalApi;
 import com.google.cloud.spanner.pgadapter.ConnectionHandler;
-import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
+import com.google.cloud.spanner.pgadapter.session.SessionState;
 import com.google.cloud.spanner.pgadapter.wireoutput.AuthenticationOkResponse;
 import com.google.cloud.spanner.pgadapter.wireoutput.KeyDataResponse;
 import com.google.cloud.spanner.pgadapter.wireoutput.ParameterStatusResponse;
@@ -106,12 +106,14 @@ public abstract class BootstrapMessage extends WireMessage {
    * @throws Exception
    */
   public static void sendStartupMessage(
-      DataOutputStream output, int connectionId, int secret, OptionsMetadata options)
+      DataOutputStream output, int connectionId, int secret, SessionState sessionState)
       throws Exception {
     new AuthenticationOkResponse(output).send();
     new KeyDataResponse(output, connectionId, secret).send();
     new ParameterStatusResponse(
-            output, "server_version".getBytes(), options.getServerVersion().getBytes())
+            output,
+            "server_version".getBytes(),
+            sessionState.get(null, "server_version").getSetting().getBytes())
         .send();
     new ParameterStatusResponse(output, "application_name".getBytes(), "PGAdapter".getBytes())
         .send();
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java
index f5586971..b4366989 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java
@@ -92,7 +92,7 @@ public class StartupMessage extends BootstrapMessage {
         connection.getConnectionMetadata().peekOutputStream(),
         connection.getConnectionId(),
         connection.getSecret(),
-        connection.getServer().getOptions());
+        connection.getExtendedQueryProtocolHandler().getBackendConnection().getSessionState());
     connection.setStatus(ConnectionStatus.AUTHENTICATED);
   }
 
diff --git a/src/main/resources/com/google/cloud/spanner/pgadapter/session/pg_settings.txt b/src/main/resources/com/google/cloud/spanner/pgadapter/session/pg_settings.txt
index 6b5e0acb..d756c6cb 100644
--- a/src/main/resources/com/google/cloud/spanner/pgadapter/session/pg_settings.txt
+++ b/src/main/resources/com/google/cloud/spanner/pgadapter/session/pg_settings.txt
@@ -248,7 +248,7 @@ search_path	public	\N	Client Connection Defaults / Statement Behavior	Sets the s
 segment_size	131072	8kB	Preset Options	Shows the number of pages per disk file.	\N	internal	integer	default	131072	131072	\N	131072	131072	\N	\N	f
 seq_page_cost	1	\N	Query Tuning / Planner Cost Constants	Sets the planner's estimate of the cost of a sequentially fetched disk page.	\N	user	real	default	0	1.79769e+308	\N	1	1	\N	\N	f
 server_encoding	UTF8	\N	Preset Options	Shows the server (database) character set encoding.	\N	internal	string	override	\N	\N	\N	SQL_ASCII	UTF8	\N	\N	f
-server_version	14.1 (Debian 14.1-1.pgdg110+1)	\N	Preset Options	Shows the server version.	\N	internal	string	default	\N	\N	\N	14.1 (Debian 14.1-1.pgdg110+1)	14.1 (Debian 14.1-1.pgdg110+1)	\N	\N	f
+server_version	14.1 (Debian 14.1-1.pgdg110+1)	\N	Preset Options	Shows the server version.	\N	backend	string	default	\N	\N	\N	14.1 (Debian 14.1-1.pgdg110+1)	14.1 (Debian 14.1-1.pgdg110+1)	\N	\N	f
 server_version_num	140001	\N	Preset Options	Shows the server version as an integer.	\N	internal	integer	default	140001	140001	\N	140001	140001	\N	\N	f
 session_preload_libraries		\N	Client Connection Defaults / Shared Library Preloading	Lists shared libraries to preload into each backend.	\N	superuser	string	default	\N	\N	\N			\N	\N	f
 session_replication_role	origin	\N	Client Connection Defaults / Statement Behavior	Sets the session's behavior for triggers and rewrite rules.	\N	superuser	enum	default	\N	\N	{origin,replica,local}	origin	origin	\N	\N	f


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
index 61876660..9dcae241 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
@@ -2090,6 +2090,35 @@ public class JdbcMockServerTest extends AbstractMockServerTest {
     }
   }
 
+  @Test
+  public void testMultipleSettingsInConnectionOptions() throws SQLException {
+    try (Connection connection =
+        DriverManager.getConnection(
+            createUrl() + "?options=-c%20spanner.setting1=value1%20-c%20spanner.setting2=value2")) {
+      verifySettingValue(connection, "spanner.setting1", "value1");
+      verifySettingValue(connection, "spanner.setting2", "value2");
+    }
+  }
+
+  @Test
+  public void testServerVersionInConnectionOptions() throws SQLException {
+    try (Connection connection =
+        DriverManager.getConnection(createUrl() + "?options=-c%20server_version=4.1")) {
+      verifySettingValue(connection, "server_version", "4.1");
+      verifySettingValue(connection, "server_version_num", "40001");
+    }
+  }
+
+  @Test
+  public void testCustomServerVersionInConnectionOptions() throws SQLException {
+    try (Connection connection =
+        DriverManager.getConnection(
+            createUrl() + "?options=-c%20server_version=5.2 custom version")) {
+      verifySettingValue(connection, "server_version", "5.2 custom version");
+      verifySettingValue(connection, "server_version_num", "50002");
+    }
+  }
+
   @Test
   public void testSelectPgType() throws SQLException {
     mockSpanner.putStatementResult(
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadataTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadataTest.java
index 359c308b..d034c4df 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadataTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadataTest.java
@@ -14,6 +14,7 @@
 
 package com.google.cloud.spanner.pgadapter.metadata;
 
+import static com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata.toServerVersionNum;
 import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertFalse;
 import static org.junit.Assert.assertThrows;
@@ -241,4 +242,14 @@ public class OptionsMetadataTest {
         new OptionsMetadata(new String[] {"-p p", "-i i", "-disable_pg_catalog_replacements"});
     assertFalse(options.replacePgCatalogTables());
   }
+
+  @Test
+  public void testToServerVersionNum() {
+    assertEquals("10000", toServerVersionNum("1.0"));
+    assertEquals("140001", toServerVersionNum("14.1"));
+    assertEquals("80004", toServerVersionNum("8.4"));
+    assertEquals("10000", toServerVersionNum("1.0.1"));
+    assertEquals("10000", toServerVersionNum("1.0 custom build"));
+    assertEquals("10010", toServerVersionNum("1.10 custom build"));
+  }
 }
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/session/PGSettingTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/session/PGSettingTest.java
new file mode 100644
index 00000000..147fa32d
--- /dev/null
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/session/PGSettingTest.java
@@ -0,0 +1,49 @@
+// Copyright 2022 Google LLC
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package com.google.cloud.spanner.pgadapter.session;
+
+import static com.google.cloud.spanner.pgadapter.session.PGSetting.tryParseContext;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import com.google.cloud.spanner.pgadapter.session.PGSetting.Context;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.JUnit4;
+
+@RunWith(JUnit4.class)
+public class PGSettingTest {
+
+  @Test
+  public void testTryParseContext() {
+    assertEquals(Context.INTERNAL, tryParseContext("internal"));
+    assertEquals(Context.SUPERUSER_BACKEND, tryParseContext("superuser-backend"));
+    assertEquals(Context.USER, tryParseContext("USER"));
+    assertEquals(Context.SUPERUSER, tryParseContext("superUSER"));
+    assertEquals(Context.POSTMASTER, tryParseContext("Postmaster"));
+
+    assertEquals(Context.USER, tryParseContext("random-value"));
+    assertEquals(Context.USER, tryParseContext(""));
+    assertEquals(Context.USER, tryParseContext(null));
+  }
+
+  @Test
+  public void testIsSettable() {
+    assertTrue(SessionState.SERVER_SETTINGS.get("server_version").isSettable(Context.INTERNAL));
+    assertTrue(SessionState.SERVER_SETTINGS.get("server_version").isSettable(Context.BACKEND));
+    assertFalse(SessionState.SERVER_SETTINGS.get("server_version").isSettable(Context.SUPERUSER));
+  }
+}
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
index 333d93ee..8e841f5b 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
@@ -421,7 +421,7 @@ public class SessionStateTest {
         + "union all\n"
         + "select 'search_path' as name, 'public' as setting, null as unit, 'Client Connection Defaults / Statement Behavior' as category, null as short_desc, null as extra_desc, 'user' as context, 'string' as vartype, null as min_val, null as max_val, null::text[] as enumvals, 'public' as boot_val, 'public' as reset_val, 'default' as source, null as sourcefile, null::bigint as sourceline, 'f'::boolean as pending_restart\n"
         + "union all\n"
-        + "select 'server_version' as name, null as setting, null as unit, 'Preset Options' as category, null as short_desc, null as extra_desc, 'internal' as context, 'string' as vartype, null as min_val, null as max_val, null::text[] as enumvals, '14.1 (Debian 14.1-1.pgdg110+1)' as boot_val, '14.1 (Debian 14.1-1.pgdg110+1)' as reset_val, 'default' as source, null as sourcefile, null::bigint as sourceline, 'f'::boolean as pending_restart\n"
+        + "select 'server_version' as name, null as setting, null as unit, 'Preset Options' as category, null as short_desc, null as extra_desc, 'backend' as context, 'string' as vartype, null as min_val, null as max_val, null::text[] as enumvals, '14.1 (Debian 14.1-1.pgdg110+1)' as boot_val, '14.1 (Debian 14.1-1.pgdg110+1)' as reset_val, 'default' as source, null as sourcefile, null::bigint as sourceline, 'f'::boolean as pending_restart\n"
         + "union all\n"
         + "select 'server_version_num' as name, null as setting, null as unit, 'Preset Options' as category, null as short_desc, null as extra_desc, 'internal' as context, 'integer' as vartype, '140001' as min_val, '140001' as max_val, null::text[] as enumvals, '140001' as boot_val, '140001' as reset_val, 'default' as source, null as sourcefile, null::bigint as sourceline, 'f'::boolean as pending_restart\n"
         + "union all\n"
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/wireprotocol/ProtocolTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/wireprotocol/ProtocolTest.java
index cf60cf95..bbe72003 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/wireprotocol/ProtocolTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/wireprotocol/ProtocolTest.java
@@ -48,6 +48,8 @@ import com.google.cloud.spanner.pgadapter.ConnectionHandler.QueryMode;
 import com.google.cloud.spanner.pgadapter.ProxyServer;
 import com.google.cloud.spanner.pgadapter.metadata.ConnectionMetadata;
 import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
+import com.google.cloud.spanner.pgadapter.session.PGSetting;
+import com.google.cloud.spanner.pgadapter.session.SessionState;
 import com.google.cloud.spanner.pgadapter.statements.BackendConnection;
 import com.google.cloud.spanner.pgadapter.statements.BackendConnection.ConnectionState;
 import com.google.cloud.spanner.pgadapter.statements.CopyStatement;
@@ -1521,6 +1523,11 @@ public class ProtocolTest {
     when(connectionHandler.getExtendedQueryProtocolHandler())
         .thenReturn(extendedQueryProtocolHandler);
     when(extendedQueryProtocolHandler.getBackendConnection()).thenReturn(backendConnection);
+    SessionState sessionState = mock(SessionState.class);
+    PGSetting serverVersionSetting = mock(PGSetting.class);
+    when(serverVersionSetting.getSetting()).thenReturn("13.4");
+    when(sessionState.get(null, "server_version")).thenReturn(serverVersionSetting);
+    when(backendConnection.getSessionState()).thenReturn(sessionState);
     when(server.getOptions()).thenReturn(options);
     when(options.getServerVersion()).thenReturn("13.4");
     when(options.shouldAuthenticate()).thenReturn(false);