主页 / dba / 问题 / 16909
Accepted
filiprem
Asked: 2012-04-24 10:15:17 +0800 CST

在 PostgreSQL 中旋转表

  • 772

问题描述

我需要旋转（使用 DROP 和 CREATE）一个被其他客户端大量使用的表。

目前，我有一个替换（DROP + CREATE）这个表的程序。

有时，在表被替换后，我从访问表的并发客户端收到“错误：无法打开与 OID xyz 的关系”。

可以说这种行为破坏了事务隔离......是吗？

我知道这是由 postgres 后端缓存的系统目录引起的（这通常是一件好事）。我是对的吗？

有什么方法可以强制后端“忘记”表 OID（DISCARD没有帮助）？

我知道如果我切换到“删除和插入”模式而不是“删除和创建”模式会有所帮助。但这是一些遗留程序，除非绝对需要，否则我们不想更改它。

对于如何摆脱这个问题的任何建议，我将不胜感激。

目标是为其他客户透明地旋转表格。

测试用例

这是我将问题简化为的最小测试用例：

A. 这是客户端（多个客户端将并行运行此 SELECT）。我们将使用pgbench来强调数据库。

$ echo "SELECT num FROM x;" > pgbench.minimal.sql

B. 这是“旋转器”。

$ cat > rotate.x.sql <<EOF
BEGIN;
DROP TABLE x;
CREATE TABLE x ( num integer );
COMMIT;
EOF

C. 创建空数据库和表 x。

$ createdb dev
$ psql dev -c "CREATE TABLE x ( num integer )"

D. 启动 30 个客户。

$ pgbench -c 30 dev -T 60 -n -f pgbench.minimal.sql

E.（在其他终端）运行“旋转器”。

$ psql dev -f rotate.x.sql

F. 观察客户端发生了什么（就在“旋转器”提交之后）。

Client 4 aborted in state 1: ERROR:  could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
                        ^
Client 0 aborted in state 1: ERROR:  could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
                        ^

（......等等 - 每个客户端都失败了）

评论和想法

有趣的是——即使客户端在“每个事务的新连接”模式下工作（向 pgbench 添加“-C”选项），也会发生同样的错误。并非总是如此，也不是针对每个客户，但确实如此。

我前段时间在邮件列表上问过这个问题：http: //archives.postgresql.org/pgsql-general/2012-04/msg00431.php - 这个 SO 帖子只是一个副本。

对于更多观众：同样适用于旋转分区。因此，对于所有使用分区的人来说，这可能会很有趣。

这一切都在 PostgreSQL 9.0、Linux 上。

解决方案

（感谢克里斯特拉弗斯）

filip@srv:~$ cat rotate.x.sql
BEGIN;
DROP TABLE IF EXISTS xold;
ALTER TABLE x RENAME TO xold;
CREATE TABLE x ( num integer );
COMMIT;
postgresql partitioning

2 个回答

  1. Best Answer

    ALTER TABLE RENAME 后跟 CREATE TABLE 怎么样？

    • 4

  2. 根据我在这里找到的：

    这是最优化的方式：

    CREATE TABLE parent ... ;
CREATE TABLE child_1() INHERITS (parent);
CREATE TABLE child_2() INHERITS (parent);

    INSERT命令总是在child_1.

    SELECT命令在parent.

    根据您的选择，运行以下 sql 进行child_1旋转child_2：

    TRUNCATE TABLE child_2;
BEGIN;
ALTER TABLE child_1 RENAME TO child_tmp;
ALTER TABLE child_2 RENAME TO child_1;
ALTER TABLE child_tmp RENAME TO child_2;
COMMIT;

    这比

    DELETE FROM parent WHERE time < now() - interval 'given period'

    这也避免了表碎片，因此 SELECT 查询在表上工作得更快。

    • 0

相关问题