主页 / dba / 问题 / 51594
Accepted
Stephan
Asked: 2013-10-17 04:11:00 +0800 CST

如何解决“元组同时更新”错误？

  • 772

以下查询由使用两个不同用户登录的两个线程同时执行：

WITH raw_stat AS (
    SELECT
       host(client_addr) as client_addr,
       pid ,
       usename
    FROM pg_stat_activity
    WHERE usename = current_user
)
INSERT INTO my_stat(id, client_addr, pid, usename)
    SELECT nextval('mystat_sequence'), t.client_addr, t.pid, t.usename
    FROM (
        SELECT client_addr, pid, usename
        FROM raw_stat s
        WHERE
            NOT EXISTS (
               SELECT NULL
               FROM my_stat u
               WHERE current_date = u.creation
                   AND s.pid = u.pid
                   AND s.client_addr = u.client_addr
                   AND s.usename = u.usename
            )
    ) t;

有时，我会收到以下错误：

tuple concurrently updated

我无法弄清楚引发此错误的原因以及引发此错误的原因。你能发光吗？

这是表 my_stat 的 sql 定义。

CREATE TABLE my_stat
(
  id bigint NOT NULL,
  creation date NOT NULL DEFAULT current_date,

  client_addr text NOT NULL,
  pid integer NOT NULL,
  usename name NOT NULL,
  CONSTRAINT mystat_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
postgresql postgresql-9.2

1 个回答

  1. Best Answer

    我设法通过将我的查询更改为这个来解决我的问题：

    INSERT INTO my_stat(id, client_addr, pid, usename)
    SELECT nextval('mystat_sequence'), client_addr, pid, usename
    FROM (
        SELECT
           host(client_addr) as client_addr,
           pid ,
           usename
        FROM pg_stat_activity
        WHERE usename = current_user
    ) s
    WHERE
        NOT EXISTS (
           SELECT NULL
           FROM my_stat u
           WHERE current_date = u.creation
               AND s.pid = u.pid
               AND s.client_addr = u.client_addr
               AND s.usename = u.usename
        );

    我认为 Postgresql 内部发生了一些事情，但我不知道是什么......

    • 0

相关问题