Postgresql Temporary Table Study 007 Tips
Postgresql 创建表-临时表
临时表
进入postgres
[root@centos7_pg ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
0b35109c39c9 postgres_10_centos7.7.1908 "docker-entrypoint.s…" 41 hours ago Exited (255) 2 minutes ago 0.0.0.0:5431->5432/tcp pgtest01
[root@centos7_pg ~]# docker start 0b35109c39c9
0b35109c39c9
[root@centos7_pg ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
0b35109c39c9 postgres_10_centos7.7.1908 "docker-entrypoint.s…" 41 hours ago Up 4 seconds 0.0.0.0:5431->5432/tcp pgtest01
[root@centos7_pg ~]# docker exec -it pgtest01 /bin/bash
[root@0b35109c39c9 /]# ps -ef
UID PID PPID C STIME TTY TIME CMD
root 1 0 0 07:25 ? 00:00:00 /bin/bash /usr/local/bin/docker-entrypoint.sh postgres
root 11 1 0 07:25 ? 00:00:00 su - postgres -c /usr/pgsql-10/bin/postgres -D /var/lib/pgsql/10/data -c config_file=/var/lib/pgsql/10/data/postgresql.conf
postgres 12 11 0 07:25 ? 00:00:00 /usr/pgsql-10/bin/postgres -D /var/lib/pgsql/10/data -c config_file=/var/lib/pgsql/10/data/postgresql.conf
postgres 25 12 0 07:25 ? 00:00:00 postgres: logger process
postgres 27 12 0 07:25 ? 00:00:00 postgres: checkpointer process
postgres 28 12 0 07:25 ? 00:00:00 postgres: writer process
postgres 29 12 0 07:25 ? 00:00:00 postgres: wal writer process
postgres 30 12 0 07:25 ? 00:00:00 postgres: autovacuum launcher process
postgres 31 12 0 07:25 ? 00:00:00 postgres: stats collector process
postgres 32 12 0 07:25 ? 00:00:00 postgres: bgworker: logical replication launcher
root 33 0 0 07:26 pts/0 00:00:00 /bin/bash
root 46 33 0 07:26 pts/0 00:00:00 ps -ef
[root@0b35109c39c9 /]#
创建表的语法:
postgres-# \h create table
Command: CREATE TABLE
Description: define a new table
Syntax:
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 [, ... ] ) ]
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
OF type_name [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ]
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
PARTITION OF parent_table [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ] FOR VALUES partition_bound_spec
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and like_option is:
{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
and partition_bound_spec is:
IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
exclude_element in an EXCLUDE constraint is:
{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
postgres-#
创建普通表/临时(session/transaction级别之分)/全局/本地/不记日志表
临时表:
temporary / temp : 临时的，自动在session之后删除 ，如果指定了 on commit 则是根据事务来是删除，还是只删除数据或者全部消失
- ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }
Session 01:
postgres=# create temporary table tmp_t1(id int,info text);
CREATE TABLE
postgres=# select * from pg_tables where tablename='tmp_t1';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
pg_temp_3 | tmp_t1 | postgres | | f | f | f | f
(1 row)
postgres=#
postgres=# select * from tmp_t1;
id | info
----+------
(0 rows)
postgres=# insert into tmp_t1 values(1,'first');
INSERT 0 1
postgres=# select * from tmp_t1;
id | info
----+-------
1 | first
(1 row)
postgres=#
Session 02: 可以通过pg_tables查看tmp_t1表，但是不能通过系统视图查看。
postgres=# select * from pg_tables where tablename='tmp_t1';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
pg_temp_3 | tmp_t1 | postgres | | f | f | f | f
(1 row)
postgres=# \d tmp_t1;
Did not find any relation named "tmp_t1".
postgres=# select * from tmp_t1;
ERROR: relation "tmp_t1" does not exist
LINE 1: select * from tmp_t1;
^
postgres=#
Session 01: 退出当前session之后 tmp_t1 则自动消失了
postgres=# select * from tmp_t1;
id | info
----+-------
1 | first
(1 row)
postgres=#
postgres-# \q
-bash-4.2$
-bash-4.2$ psql
psql (10.12)
Type "help" for help.
postgres=# select * from tmp_t1;
ERROR: relation "tmp_t1" does not exist
LINE 1: select * from tmp_t1;
^
postgres=#
-
Oracle 的临时表会话结束后，数据会消失，但是表的定义不会自动删除
-
Postgres 的临时表最长生命周期就是在 session 中，退出 session 就消失了(数据和表的定义)
另外，Postgresql 的临时表还可以在 session 中的以 transaction 的单位，可以控制临时表在事务结束之后的存在形式
ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }
临时时表是用了on commit drop选项 : 意味着事务结束时，自动删除临时表,如下示例
-
在Postgresl中，创建临时表的ddl语句被认为一个事务，所以示例虽然创建语句成功了，表也创建了，但随后，事务结束后被很快又删除了，所以也看不到临时表的定义
-
可以通过begin/end语句来限制事务块中临时表的使用
postgres=# create temporary table tmp_t2(id int, info text) on commit drop;
CREATE TABLE
postgres=# \d tmp_t2;
Did not find any relation named "tmp_t2".
postgres=#
postgres=# select * from pg_tables where tablename like 'tmp_t%';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
(0 rows)
postgres=#
在事务块中的示例:
postgres=# begin;
BEGIN
postgres=# create temporary table tmp_t2(id int, info text) on commit drop;
CREATE TABLE
postgres=# \d tmp_t2;
Table "pg_temp_4.tmp_t2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
info | text | | |
postgres=# insert into tmp_t2 values(1,'first');
INSERT 0 1
postgres=# select * from tmp_t2;
id | info
----+-------
1 | first
(1 row)
postgres=# commit;
COMMIT
postgres=# end;
WARNING: there is no transaction in progress
COMMIT
postgres=# \d tmp_t2;
Did not find any relation named "tmp_t2".
postgres=# select * from tmp_t2;
ERROR: relation "tmp_t2" does not exist
LINE 1: select * from tmp_t2;
^
postgres=# select * from pg_tables where tablename like 'tmp_t%';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
(0 rows)
postgres=#
除了通过on commit drop选项可以设置事务结束临时表就消失外，还可以通过on commit相关选项来控制临时表的数据在事务结束消失（仅仅数据消失，定义还存在）和会话结束消失（数据和表定义都消失）
如下，当前session中创建了tmp_t3 (on commit delete rows) 在之后事务中可以查看和插入数据，但是事务结束(commit)之后数据消失了，如果是 on commit preserve rows 的选项，定义和数据都不会消失，
但是最后退出session之后，全部都消失
postgres=# create temporary table tmp_t3(id int, info text) on commit delete rows;
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into tmp_t3 values(1,'frist');
INSERT 0 1
postgres=# select * from tmp_t3;
id | info
----+-------
1 | frist
(1 row)
postgres=# end;
COMMIT
postgres=#
postgres=# select * from tmp_t3;
id | info
----+------
(0 rows)
postgres=#
对临时表能收集统计信息吗？
由于 autovacuum 进行不能访问临时表，所以不能自动vacuum和analyze临时表，
需要在session中通过sql语句去vacuum和analyze临时表。
Have a good work&life! 2020/09 via LinHong