主页 / dba / 问题 / 256894
Accepted
Peter Vandivier
Asked: 2020-01-08 10:44:06 +0800 CST

看不到两个同名表

  • 772

我public在一个数据库中有两个非模式。两个模式中都存在一个名为的表"foo"，我可以对两者执行所有必要的 DDL 和 DML 命令。但是，当我执行时\d，我看不到它们。是什么赋予了？

复制品

create schema a;
create schema b;

create table a.foo (i int);
create table b.foo (i int);

set search_path = "$user", public, a, b;

在psql

postgres=# \d
            List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 a      | foo       | table | pvandivier
(1 row)

我\set ECHO_HIDDEN on提取了运行的基础查询\d并将相关行从WHERE子句移到 中，SELECT以检查两个表对象之间的差异。

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  pg_catalog.pg_table_is_visible(c.oid),
  n.nspname,
  c.relkind
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname IN ('a','b')
ORDER BY 1,2;

运行此查询显示...

 Schema | Name | Type  |   Owner    | pg_table_is_visible | nspname | relkind
--------+------+-------+------------+---------------------+---------+---------
 a      | foo  | table | pvandivier | t                   | a       | r
 b      | foo  | table | pvandivier | f                   | b       | r

相关的差异似乎是 的输出pg_table_is_visible()。但是为什么False在询问是否b.foo可见时它应该返回“”？

为什么要pg_table_is_visible()返回False我可以访问的表？

postgresql

1 个回答

  1. Best Answer

    这里的概念is_visible似乎是指“你看到的第 search_path一个”；而不是“你能看到吗”。您可以通过更改search_path.

    postgres=# set search_path = "$user", public, b, a;
SET
postgres=# \d
            List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 b      | foo       | table | pvandivier
(1 row)

postgres=#

    请注意，当在中首先列出b.foo时返回。上一个查询的输出也支持这一点，该查询在同一.bsearch_pathsearch_path

     Schema | Name | Type  |   Owner    | pg_table_is_visible | nspname | relkind
--------+------+-------+------------+---------------------+---------+---------
 a      | foo  | table | pvandivier | f                   | a       | r
 b      | foo  | table | pvandivier | t                   | b       | r
(2 rows)

    我们现在可以清楚地看到pg_table_is_visible()是Trueforb和Falsefor a。因此，“ is_visible”是当前可访问性的指标，而不是关系是否存在的指标。

    请注意，尝试确定不存在对象的可见性具有不同的行为集。

    postgres=# -- attempting to get the oid of a non-existent object
postgres=# select 'c.foo'::regclass;
ERROR:  schema "c" does not exist
LINE 1: select 'c.foo'::regclass;
               ^
postgres=# select 'bar'::regclass::oid;
ERROR:  relation "bar" does not exist
LINE 1: select 'bar'::regclass::oid
               ^
postgres=# -- passing an oid for a non-existent object returns NULL
postgres=# select pg_table_is_visible((random() * 1e7)::int::oid);
 pg_table_is_visible
---------------------

(1 row)

postgres=#

    您也可以在 src/backend/catalog/namespace.c 的源代码中阅读此内容。我对 C 语言不流利，但以下引用似乎支持这一推理。

    Datum
pg_table_is_visible(PG_FUNCTION_ARGS)
{
    Oid         oid = PG_GETARG_OID(0);

    if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(oid)))
        PG_RETURN_NULL();

    PG_RETURN_BOOL(RelationIsVisible(oid));
}

    /*
 * If it is in the path, it might still not be visible; it could be
 * hidden by another relation of the same name earlier in the path. So
 * we must do a slow check for conflicting relations.
 */
    • 2

相关问题