github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/txn_as_of (about)

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE TABLE t (i INT)
     5  
     6  statement ok
     7  INSERT INTO t VALUES (2)
     8  
     9  # Verify that transacations can be used for historical reads using BEGIN
    10  # or SET TRANSACTION
    11  
    12  statement error pq: relation "t" does not exist
    13  BEGIN AS OF SYSTEM TIME '-1h'; SELECT * FROM t
    14  
    15  statement ok
    16  COMMIT
    17  
    18  statement error pq: relation "t" does not exist
    19  BEGIN; SET TRANSACTION AS OF SYSTEM TIME '-1h'; SELECT * FROM t
    20  
    21  statement ok
    22  COMMIT
    23  
    24  statement ok
    25  BEGIN AS OF SYSTEM TIME '-1us'
    26  
    27  query I
    28  SELECT * FROM t
    29  ----
    30  2
    31  
    32  statement ok
    33  COMMIT
    34  
    35  statement ok
    36  BEGIN; SET TRANSACTION AS OF SYSTEM TIME '-1us'
    37  
    38  query I
    39  SELECT * FROM t
    40  ----
    41  2
    42  
    43  statement ok
    44  COMMIT
    45  
    46  # Subqueries are only allowed if the exact same timestamp is used.
    47  # This first two fails because '-1h' is computed for each statement based on
    48  # statement time so the timestamps will not be identical. The following two 
    49  # tests which use a matching, fixed timestamp should be semantically valid
    50  # leading to a failure due to the relation not existing.
    51  
    52  statement error pq: inconsistent AS OF SYSTEM TIME timestamp.
    53  BEGIN; SET TRANSACTION AS OF SYSTEM TIME '-1h'; SELECT * FROM t AS OF SYSTEM TIME '-1h'
    54  
    55  statement ok
    56  COMMIT
    57  
    58  statement error pq: inconsistent AS OF SYSTEM TIME timestamp.
    59  BEGIN AS OF SYSTEM TIME '-1h'; SELECT * FROM t AS OF SYSTEM TIME '-1h'
    60  
    61  statement ok
    62  COMMIT
    63  
    64  statement error pq: relation "t" does not exist
    65  BEGIN AS OF SYSTEM TIME '2018-12-30'; SELECT * FROM t AS OF SYSTEM TIME '2018-12-30'
    66  
    67  statement ok
    68  COMMIT
    69  
    70  statement error pq: relation "t" does not exist
    71  BEGIN; SET TRANSACTION AS OF SYSTEM TIME '2018-12-30'; SELECT * FROM t AS OF SYSTEM TIME '2018-12-30'
    72  
    73  statement ok
    74  COMMIT
    75  
    76  # Verify transactions with a historical timestamps imply READ ONLY.
    77  
    78  statement ok
    79  BEGIN; SET TRANSACTION AS OF SYSTEM TIME '-1us'
    80  
    81  statement error pq: cannot execute INSERT in a read-only transaction
    82  INSERT INTO t VALUES (3)
    83  
    84  statement ok
    85  COMMIT
    86  
    87  statement ok
    88  BEGIN AS OF SYSTEM TIME '-1us'
    89  
    90  statement error pq: cannot execute INSERT in a read-only transaction
    91  INSERT INTO t VALUES (3)
    92  
    93  statement ok
    94  COMMIT
    95  
    96  # Verify setting the timestamp after beginning with a timestamp overwrites
    97  # the previous value.
    98  
    99  statement ok
   100  BEGIN AS OF SYSTEM TIME '-1h'; SET TRANSACTION AS OF SYSTEM TIME '-1us';
   101  
   102  query I
   103  SELECT * FROM t
   104  ----
   105  2
   106  
   107  statement ok
   108  COMMIT
   109  
   110  # Verify that setting other parts of a transaction mode does not overwrite
   111  # the AOST from the BEGIN.
   112  
   113  statement ok
   114  BEGIN AS OF SYSTEM TIME '-1h'; SET TRANSACTION PRIORITY HIGH;
   115  
   116  statement error pq: relation "t" does not exist
   117  SELECT * FROM t
   118  
   119  statement ok
   120  COMMIT
   121  
   122  # Verify that setting a TRANSACTION READ WRITE is an error if the transaction
   123  # has a historical timestamp.
   124  
   125  statement error AS OF SYSTEM TIME specified with READ WRITE mode
   126  BEGIN AS OF SYSTEM TIME '-1h'; SET TRANSACTION READ WRITE;
   127  
   128  statement ok
   129  COMMIT
   130  
   131  statement error AS OF SYSTEM TIME specified with READ WRITE mode
   132  BEGIN AS OF SYSTEM TIME '-1h', READ WRITE
   133  
   134  statement ok
   135  BEGIN
   136  
   137  statement error AS OF SYSTEM TIME specified with READ WRITE mode
   138  SET TRANSACTION AS OF SYSTEM TIME '-1h', READ WRITE
   139  
   140  statement ok
   141  COMMIT
   142  
   143  # Verify that the TxnTimestamp used to generate now() and current_timestamp() is
   144  # set to the historical timestamp.
   145  
   146  statement ok
   147  BEGIN AS OF SYSTEM TIME '2018-01-01';
   148  
   149  query T
   150  SELECT * FROM (SELECT now()) AS OF SYSTEM TIME '2018-01-01'
   151  ----
   152  2018-01-01 00:00:00 +0000 UTC
   153  
   154  statement ok
   155  COMMIT
   156  
   157  # Verify that the the historical timestamp used in a SET TRANSACTION can
   158  # overwrite the timestamp set in a BEGIN.
   159  
   160  statement ok
   161  BEGIN AS OF SYSTEM TIME '2019-01-01'
   162  
   163  statement ok
   164  SET TRANSACTION AS OF SYSTEM TIME '2018-01-01'
   165  
   166  query T
   167  SELECT * FROM (SELECT now())
   168  ----
   169  2018-01-01 00:00:00 +0000 UTC
   170  
   171  statement ok
   172  COMMIT
   173  
   174  # Verify that a historical timestamp is preserved after a ROLLBACK to a
   175  # SAVEPOINT for a historical transaction initiated in the BEGIN.
   176  
   177  statement ok
   178  BEGIN AS OF SYSTEM TIME '2019-01-01'
   179  
   180  statement ok
   181  SAVEPOINT cockroach_restart;
   182  
   183  query T
   184  SELECT * FROM (SELECT now())
   185  ----
   186  2019-01-01 00:00:00 +0000 UTC
   187  
   188  statement ok
   189  ROLLBACK TO SAVEPOINT cockroach_restart;
   190  
   191  query T
   192  SELECT * FROM (SELECT now())
   193  ----
   194  2019-01-01 00:00:00 +0000 UTC
   195  
   196  statement ok
   197  RELEASE SAVEPOINT cockroach_restart
   198  
   199  statement ok
   200  COMMIT;
   201  
   202  # Verify that a historical timestamp is preserved after a ROLLBACK to a
   203  # SAVEPOINT for a historical transaction initiated in SET TRANSACTION
   204  
   205  statement ok
   206  BEGIN;
   207  
   208  statement ok
   209  SET TRANSACTION AS OF SYSTEM TIME '2019-01-01'
   210  
   211  statement ok
   212  SAVEPOINT cockroach_restart;
   213  
   214  query T
   215  SELECT * FROM (SELECT now())
   216  ----
   217  2019-01-01 00:00:00 +0000 UTC
   218  
   219  statement ok
   220  ROLLBACK TO SAVEPOINT cockroach_restart;
   221  
   222  query T
   223  SELECT * FROM (SELECT now())
   224  ----
   225  2019-01-01 00:00:00 +0000 UTC
   226  
   227  statement ok
   228  RELEASE SAVEPOINT cockroach_restart
   229  
   230  statement ok
   231  COMMIT;
   232  
   233  # Verify that rolling back after a syntax error which moves the conn to the
   234  # aborted state maintains the transaction timestamp.
   235  
   236  statement ok
   237  BEGIN;
   238  
   239  statement ok
   240  SET TRANSACTION AS OF SYSTEM TIME '2019-01-01'
   241  
   242  statement ok
   243  SAVEPOINT cockroach_restart;
   244  
   245  statement error at or near "selct": syntax error
   246  SELCT;
   247  
   248  statement ok
   249  ROLLBACK TO SAVEPOINT cockroach_restart;
   250  
   251  query T
   252  SELECT * FROM (SELECT now())
   253  ----
   254  2019-01-01 00:00:00 +0000 UTC
   255  
   256  statement ok
   257  RELEASE SAVEPOINT cockroach_restart
   258  
   259  statement ok
   260  COMMIT
   261  
   262  # Ensure that errors evaluating AOST clauses in BEGIN and SET TRANSACTION do not
   263  # cause problems.
   264  
   265  statement error pq: AS OF SYSTEM TIME: zero timestamp is invalid
   266  BEGIN AS OF SYSTEM TIME '0'
   267  
   268  statement ok
   269  BEGIN
   270  
   271  statement error pq: AS OF SYSTEM TIME: zero timestamp is invalid
   272  SET TRANSACTION AS OF SYSTEM TIME '0'
   273  
   274  statement ok
   275  ROLLBACK