3

I need a trigger to cause a reindex (index is on an immutable function) I define the function as

CREATE FUNCTION "TEST".triggerFuncTest()
RETURNS TRIGGER
AS $$
BEGIN
  REINDEX INDEX "TEST"."MyIndex";
  RETURN NULL;
END $$ LANGUAGE plpgsql;

with the trigger

CREATE CONSTRAINT TRIGGER triggerTest
AFTER INSERT OR UPDATE OF indexes OR DELETE ON "TEST"."MyTable"
DEFERRABLE INITIALLY DEFERRED
FOR EACH STATEMENT
EXECUTE PROCEDURE "TEST".triggerFuncTest();

Which gives

ERROR:  syntax error at or near "STATEMENT"

However the trigger will be created if I remove CONSTRAINT and DEFERRABLE INITIALLY DEFERRED, however this will cause an exception when triggered.

Improve this question
5
  • 1
    Why would you want to rebuild the index for each DML statement you are running? Are you aware that Postgres will automatically update the index? But a constraint trigger is the wrong thing in the first place, you want to a regular trigger (but still: this does not make any sense) Commented Jan 7, 2016 at 13:41
  • indexes on an immutable function dont get updated unfortunately Commented Jan 7, 2016 at 13:42
  • And why are you defining an index on an immutable function? That doesn't make sense either. Commented Jan 7, 2016 at 13:43
  • Also I tried a regular trigger, it throws an error as you cant change a tables index whilst its being accessed in the transaction Commented Jan 7, 2016 at 13:44
  • The reason for the index on the immutable function is here stackoverflow.com/questions/34655425/… Commented Jan 7, 2016 at 13:44
Related questions
1250
Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails
1107
PostgreSQL error: Fatal: role "username" does not exist
22
Deferrable, case-insensitive unique constraint
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.