phalcon/cphalcon · error · MaterializedViewsNotSupported

Materialized views are not supported by this dialect

Error message

Materialized views are not supported by this dialect

What it means

The base Phalcon\Db\Dialect implements createMaterializedView() as an unconditional throw: materialized views are a PostgreSQL feature (`CREATE MATERIALIZED VIEW ... AS`), so only the Postgresql dialect overrides it. Calling it on a Mysql or Sqlite dialect/adapter raises MaterializedViewsNotSupported instead of emitting invalid SQL. The dialect exposes supportsMaterializedViews() (false on the base class) to probe this.

Source

Thrown at phalcon/Db/Dialect.zep:514

    /**
     * Registers custom SQL functions
     */
    public function registerCustomFunction(string name, callable customFunction) -> <static>
    {
        let this->customFunctions[name] = customFunction;

        return this;
    }

    /**
     * Generates SQL to create a materialized view. Supported by PostgreSQL
     * (`CREATE MATERIALIZED VIEW name AS <sql>`). Other dialects inherit
     * this throw - MySQL and SQLite have no materialized-view concept.
     */
    public function createMaterializedView( string viewName,  array definition, string schemaName = null) -> string
    {
        throw new MaterializedViewsNotSupported();
    }

    /**
     * Generates SQL to drop a materialized view. Supported by PostgreSQL.
     */
    public function dropMaterializedView( string viewName, string schemaName = null, bool ifExists = true) -> string
    {
        throw new MaterializedViewsNotSupported();
    }

    /**
     * Generates SQL to refresh a materialized view. Supported by
     * PostgreSQL. Pass `concurrent = true` for `REFRESH MATERIALIZED VIEW
     * CONCURRENTLY ...`, which avoids blocking concurrent SELECTs (requires
     * the view to have a unique index).
     */
    public function refreshMaterializedView( string viewName, string schemaName = null, bool concurrent = false) -> string
    {

View on GitHub (pinned to b7419de9cd)

Solutions

1. Guard the call: if ($connection->getDialect()->supportsMaterializedViews()) { ... }.
2. On MySQL, emulate with a regular table plus INSERT ... SELECT refresh (cron/EVENT) - MySQL has no matview concept.
3. On SQLite, use a normal view or a shadow table refreshed in a transaction.
4. Move PostgreSQL-only DDL into driver-specific migration files.

Example fix

// before
$connection->createMaterializedView('sales_mv', [
    'sql' => 'SELECT product_id, SUM(qty) FROM sales GROUP BY product_id',
]); // throws MaterializedViewsNotSupported on MySQL/SQLite

// after
if ($connection->getDialect()->supportsMaterializedViews()) {
    $connection->createMaterializedView('sales_mv', [
        'sql' => 'SELECT product_id, SUM(qty) FROM sales GROUP BY product_id',
    ]);
} else {
    $connection->execute(
        'CREATE TABLE sales_mv AS SELECT product_id, SUM(qty) FROM sales GROUP BY product_id'
    );
}

if (!$connection->getDialect()->supportsMaterializedViews()) {
    throw new RuntimeException('This database does not support materialized views');
}
$connection->createMaterializedView('sales_mv', ['sql' => $query]);

use Phalcon\Db\Exceptions\MaterializedViewsNotSupported;

try {
    $connection->createMaterializedView('sales_mv', ['sql' => $query]);
} catch (MaterializedViewsNotSupported $e) {
    // Non-PostgreSQL connection - fall back to a plain table
    $connection->execute("CREATE TABLE sales_mv AS {$query}");
}

When it happens

Trigger: Calling $connection->createMaterializedView(...) on a Pdo\Mysql or Pdo\Sqlite connection; generic migration code running the same DDL against every configured adapter; feature-probing by direct call instead of supportsMaterializedViews().

Common situations: Multi-database apps running a shared migration set; CI matrices (mysql + pgsql) where a PostgreSQL-only migration leaks into the MySQL run; code ported from a Postgres-centric project to adapters without matviews.

Related errors

• RETURNING clauses are not supported by this dialect
• ON CONFLICT requires at least one conflict-target column
• Operator '{}' is not supported by this SQL dialect
• The index 'sql' is required in the definition array
• Role '{roleName}' (to inherit) produces an infinite loop

AI-assisted analysis of phalcon/cphalcon@b7419de9cd (2026-08-21). Data as JSON: /api/errors/4be6e7b506f9c80e. Report an issue: GitHub.