1、前言介绍

PostgreSQL当中，有些问题看起来蛮简单的。我就将近期常见的几个问题列出来，然后看看大家都是如何解决的，以及如何“优雅”地解决的。

2、不进入psql, 如何知道data_directory以及端口号

2.1、首先看，进到psql如何得到？

如果进到了psql，我们可以直接查询一些设置的变量，得到结果，如：

postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/14/data
(1 row)

postgres=# show config_file;
              config_file
----------------------------------------
 /var/lib/pgsql/14/data/postgresql.conf
(1 row)

通过data_directory可以得到$PGDATA目录，通过config_file可以得到主配置文件的位置。这两个信息非常重要。因此也常常用到。通常情况下，默认目录都还容易找到，但就怕安装的时候做了重新定制。比如像ubuntu默认安装的时候，config_file就没有放到$PGDATA下边。当然我们也可以通过show port 和show listening_addresses来得到port和address:

postgres=# show port; show listen_addresses;
 port
------
 5432
(1 row)

 listen_addresses
------------------
 localhost
(1 row)

于是，有同学开始改进了，说不如pg15里头直接支持配置的模糊查询：

postgres=# \dconfig data*
       List of configuration parameters
      Parameter      |         Value
---------------------+------------------------
 data_checksums      | off
 data_directory      | /var/lib/pgsql/15/data
 data_directory_mode | 0700
 data_sync_retry     | off
(4 rows)

postgres=# \dconfig config*
           List of configuration parameters
  Parameter  |                 Value
-------------+----------------------------------------
 config_file | /var/lib/pgsql/15/data/postgresql.conf
(1 row)

这个确实清爽了许多，使用低版本的如果不升级只能感叹了。这种模糊查询配置变量的功能，似乎在别的数据库里，很多都有了。

再说不进入psql，怎么得到上述值呢，比如那个data_directory。

2.2、不进入psql

接着就有人问了，不进入psql，怎么搞？很快有人给出了答案：

1）使用ps -ef来定位

[11:00:17-postgres@sean-rh1:/var/lib/pgsql]$ pg_ctl start -D $PGDATA
2023-03-20 11:00:32.758 UTC [31602] LOG:  database system is ready to accept connections
 done
server started
[11:00:32-postgres@sean-rh1:/var/lib/pgsql]$ ps -ef | grep postgres
postgres 31602     1  0 11:00 ?        00:00:00 /usr/pgsql-15-build/bin/postgres -D /var/lib/pgsql/15/data
postgres 31603 31602  0 11:00 ?        00:00:00 postgres: checkpointer
postgres 31604 31602  0 11:00 ?        00:00:00 postgres: background writer
postgres 31606 31602  0 11:00 ?        00:00:00 postgres: walwriter
postgres 31607 31602  0 11:00 ?        00:00:00 postgres: autovacuum launcher
postgres 31608 31602  0 11:00 ?        00:00:00 postgres: logical replication launcher

当你显式指定了-D的物理位置，确实可以看到最后的目录：

-D /var/lib/pgsql/15/data

可是，如果没有指定呢？你可以就看不到它的位置了。就像下边这样的：

postgres 32080     1  0 11:07 ?        00:00:00 /usr/pgsql-15-build/bin/postgres

大家都认为这个方法可能不行的时候，有位同学想到了方法：

ls -lrt /proc/32080/cwd
lrwxrwxrwx 1 postgres postgres 0 Mar 20 11:11 /proc/32080/cwd -> /var/lib/pgsql/15/data

直接通过进程，定位到它的相关信息，如果我们直接查看那个进程的相关信息，可以看到更多：

[11:13:21-postgres@sean-rh1.openstack.eu-nl-1.cloud.sap:/var/lib/pgsql]$ ls -la /proc/32080
.....
-r--r--r--   1 postgres postgres 0 Mar 20 11:12 cpuset
lrwxrwxrwx   1 postgres postgres 0 Mar 20 11:11 cwd -> /var/lib/pgsql/15/data
-r--------   1 postgres postgres 0 Mar 20 11:12 environ
lrwxrwxrwx   1 postgres postgres 0 Mar 20 11:12 exe -> /usr/pgsql-15-build/bin/postgres
dr-x------   2 postgres postgres 0 Mar 20 11:07 fd

另外一种比较常用的方法是使用lsof -p <进程id>来查探它访问的文件或其它资源来查找定位：

$ lsof -p 32080
COMMAND    PID     USER   FD   TYPE             DEVICE  SIZE/OFF     NODE NAME
postgres 32080 postgres  cwd    DIR                8,2      4096  1320189 /var/lib/pgsql/15/data

3、truncate语句到底是DDL还是DCL语句？

最终归结为DDL。问了chatGPT。其实，因为在PG中，它确实是重新生成了表结构。连OID都变了。

4、vacuum能回空间吗？造一个回收很明显的例子。

mydb=# create table t(id int);
CREATE TABLE
mydb=# insert into t select generate_series(1, 1000000);
INSERT 0 1000000
mydb=# select pg_total_relation_size('t');
 pg_total_relation_size
------------------------
               36282368
(1 row)

mydb=# delete from t where id >=800001;
DELETE 200000
mydb=# vacuum verbose t;
INFO:  vacuuming "public.t"
INFO:  table "t": removed 200000 dead item identifiers in 886 pages
INFO:  table "t": found 200000 removable, 800000 nonremovable row versions in 4425 out of 4425 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 740
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.05 s, system: 0.00 s, elapsed: 0.06 s.
INFO:  table "t": truncated 4425 to 3540 pages
DETAIL:  CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.05 s
VACUUM
mydb=# select pg_total_relation_size('t');
 pg_total_relation_size
------------------------
               29032448
(1 row)

mydb=# delete from t where id <= 200000;
DELETE 200000
mydb=# vacuum verbose t;
INFO:  vacuuming "public.t"
INFO:  table "t": removed 200000 dead item identifiers in 885 pages
INFO:  table "t": found 200000 removable, 196 nonremovable row versions in 886 out of 3540 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 743
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
VACUUM
mydb=#  select pg_total_relation_size('t');
 pg_total_relation_size
------------------------
               29032448
(1 row)

我们可以看到，删掉末尾的连续的元组的操作，VACUUM可以真正的回收那些连续的空间，删掉前边的20000行，则都没有进行回收。

这是PG回收空间的连续空闲空间要求决定的，并且是文件的尾部。它可以直接截断进行回收。如果是在文件的中间或前头，则无法正常回收，但是可以被重用。

5、关于select current_user查询, 它是函数吗？为何select current_user()不能用？

是函数无疑，但是为何不能用？真有点迷惑人。干脆往pgsql-bugs@lists.postgresql.org扔了封邮件过去。也收到了几个答复：

答复1：

The syntax of current_user and current_role are mandated by the SQL
specification, we have this note in the documentation:

"current_catalog, current_role, current_schema, current_user,
session_user, and user have special syntactic status in SQL: they must
be called without trailing parentheses.  In PostgreSQL, parentheses can
optionally be used with current_schema, but not with the others."

答复2：

接着答复1进行的解释：

To further illuminate why the last one is special: It's not a fully-reserved keyword:

$ grep current_ src/include/parser/kwlist.h
PG_KEYWORD("current_catalog", CURRENT_CATALOG, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("current_date", CURRENT_DATE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("current_role", CURRENT_ROLE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("current_schema", CURRENT_SCHEMA, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("current_time", CURRENT_TIME, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("current_timestamp", CURRENT_TIMESTAMP, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("current_user", CURRENT_USER, RESERVED_KEYWORD, BARE_LABEL)

估计这几个所谓的函数都会有类似的用法。current_schema是个意外，两种形式都支持。

看看下边的验证结果：

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 

postgres=#  select current_catalog, current_role, current_schema, current_user,session_user;
 current_catalog | current_role | current_schema | current_user | session_user
-----------------+--------------+----------------+--------------+--------------
 postgres        | postgres     | public         | postgres     | postgres
(1 row)

postgres=# select current_catalog();
ERROR:  syntax error at or near "("
LINE 1: select current_catalog();
                              ^
postgres=# select current_role();
ERROR:  syntax error at or near "("
LINE 1: select current_role();
                           ^
postgres=# select current_schema();
 current_schema
----------------
 public
(1 row)

postgres=# select current_user();
ERROR:  syntax error at or near "("
LINE 1: select current_user();
                           ^
postgres=# select session_user();
ERROR:  syntax error at or near "("
LINE 1: select session_user();
                           ^

postgres=# select current_database();
 current_database
------------------
 postgres
(1 row)

postgres=# select current_database;
ERROR:  column "current_database" does not exist
LINE 1: select current_database;
               ^

那么终级答案在哪里，还是在官方文档当中。

see: https://www.postgresql.org/docs/15/sql-keywords-appendix.html

ITPub博客

PostgreSQL FAQ(2) - 几个有趣的小问题