transactionsconstraintscockroachdbsqltransactioncockroachcloud

encapsulating a Drop and Add constraint in a transaction

I have a simple drop and add a constraint encapsulated in a transaction as follows:

begin;
alter table movies drop constraint actor_check;
alter table movies add constraint actor_check check ( "actor" not like '%Harpo Marx%'
    and "actor" not like '%Chico Marx%'
    and "actor" not like '%Groucho Marx%'
    and "actor" not like '%Zeppo Marx%');
end;

But the transaction errors out:

BEGIN
Time: 70ms
ALTER TABLE
Time: 364ms
DELETE 0
Time: 1.283s
ERROR: relation "movies" (61): duplicate constraint name: "actor_check"
SQLSTATE: 42710
ERROR: relation "movies" (61): duplicate constraint name: "actor_check"
SQLSTATE: 42710
Failed running "sql"

Please advise

Solution

  • This sort of atomic constraint change is not currently supported by cockroach. The reasons why not are relatively involved. Cockroach attempts to perform all schema changes in an online manner, never locking the table for writes. To achieve this, the database decomposes schema changes into stages, most of which are executed asynchronously after the transaction commits. This poses problems for a variety of schema changes which rely on eachother. We are actively working on fixing this, though it is a long project. You can read more in this RFC.

    For now, the best you can do is to drop the constraint and add a new one with a different name and in a subsequent transaction rename it to the old name.