Stephane Rolland's questions

我在 Ubuntu 12.04 上使用 PostgreSQL 9.1。

我需要在一段时间内选择记录：我的表time_limits有两个timestamp字段和一个integer属性。我的实际表中有其他列与此查询无关。

create table (
   start_date_time timestamp,
   end_date_time timestamp, 
   id_phi integer, 
   primary key(start_date_time, end_date_time,id_phi);

该表包含大约 2M 条记录。

像下面这样的查询花费了大量的时间：

select * from time_limits as t 
where t.id_phi=0 
and t.start_date_time <= timestamp'2010-08-08 00:00:00'
and t.end_date_time   >= timestamp'2010-08-08 00:05:00';

所以我尝试添加另一个索引 - PK的倒数：

create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);

我的印象是性能提高了：访问表中间记录的时间似乎更合理：大约在 40 到 90 秒之间。

但是对于时间范围中间的值，它仍然是几十秒。当目标是表的末尾时（按时间顺序），还有两倍。

我explain analyze第一次尝试得到这个查询计划：

 Bitmap Heap Scan on time_limits  (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1)
   Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
   ->  Bitmap Index Scan on idx_time_limits_phi_start_end  (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1)
         Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
 Total runtime: 44.507 ms

在 depesz.com 上查看结果。

我可以做些什么来优化搜索？id_phi您可以看到一旦设置为 ，扫描两个时间戳列所花费的所有时间0。而且我不理解时间戳上的大扫描（60K 行！）。他们不是由主键索引并且idx_inversed我添加的吗？

我应该从时间戳类型更改为其他类型吗？

我已经阅读了一些关于 GIST 和 GIN 索引的内容。我收集它们可以在自定义类型的某些条件下更有效。对于我的用例来说，这是一个可行的选择吗？

我使用 Postgresql 9.1 和 ubuntu 12.04。

受 Craig 对我的问题Concatenation of setof type or setof record的启发，我想我可以很好地使用return query,setof record和一个系列生成器到这个 plpgsql 函数中：

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns setof record as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;     
end;
$$    language plpgsql;

在执行期间，我收到错误：

ERROR: set_valued function called in context that cannot accept a set

怎么了 ？与克雷格相反，我告诉函数 return setof record。

我可以实现与 Craig 完全一样的工作，即通过定义一个类型create type pair_id_value as (idx bigint, value integer)并让我的 plpgsql 函数返回 asetof of pair_id_value而不是 a setof record。

但是即使使用这个可行的解决方案，我仍然不明白为什么select id, generate_series(0,13)单独会在两列中返回结果......相反，调用函数（返回 setof pair_id_value）只会在字段看起来像的一列return query select id, generate_series(0,my_obj.value) from my_obj中返回结果这个“（123123,0）”“（123123,1）”“（123123,2）”（3行）显然是元组。

是否必须/应该创建临时表？

我将 Postgresql 9.1 与 Ubuntu 12.04 一起使用。

在一个plpgsql函数中，我尝试连接setof从另一个函数返回的类型。

有type pair_id_value问题是用create type pair_id_value as (id bigint, value integer);

返回基本的函数setof pair_id_value（稍后将连接的函数）是这个：

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
    listResults = []
    for x in range(0,value+1):
        listResults.append({ "id": id, "value": x})
    return listResults
$$
language plpython3u;

这个直截了当的 plpython 代码应该很好，例如查询：select * from compute_pair_id_value(1712437,2);很好地返回：

  id            | value 
 ---------------+-----------
        1712437 |         0
        1712437 |         1
        1712437 |         2
 (3 rows)

这个python函数现在相当简单，对于这个例子，但最重要的是我的概念证明。在不久的将来它会变得更加复杂。

当我尝试从多个 id 连接所有结果表时，就会出现问题。

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof pair_id_value as $$
declare
    pair pair_id_value;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return; 
end; $$ language plpgsql;

我收到错误：invalid input syntax for integer "(1712437,0)"好像它不再被视为具有两列的 pair_id_value 而是作为元组 (1712437,0)。

所以我将函数的输出类型从 setof pair_id_value 更改为 setof record... 如果我执行这个类似的连接函数：

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value)  from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return; 
end; $$ language plpgsql;

我得到错误：a column definition list is required for functions returning "record"

试图遵循这个 SO 问题的答案：我尝试以这种方式在 select 中定义列定义select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer)，完整的代码在这里：

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return; 
end; $$ language plpgsql;

但是在启动 sql 脚本时，psql 不接受创建函数： syntax error at or near "(" select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer)... 将手指指向 f(

知道如何正确地做到这一点吗？

我应该考虑创建临时表来完成这项工作吗？

在这个关于存储过程的SO 问题plpgsql中，存储过程看起来像sth.name()。不知道前缀是什么意思sth.

例如：

create or replace function something.function_name()
returns setof record as
$$
-- code
$$ language plpgsql;

在“第四部分：使用PostgreSQL编程”中查看这本书，我发现没有提到这种带有函数名的创建声明在两部分中。

在 postgresql 文档中，在 create function section中，唯一相似的是当它们处理argtypeor时regtype可以写成以下形式：table_name.column_name%TYPE。但它与函数名称无关。

那么，这个语法与什么有关？

在查看 and 的几个示例时pl/python，pl/pgsql我看到了很多（但不是全部）使用volatile cost.

IE：

CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- code
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

搜索有关我的更多信息时，volatile cost我发现（乍一看）大约至少 90% 的 Web 示例都在使用. （因为它是 1000）。volatile cost 100volatile cost 1rows

据我了解，此指示有助于查询计划优化器决定如何在短路布尔操作中设置优先级。

如果我开始估计cost或rows为我的每个存储过程提供估计，是不是过早的优化？我应该只在我想优化某些查询时才这样做吗？选择成本的好价值是一门艺术吗？

我知道 command explain，我还没有学过。这个命令对估计有帮助cost吗？

作为 postgres 用户，我create extension plpython3u;的数据库中有

然后我将其设置plpython3u为trusted：select lanpltrusted from pg_language where lanname like 'plpython3u';返回true

但是当我的 db_user 尝试

  create function check_data_valid(id bigint)
    returns boolean
    as $$
    -- ...
    return true
    $$ language plpython3u;

我得到了错误：permission denied for the language plpython3u

grant usage on plpython3u to db_user因此，对于我的 postgres 用户，我已经尝试过：grant execute on plpython3u to db_user但两者都返回错误：

relation python doesn't exist

也许是因为它是一个扩展......但是，我不知道该怎么做才能创建我的存储过程。

我想在我的 postgresql 数据库中使用 python3 来编写存储过程。

在psql客户端中，当我输入命令时create extension plpython3u出现错误：

couldn't open extension control file /usr/share/postgresql/9.1/extension/plpython3u.control : No such file or directory

我检查了，目录中有 plpythonu.control 和 plpython2u.control，但不是版本 3 的那个。

但是我已经从仓库安装了软件包python3和python3-postgresql（以及其他）。我使用的是 Ubuntu 12.04，内核 3.2.0.38，安装了 postgresql 9.1。

我应该安装（或做什么）以在我的机器上安装 plpython3u.control 文件并在我的数据库中使用 python3？

我在 ubuntu 12.04 上使用 postgresql 9.1。我已经安装了 depot 包。

我在我的系统中添加了一个新分区，我想在这个分区上创建一个 postgresql 数据库（它将完全专用于这个数据库）。

我的 pgdata 位于var/lib/postgresql/9.1/main. 我打算停止 postgresql 服务，将 pgdata 内容复制到新分区，然后对新分区进行符号链接，chown将新目录指向 postgres 用户，重新启动 postgresql ......但这一切看起来更像是一个黑客。

有没有办法专门在指定分区上创建数据库？更“规范”的东西（不是用 ubuntu 玩文字游戏）

这是我的配置：

#postgres.conf:

listen_addresses = 'localhost'

#pg_hba.conf:

local all postgres md5
local all appuser trust
local all devuser trust
local all all peer

我正在使用 C-Library 在本地连接libpq。我的连接字符串是：

host=localhost user=appuser dbname=mydbname port='5432'

但是，除非我的开发人员用户 (devuser)（启动与 libpq 链接的应用程序的人）的家中有一个 .pgpass 文件，否则我的可执行文件无法连接，我会收到错误消息fe_sendauth : no password supplied

正如我所说，如果 .pgpass 文件在这里，那么它可以连接。

我不明白的是：我配置local all appuser 了 trust。为什么我同样需要一个 .pgpass 文件？

（这本身并不是一个严重的问题，因为稍后出于安全措施，它将不再位于trust, 但是md5，所以无论如何我都会拥有 .pgpass 文件。但我想知道）

我想知道这个例子

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

cityas的定义在varchar(80) 这里重复。PostgreSQL 有没有语法允许不重复varchar(80)，只根据自己references cities(city)来创建天气表？

我在 libpq 上为PostrgreSQL制作了一个远程应用程序草案。它表现良好，但我已经分析了应用程序的一般功能。对于我产生的每个最终业务结果，我都会调用类似 40 select 子句（通过 tcpip）。

我有来自 SQL-Server 的回忆，提醒我尽量减少远程应用程序和数据库之间的交互次数。在分析了我的选择之后，我确实认为我可以SELECT使用连接将这个数字减少到 3 个子句。但我不记得SELECT在 another中使用 a 结果的语法SELECT。

例如：

SELECT * FROM individual
INNER JOIN publisher
ON individual.individual_id = publisher.individual_id
WHERE individual.individual_id = 'here I would like to use the results of a another select'

这另一个SELECT将是简单的那种：

SELECT identifier FROM another_table WHERE something='something'

这是简化的表格布局，针对不同的 item_types 拒绝了多次......（3 种完全不同的类型，因此如果优化了 3 个 SQL 查询）。

table passage
  id_passage PK
  business_field_passage bytea

table item
  id_item PK
  id_passage FK
  business_field_item text

table item_detail
  id_item_detail PK
  id_item FK
  business_field_item_detail text
  image_content bytea

有几个id_item为一id_passage。
有几个id_item_detail为一id_item。

你会怎么写？
描述将一个选择重定向到另一个选择（如果有）的操作的名称是什么？

当我尝试pg_restore.exe从数据库执行转储文件时，它会抛出许多错误，都是一样的：

ERROR: insert or update on table "someTable" violates foreign key constraint "aConstraintName"

这显然是因为我在从转储文件恢复之前清空了数据库（这个文件来自生产数据库）......那么如果一个引用的表是空的，那么当然没有外键约束是可以的...

有没有办法在我调用之前禁用所有表的约束和所有外键，pg_restore.exe然后重新启用约束和外键。

在 SO 我发现了一些有趣的东西：将约束检查推迟到提交时间。但我认为在推迟约束后我不能pg_restore.exe从内部打电话。psql.exe

还有这个帖子，可以追溯到 10 年前，建议放弃然后重新添加约束。或者将 pg_class reltriggers 的值更改为 0，这对于约束也是可能的......但我担心它比好的做法更像是黑客攻击......

你有什么建议，在这种情况下最好的做法是什么？pg_dump.exe 与-clean标志一起使用是否会创建一个在还原数据库时绕过约束检查的转储？

我不知道这个问题更适合这里还是 SO ...

这是我想启动的脚本（函数的代码是从 SO 上的问题复制的）：

\c mydb

create or replace function truncate_tables(username in varchar) returns void as $$
declare
    stmt RECORD;
    statements cursor for select tablename from pg_tables where tableowner = username;
begin 
    for stmt in statements loop
        execute 'truncate table ' || quote_ident(stmt.tablename) || ' CASCADE ;';
    end loop;
end;
$$ language 'plpgsql';

我收到以下错误：

ERROR: syntax at or near "$1"    LINE1:   $1
QUERY $1
CONTEXT: SQL statement in PL/PgSQL function "truncate_tables" near line 5

我是 Postgres 和 PL/pgSQL 的新手，不知道这个错误信息是什么意思。

我想启动命令：pg_dump.exe -h 255.255.255.255 -p 5432 -U postgres -F p -a -v -f "test.db.dump" mydb从远程计算机。数据库和远程计算机都在 xp 下。

我面临两个麻烦：

1. 在服务器的命令行上执行时，pgdump 将首先提示输入密码。如何在命令行中授予密码？快速查看 pg_dump 的文档显然不接受这样做的标志（尽管我可能错过了它）。

2. 如果两台机器都在linux下，我会尝试与Putty建立SSH连接以在psql服务器上执行一些命令......但是两台机器都在windows下，我不知道从哪里开始。psqlODBC 驱动程序是否允许访问 pg_dump ？是否有相当于 Windows 的 SSL？

只是为了解释上下文，实际上我的主要目标是在远程机器上实现一个诊断脚本，该脚本将收集一些有意义的信息，包括 postgresql 库的部分或全部表的转储。诊断脚本会将转储复制到域上收集所有诊断结果的另一个位置。

该脚本必须在远程机器上执行，因为它还必须在连接到域但不是 postgresql 数据库的其他计算机上执行其他操作。

出于安全原因，更改 Microsoft Sql Server 的端口是很常见的。该主题在更改 SQL Server 端口真的那么安全吗？

我的问题是：这是否也适用于 PostgreSQL？还是无关的？

我正在数据库中寻找我不知道的精确信息。数据库在单独的机器上，但我可以登录到它，并psql以管理员权限启动命令行。

这是第三方产品，他们回答问题的速度很慢。我知道数据在那个数据库中，所以我想做一些逆向工程。

给定一个表名，是否可以获得该表中列名的列表？

例如，在 SQL Server 中，可以将表转储到可重用的CREATE语句中，该语句以文本形式列出组成该表的所有列。