CODEWITHSUNDEEP
postgresql
ocharles
ocharles

Reputation: 6080

Is it possible to restore deferred constraints after setting SET CONSTRAINTS ALL IMMEDIATE?

I am using pgtap to test some deferred constraint triggers by:

This works fine, but it limits me to a single test per transaction. If I try and set up more data later, my previously deferred constraints now fire immediately.

Is it possible to revert the affect of SET CONSTRAINTS ALL IMMEDIATE, without reverting the effects of the constraints that it caused to activate? If not, my only option is to move each test to a separate file, which is a little bit cumbersome.

Upvotes: 2

Views: 1030

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658072

Have you tried 

SET CONSTRAINTS ALL DEFERRED

after you call SET CONSTRAINTS ALL IMMEDIATE?

If that would defer more constraints than you want to, you'll have to name them individually:

SET CONSTRAINTS my_constraint [, ...] DEFERRED;

It does not revert any effect. It only defers further checks. If a constraint is violated, an EXCEPTION is raised. There is nothing that could be reverted here.

You could catch the exception in a plpgsql function if that is what you want.

If you ant to revert constraints to their initial state, you'll have to SET them individually and with the explicit state. Sadly - to my knowledge - there is no "RESET CONSTRAINTS" in PostgreSQL 9.1 that would bring them all to their initial state.

Manual page.

Upvotes: 3

Related Questions