Subscribe to this thread
Home - General / All posts - Problems with PostgreSQL views
marcus17 post(s)
#23-Nov-21 19:33

Manifold 9.

I have a PostgreSQL database with several views and materialized views (some spatial, some not). I am experiencing two problems:

1. For the spatial materialized views the drawing does not display - it has red exclamation mark. There is no index on the geom recognized even though it is indexed in the database.

2. For any materialized view the underling query does not run (nothing happens after clicking the run exclamation mark). This is different from views where the query does run.

Any help would be appreciated.

Thanks

joebocop
484 post(s)
#23-Nov-21 21:17

Hi,

Does your mat view specify geom type and SRID with a cast like

ST_SetSRID(ST_Point(easting,northing),3578)::Geometry(Point,3578)

I've had good luck with postgresql views exposing Drawings in 9, so long as they are explicit about their geoms.

marcus17 post(s)
#24-Nov-21 11:31

Hi

The materialized views don't cast or set the SRID as the geometries already exist in the source tables and have the SRID set. The geometries aren't generated by the view just joined.

Dimitri


6,852 post(s)
#24-Nov-21 06:49

1. For the spatial materialized views the drawing does not display - it has red exclamation mark.

The "red exclamation mark" advises you of a message you should read. What happens when you choose View - Messages to read what the red exclamation mark advises?

2. For any materialized view the underling query does not run (nothing happens after clicking the run exclamation mark). This is different from views where the query does run.

You run a query by running the query, not by "running" a result. Have you tried refreshing the drawing that's generated from the query?

marcus17 post(s)
#24-Nov-21 11:27

Hi

The message says the drawing table has no spatial index; but it does have a spatial index in the database.

OK, I see what you mean about "running" the result. Refresh doesn't help.

So the issue appears to be that the index isn't being picked up for some reason.

Sloots

549 post(s)
#24-Nov-21 11:30

Is it an index created with GIST?


http://www.mppng.nl/manifold/pointlabeler

marcus17 post(s)
#24-Nov-21 11:34

Yep; a gist index.

Dimitri


6,852 post(s)
#24-Nov-21 13:17

it does have a spatial index in the database.

Well, not just any spatial index will do. There has to be a spatial index on the geometry field that's being displayed by the drawing.

Within the PostgreSQL data source, right-click on the drawing and choose Properties. The FieldGeom property will tell you which field is providing the geometry that is being visualized in the drawing.

Next, right-click on the drawing's table and choose Schema. That will show you if there is a spatial index on the field cited in the FieldGeom property.

Attachments:
postgres_drawing_properties.png
postgres_table_schema.png

marcus17 post(s)
#24-Nov-21 13:34

Hi

There's only one geometry column in this materialized view, called geom. The drawing shows that it is using geom.

However, the schema of the table is empty.

See attached images.

Attachments:
2021-11-24_13-20-45.png
2021-11-24_13-25-02.png

marcus17 post(s)
#24-Nov-21 13:46

Just to confirm that there's nothing wrong with the materialized view, here it is added to QGIS.

Attachments:
2021-11-24_13-42-51.png

yves61
320 post(s)
#24-Nov-21 16:35

-- deleted. sorry.

tjhb

9,886 post(s)
#24-Nov-21 17:03

The materialized views [...] The geometries aren't generated by the view just joined.

Is this likely to be the problem?

Indexes on the source tables may not survive the join(s).

Within Manifold 9, they often don't (can't), without careful handling.

Maybe you could post the query (or queries) that generate the materialized view(s)?

marcus17 post(s)
#24-Nov-21 18:12

Thanks for suggestion. However, the index has been created for the geom column within the materialized view. See screenshots from QGIS and Navicat attached.

The query for the table I've been posting about is complex. However, the problem is identical for even simple ones. This is an example:

create MATERIALIZED view missing_countpoints as

select * from countpoints

where count_point_id not in

(select link_id from major_road_links)

and road_type = 'major';

And this is creating the index:

CREATE INDEX missing_countpoints_geom_idx

ON missing_countpoints

USING GIST (geom);

Issue is identical.

Attachments:
2021-11-24_17-52-56.png
2021-11-24_17-56-01.png

marcus17 post(s)
#24-Nov-21 18:29

Also, tables from materialized views with no geometry cannot be opened either. However, tables from views can be (by running the underlying query).

tjhb

9,886 post(s)
#24-Nov-21 19:42

Thanks, geom index noted.

But won't you be wanting to add a unique index on the records in the materialized view, as well?

I am not a postgreSQL user (to my shame!), but for Manifold to parse the table correctly, won't it need a key field with a unique index?

marcus17 post(s)
#24-Nov-21 20:06

Yes, there is also a unique index on the ID field.

tjhb

9,886 post(s)
#24-Nov-21 21:06

I am sure you are right, but note that your example does not use a join.

marcus17 post(s)
#24-Nov-21 22:00

That is just a very basic materialized view, not the one I was originally talking about. Issue is that none of them work in Manifold.

marcus17 post(s)
#24-Nov-21 22:21

I have carried out further tests using an old server running PostgreSQL 9.5. Materialized view created in exactly the same way as on my current PostgreSQL server (v13.4). On the 9.5 version it worked perfectly.

This suggests some issue with more recent versions of PostgreSQL and Manifold 9. Please also see my other thread about an error message in the log when creating the data source for the v13.4 server. Could it be related to that (Manifold seems to be trying to query a table column that hasn't existing in PostgreSQL since v12). Not sure I can proceed any further in trying to track it down as this would appear to be a Manifold bug.

marcus17 post(s)
#24-Nov-21 22:54

I thought the log error appeared on creating the data source. On further investigations it occurs when opening the materialized view drawing:

2021-11-24 22:50:01 *** (root)::[Data Source] ERROR: column pad.adsrc does not exist

2021-11-24 22:50:01 *** (root)::[Data Source] LINE 1: ...tttypmod) as data_type, a.attname as column_name, pad.adsrc ...

2021-11-24 22:50:01 *** (root)::[Data Source] ^

2021-11-24 22:50:02 Render: [Data Source]::[traffic.aadf_roads.geom] (0.254 sec)

Error is repeated when opening any materialized view drawing.

"Remove obsolete pg_attrdef.adsrc column (Peter Eisentraut)" was implemented in version 12. See:

https://www.postgresql.org/docs/12/release-12.html

Useful discussion here:

https://stackoverflow.com/questions/58789024/postgres-error-column-d-adsrc-does-not-exist

marcus17 post(s)
#24-Nov-21 23:47

OK, in a test PostgreSQL v13.5 database I've edited the system catalog pg_attrdef and added a column adsrc text with null values. Manifold then works correctly with materialized views.

So this is the confirmed cause of the problem. Manifold is trying to retrieve column pg_attrdef.adsrc which does not exist in PostgreSQL versions from v12.

This should be an easy fix. I guess I should send a bug report?

tjhb

9,886 post(s)
#25-Nov-21 00:53

Well done detective. I hope your bug report will get the very best ears.

Dimitri


6,852 post(s)
#25-Nov-21 06:24

There's a new build imminent, so I've taken the liberty of sending in a possible bug report based on what I've read in this thread. Manifold has often done tweaks in one day, so why not go for it?

That's not as good as a bug report from somebody actually working with PostgreSQL 13 (I still use 11) sending in full info, but the sooner the better for these things. See, for example, https://georeference.org/forum/t147098.3#147104

Don't forget to include all details, like which DLL you're using for the PostgreSQL client, the queries used, etc. All that stuff is important because Manifold handles materialized views in a more sophisticated way than simpler packages. See, for example, the discussion in threads like https://georeference.org/forum/t142625#142678 and https://georeference.org/forum/t157665.10#157741

Dimitri


6,852 post(s)
#27-Nov-21 14:39

Fixed in build 175.6, issued today.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.