Postgres standard_conforming_strings - should it be on or off?

Well, I think we should keep standard_conforming_strings ON. Since PostgreSQL 9.1 the default is ON, so now for over 4 years, and I'm pretty sure they changed the default to ON for some good reasons. I think it's always best to go with the defaults.

The default for PostgreSQL "standard_conforming_strings" is to ON since 9.1 because PostgreSQL aims at conforming to the SQL standard by default. The standard says that backslash is a normal character.

MySQL has a setting which does exactly the same thing: NO_BACKSLASH_ESCAPES in sql_mode since version 5.0.1. See http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

The crucial point is that with both defaults, MySQL is the opposite of PostgreSQL 9.1+, meaning that backslash quotes the next character, as in PHP strings.

As Drupal aims at cross-database compatibility, it should adopt settings that make queries compatible across the supported engines. To me it's the PostgreSQL setting that should be aligned to MySQL's default, because MySQL is the majority and most people who produce queries are not even aware that backslash might be something else than a quote character.

I have to agree with @bzrudi71 here. In an ideal world, we'd be able to set "standard_conforming_strings" to ON, and fix up any tests (or code) that wasn't working with it set to ON. It's been the default in PostgreSQL for many years, and given the expected lifespan of Drupal 8, I'd hate to be dependent on deprecated way of handling escaping for the next several years into the future.

I understand that doing that means that it's different than the MySQL default -- which is unfortunate.

I'm trying to requeue the tests now that #2565259: Some route serializations in update test database dumps are broken has been committed, but it seems the Drupal CI system is having problems this morning.

OK, looks like this patch might need a re-roll, as it looks like the patch no longer applies:

Checking patch core/lib/Drupal/Core/Database/Driver/pgsql/Install/Tasks.php...
Hunk #1 succeeded at 188 (offset -1 lines).
Hunk #2 succeeded at 202 (offset -1 lines).
error: while searching for:
      if (!$this->checkStandardConformingStringsSuccess()) {
        $replacements = array(
          '%setting' => 'standard_conforming_strings',
          '%current_value' => 'off',
          '%needed_value' => 'on',
          '!query' => "<code>" . $query . "<\/code>",
        );
        $this->fail(t("The %setting setting is currently set to '%current_value', but needs to be '%needed_value'. Change this by running the following query: !query", $replacements));

error: patch failed: core/lib/Drupal/Core/Database/Driver/pgsql/Install/Tasks.php:223
error: core/lib/Drupal/Core/Database/Driver/pgsql/Install/Tasks.php: patch does not apply
Checking patch core/modules/simpletest/src/Tests/KernelTestBaseTest.php...
Checking patch core/modules/system/src/Tests/Database/SchemaTest.php...
Checking patch core/modules/system/src/Tests/Update/UpdatePathTestBaseTest.php...
Hunk #1 succeeded at 70 (offset 21 lines).
Checking patch core/tests/Drupal/KernelTests/KernelTestBaseTest.php...
error: while searching for:
    // Ensure that the database tasks have been run during set up. Neither MySQL
    // nor SQLite make changes that are testable.
    if ($database->driver() == 'pgsql') {
      $this->assertEquals('on', $database->query("SHOW standard_conforming_strings")->fetchField());
      $this->assertEquals('escape', $database->query("SHOW bytea_output")->fetchField());
    }
  }

error: patch failed: core/tests/Drupal/KernelTests/KernelTestBaseTest.php:89
error: core/tests/Drupal/KernelTests/KernelTestBaseTest.php: patch does not apply

Re-rolling the patch from comment 5, as it no longer applies to head.