github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/ccl/backupccl/testdata/restore_mid_schema_change/create.sql (about)

     1  /*
     2  Test cases are grouped as: i) CREATE TABLE(s), ii) Schema change(s) iii) BACKUP
     3  This implies that for a given table, a BACKUP was taken while the schema changes
     4  were running. If there are multiple schema changes, they were all running
     5  simultaneously.
     6  
     7  N.B.: The binary was modified to add a time.Sleep to block the schema change
     8  after it completed the backfill portion of the schema change.
     9   */
    10  
    11  CREATE TABLE midaddcol as select * from generate_series(1,3) as a;
    12  ALTER TABLE midaddcol ADD COLUMN b DECIMAL NOT NULL DEFAULT (DECIMAL '1.3');
    13  BACKUP defaultdb.* TO 'nodelocal://1/midaddcol';
    14  DROP TABLE midaddcol;
    15  
    16  CREATE TABLE midaddconst as select * from generate_series(1,3) as a;
    17  ALTER TABLE midaddconst ADD CONSTRAINT my_const CHECK (a > 0);
    18  BACKUP defaultdb.* TO 'nodelocal://1/midaddconst';
    19  DROP TABLE midaddconst;
    20  
    21  CREATE TABLE midaddindex as select * from generate_series(1,3) as a;
    22  CREATE INDEX my_idx ON midaddindex(a);
    23  BACKUP defaultdb.* TO 'nodelocal://1/midaddindex';
    24  DROP TABLE midaddindex;
    25  
    26  CREATE TABLE middropcol as select * from generate_series(1,3) as a, generate_series(1,3) as b;
    27  ALTER TABLE middropcol DROP COLUMN b;
    28  BACKUP defaultdb.* TO 'nodelocal://1/middropcol';
    29  DROP TABLE middropcol;
    30  
    31  CREATE TABLE midmany as select * from generate_series(1,3) as a;
    32  ALTER TABLE midmany ADD COLUMN b DECIMAL NOT NULL DEFAULT (DECIMAL '1.3');
    33  ALTER TABLE midmany ADD CONSTRAINT my_const CHECK (a > 0);
    34  CREATE INDEX my_idx ON midmany(a);
    35  BACKUP defaultdb.* TO 'nodelocal://1/midmany';
    36  DROP TABLE midmany;
    37  
    38  CREATE TABLE midmultitxn as SELECT * from generate_series(1,3) as a;
    39  BEGIN;
    40  ALTER TABLE midmultitxn ADD COLUMN b DECIMAL NOT NULL DEFAULT (DECIMAL '1.3');
    41  ALTER TABLE midmultitxn ADD CONSTRAINT my_const CHECK (a > 0);
    42  CREATE INDEX my_idx ON midmultitxn(a);
    43  COMMIT;
    44  BACKUP defaultdb.* TO 'nodelocal://1/midmultitxn';
    45  DROP TABLE midmultitxn;
    46  
    47  CREATE TABLE midmultitable1 AS SELECT * FROM generate_series(1, 3) AS a;
    48  CREATE TABLE midmultitable2 AS SELECT * FROM generate_series(1, 3) AS a;
    49  ALTER TABLE midmultitable1 ADD COLUMN b DECIMAL NOT NULL DEFAULT (DECIMAL '1.3');
    50  ALTER TABLE midmultitable2 ADD CONSTRAINT my_const CHECK (a > 0);
    51  BACKUP defaultdb.* TO 'nodelocal://1/midmultitable';
    52  DROP TABLE midmultitable1;
    53  DROP TABLE midmultitable2;
    54  
    55  -- Primary key swaps are only supported on 20.1+.
    56  CREATE TABLE midprimarykeyswap AS SELECT * FROM generate_series(1,3) AS a;
    57  -- This schema change is used to enable the primary key swap. The backup is not taken during this schema change.
    58  ALTER TABLE midprimarykeyswap ALTER COLUMN a SET NOT NULL;
    59  ALTER TABLE midprimarykeyswap ALTER PRIMARY KEY USING COLUMNS (a);
    60  BACKUP defaultdb.* TO 'nodelocal://1/midprimarykeyswap';
    61  DROP TABLE midprimarykeyswap;
    62  
    63  CREATE TABLE midprimarykeyswapcleanup AS SELECT * FROM generate_series(1,3) AS a;
    64  -- This schema change is used to enable the primary key swap. The backup is not taken during this schema change.
    65  ALTER TABLE midprimarykeyswapcleanup ALTER COLUMN a SET NOT NULL;
    66  ALTER TABLE midprimarykeyswapcleanup ALTER PRIMARY KEY USING COLUMNS (a);
    67  BACKUP defaultdb.* TO 'nodelocal://1/midprimarykeyswapcleanuprestor';
    68  DROP TABLE midprimarykeyswapcleanup;