Cockroach: sql: Compatibility with Doctrine/Symfony

[knz]

So I have been iterating for a while with the folk at https://github.com/radutopala/cockroach-app/issues/3#issuecomment-337453864 who are checking compatibility between CockroachDB and Symfony (PHP web framework) with Doctrine (PHP ORM).

The latest news is a full pg-side trace of how this software stack talks to a pg server:

2017-10-18 03:55:18 UTC [9282-1] postgres@postgres LOG:  execute <unnamed>: SET NAMES 'UTF8'
2017-10-18 03:55:18 UTC [9282-2] postgres@postgres LOG:  statement: CREATE DATABASE "symfony"
2017-10-18 03:55:24 UTC [9287-1] postgres@symfony LOG:  execute <unnamed>: SET NAMES 'UTF8'
2017-10-18 03:55:24 UTC [9287-2] postgres@symfony LOG:  execute <unnamed>: SELECT schema_name AS nspname
                    FROM   information_schema.schemata
                    WHERE  schema_name NOT LIKE 'pg_%'
                    AND    schema_name != 'information_schema'
2017-10-18 03:55:24 UTC [9287-3] postgres@symfony LOG:  execute <unnamed>: SELECT sequence_name AS relname,
                           sequence_schema AS schemaname
                    FROM   information_schema.sequences
                    WHERE  sequence_schema NOT LIKE 'pg_%'
                    AND    sequence_schema != 'information_schema'
2017-10-18 03:55:24 UTC [9287-4] postgres@symfony LOG:  execute <unnamed>: SELECT quote_ident(table_name) AS table_name,
                           table_schema AS schema_name
                    FROM   information_schema.tables
                    WHERE  table_schema NOT LIKE 'pg_%'
                    AND    table_schema != 'information_schema'
                    AND    table_name != 'geometry_columns'
                    AND    table_name != 'spatial_ref_sys'
                    AND    table_type != 'VIEW'
2017-10-18 03:55:24 UTC [9287-5] postgres@symfony LOG:  execute <unnamed>: SHOW search_path
2017-10-18 03:55:24 UTC [9287-6] postgres@symfony LOG:  execute <unnamed>: SHOW search_path
2017-10-18 03:55:24 UTC [9287-7] postgres@symfony LOG:  execute <unnamed>: SELECT schema_name AS nspname
                    FROM   information_schema.schemata
                    WHERE  schema_name NOT LIKE 'pg_%'
                    AND    schema_name != 'information_schema'
2017-10-18 03:55:24 UTC [9287-8] postgres@symfony LOG:  execute <unnamed>: SELECT sequence_name AS relname,
                           sequence_schema AS schemaname
                    FROM   information_schema.sequences
                    WHERE  sequence_schema NOT LIKE 'pg_%'
                    AND    sequence_schema != 'information_schema'
2017-10-18 03:55:24 UTC [9287-9] postgres@symfony LOG:  execute <unnamed>: SELECT quote_ident(table_name) AS table_name,
                           table_schema AS schema_name
                    FROM   information_schema.tables
                    WHERE  table_schema NOT LIKE 'pg_%'
                    AND    table_schema != 'information_schema'
                    AND    table_name != 'geometry_columns'
                    AND    table_name != 'spatial_ref_sys'
                    AND    table_type != 'VIEW'
2017-10-18 03:55:24 UTC [9287-10] postgres@symfony LOG:  execute <unnamed>: SHOW search_path
2017-10-18 03:55:24 UTC [9287-11] postgres@symfony LOG:  execute <unnamed>: SHOW search_path
2017-10-18 03:55:24 UTC [9287-12] postgres@symfony LOG:  execute <unnamed>: CREATE SEQUENCE automailer_id_seq INCREMENT BY 1 MINVALUE 1 START 1
2017-10-18 03:55:24 UTC [9287-13] postgres@symfony LOG:  execute <unnamed>: CREATE TABLE automailer (id INT NOT NULL, from_email VARCHAR(255) NOT NULL, from_name VARCHAR(255) NOT NULL, to_email VARCHAR(255) NOT NULL, subject TEXT NOT NULL, body TEXT NOT NULL, alt_body TEXT NOT NULL, swift_message TEXT NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, sent_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, started_sending_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, is_html BOOLEAN NOT NULL, is_sending BOOLEAN DEFAULT NULL, is_sent BOOLEAN DEFAULT NULL, is_failed BOOLEAN DEFAULT NULL, PRIMARY KEY(id))
2017-10-18 03:55:24 UTC [9287-14] postgres@symfony LOG:  execute <unnamed>: CREATE INDEX find_next ON automailer (is_sent, is_failed, is_sending, created_at)
2017-10-18 03:55:24 UTC [9287-15] postgres@symfony LOG:  execute <unnamed>: CREATE INDEX recover_sending ON automailer (is_sending, started_sending_at)

of interest:

1) it wants quote_ident() - #15006 cc @jordanlewis @justinj
2) it wants sequences - #5811 #19196 with various options, cc @vilterp

[knz]

cc @awoods187

[jordanlewis]

Also required is SELECT FROM <sequence> the way Postgres supports it.

[vilterp]

Filed select from sequence as #21499

[lemonCMS]

Hello,

I also noticed when trying to change a column, doctrine wants the function string_to_array
````sql
An exception occurred while executing 'SELECT
a.attnum,
quote_ident(a.attname) AS field,
t.typname AS type,
format_type(a.atttypid, a.atttypmod) AS complete_type, (SELECT tc.collcollate FROM pg_catalog.pg_collation tc WHERE tc.oid = a.attcollation) AS collation,
(SELECT t1.typname FROM pg_catalog.pg_type t1 WHERE t1.oid = t.typbasetype) AS domain_type,
(SELECT format_type(t2.typbasetype, t2.typtypmod) FROM
pg_catalog.pg_type t2 WHERE t2.typtype = 'd' AND t2.oid = a.atttypid) AS domain_complete_type,
a.attnotnull AS isnotnull,
(SELECT 't'
FROM pg_index
WHERE c.oid = pg_index.indrelid
AND pg_index.indkey[0] = a.attnum
AND pg_index.indisprimary = 't'
) AS pri,
(SELECT pg_get_expr(adbin, adrelid)
FROM pg_attrdef
WHERE c.oid = pg_attrdef.adrelid
AND pg_attrdef.adnum=a.attnum
) AS default,
(SELECT pg_description.description
FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid
) AS comment
FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'pages' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"',user),' ','') from pg_catalog.pg_settings where name = 'search_path'),','))
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND n.oid = c.relnamespace
ORDER BY a.attnum':

SQLSTATE[42883]: Undefined function: 7 ERROR: unknown function: string_to_array()
````
---Update---
Looking further into the issue on how to change a column type with old fashioned SQL, i noticed this is not yet possible. So this it not really relevant at this point.

[knz]

Filed #23144 for string_to_array

[jordanlewis]

This might be ready soon. We should retest once #24190 merges.

[bobvawter]

Link to #9851 for changing column types.

[Freeaqingme]

Last time I tried Doctrine, I ran primarily into issues with correlated subqueries. These have been fixed (IIRC), thanks for that :)

I thought I'd give Doctrine another try. I'm running 'CCL v19.2.0-alpha.20190805 @ 2019/08/05 15:48:44 (go1.12.5)', combined with (what I presume to be the latest version) Doctrine doctrine/dbal v2.9.2 . I'm following the steps as documented here: https://symfony.com/doc/current/doctrine.html#installing-doctrine

After having defined the entity, I run the command 'php bin/console make:migration' which applies the entity as defined in PHP onto Cockroachdb/Postgres. Previously, this is where the correlated subquery error showed up. That's gone now. Instead, the following error shows:

Unknown database type _text requested, Doctrine\DBAL\Platforms\PostgreSQL94Platform may not support it.

It refers to PostgreSQL94Platform, because Cockroach identifies as v9.5, and there's no other PostgreSQL*Platform class between 9.4 and 10.0 in Doctrine. This error can be worked around, however. In doctrine.yaml:

doctrine:
    dbal:
        driver: 'pdo_pgsql'
        url: '%env(resolve:DATABASE_URL)%'
        mapping_types:
            _text: string

That brings about the next error:

Unknown database type _int8 requested, Doctrine\DBAL\Platforms\PostgreSQL94Platform may not support it.

Which can also be worked around by adding an additional mapping type, namely '_int8' to 'integer'.

Afterwards, this error is presented:

Unknown database type int2vector requested, Doctrine\DBAL\Platforms\PostgreSQL94Platform may not support it.

I have not yet been able to find a workaround for that. Forcing to use the PostgreSQL100Platform class does not work, for one.

I'm happy to join on Gitter to help out with debugging if that helps. I don't know much about Doctrine internals, but one should think that there's not too much in the way still.

[jordanlewis]

Thanks for the updates! The problem is that we don't support these type aliases, apparently. underscore-prefixed types are arrays of that type. Int2vector is (essentially) an integer array. Could you try mapping those types to their array equivalents?

[Freeaqingme]

Success! By adding the mapping of 'int2vector' to 'array' I was able to get the 'migration_versions' table which is used by Doctrine to track what migrations had or had not yet been applied.

The next step would be:

$ php bin/console doctrine:migrations:migrate

This results in:

$ php bin/console doctrine:migrations:migrate

                    Application Migrations                    


WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (y/n)y
Migrating up to 20190904205802 from 0

  ++ migrating 20190904205802

     -> CREATE SEQUENCE product_id_seq INCREMENT BY 1 MINVALUE 1 START 1
     -> CREATE TABLE product (id INT NOT NULL, name VARCHAR(255) NOT NULL, price INT NOT NULL, PRIMARY KEY(id))
     -> DROP TABLE crdb_internal.backward_dependencies
Migration 20190904205802 failed during Execution. Error An exception occurred while executing 'DROP TABLE crdb_internal.backward_dependencies':

SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  user intranet_dev does not have DROP privilege on relation backward_dependencies

I think this is caused by one of these queries that Doctrine performs behind the scenes:

root@:26257/intranet_dev> SELECT quote_ident(table_name) AS table_name,                                                                                   table_schema AS schema_name                                                                                                 FROM   information_schema.tables                                                                                                   WHERE  table_schema NOT LIKE 'pg\_%'                                                                                               AND    table_schema != 'information_schema'                                                                                        AND    table_name != 'geometry_columns'                                                                                            AND    table_name != 'spatial_ref_sys'                                                                                             AND    table_type != 'VIEW';

         table_name         |  schema_name   
+---------------------------+---------------+
  backward_dependencies     | crdb_internal  
  builtin_functions         | crdb_internal  
  cluster_queries           | crdb_internal  
  cluster_sessions          | crdb_internal  
...snip... 
  table_indexes             | crdb_internal  
  tables                    | crdb_internal  
  zones                     | crdb_internal  
  migration_versions        | public         
(35 rows)

Time: 1.391344ms

root@:26257/defaultdb> SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_.*' AND nspname != 'information_schema';

     nspname     
+---------------+
  crdb_internal  
  public         
(2 rows)

Time: 949.165µs

root@:26257/defaultdb> 

However, I modified (hotfixed) these queries in the Doctrine source code to not just exclude pg_* but also exclude crdb_*. I don't know if the Doctrine community is willing to adapt these queries for Cockroachdb (having not gained mainstream status, yet). However, even by excluding the crdb_* schemas, the same error still showed. So it would seem I didn't manage to capture all queries.

I think the next step in attaining compatibility with Doctrine would be to take care of those type mappings, and provide, perhaps, a way to not expose the crdb_internal schema to unprivileged or select users?

[Freeaqingme]

In my previous post I missed that I had also had performed another command, namely:

php bin/console make:migration

This command is executed before the '''doctrine:migrations:migrate''' and creates the migration files that describe how each migration is to be performed. This is where the '''DROP TABLE crdb_internal.backward_dependencies''' (etc) queries were cached. So I removed these migration files, and hotfixed the following methods to also exclude the crdb_ internal schema in:

• PostgreSqlPlatform::getListNamespacesSQL()
• PostgreSqlPlatform::getListSequencesSQL()
• PostgreSqlPlatform::getListTablesSQL()
• PostgreSqlSchemaManager::getSchemaNames()

Afterwards, we can do:

0 [dolf@dolf-desktop] ~/Projects/tipuna/intranet
$ php bin/console make:migration


  Success! 


 Next: Review the new migration "src/Migrations/Version20190904224707.php"
 Then: Run the migration with php bin/console doctrine:migrations:migrate
 See https://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html

0 [dolf@dolf-desktop] ~/Projects/tipuna/intranet
$ php bin/console doctrine:migrations:migrate

                    Application Migrations                    


WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (y/n)y
Migrating up to 20190904224707 from 0

  ++ migrating 20190904224707

     -> CREATE SEQUENCE product_id_seq INCREMENT BY 1 MINVALUE 1 START 1
     -> CREATE TABLE product (id INT NOT NULL, name VARCHAR(255) NOT NULL, price INT NOT NULL, PRIMARY KEY(id))

  ++ migrated (took 17.9ms, used 16M memory)

  ------------------------

  ++ finished in 20ms
  ++ used 16M memory
  ++ 1 migrations executed
  ++ 2 sql queries

0 [dolf@dolf-desktop] ~/Projects/tipuna/intranet

So my conclusion from my previous post still stands, besides the type mappings, we should probably either hide the crdb_ schema, or ask the Doctrine community if they're willing to adapt these queries (or introduce a separate adapter for CRDB).

Beware, so far I've only applied a single transaction. Next step is actually using Doctrine :)

[Freeaqingme]

Next issue I run into is sequences: Doctrine tries to interpret sequences, and I got the error:

An exception occurred while executing 'SELECT min_value, increment_by FROM "product_id_seq"':
SQLSTATE[42703]: Undefined column: 7 ERROR: column "min_value" does not exist

intranet_dev=# \d
                   List of relations
 Schema |        Name        |   Type   |    Owner     
--------+--------------------+----------+--------------
 public | migration_versions | table    | intranet_dev
 public | product_id_seq     | sequence | intranet_dev
(2 rows)

intranet_dev=# SELECT * FROM "product_id_seq";
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f
(1 row)

intranet_dev=# 

It seems the way sequences are exposed are different from how Postgresql presents them.

[knz]

There are two things worth noting about sequences:

1. the virtual columns produced for a SELECT on sequences are missing the min_value and increment_by fields. This needs to be added to cockroachdb. @jordanlewis may be able to comment further.

2. if doctrine expects sequences to be automatically created when using the SERIAL pseudo-type, you will also need to set the session variable experimental_serial_normalization to sql_sequences for compatibility.

Cheers

[Freeaqingme]

Alright, I'm starting another project, and figured I'd give it a shot again. I've been using CRDB with Go for the past 5 years or so, but would like to be able to do so with my php projects as well. So far, success. But I do acknowledge that there may be another bump in the road which I'll report on as well.

There's still quite a few things not working entirely properly yet, but at least there are no errors during the mgirations. For now the following works, I may turn it into a separate symfony bundle.

EDIT: I've continued improving and expanding the code below. I'm not looking to maintain a symfony bundle with this code, but if you want to do that, feel free to contact me so I can give you the latest version of what I have.

# doctrine.yaml
doctrine:
    dbal:
        user: root
        port: 26257
        host: localhost
        dbname: databaseName
        platform_service: App\Db\CockroachPlatform
        driver_class: App\Db\CockroachDriver

// src/Db/CockroachDriver.php

<?php


namespace App\Db;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver\PDOPgSql;
use Doctrine\DBAL\Driver\PDO;


/**
 * Driver that connects through pdo_pgsql.
 *
 * @deprecated Use {@link PDO\PgSQL\Driver} instead.
 */
class CockroachDriver extends PDOPgSql\Driver
{

    /**
     * {@inheritdoc}
     */
    public function connect(array $params, $username = null, $password = null, array $driverOptions = [])
    {
        /** @var PDO\Connection $pdo */
        $pdo = call_user_func_array(array($this, 'parent::connect'), func_get_args());

//        $res = $pdo->exec('set experimental_serial_normalization=sql_sequence;');

        return $pdo;
    }

    public function getSchemaManager(Connection $conn)
    {
        return new CockroachSchemaManager($conn);
    }

}

// src/Db/CockroachPlatform.php
<?php

namespace App\Db;

use Doctrine\DBAL\Platforms\PostgreSQL100Platform;

class CockroachPlatform extends PostgreSQL100Platform {

    /**
     * {@inheritDoc}
     */
    public function getListNamespacesSQL()
    {
        return "SELECT schema_name AS nspname
                FROM   information_schema.schemata
                WHERE  schema_name NOT LIKE 'pg\_%'
                AND    schema_name != 'information_schema'
                AND    schema_name != 'crdb_internal'";
    }

    /**
     * {@inheritDoc}
     */
    public function getListSequencesSQL($database): string
    {
        return "SELECT sequence_name AS relname,
                       sequence_schema AS schemaname
                FROM   information_schema.sequences
                WHERE  sequence_schema NOT LIKE 'pg\_%'
                AND    sequence_schema != 'information_schema'
                AND    sequence_schema != 'crdb_internal'";
    }

    /**
     * {@inheritDoc}
     */
    public function getListTablesSQL()
    {
        return "SELECT quote_ident(table_name) AS table_name,
                       table_schema AS schema_name
                FROM   information_schema.tables
                WHERE  table_schema NOT LIKE 'pg\_%'
                AND    table_schema != 'information_schema'
                AND    table_name != 'geometry_columns'
                AND    table_name != 'spatial_ref_sys'
                AND    table_type != 'VIEW'
                AND    table_schema != 'crdb_internal'";
    }

    /**
     * {@inheritDoc}
     */
    protected function initializeDoctrineTypeMappings()
    {
        parent::initializeDoctrineTypeMappings();

        $this->doctrineTypeMapping = array_merge($this->doctrineTypeMapping, [
            '_text' => 'string',
            '_int8' => 'integer',
            'int2vector' => 'array',
        ]);
    }
}

// src/Db/CockroachSchemaManager.php

<?php


namespace App\Db;


use Doctrine\DBAL\Schema\PostgreSqlSchemaManager;
use Doctrine\DBAL\Schema\Sequence;

class CockroachSchemaManager extends PostgreSqlSchemaManager
{
    /**
     * {@inheritdoc}
     */
    protected function _getPortableSequenceDefinition($sequence)
    {
        if ($sequence['schemaname'] !== 'public') {
            $sequenceName = $sequence['schemaname'] . '.' . $sequence['relname'];
        } else {
            $sequenceName = $sequence['relname'];
        }

        if (! isset($sequence['increment_by'], $sequence['min_value'])) {
            $sequence['min_value'] = 0;
            $sequence['increment_by'] = 0;
//            /** @var string[] $data */
//            $data = $this->_conn->fetchAssoc(
//                'SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName)
//            );
//
//            $sequence += $data;
        }

        return new Sequence($sequenceName, (int) $sequence['increment_by'], (int) $sequence['min_value']);
    }
}

I did try to give the experimental_serial_normalization thing a shot, but that only resulted in errors (on v20.2.0).