_db.TableExists<Class> stopped working on materialized views

DigitalReach · November 5, 2019, 9:21pm

I just update a project to 5.7.1 that uses:

db.TableExists<MyClass>()

To trigger a seed script that creates materialized views. Before the update it would return true is the mat view existed. After the update it returns false. Testing on normal tables works fine, it seems to just be issue with materialized view.

I am using PostgreSQL 10.9.

mythz · November 5, 2019, 10:02pm

The behavior of TableExists<T> for PostgreSQL has not changed which checks for relkind = ‘r’ (i.e. ordinary table) where as Material views should report relkind = ‘m’ so it should not report true for materialized views.

DigitalReach · November 5, 2019, 10:57pm

In version 5.5.0 it returns true (desired behaviour)

In version 5.7.1 it returns false.

How can I check if a materialized view exists in 5.7.1?

Thanks

mythz · November 5, 2019, 11:39pm

The only expected behavior is what’s returned by the relkind = ‘r’ (i.e ordinary table) metadata query:

github.com

ServiceStack/ServiceStack.OrmLite/blob/196af42a2338c76cbfdce60d5e75a41cccefc5be/src/ServiceStack.OrmLite.PostgreSQL/PostgreSQLDialectProvider.cs#L409-L436


public override bool DoesTableExist(IDbCommand dbCmd, string tableName, string schema = null)
{
    var sql = !Normalize || ReservedWords.Contains(tableName)
        ? "SELECT COUNT(*) FROM pg_class WHERE relname = {0} AND relkind = 'r'".SqlFmt(tableName)
        : "SELECT COUNT(*) FROM pg_class WHERE lower(relname) = {0} AND relkind = 'r'".SqlFmt(tableName.ToLower());


    var conn = dbCmd.Connection;
    if (conn != null)
    {
        var builder = new NpgsqlConnectionStringBuilder(conn.ConnectionString);
        if (schema == null)
            schema = builder.SearchPath;
        if (schema == null)
            schema = "public";
        
        // If a search path (schema) is specified, and there is only one, then assume the CREATE TABLE directive should apply to that schema.
        if (!string.IsNullOrEmpty(schema) && !schema.Contains(","))
        {
            sql = !Normalize || ReservedWords.Contains(schema)
                ? "SELECT COUNT(*) FROM pg_class JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace WHERE relname = {0} AND relkind = 'r' AND nspname = {1}".SqlFmt(tableName, schema)

This file has been truncated. show original

For materialized views you’d instead search for relkind = 'm' instead, in most cases (when not using a schema) this should suffice:

var sql = "SELECT COUNT(*) FROM pg_class WHERE lower(relname) = {0} AND relkind = 'm'".SqlFmt(tableName.ToLower());
var exists = dbCmd.ExecLongScalar(sql) > 0;