beldaz's questions

我正在使用 PostgreSQL 10 服务器，其中有一些表包含double precision[]用于存储一维数据数组的属性类型，长度约为 1000。我与数据库交互的代码包含一个错误，该错误将数据作为长度为 1 的数组插入（请参阅我的 psycopg 错误报告，我最终发现这是正在发生的事情），但DBMS 允许这样做：

当前的实现也不强制执行声明的维数。特定元素类型的数组都被认为是同一类型，无论大小或维数如何。因此，在中声明数组大小或维数 CREATE TABLE只是文档；它不会影响运行时行为。

我如何修复 DBMS 中的这些畸形数组（即，使用 SQL 而不是从外部代码更新）？我基本上希望按照最初的预期将每个数组展平为一维数组。

返回的数组数据片段psql是：

{{2.20751909662576},{2.20679071024511},{2.20615506273571},{2.2055910715332},{2.
20507756148068},{2.20459435596551},{2.20412336646322},{2.20364958013081},{2.2031
618420891},{2.20265334228114},{2.20212174686242},{2.20156895755458},{2.201000532
12516},{2.20042484573123},{2.19985210697606},{2.19929335742568},{2.1987595765431
},{2.19826098783408},{2.19780662274576},{2.19740415504621},{2.19705997866427},{2
.19677947299938},{2.1965673849541},{2.19642825612809},{2.1963668334181},{2.19638 ...

在 pgAdmin 3（如果您准备等待更长时间，则为 pgAdmin 4）中，当前所选数据库对象的 DDL 以及相应的触发器、约束等都显示在主面板中，所有这些都经过了很好的格式化。它看起来与从 pgdump 导出的 DDL 完全不同。有没有办法获得更好的版本，而无需从 pgAdmin 手动复制和粘贴？

我有一个 PostgreSQL 9.5 服务器，上面有自动为用户创建角色和数据库的脚本。在这些数据库中，启用特定扩展（例如 pgcrypto）会很有帮助，但据我了解，必须是超级用户才能运行CREATE EXTENSION. 有没有办法在不使用超级用户帐户手动登录的情况下启用此类扩展？

我需要编写动态 DDL 语句，例如 CREATE USER ?。我知道我可以使用 EXECUTE IMMEDIATE 来执行此操作，但到目前为止，我还没有找到在不简单地连接字符串的情况下合并动态参数的方法，这让我很容易受到 SQL 注入的影响。

来自 PostgreSQL 我习惯于能够引用标识符或使用 FORMAT 将标识符安全地放入一个格式化的字符串中，然后可以执行。Oracle（特别是 12c）中有类似的东西吗？如果不是，如何安全地执行这样的动态 SQL？

在下面的示例中，我有一个表格foo，我想从中随机选择每组一行。

CREATE TABLE foo (
  line INT
);
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);

假设我想分组line % 10。我可以这样做：

SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 AS bin, random() x
    FROM foo
    ORDER BY x
) X

我想做的是从每个垃圾箱中随机挑选几次。我原以为我可以用generate_series()和做到这一点LATERAL

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, random() x
    FROM foo
    ORDER BY x
) X
ORDER BY bin) Q
ORDER BY bin, i;

但是，当我在 PostgreSQL 9.5 中执行此操作时，我发现每次迭代都得到相同line的给定值，例如，bini

i;line;bin
1;530;0
2;530;0
3;530;0
1;611;1
2;611;1
3;611;1
...

我很困惑，因为我认为包含的子查询random()对于来自generate_series().

编辑：我意识到我可以通过生成更多组合并从中进行选择来实现相同的目标

SELECT DISTINCT ON (bin, round) round, bin, line
FROM (
    SELECT line, line % 10 as bin, round
    FROM foo, generate_series(1,3) round
    ORDER BY bin, random()
) X;

所以我的问题很简单，为什么第一种方法行不通？

编辑：问题似乎是，如果子查询以某种方式相关（感谢@ypercube 的评论），则 LATERAL 仅充当 for 循环。因此，可以通过添加以下小更改来修复我原来的方法

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(
SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, m.i, random() x -- <NOTE m.i HERE
    FROM foo
    ORDER BY x
) X
ORDER BY bin
LIMIT 3
) Q
ORDER BY bin, i;

这已经出现过几次，例如，在postgresql 新闻组和wiki中。一般来说，不同intervals 之间的关系可能没有很好地定义 - 一个月可以是不同的天数，具体取决于正在考虑的月份（和年份）。但有时需要计算两个时间点之间发生了多少间隔，例如（简化示例）：

CREATE TABLE recordings(tstart timestamp, tend timestamp, interval ticklength);

SELECT (tend - tstart) / ticklength AS numticks
FROM recordings;

这在 PostgreSQL 中是不允许的，因为它是两个间隔之间的划分，由于上述原因，它没有明确定义的一般行为。当间隔可以转换为秒时，存在一种解决方法，但是如果不是这种情况，例如，当间隔为毫秒级时，最好的方法是什么？

我正在使用 PostgreSQL 9.5，我试图了解如何实现 GiST 索引，其中我有一个表示是索引类型的有损压缩版本。例如，假设我将图像存储在BYTEA类型中，并且对于索引，我存储了颜色范围（rmin、rmax、gmin、gmax、bmin、bmax），并且我想根据颜色相似性来比较图像——例如，使用一个===运算符当颜色范围完全相同时返回 true，使我可以方便查询，例如：

SELECT COUNT(*)
FROM icons, avatars
WHERE icon.image === avatar.image AND avatar.id = 123;

其中icons和avatars都是image字段类型为 的表BYTEA。

查看了实施文档后，看起来这应该是可能的。使用上面的示例情况，我认为我可以执行以下操作：

• 该union方法将生成所有条目的边界范围
• picksplit并且penalty只会尝试最小化范围，类似于 R-Tree
• compress将获取 BYTEA 数据并计算颜色范围
• decompress将是一个身份函数
• consistent（对于===运算符）如果条目的颜色范围包含内部节点的查询范围，并且仅当范围与叶节点完全匹配时，才会返回 true。

这是正确的方法吗？我不清楚压缩步骤何时发生。例如，consistent大概在树的不同节点上多次调用。那么这是否意味着每次查询都会重新计算查询数据的颜色范围呢？在索引中，叶节点是否包含图像数据的副本或仅包含其颜色范围？

注意给出的示例仅用于说明目的。我的问题是关于 GiST 中的有损表示，而不是索引图像。

我无法让 INSTEAD OF 触发器正常工作，而且我想我误解了如何使用 NEW。考虑以下简化场景：

CREATE TABLE Product (
  product_id SERIAL PRIMARY KEY,
  product_name VARCHAR
);
CREATE TABLE Purchase (
  purchase_id SERIAL PRIMARY KEY,
  product_id INT REFERENCES Product,
  when_bought DATE
);

CREATE VIEW PurchaseView AS
SELECT purchase_id, product_name, when_bought
FROM Purchase LEFT JOIN Product USING (product_id);

我希望能够创建INSTEAD OF触发器以允许我直接插入PurchaseView，例如：

INSERT INTO Product(product_name) VALUES ('foo');
INSERT INTO PurchaseView(product_name, when_bought) VALUES ('foo', NOW());

我的想法是这样的：

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$BODY$
BEGIN
  INSERT INTO Purchase(product_id, when_bought)
  SELECT product_id, when_bought
  FROM NEW 
  LEFT JOIN Product USING (product_name)
  RETURNING * INTO NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
  INSTEAD OF INSERT
  ON PurchaseView
  FOR EACH ROW
  EXECUTE PROCEDURE insert_purchaseview_func();

然而，上面的触发函数relation "new" does not exist在执行时会给出错误（ ）。我知道我可以编写显式使用and子句NEW中的属性的查询，但有时能够包含在连接中会很方便。有没有办法做到这一点？WHERESELECTNEW

当前（不满意）的解决方案

我能得到的最接近我想要的是

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$BODY$
DECLARE
tmp RECORD;
BEGIN
    WITH input (product_name, when_bought) as (
       values (NEW.product_name, NEW.when_bought)
    ) 
    INSERT INTO Purchase(product_id, when_bought)
    SELECT product_id, when_bought
    FROM input
    LEFT JOIN Product USING (product_name)
    RETURNING * INTO tmp;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

由于以下几个原因，这有点令人不满意：

1. 我需要NEW在 CTE WITH 查询中显式编写 的所有属性，这对于大视图（尤其是那些属性由 自动确定的视图SELECT *）变得笨拙；

2. 返回的结果没有更新SERIAL类型product_id，因此您不会得到预期的结果：

   INSERT INTO PurchaseView(product_name, when_bought) 
   VALUES ('foo', NOW())
   RETURNING *;
   

当一个人想用一个新的大对象替换一个大对象时应该怎么做。一个例子是用新版本更新上传的文件。

一种方法大概是创建一个新的大对象，更新指向新 oid 的链接，然后取消链接旧对象。但是替代现有数据是否合理，比如说，通过覆盖原始数据（可能首先截断内容）？

FWIW 我会在 JDBC 和 PostgreSQL 9.4 中这样做。

我数据库中的许多表共享一个共同的布局，特别是它们有一个serial以关系名称命名的主键。从 Web 应用程序中更新这些表通常涉及以下形式的查询：

UPDATE table SET attribute = x WHERE table_id = y

这很常见，我有一个执行此任务的存储过程：

CREATE OR REPLACE FUNCTION setvalue(
    relname text,
    row_id integer,
    colname text,
    newvalue text)
RETURNS void AS
$BODY$
BEGIN
  EXECUTE format('UPDATE %I SET %I = $1 WHERE %I = $2', relname, colname, relname || '_id', colname) USING row_id;
END;
$BODY$
LANGUAGE plpgsql;

不幸的是，这不适用于非文本类型。例如，更新date列给出ERROR: column ... is of type date but expression is of type text. 假设文本是预期类型的​​有效文字表示，是否有安全的方法让 DBMS 找出正确的类型并做正确的事情？

我有一个 PostgreSQL (9.4) 数据库，它根据当前用户限制对记录的访问，并跟踪用户所做的更改。这是通过视图和触发器实现的，并且在大多数情况下效果很好，但是我遇到了需要INSTEAD OF触发器的视图的问题。我试图减少问题，但我提前道歉，这仍然很长。

情况

与数据库的所有连接都是通过单个帐户从 Web 前端进行的dbweb。连接后，角色更改SET ROLE为对应于使用 Web 界面的人员，并且所有此类角色都属于组角色dbuser。（有关详细信息，请参阅此答案）。假设用户是alice.

我的大多数表都放置在一个模式中，在这里我将调用private它并属于dbowner. 这些表不能直接访问dbuser，而是由另一个角色访问dbview。例如：

SET SESSION AUTHORIZATION dbowner;
CREATE TABLE private.incident
(
  incident_id serial PRIMARY KEY,
  incident_name character varying NOT NULL,
  incident_owner character varying NOT NULL
);
GRANT ALL ON TABLE private.incident TO dbview;

当前用户对特定行的可用性alice由其他视图确定。一个简化的示例（可以减少，但需要以这种方式完成以支持更一般的情况）将是：

-- Simplified case, but in principle could join multiple tables to determine allowed ids
CREATE OR REPLACE VIEW usr_incident AS 
 SELECT incident_id
   FROM private.incident
  WHERE incident_owner  = current_user;
ALTER TABLE usr_incident
  OWNER TO dbview;

然后通过角色可访问的视图提供对行的访问，dbuser例如alice：

CREATE OR REPLACE VIEW public.incident AS 
 SELECT incident.*
   FROM private.incident
  WHERE (incident_id IN ( SELECT incident_id
           FROM usr_incident));
ALTER TABLE public.incident
  OWNER TO dbview;
GRANT ALL ON TABLE public.incident TO dbuser;

请注意，因为FROM子句中只出现一个关系，所以这种视图是可更新的，无需任何额外的触发器。

对于日志记录，存在另一个表来记录更改了哪个表以及谁更改了它。精简版是：

CREATE TABLE private.audit
(
  audit_id serial PRIMATE KEY,
  table_name text NOT NULL,
  user_name text NOT NULL
);
GRANT INSERT ON TABLE private.audit TO dbuser;

这是通过放置在我希望跟踪的每个关系上的触发器填充的。例如，private.incident仅限于插入的示例是：

CREATE OR REPLACE FUNCTION private.if_modified_func()
  RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO private.audit (table_name, user_name)
        VALUES (tg_table_name::text, current_user::text);
        RETURN NEW;
    END IF;
END;
$BODY$
  LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION private.if_modified_func() TO dbuser;

CREATE TRIGGER log_incident
AFTER INSERT ON private.incident
FOR EACH ROW
EXECUTE PROCEDURE private.if_modified_func();

所以现在如果alice插入，审计中会出现public.incident一条记录。('incident','alice')

问题

INSTEAD OF当视图变得更复杂并且需要触发器来支持插入时，这种方法会遇到问题。

假设我有两个关系，例如表示涉及一些多对一关系的实体：

CREATE TABLE private.driver
(
  driver_id serial PRIMARY KEY,
  driver_name text NOT NULL
);
GRANT ALL ON TABLE private.driver TO dbview;

CREATE TABLE private.vehicle
(
  vehicle_id serial PRIMARY KEY,
  incident_id integer REFERENCES private.incident,
  make text NOT NULL,
  model text NOT NULL,
  driver_id integer NOT NULL REFERENCES private.driver
);
GRANT ALL ON TABLE private.vehicle TO dbview;

假设我不想公开除名称之外的详细信息private.driver，因此有一个视图可以连接表并投影我想要公开的位：

CREATE OR REPLACE VIEW public.vehicle AS 
 SELECT vehicle_id, make, model, driver_name
   FROM private.driver
   JOIN private.vehicle USING (driver_id)
  WHERE (incident_id IN ( SELECT incident_id
               FROM usr_incident));
ALTER TABLE public.vehicle OWNER TO dbview;
GRANT ALL ON TABLE public.vehicle TO dbuser;

为了alice能够插入此视图，必须提供触发器，例如：

CREATE OR REPLACE FUNCTION vehicle_vw_insert()
  RETURNS trigger AS
$BODY$
DECLARE did INTEGER;
   BEGIN
     INSERT INTO private.driver(driver_name) VALUES(NEW.driver_name) RETURNING driver_id INTO did;
     INSERT INTO private.vehicle(make, model, driver_id) VALUES(NEW.make_id,NEW.model, did) RETURNING vehicle_id INTO NEW.vehicle_id;
     RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION vehicle_vw_insert()
  OWNER TO dbowner;
GRANT EXECUTE ON FUNCTION vehicle_vw_insert() TO dbuser;

CREATE TRIGGER vehicle_vw_insert_trig
INSTEAD OF INSERT ON public.vehicle
FOR EACH ROW
EXECUTE PROCEDURE vehicle_vw_insert();

这样做的问题是SECURITY DEFINER触发器函数中的选项导致它以current_user设置为运行dbowner，因此如果alice将新记录插入到视图中，则private.audit记录中的相应条目是作者dbowner。

那么，有没有办法在current_user不让dbuser组角色直接访问模式中的关系的情况下保留private？

部分解决方案

正如 Craig 所建议的，使用规则而不是触发器可以避免更改current_user. 使用上面的示例，可以使用以下内容代替更新触发器：

CREATE OR REPLACE RULE update_vehicle_view AS
  ON UPDATE TO vehicle
  DO INSTEAD
     ( 
      UPDATE private.vehicle
        SET make = NEW.make,
            model = NEW.model
      WHERE vehicle_id = OLD.vehicle_id
       AND (NEW.incident_id IN ( SELECT incident_id
                   FROM usr_incident));
     UPDATE private.driver
        SET driver_name = NEW.driver_name
       FROM private.vehicle v
      WHERE driver_id = v.driver_id
      AND vehicle_id = OLD.vehicle_id
      AND (NEW.incident_id IN ( SELECT incident_id
                   FROM usr_incident));               
   )

这保留了current_user. 不过，支持RETURNING性条款可能有点麻烦。此外，我找不到安全的方法来使用规则同时插入两个表以处理对driver_id. 最简单的方法是WITH在 (CTE) 中使用子句INSERT，但这些子句不允许与NEW(error: rules cannot refer to NEW within WITH query)一起使用lastval()，因此强烈建议不要使用该子句。

我有一个 Java/JDBC 程序，它获取一个样本文件并将数据导入数据库，将其分解成多个关系。该程序针对几种不同的表示多次执行此操作，其中一种使用大对象。我可以分享这些表示的更多细节，但它们很长并且与这个问题无关，因为我正在寻找通用的东西。

我想通过在每次导入后检查数据库的大小来比较这些不同表示的大小。数据库位于 PostgreSQL 9.4 本地 Windows 服务器实例上，没有其他用户和默认配置。它的唯一目的是进行此测试。

我最初的计划如下：

for each representation {
  call VACUUM ANALYZE
  record old DB size with SELECT pg_tablespace_size('pg_default');
  import data into database
  call VACUUM ANALYZE
  record new DB size with SELECT pg_tablespace_size('pg_default');
  store storage cost as new DB size - old DB size
}

显然这种方法有局限性，但我的期望是对于大文件（~100MB），报告的存储成本应该是合理的近似值。请注意，我使用pg_tablespace_sizeorder 来包括主模式之外的数据贡献，例如大对象（inpg_catalog.pg_largeobject和pg_catalog.pg_largeobject_metadata）。

我想知道这是否是一种正确的方法，以及是否有更好的方法。我不确定是否VACUUM ANALYZE正确更新了 所使用的统计信息pg_tablespace_size，即使它是在同一会话中调用的。如果我能避免调用 也会更好VACUUM ANALYZE，因为这需要以超级用户身份连接才能在pg_catalog关系上运行。

有什么想法吗？

我希望将大型（100Mb -- 1 GB）多通道时间序列数据导入 PostgreSQL 数据库。数据来自EDF 格式文件，这些文件将数据分块为“记录”或“纪元”，每个记录通常为几秒。每个纪元的记录将每个数据通道的信号保存为短整数的顺序数组。

我被要求将文件存储在数据库中，在最坏的情况下作为 BLOB。鉴于此，我想研究允许我对数据库中的数据做更多事情的选项，例如促进基于信号数据的查询。

我最初的计划是将数据存储为每个纪元记录一行。我要权衡的是将实际信号数据存储为 bytea 还是 smallint[]（甚至是 smallint[][]）类型。任何人都可以推荐一个吗？我对存储和访问成本感兴趣。用法很可能是插入一次，偶尔读取，从不更新。如果一个更容易包装为自定义类型，以便我可以添加用于分析比较记录的函数，那就更好了。

毫无疑问，我缺乏细节，所以请随时对您希望我澄清的内容发表评论。

我有三个表，代表两个实体和一个连接关系。每个实体关系都有一个用于外键约束的人工代理键​​来代替自然键。

CREATE TABLE Person (
person_id SERIAL PRIMARY KEY,
username VARCHAR NOT NULL UNIQUE
);

CREATE TABLE Group (
group_id SERIAL PRIMARY KEY,
group_name VARCHAR NOT NULL UNIQUE
);

CREATE TABLE GroupMember (
person_id INTEGER REFERENCES Person,
group_id INTEGER REFERENCES Group,
PRIMARY KEY (person_id, group_id)
);

我想在关系中插入多个条目GroupMember，但使用关联实体的自然键。类似于以下内容：

INSERT INTO GroupMember (person_id,group_id)
SELECT person_id,group_id
FROM Person P, Group G, (
('alice','girls'),
('bob','boys'),
('alice','coolkids'),
('bob','coolkids')
) AS X
WHERE P.username = X.1 AND G.group_name = X.2;

显然上面的语法是无效的，但希望能传达这个想法。有没有办法在 PostgreSQL 中做到这一点？

在我收到的对上一个问题的非常有用的回复中，我正在尝试编写一些 JDBC 代码，在执行后续查询之前首先将角色设置为特定用户。为了安全起见，我想通过参数化SET ROLE语句来防止 SQL 注入攻击。我在 Groovy（使用 JDBC）中的方法是：

def sql = Sql.newInstance('jdbc:postgresql:mydb', 'mydbweb', 'mydbwebpass', 'org.postgresql.Driver')
sql.execute 'SET ROLE ?', user

但这会产生语法错误。文档说可以采用SET ROLE字符串文字，但我只是不清楚如何有效地传递它。有什么建议么？

以下似乎是一个相当普遍的情况，但我还没有看到任何解决方案。

我有一个 PostgreSQL 数据库，其中包含特定于 的视图，$user以便根据某些特定于数据库的 ACL 逻辑来限制数据。数据库通常通过 JDBC 访问，特别是在 JSP 中用于 Web 界面。用户使用 LDAP 使用密码验证登录网站。

我想要的是利用从 JDBC 连接到数据库的视图。但通常这种中间层连接是使用通用用户创建的，而不是特定于登录网站的人。我相信在 Oracle 中，“正确”的方法是Proxy Authentication，但 AFAIAA 没有 PostgreSQL 的等价物。那么该怎么做呢？

一些想法浮现在脑海：

• 为所有用户的数据库帐户提供相同的密码，然后使用这个通用密码创建用户特定的 JDBC 连接（这感觉太不对劲了……）
• 将用户的登录密码存储在网站的某处，以便在创建数据库连接时重复使用（再次，这感觉非常错误）

还有其他（希望是更好的）建议吗？