MATERIALIZED VIEWs not shown when retrieving schemas about pgjdbc

[davecramer]

Here is the code in question:

if (types != null) {
select += " AND (false ";
for (int i = 0; i < types.length; i++)
{
Map clauses = (Map)tableTypeClauses.get(types[i]);
if (clauses != null)
{
String clause = (String)clauses.get(useSchemas);
select += " OR ( " + clause + " ) ";
}
}
select += ") ";
}

Mondrian must not be asking for MATERIALIZED_VIEW

It does appear to be asking for TEMPORARY_TABLE and TEMPORARY_INDEX though

Dave Cramer

On 4 July 2014 05:15, ratio1 [email protected] wrote:

I am running a Mondrian OLAP Server in combination with PostgreSQL 9.3,
using postgresql-9.3-1101.jdbc4.jar. Mondrian cannot retrieve informations
about existing MATIERLIAZED VIEWs. It seems to be pgjdbc is not working
correctly. I checked my PostgreSQL logs and found following query, assuming
that this statement is part of pgjdbc:

SELECT
NULL AS TABLE_CAT,
n.nspname AS TABLE_SCHEM,
c.relname AS TABLE_NAME,
CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'
WHEN true THEN
CASE
WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN
CASE c.relkind
WHEN 'r' THEN 'SYSTEM TABLE'
WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX'
ELSE NULL
END
WHEN n.nspname = 'pg_toast' THEN
CASE c.relkind
WHEN 'r' THEN 'SYSTEM TOAST TABLE'
WHEN 'i' THEN 'SYSTEM TOAST INDEX'
ELSE NULL
END
ELSE
CASE c.relkind
WHEN 'r' THEN 'TEMPORARY TABLE'
WHEN 'i' THEN 'TEMPORARY INDEX'
WHEN 'S' THEN 'TEMPORARY SEQUENCE'
WHEN 'v' THEN 'TEMPORARY VIEW'
ELSE NULL
END
END
WHEN false THEN
CASE c.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'i' THEN 'INDEX'
WHEN 'S' THEN 'SEQUENCE'
WHEN 'v' THEN 'VIEW'
WHEN 'c' THEN 'TYPE'
WHEN 'f' THEN 'FOREIGN TABLE'
WHEN 'm' THEN 'MATERIALIZED VIEW'
ELSE NULL
END
ELSE NULL
END
AS TABLE_TYPE,
d.description AS REMARKS
FROM
pg_catalog.pg_namespace n, pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')
LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')
WHERE
c.relnamespace = n.oid
AND c.relname LIKE '%'
AND
(
false
OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' )
OR ( c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' )
)
ORDER BY
TABLE_TYPE,
TABLE_SCHEM,
TABLE_NAME

The causing problem is the last WHERE condition, limiting the result set
to tables and (regular) views only. Here is the correct Statement,
including materialized views into the result set:

SELECT
NULL AS TABLE_CAT,
n.nspname AS TABLE_SCHEM,
c.relname AS TABLE_NAME,
CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'
WHEN true THEN
CASE
WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN
CASE c.relkind
WHEN 'r' THEN 'SYSTEM TABLE'
WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX'
ELSE NULL
END
WHEN n.nspname = 'pg_toast' THEN
CASE c.relkind
WHEN 'r' THEN 'SYSTEM TOAST TABLE'
WHEN 'i' THEN 'SYSTEM TOAST INDEX'
ELSE NULL
END
ELSE
CASE c.relkind
WHEN 'r' THEN 'TEMPORARY TABLE'
WHEN 'i' THEN 'TEMPORARY INDEX'
WHEN 'S' THEN 'TEMPORARY SEQUENCE'
WHEN 'v' THEN 'TEMPORARY VIEW'
ELSE NULL
END
END
WHEN false THEN
CASE c.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'i' THEN 'INDEX'
WHEN 'S' THEN 'SEQUENCE'
WHEN 'v' THEN 'VIEW'
WHEN 'c' THEN 'TYPE'
WHEN 'f' THEN 'FOREIGN TABLE'
WHEN 'm' THEN 'MATERIALIZED VIEW'
ELSE NULL
END
ELSE NULL
END
AS TABLE_TYPE,
d.description AS REMARKS,
c.relkind
FROM
pg_catalog.pg_namespace n, pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')
LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')
WHERE
c.relnamespace = n.oid
AND c.relname LIKE '%'
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND c.relkind IN ('r', 'v', 'm', 'f')
ORDER BY
TABLE_TYPE,
TABLE_SCHEM,
TABLE_NAME

Since I'm not familiar to git, could you please bring the code to
master/branches/patches? Or am I wrong and this code is produced by
Mondrian?

Thank you for your great work.

—
Reply to this email directly or view it on GitHub
#166.

[ratio1]

Hi Dave,

thank you for your reply. So the problem is at the mondrian side.

Closing issue.