Kathi, if feature chaining does not work correctly with one database when chaining between two schemas, that sounds to me like a bug in app-schema joining. I am not aware of anyone using this use-case, nor of any test coverage.
While there is significant use of schemas in the reference data set used for automated testing, as far as I know, the reference data set configuration uses feature chaining mappings of views and tables that are all in one schema. You could also do this: use views in your public schema based on views in another schema, and have all your feature chaining based ion tables and views in the public schema. This will work with app-schema. Kind regards, Ben. On 26/08/16 23:29, Kathi Schleidt wrote: > Hi Ben, > > Tried with a second schema, but it seems to forget the schema when joining. > > As long as I run it without chaining the features (so removing the > feature chaining entry from the main mapping file) it works fine, > accessing the table testmain from the schema public for the main > feature, table next from the schema public 2 for the nested feature. > When I put them together (uncomment the chaining bit from the main > mapping), I get an error that it can't find the table testmain. > > I then tried playing with it's brain a bit, added a table testmain to > the schema public2. While I no longer get an exception, the nested data > isn't provided. When I set app-schema.joining = false it all works > correctly (but as you nicely illustrated, I don't want to think of the > performance loss with large datasets) > > Will see how far I can get with one DB (prefer xlinks anyway!) > > And once more thanks! > > :) > > Kathi > > > On 26.08.2016 00:31, Ben Caradoc-Davies wrote: >> Kathi, >> >> I strongly recommend keeping all tables in the same database so that >> you can use joining. The performance improvement becomes very large >> once you have more than a small number features: you might need 1 SQL >> query for 10000 features with joining, but 10001 SQL queries without >> joining, because a new SQL query is required for each chained >> relationship. With more levels of nesting, the problem becomes even >> worse. I have seen queries that take hours without joining reduced to >> a few minutes with joining. >> >> If the number of tables becomes unmanageable, you might be able to use >> different postgres schemas in the same way you were using different >> databases. These should work with joining (but I have not tested them >> myself). >> >> Other important performance considerations are to avoid CQL >> expressions in any property used for chaining or filter queries, and >> to index all these columns in the database. For example, I like to >> create gml_id columns in the database and index them to improve >> performance; postgres can do this efficiently but app-schema cannot >> convert a CQL expression with strconcat back to an SQL query, even >> with joining. >> >> Kind regards, >> Ben. >> >> On 26/08/16 02:50, Kathi Schleidt wrote: >>> Ben, >>> >>> MANY MANY THANKS!!!!! >>> >>> I've been trawling through the documentation, but hadn't found that bit >>> yet (still a lot to learn!) >>> >>> Now to get a feeling for my options based on performance, i.e. is it >>> better to pack the data from all themes (working on INSPIRE stuff here) >>> into 1 DB to enable joining across themes or saner to disaggregate and >>> forgo the joining benefits >>> >>> thanks for the prompt response! >>> >>> :) >>> >>> Kathi >>> >>> >>> On 24.08.2016 23:47, Ben Caradoc-Davies wrote: >>>> Kathi, >>>> >>>> thank you for your detailed report. I had a look through your mapping >>>> files and they look fine. Your testing proves that they work in a >>>> single database. >>>> >>>> For performance, app-schema converts queries against feature-chained >>>> types from database sources into efficient SQL, referred to as >>>> "joining": >>>> http://docs.geoserver.org/latest/en/user/data/app-schema/joining.html >>>> >>>> Joining cannot be used for feature chaining between databases because >>>> an SQL query can only be made against one database at a time. The >>>> evidence implicating joining is the mention in the stack trace of >>>> JoiningNestedAttributeMapping; this class is only used in app-schema >>>> joining. >>>> >>>> Joining is turned on by default. It is automatically turned off when >>>> using non-database sources, but I do not think that app-schema checks >>>> whether chaining is applied between database sources. As noted in the >>>> manual: >>>> >>>> "All feature mappings that are chained to each other must map to the >>>> same physical database." >>>> >>>> and >>>> >>>> "Failing to comply with any of these three restrictions when turning >>>> on Joining will result in exceptions thrown at run-time." >>>> >>>> To turn off app-schema chaining, set "app-schema.joining=false" in >>>> your app-schema.properties or as a JVM argument: >>>> http://docs.geoserver.org/latest/en/user/data/app-schema/joining.html#configuration >>>> >>>> >>>> >>>> Kind regards, >>>> Ben. >>>> >>>> On 24/08/16 22:06, Kathi Schleidt wrote: >>>>> Hi, >>>>> >>>>> In the process of trying to link features from multiple INSPIRE Themes >>>>> via Feature Chaining, I’ve run into difficulties using multiple >>>>> (well 2) >>>>> databases (was trying to do one database per feature >>>>> type/namespace). To >>>>> clarify that it’s not a problem with my configuration I put together a >>>>> simple example consisting of a main feature with two nested features, >>>>> one of which should stem from a different DB than the main parts. >>>>> (schema available at http://www.schleidt.org/schemas/test.xsd) >>>>> >>>>> Within the main DB (test2, DataStore idDataStoreTS), there are 2 >>>>> tables: >>>>> >>>>> * testmain: the main part >>>>> >>>>> * nest: the part that should actually be in the other database >>>>> >>>>> Within the 2^nd DB (nest2, DataStore idDataStoreNS), there is only the >>>>> nest table (identical to the one in test, just different content to >>>>> see >>>>> where the data is coming from) >>>>> >>>>> The DataStore configuration for both databases is identical except for >>>>> the database name (test2 vs nest2) >>>>> >>>>> As long as I’m only using the main database (test2), everything works >>>>> out beautifully. The moment I try to use the 2^nd database connection >>>>> (nest2), while there are no errors on startup, there are also no >>>>> features, just the following error (for ts:nestExt it should be going >>>>> for the table nest in the db nest2, but it’s complaining about the >>>>> table >>>>> testmain from the db test2): >>>>> >>>>> java.lang.RuntimeException: Error applying mapping with >>>>> targetAttribute >>>>> ts:nestExt >>>>> >>>>> Error applying mapping with targetAttribute ts:nestExt >>>>> >>>>> java.io.IOExceptionSchema 'testmain' does not exist. >>>>> >>>>> An interesting aside – when I set up this test, I first forgot to >>>>> set up >>>>> my db as postgis (I’m an old geek, and spatial is still new to me). >>>>> This >>>>> worked fine as long as I was only using one DB (there are no >>>>> geometries >>>>> in my simple example), when I added the 2^nd one I got an interesting >>>>> PostGis error (might help in debugging): >>>>> >>>>> Function postgis_lib_version() doesn’t exist >>>>> >>>>> >>>>> Configuration and logfiles are available at: >>>>> http://www.schleidt.org/kathi/DBProblem.zip >>>>> >>>>> Main problem in the subdirectory PostGisDB, the 2 variants with just >>>>> PostGres are PostGres1 & 2 (for 1 or 2 dbs) >>>>> >>>>> Can somebody tell me if I'm going crazy or if there's a problem here? >>>>> >>>>> :) >>>>> >>>>> Kathi >>>>> >>>>> >>>>> >>>>> ------------------------------------------------------------------------------ >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> _______________________________________________ >>>>> Geoserver-users mailing list >>>>> Geoserver-users@lists.sourceforge.net >>>>> https://lists.sourceforge.net/lists/listinfo/geoserver-users >>>>> >>>> >>> >> > -- Ben Caradoc-Davies <b...@transient.nz> Director Transient Software Limited <http://transient.nz/> New Zealand ------------------------------------------------------------------------------ _______________________________________________ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
