How to execute SQL in post_statments while in SQL model

[AtraWanderer]

Hello everyone,

I am currently trying to implement indexes in my tables but I can't figure out how to execute code after my select for my materialized view.
I am working with Postgresql and wishing doing so for the geometry indexation.

My models looks like this

MODEL (
  name shema.table,
  kind FULL,
  grain (id)
);

SELECT ...
FROM ...;


CREATE INDEX IF NOT EXISTS @resolve_template('idx_@{schema_name}_@{table_name}_geom_idx') ON @this_model USING gist
    (geom)
    WITH (fillfactor=90, buffering=auto)
    TABLESPACE pg_default;

And the last lines makes errors even when I put then in a post_statements(....) in the model definition.

Error: ERROR: transaction canceled

Thank you

[de-lazurenko]

So transaction canceled is almost never about the index itself — it means something earlier in the transaction already failed and postgres just nuked everything after it. Check your logs for the first error, that's your real culprit.

The obvious thing I see — your CREATE INDEX is floating after the SELECT, outside the MODEL block entirely. That's not valid, it needs to be inside post_statements:

MODEL` (
  name schema.table,
  kind FULL,
  grain (id),
  post_statements (
    CREATE INDEX IF NOT EXISTS @resolve_template('idx_@{schema_name}_@{table_name}_geom_idx')
    ON @this_model USING gist (geom)
    WITH (fillfactor=90, buffering=auto)
    TABLESPACE pg_default
  )
);

SELECT ...
FROM `...;

If it's still blowing up after that, a few things worth checking — does geom actually come out of your SELECT, is @resolve_template producing a clean identifier with no dots or weird chars, and does your SQLMesh version actually handle post_statements properly for FULL models (some older versions had issues there).

Run it with --verbose and paste the first error you see, probably makes the whole thing obvious.