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

[Jackie Ng]

Thank you for the more precise database example. I'll see what I can find
out with this one.

- Jackie

You wrote:

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapguide-users/attachments/20250721/64cdbe81/attachment.htm>