PostgreSQL 数据库管理

最新推荐文章于 2024-05-13 23:33:20 发布

文档搬运工

最新推荐文章于 2024-05-13 23:33:20 发布

阅读量771

点赞数

分类专栏： Postgres SQL

本文链接：https://blog.csdn.net/xxzhaobb/article/details/113183637

版权

Postgres SQL 专栏收录该内容

39 篇文章 6 订阅

订阅专栏

参考文档： PostgreSQL 9 Administration Cookbook （第二版）中文版第七章数据库管理

-- 事务，要么全部成功，要么全部失败

BEGIN;
command 1;
command 2;
command 3;
COMMIT;

-- 在psql客户端，可以使用-1 ，--single-transaction 来表示使用事务

bash $ psql -1 -f myscript.sql
bash $ psql --single-transaction -f myscript.sql

-- psql的帮助，提示是-1（数字1）

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "postgres")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit

-- if exists 和 or replace

DROP VIEW IF EXISTS cust_view;
CREATE OR REPLACE VIEW cust_view AS SELECT * FROM cust; 
drop index if exists  idx_1;

-- 如果改变函数，或者视图输出的定义，则即使有or replace也不行。需要先drop，再create

CREATE OR REPLACE VIEW emp_view AS SELECT empname as title1 FROM emp;
CREATE OR REPLACE VIEW emp_view AS SELECT empname as title2 FROM emp;

mydb=# CREATE OR REPLACE VIEW emp_view AS SELECT empname as title1 FROM emp;
CREATE VIEW
mydb=# CREATE OR REPLACE VIEW emp_view AS SELECT empname as title2 FROM emp;
ERROR:  cannot change name of view column "title1" to "title2"
mydb=#

-- postgres不支持嵌套事务

mydb=# begin;
BEGIN
mydb=# create table a(x int);
CREATE TABLE
mydb=# begin;    -- 这个语句，其实被pg忽略掉了 
WARNING:  there is already a transaction in progress
BEGIN
mydb=# create table b(x int);
CREATE TABLE
mydb=# commit;
COMMIT
mydb=# rollback;
WARNING:  there is no transaction in progress
ROLLBACK
mydb=#

-- 不能包含在事务中的语句

CREATE DATABASE / DROP DATABASE
CREATE TABLESPACE / DROP TABLESPACE
CREATE INDEX CONCURRENTLY
VACUUM
REINDEX DATABASE / REINDEX  SYSTEM 
CLUSTER

-- psql脚本工具的默认模式是当遇到错误时继续处理，这样的目的是为了历史兼容性。

-- 编辑一个脚本，然后执行，会发现，即使错误了，也一直执行下去

-bash-4.2$ more /tmp/test.sql 
mistake1;
mistake2;
mistake3;
-bash-4.2$ 

-bash-4.2$ psql -f '/tmp/test.sql'
psql.bin:/tmp/test.sql:1: ERROR:  syntax error at or near "mistake1"
LINE 1: mistake1;
        ^
psql.bin:/tmp/test.sql:2: ERROR:  syntax error at or near "mistake2"
LINE 1: mistake2;
        ^
psql.bin:/tmp/test.sql:3: ERROR:  syntax error at or near "mistake3"
LINE 1: mistake3;
        ^
-bash-4.2$

-- 让脚本，遇到错误后，立刻退出，可以在执行的时候，加上ON_ERROR_STOP=on，或者在脚本中加上。ON_ERROR_STOP是psql的一个特殊命令，控制脚本执行psql的行为模式。
-- help中的 -v参数

  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
						   
-bash-4.2$ psql -f '/tmp/test.sql' -v ON_ERROR_STOP=1
psql.bin:/tmp/test.sql:1: ERROR:  syntax error at or near "mistake1"
LINE 1: mistake1;
        ^
-bash-4.2$ psql -f '/tmp/test.sql' -v ON_ERROR_STOP=ON
psql.bin:/tmp/test.sql:1: ERROR:  syntax error at or near "mistake1"
LINE 1: mistake1;
        ^
-bash-4.2$ 

-bash-4.2$ more /tmp/test.sql 
\set ON_ERROR_STOP
mistake1;
mistake2;
mistake3;
-bash-4.2$ psql -f '/tmp/test.sql' 
psql.bin:/tmp/test.sql:2: ERROR:  syntax error at or near "mistake1"
LINE 1: mistake1;
        ^
-bash-4.2$					   

-bash-4.2$ psql -f '/tmp/test.sql' -v ON_ERROR_STOP     -- 这个遇到错误不会退出，因为缺少参数ON，或者1  。
psql.bin:/tmp/test.sql:1: ERROR:  syntax error at or near "mistake1"
LINE 1: mistake1;
        ^
psql.bin:/tmp/test.sql:2: ERROR:  syntax error at or near "mistake2"
LINE 1: mistake2;
        ^
psql.bin:/tmp/test.sql:3: ERROR:  syntax error at or near "mistake3"
LINE 1: mistake3;
        ^
-bash-4.2$

-- 可以将ON_ERROR_STOP添加到配置文件中

-bash-4.2$ psql -f '/tmp/test.sql'
psql.bin:/tmp/test.sql:1: ERROR:  syntax error at or near "mistake1"
LINE 1: mistake1;
        ^
-bash-4.2$ more .psqlrc   -- 添加以后，遇到错误退出。
\set ON_ERROR_STOP
-bash-4.2$ psql -X -f '/tmp/test.sql'   -- 如果想覆盖掉配置文件中的内容，可以使用-X参数 。
psql.bin:/tmp/test.sql:1: ERROR:  syntax error at or near "mistake1"
LINE 1: mistake1;
        ^
psql.bin:/tmp/test.sql:2: ERROR:  syntax error at or near "mistake2"
LINE 1: mistake2;
        ^
psql.bin:/tmp/test.sql:3: ERROR:  syntax error at or near "mistake3"
LINE 1: mistake3;
        ^
-bash-4.2$ 

-X, --no-psqlrc          do not read startup file (~/.psqlrc)   -- help中 -X参数的说明

-- 使用脚本生成脚本（类似oracle中的|| 来生成执行脚本）。创建三个表，使用脚本生成修改三个表的语句。

create schema test;
create table test.a (col1 INTEGER);
create table test.b (col1 INTEGER);
create table test.c (col1 INTEGER);

SELECT relname FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = 'test';
mydb=# SELECT relname FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = 'test';
 relname 
---------
 a
 b
 c
(3 rows)

mydb=# 

\t on   -- \t [on|off]            show only rows (currently off)
\o '/tmp/multi.sql'  -- \o [FILE]              send all query results to file or |pipe

SELECT 'ALTER TABLE '|| n.nspname || '.' || c.relname || ' ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE;'
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = 'test';

mydb=# \o '/tmp/multi.sql'
mydb=# SELECT 'ALTER TABLE '|| n.nspname || '.' || c.relname || ' ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE;'
mydb-# FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = 'test';
mydb=# \! '/tmp/multi.sql'
sh: /tmp/multi.sql: Permission denied
mydb=# \! more '/tmp/multi.sql'
 ALTER TABLE test.a ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE;
 ALTER TABLE test.b ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE;
 ALTER TABLE test.c ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE;

mydb=# 

\i multi.sql  -- 批量执行sql

-- 查看修改后的表结构

mydb=# \d test.a
                                  Table "test.a"
        Column         |           Type           | Collation | Nullable | Default 
-----------------------+--------------------------+-----------+----------+---------
 col1                  | integer                  |           |          | 
 last_update_timestamp | timestamp with time zone |           |          | 

mydb=# \d test.b
                                  Table "test.b"
        Column         |           Type           | Collation | Nullable | Default 
-----------------------+--------------------------+-----------+----------+---------
 col1                  | integer                  |           |          | 
 last_update_timestamp | timestamp with time zone |           |          | 

mydb=# \d test.c
                                  Table "test.c"
        Column         |           Type           | Collation | Nullable | Default 
-----------------------+--------------------------+-----------+----------+---------
 col1                  | integer                  |           |          | 
 last_update_timestamp | timestamp with time zone |           |          | 

mydb=#

-- 或者另一种方法，这种方法的缺点是直接执行了，没有生成对应的脚本。

DO $$
DECLARE c record;
BEGIN
FOR c IN SELECT t.*, n.nspname
FROM pg_class c JOIN pg_namespace n
ON c.relnamespace = n.oid
WHERE n.nspname = 'test' /* ; not needed */
LOOP
EXECUTE 'ALTER TABLE '|| quote_ident(n.nspname) ||
'.' || quote_ident(c.relname) ||
' ADD COLUMN last_update_timestamp ' ||
'TIMESTAMP WITH TIME ZONE;'
END LOOP;
END $$;

-- 并行运行，也就是把一个脚本分割成多个脚本 (这个没有执行成功)

\t on  
\o script-:i.sql
SELECT sql FROM (
SELECT 'ALTER TABLE '|| n.nspname || '.' || c.relname ||
' ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE DEFAULT
now();' as sql
,row_number() OVER (ORDER BY pg_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n
ON c.relnamespace = n.oid
WHERE n.nspname = 'test'
ORDER BY 2 DESC) as s
WHERE row_number % 2 = :i;
\o

-- 然后生成两个脚本

psql -v i=0 -f make-script.sql 
psql -v i=1 -f make-script.sql

-- 然后执行

psql -f script-0.sql &
psql -f script-1.sql &

-- 生成两个文件，分别执行（这个执行成功了）

\t on
\o script0.sql
SELECT sql FROM (
SELECT 'ALTER TABLE '|| n.nspname || '.' || c.relname ||
' ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE DEFAULT
now();' as sql
,row_number() OVER (ORDER BY pg_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n
ON c.relnamespace = n.oid
WHERE n.nspname = 'test'
ORDER BY 2 DESC) as s
WHERE row_number % 2 = 0;
\o script1.sql
SELECT sql FROM (
SELECT 'ALTER TABLE '|| n.nspname || '.' || c.relname ||
' ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE DEFAULT
now();' as sql
,row_number() OVER (ORDER BY pg_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n
ON c.relnamespace = n.oid
WHERE n.nspname = 'test'
ORDER BY 2 DESC) as s
WHERE row_number % 2 = 1;

pg_batch, 一个工具，还是调用上面的脚本。

-- 添加删除表上的列。添加或者删除表上的列，会持有全表所，有可能会运行很长时间。

ALTER TABLE mytable ADD COLUMN last_update_timestamp TIMESTAMP WITHOUT TIME ZONE;
ALTER TABLE mytable DROP COLUMN last_update_timestamp;

ALTER TABLE mytable DROP COLUMN IF EXISTS last_update_timestamp, ADD COLUMN last_update_timestamp TIMESTAMP WITHOUT TIME ZONE;  -- 使用alter合并多个操作
UPDATE mytable SET last_update_timestamp = NULL;  -- 等同于上面的语句

drop column实际上并没有从表中的每一行删除列，而是将这个列标记为已删除，所以drop column会感觉很快。
如果增加了一个允许为空并且默认值是空的列，则add column会很快。如果使用了非空约束，或者明确指定了默认值，则需要写入表中的每一行，会导致很慢。
alter table允许一次执行多个列的操作。alter table命令被优化过，所以所有的列操作都将在对表进行一次扫描侯就可以完成。
ALTER TABLE mytable ADD COLUMN last_update_userid INTEGER, ADD COLUMN last_update_comment TEXT; -- 一次性操作完毕

列被删除时候，其上的索引也开会被自动删除。当列上的其他对象（如外键）会导致alter table命令被拒绝，则可以使用cascade 。
ALTER TABLE x DROP COLUMN last_update_timestamp CASCADE;

-- 修改列

CREATE TABLE birthday( name TEXT, dob INTEGER);
insert into birthday values('simon',690926);

ALTER TABLE birthday ALTER COLUMN dob SET DATA TYPE text;

mydb=# \d birthday
            Table "public.birthday"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 name   | text |           |          | 
 dob    | text |           |          | 

mydb=# 

ALTER TABLE birthday ALTER COLUMN dob SET DATA TYPE integer;

mydb=# ALTER TABLE birthday ALTER COLUMN dob SET DATA TYPE integer;
ERROR:  column "dob" cannot be cast automatically to type integer
HINT:  You might need to specify "USING dob::integer".
mydb=# 

ALTER TABLE birthday ALTER COLUMN dob SET DATA TYPE integer USING dob::integer;  -- 使用using从句进行显示转换
mydb=# ALTER TABLE birthday ALTER COLUMN dob SET DATA TYPE integer USING dob::integer;
ALTER TABLE
mydb=# \dS birthday
              Table "public.birthday"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 name   | text    |           |          | 
 dob    | integer |           |          | 

mydb=#

-- dob::text的意思，是转换成文本，（无法将整型integer转换成时间类型date，需要先转换成text，再转换成date日期类型，一旦有了文本类型，就可以使用to_date函数了）
开始的数据是690926，认为格式是YYMMDD。PG转换这个日期的时候，认为69属于当前世纪，会被转换为2069而不是1969 。使用case语句显式地剪掉100，将年份再16和69之间的日志前移一个世纪。
ALTER TABLE birthday ALTER COLUMN dob SET DATA TYPE date USING date(to_date(dob::text, 'YYMMDD') - (case when dob/10000 < 15 then interval '0' else interval '100 years' end));

mydb=# ALTER TABLE birthday ALTER COLUMN dob SET DATA TYPE date USING date(to_date(dob::text, 'YYMMDD') - (case when dob/10000 < 15 then interval '0' else interval '100 years' end));
ALTER TABLE
mydb=# \dS birthday
            Table "public.birthday"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 name   | text |           |          | 
 dob    | date |           |          | 

mydb=# select * from birthday;
 name  |    dob     
-------+------------
 simon | 1969-09-26
(1 row)

mydb=#

-- 在PG中，无论是否有not null约束，都可以设置或者删除字段的默认表达式

ALTER TABLE foo ALTER COLUMN col DROP DEFAULT expression;
ALTER TABLE foo ALTER COLUMN col SET DEFAULT 'expression';
ALTER TABLE foo ALTER COLUMN col SET NOT NULL;
ALTER TABLE foo ALTER COLUMN col DROP NOT NULL;

-- 添加或删除模式schema
-- 添加一个新的模式

CREATE SCHEMA sharedschema;

-- 添加一个新的模式，该模式被指定的用户拥有

CREATE SCHEMA sharedschema AUTHORIZATION scarlett;

-- 创建一个schema，和当前已存在的用户名相同

CREATE SCHEMA AUTHORIZATION scarlett;

-- 删除一个schema

DROP SCHEMA str;

-- 在任何情况下，都确保模式创建成功

create schema if not exists str;

-- 在不同的schema之间移动对象

alter table cust set schema anotherschema;
alter schema existingschema rename to anotherschema;  -- 移动所有对象

-- 添加、删除表空间

CREATE TABLESPACE new_tablespace LOCATION '/usr/local/pgsql/new_tablespace';
DROP TABLESPACE new_tablespace;  -- 表空间只有为空时，才能被删除

每个表空间都有一个对应的位置。例外的情况，用于共享系统编目的对象和其他对象的pg_global、pg_default表空间，不需要设置位置，直接存放在data目录下的子目录中。

-- 查看每个用户对象的表空间

SELECT spcname
,relname
--,CASE WHEN relistemp THEN 'temp ' ELSE '' END ||           -- pg 9
,CASE WHEN relpersistence='t' THEN 'temp ' ELSE '' END ||    -- PG10
CASE
WHEN relkind = 'r' THEN 'table'
WHEN relkind = 'v' THEN 'view'
WHEN relkind = 'S' THEN 'sequence'
WHEN relkind = 'c' THEN 'type'
ELSE 'index' END as objtype
FROM pg_class c join pg_tablespace ts
ON (CASE WHEN c.reltablespace = 0 THEN
(SELECT dattablespace FROM pg_database
WHERE datname = current_database())
ELSE c.reltablespace END) = ts.oid
WHERE relname NOT LIKE 'pg_toast%'
AND relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE
nspname IN ('pg_catalog', 'information_schema'));

mydb=# SELECT spcname
mydb-# ,relname
mydb-# --,CASE WHEN relistemp THEN 'temp ' ELSE '' END ||           -- pg 9
mydb-# ,CASE WHEN relpersistence='t' THEN 'temp ' ELSE '' END ||    -- PG10
mydb-# CASE
mydb-# WHEN relkind = 'r' THEN 'table'
mydb-# WHEN relkind = 'v' THEN 'view'
mydb-# WHEN relkind = 'S' THEN 'sequence'
mydb-# WHEN relkind = 'c' THEN 'type'
mydb-# ELSE 'index' END as objtype
mydb-# FROM pg_class c join pg_tablespace ts
mydb-# ON (CASE WHEN c.reltablespace = 0 THEN
mydb(# (SELECT dattablespace FROM pg_database
mydb(# WHERE datname = current_database())
mydb(# ELSE c.reltablespace END) = ts.oid
mydb-# WHERE relname NOT LIKE 'pg_toast%'
mydb-# AND relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE
mydb(# nspname IN ('pg_catalog', 'information_schema'));
  spcname   |             relname              | objtype  
------------+----------------------------------+----------
 pg_default | weather                          | table
 pg_default | cities                           | table
 pg_default | shoe                             | view
 pg_default | shoelace                         | view
 pg_default | shoe_ready                       | view
 pg_default | sales_summary_seller             | index
 pg_default | words                            | index
 pg_default | wrd                              | index
 pg_default | wrd_word                         | index
 pg_default | wrd_trgm                         | index
 pg_default | shoelace_log                     | table
 pg_default | t                                | table
 pg_default | orders_pkey                      | index
 pg_default | orderlines_pkey                  | index
 pg_default | x                                | table
 pg_default | orders                           | table
 pg_default | orderlines                       | table
 pg_default | t1                               | table
 pg_default | Myemp                            | table
 pg_default | x                                | table
 pg_default | boxes                            | table
 pg_default | boxes_position_excl              | index
 pg_default | foo_fooid_excl                   | index
 pg_default | t2_id_seq                        | sequence
 pg_default | t2                               | table
 pg_default | iprange                          | table
 pg_default | abc                              | table
 pg_default | myview                           | view
 pg_default | my_first_table                   | table
 pg_default | products                         | table
 pg_default | company                          | table
 pg_default | company_pkey                     | index
 pg_default | capitals                         | table
 pg_default | measurement                      | index
 pg_default | measurement_y2006m02             | table
 pg_default | measurement_y2006m03             | table
 pg_default | measurement_y2007m11             | table
 pg_default | measurement_y2006m02_logdate_idx | index
 pg_default | measurement_y2006m03_logdate_idx | index
 pg_default | measurement_y2007m11_logdate_idx | index
 pg_default | users_id_seq                     | sequence
 pg_default | users                            | table
 pg_default | users_pkey                       | index
 pg_default | invoice                          | table
 pg_default | vw_getfoo                        | view
 pg_default | foo_1                            | table
 pg_default | invoice_pkey                     | index
 pg_default | test1                            | table
 pg_default | test1_id_index                   | index
 pg_default | test2                            | table
 pg_default | test2_mm_idx                     | index
 pg_default | tab_big                          | table
 pg_default | image                            | table
 pg_default | sum_prod                         | type
 pg_default | foo                              | table
 pg_default | tab                              | table
 pg_default | emp1                             | table
 pg_default | sales_summary                    | index
 pg_default | shoe_data                        | table
 pg_default | unit                             | table
 pg_default | shoelace_data                    | table
 pg_default | lines                            | table
 pg_default | b                                | table
 pg_default | emp_audit                        | table
 pg_default | c                                | table
 pg_default | emp                              | table
 pg_default | people_id_seq                    | sequence
 pg_default | people_pkey                      | index
 pg_default | hats_id_seq                      | sequence
 pg_default | hats_pkey                        | index
 pg_default | audit_log                        | table
 pg_default | hats                             | table
 pg_default | people                           | table
 pg_default | emp_view                         | view
 pg_default | a                                | table
 pg_default | b                                | table
 pg_default | a                                | table
 pg_default | birthday                         | table
(78 rows)

mydb=#

-- 为每个表空间设置一个特定的所有者

ALTER TABLESPACE new_tablespace OWNER TO eliza;

-- 为用户设置默认的表空间

ALTER USER eliza SET default_tablespace = 'new_tablespace';

-- 将pg_xlog存放到单独的设备上（PG10 ，是pg_wal）

停止数据库
pg_ctl  stop  
将pg_xlog移动到另一个不同的磁盘设备上
mv $PGDATA/pg_xlog  /mnt/newdisk/
建立一个从旧位置到新位置的软连接
ln -s /mnt/newdisk/pg_xlog $PGDATA/pg_xlog  
重启数据库
pg_ctl start
提交个事务，验证
psql -c 'create table pgxlogtext(x int);

-- 表空间级调优（这个设置大致适用于SSD驱动器，假设驱动器随机读方面比HDD快40倍，顺序读快20倍）

ALTER TABLESPACE new_tablespace SET (seq_page_cost = 0.05, random_page_cost = 0.1);

-- 在不同的表空间之间移动对象（不需要考虑toast对象，当移动表的时候，toast对应也会被移动。但是索引不会被移动，要手动移动）

ALTER TABLE mytable SET TABLESPACE new_tablespace;
ALTER INDEX mytable_val_idx SET TABLESPACE new_tablespace;

-- 一次性移动一个表和该表的所有索引，可以在一个事务中执行（表越大，耗时可能会越长，该过程会产生WAL日志，还会锁表）

BEGIN;
ALTER TABLE mytable SET TABLESPACE new_tablespace;
ALTER INDEX mytable_val1_idx SET TABLESPACE new_tablespace;
ALTER INDEX mytable_val2_idx SET TABLESPACE new_tablespace;
COMMIT;

-- 可以设置缺省的表空间，后续创建的对象，会创建在这个默认的表空间中

set default_tablespace ='new_tablespace';

-- 也可以设置，让所有的用户连接到数据库的时候，自动执行表空间设置

ALTER DATABASE mydb SET default_tablespace = 'new_tablespace';
ALTER DATABASE mydb SET tablespace new_tablespace;  -- 不要执行这个命令，这个命令相当于把不在表空间的对象，移动到这个表空间 。

-- 检查索引是否和表位于同一个表空间（比如，移动表后，发现索引并没有被移动），也可以通过\d t 来查看表和索引是否在同一个表空间（会显示索引所在的表空间）

SELECT i.relname as indexname
,tsi.spcname
,t.relname as tablename
,tst.spcname
FROM (((pg_class t /* tables */
JOIN pg_tablespace tst
ON t.reltablespace = tst.oid)
JOIN pg_index pgi
ON pgi.indrelid = t.oid)
JOIN pg_class i /* indexes */
ON pgi.indexrelid = i.oid)
JOIN pg_tablespace tsi
ON i.reltablespace = tsi.oid
WHERE i.relname NOT LIKE 'pg_toast%'
AND i.reltablespace != t.reltablespace
;

mydb=# SELECT i.relname as indexname
mydb-# ,tsi.spcname
mydb-# ,t.relname as tablename
mydb-# ,tst.spcname
mydb-# FROM (((pg_class t /* tables */
mydb(# JOIN pg_tablespace tst
mydb(# ON t.reltablespace = tst.oid)
mydb(# JOIN pg_index pgi
mydb(# ON pgi.indrelid = t.oid)
mydb(# JOIN pg_class i /* indexes */
mydb(# ON pgi.indexrelid = i.oid)
mydb-# JOIN pg_tablespace tsi
mydb-# ON i.reltablespace = tsi.oid
mydb-# WHERE i.relname NOT LIKE 'pg_toast%'
mydb-# AND i.reltablespace != t.reltablespace
mydb-# ;
 indexname | spcname | tablename | spcname 
-----------+---------+-----------+---------
(0 rows)

mydb=#

FDW外部表的访问：可以参考 https://oracledocs.blog.csdn.net/article/details/112283716

外部表可以收集统计信息，postgres_fdw透明推送where从句到远程服务中。dblink模块无法发送本地的where从句到远程数据库。
另一个访问其他数据库的方法是使用PL/Proxy的工具。PL/Proxy仅以函数方式运行。PL/Proxy是专门设计应用于分片数据和负载均衡的较为复杂的架构。（例子略）

pg访问oracle数据库的扩展oracle_fdw。 (测试略，有空再研究，好像要安装oracle 客户端之类的)

-- 对视图的修改，比如对视图插入数据等（未做测试）
一个简单的视图

CREATE VIEW cust_view AS SELECT customerid ,firstname ,lastname ,age FROM cust;

尝试对视图执行插入动作，报错

postgres=# INSERT INTO cust_view
postgres-# VALUES (5, 'simon', 'riggs', 133);
ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
尝试以下语句
CREATE RULE cust_view_insert AS
ON insert TO cust_view
DO INSTEAD
INSERT INTO cust
VALUES (new.customerid, new.firstname, new.lastname, new.age);
重新插入数据
postgres=# INSERT INTO cust_view
postgres-# VALUES (5, 'simon', 'riggs', 133);
INSERT 0 1
插入数据成功，继续添加针对update和delete的规则
CREATE RULE cust_view_update AS
ON update TO cust_view
DO INSTEAD
UPDATE cust SET
firstname = new.firstname
,lastname = new.lastname
,age = new.age
WHERE customerid = old.customerid;
CREATE RULE cust_view_delete AS
ON delete TO cust_view
DO INSTEAD
DELETE FROM cust
WHERE customerid = old.customerid;

-- 使用物化视图
创建两个随机填充内容的表，其中一个很大

create table dish(dish_id serial primary key,dish_description text);
create table eater(eater_id serial,eating_date date,dish_id int references dish(dish_id));

insert into dish(dish_description) values('Lentils'),('Mango'),('Plantain'),('Rice'),('Tea');
insert into eater(eating_date,dish_id) 
select floor(abs(sin(n)) * 365) :: int + date '2014-01-01',
			 ceil(abs(sin(n :: float * n))*5) :: int 
			 from generate_series(1,500000) as rand(n);

-- 创建视图

create view v_dish as select dish_description,count(*) from dish join eater using(dish_id) group by dish_description order by 1;

-- 创建物化视图

create materialized view mv_dish as select dish_description,count(*) from dish join eater using(dish_id) group by dish_description order by 1;

-- 视图和物化视图产生相同的结果,但是查询的耗时有较大差异。物化视图比非物化视图快很多。当建立物化视图的时候，默认行为是马上执行查询并保存结果集，就像保存在表中一样。简单来说，创建物化视图慢，但使用起来很快。刚好和普通视图相反。普通视图是立即被创建，但是在每次使用的时候都要重新计算。

\timing
select * from v_dish;
select * from mv_dish;

mydb=# \timing
Timing is on.
mydb=# select * from v_dish;  -- 视图查询，消耗124ms多
 dish_description | count  
------------------+--------
 Lentils          |  64236
 Mango            |  66512
 Plantain         |  74058
 Rice             |  90222
 Tea              | 204972
(5 rows)

Time: 124.876 ms
mydb=# select * from mv_dish;  -- 物化视图查询，消耗0.6ms多 ，时间上差异很大
 dish_description | count  
------------------+--------
 Lentils          |  64236
 Mango            |  66512
 Plantain         |  74058
 Rice             |  90222
 Tea              | 204972
(5 rows)

Time: 0.606 ms
mydb=#

-- 视图的刷新。当构成表发生变化的时候，物化视图不会自动变更，需要刷新。

refresh materialized view mv_dish;
mydb=# refresh materialized view mv_dish;
REFRESH MATERIALIZED VIEW
Time: 214.795 ms
mydb=#

END

文档搬运工

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录