时间戳

首页 > 解决方案 > 为什么我的表从 pg_catalog.pg_class 中消失了？（或者，如何找到主键列？）

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

问题描述

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

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

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

我最近将“alter table customer add field 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;

这应该告诉我哪些索引是我的表的主键。然而，“客户”表现在从这个查询中消失了——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)，但如果是这样，我应该如何查找该信息？

标签： sqlpostgresqlpostgresql-10

解决方案

我认为您可能应该加入pg_index该pg_class.oid专栏，即：

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;

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

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

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

只是为了感兴趣，我还做了一些进一步的测试，以确定究竟是哪个表更改触发了pg_class.relfilenode更改。似乎alter table add column ...默认的''`是原因。

当添加列而不指定 adefault时，值保持不变。以下脚本演示了该行为：

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 文档以pg_classrelfilenode获取有关其他属性的更多详细信息。

推荐阅读