Alexander Kleinhans's questions

为了论证，我有一个简单的表格。我有一个选择 id 并循环遍历它们的函数，称为loop_test. 我可以选择一组 id 并循环遍历它们，从而导致我在事务中发生更改。

CREATE OR REPLACE FUNCTION loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[];
        _id INTEGER;
BEGIN
        SELECT ARRAY(SELECT id FROM loop_test) INTO _ids_array; 
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET looped = TRUE WHERE id = _id;
        END LOOP;
END;
$$ LANGUAGE plpgsql;

桌子：

db=# \d loop_test;
      Table "public.loop_test"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 id            | integer | 
 other_id      | integer | 
 id_copy       | integer | 
 other_id_copy | integer | 
 looped        | boolean | 

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped 
----+----------+---------+---------------+--------
  1 |       10 |         |               | 
  6 |       15 |         |               | 
  2 |       11 |         |               | 
  7 |       16 |         |               | 
  3 |       12 |         |               | 
  4 |       13 |         |               | 
  5 |       14 |         |               | 
(7 rows)

当我打电话时select loop_test()，我得到以下结果：

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped 
----+----------+---------+---------------+--------
  1 |       10 |         |               | t
  6 |       15 |         |               | t
  2 |       11 |         |               | t
  7 |       16 |         |               | t
  3 |       12 |         |               | t
  4 |       13 |         |               | t
  5 |       14 |         |               | t
(7 rows)

但是，我想创建一个函数来选择 theid和 theother_id到一个数组中。有人告诉我使用类似的东西agg_array，但我不完全理解它是如何工作的。

我在想像下面这样的事情？

CREATE OR REPLACE FUNCTION agg_loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[][];
        _id INTEGER;
BEGIN
        SELECT AGG_ARRAY(SELECT id, other_id FROM loop_test) INTO _ids_array;
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET id_copy = _id[0], other_id_copy = _id[1] WHERE id = _id[0];
        END LOOP;
END;
$$ LANGUAGE plpgsql;

如果我有一个数百万行的表并且我运行一个更新 50k 行的事务，这对性能有什么影响？

假设它的索引正确，它应该不会花费很长时间，但是锁定了哪些行以及该表的使用如何受到影响？

1. 在事务开始之后和结束之前是否可以读取事务期间正在更新的行？
2. 事务期间未更新的行是否可以在事务开始之后和结束之前读取？
3. 如果另一个事务开始尝试更改之前未完成的事务正在更改的行，那么该事务会在开始时失败还是在尝试提交之后失败（假设冲突）？

我的问题是 Postgres 9.3；我假设有变化。

我有这个功能，它很简单：

CREATE OR REPLACE FUNCTION test_function()
RETURNS TABLE(
        id INTEGER,
        organization_id INTEGER,
        email_address CHARACTER VARYING
)
AS $BODY$
        SELECT id, organization_id, email_address FROM users;

$BODY$ LANGUAGE SQL;

这个函数的输出给了我一些看起来像这样的东西：

> SELECT test_function();                                                                                                                                                                                                          test_function 
---------------
 (1,1,test)
 (3,1,test2)
 (4,2,2test)

当我自己运行查询时，我希望得到更像这样的东西：

> select id, organization_id, email_address FROM users;
 id | organization_id | email_address 
----+-----------------+---------------
  1 |               1 | test
  3 |               1 | test2
  4 |               2 | 2test

我怎样才能得到类似的结果，以便我可以在 SQL 函数中解析它？

在 Postgres 中，我正在考虑查询pg_type我定期使用的最新枚举列表。我会使用这样的东西：

SELECT pg_type.typname AS enum_type, pg_enum.enumlabel AS enmu_label FROM 
pg_type JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid;

或者

SELECT distinct pg_type.typname AS enum_type FROM pg_type JOIN pg_enum ON 
pg_enum.enumtypid = pg_type.oid;

这是不好的做法吗？