Replace TRUNCATE with CLEAR TABLE #507
Assignees
Labels
Comments
|
Im sure there are many more. It's quite bizarre the outage happened after having written these harvesters years ago |
|
I'd say most harvesters use truncate since it was what we've been told to use in the original Talend Google document. Surely we could do a batch update, that's what I ended up doing for AATAMS_ACOUSTIC_REPORTING. |
|
The issue in production the other day was mainly caused by a jndi connection pool leak in geoserver performing shapefile downloads. The truncate just exacerbated the problem (blocked access to that table from any other application). I've attached a list of harvesters which still use the TRUNCATE table action and in sql below. We've actually been moving to modifying data based on what files have changed rather than wholesale truncate and repopulate so there aren't as many as you would think. |
|
This is causing problems for imos:anmn_nrs_bgc_plankton_zooplankton_data for which wfs requests regulary fail at 6:04pm when the harvester is running as per https://github.com/aodn/backlog/issues/836. Read requests must wait until changes containing a truncate are committed. If this takes a while wfs requests time out waiting. |
|
Can we do a bulk update to fix all the harvesters? |
|
We can do that, but developers don't really know what these harvesters are meant to do/how to verify that they are still working correctly after the changes which is why I think this was raised in the harvesters repo. Making the changes shouldn't be that hard. |
|
It doesn't look like we've made any progress with this. I won't dump the full grep from above, but these harvesters still include at least one TRUNCATE: Has this led to any more data availability issues recently? Do we need to prioritise this?
Not hard, just time-consuming, if we need to do it by clicking around in TOS. Of course changing "TRUNCATE" to "CLEAR" can be done with a simple search/replace on the relevant "process/*.item" files, but testing the updated harvester takes a bit more time. Maybe the harvester tests developed for the TOS7 upgrade could also help with this? |
|
Also we need to be careful doing just a simple grep for "TRUNCATE" and replacing it with "CLEAR". I just noticed that some of the harvesters above use "TRUNCATE TABLE x;" in SQL inside the liquibase script (e.g. ANMN_NRS_DAR_YON_TS and AUV). In these cases the replacement string should be "DELETE FROM x;" I guess? |
|
why changing what is not broken ? |
|
Note that this is broken. See #507 (comment) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
When a harvester needs to delete all rows from a table before writing new data to it, the "clear table" option should be used instead of "truncate table". Truncate requires an exclusive lock on the table and prevents any subsequent queries from running, even while the truncate itself is still waiting to run.
This may have been part of the cause for a recent geoserver-123 outage (https://github.com/aodn/issues/issues/29)
The text was updated successfully, but these errors were encountered: