Support temporary table

[tiancaiamao]

Development Task

A temporary table is a special table whose rows data are only temporarily available.

For a global temporary table, the table schema is visible to all the sessions, but the changes into the table are only available within the current transaction, when the transaction commits, all changes to the global temporary table are discarded.

For a local temporary table, it is visible only within the current session, and the table is dropped automatically when the session is closed.

Tech Design

Weekly Report

Proposal

Milestones

Stage 1

DDL @Howie59

• Grammar change for the global temporary table parser: support the 'create global temporary table' syntax parser#1211
• Ignore table options or report errors for create table like ddl: add create table like check for temporary table #25082
• Forbid some table options
  • pre_split_regions ddl: add region and split compatibility for temporary table #24684
  • on commit preserve rows / engine=innodb ddl: grammar check for create unsupported temporary table #24723
  • auto_random / shard_row_id_bits ddl: add auto random && shard_row_id_bits compatibility for temporary table #24940
  • partition ddl: add partition compatibility for temporary table #24406
  • foreign key ddl: add foreign key compatibility for temporary table #24961
  • virtual column ddl: add virtual columns compatibility for temporary table #25065
• Fix show create table executor,table: fix 'show create table' for the temporary table #24857
• Add a switch to disable global temporary tables by default *: add sys var tidb_enable_global_temporary_table to open global temp table #25075

DML @tiancaiamao

• Support write/read to a global temporary table *: support read and write operations for the global temporary table #24196
• Allocate autoID from memory executor, meta: Allocate auto id for global temporary tables #24506
• Do not write binlog binlog: DML on temporary tables do not write binlog #24570
• Memory size does not exceed temp_table_size *: add global system variable tmp_table_size #24827

DQL

• Queries on temporary tables do not fall on TiKV @tiancaiamao
  • no coprocessor request send to TiKV executor: avoid distsql request for TableReader/IndexReader/IndexLookup on temporary table #24769
  • batch point get / point get executor: do not send snapshot request for (batch) point get on temporary table #24804
• Cost estimation session, planner: Add a factor getter for networkFactor/scanFactor/descScanFactor/seekFactor #25046

Transaction

• Handle schema change during transaction commit ddl, transaction: DDL on temporary tables won't affect transactions #24534
• Ignore locks on temporary tables @tiancaiamao
  • point get / batch point get planner: ignore lock for temporary table of PointGet and BatchPointGet #24540
  • Pessimistic lock executor: pessimistic lock on the temporary table should not be written to TiKV #24737
• Retry DML on temporary tables session: test that temporary tables will also be retried #24505

Feature compatibility

• Admin check table ddl: add check table compatibility for temporary table #24501
• Admin checksum table ddl: add admin_checksum_table compatibility for temporary table #24867
• Show table regions / split table ddl: add region and split compatibility for temporary table #24684
• Stale read planner, executor: add stale read compatibility for temporary table #25206
• historical read executor: add history read compatibility for temporary table #25237
• tablesample executor: add tablesample compatibility for temporary table #25288
• Flashback / recover table ddl: forbid recover/flashback temporary tables #24518
• tidb_constraint_check_in_place variable (verified, add a test) @tiancaiamao executor: add a test case for 'tidb_constraint_check_in_place' on temporary table #24784
• Do not support SQL binding bindinfo,planner: report error when creating sql binding on temporary table #25058
• Telemetry telemetry,session: add telemetry for the temporary table feature #24866
• Forbid setting tiflash replicas ddl: Forbid set tiflash replicas on temporary tables #25036

Tools compatibility @djshow832

• BR: update TiDB module (already updated)
• TiCDC: recognize temporary tables changefeed: filter out the temporary table schema change tiflow#1923 (already updated)

Stage 2

DDL

• grammar change for the local temporary table (already done by global temporary table)
• local temporary table schema definition *: add TestLocalTemporaryTableInfoSchema to store local temporary table schemas #25518
• DDL changes for creating a local temporary table *: support create a local temporary table #25851
• show create table for a local temporary table
• Drop a local temporary table ddl: Drop a local temporary table #26063
• drop global temporary table can only drop global temporary table #26278
• Truncate a local temporary table
• Dropping a database does not drop local temporary tables
• Check other operations and make sure 'alter table' operations raise an error
• Check table options behavior same with global table
• Create table like
• Support create if not exists #25918 *: Support create local temporary table if not exist #25983
• Create view should not refer a local temporary table #25916 planner: Forbide create view on tmp table #26091
• View should always read non local temporary table #25917 planner: Keep view always select non-local-temporary table #26087

DML

• Complete getting network factor for temporary table using cascade optimizer #25957 planner: Make temporary table calculate right cost for cascades optimizer  #25958
• Support write operation to a local temporary table #25919
• Support insert and point_get for local temporary table #26048 *: finish insert/point_get for local temporary table #26053
• Support delete for local temporary table #26049
• Support batch point get for temporary table #26050
• Support update for local temporary table #26051
• Check local temporary table behavior 'insert ignore/on duplicate/replace' #26083
• Support read operation to a local temporary table #25920
• Drop table should clear data in session #25921
• Handle the point get / batch point get path #25922
• check tidb_constraint_check_in_place for insert and update #25923
• Check allocate autoID behavior #25926
• Check binlog behavior #25927
• Memory size does not exceed temp_table_size #25928

Transaction

• Creating and dropping a local temporary table doesn’t cause an implicit commit *: support create a local temporary table #25851
• Check handle schema change during transaction commit #25931
• Check Ignore locks behavior on local temporary tables #25932
• Check Retry DML on local temporary tables #25933

Feature compatibility, most should have a same behavior with temporary table

• Forbid admin check table on local temporary tables #25934
• Forbid admin checksum table on local temporary tables #25935
• Forbid show table regions / split table in local temporary tables #25936
• Forbid stale read in local temporary tables #25937
• do not allow historical read in local temporary tables #25938
• Forbid tablesample in local temporary tables #25939
• Forbid flashback / recover table in local temporary tables #25940
• Forbid SQL binding in local temporary tables #25941
• Forbid setting tiflash in local temporary tables #25943
• Add cases that local temporary table works well for cte #25951
• Complete bind of temporary table check for Insert/Update/Delete statements #27422
• Forbid defining placement policies on temporary tables #28818

Tests

• port mysql temporary table tests #25950

GA

• Default enable temporary table before GA #28281
• Add temporary table to public document #28346

Stage 3

• Support telemetry in local temporary tables #25942
• Support spilling to disk for the MemDB
• Support TiKV as a storage engine
• Maintain row count stats for temporary tables #25924
• Analyze / auto analyze #25925

[tisonkun]

@djshow832 we can possibly use format

• grammar change for the global temporary table parser: support the 'create global temporary table' syntax parser#1211

instead of

• grammar change for the global temporary table

and thus we get more info while keep link click magic.

[tisonkun]

@tiancaiamao @djshow832 @xhebox is this task finished?