PostgreSQL问答-第20230220期

目录

• Q1. ERROR: canceling statement due to conflictwith recovery
• Q2. 聚合函数嵌套的问题
• Q3. 查看用户的创建时间和最后登录时间
• Q4. zh_CN.utf8的排序规则

Q1. ERROR: canceling statement due to conflictwith recovery

问题描述

PostgreSQL中，备库报错ERROR: canceling statement due to conflictwith recovery，需要怎么处理？

问题解答

三板斧：

• max_standby_streaming_delay
• hot_standby_feedback + slot
• vacuum_defer_cleanup_age

要杜绝（不是100%）使用slot + hot_standby_feedback

Q2. 聚合函数嵌套的问题

问题描述

Oracle中支持聚合函数的嵌套语法，放在PostgreSQL数据库针对这种SQL应该如何改写呢？

问题解答

SQL 改写，需要用到子查询

例：

select sum(count(bc.customerId)) from business_contract bc  group by bc.customerId

改写后：

select id,sum(cnt) from(
        select id,count(id) cnt
          from  test
      group by id) foo
group by id;

Q3. 查看用户的创建时间和最后登录时间

问题描述

在PostgreSQL数据库中，如何查看用户的创建时间和登录时间呢？

问题解答

• 查询用户创建时间

  前提：设置参数track_commit_timestamp=on

  postgres=# create user utest3;
  CREATE ROLE
  postgres=# select pg_xact_commit_timestamp(xmin) from pg_authid where rolname = 'utest3' ;
     pg_xact_commit_timestamp    
  -------------------------------
   2023-02-19 10:00:27.927322+08
  (1 row)
  

• 查询登录时间

  日志设置log_connections = on和log_disconnections = on

  2023-02-19 10:03:55.145 HKT [30945] LOG:  connection authorized: user=utest3 database=postgres application_name=psql
  2023-02-19 10:04:01.163 HKT [30945] LOG:  disconnection: session time: 0:00:06.019 user=utest3 database=postgres host=[local]
  

Q4. zh_CN.utf8的排序规则

问题描述

PostgreSQL中zh_CN.utf8的排序是按什么规则来进行排序的？

问题解答

iso14651_t1_pinyin

[postgres@node4 locales]$ pwd
/usr/share/i18n/locales
[postgres@node4 locales]$ cat iso14651_t1_pinyin | more
LC_COLLATE

copy "iso14651_t1_common"

script <HAN>

order_start <HAN>;forward;forward;forward;forward,position
<U5416> <U5416>;IGNORE;IGNORE;IGNORE    #吖104
<U814C> <U814C>;IGNORE;IGNORE;IGNORE    #腌185
<U9312> <U9312>;IGNORE;IGNORE;IGNORE    #錒0
<U9515> <U9515>;IGNORE;IGNORE;IGNORE    #锕7
<U963F> <U963F>;IGNORE;IGNORE;IGNORE    #阿23237
<U55C4> <U55C4>;IGNORE;IGNORE;IGNORE    #嗄60
<U554A> <U554A>;IGNORE;IGNORE;IGNORE    #啊16566
……

例：

postgres=# select * from test,ascii(info) order by info collate "zh_CN";
 info | ascii 
------+-------
 李四 | 26446
 李五 | 26446
 王五 | 29579
 小小 | 23567
 张三 | 24352
(5 rows)

postgres=# select * from test,ascii(info) order by info collate "en_US";
 info | ascii 
------+-------
 小小 | 23567
 张三 | 24352
 李五 | 26446
 李四 | 26446
 王五 | 29579
(5 rows)