Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Whenever I try to disable a trigger from within a function, I get following error:

ERROR:  cannot ALTER TABLE "datace" because it is being used by active queries in this session

It seems logical that tables are locked for ALTER TABLE command while used for reading writing by some function. But how should I disable/enable some trigger from that function then?

What I want to do is to update a column that should be protected from updates from outside the function. I thought this should be done through DISABLE/ENABLE trigger, but if this should be done some other way (or is not possible at all in PostgreSQL), I would be glad for such an answer too.

share|improve this question
1  
So you have a BEFORE|AFTER UPDATE ... FOR EACH ROW trigger and within the procedure it executes, you want to UPDATE the same table again, protecting against recursion? If so: Show us your use case - explain what problem you're trying to solve. Usually these problems are better solved by modifying NEW in-place, but there are exceptions. –  Craig Ringer May 23 at 9:54
    
@CraigRinger: you are probably right. I have a big function (about 400 code rows) with several updates, nothing I could easily post here or rewrite to a NEW modification within a trigger procedure. I considered making it a trigger function for insert/update on the table, but that would slow everything too much, and since it is not crucial for consistence, I'd prefer to call it just once in a time. I'll reconsider this (and probably completely change the design) and then I'll update my question to reflect the current state. –  Pavel V. May 23 at 11:10
    
Well, if it's a case of trying to avoid recursive trigger calls, PostgreSQL doesn't really have anything conveniently built-in for that. Search Stack Overflow for a variety of workarounds. –  Craig Ringer May 23 at 11:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.