Build exclusion constraints USING INDEX

From: Steven Winfield <Steven(dot)Winfield(at)cantabcapital(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Build exclusion constraints USING INDEX
Date: 2017-02-07 13:20:36
Message-ID: E9FA92C2921F31408041863B74EE4C20016CD4C8BE@CCPMAILDAG03.cantab.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Steve Atkins 2017-02-07 20:05:00 Re: Logging broken messages
Previous Message Aron Podrigal 2017-02-07 00:12:36 Re: Simple Query not using Primary Key Index