POSTGRESQL 创建一个表到底有什么说的? 可说的挺多的

创建一张表,到底有什么说的, 下面是POSTGRESQL 创建数据表的官方文档的内容截图. 那我们就往下看,到底我们可以说点什么

建表的开头是关于临时表的问题,其中临时表的global 和 local,在目前的V12的版本中并没有具体的含义,

问题1 ， POSTGRESQL 怎么创建一个看似global 的tempary table

问题2,     POSTGRESQL 对于表是否可以有无日志表,有什么影响

问题3，  POSTGRESQL 创建了实体表和临时表的表名是一致的情况下到底访问那个表

问题4，  创建表的时候需要考虑INDEX 的问题吗， 我是不是应该把INDEX 放到另外的一个表空间

问题1 ,  正如上面所说,POSTGRESQL 截止目前为止是没有GLOBAL 的数据临时表的, 那么我们如果想建立一个在其他数据库意义上的GLOBAL 的临时表怎么办.  其实问题1 和 问题 2 是可以合并进行回答的

方法1, 使用商业数据库的版本 EDB, 他们支持GLOBAL TEMP 数据表

方法2, 使用POSTGRESQL 的无日志表来模拟所谓的GLOBAL 表

POSTGRESQL 的UNLOGGED TABLE, 这个功能是在POSTGRESQL 9.1 上开始的,主要的原因也是为了某些数据的写入的性能.  通过UNLOGGED TABLE 来解决的原因是,性能的问题, 我们都知道临时表是没有日志写入的,这点提高了临时表的性能,那么PG 中的UNLOGGED TABLE 本身就是在操作中不记录日志,这与  TEMP 表的实现方式类似.  或者可以想一下,我们的应用有这样一种需求,就是频繁写入日志,但我们对这样的表并不需要保证数据的安全,但我们需要日志表写入的速度,并且这样的表不能影响到数据库的I/O,因为数据在写入的时候会造成WAL LOG 的写入,如何减轻数据写入的压力.

那到底这个UNLOGGED TABLE 是不是我们需要的,回答是当然,我们付出的是数据在写入磁盘时,没有WAL LOG的保护,但换来的是整体性能的提升,对于静态数据或者日志等数据,或者对大量写入更新的表,在业务可以忍受,服务器以外CRASH后的部分数据丢失的情况下,是可以使用的.

CREATE UNLOGGED Table  log_t  (id int, system_info text);

SELECT * FROM pg_class WHERE relpersistence = 'u';

同我们也可以设置将已经unlogged表变为 logged , 通过UNLOGGED 可以模拟GLOBAL 临时表,在使用完毕后,可以直接DROP.

问题 3  POSTGRESQL 创建了实体表和临时表的表名是一致的情况下到底访问那个表

首先我们先建立一个容纳测试表的数据库

CREATE DATABASE class

    WITH 

    OWNER = postgres

    TEMPLATE = template1

    ENCODING = 'UTF8'

    LC_COLLATE = 'en_US.UTF-8'

    LC_CTYPE = 'en_US.UTF-8'

    TABLESPACE = pg_default

    CONNECTION LIMIT = -1;

COMMENT ON DATABASE class

    IS 'pg_class create table';

CREATE TEMPORARY TABLE tempory_table(
   id int,
   system_info text
);
CREATE TABLE tempory_table (
   id int,
   system_info text,
   type_d      varchar(10)
);
insert into tempory_table (id,system_info)  values (1,'system_info');
insert into tempory_table (id,system_info,type_d)  values (2,'system_info','type_d');
select * from tempory_table;
select * from pg_tables where tablename = 'tempory_table';
select * from tempory_table;

我们建立临时表和实体表,同名的数据表,在实际当中进行访问, 我们可以看到实际上访问的数据表并不是实体表而是临时表.我们也叫SESSION 临时表,我们换一个SESSION 访问这个表看我们实际上访问的是那个表.

实际上同名的临时表和实体表,在同一个生成临时表的SESSION的位置,访问同一个表名是会访问我们的临时表.  

所以在建表时,强烈不建议 临时表和实体表的表名是一样的. 比如临时表建立在前面加入一个标识.   而实际上允许同一个表名在同一个数据库中存在的基础是,不同的schemaname, 普通表默认是建立在public的数据schema的基础上.

同时临时表还有很多候选项,例如在建表的时候我们使用 on commit drop;

但是实际上我们看到并没有此表,原因在于我们使用commit drop;

问题4：这个问题实际上要根据实际情况来，比如服务器上的磁盘空间，或者有高性能磁盘等，希望索引和数据分别存储在两个物理位置提高性能的项目的确是可以这样去做。

首先需要建立一个特别存放index 的 table space 

 create tablespace index owner current_user location '/pgdata/data_index';

这样的情况下，创建的索引可以存储在INDEX 的特殊的文件夹中