Renaming database in a transaction


(Martin Hrabovcin) #1

Hello!

I am trying to implement “atomic” database swap for DB restore operation from a backup. My current design is the following:

I have an existing database a that has been dumped into a file. When I do DB restore from a file, I create a new database b and load data from DB dump into the database b. When data are loaded and I have 2 consistent databases a and b I am trying to do following transaction:

./cockroach sql --insecure -e "BEGIN; SAVEPOINT cockroach_restart; ALTER DATABASE a RENAME TO a_old ; ALTER DATABASE b RENAME TO a; RELEASE SAVEPOINT cockroach_restart; COMMIT;"

I’d like to confirm that this is the right way to achieve “atomic” database swap in a single transaction that doesn’t leave a room for inconsistent state.

Thanks,
Martin


(Ron Arévalo) #2

Hey @mhrabovcin,

I just tried this out on my own local cluster, and it worked as expected. As a note, all transactions in Cockroach are atomic, if one part of the transaction fails the entire transaction will fail. You can read more about that here.

Thanks,

Ron


(Ron Arévalo) #3

Hey @mhrabovcin,

I actually wanted to clarify this answer, database renames, in contrast to pretty much everything else, are not transactional in cockroachdb.

There are a few things that are not transactional in Cockroach:

  1. database renames (not tables/indexes)
  2. sequence increments (these are atomic but cannot be rolled back)
  3. statistic creation

Please let me know if you have any more questions.

Thanks,

Ron


(Martin Hrabovcin) #4

Hi @ronarev,

thanks for your response. I’d like to ask you for more information about what guarantees I am getting by running this rename in a transaction? Is there a better approach to do an atomic database swap in cockroachdb?

Hypothetical situation, a client connected to cockroachdb is running following statements:

BEGIN;
SAVEPOINT cockroach_restart;
ALTER DATABASE a RENAME TO a_old ;
-- Following statement will fail for any reason.
ALTER DATABASE b RENAME TO a;
RELEASE SAVEPOINT cockroach_restart;
COMMIT;

In what state will be the database after the transaction?


(Raphael 'kena' Poss) #5

The end state is that the database a is renamed to a_old and there is no database named a any more.


(Ron Arévalo) #6

Hi @mhrabovcin,

We would love to know more about your use case. At the moment, CockroachDB doesn’t support transactional renames. This is something we’d like to work on and having your input would be valuable in order to report it to our Product Managers and prioritize a solution at in a later version of Cockroach DB.

Thanks,

Ron


(Martin Hrabovcin) #7

Hi @knz,

I’ve tried following test

root@:26257/> SHOW DATABASES;
+----------+
| Database |
+----------+
| a        |
| b        |
| system   |
+----------+
(3 rows)

Time: 1.1108ms

root@:26257/> BEGIN;                                                                                                                   SAVEPOINT cockroach_restart;                                                                                                           ALTER DATABASE a RENAME TO a_old;                                                                                                      ALTER DATABASE b RENAME TO a_old;                                                                                                      RELEASE SAVEPOINT cockroach_restart;                                                                                                   COMMIT;
pq: the new database name "a_old" already exists
root@:26257 ERROR> ROLLBACK;
ROLLBACK

Time: 457.9µs

root@:26257/> SHOW DATABASES;
+----------+
| Database |
+----------+
| a        |
| b        |
| system   |
+----------+
(3 rows)

Time: 1.0523ms

and by what you’ve said I’d expect to end up with a_old and b databases.


(Raphael 'kena' Poss) #8

It’s unfortunately more complicated than that.
There are two phases during a database rename:

  1. the database descriptor is updated, and announced to every other node
  2. the system.namespace table is updated

The name of a database is both in the descriptor and the system.namespace table.

Step 1 is non-transactional. The rename will be announced to other nodes even if the transaction is aborted.

Step 2 is transactional, and will be rolled back if the transaction aborts.

This yields a surprising and undesirable behavior: when run inside begin…commit, it’s possible for a rename to be half-done - not persisted in storage, but visible to other nodes or other transactions. This violates A, C and I in ACID. Only D is guaranteed: if the transaction commits successfully, the new name will persist after that.

We’re very sorry by this state of affairs, and we’d like to know more about your use case, what you are trying to achieve and what we can do to answer your needs.


(Martin Hrabovcin) #9

Hey @knz,

thanks for the detailed explanation. I am sorry I haven’t explained my use case in my original submission.

I have a python web application that is using SQLAlchemy and cockroachdb as a storage backend. A user can backup the data of the application to the file before a software upgrade. As a potential upgrade rollback, we advise users to restore data from backup file.

My goal is to provide a safe way to restore a database, while a python application is running.

Thank you for your help


(Raphael 'kena' Poss) #10

You can set a value in a “lock” table (use the values 1 vs 0 in a table cell) to indicate that a backup or restore is in progress. Since table access is transactional, that would create isolation around your backup/restore process. Would that help?


(Martin Hrabovcin) #11

@knz I’ll look into possibility of introducing a “lock” table. Ideally I’d rather not leak backup/restore concepts into the python application.

Thank you for your help!