Max experienced a bug on Monday 7/28. A renamed observation well, that had previously been tagged with a facility ID from the wrong SMP, was unable to be updated in the Add OW UI. The Teams message detailing the data anomaly is here.
This issue was exacerbated by the OW table storing hardcoded facility IDs and not foreign keys to an asset table. The data pipeline responsible for serving these facility IDs to the fieldwork app UI is old and complex, and was originally built around design constraints that no longer exist.
GIS attribute tables are harvested via the DataConv scrape script. These tables are unified into a materialized view called external.mat_assets. These tables were originally pulled in via live database connections to DataConv via an ODBC Foreign Data Wrapper, which we no longer use. The matview existed to speed up queries, which were otherwise laden with network latency.
mat_assets does not contain a field that uniquely identifies its rows. The constituent tables have primary keys of their own, but they are not unique across all such tables. In order for mat_assets to have a unique rowwise identifier suitable for use as a foreign key, the constituent tables (and their primary keys) would need to be regenerated with different parameters. An easy such parameter would be to use an old-style sequence-based primary key, and use the same sequence for each imported GIS table.
However, mat_assets does not necessarily need to be a materialized view at all. If it's just unifying tables we are already scraping, we could scrape them into a single unified table in the first place. However, this would be a much more complicated solution, as mat_assets is extensively used in other data structures in our DB and its associated software. The code and testing burden for such a change would be a major implementation, as opposed to the comparatively lighter foreign key rework.
To my mind, the pros and cons of the two implementations are as follows:
- Keep mat_assets, and regenerate external.tbl_gswi* tables to use a unified primary key
- Pro: Quicker and easier implementation
- Pro: Other software in our ecosystem need not be modified
- Con: The OW table's use of a matview for data integrity requires an awkward CHECK constraint, rather than a direct foreign key. This is how it currently works, and adds unnecessary complexity to the database.
- Replace mat_assets with a unified table into which GSWI assets are scraped
- Pro: Simplifies our data structures internally
- Pro: Allows for direct use of foreign keys in fieldwork.tbl_ow
- Con: Would require an audit of our entire software ecosystem to determine the reach of mat_assets, and solutions would need to be tested against all of it
Max's bug is the first time in almost 7 years of this data environment that this bug has been relevant, and it is unlikely to appear in increased frequency. This bug slightly increases the complexity of changing SMP IDs for defined observation wells in the database backend. To that end, I believe that implementing fix number 1, the unified primary key, is the appropriate solution for now.
I believe that solution 2 can be revisited at another time, perhaps post-EAP15, when time allows for a larger-scope database project.
Max experienced a bug on Monday 7/28. A renamed observation well, that had previously been tagged with a facility ID from the wrong SMP, was unable to be updated in the Add OW UI. The Teams message detailing the data anomaly is here.
This issue was exacerbated by the OW table storing hardcoded facility IDs and not foreign keys to an asset table. The data pipeline responsible for serving these facility IDs to the fieldwork app UI is old and complex, and was originally built around design constraints that no longer exist.
GIS attribute tables are harvested via the DataConv scrape script. These tables are unified into a materialized view called external.mat_assets. These tables were originally pulled in via live database connections to DataConv via an ODBC Foreign Data Wrapper, which we no longer use. The matview existed to speed up queries, which were otherwise laden with network latency.
mat_assets does not contain a field that uniquely identifies its rows. The constituent tables have primary keys of their own, but they are not unique across all such tables. In order for mat_assets to have a unique rowwise identifier suitable for use as a foreign key, the constituent tables (and their primary keys) would need to be regenerated with different parameters. An easy such parameter would be to use an old-style sequence-based primary key, and use the same sequence for each imported GIS table.
However, mat_assets does not necessarily need to be a materialized view at all. If it's just unifying tables we are already scraping, we could scrape them into a single unified table in the first place. However, this would be a much more complicated solution, as mat_assets is extensively used in other data structures in our DB and its associated software. The code and testing burden for such a change would be a major implementation, as opposed to the comparatively lighter foreign key rework.
To my mind, the pros and cons of the two implementations are as follows:
Max's bug is the first time in almost 7 years of this data environment that this bug has been relevant, and it is unlikely to appear in increased frequency. This bug slightly increases the complexity of changing SMP IDs for defined observation wells in the database backend. To that end, I believe that implementing fix number 1, the unified primary key, is the appropriate solution for now.
I believe that solution 2 can be revisited at another time, perhaps post-EAP15, when time allows for a larger-scope database project.