[Idea] Should add_unique_constraint be considered unsafe?

[jdufresne]

Rails add_unique_constraint is documented at:

https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/PostgreSQL/SchemaStatements.html#method-i-add_unique_constraint

According to the PostgreSQL documentation at:

https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint.

This suggests to me that it is just as unsafe as adding a unique index. However, when I use add_unique_constraint, I don't get a warning from strong_migrations like I would for an index.

One advantage of using add_unique_constraint is that it allows making the index deferred.

[ankane]

Hi @jdufresne, thanks for the suggestion! It does look to be unsafe:

CREATE TABLE t1 (value int);
INSERT INTO t1 SELECT i FROM generate_series(1,10000000) i;
ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE (value);

but can be made safe with:

CREATE UNIQUE INDEX CONCURRENTLY c1 ON t1 (value);
ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE USING INDEX c1;

Added a check in the commit above.

[jdufresne]

Thanks for the quick response!

[andyatkinson]

Nice points @jdufresne and examples @ankane for the use of USING with an existing index that was created concurrently 👍