Setting up the index validate process starts by creating a template of type Block.

Following is an example of a SQL Server template:

SELECT '{{index.name}} does not exist'
EXCEPT
SELECT '{{index.name}} does not exist'
FROM sys.indexes
WHERE name = N'{{ index.name }}'
AND object_id = object_id(N'{{ index.table.schema }}.{{ index.table.name }}')

 Select the template in the Target Connection and make sure to check Enable Automatic Creation of Indexes option:

Select the index templates at the table level for existing tables, new tables pick these up automatically:

Next, an example of an index validate template for PostgreSQL:

{# -- TemplateVersion:001 MinVersion:21010 MaxVersion:* TargetType:Postgres ModelType:* TemplateType:Block       -- #}
SELECT '{{index.name | lower}} does not exist'
EXCEPT
SELECT CONCAT(i.relname,' does not exist')
FROM
  pg_catalog.pg_class t,
  pg_catalog.pg_class i,
  pg_catalog.pg_index ix
WHERE
  t.oid = ix.indrelid
  AND i.oid = ix.indexrelid
  AND t.relkind = 'r'
  AND t.relname = '{{ index.table.name | lower }}'
  AND i.relname =  '{{ index.name | lower }}'
;

{%- for col in index.columns %}
SELECT '{{ index.name | lower }} requires updating'
EXCEPT
SELECT CONCAT(i.relname,' requires updating')
FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
WHERE
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND t.relname = '{{ index.table.name | lower }}'
    AND i.relname =  '{{ index.name | lower }}'
        AND a.attname = '{{ col.name | lower }}'
;
{% endfor %}


  • Validate Index block is executed during Procedure/Script Rebuild/Regenerate workflows
  • If there is no configured Index Validate Block Template in a table object that an index is associated with, then the index validate fails.
  • The SQL Block which is generated from the configured Index Validate Block Template can consist of zero of more Select queries that fetch a single column of type Varchar(256).
  • Each Select query is separated by the SQL Block statement separator which is configured in Tools > Options (for example, <EOS>).
  • Each Select query is executed in turn, in the order in the generated SQL Block.
  • Any null values or empty strings are ignored from the result set.
  • If there are any non-null non-empty strings returned, these indicate a validation failure.
  • As soon as a query in the template returns any validation errors, the process halts (so subsequent queries of the generated SQL Block are not executed).
  • If the index validate fails for any reason, then the drop and create index operations will be performed when the index is regenerated.



  • No labels