PostgreSQL 临时表

转载自： http://blog.163.com/digoal@126/blog/static/1638770402012101575032326/

sql标准中临时表是一次创建,以后使用的时候无须再次创建的. 并且每个会话保持各自的数据.

但是在Postgresql中,临时表的使用有所改变.

1. 临时表在会话结束后会自动删除（或者在事务结束后删除on commit drop）. 也就是说每个会话中需要使用临时表的话需要重新创建.

这个有好处也有坏处,好处是不同的会话能够使用同名但是不同结构的临时表. sql标准无法做到.

坏处是新建的会话如果只是要使用同名同结构的临时表也有重新创建.

2. 临时表可以选择在事务结束后删除数据或者保留数据或者删除表.

【语法】

  
  
   
   CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  
  
  
  
   
    { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
  
  
  
  
   
     | table_constraint
  
  
  
  
   
     | LIKE source_table [ like_option ... ] }
  
  
  
  
   
     [, ... ]
  
  
  
  
   
   ] )
  
  
  
  
   
   [ INHERITS ( parent_table [, ... ] ) ]
  
  
  
  
   
   [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
  
  
  
  
   
   [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
  
  
  
  
   
   [ TABLESPACE tablespace_name ]

红色部分是与临时表有关的. 其中GLOBAL和LOCAL在这个语法中是一样的,没有分别,但是在sql标准中是不一样的.

ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP

PRESERVE ROWS 表示临时表的数据在事务结束后保留.

DELETE ROWS 表示临时表的数据在事务结束后truncate掉.

DROP 表示临时表在事务结束后删除.

默认使用的是 PRESERVE ROWS.

【例子】

1. 临时表在会话结束后会自动删除（或者在事务结束后删除on commit drop）.

会话1 :

  
  
   
   
   
    
    pg9 
    . 
    2.0@db 
    - 
    172 
    - 
    16 
    - 
    3 
    - 
    150 
    -> 
     psql digoal digoal 
   
   
    
    psql  
    ( 
    9.2 
    . 
    0 
    ) 
   
   
    
    Type 
      
    "help" 
      
    for 
     help 
    . 
   
  
  
  
  
   
    
    digoal=> create temp table t(id int); 
   
   
    
    CREATE TABLE 
   
   
    
    digoal=> select relname,relnamespace,oid from pg_class where relname='t'; 
   
   
    
    relname | relnamespace | oid  
   
   
    
    ---------+--------------+------- 
   
   
    
    t    |    41192 | 41203 
   
   
    
    (1 row) 
   
   
    
     
     digoal=> select nspname from pg_namespace where oid=41192; 
     
     
      nspname  
     
     
     ----------- 
     
     
     pg_temp_2 
     
     
     (1 row)

退出会话1后重进,临时表已经被删除了.

   
   
    
    digoal=> \q
   
   
   
   
    
    pg9.2.0@db-172-16-3-150-> psql digoal digoal
   
   
   
   
    
    psql (9.2.0)
   
   
   
   
    
    Type "help" for help.
   
   
   
   
    
    digoal=> select nspname from pg_namespace where oid=41192;
   
   
   
   
    
     nspname 
   
   
   
   
    
    -----------
   
   
   
   
    
    pg_temp_2
   
   
   
   
    
    (1 row)
   
   
   
   
    
    digoal=> select relname,oid from pg_class where relname='t';
   
   
   
   
    
    relname | relnamespace | oid
   
   
   
   
    
    ---------+--------------+-----
   
   
   
   
    
    (0 rows)

2. 每个会话中需要使用临时表的话需要重新创建. 好处是不同的会话能够使用同名但是不同结构的临时表.

会话1

   
   
    
     
     pg9.2.0@db-172-16-3-150-> psql digoal digoal 
    
    
     
     psql (9.2.0) 
    
    
     
     Type "help" for help. 
    
   
   
   
   
    
    
    
     
     digoal 
     => 
      create temp table t 
     ( 
     id  
     int 
     ); 
    
    
     
     CREATE TABLE

会话2

  
  
   
    
    pg9.2.0@db-172-16-3-150-> psql digoal digoal 
   
   
    
    psql (9.2.0) 
   
   
    
    Type "help" for help. 
   
   
    
    digoal=> create temp table t(id text,id2 int); 
   
   
    
    CREATE TABLE 
   
  
  
  
  
   
    
    digoal=> select relname,oid from pg_class where relname='t'; 
   
   
    
    relname | relnamespace | oid  
   
   
    
    ---------+--------------+------- 
   
   
    
    t    |    11194 | 41206 
   
   
    
    t    |    41192 | 41209 
   
   
    
    (2 rows) 
   
   
    
    digoal=> select nspname from pg_namespace where oid in (11194, 41192); 
   
   
    
     nspname  
   
   
    
    ----------- 
   
   
    
    pg_temp_1 
   
   
    
    pg_temp_2 
   
   
    
    (2 rows)

会话3

  
  
   
   pg9.2.0@db-172-16-3-150-> psql digoal digoal
  
  
  
  
   
   psql (9.2.0)
  
  
  
  
   
   Type "help" for help.
  
  
  
  
   
   digoal=> create temp table t(id text,id2 int,info text);
  
  
  
  
   
   CREATE TABLE
  
  
  
  
   
   digoal=> select relname,oid from pg_class where relname='t';
  
  
  
  
   
   relname | relnamespace | oid 
  
  
  
  
   
   ---------+--------------+-------
  
  
  
  
   
   t    |    11194 | 41206
  
  
  
  
   
   t    |    41192 | 41209
  
  
  
  
   
   t    |    41215 | 41217
  
  
  
  
   
   (3 rows)
  
  
  
  
   
   digoal=> select nspname from pg_namespace where oid in (11194, 41192, 41215);
  
  
  
  
   
    nspname 
  
  
  
  
   
   -----------
  
  
  
  
   
   pg_temp_1
  
  
  
  
   
   pg_temp_2
  
  
  
  
   
   pg_temp_3
  
  
  
  
   
   (3 rows)

3. 临时表可以选择在事务结束后删除数据或者保留数据或者删除表.

   
   
    
    digoal=> begin;
   
   
   
   
    
    BEGIN
   
   
   
   
    
     
     digoal=> create temp table test (id int) on commit preserve rows; 
    
    
     
     CREATE TABLE 
    
    
     
     digoal=> create temp table test1 (id int) on commit delete rows; 
    
    
     
     CREATE TABLE 
    
   
   
   
   
    
     
     digoal=> create temp table test2 (id int) on commit drop; 
    
    
     
     CREATE TABLE 
    
    
     
     digoal=> select relname,oid from pg_class where relname in ('test', 'test1', 'test2'); 
    
    
     
     relname | relnamespace | oid  
    
    
     
     ---------+--------------+------- 
    
    
     
     test  |    41215 | 41223 
    
    
     
     test1  |    41215 | 41226 
    
    
     
     test2  |    41215 | 41232 
    
    
     
     (3 rows) 
    
   
   
   
   
    
     
     digoal=> insert into test values (1); 
    
    
     
     INSERT 0 1 
    
    
     
     digoal=> insert into test1 values (1); 
    
    
     
     INSERT 0 1 
    
    
     
     digoal=> commit; 
    
    
     
     COMMIT

事务提交后test2已经被自动drop掉了.

    
    
     
     digoal=> select relname, 'test2');
    
    
    
    
     
     relname | relnamespace | oid 
    
    
    
    
     
     ---------+--------------+-------
    
    
    
    
     
     test  |    41215 | 41223
    
    
    
    
     
     test1  |    41215 | 41226
    
    
    
    
     
     (2 rows)

test的数据事务提交后数据保留.

    
    
     
     digoal=> select * from test;
    
    
    
    
     
     id
    
    
    
    
     
     ----
    
    
    
    
     
      1
    
    
    
    
     
     (1 row)

test1的数据事务提交后数据已删除.

    
    
     
     digoal=> select * from test1;
    
    
    
    
     
     id
    
    
    
    
     
     ----
    
    
    
    
     
     (0 rows)

test2在事务提交后表已删除.

    
    
     
     digoal=> select * from test2;
    
    
    
    
     
     ERROR: relation "test2" does not exist
    
    
    
    
     
     LINE 1: select * from test2;
    
    
    
    
     
                ^

4. 如果有临时表和非临时表重名了,那么默认是使用临时表的,如果要使用非临时表,需要带上schema,如schema.table.

   
   
    
    digoal=> create table dup_table_name (id int);
   
   
   
   
    
    CREATE TABLE
   
   
   
   
    
    digoal=> create temp table dup_table_name (id int);
   
   
   
   
    
    CREATE TABLE
   
   
   
   
    
    digoal=> insert into digoal.dup_table_name values (1);
   
   
   
   
    
    INSERT 0 1
   
   
   
   
    
    digoal=> select * from dup_table_name ;
   
   
   
   
    
    id
   
   
   
   
    
    ----
   
   
   
   
    
    (0 rows)
   
   
   
   
    
    digoal=> insert into dup_table_name values (2);
   
   
   
   
    
    INSERT 0 1
   
   
   
   
    
    digoal=> select * from dup_table_name ;
   
   
   
   
    
    id
   
   
   
   
    
    ----
   
   
   
   
    
     2
   
   
   
   
    
    (1 row)
   
   
   
   
    
    digoal=> select * from digoal.dup_table_name ;
   
   
   
   
    
    id
   
   
   
   
    
    ----
   
   
   
   
    
     1
   
   
   
   
    
    (1 row)

5. 临时表上创建的索引也是临时的.

   
   
    
    digoal=> create index idx_test on dup_table_name (id);
   
   
   
   
    
    CREATE INDEX
   
   
   
   
    
    digoal=> \d dup_table_name
   
   
   
   
    
    Table "pg_temp_3.dup_table_name"
   
   
   
   
    
    Column | Type  | Modifiers
   
   
   
   
    
    --------+---------+-----------
   
   
   
   
    
    id   | integer |
   
   
   
   
    
    Indexes:
   
   
   
   
    
      "idx_test" btree (id)
   
   
   
   
    
     
   
   
   
   
    
    digoal=> \di idx_test
   
   
   
   
    
             List of relations
   
   
   
   
    
     Schema  |  Name  | Type | Owner |   Table   
   
   
   
   
    
    -----------+----------+-------+--------+----------------
   
   
   
   
    
    pg_temp_3 | idx_test | index | digoal | dup_table_name
   
   
   
   
    
    (1 row)

6. 临时表无法选择性的创建在某个schema下面,它是存在于临时schema的,例如pg_temp_?. 对应的TOAST表也在临时的schema下,例如(pg_toast_temp_?) . 虽然无法选择schema但是tablespace是可以指定的.

   
   
    
    digoal=> create temp table digoal.tmp_test (id int);
   
   
   
   
    
    ERROR: cannot create temporary relation in non-temporary schema

7. Postgresql 中临时表的统计信息不会被autovacuum daemo自动收集. 所以如果有复杂查询的话,最好再有DML后自己执行analyze.

【小结】

1. 如果有临时表和非临时表重名了,如schema.table.

2. 临时表上创建的索引也是临时的.

3. 临时表无法选择性的创建在某个schema下面,例如(pg_toast_temp_?) . 虽然无法选择schema但是tablespace是可以指定的.

4. Postgresql 中临时表的统计信息不会被autovacuum daemo自动收集. 所以如果有索引的情况下,最好再有DML后自己执行analyze.

【参考】

PostgreSQL 临时表

猜你在找的Postgre SQL相关文章