[Idea] Are there any concerns about creating PostgreSQL exclusion constraints (gist)?

[cbortz]

Hey y'all. Thanks for this super helpful gem.

Last month rails/rails#40224 was merged, which introduces support for PostgreSQL exclusion constraints using gist. Among other things, there is a new migration method add_exclusion_constraint.

As far as I can tell, it hasn't yet been staged for an official release, but it's in the main branch at this point.

Admittedly, I don't have enough experience to know what might (if anything) make this type of table alteration unsafe, but thought it may be relevant to this project. So, I figured I'd at least get a conversation started.

[ankane]

Hey @cbortz, thanks for sharing! From what I can tell, it appears to be unsafe (for existing tables).

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE t1 (value int);
INSERT INTO t1 SELECT i FROM generate_series(1,1000000) i;
ALTER TABLE t1 ADD CONSTRAINT c1 EXCLUDE USING gist (value WITH =); -- slow, table locked

Also, I'm not sure there's a safe way to do it.

ALTER TABLE t1 ADD CONSTRAINT c1 EXCLUDE USING gist (value WITH =) NOT VALID;
ERROR:  EXCLUDE constraints cannot be marked NOT VALID

I've added it to the plan (#177) for when Rails 7.1 rc1 is released.

[cbortz]

@ankane Glad it was helpful. Thanks for sharing that example from your experiment! 🙏

[ankane]

Hey @cbortz, added (a little early) in the commit above. Thanks again for the suggestion!

[cbortz]

@ankane As always, thank you for continuing to maintain this library (and all of the others)!