Skip to main content

PostgreSQL FAQ

note

限制​

unique constraint vs unique index​

核心业务语义尽量用 constraint

  • unique constraint
    • unique(tid,entity_id) -> flow_tid_entity_id_key
    • 可以延后
    • 通过 unique index 实现 - 自动创建
    • 附带在 TABLE 上
      • unique 在 create table 时定义
      • alter table TABLE add unique (tid,rid,cid); 在 create table 之后定义
    • 不支持 ADD CONSTRAINT IF NOT EXISTS
    • 支持 DROP CONSTRAINT IF EXISTS
  • unique index
    • create unique index on flow(tid,entity_id) -> flow_tid_entity_id_idx
    • 可以并发
    • 可以 带条件
    • 独立 INDEX 概念
      • 不能在 create table 时定义 - inline index 概念
      • UNIQUE 只是 INDEX 的一个限制
  • https://stackoverflow.com/a/6804058/1870054
    • add constraint if not exists
  • https://stackoverflow.com/questions/23542794
  • 都支持 UNIQUE NULLS NOT DISTINCT
    • 因为是 INDEX 的能力

TOAST​

  • TOAST = The Oversized-Attribute Storage Technique
  • 一行 2kB - TOAST_TUPLE_THRESHOLD
    • 超过先尝试压缩到 2kB - TOAST_TUPLE_TARGET
      • 默认 default_toast_compression=pglz, 支持 lz4
      • 可按列设置 COMPRESSION
    • 压缩不足，则分 chunk 存储到关联的 toast 表
  • TOAST 使用 oid 跟踪 - 最多 2^32 = 4 十亿 条记录
  • 存储
    • PLAIN - 不压缩，无 out-of-line - 超过则异常
    • EXTENDED - 压缩+out-of-line
    • EXTERNAL - out-of-line
    • MAIN - 压缩+尝试尽量不 out-of-line
  • 参考 
-- 默认 EXTENDED
ALTER TABLE users SET STORAGE EXTENDED;
-- 修改 TOAST_TUPLE_TARGET
ALTER TABLE users SET (toast_tuple_target = N);

服务重载​

  1. pg_ctl
su postgres
pg_ctl reload
  1. sql
psql -U postgres
SELECT pg_reload_conf();
  1. service
service postgresql restart

维护​

升级​

可以构造一个包含所有版本的镜像，然后进行升级 - Dockerfile

# https://github.com/tianon/docker-postgres-upgrade
docker run --rm \
  -v PGDATAOLD:/var/lib/postgresql/OLD/data \
  -v PGDATANEW:/var/lib/postgresql/NEW/data \
  tianon/postgres-upgrade:OLD-to-NEW
pg_upgrade --old-datadir /var/lib/pgsql/data/ --new-datadir /var/lib/pgsql/10/data/ \
  --old-bindir /usr/bin/ --new-bindir /usr/pgsql-10/bin/

CTID​

https://dba.stackexchange.com/questions/203989/what-is-the-data-type-of-the-ctid-system-column-in-postgres

https://postgresql.verite.pro/blog/2019/04/24/oid-column.html

时区问题​

  • PG 实际存储的是 UTC 不会存储时区
  • 时区信息会用于转换
-- 当前时区
show timezone;
show timezone_abbreviations;
-- 可用时区
SELECT * FROM pg_timezone_names;

-- date 转 tz 应该是正常的
select ('2020-01-02'::date)::TIMESTAMPTZ;

-- session 有效
SET TIME ZONE TO 'UTC';
SET TIMEZONE TO 'UTC';
-- 也可以直接指定 offset
SET timezone=-4;

-- 当前时间
-- UTC 可以使用 localtime
SELECT LOCALTIMESTAMP AT TIME ZONE 'Asia/Shanghai';
-- timestamp 不显示 TZ 信息
SELECT NOW()::TIMESTAMP;
-- current_timestamp 是 timestamptz
-- 可以提取 tz 信息 秒、小时
select current_timestamp,
       pg_typeof(current_timestamp),
       extract(timezone from current_timestamp::timestamptz),
       extract(timezone_h from current_timestamp::timestamptz)
;

-- db 配置
ALTER SYSTEM SET timezone = 'UTC';

-- 修改角色时区
ALTER ROLE my_role SET TIMEZONE = '+1';

NULL 字符 / \0 字符​

  • PG 不允许字符串包含 \0
  • 传入之前替换或用 bytea 存储
  • JSON 也不允许包含空字符

unsupported Unicode escape sequence​

一般是因为 \u0000, 替换掉即可, pg 的字符串不支持 \u0000

切换用户​

set role user;

切换数据库​

  • 只能重新链接

密码存储​

-- 生成 Hash
select crypt('12345', gen_salt('bf', 8));
-- 判断密码相等
select crypt('12345', password) = password

-- PG 无法处理 2b - 或者插入时修改为 2a
select *
from users
where username = 'admin'
  and  regexp_replace(password,'^[$]2b','$2a') = crypt('admin', regexp_replace(salt,'^[$]2b','$2a'));

Calculating and Saving Space in PostgreSQL​

pg_controldata data/
-- 列宽
select pg_column_size('int');
pg_control version number:            1002
Catalog version number:               201707211
Database system identifier:           6502788473953883273
Database cluster state:               in production
pg_control last modified:             五  3/ 9 12:04:15 2018
Latest checkpoint location:           0/32F8A88
Prior checkpoint location:            0/32DA0D0
Latest checkpoint's REDO location:    0/32F8A50
Latest checkpoint's REDO WAL file:    000000010000000000000003
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:730
Latest checkpoint's NextOID:          25609
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        548
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  730
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            五  3/ 9 12:04:12 2018
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_prepared_xacts setting:           200
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           1
Mock authentication nonce:            32f8310a0cf344f7c1432dd733d3cf6065b748697485724af31fbaf7605f50bc

psql 开启时间记录​

  • \timing
# 直接命令行使用
psql -c '\timing' -c 'select 1'

数组索引​

GIN 索引是反向索引(inverted indexes), 适用于包含多个值的情况.

  • 支持的操作符
    • <@
    • @>
    • =
    • &&

数组外键​

  • 不支持

ERROR: cannot alter type of a column used by a view or rule​

必须要先 drop view 再操作, 目前没有比较好的操作方式, 但操作都可以在一个事务中完成

有些修改可以通过直接修改 pg_attribute 来达到目的, 但是非常不建议.

时间戳上的毫秒处理​

目前没有比较好的处理方式

-- 将一个毫秒的 ts 转为 timestamp 类型
ALTER TABLE  my_info
  ALTER COLUMN tstmp TYPE TIMESTAMP USING to_timestamp(tstmp / 1000) + ((tstmp % 1000) || ' milliseconds') :: INTERVAL;

查询语句的最大大小​

#define MaxAllocSize   ((Size) 0x3fffffff) /* 1 gigabyte - 1 */

IN vs any​

CREATE TABLE test (
  id  BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  val TEXT
);

-- 插入测试数据
DO
$$
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO test (val) VALUES ('val#' || i);
  END LOOP;
END
$$;

EXPLAIN SELECT *
        FROM test
        WHERE id IN (1, 2, 3);

EXPLAIN SELECT *
        FROM test
        WHERE id = ANY (VALUES (1), (2), (3));
# IN
Bitmap Heap Scan on test  (cost=12.86..19.97 rows=3 width=40)
  Recheck Cond: (id = ANY ('{1,2,3}'::bigint[]))
  ->  Bitmap Index Scan on test_pkey  (cost=0.00..12.86 rows=3 width=0)
        Index Cond: (id = ANY ('{1,2,3}'::bigint[]))

# ANY
Nested Loop  (cost=0.32..25.00 rows=3 width=40)
  ->  HashAggregate  (cost=0.05..0.08 rows=3 width=4)
        Group Key: ""*VALUES*"".column1
        ->  Values Scan on ""*VALUES*""  (cost=0.00..0.04 rows=3 width=4)
  ->  Index Scan using test_pkey on test  (cost=0.28..8.29 rows=1 width=40)
        Index Cond: (id = ""*VALUES*"".column1)
-- 测试 IN
DO
$$
DECLARE
  x   TEXT = '';
  r   REFCURSOR;
  rec RECORD;
BEGIN
  x = '0';
  FOR i IN 1..1000 LOOP
    x = x || ',' || i;
  END LOOP;
  OPEN r FOR EXECUTE 'EXPLAIN ANALYSE SELECT *
        FROM test
        WHERE id IN (' || x || ')';

  FOR i IN 1..6 LOOP
    FETCH r INTO rec;
    RAISE NOTICE 'ROW %', rec;
  END LOOP;
END
$$;

-- 测试 ANY
DO
$$
DECLARE
  x   TEXT;
  r   REFCURSOR;
  rec RECORD;
BEGIN
  x = '(0)';
  FOR i IN 1..1000 LOOP
    x = x || ',(' || i || ')';
  END LOOP;
  OPEN r FOR EXECUTE 'EXPLAIN ANALYSE SELECT *
        FROM test
        WHERE id = any (VALUES ' || x || ')';

  FOR i IN 1..10 LOOP
    FETCH r INTO rec;
    RAISE NOTICE 'ROW %', rec;
  END LOOP;
END
$$;

重置 schema​

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

-- 9.3+ 可能需要
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

psql 使用连接字符串​

psql -Atx "host=localhost port=5432 dbname=taop user=taop" -c 'select current_date'

# -d database
psql -d "host=localhost port=5432 dbname=taop user=taop"

control reached end of trigger procedure without RETURN​

使用 trigger 时出现，添加 return null

create or replace function sync_events_trigger() returns trigger
as
$$
BEGIN
    -- ...
    return null; -- this is important
END;
$$ language plpgsql volatile;

query has no destination for result data​

在函数中 select 需要指定输出

  • 可以将返回类型指定为 table
  • 可以 select into
  • 可以 返回 return(select * from tab)
  • 可以使用 perform/execute 如果不想要结果
CREATE OR REPLACE FUNCTION tst_dates_func()
RETURNS TABLE( v int) as
$$
    select 1;
$$ LANGUAGE sql;

RETURN QUERY (select 1);
RETURN(SELECT dblink_disconnect());

SELECT 1 into result;
RETURN result;

PERFORM select 1;

could not resize shared memory segment "/PostgreSQL.153520683" to 1073812480 bytes: No space left on device​

增加 docker 的 shm_size

set-returning functions are not allowed in UPDATE​

  • 替换 unnest(array_value) -> array_value[1]
  • 替换 (regexp_matches(album, '^6,(?:(.+),)?tv\d+'))[1] -> substring (album FROM '^6,(?:(.+),)?tv\d+')

Tuning​

Sale Point​

ON CONFLICT DO UPDATE command cannot affect row a second time​

  1. 插入前进行 dedup
  2. CTE 先 select distinct

生成列​

  • 生成列不能被手动指定
  • stored - INSERT 或 UPDATE 时写入
  • virtual - 实时计算 - 类似 view

generated vs default column​

  • default
    • 可指定
    • 不指定使用默认 - 计算 1 次
    • 可关联其他列
  • generated
    • 每次行变化都会生成

remaining connection slots are reserved for non-replication superuser connection​

排查连接数问题

Note

  • 修改 max_connections 必须重启 pg
  • 建议应用指定 application_name
-- 查看当前的最大连接数
show max_connections;

-- 查看连接数主要谁占用
-- idle 多还是 active 多
select state, usename, application_name, datname, count(*)
from pg_stat_activity
group by state, usename, application_name, datname
order by 1, 2, 3;

-- 排查 active 连接
select *
from pg_stat_activity
where state = 'active';

-- 按需 kill 连接

CURRENT_TIMESTAMP vs NOW​

  • 没有区别
  • CURRENT_TIMESTAMP=事物开始时间=transaction_timestamp()=now()
  • 输入时的 now 不等于 now()
  • 其他时间
    • statement_timestamp() - STABLE - 语句时间
    • clock_timestamp() - VOLATILE - 真正当前时间

ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000)​

  • insert on conflict 时有重复数据
  • 插入之前先去重

column "datlastsysoid" does not exist​

select distinct datlastsysoid from pg_database;

查找所有外键​

SELECT conrelid::regclass AS table_name,
       conname AS foreign_key,
       pg_get_constraintdef(oid)
FROM   pg_constraint
WHERE  contype = 'f'
AND    connamespace = 'public'::regnamespace
ORDER  BY conrelid::regclass::text, contype DESC;

-- psql
\d table_name

ARRAY/数组 外键​

cannot use column reference in DEFAULT expression​

  • 不能在 default 中使用其他列
  • 通过 before insert 触发器实现

lateral vs subquery​

  • lateral
    • PostgreSQL 9.3+
    • correlated subquery
    • 不产生新的行
    • 每行只执行 1 次
    • LEFT JOIN LATERAL (select 1) t ON TRUE
    • LATERAL (select 1) t
    • 查询可以引用外部列
  • subquery
    • 只能返回一个值
    • 只执行 1 次

union vs union all​

  • union
    • 合并去重
  • union all
    • 不做处理

外键需要建立索引么？​

  • pk 和 unique 会自动建立索引
  • fk 不会自动建立索引
    • 建议建立索引

enum vs check vs fk​

  • enum
  • check
    • 所见即所得
  • fk
    • 可以包含 label - 方便生成
    • 可以动态变化
    • 推荐 FK Value
    • 不需要 alter table
-- ENUM
CREATE TYPE valid_colors AS ENUM ('red', 'green', 'blue');

CREATE TABLE t (
    color VALID_COLORS
);

-- CHECK
CREATE TABLE t (
    colors TEXT CHECK (colors IN ('red', 'green', 'blue'))
);

-- FK ID
CREATE TABLE valid_colors (
    id SERIAL PRIMARY KEY NOT NULL,
    color TEXT
);

INSERT INTO valid_colors (color) VALUES
    ('red'),
    ('green'),
    ('blue');

CREATE TABLE t (
    color_id INTEGER REFERENCES valid_colors (id)
);

-- FK value
CREATE TABLE valid_colors (
    value text PRIMARY KEY NOT NULL,
    label text
);

INSERT INTO valid_colors (value) VALUES
    ('red'),
    ('green'),
    ('blue');

CREATE TABLE t (
    color text REFERENCES valid_colors (value)
);

预加载 table 到内存​

pg_prewarm

ERROR: could not open file "base/5/2704": No such file or directory​

数据库少了数据文件

A field with precision 5, scale 4 must round to an absolute value less than 10^1​

the database system is in recovery mode the database system is in recovery mode​

  • 使用 pg_isready 判断是否可用

COLLATE​

create index on t (name COLLATE "C");
-- 支持使用 Index
explain select * from t where name like 'W%';
-- 不会用 Index
explain select * from t where name = 'Wener';
-- 会用 Index
explain select * from t where name = 'Wener' collate "C";

-- 修改默认 COLLATE
alter table t alter column name set data type text collate "C";
-- 会用 Index
explain select * from t where name = 'Wener';
  • COLLATE "C" 支持前缀过滤索引
  • LC_COLLATE

ERROR: could not resize shared memory segment "/PostgreSQL.2692148336" to 1073812480 bytes: No space left on device​

  • vacuum 时发生
sysctl kernel.shmmax
ls -lash /dev/shm
# PostgreSQL.1489521326

K8S 修改

spec:
  containers:
    - name: postgres
      image: postgres
      volumeMounts:
        - mountPath: /dev/shm
          name: dshm
      ports:
        - containerPort: 5432
  volumes:
    - name: dshm
      emptyDir:
        medium: Memory

Docker

docker run --cap-add=SYS_ADMIN --shm-size 2G postgres

K8S start & shutdown​

preStop:
  exec:
    command: ['/bin/sh', '-c', 'pg_ctl -D /var/lib/postgresql/data/pgdata -w -t 60 stop -m fast']
    #command: ['/usr/local/bin/pg_ctl stop -D /var/lib/postgresql/data -w -t 60 -m fast']

LC_MONETERY​

caution
  • collate 不能创建 db 后修改，可以针对 column 或 table 修改。
show lc_collate;
show lc_monetary;

select *
from pg_settings
where name like 'lc_%';

set lc_monetary to "en_IE.utf8";
select 10::money;
namedefaultaliyun
lc_collateen_US.utf8C
lc_ctypeen_US.utf8en_US.utf8
lc_messagesen_US.utf8""
lc_monetaryen_US.utf8C
lc_numericen_US.utf8C
lc_timeen_US.utf8C
  • initdb - --encoding=UTF-8 --lc-collate=C --lc-ctype=C
POSTGRES_INITDB_ARGS: '--encoding=UTF-8 --lc-collate=C --lc-ctype=C'

there is no unique constraint matching given keys for referenced table​

外键必须 unique

permission denied for table​

source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression​

不能单个字段

on conflict(value) do update set (label)= (excluded.label);

修改为

on conflict(value) do update set (label,extensions)= (excluded.label,excluded.extensions);

for update nowait - FOR UPDATE cannot be applied to the nullable side of an outer join​

  • mikroorm LockMode.PESSIMISTIC_WRITE_OR_FAIL

permission denied to set session authorization​

-- 获取当前的 superuser
SELECT usename
FROM pg_catalog.pg_user
WHERE usesuper = true;
  • ⚠️注意 supabase 的 pg superuser 是 supabase_admin
    • supabase_admin 密码和 postgres 相同，登录后 ALTER USER postgres WITH SUPERUSER

Windows minimal​

rm -rf pgAdmin doc include StackBuilder
rm lib/*.pdb bin/*.pdb

cd bin
rm clusterdb.exe createdb.exe createuser.exe dropdb.exe dropuser.exe ecpg.exe oid2name.exe pg_amcheck.exe pg_archivecleanup.exe pg_basebackup.exe pg_checksums.exe pg_combinebackup.exe pg_config.exe pg_controldata.exe pg_createsubscriber.exe pg_isready.exe pg_receivewal.exe pg_recvlogical.exe pg_resetwal.exe pg_rewind.exe pg_test_fsync.exe pg_test_timing.exe pg_upgrade.exe pg_verifybackup.exe pg_waldump.exe pg_walsummary.exe pgbench.exe reindexdb.exe stackbuilder.exe vacuumdb.exe vacuumlo.exe
cd -

rm -rf share/locale
rm -rf share/doc

ls bin/*.exe | sort
# bin/initdb.exe
# bin/pg_ctl.exe
# bin/pg_dump.exe
# bin/pg_dumpall.exe
# bin/pg_restore.exe
# bin/postgres.exe
# bin/psql.exe

package

cd ..
# gnu tar + zstd ~ 27MB
tar -I 'zstd -20' --exclude='.DS_Store' -cvf pgsql.tar.zst pgsql
# just zip ~ 38MB
zip -r -9 pgsql.zip pgsql -x "*.DS_Store"

init

set PATH=%PATH%;%CD%\pgsql\bin
initdb.exe -D .\data -U postgres --encoding=UTF-8 --lc-collate=C --lc-ctype=C
pg_ctl -D ./data start -l db.log
Shell
export PATH=$PWD/pgsql/bin:$PATH
TZ=Asia/Shanghai initdb -D ./data  -U postgres --encoding=UTF-8 --lc-collate=C --lc-ctype=C
pg_ctl -D ./data start -l db.log


export PGDATA=$PWD/data
pg_ctl status
psql -U postgres
pg_ctl stop
echo 'listen_addresses = "*"' >> data/postgresql.conf
echo 'host all all 0.0.0.0/0 md5' >> data/pg_hba.conf

pg_ctl reload