github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/cli/testdata/dump/reference_self (about)

     1  # Test dumping in the presence of cycles.
     2  # This used to crash before with stack overflow due to an infinite loop before:
     3  # https://github.com/cockroachdb/cockroach/pull/20255
     4  
     5  sql
     6  CREATE DATABASE d;
     7  CREATE TABLE d.t (
     8  	PRIMARY KEY (id),
     9  	FOREIGN KEY (next_id) REFERENCES d.t(id),
    10  	id INT,
    11  	next_id INT
    12  );
    13  INSERT INTO d.t VALUES (
    14  	1,
    15  	NULL
    16  );
    17  ----
    18  INSERT 1
    19  
    20  dump d t
    21  ----
    22  ----
    23  CREATE TABLE t (
    24  	id INT8 NOT NULL,
    25  	next_id INT8 NULL,
    26  	CONSTRAINT "primary" PRIMARY KEY (id ASC),
    27  	INDEX t_auto_index_fk_next_id_ref_t (next_id ASC),
    28  	FAMILY "primary" (id, next_id)
    29  );
    30  
    31  INSERT INTO t (id, next_id) VALUES
    32  	(1, NULL);
    33  
    34  ALTER TABLE t ADD CONSTRAINT fk_next_id_ref_t FOREIGN KEY (next_id) REFERENCES t(id);
    35  
    36  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
    37  ALTER TABLE t VALIDATE CONSTRAINT fk_next_id_ref_t;
    38  ----
    39  ----
    40  
    41  # Now make a reference forces the dump to add the FKs after the data has been inserted.
    42  
    43  sql
    44  UPDATE d.t SET next_id = 1
    45  ----
    46  UPDATE 1
    47  
    48  dump d t
    49  ----
    50  ----
    51  CREATE TABLE t (
    52  	id INT8 NOT NULL,
    53  	next_id INT8 NULL,
    54  	CONSTRAINT "primary" PRIMARY KEY (id ASC),
    55  	INDEX t_auto_index_fk_next_id_ref_t (next_id ASC),
    56  	FAMILY "primary" (id, next_id)
    57  );
    58  
    59  INSERT INTO t (id, next_id) VALUES
    60  	(1, 1);
    61  
    62  ALTER TABLE t ADD CONSTRAINT fk_next_id_ref_t FOREIGN KEY (next_id) REFERENCES t(id);
    63  
    64  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
    65  ALTER TABLE t VALIDATE CONSTRAINT fk_next_id_ref_t;
    66  ----
    67  ----
    68  
    69  # Make some weirdo identifiers and the second FK.
    70  
    71  sql
    72  ALTER TABLE d.t RENAME COLUMN next_id TO "'";
    73  ALTER TABLE d.t RENAME TO d."table";
    74  ----
    75  RENAME TABLE
    76  
    77  dump d table
    78  ----
    79  ----
    80  CREATE TABLE "table" (
    81  	id INT8 NOT NULL,
    82  	"'" INT8 NULL,
    83  	CONSTRAINT "primary" PRIMARY KEY (id ASC),
    84  	INDEX t_auto_index_fk_next_id_ref_t ("'" ASC),
    85  	FAMILY "primary" (id, "'")
    86  );
    87  
    88  INSERT INTO "table" (id, "'") VALUES
    89  	(1, 1);
    90  
    91  ALTER TABLE "table" ADD CONSTRAINT fk_next_id_ref_t FOREIGN KEY ("'") REFERENCES "table"(id);
    92  
    93  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
    94  ALTER TABLE "table" VALIDATE CONSTRAINT fk_next_id_ref_t;
    95  ----
    96  ----
    97  
    98  # Dumping only the schema doesn't need to use the ALTER TABLE FK stuff.
    99  
   100  dump d --dump-mode=schema
   101  ----
   102  ----
   103  CREATE TABLE "table" (
   104  	id INT8 NOT NULL,
   105  	"'" INT8 NULL,
   106  	CONSTRAINT "primary" PRIMARY KEY (id ASC),
   107  	INDEX t_auto_index_fk_next_id_ref_t ("'" ASC),
   108  	FAMILY "primary" (id, "'")
   109  );
   110  
   111  ALTER TABLE "table" ADD CONSTRAINT fk_next_id_ref_t FOREIGN KEY ("'") REFERENCES "table"(id);
   112  
   113  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
   114  ALTER TABLE "table" VALIDATE CONSTRAINT fk_next_id_ref_t;
   115  ----
   116  ----
   117  # Dumping only the data shouldn't have the ALTER stuff either.
   118  
   119  dump d --dump-mode=data
   120  noroundtrip
   121  ----
   122  ----
   123  
   124  INSERT INTO "table" (id, "'") VALUES
   125  	(1, 1);
   126  ----
   127  ----