Repository: GoogleCloudPlatform/pgadapter
Pull request #305 (2022-07-31 13:47:20)

feat: manage session state
Support SHOW, SET and RESET commands for session variables. The session state
is not used, but all supported SHOW/SET/RESET commands from PostgreSQL are
supported. The transactionality of these statements follow the rules of
PostgreSQL.

codecov[bot]: # [Codecov](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/305?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) Report
> Merging [#305](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/305?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) (e003777) into [postgresql-dialect](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/commit/c9d5c7bbd17124a329e9e37570b028f6ec082232?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) (c9d5c7b) will **decrease** coverage by `0.50%`.
> The diff coverage is `76.73%`.

```diff
@@                   Coverage Diff                    @@
##             postgresql-dialect     #305      +/-   ##
========================================================
- Coverage                 82.85%   82.35%   -0.51%     
- Complexity                 1324     1384      +60     
========================================================
  Files                       109      109              
  Lines                      4568     4874     +306     
  Branches                    568      623      +55     
========================================================
+ Hits                       3785     4014     +229     
- Misses                      595      647      +52     
- Partials                    188      213      +25     
```

| Flag | Coverage Δ | |
|---|---|---|
| all_tests | `82.35% <76.73%> (-0.51%)` | :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/305?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) | Coverage Δ | |
|---|---|---|
| [...ud/spanner/pgadapter/utils/ClientAutoDetector.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/305/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3V0aWxzL0NsaWVudEF1dG9EZXRlY3Rvci5qYXZh) | `92.68% <ø> (ø)` | |
| [...r/pgadapter/statements/SessionStatementParser.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/305/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3N0YXRlbWVudHMvU2Vzc2lvblN0YXRlbWVudFBhcnNlci5qYXZh) | `69.40% <69.40%> (ø)` | |
| [.../cloud/spanner/pgadapter/session/SessionState.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/305/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3Nlc3Npb24vU2Vzc2lvblN0YXRlLmphdmE=) | `69.56% <69.56%> (ø)` | |
| [...gle/cloud/spanner/pgadapter/session/PGSetting.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/305/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3Nlc3Npb24vUEdTZXR0aW5nLmphdmE=) | `87.03% <87.03%> (ø)` | |
| [...panner/pgadapter/statements/BackendConnection.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/305/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3N0YXRlbWVudHMvQmFja2VuZENvbm5lY3Rpb24uamF2YQ==) | `90.88% <95.00%> (-0.05%)` | :arrow_down: |
| [...oud/spanner/pgadapter/statements/SimpleParser.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/305/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3N0YXRlbWVudHMvU2ltcGxlUGFyc2VyLmphdmE=) | `90.90% <0.00%> (+0.90%)` | :arrow_up: |

Help us with your feedback. Take ten seconds to tell us [how you rate us](https://about.codecov.io/nps?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/session/PGSetting.java b/src/main/java/com/google/cloud/spanner/pgadapter/session/PGSetting.java
new file mode 100644
index 00000000..c4abe862
--- /dev/null
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/session/PGSetting.java
@@ -0,0 +1,346 @@
+// 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 com.google.api.core.InternalApi;
+import com.google.cloud.spanner.ErrorCode;
+import com.google.cloud.spanner.SpannerException;
+import com.google.cloud.spanner.SpannerExceptionFactory;
+import com.google.cloud.spanner.pgadapter.parsers.BooleanParser;
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Iterables;
+import java.util.Arrays;
+import java.util.Objects;
+import java.util.Scanner;
+import javax.annotation.Nonnull;
+
+/** Represents a row in the pg_settings table. */
+@InternalApi
+public class PGSetting {
+  private static final int NAME_INDEX = 0;
+  private static final int SETTING_INDEX = 1;
+  private static final int UNIT_INDEX = 2;
+  private static final int CATEGORY_INDEX = 3;
+  private static final int SHORT_DESC_INDEX = 4;
+  private static final int EXTRA_DESC_INDEX = 5;
+  private static final int CONTEXT_INDEX = 6;
+  private static final int VARTYPE_INDEX = 7;
+  private static final int SOURCE_INDEX = 8;
+  private static final int MIN_VAL_INDEX = 9;
+  private static final int MAX_VAL_INDEX = 10;
+  private static final int ENUM_VALS_INDEX = 11;
+  private static final int BOOT_VAL_INDEX = 12;
+  private static final int RESET_VAL_INDEX = 13;
+  private static final int SOURCEFILE_INDEX = 14;
+  private static final int SOURCELINE_INDEX = 15;
+  private static final int PENDING_RESTART_INDEX = 16;
+
+  private final String extension;
+  private final String name;
+  private final String category;
+  private final String shortDesc;
+  private final String extraDesc;
+  private final String context;
+  private final String vartype;
+  private final String minVal;
+  private final String maxVal;
+  private final String[] enumVals;
+
+  private String setting;
+  private String unit;
+  private String source;
+  private String bootVal;
+  private String resetVal;
+  private String sourcefile;
+  private Integer sourceline;
+  private boolean pendingRestart;
+
+  static ImmutableList<PGSetting> read() {
+    ImmutableList.Builder<PGSetting> builder = ImmutableList.builder();
+    try (Scanner scanner =
+        new Scanner(
+            Objects.requireNonNull(PGSetting.class.getResourceAsStream("pg_settings.txt")))) {
+      while (scanner.hasNextLine()) {
+        builder.add(parse(scanner.nextLine()));
+      }
+    }
+    return builder.build();
+  }
+
+  static @Nonnull PGSetting parse(String line) {
+    String[] values = line.split("\t");
+    Preconditions.checkArgument(values.length == 17);
+    return new PGSetting(
+        /* extension = */ null,
+        parseString(values[NAME_INDEX]),
+        parseString(values[CATEGORY_INDEX]),
+        parseString(values[SHORT_DESC_INDEX]),
+        parseString(values[EXTRA_DESC_INDEX]),
+        parseString(values[CONTEXT_INDEX]),
+        parseString(values[VARTYPE_INDEX]),
+        parseString(values[MIN_VAL_INDEX]),
+        parseString(values[MAX_VAL_INDEX]),
+        parseStringArray(values[ENUM_VALS_INDEX]),
+        parseString(values[SETTING_INDEX]),
+        parseString(values[UNIT_INDEX]),
+        parseString(values[SOURCE_INDEX]),
+        parseString(values[BOOT_VAL_INDEX]),
+        parseString(values[RESET_VAL_INDEX]),
+        parseString(values[SOURCEFILE_INDEX]),
+        parseInteger(values[SOURCELINE_INDEX]),
+        values[PENDING_RESTART_INDEX].equals("t"));
+  }
+
+  static String parseString(String value) {
+    if ("\\N".equals(value)) {
+      return null;
+    }
+    return value;
+  }
+
+  static String[] parseStringArray(String value) {
+    if ("\\N".equals(value)) {
+      return null;
+    }
+    Preconditions.checkArgument(value.startsWith("{") && value.endsWith("}"));
+    return value.substring(1, value.length() - 1).split(",");
+  }
+
+  static Integer parseInteger(String value) {
+    if ("\\N".equals(value)) {
+      return null;
+    }
+    return Integer.valueOf(value);
+  }
+
+  PGSetting(String extension, String name) {
+    this.extension = extension;
+    this.name = name;
+    this.category = null;
+    this.shortDesc = null;
+    this.extraDesc = null;
+    this.context = null;
+    this.vartype = null;
+    this.minVal = null;
+    this.maxVal = null;
+    this.enumVals = null;
+  }
+
+  private PGSetting(
+      String extension,
+      String name,
+      String category,
+      String shortDesc,
+      String extraDesc,
+      String context,
+      String vartype,
+      String minVal,
+      String maxVal,
+      String[] enumVals,
+      String setting,
+      String unit,
+      String source,
+      String bootVal,
+      String resetVal,
+      String sourcefile,
+      Integer sourceline,
+      boolean pendingRestart) {
+    this.extension = extension;
+    this.name = name;
+    this.category = category;
+    this.shortDesc = shortDesc;
+    this.extraDesc = extraDesc;
+    this.context = context;
+    this.vartype = vartype;
+    this.minVal = minVal;
+    this.maxVal = maxVal;
+    this.enumVals = enumVals;
+    this.setting = setting;
+    this.unit = unit;
+    this.source = source;
+    this.bootVal = bootVal;
+    this.resetVal = resetVal;
+    this.sourcefile = sourcefile;
+    this.sourceline = sourceline;
+    this.pendingRestart = pendingRestart;
+  }
+
+  PGSetting copy() {
+    return new PGSetting(
+        extension,
+        name,
+        category,
+        shortDesc,
+        extraDesc,
+        context,
+        vartype,
+        minVal,
+        maxVal,
+        enumVals,
+        setting,
+        unit,
+        source,
+        bootVal,
+        resetVal,
+        sourcefile,
+        sourceline,
+        pendingRestart);
+  }
+
+  public String getKey() {
+    if (extension == null) {
+      return name;
+    }
+    return extension + "." + name;
+  }
+
+  public String getExtension() {
+    return extension;
+  }
+
+  public String getName() {
+    return name;
+  }
+
+  /** Returns the value of this setting. */
+  public String getSetting() {
+    return setting;
+  }
+
+  /** Initializes the value of the setting without checking for validity. */
+  void initSettingValue(String value) {
+    this.setting = value;
+  }
+
+  /**
+   * 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) {
+    if (this.context != null) {
+      checkValidContext();
+    }
+    if (this.vartype != null) {
+      // Check validity of the value.
+      checkValidValue(value);
+    }
+    this.setting = value;
+  }
+
+  boolean isSettable() {
+    // Consider all users as superuser.
+    return "user".equals(this.context) || "superuser".equals(this.context);
+  }
+
+  private void checkValidContext() {
+    if (!isSettable()) {
+      throw invalidContextError(getKey(), 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));
+    }
+    return SpannerExceptionFactory.newSpannerException(
+        ErrorCode.INVALID_ARGUMENT,
+        String.format("parameter \"%s\" cannot be set after connection start", key));
+  }
+
+  private void checkValidValue(String value) {
+    if ("bool".equals(this.vartype)) {
+      // Just verify that it is a valid boolean. This will throw an IllegalArgumentException if
+      // setting is not a valid boolean value.
+      try {
+        BooleanParser.toBoolean(value);
+      } catch (IllegalArgumentException exception) {
+        throw invalidBoolError(getKey());
+      }
+    } else if ("integer".equals(this.vartype)) {
+      try {
+        Integer.parseInt(value);
+      } catch (NumberFormatException exception) {
+        throw invalidValueError(getKey(), value);
+      }
+    } else if ("real".equals(this.vartype)) {
+      try {
+        Double.parseDouble(value);
+      } catch (NumberFormatException exception) {
+        throw invalidValueError(getKey(), value);
+      }
+    } else if (enumVals != null && !Iterables.contains(Arrays.asList(this.enumVals), value)) {
+      throw invalidValueError(getKey(), value);
+    }
+  }
+
+  static SpannerException invalidBoolError(String key) {
+    return SpannerExceptionFactory.newSpannerException(
+        ErrorCode.INVALID_ARGUMENT,
+        String.format("parameter \"%s\" requires a Boolean value", key));
+  }
+
+  static SpannerException invalidValueError(String key, String value) {
+    return SpannerExceptionFactory.newSpannerException(
+        ErrorCode.INVALID_ARGUMENT,
+        String.format("invalid value for parameter \"%s\": \"%s\"", key, value));
+  }
+
+  public String getCategory() {
+    return category;
+  }
+
+  public String getShortDesc() {
+    return shortDesc;
+  }
+
+  public String getExtraDesc() {
+    return extraDesc;
+  }
+
+  public String getContext() {
+    return context;
+  }
+
+  public String getVartype() {
+    return vartype;
+  }
+
+  public String getMinVal() {
+    return minVal;
+  }
+
+  public String getMaxVal() {
+    return maxVal;
+  }
+
+  public String[] getEnumVals() {
+    return enumVals;
+  }
+
+  public String getResetVal() {
+    return resetVal;
+  }
+}
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
new file mode 100644
index 00000000..4b226d6e
--- /dev/null
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
@@ -0,0 +1,187 @@
+// 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 com.google.api.core.InternalApi;
+import com.google.cloud.spanner.ErrorCode;
+import com.google.cloud.spanner.SpannerException;
+import com.google.cloud.spanner.SpannerExceptionFactory;
+import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
+import com.google.common.collect.Sets;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.Comparator;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Locale;
+import java.util.Map;
+import java.util.Objects;
+import java.util.Set;
+
+/** {@link SessionState} contains all session variables for a connection. */
+@InternalApi
+public class SessionState {
+  private static final Map<String, PGSetting> SERVER_SETTINGS = new HashMap<>();
+
+  static {
+    for (PGSetting setting : PGSetting.read()) {
+      SERVER_SETTINGS.put(toKey(null, setting.getName()), setting);
+    }
+  }
+
+  private final Map<String, PGSetting> settings;
+
+  /** transactionSettings are the modified session settings during a transaction. */
+  private Map<String, PGSetting> transactionSettings;
+  /** localSettings are the modified local settings during a transaction. */
+  private Map<String, PGSetting> localSettings;
+
+  public SessionState(OptionsMetadata options) {
+    this.settings = new HashMap<>(SERVER_SETTINGS);
+    this.settings.get("server_version").initSettingValue(options.getServerVersion());
+  }
+
+  private static String toKey(String extension, String name) {
+    return extension == null
+        ? name.toLowerCase(Locale.ROOT)
+        : extension.toLowerCase(Locale.ROOT) + "." + name.toLowerCase(Locale.ROOT);
+  }
+
+  /**
+   * Sets the value of the specified setting. The new value will be persisted if the current
+   * transaction is committed. The value will be lost if the transaction is rolled back.
+   */
+  public void set(String extension, String name, String setting) {
+    if (transactionSettings == null) {
+      transactionSettings = new HashMap<>();
+    }
+    internalSet(extension, name, setting, transactionSettings);
+    // Remove the setting from the local settings if it's there, as the new transaction setting is
+    // the one that should be used.
+    if (localSettings != null) {
+      localSettings.remove(toKey(extension, name));
+    }
+  }
+
+  /**
+   * Sets the value of the specified setting for the current transaction. This value is lost when
+   * the transaction is committed or rolled back.
+   */
+  public void setLocal(String extension, String name, String setting) {
+    if (localSettings == null) {
+      localSettings = new HashMap<>();
+    }
+    // Note that setting a local setting does not remove it from the transaction settings. This
+    // means that a commit will persist the setting in transactionSettings.
+    internalSet(extension, name, setting, localSettings);
+  }
+
+  private void internalSet(
+      String extension, String name, String setting, Map<String, PGSetting> currentSettings) {
+    String key = toKey(extension, name);
+    PGSetting newSetting = currentSettings.get(key);
+    if (newSetting == null) {
+      PGSetting existingSetting = settings.get(key);
+      if (existingSetting == null) {
+        if (extension == null) {
+          throw unknownParamError(key);
+        }
+        newSetting = new PGSetting(extension, name);
+      } else {
+        newSetting = existingSetting.copy();
+      }
+    }
+    if (setting == null) {
+      setting = newSetting.getResetVal();
+    }
+    newSetting.setSetting(setting);
+    currentSettings.put(key, newSetting);
+  }
+
+  /** Returns the current value of the specified setting. */
+  public PGSetting get(String extension, String name) {
+    return internalGet(toKey(extension, name));
+  }
+
+  private PGSetting internalGet(String key) {
+    if (localSettings != null && localSettings.containsKey(key)) {
+      return localSettings.get(key);
+    }
+    if (transactionSettings != null && transactionSettings.containsKey(key)) {
+      return transactionSettings.get(key);
+    }
+    if (settings.containsKey(key)) {
+      return settings.get(key);
+    }
+    throw unknownParamError(key);
+  }
+
+  /** Returns all settings and their current values. */
+  public List<PGSetting> getAll() {
+    List<PGSetting> result =
+        new ArrayList<>(
+            (localSettings == null ? 0 : localSettings.size())
+                + (transactionSettings == null ? 0 : transactionSettings.size())
+                + settings.size());
+    Set<String> keys =
+        Sets.union(
+            settings.keySet(),
+            Sets.union(
+                localSettings == null ? Collections.emptySet() : localSettings.keySet(),
+                transactionSettings == null
+                    ? Collections.emptySet()
+                    : transactionSettings.keySet()));
+    for (String key : keys) {
+      result.add(internalGet(key));
+    }
+    result.sort(Comparator.comparing(PGSetting::getKey));
+    return result;
+  }
+
+  /** Resets all values to their 'reset' value. */
+  public void resetAll() {
+    for (PGSetting setting : getAll()) {
+      if (setting.isSettable() && !Objects.equals(setting.getSetting(), setting.getResetVal())) {
+        set(setting.getExtension(), setting.getName(), setting.getResetVal());
+      }
+    }
+  }
+
+  static SpannerException unknownParamError(String key) {
+    return SpannerExceptionFactory.newSpannerException(
+        ErrorCode.INVALID_ARGUMENT,
+        String.format("unrecognized configuration parameter \"%s\"", key));
+  }
+
+  /**
+   * Commits the current transaction and persists any changes to the settings (except local
+   * changes).
+   */
+  public void commit() {
+    if (transactionSettings != null) {
+      for (PGSetting setting : transactionSettings.values()) {
+        settings.put(toKey(setting.getExtension(), setting.getName()), setting);
+      }
+    }
+    this.localSettings = null;
+    this.transactionSettings = null;
+  }
+
+  /** Rolls back the current transaction and abandons any pending changes to the settings. */
+  public void rollback() {
+    this.localSettings = null;
+    this.transactionSettings = null;
+  }
+}
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 ece4ebb8..4870c271 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
@@ -45,7 +45,9 @@ import com.google.cloud.spanner.connection.StatementResult;
 import com.google.cloud.spanner.connection.StatementResult.ClientSideStatementType;
 import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
 import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata.DdlTransactionMode;
+import com.google.cloud.spanner.pgadapter.session.SessionState;
 import com.google.cloud.spanner.pgadapter.statements.DdlExecutor.NotExecuted;
+import com.google.cloud.spanner.pgadapter.statements.SessionStatementParser.SessionStatement;
 import com.google.cloud.spanner.pgadapter.statements.local.LocalStatement;
 import com.google.cloud.spanner.pgadapter.utils.CopyDataReceiver;
 import com.google.cloud.spanner.pgadapter.utils.MutationWriter;
@@ -75,6 +77,7 @@ import java.util.concurrent.ExecutionException;
 import java.util.concurrent.ExecutorService;
 import java.util.concurrent.Future;
 import java.util.stream.Collectors;
+import javax.annotation.Nullable;
 
 /**
  * This class emulates a backend PostgreSQL connection. Statements are buffered in memory until a
@@ -162,10 +165,13 @@ public class BackendConnection {
         //  SELECT statements, then we should create a read-only transaction. Also, if a transaction
         //  block always ends with a ROLLBACK, PGAdapter should skip the entire execution of that
         //  block.
+        SessionStatement sessionStatement = getSessionManagementStatement(parsedStatement);
         if (!localStatements.isEmpty() && localStatements.containsKey(statement.getSql())) {
           result.set(
               Objects.requireNonNull(localStatements.get(statement.getSql()))
                   .execute(BackendConnection.this));
+        } else if (sessionStatement != null) {
+          result.set(sessionStatement.execute(sessionState));
         } else if (connectionState == ConnectionState.ABORTED
             && !spannerConnection.isInTransaction()
             && (isRollback(parsedStatement) || isCommit(parsedStatement))) {
@@ -235,6 +241,20 @@ public class BackendConnection {
               .startsWith(
                   "INVALID_ARGUMENT: io.grpc.StatusRuntimeException: INVALID_ARGUMENT: Unsupported concurrency mode in query using INFORMATION_SCHEMA.");
     }
+
+    @Nullable
+    SessionStatement getSessionManagementStatement(ParsedStatement parsedStatement) {
+      if (parsedStatement.getType() == StatementType.UNKNOWN
+          || (parsedStatement.getType() == StatementType.QUERY
+              && parsedStatement.getSqlWithoutComments().length() >= 4
+              && parsedStatement
+                  .getSqlWithoutComments()
+                  .substring(0, 4)
+                  .equalsIgnoreCase("show"))) {
+        return SessionStatementParser.parse(parsedStatement);
+      }
+      return null;
+    }
   }
 
   private static final int MAX_PARTITIONS =
@@ -345,6 +365,7 @@ public class BackendConnection {
   private static final StatementResult ROLLBACK_RESULT = new NoResult("ROLLBACK");
   private static final Statement ROLLBACK = Statement.of("ROLLBACK");
 
+  private final SessionState sessionState;
   private final ImmutableMap<String, LocalStatement> localStatements;
   private ConnectionState connectionState = ConnectionState.IDLE;
   private TransactionMode transactionMode = TransactionMode.IMPLICIT;
@@ -364,6 +385,7 @@ public class BackendConnection {
       Connection spannerConnection,
       OptionsMetadata optionsMetadata,
       ImmutableList<LocalStatement> localStatements) {
+    this.sessionState = new SessionState(optionsMetadata);
     this.spannerConnection = spannerConnection;
     this.databaseId = databaseId;
     this.ddlExecutor = new DdlExecutor(databaseId, this);
@@ -501,6 +523,11 @@ public class BackendConnection {
             transactionMode = TransactionMode.EXPLICIT;
             connectionState = ConnectionState.TRANSACTION;
           } else if (isCommit(index) || isRollback(index)) {
+            if (isCommit(index)) {
+              sessionState.commit();
+            } else {
+              sessionState.rollback();
+            }
             transactionMode = TransactionMode.IMPLICIT;
             connectionState = ConnectionState.IDLE;
           }
@@ -509,6 +536,7 @@ public class BackendConnection {
       }
     } catch (Exception exception) {
       connectionState = ConnectionState.ABORTED;
+      sessionState.rollback();
       if (spannerConnection.isInTransaction()) {
         spannerConnection.setStatementTag(null);
         spannerConnection.execute(ROLLBACK);
@@ -564,6 +592,9 @@ public class BackendConnection {
     }
 
     try {
+      if (connectionState != ConnectionState.ABORTED) {
+        sessionState.commit();
+      }
       if (spannerConnection.isInTransaction()) {
         spannerConnection.setStatementTag(null);
         if (connectionState == ConnectionState.ABORTED) {
@@ -609,6 +640,7 @@ public class BackendConnection {
             // might be active.
             if (spannerConnection.isInTransaction()) {
               spannerConnection.commit();
+              sessionState.commit();
               if (transactionMode == TransactionMode.EXPLICIT) {
                 // Convert the explicit transaction to a DDL batch.
                 transactionMode = TransactionMode.DDL_BATCH;
@@ -648,6 +680,7 @@ public class BackendConnection {
           // Switch the execution state to implicit transaction.
           if (spannerConnection.isInTransaction()) {
             spannerConnection.commit();
+            sessionState.commit();
             if (transactionMode == TransactionMode.EXPLICIT) {
               // Convert the explicit transaction to a DDL batch.
               transactionMode = TransactionMode.DDL_BATCH;
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/SessionStatementParser.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/SessionStatementParser.java
new file mode 100644
index 00000000..08aaafe4
--- /dev/null
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/statements/SessionStatementParser.java
@@ -0,0 +1,383 @@
+// 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.statements;
+
+import com.google.api.client.util.Strings;
+import com.google.api.core.InternalApi;
+import com.google.cloud.spanner.ErrorCode;
+import com.google.cloud.spanner.ResultSets;
+import com.google.cloud.spanner.SpannerExceptionFactory;
+import com.google.cloud.spanner.Struct;
+import com.google.cloud.spanner.Type;
+import com.google.cloud.spanner.Type.StructField;
+import com.google.cloud.spanner.connection.AbstractStatementParser.ParsedStatement;
+import com.google.cloud.spanner.connection.AbstractStatementParser.StatementType;
+import com.google.cloud.spanner.connection.StatementResult;
+import com.google.cloud.spanner.pgadapter.session.PGSetting;
+import com.google.cloud.spanner.pgadapter.session.SessionState;
+import com.google.cloud.spanner.pgadapter.statements.BackendConnection.NoResult;
+import com.google.cloud.spanner.pgadapter.statements.BackendConnection.QueryResult;
+import com.google.cloud.spanner.pgadapter.statements.SessionStatementParser.SetStatement.Builder;
+import com.google.cloud.spanner.pgadapter.statements.SimpleParser.TableOrIndexName;
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+import java.util.Locale;
+import java.util.Objects;
+import java.util.stream.Collectors;
+import javax.annotation.Nullable;
+
+/** Simple parser for session management commands (SET/SHOW/RESET variable_name) */
+@InternalApi
+public class SessionStatementParser {
+  abstract static class SessionStatement {
+    protected final String extension;
+    protected final String name;
+
+    SessionStatement(TableOrIndexName name) {
+      if (name == null) {
+        this.extension = null;
+        this.name = null;
+      } else {
+        this.extension = toParameterExtension(name);
+        this.name = toParameterName(name);
+      }
+    }
+
+    String getKey() {
+      if (extension != null && name != null) {
+        return extension + "." + name;
+      } else if (extension == null && name != null) {
+        return name;
+      }
+      return "all";
+    }
+
+    abstract StatementResult execute(SessionState sessionState);
+  }
+
+  static class SetStatement extends SessionStatement {
+    static class Builder {
+      private boolean local;
+      private TableOrIndexName name;
+      private String value;
+
+      Builder setLocal() {
+        this.local = true;
+        return this;
+      }
+
+      Builder setName(TableOrIndexName name) {
+        this.name = name;
+        return this;
+      }
+
+      Builder setValue(String value) {
+        this.value = value;
+        return this;
+      }
+
+      SetStatement build() {
+        return new SetStatement(local, name, value);
+      }
+    }
+
+    private static final NoResult SET_RESULT = new NoResult("SET");
+    final boolean local;
+    final String value;
+
+    SetStatement(boolean local, TableOrIndexName name, String value) {
+      super(Preconditions.checkNotNull(name));
+      this.local = local;
+      this.value = unquote(value);
+    }
+
+    @Override
+    public StatementResult execute(SessionState sessionState) {
+      if (local) {
+        sessionState.setLocal(extension, name, value);
+      } else {
+        sessionState.set(extension, name, value);
+      }
+      return SET_RESULT;
+    }
+
+    public boolean equals(Object o) {
+      if (!(o instanceof SetStatement)) {
+        return false;
+      }
+      SetStatement other = (SetStatement) o;
+      return Objects.equals(this.extension, other.extension)
+          && Objects.equals(this.name, other.name)
+          && Objects.equals(this.local, other.local)
+          && Objects.equals(this.value, other.value);
+    }
+
+    @Override
+    public String toString() {
+      return "set "
+          + (local ? "local " : "")
+          + getKey()
+          + " to "
+          + (value == null ? "default" : value);
+    }
+  }
+
+  static class ResetStatement extends SessionStatement {
+    private static final NoResult RESET_RESULT = new NoResult("RESET");
+
+    static ResetStatement createResetAll() {
+      return new ResetStatement(null);
+    }
+
+    ResetStatement(TableOrIndexName name) {
+      super(name);
+    }
+
+    @Override
+    public StatementResult execute(SessionState sessionState) {
+      if (extension == null && name != null) {
+        PGSetting setting = sessionState.get(null, name);
+        sessionState.set(null, name, setting.getResetVal());
+      } else if (extension != null && name != null) {
+        sessionState.set(extension, name, null);
+      } else {
+        sessionState.resetAll();
+      }
+      return RESET_RESULT;
+    }
+
+    public boolean equals(Object o) {
+      if (!(o instanceof ResetStatement)) {
+        return false;
+      }
+      ResetStatement other = (ResetStatement) o;
+      return Objects.equals(this.name, other.name);
+    }
+
+    @Override
+    public String toString() {
+      return "reset " + (name == null ? "all" : getKey());
+    }
+  }
+
+  static class ShowStatement extends SessionStatement {
+
+    static ShowStatement createShowAll() {
+      return new ShowStatement(null);
+    }
+
+    ShowStatement(TableOrIndexName name) {
+      super(name);
+    }
+
+    @Override
+    public StatementResult execute(SessionState sessionState) {
+      if (name != null) {
+        return new QueryResult(
+            ResultSets.forRows(
+                Type.struct(StructField.of(getKey(), Type.string())),
+                ImmutableList.of(
+                    Struct.newBuilder()
+                        .set(getKey())
+                        .to(sessionState.get(extension, name).getSetting())
+                        .build())));
+      }
+      return new QueryResult(
+          ResultSets.forRows(
+              Type.struct(
+                  StructField.of("name", Type.string()),
+                  StructField.of("setting", Type.string()),
+                  StructField.of("description", Type.string())),
+              sessionState.getAll().stream()
+                  .map(
+                      setting ->
+                          Struct.newBuilder()
+                              .set("name")
+                              .to(setting.getKey())
+                              .set("setting")
+                              .to(setting.getSetting())
+                              .set("description")
+                              .to(setting.getShortDesc())
+                              .build())
+                  .collect(Collectors.toList())));
+    }
+
+    public boolean equals(Object o) {
+      if (!(o instanceof ShowStatement)) {
+        return false;
+      }
+      ShowStatement other = (ShowStatement) o;
+      return Objects.equals(this.name, other.name);
+    }
+
+    @Override
+    public String toString() {
+      return "show " + (name == null ? "all" : getKey());
+    }
+  }
+
+  private static String toParameterExtension(TableOrIndexName name) {
+    return name.schema == null ? null : unquote(name.schema).toLowerCase(Locale.ROOT);
+  }
+
+  private static String toParameterName(TableOrIndexName name) {
+    return unquote(name.name).toLowerCase(Locale.ROOT);
+  }
+
+  private static String unquote(String value) {
+    if (value == null) {
+      return null;
+    }
+    if (value.length() < 2) {
+      return value;
+    }
+    if (value.charAt(0) == '"' && value.charAt(value.length() - 1) == '"') {
+      return value.substring(1, value.length() - 1);
+    }
+    if (value.charAt(0) == '\'' && value.charAt(value.length() - 1) == '\'') {
+      return value.substring(1, value.length() - 1);
+    }
+    return value;
+  }
+
+  public static @Nullable SessionStatement parse(ParsedStatement parsedStatement) {
+    if (parsedStatement.getType() == StatementType.CLIENT_SIDE) {
+      // This statement is handled by the Connection API.
+      return null;
+    }
+    SimpleParser parser = new SimpleParser(parsedStatement.getSqlWithoutComments());
+    if (parser.eat("set")) {
+      return parseSetStatement(parser);
+    }
+    if (parser.eat("reset")) {
+      return parseResetStatement(parser);
+    }
+    if (parser.eat("show")) {
+      return parseShowStatement(parser);
+    }
+
+    return null;
+  }
+
+  static SetStatement parseSetStatement(SimpleParser parser) {
+    SetStatement.Builder builder = new Builder();
+    if (parser.eat("local")) {
+      builder.setLocal();
+    } else {
+      // Ignore, this is the default.
+      parser.eat("session");
+    }
+    TableOrIndexName name = parser.readTableOrIndexName();
+    if (name == null) {
+      throw SpannerExceptionFactory.newSpannerException(
+          ErrorCode.INVALID_ARGUMENT,
+          "Invalid SET statement: " + parser.getSql() + ". Expected configuration parameter name.");
+    }
+    builder.setName(name);
+
+    if (!(parser.eat("to") || parser.eat("="))) {
+      throw SpannerExceptionFactory.newSpannerException(
+          ErrorCode.INVALID_ARGUMENT,
+          "Invalid SET statement: " + parser.getSql() + ". Expected TO or =.");
+    }
+
+    String value = parser.parseExpression();
+    if (value == null) {
+      throw SpannerExceptionFactory.newSpannerException(
+          ErrorCode.INVALID_ARGUMENT,
+          "Invalid SET statement: " + parser.getSql() + ". Expected value.");
+    }
+    if ("default".equalsIgnoreCase(value)) {
+      builder.setValue(null);
+    } else {
+      builder.setValue(value);
+    }
+    String remaining = parser.parseExpression();
+    if (!Strings.isNullOrEmpty(remaining)) {
+      throw SpannerExceptionFactory.newSpannerException(
+          ErrorCode.INVALID_ARGUMENT,
+          "Invalid SET statement: "
+              + parser.getSql()
+              + ". Expected end of statement after "
+              + value);
+    }
+
+    return builder.build();
+  }
+
+  static ResetStatement parseResetStatement(SimpleParser parser) {
+    if (parser.eat("all")) {
+      String remaining = parser.parseExpression();
+      if (!Strings.isNullOrEmpty(remaining)) {
+        throw SpannerExceptionFactory.newSpannerException(
+            ErrorCode.INVALID_ARGUMENT,
+            "Invalid SHOW statement: "
+                + parser.getSql()
+                + ". Expected end of statement after \"all\"");
+      }
+      return ResetStatement.createResetAll();
+    }
+    TableOrIndexName name = parser.readTableOrIndexName();
+    if (name == null) {
+      throw SpannerExceptionFactory.newSpannerException(
+          ErrorCode.INVALID_ARGUMENT,
+          "Invalid RESET statement: "
+              + parser.getSql()
+              + ". Expected configuration parameter name.");
+    }
+    String remaining = parser.parseExpression();
+    if (!Strings.isNullOrEmpty(remaining)) {
+      throw SpannerExceptionFactory.newSpannerException(
+          ErrorCode.INVALID_ARGUMENT,
+          "Invalid RESET statement: "
+              + parser.getSql()
+              + ". Expected end of statement after "
+              + name);
+    }
+    return new ResetStatement(name);
+  }
+
+  static ShowStatement parseShowStatement(SimpleParser parser) {
+    if (parser.eat("all")) {
+      String remaining = parser.parseExpression();
+      if (!Strings.isNullOrEmpty(remaining)) {
+        throw SpannerExceptionFactory.newSpannerException(
+            ErrorCode.INVALID_ARGUMENT,
+            "Invalid SHOW statement: "
+                + parser.getSql()
+                + ". Expected end of statement after \"all\"");
+      }
+      return ShowStatement.createShowAll();
+    }
+    TableOrIndexName name = parser.readTableOrIndexName();
+    if (name == null) {
+      throw SpannerExceptionFactory.newSpannerException(
+          ErrorCode.INVALID_ARGUMENT,
+          "Invalid SHOW statement: "
+              + parser.getSql()
+              + ". Expected configuration parameter name or ALL.");
+    }
+    String remaining = parser.parseExpression();
+    if (!Strings.isNullOrEmpty(remaining)) {
+      throw SpannerExceptionFactory.newSpannerException(
+          ErrorCode.INVALID_ARGUMENT,
+          "Invalid SHOW statement: "
+              + parser.getSql()
+              + ". Expected end of statement after "
+              + name);
+    }
+    return new ShowStatement(name);
+  }
+}
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/local/SetSearchPathStatement.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/local/SetSearchPathStatement.java
deleted file mode 100644
index a2c1b2e5..00000000
--- a/src/main/java/com/google/cloud/spanner/pgadapter/statements/local/SetSearchPathStatement.java
+++ /dev/null
@@ -1,69 +0,0 @@
-// 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.statements.local;
-
-import com.google.api.core.InternalApi;
-import com.google.cloud.spanner.connection.StatementResult;
-import com.google.cloud.spanner.pgadapter.statements.BackendConnection;
-import com.google.cloud.spanner.pgadapter.statements.BackendConnection.NoResult;
-
-/**
- * A no-op SET search_path implementation. This should be removed once support has been added to the
- * Connection API.
- */
-// TODO: Remove this once search_path support has been added to the Connection API.
-@InternalApi
-public class SetSearchPathStatement implements LocalStatement {
-  public static final SetSearchPathStatement INSTANCE = new SetSearchPathStatement();
-
-  private SetSearchPathStatement() {}
-
-  @Override
-  public String[] getSql() {
-    return new String[] {
-      "set search_path to public",
-      "SET search_path TO public",
-      "SET SEARCH_PATH TO public",
-      "set search_path to \"public\"",
-      "SET search_path TO \"public\"",
-      "SET SEARCH_PATH TO \"public\"",
-      "set search_path to \"$user\", public",
-      "SET search_path TO \"$user\", public",
-      "SET SEARCH_PATH TO \"$user\", public",
-      "set search_path to \"$user\", \"public\"",
-      "SET search_path TO \"$user\", \"public\"",
-      "SET SEARCH_PATH TO \"$user\", \"public\"",
-      // Some tools prepend the current search_path with 'public' without properly checking whether
-      // 'public' is already part of the existing search path.
-      "set search_path to public, public",
-      "SET search_path TO public, public",
-      "SET SEARCH_PATH TO public, public",
-      "set search_path to public, \"public\"",
-      "SET search_path TO public, \"public\"",
-      "SET SEARCH_PATH TO public, \"public\"",
-      "set search_path to public, \"$user\", public",
-      "SET search_path TO public, \"$user\", public",
-      "SET SEARCH_PATH TO public, \"$user\", public",
-      "set search_path to public, \"$user\", \"public\"",
-      "SET search_path TO public, \"$user\", \"public\"",
-      "SET SEARCH_PATH TO public, \"$user\", \"public\"",
-    };
-  }
-
-  @Override
-  public StatementResult execute(BackendConnection backendConnection) {
-    return new NoResult("SET");
-  }
-}
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/local/ShowSearchPathStatement.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/local/ShowSearchPathStatement.java
deleted file mode 100644
index c906a35b..00000000
--- a/src/main/java/com/google/cloud/spanner/pgadapter/statements/local/ShowSearchPathStatement.java
+++ /dev/null
@@ -1,53 +0,0 @@
-// 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.statements.local;
-
-import com.google.api.core.InternalApi;
-import com.google.cloud.spanner.ResultSet;
-import com.google.cloud.spanner.ResultSets;
-import com.google.cloud.spanner.Struct;
-import com.google.cloud.spanner.Type;
-import com.google.cloud.spanner.Type.StructField;
-import com.google.cloud.spanner.connection.StatementResult;
-import com.google.cloud.spanner.pgadapter.statements.BackendConnection;
-import com.google.cloud.spanner.pgadapter.statements.BackendConnection.QueryResult;
-import com.google.common.collect.ImmutableList;
-
-@InternalApi
-public class ShowSearchPathStatement implements LocalStatement {
-  public static final ShowSearchPathStatement INSTANCE = new ShowSearchPathStatement();
-
-  private ShowSearchPathStatement() {}
-
-  @Override
-  public String[] getSql() {
-    return new String[] {
-      "show search_path", "SHOW search_path", "SHOW SEARCH_PATH",
-    };
-  }
-
-  @Override
-  public StatementResult execute(BackendConnection backendConnection) {
-    ResultSet resultSet =
-        ResultSets.forRows(
-            Type.struct(StructField.of("search_path", Type.string())),
-            ImmutableList.of(
-                Struct.newBuilder()
-                    .set("search_path")
-                    .to(backendConnection.getCurrentSchema())
-                    .build()));
-    return new QueryResult(resultSet);
-  }
-}
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/local/ShowServerVersionStatement.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/local/ShowServerVersionStatement.java
deleted file mode 100644
index abf96f23..00000000
--- a/src/main/java/com/google/cloud/spanner/pgadapter/statements/local/ShowServerVersionStatement.java
+++ /dev/null
@@ -1,51 +0,0 @@
-// 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.statements.local;
-
-import com.google.api.core.InternalApi;
-import com.google.cloud.spanner.ResultSet;
-import com.google.cloud.spanner.ResultSets;
-import com.google.cloud.spanner.Struct;
-import com.google.cloud.spanner.Type;
-import com.google.cloud.spanner.Type.StructField;
-import com.google.cloud.spanner.connection.StatementResult;
-import com.google.cloud.spanner.pgadapter.statements.BackendConnection;
-import com.google.cloud.spanner.pgadapter.statements.BackendConnection.QueryResult;
-import com.google.common.collect.ImmutableList;
-
-@InternalApi
-public class ShowServerVersionStatement implements LocalStatement {
-  public static final ShowServerVersionStatement INSTANCE = new ShowServerVersionStatement();
-
-  private ShowServerVersionStatement() {}
-
-  @Override
-  public String[] getSql() {
-    return new String[] {"show server_version", "SHOW server_version", "SHOW SERVER_VERSION"};
-  }
-
-  @Override
-  public StatementResult execute(BackendConnection backendConnection) {
-    ResultSet resultSet =
-        ResultSets.forRows(
-            Type.struct(StructField.of("server_version", Type.string())),
-            ImmutableList.of(
-                Struct.newBuilder()
-                    .set("server_version")
-                    .to(backendConnection.getOptionsMetadata().getServerVersion())
-                    .build()));
-    return new QueryResult(resultSet);
-  }
-}
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java b/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java
index e9b3f210..5f3f17c9 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java
@@ -21,9 +21,6 @@ import com.google.cloud.spanner.pgadapter.statements.local.LocalStatement;
 import com.google.cloud.spanner.pgadapter.statements.local.SelectCurrentCatalogStatement;
 import com.google.cloud.spanner.pgadapter.statements.local.SelectCurrentDatabaseStatement;
 import com.google.cloud.spanner.pgadapter.statements.local.SelectCurrentSchemaStatement;
-import com.google.cloud.spanner.pgadapter.statements.local.SetSearchPathStatement;
-import com.google.cloud.spanner.pgadapter.statements.local.ShowSearchPathStatement;
-import com.google.cloud.spanner.pgadapter.statements.local.ShowServerVersionStatement;
 import com.google.common.collect.ImmutableList;
 import java.util.List;
 import java.util.Map;
@@ -40,10 +37,7 @@ public class ClientAutoDetector {
       ImmutableList.of(
           SelectCurrentSchemaStatement.INSTANCE,
           SelectCurrentDatabaseStatement.INSTANCE,
-          SelectCurrentCatalogStatement.INSTANCE,
-          ShowSearchPathStatement.INSTANCE,
-          ShowServerVersionStatement.INSTANCE,
-          SetSearchPathStatement.INSTANCE);
+          SelectCurrentCatalogStatement.INSTANCE);
 
   public enum WellKnownClient {
     PSQL {
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
new file mode 100644
index 00000000..2b916996
--- /dev/null
+++ b/src/main/resources/com/google/cloud/spanner/pgadapter/session/pg_settings.txt
@@ -0,0 +1,308 @@
+allow_system_table_mods	off	\N	Developer Options	Allows modifications of the structure of system tables.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+application_name	\N	\N	Reporting and Logging / What to Log	Sets the application name to be reported in statistics and logs.	\N	user	string	client	\N	\N	\N		\N	\N	\N	f
+archive_cleanup_command		\N	Write-Ahead Log / Archive Recovery	Sets the shell command that will be executed at every restart point.	\N	sighup	string	default	\N	\N	\N			\N	\N	f
+archive_command	(disabled)	\N	Write-Ahead Log / Archiving	Sets the shell command that will be called to archive a WAL file.	\N	sighup	string	default	\N	\N	\N			\N	\N	f
+archive_mode	off	\N	Write-Ahead Log / Archiving	Allows archiving of WAL files using archive_command.	\N	postmaster	enum	default	\N	\N	{always,on,off}	off	off	\N	\N	f
+archive_timeout	0	s	Write-Ahead Log / Archiving	Forces a switch to the next WAL file if a new file has not been started within N seconds.	\N	sighup	integer	default	0	1073741823	\N	0	0	\N	\N	f
+array_nulls	on	\N	Version and Platform Compatibility / Previous PostgreSQL Versions	Enable input of NULL elements in arrays.	When turned on, unquoted NULL in an array input value means a null value; otherwise it is taken literally.	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+authentication_timeout	60	s	Connections and Authentication / Authentication	Sets the maximum allowed time to complete client authentication.	\N	sighup	integer	default	1	600	\N	60	60	\N	\N	f
+autovacuum	on	\N	Autovacuum	Starts the autovacuum subprocess.	\N	sighup	bool	default	\N	\N	\N	on	on	\N	\N	f
+autovacuum_analyze_scale_factor	0.1	\N	Autovacuum	Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.	\N	sighup	real	default	0	100	\N	0.1	0.1	\N	\N	f
+autovacuum_analyze_threshold	50	\N	Autovacuum	Minimum number of tuple inserts, updates, or deletes prior to analyze.	\N	sighup	integer	default	0	2147483647	\N	50	50	\N	\N	f
+autovacuum_freeze_max_age	200000000	\N	Autovacuum	Age at which to autovacuum a table to prevent transaction ID wraparound.	\N	postmaster	integer	default	100000	2000000000	\N	200000000	200000000	\N	\N	f
+autovacuum_max_workers	3	\N	Autovacuum	Sets the maximum number of simultaneously running autovacuum worker processes.	\N	postmaster	integer	default	1	262143	\N	3	3	\N	\N	f
+autovacuum_multixact_freeze_max_age	400000000	\N	Autovacuum	Multixact age at which to autovacuum a table to prevent multixact wraparound.	\N	postmaster	integer	default	10000	2000000000	\N	400000000	400000000	\N	\N	f
+autovacuum_naptime	60	s	Autovacuum	Time to sleep between autovacuum runs.	\N	sighup	integer	default	1	2147483	\N	60	60	\N	\N	f
+autovacuum_vacuum_cost_delay	2	ms	Autovacuum	Vacuum cost delay in milliseconds, for autovacuum.	\N	sighup	real	default	-1	100	\N	2	2	\N	\N	f
+autovacuum_vacuum_cost_limit	-1	\N	Autovacuum	Vacuum cost amount available before napping, for autovacuum.	\N	sighup	integer	default	-1	10000	\N	-1	-1	\N	\N	f
+autovacuum_vacuum_insert_scale_factor	0.2	\N	Autovacuum	Number of tuple inserts prior to vacuum as a fraction of reltuples.	\N	sighup	real	default	0	100	\N	0.2	0.2	\N	\N	f
+autovacuum_vacuum_insert_threshold	1000	\N	Autovacuum	Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums.	\N	sighup	integer	default	-1	2147483647	\N	1000	1000	\N	\N	f
+autovacuum_vacuum_scale_factor	0.2	\N	Autovacuum	Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.	\N	sighup	real	default	0	100	\N	0.2	0.2	\N	\N	f
+autovacuum_vacuum_threshold	50	\N	Autovacuum	Minimum number of tuple updates or deletes prior to vacuum.	\N	sighup	integer	default	0	2147483647	\N	50	50	\N	\N	f
+autovacuum_work_mem	-1	kB	Resource Usage / Memory	Sets the maximum memory to be used by each autovacuum worker process.	\N	sighup	integer	default	-1	2147483647	\N	-1	-1	\N	\N	f
+backend_flush_after	0	8kB	Resource Usage / Asynchronous Behavior	Number of pages after which previously performed writes are flushed to disk.	\N	user	integer	default	0	256	\N	0	0	\N	\N	f
+backslash_quote	safe_encoding	\N	Version and Platform Compatibility / Previous PostgreSQL Versions	Sets whether "\\'" is allowed in string literals.	\N	user	enum	default	\N	\N	{safe_encoding,on,off}	safe_encoding	safe_encoding	\N	\N	f
+backtrace_functions		\N	Developer Options	Log backtrace for errors in these functions.	\N	superuser	string	default	\N	\N	\N			\N	\N	f
+bgwriter_delay	200	ms	Resource Usage / Background Writer	Background writer sleep time between rounds.	\N	sighup	integer	default	10	10000	\N	200	200	\N	\N	f
+bgwriter_flush_after	0	8kB	Resource Usage / Background Writer	Number of pages after which previously performed writes are flushed to disk.	\N	sighup	integer	default	0	256	\N	0	0	\N	\N	f
+bgwriter_lru_maxpages	100	\N	Resource Usage / Background Writer	Background writer maximum number of LRU pages to flush per round.	\N	sighup	integer	default	0	1073741823	\N	100	100	\N	\N	f
+bgwriter_lru_multiplier	2	\N	Resource Usage / Background Writer	Multiple of the average buffer usage to free per round.	\N	sighup	real	default	0	10	\N	2	2	\N	\N	f
+block_size	8192	\N	Preset Options	Shows the size of a disk block.	\N	internal	integer	default	8192	8192	\N	8192	8192	\N	\N	f
+bonjour	off	\N	Connections and Authentication / Connection Settings	Enables advertising the server via Bonjour.	\N	postmaster	bool	default	\N	\N	\N	off	off	\N	\N	f
+bonjour_name		\N	Connections and Authentication / Connection Settings	Sets the Bonjour service name.	\N	postmaster	string	default	\N	\N	\N			\N	\N	f
+bytea_output	hex	\N	Client Connection Defaults / Statement Behavior	Sets the output format for bytea.	\N	user	enum	default	\N	\N	{escape,hex}	hex	hex	\N	\N	f
+check_function_bodies	on	\N	Client Connection Defaults / Statement Behavior	Check function bodies during CREATE FUNCTION.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+checkpoint_completion_target	0.5	\N	Write-Ahead Log / Checkpoints	Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.	\N	sighup	real	default	0	1	\N	0.5	0.5	\N	\N	f
+checkpoint_flush_after	0	8kB	Write-Ahead Log / Checkpoints	Number of pages after which previously performed writes are flushed to disk.	\N	sighup	integer	default	0	256	\N	0	0	\N	\N	f
+checkpoint_timeout	300	s	Write-Ahead Log / Checkpoints	Sets the maximum time between automatic WAL checkpoints.	\N	sighup	integer	default	30	86400	\N	300	300	\N	\N	f
+checkpoint_warning	30	s	Write-Ahead Log / Checkpoints	Enables warnings if checkpoint segments are filled more frequently than this.	Write a message to the server log if checkpoints caused by the filling of checkpoint segment files happens more frequently than this number of seconds. Zero turns off the warning.	sighup	integer	default	0	2147483647	\N	30	30	\N	\N	f
+client_encoding	UTF8	\N	Client Connection Defaults / Locale and Formatting	Sets the client's character set encoding.	\N	user	string	default	\N	\N	\N	SQL_ASCII	UTF8	\N	\N	f
+client_min_messages	notice	\N	Client Connection Defaults / Statement Behavior	Sets the message levels that are sent to the client.	Each level includes all the levels that follow it. The later the level, the fewer messages are sent.	user	enum	default	\N	\N	{debug5,debug4,debug3,debug2,debug1,log,notice,warning,error}	notice	notice	\N	\N	f
+cluster_name		\N	Process Title	Sets the name of the cluster, which is included in the process title.	\N	postmaster	string	default	\N	\N	\N			\N	\N	f
+commit_delay	0	\N	Write-Ahead Log / Settings	Sets the delay in microseconds between transaction commit and flushing WAL to disk.	\N	superuser	integer	default	0	100000	\N	0	0	\N	\N	f
+commit_siblings	5	\N	Write-Ahead Log / Settings	Sets the minimum concurrent open transactions before performing commit_delay.	\N	user	integer	default	0	1000	\N	5	5	\N	\N	f
+constraint_exclusion	partition	\N	Query Tuning / Other Planner Options	Enables the planner to use constraints to optimize queries.	Table scans will be skipped if their constraints guarantee that no rows match the query.	user	enum	default	\N	\N	{partition,on,off}	partition	partition	\N	\N	f
+cpu_index_tuple_cost	0.005	\N	Query Tuning / Planner Cost Constants	Sets the planner's estimate of the cost of processing each index entry during an index scan.	\N	user	real	default	0	1.79769e+308	\N	0.005	0.005	\N	\N	f
+cpu_operator_cost	0.0025	\N	Query Tuning / Planner Cost Constants	Sets the planner's estimate of the cost of processing each operator or function call.	\N	user	real	default	0	1.79769e+308	\N	0.0025	0.0025	\N	\N	f
+cpu_tuple_cost	0.01	\N	Query Tuning / Planner Cost Constants	Sets the planner's estimate of the cost of processing each tuple (row).	\N	user	real	default	0	1.79769e+308	\N	0.01	0.01	\N	\N	f
+cursor_tuple_fraction	0.1	\N	Query Tuning / Other Planner Options	Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.	\N	user	real	default	0	1	\N	0.1	0.1	\N	\N	f
+data_checksums	off	\N	Preset Options	Shows whether data checksums are turned on for this cluster.	\N	internal	bool	override	\N	\N	\N	off	off	\N	\N	f
+data_directory_mode	0700	\N	Preset Options	Mode of the data directory.	The parameter value is a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).)	internal	integer	default	0	511	\N	448	448	\N	\N	f
+data_sync_retry	off	\N	Error Handling	Whether to continue running after a failure to sync data files.	\N	postmaster	bool	default	\N	\N	\N	off	off	\N	\N	f
+DateStyle	ISO, MDY	\N	Client Connection Defaults / Locale and Formatting	Sets the display format for date and time values.	Also controls interpretation of ambiguous date inputs.	user	string	configuration file	\N	\N	\N	ISO, MDY	ISO, MDY	\N	\N	f
+db_user_namespace	off	\N	Connections and Authentication / Authentication	Enables per-database user names.	\N	sighup	bool	default	\N	\N	\N	off	off	\N	\N	f
+deadlock_timeout	1000	ms	Lock Management	Sets the time to wait on a lock before checking for deadlock.	\N	superuser	integer	default	1	2147483647	\N	1000	1000	\N	\N	f
+debug_assertions	off	\N	Preset Options	Shows whether the running server has assertion checks enabled.	\N	internal	bool	default	\N	\N	\N	off	off	\N	\N	f
+debug_pretty_print	on	\N	Reporting and Logging / What to Log	Indents parse and plan tree displays.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+debug_print_parse	off	\N	Reporting and Logging / What to Log	Logs each query's parse tree.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+debug_print_plan	off	\N	Reporting and Logging / What to Log	Logs each query's execution plan.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+debug_print_rewritten	off	\N	Reporting and Logging / What to Log	Logs each query's rewritten parse tree.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+default_statistics_target	100	\N	Query Tuning / Other Planner Options	Sets the default statistics target.	This applies to table columns that have not had a column-specific target set via ALTER TABLE SET STATISTICS.	user	integer	default	1	10000	\N	100	100	\N	\N	f
+default_table_access_method	heap	\N	Client Connection Defaults / Statement Behavior	Sets the default table access method for new tables.	\N	user	string	default	\N	\N	\N	heap	heap	\N	\N	f
+default_tablespace		\N	Client Connection Defaults / Statement Behavior	Sets the default tablespace to create tables and indexes in.	An empty string selects the database's default tablespace.	user	string	default	\N	\N	\N			\N	\N	f
+default_text_search_config	pg_catalog.english	\N	Client Connection Defaults / Locale and Formatting	Sets default text search configuration.	\N	user	string	configuration file	\N	\N	\N	pg_catalog.simple	pg_catalog.english	\N	\N	f
+default_transaction_deferrable	off	\N	Client Connection Defaults / Statement Behavior	Sets the default deferrable status of new transactions.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+default_transaction_isolation	read committed	\N	Client Connection Defaults / Statement Behavior	Sets the transaction isolation level of each new transaction.	\N	user	enum	default	\N	\N	{serializable,"repeatable read","read committed","read uncommitted"}	read committed	read committed	\N	\N	f
+default_transaction_read_only	off	\N	Client Connection Defaults / Statement Behavior	Sets the default read-only status of new transactions.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+dynamic_shared_memory_type	posix	\N	Resource Usage / Memory	Selects the dynamic shared memory implementation used.	\N	postmaster	enum	configuration file	\N	\N	{posix,sysv,mmap}	posix	posix	\N	\N	f
+effective_cache_size	524288	8kB	Query Tuning / Planner Cost Constants	Sets the planner's assumption about the total size of the data caches.	That is, the total size of the caches (kernel cache and shared buffers) used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each.	user	integer	default	1	2147483647	\N	524288	524288	\N	\N	f
+effective_io_concurrency	0	\N	Resource Usage / Asynchronous Behavior	Number of simultaneous requests that can be handled efficiently by the disk subsystem.	\N	user	integer	default	0	1000	\N	0	0	\N	\N	f
+enable_bitmapscan	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of bitmap-scan plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_gathermerge	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of gather merge plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_hashagg	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of hashed aggregation plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_hashjoin	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of hash join plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_incremental_sort	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of incremental sort steps.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_indexonlyscan	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of index-only-scan plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_indexscan	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of index-scan plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_material	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of materialization.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_mergejoin	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of merge join plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_nestloop	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of nested-loop join plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_parallel_append	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of parallel append plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_parallel_hash	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of parallel hash plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_partition_pruning	on	\N	Query Tuning / Planner Method Configuration	Enables plan-time and run-time partition pruning.	Allows the query planner and executor to compare partition bounds to conditions in the query to determine which partitions must be scanned.	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_partitionwise_aggregate	off	\N	Query Tuning / Planner Method Configuration	Enables partitionwise aggregation and grouping.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+enable_partitionwise_join	off	\N	Query Tuning / Planner Method Configuration	Enables partitionwise join.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+enable_seqscan	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of sequential-scan plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_sort	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of explicit sort steps.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+enable_tidscan	on	\N	Query Tuning / Planner Method Configuration	Enables the planner's use of TID scan plans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+escape_string_warning	on	\N	Version and Platform Compatibility / Previous PostgreSQL Versions	Warn about backslash escapes in ordinary string literals.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+event_source	PostgreSQL	\N	Reporting and Logging / Where to Log	Sets the application name used to identify PostgreSQL messages in the event log.	\N	postmaster	string	default	\N	\N	\N	PostgreSQL	PostgreSQL	\N	\N	f
+exit_on_error	off	\N	Error Handling	Terminate session on any error.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+extra_float_digits	1	\N	Client Connection Defaults / Locale and Formatting	Sets the number of digits displayed for floating-point values.	This affects real, double precision, and geometric data types. A zero or negative parameter value is added to the standard number of digits (FLT_DIG or DBL_DIG as appropriate). Any value greater than zero selects precise output mode.	user	integer	default	-15	3	\N	1	1	\N	\N	f
+force_parallel_mode	off	\N	Query Tuning / Other Planner Options	Forces use of parallel query facilities.	If possible, run query using a parallel worker and with parallel restrictions.	user	enum	default	\N	\N	{off,on,regress}	off	off	\N	\N	f
+from_collapse_limit	8	\N	Query Tuning / Other Planner Options	Sets the FROM-list size beyond which subqueries are not collapsed.	The planner will merge subqueries into upper queries if the resulting FROM list would have no more than this many items.	user	integer	default	1	2147483647	\N	8	8	\N	\N	f
+fsync	on	\N	Write-Ahead Log / Settings	Forces synchronization of updates to disk.	The server will use the fsync() system call in several places to make sure that updates are physically written to disk. This insures that a database cluster will recover to a consistent state after an operating system or hardware crash.	sighup	bool	default	\N	\N	\N	on	on	\N	\N	f
+full_page_writes	on	\N	Write-Ahead Log / Settings	Writes full pages to WAL when first modified after a checkpoint.	A page write in process during an operating system crash might be only partially written to disk.  During recovery, the row changes stored in WAL are not enough to recover.  This option writes pages when first modified after a checkpoint to WAL so full recovery is possible.	sighup	bool	default	\N	\N	\N	on	on	\N	\N	f
+geqo	on	\N	Query Tuning / Genetic Query Optimizer	Enables genetic query optimization.	This algorithm attempts to do planning without exhaustive searching.	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+geqo_effort	5	\N	Query Tuning / Genetic Query Optimizer	GEQO: effort is used to set the default for other GEQO parameters.	\N	user	integer	default	1	10	\N	5	5	\N	\N	f
+geqo_generations	0	\N	Query Tuning / Genetic Query Optimizer	GEQO: number of iterations of the algorithm.	Zero selects a suitable default value.	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+geqo_pool_size	0	\N	Query Tuning / Genetic Query Optimizer	GEQO: number of individuals in the population.	Zero selects a suitable default value.	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+geqo_seed	0	\N	Query Tuning / Genetic Query Optimizer	GEQO: seed for random path selection.	\N	user	real	default	0	1	\N	0	0	\N	\N	f
+geqo_selection_bias	2	\N	Query Tuning / Genetic Query Optimizer	GEQO: selective pressure within the population.	\N	user	real	default	1.5	2	\N	2	2	\N	\N	f
+geqo_threshold	12	\N	Query Tuning / Genetic Query Optimizer	Sets the threshold of FROM items beyond which GEQO is used.	\N	user	integer	default	2	2147483647	\N	12	12	\N	\N	f
+gin_fuzzy_search_limit	0	\N	Client Connection Defaults / Other Defaults	Sets the maximum allowed result for exact search by GIN.	\N	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+gin_pending_list_limit	4096	kB	Client Connection Defaults / Statement Behavior	Sets the maximum size of the pending list for GIN index.	\N	user	integer	default	64	2147483647	\N	4096	4096	\N	\N	f
+hash_mem_multiplier	1	\N	Resource Usage / Memory	Multiple of work_mem to use for hash tables.	\N	user	real	default	1	1000	\N	1	1	\N	\N	f
+hot_standby	on	\N	Replication / Standby Servers	Allows connections and queries during recovery.	\N	postmaster	bool	default	\N	\N	\N	on	on	\N	\N	f
+hot_standby_feedback	off	\N	Replication / Standby Servers	Allows feedback from a hot standby to the primary that will avoid query conflicts.	\N	sighup	bool	default	\N	\N	\N	off	off	\N	\N	f
+huge_pages	try	\N	Resource Usage / Memory	Use of huge pages on Linux or Windows.	\N	postmaster	enum	default	\N	\N	{off,on,try}	try	try	\N	\N	f
+idle_in_transaction_session_timeout	0	ms	Client Connection Defaults / Statement Behavior	Sets the maximum allowed duration of any idling transaction.	A value of 0 turns off the timeout.	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+ignore_checksum_failure	off	\N	Developer Options	Continues processing after a checksum failure.	Detection of a checksum failure normally causes PostgreSQL to report an error, aborting the current transaction. Setting ignore_checksum_failure to true causes the system to ignore the failure (but still report a warning), and continue processing. This behavior could cause crashes or other serious problems. Only has an effect if checksums are enabled.	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+ignore_invalid_pages	off	\N	Developer Options	Continues recovery after an invalid pages failure.	Detection of WAL records having references to invalid pages during recovery causes PostgreSQL to raise a PANIC-level error, aborting the recovery. Setting ignore_invalid_pages to true causes the system to ignore invalid page references in WAL records (but still report a warning), and continue recovery. This behavior may cause crashes, data loss, propagate or hide corruption, or other serious problems. Only has an effect during recovery or in standby mode.	postmaster	bool	default	\N	\N	\N	off	off	\N	\N	f
+ignore_system_indexes	off	\N	Developer Options	Disables reading from system indexes.	It does not prevent updating the indexes, so it is safe to use.  The worst consequence is slowness.	backend	bool	default	\N	\N	\N	off	off	\N	\N	f
+integer_datetimes	on	\N	Preset Options	Datetimes are integer based.	\N	internal	bool	default	\N	\N	\N	on	on	\N	\N	f
+IntervalStyle	postgres	\N	Client Connection Defaults / Locale and Formatting	Sets the display format for interval values.	\N	user	enum	default	\N	\N	{postgres,postgres_verbose,sql_standard,iso_8601}	postgres	postgres	\N	\N	f
+jit	on	\N	Query Tuning / Other Planner Options	Allow JIT compilation.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+jit_above_cost	100000	\N	Query Tuning / Planner Cost Constants	Perform JIT compilation if query is more expensive.	-1 disables JIT compilation.	user	real	default	-1	1.79769e+308	\N	100000	100000	\N	\N	f
+jit_debugging_support	off	\N	Developer Options	Register JIT compiled function with debugger.	\N	superuser-backend	bool	default	\N	\N	\N	off	off	\N	\N	f
+jit_dump_bitcode	off	\N	Developer Options	Write out LLVM bitcode to facilitate JIT debugging.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+jit_expressions	on	\N	Developer Options	Allow JIT compilation of expressions.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+jit_inline_above_cost	500000	\N	Query Tuning / Planner Cost Constants	Perform JIT inlining if query is more expensive.	-1 disables inlining.	user	real	default	-1	1.79769e+308	\N	500000	500000	\N	\N	f
+jit_optimize_above_cost	500000	\N	Query Tuning / Planner Cost Constants	Optimize JITed functions if query is more expensive.	-1 disables optimization.	user	real	default	-1	1.79769e+308	\N	500000	500000	\N	\N	f
+jit_profiling_support	off	\N	Developer Options	Register JIT compiled function with perf profiler.	\N	superuser-backend	bool	default	\N	\N	\N	off	off	\N	\N	f
+jit_tuple_deforming	on	\N	Developer Options	Allow JIT compilation of tuple deforming.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+join_collapse_limit	8	\N	Query Tuning / Other Planner Options	Sets the FROM-list size beyond which JOIN constructs are not flattened.	The planner will flatten explicit JOIN constructs into lists of FROM items whenever a list of no more than this many items would result.	user	integer	default	1	2147483647	\N	8	8	\N	\N	f
+krb_caseins_users	off	\N	Connections and Authentication / Authentication	Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.	\N	sighup	bool	default	\N	\N	\N	off	off	\N	\N	f
+lc_collate	C	\N	Client Connection Defaults / Locale and Formatting	Shows the collation order locale.	\N	internal	string	override	\N	\N	\N	C	C	\N	\N	f
+lc_ctype	C	\N	Client Connection Defaults / Locale and Formatting	Shows the character classification and case conversion locale.	\N	internal	string	override	\N	\N	\N	C	C	\N	\N	f
+lc_messages	C	\N	Client Connection Defaults / Locale and Formatting	Sets the language in which messages are displayed.	\N	superuser	string	configuration file	\N	\N	\N		C	\N	\N	f
+lc_monetary	C	\N	Client Connection Defaults / Locale and Formatting	Sets the locale for formatting monetary amounts.	\N	user	string	configuration file	\N	\N	\N	C	C	\N	\N	f
+lc_numeric	C	\N	Client Connection Defaults / Locale and Formatting	Sets the locale for formatting numbers.	\N	user	string	configuration file	\N	\N	\N	C	C	\N	\N	f
+lc_time	C	\N	Client Connection Defaults / Locale and Formatting	Sets the locale for formatting date and time values.	\N	user	string	configuration file	\N	\N	\N	C	C	\N	\N	f
+listen_addresses	*	\N	Connections and Authentication / Connection Settings	Sets the host name or IP address(es) to listen to.	\N	postmaster	string	configuration file	\N	\N	\N	localhost	*	\N	\N	f
+lo_compat_privileges	off	\N	Version and Platform Compatibility / Previous PostgreSQL Versions	Enables backward compatibility mode for privilege checks on large objects.	Skips privilege checks when reading or modifying large objects, for compatibility with PostgreSQL releases prior to 9.0.	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+local_preload_libraries		\N	Client Connection Defaults / Shared Library Preloading	Lists unprivileged shared libraries to preload into each backend.	\N	user	string	default	\N	\N	\N			\N	\N	f
+lock_timeout	0	ms	Client Connection Defaults / Statement Behavior	Sets the maximum allowed duration of any wait for a lock.	A value of 0 turns off the timeout.	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+log_autovacuum_min_duration	-1	ms	Reporting and Logging / What to Log	Sets the minimum execution time above which autovacuum actions will be logged.	Zero prints all actions. -1 turns autovacuum logging off.	sighup	integer	default	-1	2147483647	\N	-1	-1	\N	\N	f
+log_checkpoints	off	\N	Reporting and Logging / What to Log	Logs each checkpoint.	\N	sighup	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_connections	off	\N	Reporting and Logging / What to Log	Logs each successful connection.	\N	superuser-backend	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_destination	stderr	\N	Reporting and Logging / Where to Log	Sets the destination for server log output.	Valid values are combinations of "stderr", "syslog", "csvlog", and "eventlog", depending on the platform.	sighup	string	configuration file	\N	\N	\N	stderr	stderr	\N	\N	f
+log_disconnections	off	\N	Reporting and Logging / What to Log	Logs end of a session, including duration.	\N	superuser-backend	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_duration	off	\N	Reporting and Logging / What to Log	Logs the duration of each completed SQL statement.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_error_verbosity	default	\N	Reporting and Logging / What to Log	Sets the verbosity of logged messages.	\N	superuser	enum	default	\N	\N	{terse,default,verbose}	default	default	\N	\N	f
+log_executor_stats	off	\N	Statistics / Monitoring	Writes executor performance statistics to the server log.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_file_mode	0600	\N	Reporting and Logging / Where to Log	Sets the file permissions for log files.	The parameter value is expected to be a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).)	sighup	integer	default	0	511	\N	384	384	\N	\N	f
+log_hostname	off	\N	Reporting and Logging / What to Log	Logs the host name in the connection logs.	By default, connection logs only show the IP address of the connecting host. If you want them to show the host name you can turn this on, but depending on your host name resolution setup it might impose a non-negligible performance penalty.	sighup	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_line_prefix	%m [%p] 	\N	Reporting and Logging / What to Log	Controls information prefixed to each log line.	If blank, no prefix is used.	sighup	string	default	\N	\N	\N	%m [%p] 	%m [%p] 	\N	\N	f
+log_lock_waits	off	\N	Reporting and Logging / What to Log	Logs long lock waits.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_min_duration_sample	-1	ms	Reporting and Logging / When to Log	Sets the minimum execution time above which a sample of statements will be logged. Sampling is determined by log_statement_sample_rate.	Zero logs a sample of all queries. -1 turns this feature off.	superuser	integer	default	-1	2147483647	\N	-1	-1	\N	\N	f
+log_min_duration_statement	-1	ms	Reporting and Logging / When to Log	Sets the minimum execution time above which all statements will be logged.	Zero prints all queries. -1 turns this feature off.	superuser	integer	default	-1	2147483647	\N	-1	-1	\N	\N	f
+log_min_error_statement	error	\N	Reporting and Logging / When to Log	Causes all statements generating error at or above this level to be logged.	Each level includes all the levels that follow it. The later the level, the fewer messages are sent.	superuser	enum	default	\N	\N	{debug5,debug4,debug3,debug2,debug1,info,notice,warning,error,log,fatal,panic}	error	error	\N	\N	f
+log_min_messages	warning	\N	Reporting and Logging / When to Log	Sets the message levels that are logged.	Each level includes all the levels that follow it. The later the level, the fewer messages are sent.	superuser	enum	default	\N	\N	{debug5,debug4,debug3,debug2,debug1,info,notice,warning,error,log,fatal,panic}	warning	warning	\N	\N	f
+log_parameter_max_length	-1	B	Reporting and Logging / What to Log	When logging statements, limit logged parameter values to first N bytes.	-1 to print values in full.	superuser	integer	default	-1	1073741823	\N	-1	-1	\N	\N	f
+log_parameter_max_length_on_error	0	B	Reporting and Logging / What to Log	When reporting an error, limit logged parameter values to first N bytes.	-1 to print values in full.	user	integer	default	-1	1073741823	\N	0	0	\N	\N	f
+log_parser_stats	off	\N	Statistics / Monitoring	Writes parser performance statistics to the server log.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_planner_stats	off	\N	Statistics / Monitoring	Writes planner performance statistics to the server log.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_replication_commands	off	\N	Reporting and Logging / What to Log	Logs each replication command.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_rotation_age	1440	min	Reporting and Logging / Where to Log	Automatic log file rotation will occur after N minutes.	\N	sighup	integer	default	0	35791394	\N	1440	1440	\N	\N	f
+log_rotation_size	10240	kB	Reporting and Logging / Where to Log	Automatic log file rotation will occur after N kilobytes.	\N	sighup	integer	default	0	2097151	\N	10240	10240	\N	\N	f
+log_statement	none	\N	Reporting and Logging / What to Log	Sets the type of statements logged.	\N	superuser	enum	default	\N	\N	{none,ddl,mod,all}	none	none	\N	\N	f
+log_statement_sample_rate	1	\N	Reporting and Logging / When to Log	Fraction of statements exceeding log_min_duration_sample to be logged.	Use a value between 0.0 (never log) and 1.0 (always log).	superuser	real	default	0	1	\N	1	1	\N	\N	f
+log_statement_stats	off	\N	Statistics / Monitoring	Writes cumulative performance statistics to the server log.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+log_temp_files	-1	kB	Reporting and Logging / What to Log	Log the use of temporary files larger than this number of kilobytes.	Zero logs all files. The default is -1 (turning this feature off).	superuser	integer	default	-1	2147483647	\N	-1	-1	\N	\N	f
+log_timezone	Europe/Berlin	\N	Reporting and Logging / What to Log	Sets the time zone to use in log messages.	\N	sighup	string	configuration file	\N	\N	\N	GMT	Europe/Berlin	\N	\N	f
+log_transaction_sample_rate	0	\N	Reporting and Logging / When to Log	Set the fraction of transactions to log for new transactions.	Logs all statements from a fraction of transactions. Use a value between 0.0 (never log) and 1.0 (log all statements for all transactions).	superuser	real	default	0	1	\N	0	0	\N	\N	f
+log_truncate_on_rotation	off	\N	Reporting and Logging / Where to Log	Truncate existing log files of same name during log rotation.	\N	sighup	bool	default	\N	\N	\N	off	off	\N	\N	f
+logging_collector	on	\N	Reporting and Logging / Where to Log	Start a subprocess to capture stderr output and/or csvlogs into log files.	\N	postmaster	bool	configuration file	\N	\N	\N	off	on	\N	\N	f
+logical_decoding_work_mem	65536	kB	Resource Usage / Memory	Sets the maximum memory to be used for logical decoding.	This much memory can be used by each internal reorder buffer before spilling to disk.	user	integer	default	64	2147483647	\N	65536	65536	\N	\N	f
+maintenance_io_concurrency	0	\N	Resource Usage / Asynchronous Behavior	A variant of effective_io_concurrency that is used for maintenance work.	\N	user	integer	default	0	1000	\N	0	0	\N	\N	f
+maintenance_work_mem	65536	kB	Resource Usage / Memory	Sets the maximum memory to be used for maintenance operations.	This includes operations such as VACUUM and CREATE INDEX.	user	integer	default	1024	2147483647	\N	65536	65536	\N	\N	f
+max_connections	100	\N	Connections and Authentication / Connection Settings	Sets the maximum number of concurrent connections.	\N	postmaster	integer	configuration file	1	262143	\N	100	100	\N	\N	f
+max_files_per_process	1000	\N	Resource Usage / Kernel Resources	Sets the maximum number of simultaneously open files for each server process.	\N	postmaster	integer	default	64	2147483647	\N	1000	1000	\N	\N	f
+max_function_args	100	\N	Preset Options	Shows the maximum number of function arguments.	\N	internal	integer	default	100	100	\N	100	100	\N	\N	f
+max_identifier_length	63	\N	Preset Options	Shows the maximum identifier length.	\N	internal	integer	default	63	63	\N	63	63	\N	\N	f
+max_index_keys	32	\N	Preset Options	Shows the maximum number of index keys.	\N	internal	integer	default	32	32	\N	32	32	\N	\N	f
+max_locks_per_transaction	64	\N	Lock Management	Sets the maximum number of locks per transaction.	The shared lock table is sized on the assumption that at most max_locks_per_transaction * max_connections distinct objects will need to be locked at any one time.	postmaster	integer	default	10	2147483647	\N	64	64	\N	\N	f
+max_logical_replication_workers	4	\N	Replication / Subscribers	Maximum number of logical replication worker processes.	\N	postmaster	integer	default	0	262143	\N	4	4	\N	\N	f
+max_parallel_maintenance_workers	2	\N	Resource Usage / Asynchronous Behavior	Sets the maximum number of parallel processes per maintenance operation.	\N	user	integer	default	0	1024	\N	2	2	\N	\N	f
+max_parallel_workers	8	\N	Resource Usage / Asynchronous Behavior	Sets the maximum number of parallel workers that can be active at one time.	\N	user	integer	default	0	1024	\N	8	8	\N	\N	f
+max_parallel_workers_per_gather	2	\N	Resource Usage / Asynchronous Behavior	Sets the maximum number of parallel processes per executor node.	\N	user	integer	default	0	1024	\N	2	2	\N	\N	f
+max_pred_locks_per_page	2	\N	Lock Management	Sets the maximum number of predicate-locked tuples per page.	If more than this number of tuples on the same page are locked by a connection, those locks are replaced by a page-level lock.	sighup	integer	default	0	2147483647	\N	2	2	\N	\N	f
+max_pred_locks_per_relation	-2	\N	Lock Management	Sets the maximum number of predicate-locked pages and tuples per relation.	If more than this total of pages and tuples in the same relation are locked by a connection, those locks are replaced by a relation-level lock.	sighup	integer	default	-2147483648	2147483647	\N	-2	-2	\N	\N	f
+max_pred_locks_per_transaction	64	\N	Lock Management	Sets the maximum number of predicate locks per transaction.	The shared predicate lock table is sized on the assumption that at most max_pred_locks_per_transaction * max_connections distinct objects will need to be locked at any one time.	postmaster	integer	default	10	2147483647	\N	64	64	\N	\N	f
+max_prepared_transactions	0	\N	Resource Usage / Memory	Sets the maximum number of simultaneously prepared transactions.	\N	postmaster	integer	default	0	262143	\N	0	0	\N	\N	f
+max_replication_slots	10	\N	Replication / Sending Servers	Sets the maximum number of simultaneously defined replication slots.	\N	postmaster	integer	default	0	262143	\N	10	10	\N	\N	f
+max_slot_wal_keep_size	-1	MB	Replication / Sending Servers	Sets the maximum WAL size that can be reserved by replication slots.	Replication slots will be marked as failed, and segments released for deletion or recycling, if this much space is occupied by WAL on disk.	sighup	integer	default	-1	2147483647	\N	-1	-1	\N	\N	f
+max_stack_depth	2048	kB	Resource Usage / Memory	Sets the maximum stack depth, in kilobytes.	\N	superuser	integer	environment variable	100	2147483647	\N	100	2048	\N	\N	f
+max_standby_archive_delay	30000	ms	Replication / Standby Servers	Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.	\N	sighup	integer	default	-1	2147483647	\N	30000	30000	\N	\N	f
+max_standby_streaming_delay	30000	ms	Replication / Standby Servers	Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data.	\N	sighup	integer	default	-1	2147483647	\N	30000	30000	\N	\N	f
+max_sync_workers_per_subscription	2	\N	Replication / Subscribers	Maximum number of table synchronization workers per subscription.	\N	sighup	integer	default	0	262143	\N	2	2	\N	\N	f
+max_wal_senders	10	\N	Replication / Sending Servers	Sets the maximum number of simultaneously running WAL sender processes.	\N	postmaster	integer	default	0	262143	\N	10	10	\N	\N	f
+max_wal_size	1024	MB	Write-Ahead Log / Checkpoints	Sets the WAL size that triggers a checkpoint.	\N	sighup	integer	configuration file	2	2147483647	\N	1024	1024	\N	\N	f
+max_worker_processes	8	\N	Resource Usage / Asynchronous Behavior	Maximum number of concurrent worker processes.	\N	postmaster	integer	default	0	262143	\N	8	8	\N	\N	f
+min_parallel_index_scan_size	64	8kB	Query Tuning / Planner Cost Constants	Sets the minimum amount of index data for a parallel scan.	If the planner estimates that it will read a number of index pages too small to reach this limit, a parallel scan will not be considered.	user	integer	default	0	715827882	\N	64	64	\N	\N	f
+min_parallel_table_scan_size	1024	8kB	Query Tuning / Planner Cost Constants	Sets the minimum amount of table data for a parallel scan.	If the planner estimates that it will read a number of table pages too small to reach this limit, a parallel scan will not be considered.	user	integer	default	0	715827882	\N	1024	1024	\N	\N	f
+min_wal_size	80	MB	Write-Ahead Log / Checkpoints	Sets the minimum size to shrink the WAL to.	\N	sighup	integer	configuration file	2	2147483647	\N	80	80	\N	\N	f
+old_snapshot_threshold	-1	min	Resource Usage / Asynchronous Behavior	Time before a snapshot is too old to read pages changed after the snapshot was taken.	A value of -1 disables this feature.	postmaster	integer	default	-1	86400	\N	-1	-1	\N	\N	f
+operator_precedence_warning	off	\N	Version and Platform Compatibility / Previous PostgreSQL Versions	Emit a warning for constructs that changed meaning since PostgreSQL 9.4.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+parallel_leader_participation	on	\N	Resource Usage / Asynchronous Behavior	Controls whether Gather and Gather Merge also run subplans.	Should gather nodes also run subplans, or just gather tuples?	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+parallel_setup_cost	1000	\N	Query Tuning / Planner Cost Constants	Sets the planner's estimate of the cost of starting up worker processes for parallel query.	\N	user	real	default	0	1.79769e+308	\N	1000	1000	\N	\N	f
+parallel_tuple_cost	0.1	\N	Query Tuning / Planner Cost Constants	Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend.	\N	user	real	default	0	1.79769e+308	\N	0.1	0.1	\N	\N	f
+password_encryption	scram-sha-256	\N	Connections and Authentication / Authentication	Chooses the algorithm for encrypting passwords.	\N	user	enum	configuration file	\N	\N	{md5,scram-sha-256}	md5	scram-sha-256	\N	\N	f
+plan_cache_mode	auto	\N	Query Tuning / Other Planner Options	Controls the planner's selection of custom or generic plan.	Prepared statements can have custom and generic plans, and the planner will attempt to choose which is better.  This can be set to override the default behavior.	user	enum	default	\N	\N	{auto,force_generic_plan,force_custom_plan}	auto	auto	\N	\N	f
+port	5432	\N	Connections and Authentication / Connection Settings	Sets the TCP port the server listens on.	\N	postmaster	integer	configuration file	1	65535	\N	5432	5432	\N	\N	f
+post_auth_delay	0	s	Developer Options	Waits N seconds on connection startup after authentication.	This allows attaching a debugger to the process.	backend	integer	default	0	2147	\N	0	0	\N	\N	f
+pre_auth_delay	0	s	Developer Options	Waits N seconds on connection startup before authentication.	This allows attaching a debugger to the process.	sighup	integer	default	0	60	\N	0	0	\N	\N	f
+primary_slot_name		\N	Replication / Standby Servers	Sets the name of the replication slot to use on the sending server.	\N	sighup	string	default	\N	\N	\N			\N	\N	f
+promote_trigger_file		\N	Replication / Standby Servers	Specifies a file name whose presence ends recovery in the standby.	\N	sighup	string	default	\N	\N	\N			\N	\N	f
+quote_all_identifiers	off	\N	Version and Platform Compatibility / Previous PostgreSQL Versions	When generating SQL fragments, quote all identifiers.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+random_page_cost	4	\N	Query Tuning / Planner Cost Constants	Sets the planner's estimate of the cost of a nonsequentially fetched disk page.	\N	user	real	default	0	1.79769e+308	\N	4	4	\N	\N	f
+recovery_end_command		\N	Write-Ahead Log / Archive Recovery	Sets the shell command that will be executed once at the end of recovery.	\N	sighup	string	default	\N	\N	\N			\N	\N	f
+recovery_min_apply_delay	0	ms	Replication / Standby Servers	Sets the minimum delay for applying changes during recovery.	\N	sighup	integer	default	0	2147483647	\N	0	0	\N	\N	f
+recovery_target		\N	Write-Ahead Log / Recovery Target	Set to "immediate" to end recovery as soon as a consistent state is reached.	\N	postmaster	string	default	\N	\N	\N			\N	\N	f
+recovery_target_action	pause	\N	Write-Ahead Log / Recovery Target	Sets the action to perform upon reaching the recovery target.	\N	postmaster	enum	default	\N	\N	{pause,promote,shutdown}	pause	pause	\N	\N	f
+recovery_target_inclusive	on	\N	Write-Ahead Log / Recovery Target	Sets whether to include or exclude transaction with recovery target.	\N	postmaster	bool	default	\N	\N	\N	on	on	\N	\N	f
+recovery_target_lsn		\N	Write-Ahead Log / Recovery Target	Sets the LSN of the write-ahead log location up to which recovery will proceed.	\N	postmaster	string	default	\N	\N	\N			\N	\N	f
+recovery_target_name		\N	Write-Ahead Log / Recovery Target	Sets the named restore point up to which recovery will proceed.	\N	postmaster	string	default	\N	\N	\N			\N	\N	f
+recovery_target_time		\N	Write-Ahead Log / Recovery Target	Sets the time stamp up to which recovery will proceed.	\N	postmaster	string	default	\N	\N	\N			\N	\N	f
+recovery_target_timeline	latest	\N	Write-Ahead Log / Recovery Target	Specifies the timeline to recover into.	\N	postmaster	string	default	\N	\N	\N	latest	latest	\N	\N	f
+recovery_target_xid		\N	Write-Ahead Log / Recovery Target	Sets the transaction ID up to which recovery will proceed.	\N	postmaster	string	default	\N	\N	\N			\N	\N	f
+restart_after_crash	on	\N	Error Handling	Reinitialize server after backend crash.	\N	sighup	bool	default	\N	\N	\N	on	on	\N	\N	f
+restore_command		\N	Write-Ahead Log / Archive Recovery	Sets the shell command that will be called to retrieve an archived WAL file.	\N	postmaster	string	default	\N	\N	\N			\N	\N	f
+row_security	on	\N	Client Connection Defaults / Statement Behavior	Enable row security.	When enabled, row security will be applied to all users.	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+search_path	public	\N	Client Connection Defaults / Statement Behavior	Sets the schema search order for names that are not schema-qualified.	\N	user	string	default	\N	\N	\N	public	public	\N	\N	f
+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	Client Connection Defaults / Locale and Formatting	Sets the server (database) character set encoding.	\N	internal	string	override	\N	\N	\N	SQL_ASCII	UTF8	\N	\N	f
+server_version	13.4	\N	Preset Options	Shows the server version.	\N	internal	string	default	\N	\N	\N	13.4	13.4	\N	\N	f
+server_version_num	130004	\N	Preset Options	Shows the server version as an integer.	\N	internal	integer	default	130004	130004	\N	130004	130004	\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
+shared_buffers	16384	8kB	Resource Usage / Memory	Sets the number of shared memory buffers used by the server.	\N	postmaster	integer	configuration file	16	1073741823	\N	1024	16384	\N	\N	f
+shared_memory_type	mmap	\N	Resource Usage / Memory	Selects the shared memory implementation used for the main shared memory region.	\N	postmaster	enum	default	\N	\N	{sysv,mmap}	mmap	mmap	\N	\N	f
+ssl	off	\N	Connections and Authentication / SSL	Enables SSL connections.	\N	sighup	bool	default	\N	\N	\N	off	off	\N	\N	f
+ssl_ca_file		\N	Connections and Authentication / SSL	Location of the SSL certificate authority file.	\N	sighup	string	default	\N	\N	\N			\N	\N	f
+ssl_cert_file	server.crt	\N	Connections and Authentication / SSL	Location of the SSL server certificate file.	\N	sighup	string	default	\N	\N	\N	server.crt	server.crt	\N	\N	f
+ssl_crl_file		\N	Connections and Authentication / SSL	Location of the SSL certificate revocation list file.	\N	sighup	string	default	\N	\N	\N			\N	\N	f
+ssl_key_file	server.key	\N	Connections and Authentication / SSL	Location of the SSL server private key file.	\N	sighup	string	default	\N	\N	\N	server.key	server.key	\N	\N	f
+ssl_library	OpenSSL	\N	Preset Options	Name of the SSL library.	\N	internal	string	default	\N	\N	\N	OpenSSL	OpenSSL	\N	\N	f
+ssl_passphrase_command_supports_reload	off	\N	Connections and Authentication / SSL	Also use ssl_passphrase_command during server reload.	\N	sighup	bool	default	\N	\N	\N	off	off	\N	\N	f
+ssl_prefer_server_ciphers	on	\N	Connections and Authentication / SSL	Give priority to server ciphersuite order.	\N	sighup	bool	default	\N	\N	\N	on	on	\N	\N	f
+standard_conforming_strings	on	\N	Version and Platform Compatibility / Previous PostgreSQL Versions	Causes '...' strings to treat backslashes literally.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+statement_timeout	0	ms	Client Connection Defaults / Statement Behavior	Sets the maximum allowed duration of any statement.	A value of 0 turns off the timeout.	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+superuser_reserved_connections	3	\N	Connections and Authentication / Connection Settings	Sets the number of connection slots reserved for superusers.	\N	postmaster	integer	default	0	262143	\N	3	3	\N	\N	f
+synchronize_seqscans	on	\N	Version and Platform Compatibility / Previous PostgreSQL Versions	Enable synchronized sequential scans.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
+synchronous_commit	on	\N	Write-Ahead Log / Settings	Sets the current transaction's synchronization level.	\N	user	enum	default	\N	\N	{local,remote_write,remote_apply,on,off}	on	on	\N	\N	f
+synchronous_standby_names		\N	Replication / Master Server	Number of synchronous standbys and list of names of potential synchronous ones.	\N	sighup	string	default	\N	\N	\N			\N	\N	f
+syslog_facility	local0	\N	Reporting and Logging / Where to Log	Sets the syslog "facility" to be used when syslog enabled.	\N	sighup	enum	default	\N	\N	{local0,local1,local2,local3,local4,local5,local6,local7}	local0	local0	\N	\N	f
+syslog_ident	postgres	\N	Reporting and Logging / Where to Log	Sets the program name used to identify PostgreSQL messages in syslog.	\N	sighup	string	default	\N	\N	\N	postgres	postgres	\N	\N	f
+syslog_sequence_numbers	on	\N	Reporting and Logging / Where to Log	Add sequence number to syslog messages to avoid duplicate suppression.	\N	sighup	bool	default	\N	\N	\N	on	on	\N	\N	f
+syslog_split_messages	on	\N	Reporting and Logging / Where to Log	Split messages sent to syslog by lines and to fit into 1024 bytes.	\N	sighup	bool	default	\N	\N	\N	on	on	\N	\N	f
+tcp_keepalives_count	0	\N	Client Connection Defaults / Other Defaults	Maximum number of TCP keepalive retransmits.	This controls the number of consecutive keepalive retransmits that can be lost before a connection is considered dead. A value of 0 uses the system default.	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+tcp_keepalives_idle	0	s	Client Connection Defaults / Other Defaults	Time between issuing TCP keepalives.	A value of 0 uses the system default.	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+tcp_keepalives_interval	0	s	Client Connection Defaults / Other Defaults	Time between TCP keepalive retransmits.	A value of 0 uses the system default.	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+tcp_user_timeout	0	ms	Client Connection Defaults / Other Defaults	TCP user timeout.	A value of 0 uses the system default.	user	integer	default	0	2147483647	\N	0	0	\N	\N	f
+temp_buffers	1024	8kB	Resource Usage / Memory	Sets the maximum number of temporary buffers used by each session.	\N	user	integer	default	100	1073741823	\N	1024	1024	\N	\N	f
+temp_file_limit	-1	kB	Resource Usage / Disk	Limits the total size of all temporary files used by each process.	-1 means no limit.	superuser	integer	default	-1	2147483647	\N	-1	-1	\N	\N	f
+temp_tablespaces		\N	Client Connection Defaults / Statement Behavior	Sets the tablespace(s) to use for temporary tables and sort files.	\N	user	string	default	\N	\N	\N			\N	\N	f
+TimeZone	Europe/Berlin	\N	Client Connection Defaults / Locale and Formatting	Sets the time zone for displaying and interpreting time stamps.	\N	user	string	configuration file	\N	\N	\N	GMT	Europe/Berlin	\N	\N	f
+timezone_abbreviations	Default	\N	Client Connection Defaults / Locale and Formatting	Selects a file of time zone abbreviations.	\N	user	string	default	\N	\N	\N	\N	Default	\N	\N	f
+trace_notify	off	\N	Developer Options	Generates debugging output for LISTEN and NOTIFY.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+trace_recovery_messages	log	\N	Developer Options	Enables logging of recovery-related debugging information.	Each level includes all the levels that follow it. The later the level, the fewer messages are sent.	sighup	enum	default	\N	\N	{debug5,debug4,debug3,debug2,debug1,log,notice,warning,error}	log	log	\N	\N	f
+trace_sort	off	\N	Developer Options	Emit information about resource usage in sorting.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+track_activities	on	\N	Statistics / Query and Index Statistics Collector	Collects information about executing commands.	Enables the collection of information on the currently executing command of each session, along with the time at which that command began execution.	superuser	bool	default	\N	\N	\N	on	on	\N	\N	f
+track_activity_query_size	1024	B	Resource Usage / Memory	Sets the size reserved for pg_stat_activity.query, in bytes.	\N	postmaster	integer	default	100	1048576	\N	1024	1024	\N	\N	f
+track_commit_timestamp	off	\N	Replication	Collects transaction commit time.	\N	postmaster	bool	default	\N	\N	\N	off	off	\N	\N	f
+track_counts	on	\N	Statistics / Query and Index Statistics Collector	Collects statistics on database activity.	\N	superuser	bool	default	\N	\N	\N	on	on	\N	\N	f
+track_functions	none	\N	Statistics / Query and Index Statistics Collector	Collects function-level statistics on database activity.	\N	superuser	enum	default	\N	\N	{none,pl,all}	none	none	\N	\N	f
+track_io_timing	off	\N	Statistics / Query and Index Statistics Collector	Collects timing statistics for database I/O activity.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+transaction_deferrable	off	\N	Client Connection Defaults / Statement Behavior	Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures.	\N	user	bool	override	\N	\N	\N	off	off	\N	\N	f
+transaction_isolation	read committed	\N	Client Connection Defaults / Statement Behavior	Sets the current transaction's isolation level.	\N	user	enum	override	\N	\N	{serializable,"repeatable read","read committed","read uncommitted"}	read committed	read committed	\N	\N	f
+transaction_read_only	off	\N	Client Connection Defaults / Statement Behavior	Sets the current transaction's read-only status.	\N	user	bool	override	\N	\N	\N	off	off	\N	\N	f
+transform_null_equals	off	\N	Version and Platform Compatibility / Other Platforms and Clients	Treats "expr=NULL" as "expr IS NULL".	When turned on, expressions of the form expr = NULL (or NULL = expr) are treated as expr IS NULL, that is, they return true if expr evaluates to the null value, and false otherwise. The correct behavior of expr = NULL is to always return null (unknown).	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+unix_socket_group		\N	Connections and Authentication / Connection Settings	Sets the owning group of the Unix-domain socket.	The owning user of the socket is always the user that starts the server.	postmaster	string	default	\N	\N	\N			\N	\N	f
+unix_socket_permissions	0777	\N	Connections and Authentication / Connection Settings	Sets the access permissions of the Unix-domain socket.	Unix-domain sockets use the usual Unix file system permission set. The parameter value is expected to be a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).)	postmaster	integer	default	0	511	\N	511	511	\N	\N	f
+update_process_title	on	\N	Process Title	Updates the process title to show the active SQL command.	Enables updating of the process title every time a new SQL command is received by the server.	superuser	bool	default	\N	\N	\N	on	on	\N	\N	f
+vacuum_cleanup_index_scale_factor	0.1	\N	Client Connection Defaults / Statement Behavior	Number of tuple inserts prior to index cleanup as a fraction of reltuples.	\N	user	real	default	0	1e+10	\N	0.1	0.1	\N	\N	f
+vacuum_cost_delay	0	ms	Resource Usage / Cost-Based Vacuum Delay	Vacuum cost delay in milliseconds.	\N	user	real	default	0	100	\N	0	0	\N	\N	f
+vacuum_cost_limit	200	\N	Resource Usage / Cost-Based Vacuum Delay	Vacuum cost amount available before napping.	\N	user	integer	default	1	10000	\N	200	200	\N	\N	f
+vacuum_cost_page_dirty	20	\N	Resource Usage / Cost-Based Vacuum Delay	Vacuum cost for a page dirtied by vacuum.	\N	user	integer	default	0	10000	\N	20	20	\N	\N	f
+vacuum_cost_page_hit	1	\N	Resource Usage / Cost-Based Vacuum Delay	Vacuum cost for a page found in the buffer cache.	\N	user	integer	default	0	10000	\N	1	1	\N	\N	f
+vacuum_cost_page_miss	10	\N	Resource Usage / Cost-Based Vacuum Delay	Vacuum cost for a page not found in the buffer cache.	\N	user	integer	default	0	10000	\N	10	10	\N	\N	f
+vacuum_defer_cleanup_age	0	\N	Replication / Master Server	Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.	\N	sighup	integer	default	0	1000000	\N	0	0	\N	\N	f
+vacuum_freeze_min_age	50000000	\N	Client Connection Defaults / Statement Behavior	Minimum age at which VACUUM should freeze a table row.	\N	user	integer	default	0	1000000000	\N	50000000	50000000	\N	\N	f
+vacuum_freeze_table_age	150000000	\N	Client Connection Defaults / Statement Behavior	Age at which VACUUM should scan whole table to freeze tuples.	\N	user	integer	default	0	2000000000	\N	150000000	150000000	\N	\N	f
+vacuum_multixact_freeze_min_age	5000000	\N	Client Connection Defaults / Statement Behavior	Minimum age at which VACUUM should freeze a MultiXactId in a table row.	\N	user	integer	default	0	1000000000	\N	5000000	5000000	\N	\N	f
+vacuum_multixact_freeze_table_age	150000000	\N	Client Connection Defaults / Statement Behavior	Multixact age at which VACUUM should scan whole table to freeze tuples.	\N	user	integer	default	0	2000000000	\N	150000000	150000000	\N	\N	f
+wal_block_size	8192	\N	Preset Options	Shows the block size in the write ahead log.	\N	internal	integer	default	8192	8192	\N	8192	8192	\N	\N	f
+wal_buffers	512	8kB	Write-Ahead Log / Settings	Sets the number of disk-page buffers in shared memory for WAL.	\N	postmaster	integer	override	-1	262143	\N	-1	512	\N	\N	f
+wal_compression	off	\N	Write-Ahead Log / Settings	Compresses full-page writes written in WAL file.	\N	superuser	bool	default	\N	\N	\N	off	off	\N	\N	f
+wal_consistency_checking		\N	Developer Options	Sets the WAL resource managers for which WAL consistency checks are done.	Full-page images will be logged for all data blocks and cross-checked against the results of WAL replay.	superuser	string	default	\N	\N	\N			\N	\N	f
+wal_init_zero	on	\N	Write-Ahead Log / Settings	Writes zeroes to new WAL files before first use.	\N	superuser	bool	default	\N	\N	\N	on	on	\N	\N	f
+wal_keep_size	0	MB	Replication / Sending Servers	Sets the size of WAL files held for standby servers.	\N	sighup	integer	default	0	2147483647	\N	0	0	\N	\N	f
+wal_level	replica	\N	Write-Ahead Log / Settings	Set the level of information written to the WAL.	\N	postmaster	enum	default	\N	\N	{minimal,replica,logical}	replica	replica	\N	\N	f
+wal_log_hints	off	\N	Write-Ahead Log / Settings	Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modification.	\N	postmaster	bool	default	\N	\N	\N	off	off	\N	\N	f
+wal_receiver_create_temp_slot	off	\N	Replication / Standby Servers	Sets whether a WAL receiver should create a temporary replication slot if no permanent slot is configured.	\N	sighup	bool	default	\N	\N	\N	off	off	\N	\N	f
+wal_receiver_status_interval	10	s	Replication / Standby Servers	Sets the maximum interval between WAL receiver status reports to the sending server.	\N	sighup	integer	default	0	2147483	\N	10	10	\N	\N	f
+wal_receiver_timeout	60000	ms	Replication / Standby Servers	Sets the maximum wait time to receive data from the sending server.	\N	sighup	integer	default	0	2147483647	\N	60000	60000	\N	\N	f
+wal_recycle	on	\N	Write-Ahead Log / Settings	Recycles WAL files by renaming them.	\N	superuser	bool	default	\N	\N	\N	on	on	\N	\N	f
+wal_retrieve_retry_interval	5000	ms	Replication / Standby Servers	Sets the time to wait before retrying to retrieve WAL after a failed attempt.	\N	sighup	integer	default	1	2147483647	\N	5000	5000	\N	\N	f
+wal_segment_size	16777216	B	Preset Options	Shows the size of write ahead log segments.	\N	internal	integer	override	1048576	1073741824	\N	16777216	16777216	\N	\N	f
+wal_sender_timeout	60000	ms	Replication / Sending Servers	Sets the maximum time to wait for WAL replication.	\N	user	integer	default	0	2147483647	\N	60000	60000	\N	\N	f
+wal_skip_threshold	2048	kB	Write-Ahead Log / Settings	Size of new file to fsync instead of writing WAL.	\N	user	integer	default	0	2147483647	\N	2048	2048	\N	\N	f
+wal_sync_method	open_datasync	\N	Write-Ahead Log / Settings	Selects the method used for forcing WAL updates to disk.	\N	sighup	enum	default	\N	\N	{fsync,fsync_writethrough,fdatasync,open_sync,open_datasync}	open_datasync	open_datasync	\N	\N	f
+wal_writer_delay	200	ms	Write-Ahead Log / Settings	Time between WAL flushes performed in the WAL writer.	\N	sighup	integer	default	1	10000	\N	200	200	\N	\N	f
+wal_writer_flush_after	128	8kB	Write-Ahead Log / Settings	Amount of WAL written out by WAL writer that triggers a flush.	\N	sighup	integer	default	0	2147483647	\N	128	128	\N	\N	f
+work_mem	4096	kB	Resource Usage / Memory	Sets the maximum memory to be used for query workspaces.	This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.	user	integer	default	64	2147483647	\N	4096	4096	\N	\N	f
+xmlbinary	base64	\N	Client Connection Defaults / Statement Behavior	Sets how binary values are to be encoded in XML.	\N	user	enum	default	\N	\N	{base64,hex}	base64	base64	\N	\N	f
+xmloption	content	\N	Client Connection Defaults / Statement Behavior	Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.	\N	user	enum	default	\N	\N	{content,document}	content	content	\N	\N	f
+zero_damaged_pages	off	\N	Developer Options	Continues processing past damaged page headers.	Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting zero_damaged_pages to true causes the system to instead report a warning, zero out the damaged page, and continue processing. This behavior will destroy data, namely all the rows on the damaged page.	superuser	bool	default	\N	\N	\N	off	off	\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 b690b946..5457f3af 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
@@ -14,6 +14,7 @@
 
 package com.google.cloud.spanner.pgadapter;
 
+import static com.google.cloud.spanner.pgadapter.statements.BackendConnection.TRANSACTION_ABORTED_ERROR;
 import static org.junit.Assert.assertArrayEquals;
 import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertFalse;
@@ -1638,4 +1639,412 @@ public class JdbcMockServerTest extends AbstractMockServerTest {
     assertEquals(3, mockSpanner.countRequestsOfType(ExecuteSqlRequest.class));
     assertEquals(0, mockSpanner.countRequestsOfType(CommitRequest.class));
   }
+
+  @Test
+  public void testShowValidSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      try (ResultSet resultSet =
+          connection.createStatement().executeQuery("show application_name ")) {
+        assertTrue(resultSet.next());
+        assertNull(resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
+  @Test
+  public void testShowInvalidSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      SQLException exception =
+          assertThrows(
+              SQLException.class,
+              () -> connection.createStatement().executeQuery("show random_setting"));
+      assertEquals(
+          "ERROR: INVALID_ARGUMENT: unrecognized configuration parameter \"random_setting\"",
+          exception.getMessage());
+    }
+  }
+
+  @Test
+  public void testSetValidSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.createStatement().execute("set application_name to 'my-application'");
+
+      try (ResultSet resultSet =
+          connection.createStatement().executeQuery("show application_name ")) {
+        assertTrue(resultSet.next());
+        assertEquals("my-application", resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
+  @Test
+  public void testSetCaseInsensitiveSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      // The setting is called 'DateStyle' in the pg_settings table.
+      connection.createStatement().execute("set datestyle to 'iso'");
+
+      try (ResultSet resultSet = connection.createStatement().executeQuery("show DATESTYLE")) {
+        assertTrue(resultSet.next());
+        assertEquals("iso", resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
+  @Test
+  public void testSetInvalidSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      SQLException exception =
+          assertThrows(
+              SQLException.class,
+              () ->
+                  connection.createStatement().executeQuery("set random_setting to 'some-value'"));
+      assertEquals(
+          "ERROR: INVALID_ARGUMENT: unrecognized configuration parameter \"random_setting\"",
+          exception.getMessage());
+    }
+  }
+
+  @Test
+  public void testResetValidSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.createStatement().execute("set application_name to 'my-application'");
+
+      try (ResultSet resultSet =
+          connection.createStatement().executeQuery("show application_name ")) {
+        assertTrue(resultSet.next());
+        assertEquals("my-application", resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+
+      connection.createStatement().execute("reset application_name");
+
+      try (ResultSet resultSet =
+          connection.createStatement().executeQuery("show application_name ")) {
+        assertTrue(resultSet.next());
+        assertNull(resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
+  @Test
+  public void testResetInvalidSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      SQLException exception =
+          assertThrows(
+              SQLException.class,
+              () -> connection.createStatement().executeQuery("reset random_setting"));
+      assertEquals(
+          "ERROR: INVALID_ARGUMENT: unrecognized configuration parameter \"random_setting\"",
+          exception.getMessage());
+    }
+  }
+
+  @Test
+  public void testShowUndefinedExtensionSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      SQLException exception =
+          assertThrows(
+              SQLException.class,
+              () -> connection.createStatement().executeQuery("show spanner.some_setting"));
+      assertEquals(
+          "ERROR: INVALID_ARGUMENT: unrecognized configuration parameter \"spanner.some_setting\"",
+          exception.getMessage());
+    }
+  }
+
+  @Test
+  public void testSetExtensionSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.createStatement().execute("set spanner.some_setting to 'some-value'");
+
+      try (ResultSet resultSet =
+          connection.createStatement().executeQuery("show spanner.some_setting ")) {
+        assertTrue(resultSet.next());
+        assertEquals("some-value", resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
+  @Test
+  public void testResetValidExtensionSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.createStatement().execute("set spanner.some_setting to 'some-value'");
+
+      try (ResultSet resultSet =
+          connection.createStatement().executeQuery("show spanner.some_setting")) {
+        assertTrue(resultSet.next());
+        assertEquals("some-value", resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+
+      connection.createStatement().execute("reset spanner.some_setting");
+
+      try (ResultSet resultSet =
+          connection.createStatement().executeQuery("show spanner.some_setting")) {
+        assertTrue(resultSet.next());
+        assertNull(resultSet.getString(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
+  @Test
+  public void testResetUndefinedExtensionSetting() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      // Resetting an undefined extension setting is allowed by PostgreSQL, and will effectively set
+      // the extension setting to null.
+      connection.createStatement().execute("reset spanner.some_setting");
+
+      verifySettingIsNull(connection, "spanner.some_setting");
+    }
+  }
+
+  @Test
+  public void testCommitSet() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.setAutoCommit(false);
+
+      // Verify that the initial value is null.
+      verifySettingIsNull(connection, "application_name");
+      connection.createStatement().execute("set application_name to \"my-application\"");
+      verifySettingValue(connection, "application_name", "my-application");
+      // Committing the transaction should persist the value.
+      connection.commit();
+      verifySettingValue(connection, "application_name", "my-application");
+    }
+  }
+
+  @Test
+  public void testRollbackSet() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.setAutoCommit(false);
+
+      // Verify that the initial value is null.
+      verifySettingIsNull(connection, "application_name");
+      connection.createStatement().execute("set application_name to \"my-application\"");
+      verifySettingValue(connection, "application_name", "my-application");
+      // Rolling back the transaction should reset the value to what it was before the transaction.
+      connection.rollback();
+      verifySettingIsNull(connection, "application_name");
+    }
+  }
+
+  @Test
+  public void testCommitSetExtension() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.setAutoCommit(false);
+
+      connection.createStatement().execute("set spanner.random_setting to \"42\"");
+      verifySettingValue(connection, "spanner.random_setting", "42");
+      // Committing the transaction should persist the value.
+      connection.commit();
+      verifySettingValue(connection, "spanner.random_setting", "42");
+    }
+  }
+
+  @Test
+  public void testRollbackSetExtension() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.setAutoCommit(false);
+
+      connection.createStatement().execute("set spanner.random_setting to \"42\"");
+      verifySettingValue(connection, "spanner.random_setting", "42");
+      // Rolling back the transaction should reset the value to what it was before the transaction.
+      // In this case, that means that it should be undefined.
+      connection.rollback();
+      verifySettingIsUnrecognized(connection, "spanner.random_setting");
+    }
+  }
+
+  @Test
+  public void testRollbackDefinedExtension() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      // First define the extension setting.
+      connection.createStatement().execute("set spanner.random_setting to '100'");
+
+      connection.setAutoCommit(false);
+
+      connection.createStatement().execute("set spanner.random_setting to \"42\"");
+      verifySettingValue(connection, "spanner.random_setting", "42");
+      // Rolling back the transaction should reset the value to what it was before the transaction.
+      // In this case, that means back to '100'.
+      connection.rollback();
+      verifySettingValue(connection, "spanner.random_setting", "100");
+    }
+  }
+
+  @Test
+  public void testCommitSetLocal() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.setAutoCommit(false);
+
+      // Verify that the initial value is null.
+      verifySettingIsNull(connection, "application_name");
+      connection.createStatement().execute("set local application_name to \"my-application\"");
+      verifySettingValue(connection, "application_name", "my-application");
+      // Committing the transaction should not persist the value as it was only set for the current
+      // transaction.
+      connection.commit();
+      verifySettingIsNull(connection, "application_name");
+    }
+  }
+
+  @Test
+  public void testCommitSetLocalAndSession() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.setAutoCommit(false);
+
+      // Verify that the initial value is null.
+      verifySettingIsNull(connection, "application_name");
+      // Set both a session and a local value. The session value will be 'hidden' by the local
+      // value, but the session value will be committed.
+      connection
+          .createStatement()
+          .execute("set session application_name to \"my-session-application\"");
+      verifySettingValue(connection, "application_name", "my-session-application");
+      connection
+          .createStatement()
+          .execute("set local application_name to \"my-local-application\"");
+      verifySettingValue(connection, "application_name", "my-local-application");
+      // Committing the transaction should persist the session value.
+      connection.commit();
+      verifySettingValue(connection, "application_name", "my-session-application");
+    }
+  }
+
+  @Test
+  public void testCommitSetLocalAndSessionExtension() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      // Verify that the initial value is undefined.
+      verifySettingIsUnrecognized(connection, "spanner.custom_setting");
+
+      connection.setAutoCommit(false);
+
+      // Set both a session and a local value. The session value will be 'hidden' by the local
+      // value, but the session value will be committed.
+      connection.createStatement().execute("set spanner.custom_setting to 'session-value'");
+      verifySettingValue(connection, "spanner.custom_setting", "session-value");
+      connection.createStatement().execute("set local spanner.custom_setting to 'local-value'");
+      verifySettingValue(connection, "spanner.custom_setting", "local-value");
+      // Committing the transaction should persist the session value.
+      connection.commit();
+      verifySettingValue(connection, "spanner.custom_setting", "session-value");
+    }
+  }
+
+  @Test
+  public void testInvalidShowAbortsTransaction() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.setAutoCommit(false);
+
+      // Verify that executing an invalid show statement will abort the transaction.
+      assertThrows(
+          SQLException.class,
+          () -> connection.createStatement().execute("show spanner.non_existing_param"));
+      SQLException exception =
+          assertThrows(
+              SQLException.class,
+              () -> connection.createStatement().execute("show application_name "));
+      assertEquals("ERROR: INVALID_ARGUMENT: " + TRANSACTION_ABORTED_ERROR, exception.getMessage());
+
+      connection.rollback();
+
+      // Verify that the connection is usable again.
+      verifySettingIsNull(connection, "application_name");
+    }
+  }
+
+  @Test
+  public void testShowAll() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      try (ResultSet resultSet = connection.createStatement().executeQuery("show all")) {
+        assertEquals(3, resultSet.getMetaData().getColumnCount());
+        assertEquals("name", resultSet.getMetaData().getColumnName(1));
+        assertEquals("setting", resultSet.getMetaData().getColumnName(2));
+        assertEquals("description", resultSet.getMetaData().getColumnName(3));
+        int count = 0;
+        while (resultSet.next()) {
+          if ("client_encoding".equals(resultSet.getString("name"))) {
+            assertEquals("UTF8", resultSet.getString("setting"));
+          }
+          count++;
+        }
+        assertEquals(308, count);
+      }
+    }
+  }
+
+  @Test
+  public void testResetAll() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.createStatement().execute("set application_name to 'my-app'");
+      connection.createStatement().execute("set search_path to 'my_schema'");
+      verifySettingValue(connection, "application_name", "my-app");
+      verifySettingValue(connection, "search_path", "my_schema");
+
+      connection.createStatement().execute("reset all");
+
+      verifySettingIsNull(connection, "application_name");
+      verifySettingValue(connection, "search_path", "public");
+    }
+  }
+
+  @Test
+  public void testSetToDefault() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.createStatement().execute("set application_name to 'my-app'");
+      connection.createStatement().execute("set search_path to 'my_schema'");
+      verifySettingValue(connection, "application_name", "my-app");
+      verifySettingValue(connection, "search_path", "my_schema");
+
+      connection.createStatement().execute("set application_name to default");
+      connection.createStatement().execute("set search_path to default");
+
+      verifySettingIsNull(connection, "application_name");
+      verifySettingValue(connection, "search_path", "public");
+    }
+  }
+
+  @Test
+  public void testSetToEmpty() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.createStatement().execute("set application_name to ''");
+      verifySettingValue(connection, "application_name", "");
+    }
+  }
+
+  private void verifySettingIsNull(Connection connection, String setting) throws SQLException {
+    try (ResultSet resultSet =
+        connection.createStatement().executeQuery(String.format("show %s", setting))) {
+      assertTrue(resultSet.next());
+      assertNull(resultSet.getString(1));
+      assertFalse(resultSet.next());
+    }
+  }
+
+  private void verifySettingValue(Connection connection, String setting, String value)
+      throws SQLException {
+    try (ResultSet resultSet =
+        connection.createStatement().executeQuery(String.format("show %s", setting))) {
+      assertTrue(resultSet.next());
+      assertEquals(value, resultSet.getString(1));
+      assertFalse(resultSet.next());
+    }
+  }
+
+  private void verifySettingIsUnrecognized(Connection connection, String setting) {
+    SQLException exception =
+        assertThrows(
+            SQLException.class,
+            () -> connection.createStatement().execute(String.format("show %s", setting)));
+    assertEquals(
+        String.format(
+            "ERROR: INVALID_ARGUMENT: unrecognized configuration parameter \"%s\"", setting),
+        exception.getMessage());
+  }
 }
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
new file mode 100644
index 00000000..95ad3bc8
--- /dev/null
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
@@ -0,0 +1,389 @@
+// 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 org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertThrows;
+import static org.mockito.Mockito.mock;
+
+import com.google.cloud.spanner.SpannerException;
+import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
+import java.util.List;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.JUnit4;
+
+@RunWith(JUnit4.class)
+public class SessionStateTest {
+  @Test
+  public void testShowInitialSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    assertEquals("UTF8", state.get(null, "client_encoding").getSetting());
+  }
+
+  @Test
+  public void testShowLocalSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    assertEquals("UTF8", state.get(null, "client_encoding").getSetting());
+
+    state.setLocal(null, "client_encoding", "SQL_ASCII");
+    assertEquals("SQL_ASCII", state.get(null, "client_encoding").getSetting());
+
+    // Local settings are not persisted after a commit.
+    state.commit();
+    assertEquals("UTF8", state.get(null, "client_encoding").getSetting());
+
+    // Also verify that a rollback clears any local setting.
+    state.setLocal(null, "client_encoding", "SQL_ASCII");
+    assertEquals("SQL_ASCII", state.get(null, "client_encoding").getSetting());
+    state.rollback();
+    assertEquals("UTF8", state.get(null, "client_encoding").getSetting());
+  }
+
+  @Test
+  public void testCommitSessionSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    assertEquals("UTF8", state.get(null, "client_encoding").getSetting());
+
+    state.set(null, "client_encoding", "SQL_ASCII");
+    assertEquals("SQL_ASCII", state.get(null, "client_encoding").getSetting());
+
+    // Session settings are persisted after a commit.
+    state.commit();
+    assertEquals("SQL_ASCII", state.get(null, "client_encoding").getSetting());
+  }
+
+  @Test
+  public void testRollbackSessionSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    assertEquals("UTF8", state.get(null, "client_encoding").getSetting());
+
+    state.set(null, "client_encoding", "SQL_ASCII");
+    assertEquals("SQL_ASCII", state.get(null, "client_encoding").getSetting());
+
+    state.rollback();
+    assertEquals("UTF8", state.get(null, "client_encoding").getSetting());
+  }
+
+  @Test
+  public void testCommitSessionSettingHiddenBehindLocalSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    assertNull(state.get(null, "application_name").getSetting());
+
+    state.set(null, "application_name", "my-app");
+    assertEquals("my-app", state.get(null, "application_name").getSetting());
+
+    // This will set the application_name for this transaction only.
+    // The session value will (in the background) be 'my-app', which is what will be committed.
+    state.setLocal(null, "application_name", "local-app");
+    assertEquals("local-app", state.get(null, "application_name").getSetting());
+
+    state.commit();
+    assertEquals("my-app", state.get(null, "application_name").getSetting());
+  }
+
+  @Test
+  public void testRollbackSessionSettingHiddenBehindLocalSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    assertNull(state.get(null, "application_name").getSetting());
+
+    state.set(null, "application_name", "my-app");
+    assertEquals("my-app", state.get(null, "application_name").getSetting());
+
+    // This will set the application_name for this transaction only.
+    // The session value will (in the background) be 'my-app', which is what will be committed.
+    state.setLocal(null, "application_name", "local-app");
+    assertEquals("local-app", state.get(null, "application_name").getSetting());
+
+    state.rollback();
+    assertNull(state.get(null, "application_name").getSetting());
+  }
+
+  @Test
+  public void testOverwriteLocalSettingWithNewLocalSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    assertNull(state.get(null, "application_name").getSetting());
+
+    state.setLocal(null, "application_name", "local-app1");
+    assertEquals("local-app1", state.get(null, "application_name").getSetting());
+
+    state.setLocal(null, "application_name", "local-app2");
+    assertEquals("local-app2", state.get(null, "application_name").getSetting());
+
+    state.commit();
+    assertNull(state.get(null, "application_name").getSetting());
+  }
+
+  @Test
+  public void testOverwriteLocalSettingWithSessionSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    assertNull(state.get(null, "application_name").getSetting());
+
+    state.setLocal(null, "application_name", "local-app");
+    assertEquals("local-app", state.get(null, "application_name").getSetting());
+
+    state.set(null, "application_name", "my-app");
+    assertEquals("my-app", state.get(null, "application_name").getSetting());
+
+    state.commit();
+    assertEquals("my-app", state.get(null, "application_name").getSetting());
+  }
+
+  @Test
+  public void testGetAll() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    List<PGSetting> allSettings = state.getAll();
+    assertEquals(308, allSettings.size());
+  }
+
+  @Test
+  public void testResetAll() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+
+    state.set(null, "application_name", "my-app");
+    state.commit();
+    assertEquals("my-app", state.get(null, "application_name").getSetting());
+
+    state.resetAll();
+    assertNull(state.get(null, "application_name").getSetting());
+  }
+
+  @Test
+  public void testSetDefault() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+
+    state.set(null, "search_path", "my_schema");
+    state.commit();
+    assertEquals("my_schema", state.get(null, "search_path").getSetting());
+
+    state.set(null, "search_path", null);
+    assertEquals("public", state.get(null, "search_path").getSetting());
+  }
+
+  @Test
+  public void testGetAllWithLocalAndSessionChanges() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+
+    state.set(null, "application_name", "my-app");
+    state.setLocal(null, "client_encoding", "my-encoding");
+
+    state.set("spanner", "custom_session_setting", "value1");
+    state.setLocal("spanner", "custom_local_setting", "value2");
+
+    List<PGSetting> allSettings = state.getAll();
+    assertEquals(310, allSettings.size());
+
+    PGSetting applicationName =
+        allSettings.stream()
+            .filter(pgSetting -> pgSetting.getName().equals("application_name"))
+            .findAny()
+            .orElse(mock(PGSetting.class));
+    assertEquals("my-app", applicationName.getSetting());
+    PGSetting clientEncoding =
+        allSettings.stream()
+            .filter(pgSetting -> pgSetting.getName().equals("client_encoding"))
+            .findAny()
+            .orElse(mock(PGSetting.class));
+    assertEquals("my-encoding", clientEncoding.getSetting());
+    PGSetting customSessionSetting =
+        allSettings.stream()
+            .filter(pgSetting -> pgSetting.getName().equals("custom_session_setting"))
+            .findAny()
+            .orElse(mock(PGSetting.class));
+    assertEquals("value1", customSessionSetting.getSetting());
+    PGSetting customLocalSetting =
+        allSettings.stream()
+            .filter(pgSetting -> pgSetting.getName().equals("custom_local_setting"))
+            .findAny()
+            .orElse(mock(PGSetting.class));
+    assertEquals("value2", customLocalSetting.getSetting());
+  }
+
+  @Test
+  public void testShowUnknownSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(SpannerException.class, () -> state.get(null, "some_random_setting"));
+    assertEquals(
+        "INVALID_ARGUMENT: unrecognized configuration parameter \"some_random_setting\"",
+        exception.getMessage());
+  }
+
+  @Test
+  public void testShowUnknownExtensionSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(
+            SpannerException.class, () -> state.get("some_extension", "some_random_setting"));
+    assertEquals(
+        "INVALID_ARGUMENT: unrecognized configuration parameter \"some_extension.some_random_setting\"",
+        exception.getMessage());
+  }
+
+  @Test
+  public void testSetUnknownSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(SpannerException.class, () -> state.set(null, "some_random_setting", "value"));
+    assertEquals(
+        "INVALID_ARGUMENT: unrecognized configuration parameter \"some_random_setting\"",
+        exception.getMessage());
+  }
+
+  @Test
+  public void testSetUnknownExtensionSetting() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    // Setting an unknown extension setting is allowed.
+    state.set("some_extension", "some_random_setting", "my value");
+
+    assertEquals("my value", state.get("some_extension", "some_random_setting").getSetting());
+  }
+
+  @Test
+  public void testSetValidBoolValue() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+
+    state.set(null, "check_function_bodies", "on");
+    assertEquals("on", state.get(null, "check_function_bodies").getSetting());
+
+    state.set(null, "check_function_bodies", "off");
+    assertEquals("off", state.get(null, "check_function_bodies").getSetting());
+  }
+
+  @Test
+  public void testSetInvalidBoolValue() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(
+            SpannerException.class, () -> state.set(null, "check_function_bodies", "random_value"));
+    assertEquals(
+        "INVALID_ARGUMENT: parameter \"check_function_bodies\" requires a Boolean value",
+        exception.getMessage());
+  }
+
+  @Test
+  public void testSetValidIntegerValue() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+
+    state.set(null, "effective_cache_size", "10000");
+    assertEquals("10000", state.get(null, "effective_cache_size").getSetting());
+
+    state.set(null, "effective_cache_size", "20000");
+    assertEquals("20000", state.get(null, "effective_cache_size").getSetting());
+  }
+
+  @Test
+  public void testSetInvalidIntegerValue() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(
+            SpannerException.class, () -> state.set(null, "effective_cache_size", "random_value"));
+    assertEquals(
+        "INVALID_ARGUMENT: invalid value for parameter \"effective_cache_size\": \"random_value\"",
+        exception.getMessage());
+  }
+
+  @Test
+  public void testSetValidRealValue() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+
+    state.set(null, "cpu_tuple_cost", "0.02");
+    assertEquals("0.02", state.get(null, "cpu_tuple_cost").getSetting());
+
+    state.set(null, "cpu_tuple_cost", "0.01");
+    assertEquals("0.01", state.get(null, "cpu_tuple_cost").getSetting());
+  }
+
+  @Test
+  public void testSetInvalidRealValue() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(
+            SpannerException.class, () -> state.set(null, "cpu_tuple_cost", "random_value"));
+    assertEquals(
+        "INVALID_ARGUMENT: invalid value for parameter \"cpu_tuple_cost\": \"random_value\"",
+        exception.getMessage());
+  }
+
+  @Test
+  public void testSetValidEnumValue() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+
+    state.set(null, "bytea_output", "hex");
+    assertEquals("hex", state.get(null, "bytea_output").getSetting());
+
+    state.set(null, "bytea_output", "escape");
+    assertEquals("escape", state.get(null, "bytea_output").getSetting());
+  }
+
+  @Test
+  public void testSetInvalidEnumValue() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(SpannerException.class, () -> state.set(null, "bytea_output", "random_value"));
+    assertEquals(
+        "INVALID_ARGUMENT: invalid value for parameter \"bytea_output\": \"random_value\"",
+        exception.getMessage());
+  }
+
+  @Test
+  public void testSetInternalParam() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(SpannerException.class, () -> state.set(null, "segment_size", "100"));
+    assertEquals(
+        "INVALID_ARGUMENT: parameter \"segment_size\" cannot be changed", exception.getMessage());
+  }
+
+  @Test
+  public void testSetPostmasterParam() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(SpannerException.class, () -> state.set(null, "shared_buffers", "100"));
+    assertEquals(
+        "INVALID_ARGUMENT: parameter \"shared_buffers\" cannot be changed without restarting the server",
+        exception.getMessage());
+  }
+
+  @Test
+  public void testSetSighupParam() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(SpannerException.class, () -> state.set(null, "ssl", "off"));
+    assertEquals(
+        "INVALID_ARGUMENT: parameter \"ssl\" cannot be changed now", exception.getMessage());
+  }
+
+  @Test
+  public void testSetsuperuserBackendParam() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(SpannerException.class, () -> state.set(null, "jit_debugging_support", "off"));
+    assertEquals(
+        "INVALID_ARGUMENT: parameter \"jit_debugging_support\" cannot be set after connection start",
+        exception.getMessage());
+  }
+
+  @Test
+  public void testSetBackendParam() {
+    SessionState state = new SessionState(mock(OptionsMetadata.class));
+    SpannerException exception =
+        assertThrows(SpannerException.class, () -> state.set(null, "post_auth_delay", "100"));
+    assertEquals(
+        "INVALID_ARGUMENT: parameter \"post_auth_delay\" cannot be set after connection start",
+        exception.getMessage());
+  }
+}
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/statements/SessionStatementParserTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/statements/SessionStatementParserTest.java
new file mode 100644
index 00000000..f0fb7aaf
--- /dev/null
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/statements/SessionStatementParserTest.java
@@ -0,0 +1,172 @@
+// 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.statements;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertThrows;
+
+import com.google.cloud.spanner.Dialect;
+import com.google.cloud.spanner.SpannerException;
+import com.google.cloud.spanner.Statement;
+import com.google.cloud.spanner.connection.AbstractStatementParser;
+import com.google.cloud.spanner.pgadapter.statements.SessionStatementParser.ResetStatement;
+import com.google.cloud.spanner.pgadapter.statements.SessionStatementParser.SetStatement;
+import com.google.cloud.spanner.pgadapter.statements.SessionStatementParser.ShowStatement;
+import com.google.cloud.spanner.pgadapter.statements.SimpleParser.TableOrIndexName;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.JUnit4;
+
+@RunWith(JUnit4.class)
+public class SessionStatementParserTest {
+  private static final AbstractStatementParser PG_PARSER =
+      AbstractStatementParser.getInstance(Dialect.POSTGRESQL);
+
+  @Test
+  public void testParseShowAll() {
+    assertEquals(
+        ShowStatement.createShowAll(),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show all"))));
+  }
+
+  @Test
+  public void testParseShow() {
+    assertEquals(
+        new ShowStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show foo"))));
+    assertEquals(
+        new ShowStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show \"foo\""))));
+    assertEquals(
+        new ShowStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show\t\"foo\""))));
+    assertEquals(
+        new ShowStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show FOO"))));
+    assertEquals(
+        new ShowStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show \"FOO\""))));
+    assertEquals(
+        new ShowStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show\t\"FOO\""))));
+
+    assertEquals(
+        new ShowStatement(new TableOrIndexName("foo", "bar")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show foo.bar"))));
+    assertEquals(
+        new ShowStatement(new TableOrIndexName("foo", "bar")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show \"FOO\".bar"))));
+    assertEquals(
+        new ShowStatement(new TableOrIndexName("foo", "bar")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show\t\"FOO\".\"Bar\""))));
+
+    assertThrows(
+        SpannerException.class,
+        () -> SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show"))));
+    assertThrows(
+        SpannerException.class,
+        () -> SessionStatementParser.parse(PG_PARSER.parse(Statement.of("show foo bar"))));
+  }
+
+  @Test
+  public void testParseReset() {
+    assertEquals(
+        new ResetStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset foo"))));
+    assertEquals(
+        new ResetStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset \"foo\""))));
+    assertEquals(
+        new ResetStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset\t\"foo\""))));
+    assertEquals(
+        new ResetStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset FOO"))));
+    assertEquals(
+        new ResetStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset \"FOO\""))));
+    assertEquals(
+        new ResetStatement(new TableOrIndexName("foo")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset\t\"FOO\""))));
+
+    assertEquals(
+        new ResetStatement(new TableOrIndexName("foo", "bar")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset foo.bar"))));
+    assertEquals(
+        new ResetStatement(new TableOrIndexName("foo", "bar")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset \"FOO\".bar"))));
+    assertEquals(
+        new ResetStatement(new TableOrIndexName("foo", "bar")),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset\t\"FOO\".\"Bar\""))));
+
+    assertThrows(
+        SpannerException.class,
+        () -> SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset"))));
+    assertThrows(
+        SpannerException.class,
+        () -> SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset foo bar"))));
+  }
+
+  @Test
+  public void testParseResetAll() {
+    assertEquals(
+        ResetStatement.createResetAll(),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("reset all"))));
+  }
+
+  @Test
+  public void testParseSetTo() {
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set foo to bar"))));
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set session foo to bar"))));
+    assertEquals(
+        new SetStatement(true, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set local foo to bar"))));
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set \"foo\" to 'bar'"))));
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set \"foo\" to \"bar\""))));
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), null),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set foo to default"))));
+  }
+
+  @Test
+  public void testParseSetEquals() {
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set foo = bar"))));
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set session foo = bar"))));
+    assertEquals(
+        new SetStatement(true, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set local foo = bar"))));
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set \"foo\" = 'bar'"))));
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), "bar"),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set \"foo\" = \"bar\""))));
+    assertEquals(
+        new SetStatement(false, new TableOrIndexName("foo"), null),
+        SessionStatementParser.parse(PG_PARSER.parse(Statement.of("set foo = default"))));
+  }
+}
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/statements/local/ShowSearchPathStatementTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/statements/local/ShowSearchPathStatementTest.java
deleted file mode 100644
index f7053024..00000000
--- a/src/test/java/com/google/cloud/spanner/pgadapter/statements/local/ShowSearchPathStatementTest.java
+++ /dev/null
@@ -1,47 +0,0 @@
-// 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.statements.local;
-
-import static org.junit.Assert.assertEquals;
-import static org.junit.Assert.assertFalse;
-import static org.junit.Assert.assertTrue;
-import static org.mockito.Mockito.mock;
-import static org.mockito.Mockito.when;
-
-import com.google.cloud.spanner.ResultSet;
-import com.google.cloud.spanner.pgadapter.statements.BackendConnection;
-import org.junit.Test;
-import org.junit.runner.RunWith;
-import org.junit.runners.JUnit4;
-
-@RunWith(JUnit4.class)
-public class ShowSearchPathStatementTest {
-
-  @Test
-  public void testExecute() {
-    for (String schema : new String[] {"public", "my_schema"}) {
-      BackendConnection backendConnection = mock(BackendConnection.class);
-      when(backendConnection.getCurrentSchema()).thenReturn(schema);
-
-      try (ResultSet resultSet =
-          ShowSearchPathStatement.INSTANCE.execute(backendConnection).getResultSet()) {
-        assertTrue(resultSet.next());
-        assertEquals(1, resultSet.getColumnCount());
-        assertEquals(schema, resultSet.getString("search_path"));
-        assertFalse(resultSet.next());
-      }
-    }
-  }
-}
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/statements/local/ShowServerVersionStatementTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/statements/local/ShowServerVersionStatementTest.java
deleted file mode 100644
index 1106d662..00000000
--- a/src/test/java/com/google/cloud/spanner/pgadapter/statements/local/ShowServerVersionStatementTest.java
+++ /dev/null
@@ -1,50 +0,0 @@
-// 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.statements.local;
-
-import static org.junit.Assert.assertEquals;
-import static org.junit.Assert.assertFalse;
-import static org.junit.Assert.assertTrue;
-import static org.mockito.Mockito.mock;
-import static org.mockito.Mockito.when;
-
-import com.google.cloud.spanner.ResultSet;
-import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
-import com.google.cloud.spanner.pgadapter.statements.BackendConnection;
-import org.junit.Test;
-import org.junit.runner.RunWith;
-import org.junit.runners.JUnit4;
-
-@RunWith(JUnit4.class)
-public class ShowServerVersionStatementTest {
-
-  @Test
-  public void testExecute() {
-    for (String version : new String[] {"1.0.0", "14.0.1"}) {
-      BackendConnection backendConnection = mock(BackendConnection.class);
-      OptionsMetadata options = mock(OptionsMetadata.class);
-      when(options.getServerVersion()).thenReturn(version);
-      when(backendConnection.getOptionsMetadata()).thenReturn(options);
-
-      try (ResultSet resultSet =
-          ShowServerVersionStatement.INSTANCE.execute(backendConnection).getResultSet()) {
-        assertTrue(resultSet.next());
-        assertEquals(1, resultSet.getColumnCount());
-        assertEquals(version, resultSet.getString("server_version"));
-        assertFalse(resultSet.next());
-      }
-    }
-  }
-}