sql - 为什么我的表从 pg_catalog.pg_class 中消失了？ (或者，如何找到主键列？)

Question

使用来自 debian 的 postgres 10.4.2(实际上，使用 postgresql docker 容器。)

我有一些按顺序应用的架构文件。这些创建、删除和更改表以跟随架构随时间的演变。

然后，我运行一个程序，从 pg_catalog 中选择表定义来为现有表生成代码包装器。到目前为止，这一直很好。 (这个程序的代码是我开发的，因此非常值得怀疑!)

我最近将“改变表客户添加字段 customer_stem varchar(255) not null default ''”和“create index on customer(customer_stem)”添加到一个新的模式文件中。现在，在查找主键的表查询中不再找到客户表。

我运行的查询是这样的:

    select c.relname, i.indkey 
    from pg_catalog.pg_index i 
    inner join pg_catalog.pg_class c 
      on i.indrelid=c.relfilenode 
    inner join pg_catalog.pg_tables t 
      on c.relname=t.tablename 
    where t.schemaname='public' 
      and i.indisprimary;

这应该告诉我哪些索引是我的表的主键。然而，“customer”表现在不在这个查询中——pg_class 中的 relfilenode 不再匹配 pg_index 中的任何内容。

# select relname, relfilenode from pg_catalog.pg_class where relname='customer';
 relname  | relfilenode
----------+-------------
 customer |       16512

observe_dev=# select count(1) from pg_catalog.pg_index where indrelid=16512;
 count
-------
     0
(1 row)

这张表应该有主键和二级索引!

因此，我的猜测是“alter table”语句以某种方式更改了“customer”类的 ID，使其不再匹配“pg_index”表，但这看起来很奇怪。 PSQL 仍然知道定义是什么:

                                                    Table "public.customer"
    Column     |          Type          | Collation | Nullable |        Default        | Storage  | Stats target | Description
---------------+------------------------+-----------+----------+-----------------------+----------+--------------+-------------
 customer_id   | integer                |           | not null |                       | plain    |              |
 customer_name | character varying(255) |           | not null |                       | extended |              |
 customer_stem | character varying(255) |           | not null | ''::character varying | extended |              |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (customer_id)
    "customer_customer_name_idx" btree (customer_name)
    "customer_customer_stem_idx" btree (customer_stem)
Referenced by:

也有一些外键关系。

因此，以下两种情况之一正在发生:1) alter table 以某种方式导致 pg_catalog 停止更新(这似乎不太可能。)2) 我对如何查找每个表的主键列的研究是错误的，我正在查看错误的表/列

很可能是 2)，但如果是这样，我应该如何找到该信息？

Answer 1

我认为您应该在 pg_class.oid 列上加入 pg_index，即:

select c.relname, i.indkey 
from pg_catalog.pg_index i 
inner join pg_catalog.pg_class c 
  on i.indrelid=c.oid  -- << HERE change c.relfilenode to c.oid 
left join pg_catalog.pg_tables t 
  on c.relname=t.tablename 
where t.schemaname='public' 
  and i.indisprimary;

请注意，oid 隐藏在 psql 中 select * from pg_catalog.pg_class 和 \d pg_catalog.pg_class 的输出中，但它作为“隐藏”列存在。

我能够在本地复制您的问题，所以我猜测添加列或索引会更改表的 pg_class 记录中的 relfilenode 的值。

另见 https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns其中详细说明了如何查询主键 - 可能值得添加到 pg_attribute 的连接中。有点 OT，但我发现值得注意的是 'my_table_name'::regclass 被 PostgreSQL 转换为适当的 pg_class.oid。您实际上可以通过运行 select 'my_table_name'::regclass::oid 来测试(并获取任何表的 oid)!

出于兴趣，我还做了一些进一步的测试，以确定究竟是哪个表更改触发了 pg_class.relfilenode 更改。看来 alter table add column ...default ''` 是原因。

在未指定 default 的情况下添加列时，该值保持不变。以下脚本演示了该行为:

create table test (id uuid, col1 varchar(255));
select c.oid, c.relfilenode, c.relname from pg_catalog.pg_class c where c.oid = 'test'::regclass;
alter table test add column col2 varchar(255) not null;
select c.oid, c.relfilenode, c.relname from pg_catalog.pg_class c where c.oid = 'test'::regclass;
alter table test add column col3 varchar(255) not null default '';
select c.oid, c.relfilenode, c.relname from pg_catalog.pg_class c where c.oid = 'test'::regclass;
drop table test;

因此，由于某种原因，添加具有默认值的列似乎会更改关系的文件节点。 PostgreSQL docs on pg_class 也值得一读有关 relfilenode 和其他属性的更多详细信息。