search

geosolutions-it / geoserver

Official GeoServer repository
http://geoserver.org
Other
10 stars 7 forks source link

GeoServer cannot show PARTITIONED table in PostGIS #214

Closed chpicone closed 3 years ago

chpicone commented 3 years ago

If we have a (parent) partitioned table in postGIS (example TAB_A) we can see just the child tables (eg TAB_A_1, TAB_A_2, etc).

Tested on GeoServer 2.18.0.

Here the JIRA ticket

simboss commented 3 years ago

We need to perform an investigation for implementing this since apparently this is needed to simulate subtypes as they exist in the ESRI world. @chpicone can you explain further?

simboss commented 3 years ago

@aaime I just need an estimate and an implementation plan.

aaime commented 3 years ago

Subtypes? Hum... GeoTools would understand a partitioned table as a normal table and deliver just a single type out of it. If we are dealing with inheritance, then it seems to me we are talking about app-schema, and the rest of the response does not make sense.

I've seen usage of partitioned tables in a different context, as a way to speed up queries, if the filter manages to exclude entire partitions and possibly match only one or a handful. If that's the case, and PostgreSQL does not need any special way to query the partitioned table, then it's a matter of:

Estimate wise, I'd guess between half a day and a day, depending on what experience the developers in charge has with the JDBCTestSupport/JDBCTestSetup testing framework.

simboss commented 3 years ago

@aaime can you liaise with @chpicone to make sure the scenario is clear as this is going to be part of next webinar I guess.

aaime commented 3 years ago

Chatted with @chpicone, the subtypes have the same structure as the parent table, but different check constraints to maintain integrity, something that would pop up only when using WFS-T (and even in that case, would not prevent it, it's just checks for valid values on insert). So the estimate I've made above is all we need.

simboss commented 3 years ago

@aaime thake into account that we would need to backport this to 2.17.x for this project as we are using GeoNode.

Do you think it would be acceptable?

aaime commented 3 years ago

Yes, the actual code change is a line, it's just the test will take some effort.

simboss commented 3 years ago

Being a fix, we would need this to go as back as possible, until 2.17 I'd say. This will have to be tested on GeoNode's GeoServer.

When yout get here, let me know.

aaime commented 3 years ago

Changes merged on master and backported on 24.x and 23.x. Will be available starting with the January releases, onwards.

simboss commented 3 years ago

@aaime is it possible to isolate the JARs involved in this fix?

@chpicone this is for you...

aaime commented 3 years ago

Yes, only gt-jdbc-postgis-<version>.jar has been affected. It can be picked from a GeoServer nightly build (starting tomorrow, but I can force the nightlies to run sooner if needs be).

simboss commented 3 years ago

@aaime is this fix part of 2.17.4? I think so, right?

aaime commented 3 years ago

No, the backport missed the release by a couple of days. As per my previous comment: "Changes merged on master and backported on 24.x and 23.x. Will be available starting with the January releases, onwards."

simboss commented 3 years ago

ouch, @chpicone did you read this message from @aaime ?

This means we have discuss a little on how to upgrade Krihs GeoNode (separately from this issue).

chpicone commented 3 years ago

Can we update just the gt-jdbc-postgis-.jar library?

aaime commented 3 years ago

I believe so, yes, it's the only one that has been changed, and I see no other changes after the partitioned table fix in gt-jdbc-postgis (nor in gt-jdbc). So it should work. We can only make sure testing it out.