[mapguide-users] Help required following up on a FDO PostgreSQL provider bug

[Ari Hyvönen]

Hi,

I think that error occurs only when you are viewing data as layer. Here's
steps for replicating that.

1. Start with empty database with postgis extension created.
2. Create normal view with this command:
CREATE VIEW public.v_test
 AS
 WITH point_series AS (
         SELECT generate_series('-20'::integer::numeric, 20::numeric, 0.5)
AS point_values
        )
 SELECT row_number() OVER () AS id,
    st_setsrid(st_point(point_values::double precision,
point_values::double precision), 4326) AS geom
   FROM point_series a;

3. Create database connection in Mapguide and layer from view above and and
that to Map.
4. Browse Map and everything is fine.
5. Create materialized view to database. It should work with previous
examples too but you can also create materialized view with geometry column
in it.
CREATE MATERIALIZED VIEW public.mv_test2
AS
 WITH point_series AS (
         SELECT generate_series('-20'::integer::numeric, 20::numeric, 0.5)
AS point_values
        )
 SELECT row_number() OVER () AS id,
    st_setsrid(st_point(point_values::double precision,
point_values::double precision), 4326) AS geom
   FROM point_series a;
6. Refresh database datasource in Mapguide so it will get info about new
materialized view.
7. Browse Map for awhile. Layer will be lost and visible again and error
log will have rows showing error  Class 'public:v_test' not found
8. Delete materialized view from database, refresh datasource and layer
will work again when Browsing map.

If you run query shown in ticket with correct parameters it will return
only table or normal view with same schema and name as in parameters and
all materialized views. If you add these conditions to where part of second
select after union query will return only wanted rows. Values should be the
same as placed to first select.

AND ns.nspname='public' AND c.relname='v_test'


SELECT  t.table_schema  || '.' || t.table_name AS name,
 lower(t.table_type) AS type,  convert_to(t.table_schema,'UTF8') as
collate_schema_name,  convert_to(t.table_name,'UTF8') as collate_table_name

FROM information_schema.tables AS t
WHERE t.table_schema not in ( 'information_schema' ,'pg_catalog')  and  (
(t.table_schema = 'public' and t.table_name = 'v_test') ) AND t.table_type
IN ('BASE TABLE','VIEW')  AND t.table_name not in ( 'geometry_columns',
'geography_columns','spatial_ref_sys', 'raster_columns', 'raster_overviews')
UNION
SELECT ns.nspname || '.' || c.relname AS name,  'view' AS type,
 convert_to(ns.nspname,'UTF8') AS collate_schema_name,
 convert_to(c.relname,'UTF8') AS collate_table_name
FROM pg_class AS c  JOIN pg_namespace ns ON c.relnamespace = ns.oid
WHERE c.relkind = 'm' AND ns.nspname='public' AND c.relname='v_test'
ORDER BY collate_schema_name, collate_table_name ASC;

Br, Ari


pe 18.7.2025 klo 15.22 Jackie Ng via mapguide-users (
mapguide-users at lists.osgeo.org) kirjoitti:

> In the interest of maximum transparency as to what I'm doing, this is my
> unit test.
>
> I run these SQL statements on an empty database:
>
> CREATE TABLE vec (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
> CREATE VIEW vec_view AS SELECT * FROM vec;
> CREATE MATERIALIZED VIEW hypot AS SELECT id, sqrt(a*a + b*b) as c FROM vec;
> CREATE MATERIALIZED VIEW squared AS SELECT id, pow(a, 2) as c, pow(b, 2)
> as d FROM vec;
> CREATE SCHEMA Foo;
> CREATE TABLE Foo.vec2 (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
> CREATE VIEW Foo.vec_view2 AS SELECT * FROM Foo.vec2;
> CREATE MATERIALIZED VIEW Foo.hypot2 AS SELECT id, sqrt(a*a + b*b) as c
> FROM Foo.vec2;
>
> This results in a database with the following schema/tables/views
>
>    - public
>       - vec [table]
>       - vec_view [view]
>       - hypot [mat view]
>       - squared [mat view]
>    - Foo
>       - vec2 [table]
>       - vec_view2 [view]
>       - hypot2 [mat view]
>
> I then proceed to run the following tests:
>
> 1. Describe the full schema. I expect each schema to be present and each
> table/view/matview under it is a class definition. Each column is a data
> property on their respective class.
>
> Expectations: correct
>
> 2. Describe a partial schema with a schema hint of "public" and a class
> name hint of "hypot". I expect it to contain only one schema (public) with
> one class (hypot). Each column from the hypot view is present in the class
> as a data property.
>
> Expectations: correct
>
> What am I possibly missing here? No class definitions are being misplaced
> in other schemas.
>
> - Jackie
>
>
>
> You wrote:
>
> Hi,
>
> Creating these two will trigger issue. Materialized views can be in public
> schema.
>
> Create materialized view mv_test1 as select 1 as foo;
>
> Create materialized view mv_test2 as select 2 as foo;
>
> Br, Ari
> _______________________________________________
> mapguide-users mailing list
> mapguide-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapguide-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapguide-users/attachments/20250718/d24dcb7f/attachment.htm>