从两个小案例说起
xiongcc约 5 分钟1前言
这篇文章的起因是，在②群里有一位筒子提了这么一个问题：
列长度超长报错后，日志内部怎么可以打印出具体的超长列名称呢？有什么办法？
我看到这个问题就秒回了，因为我对这个问题积怨已久。我为什么会这么说呢？容我细细道来（吐槽..）
2现象
简而言之，就是当插入的数据超过了列的长度之后会报错，报错当然没问题，但是怪异的是，数据库并不会告诉你是哪一列超过了长度，这就很 xxx 了。
看个例子：
postgres=# create table t1(c1 varchar(10));
CREATE TABLE
postgres=# insert into t1 values('hello');
INSERT 0 1
postgres=# insert into t1 values('hello hello hello');
ERROR: value too long for type character varying(10)
假如是只有几列的表那还好，根据表定义去人肉比对。但是，假如我这个表是个大宽表呢？
postgres=# create table t2(info1 varchar(3),info2 varchar(5),info3 varchar(3),info4 varchar(10));
CREATE TABLE
postgres=# insert into t2 values('hello','hello','hell','hell'); ---第一列超了
ERROR: value too long for type character varying(3)
postgres=# insert into t2 values('hel','hello','hell','hell'); ---第三列超了
ERROR: value too long for type character varying(3)
postgres=# insert into t2 values('hel','hello','hel','hell');
INSERT 0 1postgres=# \set VERBOSITY verbose
postgres=# insert into t2 values('hel','hello','hell','hell'); ---即使打开错误明细也看不到
ERROR: 22001: value too long for type character varying(3)
LOCATION: varchar, varchar.c:635
varchar 是最常见的类型。👆🏻 可以看到上方两个语句插入都报错了，但是你不能准确分辨出是哪一列超了，你得自己人肉比对... what？？？所以假如你这个表含有几十个 varchar 类型的字段的话，那酸爽，只有体会过的人才知道了。当然日志里也是没有的，不要想了
2023-05-26 16:57:22.317 CST,"postgres","postgres",16995,"[local]",6470746b.4263,1,"idle",2023-05-26 16:57:15 CST,3/61,0,LOG,00000,"statement: insert into t2 values('hel','hello','hell','hell');",,,,,,,,,"psql","client backend",,0
2023-05-26 16:57:22.318 CST,"postgres","postgres",16995,"[local]",6470746b.4263,2,"INSERT",2023-05-26 16:57:15 CST,3/61,0,ERROR,22001,"value too long for type character varying(3)",,,,,,"insert into t2 values('hel','hello','hell','hell');",,,"psql", "client backend",,9059870578267765131
至于我说为什么积怨已久，因为这个问题很早很早就有人提过了，8.4 的古董版本 value too long - but for which column?
patch 也是 Returned with feedback，没有后文了
这个功能 u1s1，对于开发很重要，对于 DBA 也重要，毕竟谁也不想盯着大几十个字段的表结构挨个瞪眼找冲突的数据类型。但是社区的一贯作风就是这样，可能由于学院派风格？倒是对边边角角的功能 argue 十分热闹。
没法，对于这种情况，可以使用如下 SQL 先找一下列的长度
postgres=# SELECT FORMAT( '%s;', string_agg(stmt, E'\nUNION ALL ') )
FROM (
SELECT FORMAT(
'SELECT %L AS table_catalog, %L AS table_schema, %L AS table_name, %s FROM %I.%I.%I',
table_catalog,
table_schema,
table_name,
string_agg(
FORMAT( 'jsonb_build_object(%L,max(length(%I)))', column_name, column_name),
' || '
ORDER BY column_name
),
table_catalog,
table_schema,
table_name
)
FROM information_schema.columns
WHERE data_type = 'character varying'
AND table_schema <> 'pg_catalog'
AND table_name = 't2'
GROUP BY table_catalog, table_schema, table_name
) AS t(stmt);
format ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------
SELECT 'postgres' AS table_catalog, 'public' AS table_schema, 't2' AS table_name, jsonb_build_object('info1',max(length(info1))) || jsonb_build_object('info2',max(length(info2))) || jsonb_build_object('info3',max(length(info3))) || jsonb_build_object(
'info4',max(length(info4))) FROM postgres.public.t2;
(1 row)
postgres=# \gexec
table_catalog | table_schema | table_name | ?column?
---------------+--------------+------------+--------------------------------------------------
postgres | public | t2 | {"info1": 3, "info2": 5, "info3": 3, "info4": 4}
(1 row)
另外一个问题是昨天在其他群里看到的，当表有依赖视图的时候，变更表结构就不是那么方便了
看个例子
postgres=# create table t3(id int);
CREATE TABLE
postgres=# create view myview as select * from t3;
CREATE VIEW
postgres=# alter table t3 alter COLUMN id type bigint;
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view myview depends on column "id"
对于这种情况难道要一个个手动删了视图再重建吗？非也，这难不倒 stackoverflow 的大神，函数在这里 👉🏻 https://gist.github.com/briandignan/03ef42e78434658cf27f052e2f0798e8，需要在库中建两个函数
deps_save_and_drop_dependencies deps_restore_dependencies
现在让我们换个姿势再来一次
postgres=# begin;
BEGIN
postgres=*# select deps_save_and_drop_dependencies('public','t3');
deps_save_and_drop_dependencies
--------------------------------- (1 row)
postgres=*# alter table t3 alter COLUMN id type bigint;
ALTER TABLE
postgres=*# select deps_restore_dependencies('public','t3');
deps_restore_dependencies
---------------------------
(1 row)
postgres=*# commit;
COMMIT
postgres=# \d+ t3
Table "public.t3"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------+-----------+----------+---------+---------+-------------+--------------+-------------
id | bigint | | | | plain | | |
Access method: heap
postgres=# \d+ myview
View "public.myview"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+--------+-----------+----------+---------+---------+-------------
id | bigint | | | | plain |
View definition:
SELECT t3.id
FROM t3;
so easy，轻轻松松。
3小结
其实这两个看似不起眼的小案例，实则用途很大，能够极大提升生产效率，但是社区的一贯作风如此，其实这类功能的实现我觉得才更加"亲民有效"。
16 beta 也 release 了，但是看了一眼，还是挤牙膏，除了在备库上支持 logical decoding，其他都是不痛不痒的功能，被人诟病的 xid、connection pool、hint 都没看到，各位只能继续盼星星盼月亮期待下个大版本啦。😤
4参考
https://commitfest.postgresql.org/11/818/
https://stackoverflow.com/questions/3243863/problem-with-postgres-alter-table
https://dba.stackexchange.com/questions/215798/how-to-get-max-field-value-length-for-each-text-field-in-all-tables-in-database