python - postgres 插入规则而不是更新

Question

我有一个包含三列的简单表格；一个 bigint，一个 bool 值和一个文本。像这样:

CREATE SEQUENCE foo_seq;
CREATE TABLE foo (
    i_foo     BIGINT DEFAULT nextval('foo_seq'),
    isok      BOOLEAN DEFAULT TRUE,
    who       CHARACTER VARYING(32) NOT NULL
);
ALTER TABLE foo ADD CONSTRAINT foo_pkey PRIMARY KEY (i_foo);
CREATE RULE foo_insert AS ON INSERT TO foo DO (
    DELETE FROM foo
        WHERE ((foo.i_foo < NEW.i_foo) AND (NEW.who = foo.who))
);

快更新了。我希望在任何插入之后，表格总是只有一行，只是 i_foo 增加。

当我手动插入新项目时，它工作正常，它删除最后一个项目并插入新项目。换句话说，就是 i_foo 增加 1。

但是相同的命令在 python 脚本中的作用不同。所有实体，包括新实体，都将被删除。插入后表格将为空。

INSERT INTO foo (isok, who) VALUES (True, 'someone');

class adoerclass(threading.Thread):

    def __init__(self, db):
        self.db = db
        threading.Thread.__init__(self)
        self.setDaemon(True)
        self.start()

    def run(self):
        while True:
            isGood = True
            try:
                lock.acquire()
                self.db.execute("INSERT INTO foo (isok, who) "
                "VALUES (%s, %s)", [isGood, 'someone'])
                self.db.commit()
                lock.release()
            except:
                lock.release()
                print("exception!")
                time.sleep(RETRY_TIME)
                lock.release()

我很困惑。

Answer 1

最后我为此写了一个触发器。效果不错

CREATE SEQUENCE foo_seq;
CREATE TABLE foo (
    i_foo     BIGINT DEFAULT nextval('foo_seq'),
    isok      BOOLEAN DEFAULT TRUE,
    who       CHARACTER VARYING(32) NOT NULL
);
ALTER TABLE foo ADD CONSTRAINT foo_pkey PRIMARY KEY (i_foo);
CREATE FUNCTION foo_insert_before() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    DELETE FROM foo WHERE (( NEW.i_foo > foo.i_foo ) AND (NEW.who = foo.who));
    RETURN NEW;
END;
$$;

CREATE TRIGGER foo_insert BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE foo_insert_before();