4

使用 PostgreSQL 游标分页

 2 years ago
source link: https://blog.lilydjwg.me/2013/7/22/paging-with-postgresql-cursors.40060.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

使用 PostgreSQL 游标分页

本文来自依云's Blog，转载请注明。

使用 PostgreSQL 的命名游标对查询结果进行分页，相对于 OFFSET+LIMIT 查询，相当于保留了每个查询结果，避免了在翻页时的重复计算。1, 2, 3

这是我自己的测试结果。数据量不大，这个测试用的结果集才 20 条结果，所以效果不太明显。（好吧，其实我这边目前的数据量也没必要用现在这个复杂的方案。只是尝试新东西而已啦 ^_^）

In [m]: %%timeit
....: for i in range(10):
....:   c.execute(sql_c, (sql_m, i*2, 2))
....:   list(c)
....:
100 loops, best of 3: 9.83 ms per loop
In [n]: %%timeit
....: for i in range(10):
....:   c.execute(sql_m.replace('%', '%%') + ' offset %s limit %s', (i*2, 2))
....:   list(c)
....:
10 loops, best of 3: 19.8 ms per loop

我使用了一个 PostgreSQL 函数来创建或者复用 cursor。此函数输入参数有：查询语句、位置偏移、获取的数量。这个函数会检查是否已经存在对应的 cursor，如果没有就把查询语句的 md5 值加前缀「p」作为名字。查询语句当然是程序拼接的，不会有人工输入的那种意义相同但是某些写法不一样造成的不同。

PostgreSQL cursor 有两个很重要的特性。其一，它的内容不会随着数据的更新而更新。所以，在相关数据更新之后，已经创建的 cursor 的数据就陈旧了。我创建了一个创建触发器的函数以便清理陈这些旧的 cursor。另外，cursor 是会占用内存或者磁盘空间的，因此要清理掉长期不使用的 cursor。为此，我维护了一张记录 cursor 最后使用时间的表，以及一个清理函数。

PostgreSQL cursor 特性之二：即使指定了WITH HOLD，cursor 的生存期也只在当前会话（连接），并且只在当前会话中看得到。所以，清理函数cleanupCursors还需要将没有记录的 cursor 清除。

CREATE OR REPLACE FUNCTION createCursorTable(name text) RETURNS void AS $$
BEGIN
EXECUTE format('CREATE TABLE IF NOT EXISTS %I (
name text UNIQUE,
last_used TIMESTAMP WITH TIME ZONE default current_timestamp
)', name);
EXECUTE format('CREATE INDEX ON %I (last_used)', name);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION createTriggerFor(tname text, cname text) RETURNS void AS $$
BEGIN
EXECUTE format($f$
CREATE TRIGGER %I
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON %I
FOR EACH STATEMENT
EXECUTE PROCEDURE cleanupTriggerFunc (%L)
$f$, 'cleanupCursorForTable_' || tname || '_' || cname,
tname, cname);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION dropTriggerFor(tname text, cname text) RETURNS void AS $$
BEGIN
EXECUTE format($f$ DROP TRIGGER %I on %I $f$,
'cleanupCursorForTable_' || tname || '_' || cname, tname);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION cleanupTriggerFunc() RETURNS TRIGGER AS $$
DECLARE
cname text := TG_ARGV[0];
BEGIN
EXECUTE format('SELECT cleanupCursors(%L, 0)', cname);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION fetchFromCursor(tname text, query text, off integer, size integer)
RETURNS SETOF record AS $$
DECLARE
cname text := 'p' || md5(query);
need_update boolean := false;
BEGIN
PERFORM name FROM pg_cursors WHERE name = cname;
IF NOT FOUND THEN
EXECUTE format('DECLARE %I SCROLL CURSOR WITH HOLD FOR ', cname) || query;
RAISE NOTICE 'new cursor % created', cname;
BEGIN
EXECUTE format('INSERT INTO %I (name) VALUES (%L)', tname, cname);
EXCEPTION
WHEN unique_violation THEN
need_update := true;
END;
ELSE
need_update := true;
END IF;
IF need_update THEN
EXECUTE format('UPDATE %I SET last_used = current_timestamp WHERE name = %L',
tname, cname);
END IF;
EXECUTE format('MOVE ABSOLUTE ' || off || ' FROM %I', cname);
RETURN QUERY EXECUTE format('FETCH ' || size || ' FROM %I', cname);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION cleanupCursors(tname text, timeout real) RETURNS integer AS $$
DECLARE
c record;
i integer := 0;
BEGIN
FOR c IN
EXECUTE format($f$
SELECT name FROM %I
WHERE extract('epoch' from current_timestamp - last_used) > %L
$f$, tname, timeout)
LOOP
PERFORM name FROM pg_cursors WHERE name = c.name;
IF FOUND THEN
RAISE NOTICE 'closing cursor %', c.name;
EXECUTE format('CLOSE %I', c.name);
END IF;
RAISE NOTICE 'clean up record for cursor %', c.name;
EXECUTE format($f$ DELETE FROM %I WHERE name = %L$f$, tname, c.name);
i := i + 1;
END LOOP;
FOR c IN
EXECUTE format($f$
SELECT name FROM pg_cursors WHERE name NOT IN (
SELECT name FROM %I
) AND length(name) = 33 AND substring(name for 1) = 'p'
$f$, tname)
LOOP
RAISE NOTICE 'closing cursor % not present in table %', c.name, tname;
EXECUTE format('CLOSE %I', c.name);
i := i + 1;
END LOOP;
RETURN i;
END;
$$ LANGUAGE plpgsql;

使用时需要经常去调用下cleanupCursors函数。

PostgreSQL 函数还有这么一个特性，当函数返回setof record时，PostgreSQL 不知道怎么解读那些 record。所以用fetchFromCursor函数时得明确指定获取结果的行类型：

select * from fetchFromCursor('cursors', $$select name from users where name like 'a%' order by last_login_time$$, 0, 10) as f(name text);

有点麻烦。

发送到 Kindle

Category: 数据存储 | Tags: PostgreSQL | Read Count: 15002

评论 (2)
yuzd 说:
6 年前

在postgre sql 9.4版本里面 在触发器函数里面 删除不掉游标。。

依云 说:
6 年前

其实不建议这么搞的说。上 Redis 吧，简单明了。

[取消回复评论]

昵称 登录 E-mail: *
Web:
Twitter:
当有新评论通过 E-mail 通知我

loading captcha image...
(输入验证码)

or Ctrl+Enter


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK