Dependency Failures with Option SQL in xdat.log

[Cory Johnson]

Our xdat.log included "Optional SQL" commands that I attempted to execute. I attached the SQL script.

For two of the tables (xnat_resource and  xnat_mrscandata), where the SQL updates are essentially changing the data type of a column, there are failures because there is a table that depends on the column that is dropped before being re-added as a different data type.

Here are the two failures:

----Fix xnat_resource table.
ALTER TABLE xnat_resource ADD COLUMN uri_cp varchar;
ALTER TABLE
UPDATE xnat_resource SET uri_cp=uri;
UPDATE 435058
ALTER TABLE xnat_resource DROP COLUMN uri;
psql:/srv/dumps/option_sql_commands.sql:52: ERROR:  cannot drop column uri of table xnat_resource because other objects depend on it
DETAIL:  view displayfields_xnat_resourcecatalog depends on column uri of table xnat_resource
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
ALTER TABLE xnat_resource ADD COLUMN uri TEXT;
psql:/srv/dumps/option_sql_commands.sql:53: ERROR:  column "uri" of relation "xnat_resource" already exists
UPDATE xnat_resource SET uri=CAST(uri_cp AS TEXT);
UPDATE 435058

----Fix xnat_mrscandata table.
ALTER TABLE xnat_mrscandata ADD COLUMN parameters_flip_cp integer;
ALTER TABLE
UPDATE xnat_mrscandata SET parameters_flip_cp=parameters_flip;
UPDATE 102165
ALTER TABLE xnat_mrscandata DROP COLUMN parameters_flip;
psql:/srv/dumps/option_sql_commands.sql:71: ERROR:  cannot drop column parameters_flip of table xnat_mrscandata because other objects depend on it
DETAIL:  view displayfields_xnat_mrscandata depends on column parameters_flip of table xnat_mrscandata
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
ALTER TABLE xnat_mrscandata ADD COLUMN parameters_flip FLOAT;
psql:/srv/dumps/option_sql_commands.sql:72: ERROR:  column "parameters_flip" of relation "xnat_mrscandata" already exists
UPDATE xnat_mrscandata SET parameters_flip=CAST(parameters_flip_cp AS FLOAT);
UPDATE 102165

Cascading the drop is not the right choice.

I could temporarily drop the views and readd them after the column data type changes have completed.

How important is performing the optional SQL commands? 

[Rick Herrick]

Hi Cory,

 

I have two comments on this. The first is that XNAT actually regenerates those particular views (i.e., displayfields_*) on start-up if they don’t exist. This means you could actually just drop all of them, make the changes suggested in xdat.log, then re-start Tomcat, and those views will be restored automatically. A nice script for this is something like:

 

psql --username=xnat --tuples-only --no-align --command="select table_name from information_schema.tables where table_name ~ '^displayfields_.*'" | while read VIEW; do

    psql --username=xnat --command="DROP VIEW ${VIEW}"

done

 

The second thing is that XNAT provides some functions in its database schema that you can use to save and drop tables dependencies (and later restore those dependencies). There are three functions that are useful here:

 

• dependencies_identify()
• dependencies_restore()
• dependencies_save_and_drop()

 

The first one just tells you which other tables/views have dependencies on another particular table/view, e.g.:

 

xnat=> SELECT * FROM dependencies_identify('xnat_resource');

view_schema |             view_name              | view_type

-------------+------------------------------------+-----------

public      | displayfields_xnat_resourcecatalog | v

(1 row)

 

This is what the error message told you:

 

DETAIL: view displayfields_xnat_mrscandata depends on column parameters_flip of table xnat_mrscandata

 

Use dependencies_save_and_drop() to drop any dependencies:

 

xnat=> SELECT * FROM dependencies_save_and_drop('xnat_resource');

dependencies_save_and_drop

----------------------------

                          1

(1 row)

 

Now make your changes from the suggested SQL. You should be able to make these changes without running into the dependency issue because the dependencies are now gone. Once you’ve finished your changes, just restore the deleted dependencies with dependencies_save_and_drop():

 

xnat=> SELECT * FROM dependencies_restore('xnat_resource');

dependencies_restore

----------------------

                    1

(1 row)

 

If you re-run dependencies_identify() now, you’ll see that you’re back to where you were.

 

--
You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xnat_discussi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xnat_discussion/a8e97138-c15b-4c36-8ef8-a68af2da6462n%40googlegroups.com.

[Cory Johnson]

Thank you Rick! You are an xnat God!

I have executed the optional SQL commands. The following are the only remaining errors in the xdat log. Is the log suggesting to execute those statements?

2024-07-25 13:52:03,178 [main] ERROR org.nrg.xft.db.DBAction - Preparing to convert table 'xhbm_custom_variable_form_applies_to' to use sequence for default value.
2024-07-25 13:52:03,179 [main] ERROR org.nrg.xft.db.DBAction - Queries prepared for conversion:
2024-07-25 13:52:03,179 [main] ERROR org.nrg.xft.db.DBAction -  *** CREATE SEQUENCE xhbm_custom_variable_form_applies_to_id_seq
2024-07-25 13:52:03,179 [main] ERROR org.nrg.xft.db.DBAction -  *** ALTER TABLE xhbm_custom_variable_form_applies_to ALTER COLUMN id SET DEFAULT nextval('xhbm_custom_variable_form_applies_to_id_seq')
2024-07-25 13:52:03,179 [main] ERROR org.nrg.xft.db.DBAction -  *** ALTER TABLE xhbm_custom_variable_form_applies_to ALTER COLUMN id SET NOT NULL
2024-07-25 13:52:03,179 [main] ERROR org.nrg.xft.db.DBAction -  *** ALTER SEQUENCE xhbm_custom_variable_form_applies_to_id_seq OWNED BY xhbm_custom_variable_form_applies_to.id
2024-07-25 13:52:03,185 [main] ERROR org.nrg.xft.db.DBAction - Ran the query 'SELECT (MAX(id) + 1) AS value FROM xhbm_custom_variable_form_applies_to' and got the value 1. Now preparing to run the query: SELECT setval('xhbm_custom_variable_form_applies_to_id_seq', 1) AS value
2024-07-25 13:52:03,186 [main] ERROR org.nrg.xft.db.DBAction - Preparing to convert table 'xhbm_file_store_info_aud' to use sequence for default value.
2024-07-25 13:52:03,186 [main] ERROR org.nrg.xft.db.DBAction - Queries prepared for conversion:
2024-07-25 13:52:03,186 [main] ERROR org.nrg.xft.db.DBAction -  *** CREATE SEQUENCE xhbm_file_store_info_aud_id_seq
2024-07-25 13:52:03,186 [main] ERROR org.nrg.xft.db.DBAction -  *** ALTER TABLE xhbm_file_store_info_aud ALTER COLUMN id SET DEFAULT nextval('xhbm_file_store_info_aud_id_seq')
2024-07-25 13:52:03,186 [main] ERROR org.nrg.xft.db.DBAction -  *** ALTER TABLE xhbm_file_store_info_aud ALTER COLUMN id SET NOT NULL
2024-07-25 13:52:03,186 [main] ERROR org.nrg.xft.db.DBAction -  *** ALTER SEQUENCE xhbm_file_store_info_aud_id_seq OWNED BY xhbm_file_store_info_aud.id
2024-07-25 13:52:03,188 [main] ERROR org.nrg.xft.db.DBAction - Ran the query 'SELECT (MAX(id) + 1) AS value FROM xhbm_file_store_info_aud' and got the value 1. Now preparing to run the query: SELECT setval('xhbm_file_store_info_aud_id_seq', 1) AS value
2024-07-25 13:52:03,189 [main] ERROR org.nrg.xft.db.DBAction - Preparing to convert table 'xhbm_event_service_payload_entity' to use sequence for default value.
2024-07-25 13:52:03,189 [main] ERROR org.nrg.xft.db.DBAction - Queries prepared for conversion:
2024-07-25 13:52:03,189 [main] ERROR org.nrg.xft.db.DBAction -  *** CREATE SEQUENCE xhbm_event_service_payload_entity_id_seq
2024-07-25 13:52:03,189 [main] ERROR org.nrg.xft.db.DBAction -  *** ALTER TABLE xhbm_event_service_payload_entity ALTER COLUMN id SET DEFAULT nextval('xhbm_event_service_payload_entity_id_seq')
2024-07-25 13:52:03,189 [main] ERROR org.nrg.xft.db.DBAction -  *** ALTER TABLE xhbm_event_service_payload_entity ALTER COLUMN id SET NOT NULL
2024-07-25 13:52:03,189 [main] ERROR org.nrg.xft.db.DBAction -  *** ALTER SEQUENCE xhbm_event_service_payload_entity_id_seq OWNED BY xhbm_event_service_payload_entity.id
2024-07-25 13:52:03,191 [main] ERROR org.nrg.xft.db.DBAction - Ran the query 'SELECT (MAX(id) + 1) AS value FROM xhbm_event_service_payload_entity' and got the value 1. Now preparing to run the query: SELECT setval('xhbm_event_service_payload_entity_id_seq', 1) AS value

[Rick Herrick]

No, those are ALTER statements that XNAT is running itself. It tries to update its schema when possible but in situations where there are dependencies or something where XNAT (okay, me, it was me that wrote that) can’t figure out how to change the schema on its own it just writes them out into the optional statements in the log.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/xnat_discussion/a7ba9b7b-c128-474f-a240-df75c2e0eb6dn%40googlegroups.com.