postgresql - PL/pgSQL函数随机选择一个id

Question

目标:

1. 使用顺序 ID 列表预填充表格，例如1 到 1,000,000。该表有一个可以为空的附加列。 NULL 值标记为未分配，非 NULL 值标记为已分配
2. 有我可以调用的函数，它要求从表中随机选择 x 个尚未分配的 ID。

这是针对一些非常具体的事情，虽然我知道有不同的方法可以做到这一点，但我想知道是否有解决这个特定实现中的缺陷的方法。

我有一些东西可以部分工作，但想知道功能中的缺陷在哪里。

这是表格:

CREATE SEQUENCE accounts_seq MINVALUE 700000000001 NO MAXVALUE;

CREATE TABLE accounts (
  id BIGINT PRIMARY KEY default nextval('accounts_seq'), 
  client VARCHAR(25), UNIQUE(id, client)
);

此函数 gen_account_ids 只是一次性设置，用固定数量的行预填充表，所有行都标记为未分配。

/*
  This function will insert new rows into the accounts table with ids being
  generated by a sequence, and client being NULL.  A NULL client indicates
  the account has not yet been assigned.
*/
CREATE OR REPLACE FUNCTION gen_account_ids(bigint)
  RETURNS INT AS $gen_account_ids$
DECLARE
  -- count is the number of new accounts you want generated
  count alias for $1;
  -- rowcount is returned as the number of rows inserted
  rowcount int;
BEGIN
  INSERT INTO accounts(client) SELECT NULL FROM generate_series(1, count);
  GET DIAGNOSTICS rowcount = ROW_COUNT;
  RETURN rowcount;
END;
$gen_account_ids$ LANGUAGE plpgsql;

所以，我用它来预填充表格，比如说 1000 条记录:

SELECT gen_account_ids(1000);

下一个函数 assign 是为了随机选择一个 unassigned id(未分配意味着 client 列为空)，并用一个更新它客户值(value)，因此它被分配。它返回受影响的行数。

它有时有效，但我确实相信会发生冲突——这就是我尝试使用 DISTINCT 的原因，但它返回的行数通常少于所需的行数.例如，如果我 select assign(100, 'foo'); 它可能会返回 95 行而不是所需的 100 行。

我如何修改它以使其始终返回准确的所需行？

   /*
     This will assign ids to a client randomly
     @param int is the number of account numbers to generate
     @param varchar(10) is a string descriptor for the client
     @returns the number of rows affected -- should be the same as the input int

     Call it like this: `SELECT * FROM assign(100, 'FOO')`
   */
   CREATE OR REPLACE FUNCTION assign(INT, VARCHAR(10))
     RETURNS INT AS $$
   DECLARE
     total ALIAS FOR $1;
     clientname ALIAS FOR $2;
     rowcount int;
   BEGIN
     UPDATE accounts SET client = clientname WHERE id IN (
       SELECT DISTINCT trunc(random() * (
         (SELECT max(id) FROM accounts WHERE client IS NULL) - 
         (SELECT min(id) FROM accounts WHERE client IS NULL)) + 
         (SELECT min(id) FROM accounts WHERE client IS NULL)) FROM generate_series(1, total));
     GET DIAGNOSTICS rowcount = ROW_COUNT;
     RETURN rowcount;
   END;
   $$ LANGUAGE plpgsql;

这大致基于 this您可以在其中执行类似 SELECT trunc(random() * (100 - 1) + 1) FROM generate_series(1,5); 之类的操作，它将选择 1 到 100 之间的 5 个随机数。

我的目标是做类似的事情，我在最小和最大未分配行之间选择一个随机 ID，并将其标记为更新。

Answer 1

这不是最佳答案 b/c 它确实涉及全表扫描，但在我的情况下，我不关心性能，而且它有效。这是基于@CraigRinger 对博客文章 getting random tuples 的引用。

我通常有兴趣了解其他(也许更好)的解决方案——并且特别好奇为什么原始解决方案不尽如人意，以及@klin 还设计了什么。

所以，这是我的暴力随机排序解决方案:

-- generate a million unassigned rows with null client column
insert into accounts(client) select null from generate_series(1, 1000000);

-- assign 1000 random rows to client 'foo'
update accounts set client = 'foo' where id in 
  (select id from accounts where client is null order by random() limit 1000);