postgresql materialized views missing

[mjsottile]

I am having trouble using the current version of the SQLProvider from github with a PostgreSQL database that uses materialized views. Tables and normal views show up fine, but the materialized views do not show up at all. Is this a known issue?

[pezipink]

I don't even know what a materialized view is! @janno-p ?

If you look at the source however you can see the query against INFORMATION_SCHEMA that we currently use to retrieve the tables / views.

[colinbull]

This is true across all of the providers currently, shouldn't be that hard to add thou.

-----Original Message----- From: "Ross McKinlay" notifications@github.com Sent: ‎22/‎05/‎2015 08:09 To: "fsprojects/SQLProvider" SQLProvider@noreply.github.com Subject: Re: [SQLProvider] postgresql materialized views missing (#136)

I don't even know what a materialized view is! @janno-p ? If you look at the source however you can see the query against INFORMATION_SCHEMA that we currently use to retrieve the tables / views. — Reply to this email directly or view it on GitHub.

[pezipink]

marked this as up for grabs then, might be a nice one for someone to pickup?

[s952163]

It seems this is still up for grabs... there are two advantage to materialized views, as they are stored on the disk, 1. it's extremely fast to query, just like a table. I benchmarked a simple three column group by query, it's 500ms (View) vs 0.1ms (Materialized View). And 2., since sqlprovider doesn't (yet) do groupvalby, any groupby or other missing query functionality can be implemented server side, and then just queried from a materialized view. Just bumping it in the hope someone tackles this.

[Thorium]

With Oracle someone has fixed similar issue: #345

[Thorium]

Searching a bit more this, it's not so easy task. Postgres materialized views don't belong to standard SQL information_schema.

After Postgres version 9.3 the views are listed in pg_matviews so they could be fetched from there. I tried to combine those with tables:

SELECT  table_schema,
        table_name,
        table_type
FROM  information_schema.tables
WHERE  table_schema = '%s'
UNION ALL
SELECT  schemaname as table_schema,
        matviewname as table_name,
        'M_VIEW' as table_type
FROM  pg_matviews
WHERE  schemaname = '%s'

But the next problem is that then the column-lookups (and relationship-lookups) for those fails as they are neither in information_schema.columns. So GetColumns should be rewritten also.

[s952163]

Thanks for looking into this! I appreciate the difficulty of this.

[OnofreTZK]

There is any update on this issue? I'm having this problem:

 error FS0039: The type 'my_schemaSchema' does not define the field, constructor or member 'MyMaterializedView'

[Thorium]

I'm not actively using Postgres, but I will accept PRs.

[Thorium]

As a workaround, could you create a view / sp that uses the materialised view and query that with SQLProvider?

[JordanMarr]

FYI, the two queries you will need to implement materialized views were recently implemented in latest version of SqlHydra here: https://github.com/JordanMarr/SqlHydra/blob/main/src/SqlHydra.Cli/Npgsql/NpgsqlSchemaProvider.fs

Feel free to reuse the queries for SQLProvider.