Child pages
  • Rebuilding the XNAT database views
Skip to end of metadata
Go to start of metadata

It's possible for XNAT's database views to become corrupted, especially when developing a new data type or working with complex data. This can lead to odd and unpredictable effects in XNAT. If you're seeing strange behavior but can't identify a cause, dropping the XNAT database views is a possible remedy. On start-up, XNAT checks to see if the views it expects to find are available and inspects them for integrity. If they are missing or corrupted, XNAT will simply drop the views and rebuild them. However, XNAT can't detect corruption of the views sometimes. To force the rebuild of the views, you can use this procedure.

Step-by-step guide

To rebuild the XNAT database views:

  1. If Tomcat is currently running, stop it.
  2. Once Tomcat has stopped, run the SQL below (it is also attached to this page as an SQL script file):
    • Start the PostgreSQL command-line client psql and paste the commands in directly
    • Save the commands into a file or download the SQL script file and run them from the command line:

      Running XNAT database view SQL script
      $ psql < drop_user_views.sql
    • Run the commands from pgAdmin or another database-management tool
  3. When the script has completed, restart Tomcat.

The SQL is shown below. Note that you'll want to change the name specified in the call to drop_user_views() to reflect the database user configured for your XNAT deployment.

Finding and dropping all XNAT database views
CREATE OR REPLACE FUNCTION find_user_views(username TEXT)
  RETURNS TABLE(table_schema NAME, view_name NAME) AS $$
    n.nspname AS table_schema,
    c.relname AS view_name
  FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n
      ON (n.oid = c.relnamespace)
  WHERE c.relkind = 'v'
        AND c.relowner = (SELECT usesysid
                          FROM pg_catalog.pg_user
                          WHERE usename = $1);
END$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION drop_user_views(username TEXT)
  s TEXT;
  c INTEGER := 0;
  RAISE NOTICE 'Dropping views for user %', $1;
  FOR r IN
    SELECT * FROM find_user_views($1)
    S := 'DROP VIEW IF EXISTS ' || quote_ident(r.table_schema) || '.' || quote_ident(r.view_name) || ' CASCADE;';
    EXECUTE s;
    c := c + 1;
    RAISE NOTICE 's = % ', S;
END$$ LANGUAGE plpgsql;

-- Change 'xnat' to the database user for your XNAT deployment.
SELECT drop_user_views('xnat');