常见问题

PostgreSQL FAQ

维护

How to Upgrade

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

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 也不允许包含空字符

切换用户

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

unsupported Unicode escape sequence

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

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
$$;