Thread: [GENERAL] Build exclusion constraints USING INDEX

Hi,

I was wondering if there was any way to break down the creation of a new exclusion constraint into stages such that table locks most likely to affect performance during production hours are not taken.

Something like:

CREATE INDEX CONCURRENTLY new_index ON my_table USING gist (column1, column2, column3);

ALTER TABLE my_table ADD CONSTRAINT my_exclusion_constraint EXCLUDE USING INDEX new_index (column1 WITH &&, column2 WITH =, column3 WITH &&) NOT VALID;

ALTER TABLE my_table VALIDATE CONSTRAINT my_exclusion_constraint;

AFAICT nothing like the second statement is currently available, but I wanted to check that and see if there are any workarounds.

Thanks,

Steven.

> I was wondering if there was any way to break down the creation of a new exclusion constraint into stages such that table locks most likely to affect performance during production hours are not taken.

> 

> Something like:

> 

> CREATE INDEX CONCURRENTLY new_index ON my_table USING gist (column1, column2, column3);

> ALTER TABLE my_table ADD CONSTRAINT my_exclusion_constraint EXCLUDE USING INDEX new_index (column1 WITH &&, column2 WITH =, column3 WITH &&) NOT VALID;

> ALTER TABLE my_table VALIDATE CONSTRAINT my_exclusion_constraint;

> 

> AFAICT nothing like the second statement is currently available, but I wanted to check that and see if there are any workarounds.

I’m guessing there aren’t any workarounds then, and exclusion constraints will lock the whole table against writes while they are being created?

Steve.