postgresql----TEMPORARY TABLE和UNLOGGED TABLE

in 网站建设
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

一.TEMPORARY|TEMP TABLE

会话级或事务级的临时表,临时表在会话结束或事物结束自动删除,任何在临时表上创建的索引也会被自动删除。除非用模式修饰的名字引用,否则现有的同名永久表在临时表存在期间,在本会话或事务中是不可见的。另外临时表对其他会话也是不可见的,但是会话级的临时表也可以使用临时表所在模式修饰的名字引用。

创建临时表的语法:

CREATE TEMP tbl_name()ON COMMIT{PRESERVE ROWS|DELETE ROWS|DROP};

PRESERVE ROWS:默认值,事务提交后保留临时表和数据

DELETE ROWS:事务提交后删除数据,保留临时表

DROP:事务提交后删除表

示例1

会话A:

创建临时表

test=# create temp table tbl_temp(a int); CREATE TABLE

会话B:

1.在会话B查询临时表tbl_temp,提示表不存在

test=# select * from tbl_temp;
ERROR: relation "tbl_temp" does not exist
LINE 1: select * from tbl_temp;

2.但是在会话B查询pg_class中可以查到tbl_temp的记录

test=# select relname,relnamespace from pg_class where relname = 'tbl_temp';
relname | relnamespace ----------+--------------
tbl_temp | 16488 (1 row)

3.从上述查询结果中可以看到临时表tbl_temp属于16488的模式

test=# select nspname from pg_namespace where oid = 16488;
nspname -----------
pg_temp_3
(1 row)

4.直接使用模式修饰的表名访问成功

test=# select * from pg_temp_3.tbl_temp ;
a ---
(0 rows)

会话A:

退出会话A

会话B:

再次查询tbl_temp时提示不存在

test=# select * from pg_temp_3.tbl_temp ;
ERROR: relation "pg_temp_3.tbl_temp" does not exist
LINE 1: select * from pg_temp_3.tbl_temp ; ^

示例2.创建ON COMMIT DELETE ROWS的临时表

复制代码; "复制代码")

test=# begin ; BEGIN test=# create temp table tbl_temp(a int) on commit delete rows; CREATE TABLE test=# insert into tbl_temp values (1); INSERT 0 1 test=# select * from tbl_temp ;
a ---
1 (1 row)

test=# commit ; COMMIT test=# select * from tbl_temp ;
a ---
(0 rows)

复制代码; "复制代码")

示例3.创建ON COMMIT DROP临时表

复制代码; "复制代码")

test=# begin ; BEGIN test=# create temp table tbl_temp(a int) on commit drop; CREATE TABLE test=# commit ; COMMIT test=# select * from tbl_temp;
ERROR: relation "tbl_temp" does not exist
LINE 1: select * from tbl_temp; ^

复制代码; "复制代码")

示例4.查询数据库中所有临时表

复制代码; "复制代码")

test=# select relname,nspname from pg_class join pg_namespace on(relnamespace=pg_namespace.oid) where pg_is_other_temp_schema(relnamespace);
relname | nspname ----------+-----------
tbl_test | pg_temp_2
(1 row)

复制代码; "复制代码")

二.UNLOGGED TABLE

unlogged table是为临时数据设计的,写入性能较高,但是当postgresql进程崩溃时会丢失数据。

创建一张普通表test和一张unlogged表test,测试性能情况

普通表:

复制代码; "复制代码")

test=# create table test(a int); CREATE TABLE test=# \timing
Timing is on.
test=# insert into test select generate_series(1,1000000); INSERT 0 1000000 Time: 3603.715 ms

复制代码; "复制代码")

unlogged表

复制代码; "复制代码")

test=# create unlogged table testu(a int); CREATE TABLE Time: 12.920 ms
test=# insert into testu select generate_series(1,1000000); INSERT 0 1000000 Time: 801.376 ms

复制代码; "复制代码")

比较以上两个结果,unlogged表的写性能是普通表的4.5倍。

杀死postgresql的主进程,重启DB服务

复制代码; "复制代码")

[root@MiWiFi-R1CL-srv ~]# ps -elf | grep postgres 0 S postgres 2129 1 0 80 0 - 66830 poll_s 04:24 ? 00:00:00 /opt/pg9.6/bin/postgres -D /mnt/pgdata 1 S postgres 2130 2129 0 80 0 - 29645 ep_pol 04:24 ? 00:00:00 postgres: logger process 1 S postgres 2132 2129 0 80 0 - 66898 poll_s 04:24 ? 00:00:00 postgres: checkpointer process 1 S postgres 2133 2129 0 80 0 - 66830 ep_pol 04:24 ? 00:00:00 postgres: writer process 1 S postgres 2134 2129 0 80 0 - 66871 ep_pol 04:24 ? 00:00:00 postgres: wal writer process 1 S postgres 2135 2129 0 80 0 - 66954 ep_pol 04:24 ? 00:00:00 postgres: autovacuum launcher process 1 S postgres 2136 2129 0 80 0 - 29677 ep_pol 04:24 ? 00:00:00 postgres: stats collector process 0 S root 2262 2099 0 80 0 - 28768 n_tty_ 04:52 pts/1 00:00:00 /opt/pg9.6/bin/psql -d test -U postgres 1 S postgres 2264 2129 0 80 0 - 67351 ep_pol 04:52 ? 00:00:02 postgres: postgres test [local] idle 0 S root 2334 2198 0 80 0 - 25813 pipe_w 05:15 pts/2 00:00:00 grep postgres
[root@MiWiFi-R1CL-srv ~]# kill -9 2129 [root@MiWiFi-R1CL-srv ~]# rm -rf /mnt/pgdata/postmaster.pid
[root@MiWiFi-R1CL-srv ~]# su -l postgres -c '/opt/pg9.6/bin/pg_ctl -D /mnt/pgdata start' server starting
[root@MiWiFi-R1CL-srv ~]# 2016-06-22 05:16:04.399 CST 2372 LOG: redirecting log output to logging collector process 2016-06-22 05:16:04.399 CST 2372 HINT: Future log output will appear in directory "/var/log/pg_log".

复制代码; "复制代码")

再次查询unlogged表testu,发现数据已丢失

test=# select * from testu ;
a ---
(0 rows)

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看