使用Npgsql的C#/ASP.NET应用无法访问部分PostgreSQL表

背景 #

我正在开发一款在线回合制浏览器游戏的后端，使用C#/ASP.NET Core API + Npgsql连接PostgreSQL，已在默认public模式下创建相关表，核心表的创建语句如下：

users表 #

CREATE TABLE IF NOT EXISTS users
(
    user_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    username varchar(32) NOT NULL,
    email varchar(256) NOT NULL,
    password varchar(64) NOT NULL,
    PRIMARY KEY (user_id),
    CONSTRAINT "user_id_UNIQUE" UNIQUE (user_id),
    CONSTRAINT "username_UNIQUE" UNIQUE (username),
    CONSTRAINT "email_UNIQUE" UNIQUE (email)
);

ALTER TABLE IF EXISTS users
    OWNER to postgres;
    
GRANT ALL ON TABLE users TO postgres;

game_requests表 #

CREATE TABLE IF NOT EXISTS game_requests
(
    game_request_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    player_id integer NOT NULL,
    date_created varchar(32) NOT NULL,
    time_control integer NOT NULL,
    PRIMARY KEY (game_request_id),
    CONSTRAINT "game_request_id_UNIQUE" UNIQUE (game_request_id),
    CONSTRAINT "game_requests.player_id" FOREIGN KEY (player_id)
        REFERENCES public.users (user_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE SET NULL
        NOT VALID,
    CONSTRAINT "game_requests.time_control" FOREIGN KEY (time_control)
        REFERENCES public.time_controls (time_control_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);

ALTER TABLE IF EXISTS game_requests
    OWNER to postgres;
    
GRANT ALL ON TABLE game_requests TO postgres;

time_controls表（补充依赖） #

CREATE TABLE IF NOT EXISTS time_controls
(
    time_control_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "time" integer NOT NULL,
    time_unit varchar(16) NOT NULL,
    increment integer NOT NULL,
    increment_unit varchar(16) NOT NULL,
    PRIMARY KEY (time_control_id),
    CONSTRAINT "time_control_id_UNIQUE" UNIQUE (time_control_id)
);

ALTER TABLE IF EXISTS time_controls
    OWNER to postgres;
    
GRANT ALL ON TABLE time_controls TO postgres;

问题 #

通过Npgsql连接可以正常访问users表：

NpgsqlConnection connection = new NpgsqlConnection(GlobalConstants.ConnectionString);
string command = "SELECT * FROM users;";
NpgsqlDataReader reader = await new NpgsqlCommand(command, connection).ExecuteReaderAsync();
Console.WriteLine(reader.HasRows); // 输出true

但查询game_requests表时，ExecuteReaderAsync抛出42P01: relation "game_requests" does not exist异常：

NpgsqlConnection connection = new NpgsqlConnection(GlobalConstants.ConnectionString);
string command = "SELECT * FROM game_requests;";
NpgsqlDataReader reader = await new NpgsqlCommand(command, connection).ExecuteReaderAsync(); // 抛出异常
Console.WriteLine(reader.HasRows);

PGAdmin中使用相同凭据可正常查询game_requests表，确认表存在。

已尝试操作 #

• 执行SELECT * FROM information_schema.tables WHERE table_name='game_requests';：PGAdmin返回该表的public模式信息，但应用中无结果；users表在两者中均能正常返回。

解决方案 #

1. 明确指定表的模式
   修改SQL查询语句，显式指定public模式：

   SELECT * FROM public.game_requests;
   

   若连接的search_path未包含public，会导致默认找不到该模式下的表。

2. 配置连接字符串的默认搜索路径
   在GlobalConstants.ConnectionString中添加Search Path参数，强制使用public作为默认模式：

   Server=你的服务器地址;Database=你的数据库名;User Id=postgres;Password=你的密码;Search Path=public;
   

3. 验证并修复表的约束有效性
   game_requests表的外键约束处于NOT VALID状态，可能导致表无法被正常识别，执行以下SQL验证并修复：

   -- 验证用户是否有查询权限
   SELECT has_table_privilege('postgres', 'public.game_requests', 'SELECT');
   -- 生效外键约束
   ALTER TABLE game_requests VALIDATE CONSTRAINT "game_requests.player_id";
   ALTER TABLE game_requests VALIDATE CONSTRAINT "game_requests.time_control";
   

4. 确认连接的数据库正确性
   排查代码是否连接到了错误的数据库，添加以下代码验证：

   await connection.OpenAsync();
   var currentDb = await new NpgsqlCommand("SELECT current_database();", connection).ExecuteScalarAsync();
   Console.WriteLine($"当前连接数据库：{currentDb}");
   

内容的提问来源于stack exchange，提问作者zai-turner