SqlBase::listTablesQuoted() does not work with PostgreSQL-15 with \timing in ~/.psqlrc

[Sweetchuck]

SqlBase::listTablesQuoted() adds invalid elements to the return array
Command sql:drop does not work with PostgreSQL(15.2) when the ~/.psqlrc contains \timing directive.
After running the drush sql:drop command, every DB table is still there, and there is no error or warning in the stdOutput.

Same problem with the drush site:install command.
There is a confirmation question:

Do you really want to drop all tables in the database ...?

but after that the site install fails because of the still existing tables.

After a short debugging, I found that in the \Drush\Commands\sql\SqlCommands::drop()
the $tables = $sql->listTablesQuoted(); does not works as expected, because of the last item in the array is an invalid item.

var_dump($tables);

  string(10) "cache_page"
  [69] =>
  string(12) "cache_entity"
  [70] =>
  string(16) "key_value_expire"
  [71] =>
  string(5) "queue"
  [72] =>
  string(14) "Time: 1.169 ms"
}

The Time: 1.169 ms is there because my ~/.psqlrc contains the \timing.

System Configuration

Q	A
Drush version?	11.5.1
Drupal version?	10.x
PHP version	8.1
OS?	Linux
PostgreSQL	15.2

[Sweetchuck]

Other solution would be to add the --no-psqlrc flag.

[weitzman]

If the user turned on timing, then I'm not 100% sure we should turn it off for all our queries. I could go either way.

[Sweetchuck]

Because it is part of the output, and it is not a valid table name.

cache_page
cache_entity
key_value_expire
queue
Time: 1.169 ms

[Sweetchuck]

The CI failed in MR #5546 because it uses PostgreSQL-10 and the \timing is not supported.
Maybe that is why the --no-psqlrc is a better approach, and there can be other options which are influences the format and the content of the output.

[Chi-teck]

Besides timing there are some pset commands that can mess the table processing. Seems like the --no-psqlrc is only possible approach.

[Sweetchuck]

There are other problems as well :-(

\Drush\Sql\SqlPgsql::dbExists()
Always returns TRUE with PostgreSQL-15.

Exit code of the command psql --no-psqlrc -t -c "SELECT 1 AS result FROM pg_database WHERE datname='foo'" shows that the query ran successfully.
It doesn't care about the number of records.

return $process->isSuccessful()
    && trim($process->getOutput()) === '1';

[Sweetchuck]

\Drush\Commands\core\ArchiveRestoreCommands::importDatabase

        if ($isDbExist && !$sql->drop($sql->listTablesQuoted())) {
            throw new Exception(
                dt('Failed to drop database !database.', ['!database' => $databaseSpec['database']])
            );
        }

        if (!$isDbExist && !$sql->createdb(true)) {
            throw new Exception(
                dt('Failed to create database !database.', ['!database' => $databaseSpec['database']])
            );
        }

---

ps.: Edited. #5639

[Sweetchuck]

Currently my problem is that I can't run the test when PostgreSQL port number is other than the default 5432. In my case it is 5415.

Environment variable UNISH_DB_URL has the correct value, and some psql command running during the test use the correct port number, but some commands don't.

[Sweetchuck]

Tests are create databases like this archive_dump_restore_test_1686171279, but it never gets deleted in the ::tearDown().

[weitzman]

I think a --no-psqlrc flag would cause as much harm as good.

[Sweetchuck]

@weitzman Could you provide some details?

[Sweetchuck]

I just found this
https://pgpedia.info/p/psqlrc.html

PSQLRC environment variable:
setting this variable will cause psql to look for the .psqlrc file in the specified location.

That means Drush could ship a custom psqlrc file, and use it with every psql command by settings the PSQLRC environment variable to a custom filepath.