共计 3510 个字符,预计需要花费 9 分钟才能阅读完成。
阐明
PostgreSQL 一览表 pg_attribute 存储对于表列的信息,数据库中每张表中的行都会对应在该零碎表 pg_attribute 中。既然存储的是数据库中表字段相干的信息,那么对表所做的批改都会通过该表记录。如创立表指定的列,批改表,批改表的数据类型等等。
创立自定义函数查看某张表的信息
postgres=# CREATE OR REPLACE FUNCTION f_get_table_column_info(varchar,varchar)
postgres-# RETURNS TABLE
postgres-# (
postgres(# 模式名称 varchar,
postgres(# 表名称 varchar,
postgres(# 表所属表空间 varchar,
postgres(# 表对应列名称 varchar,
postgres(# 表对应列数据类型 oid oid,
postgres(# 表对应列程序编号 integer,
postgres(# 标识列 text
postgres(#)
postgres-# AS
postgres-# $FUNCTION$
postgres$# SELECT a.schemaname,
postgres$# a.tablename,
postgres$# a.tablespace,
postgres$# d.attname,
postgres$# d.atttypid,
postgres$# d.attnum,
postgres$# d.attidentity
postgres$# FROM pg_tables a,
postgres$# pg_class b,
postgres$# pg_attribute d
postgres$# WHERE b.oid = d.attrelid
postgres$# AND a.tablename = b.relname
postgres$# AND d.attrelid = $1::regclass
postgres$# AND a.schemaname = $2
postgres$# AND a.schemaname !~ 'pg_catalog|information_schema'
postgres$# AND d.attname !~ 'xmin|xmax|cmax|cmin|ctid|tableoid';
postgres$# $FUNCTION$
postgres-# LANGUAGE SQL;
CREATE FUNCTION
创立测试表
postgres=# CREATE TABLE tab_product
postgres-# (
postgres(# id int generated by default as identity
postgres(# (start with 1 increment by 2 minvalue 1 maxvalue 10 cycle),
postgres(# product_name varchar(80),
postgres(# product_date date,
postgres(# product_vendor varchar(80)
postgres(#);
CREATE TABLE
查看表字段信息
postgres=# SELECT * FROM f_get_table_column_info('tab_product','public');
模式名称 | 表名称 | 表所属表空间 | 表对应列名称 | 表对应列数据类型 oid | 表对应列程序编号 | 标识列
----------+-------------+--------------+----------------+---------------------+------------------+--------
public | tab_product | | id | 23 | 1 | d
public | tab_product | | product_name | 1043 | 2 |
public | tab_product | | product_date | 1082 | 3 |
public | tab_product | | product_vendor | 1043 | 4 |
(4 rows)
当然,这里并非仅仅对该表存储的是数据库中的表列做形容,而是为了解决在零碎表中仍然存在表收缩的景象,如在生产环境中,通常会做删除表或者删除 schema 的动作,在 PostgreSQL 中,只有有对表或者 schema 的删除动作,那么就会造成该表 pg_attribute 的收缩。如下:
在某个 schema 下创立表
postgres=# CREATE TABLE s1.tab_product
postgres-# (
postgres(# id int generated by default as identity
postgres(# (start with 1 increment by 2 minvalue 1 maxvalue 10 cycle),
postgres(# product_name varchar(80),
postgres(# product_date date,
postgres(# product_vendor varchar(80)
postgres(#);
CREATE TABLE
查看表列信息
postgres=# SELECT * FROM f_get_table_column_info('tab_product','s1');
模式名称 | 表名称 | 表所属表空间 | 表对应列名称 | 表对应列数据类型 oid | 表对应列程序编号 | 标识列
----------+-------------+--------------+----------------+---------------------+------------------+--------
s1 | tab_product | | id | 23 | 1 | d
s1 | tab_product | | product_name | 1043 | 2 |
s1 | tab_product | | product_date | 1082 | 3 |
s1 | tab_product | | product_vendor | 1043 | 4 |
(4 rows)
查看表 pg_attribute 的大小
postgres=# SELECT pg_size_pretty(pg_relation_size('pg_attribute'));
pg_size_pretty
----------------
464 kB
(1 row)
此处对 s1 schema 进行删除重建 10000 次
[postgres@pgnode01 ~]$ cat drop_rebuild_s1.sh
#!/bin/bash
CONNINFO="psql -U postgres -d postgres -Atq -c"
DROP_REBUILDSCHEMA="DROP SCHEMA IF EXISTS s1 CASCADE;CREATE SCHEMA IF NOT EXISTS s1"
CREATE_TABLE="
CREATE TABLE IF NOT EXISTS s1.tab_product
(
id int generated by default as identity
(start with 1 increment by 2 minvalue 1 maxvalue 10 cycle),
product_name varchar(80),
product_date date,
product_vendor varchar(80)
) "
for i in {1..10000};do
if [$i -le 10000];then
${CONNINFO} "${DROP_REBUILDSCHEMA}"
${CONNINFO} "${CREATE_TABLE}"
echo "$i"
else
exit 1;
fi
i=$i+1;
done
查看 pg_attribute 大小
postgres=# SELECT pg_size_pretty(pg_relation_size('pg_attribute'));
pg_size_pretty
----------------
4248 kB
(1 row)
对 pg_attribute 执行 VACUUM FULL
postgres=# VACUUM FULL pg_attribute ;
VACUUM
postgres=# SELECT pg_size_pretty(pg_relation_size('pg_attribute'));
pg_size_pretty
----------------
440 kB
(1 row)
当对表执行完 VACUUM FULL 操作时,该收缩的空间会返还给操作系统
结语
在理论生产环境中,有可能会疏忽该表收缩问题,会导致数据库中的表和数据没多少,然而数据库特地大,那么阐明有可能是数据库系统表中的局部表产生了收缩景象。该景象也会呈现在基于 pg 开发的其余数据库中,如 Greenplum。