PostgreSQL：即使扩展已经存在，也提示不存在的问题

在本文中，我们将介绍PostgreSQL中的一个常见问题，即使用dblink扩展时提示“dblink does not exist”错误，尽管扩展已经存在的情况。

阅读更多：PostgreSQL 教程

问题描述

在PostgreSQL中，dblink是一个常用的扩展，可用于在数据库之间创建连接和实现跨数据库查询。在使用dblink扩展之前，需要确认该扩展已经在数据库中安装并可用。然而，有时候即使已经正确安装了dblink扩展，在执行相关操作时仍然会提示“dblink does not exist”错误。

问题解决

出现上述问题的原因通常是因为在创建扩展时使用了错误的模式或者数据库。在PostgreSQL中，扩展可以在不同的模式或数据库中创建。如果在创建扩展时使用了错误的模式或数据库，那么在执行操作时就会提示扩展不存在的错误。

解决该问题的一个常见方法是运行以下查询：

SELECT extname, extnamespace::regnamespace AS schema, extrelocatable
FROM pg_extension
WHERE extname = 'dblink';

上述查询将返回与dblink扩展相关的信息，包括扩展名称、模式以及是否可重定位。确保查询结果中的模式与您当前工作的模式一致。如果查询结果返回多行，那么可能存在多个dblink扩展，需要进一步确认使用的是哪个扩展。

如果查询没有返回结果，那么表示dblink扩展尚未在当前模式中创建。在这种情况下，可以使用以下命令在当前模式中创建dblink扩展：

CREATE EXTENSION IF NOT EXISTS dblink;

该命令将创建dblink扩展，并将其所属模式设置为当前模式。

另外一个导致该问题的原因可能是没有正确加载dblink扩展。你需要确认在PostgreSQL的配置文件中（通常是postgresql.conf）中是否启用了dblink扩展。找到shared_preload_libraries参数，并确保其中包含了dblink。

保存并重新加载PostgreSQL配置文件之后，重新启动PostgreSQL服务，以确保对配置文件的更改生效。

示例说明

假设我们的PostgreSQL数据库中已经正确安装了dblink扩展，并且在名为public的模式中创建了该扩展。现在，我们创建一个用于连接远程数据库的函数，并在执行函数时遇到“dblink does not exist”错误。

首先，我们使用以下查询检查数据库中是否已经创建了dblink扩展：

SELECT extname, extnamespace::regnamespace AS schema, extrelocatable
FROM pg_extension
WHERE extname = 'dblink';

执行上述查询后，我们发现dblink扩展已经正确创建在public模式中。然而，在执行以下函数时仍然遇到错误：

CREATE OR REPLACE FUNCTION remote_query()
RETURNS TABLE (id INT, name TEXT) AS BEGIN
    RETURN QUERY
    SELECT * FROM dblink('dbname=mydb', 'SELECT id, name FROM users') AS t(id INT, name TEXT);
END; LANGUAGE plpgsql;

在执行上述函数时，错误消息提示“dblink does not exist”。

解决该问题的方法是将函数中的dblink扩展的模式从public修改为正确的模式。在这种情况下，我们将函数重新定义为：

CREATE OR REPLACE FUNCTION remote_query()
RETURNS TABLE (id INT, name TEXT) AS BEGIN
    RETURN QUERY
    SELECT * FROM public.dblink('dbname=mydb', 'SELECT id, name FROM users') AS t(id INT, name TEXT);
END; LANGUAGE plpgsql;

通过在函数中指定正确的模式，我们解决了“dblink does not exist”错误。

总结

在本文中，我们解决了使用PostgreSQL的dblink扩展时提示“dblink does not exist”错误的问题。我们介绍了该问题的可能原因，并提供了解决方法。如果您在使用dblink扩展时遇到了类似的问题，希望本文的内容能为您提供帮助。记住，在执行相关操作之前，始终确认扩展已经正确安装并在正确的模式中创建。