PostgreSQL，顺序问题 [英] PostgreSQL, problems with sequence

问题描述

大家好！

我有以下问题.我已经在Postgres中创建了一个表格.

Hi all!

I have the following problem. I''ve created a table in Postgres.

CREATE SEQUENCE test_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE test_id_seq OWNER TO admin;

CREATE TABLE test
(
  id bigserial NOT NULL,
  data character varying(128) NOT NULL,
  CONSTRAINT pkey_id_test PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test OWNER TO admin;

CREATE OR REPLACE RULE replace_test AS
    ON INSERT TO test
   WHERE (EXISTS ( SELECT 1
           FROM test
          WHERE test.id = new.id)) DO INSTEAD  UPDATE test SET data = new.data
  WHERE test.id = new.id;

ALTER TABLE test
  ADD CONSTRAINT pkey_id_test PRIMARY KEY(id);

ALTER TABLE test ADD COLUMN id bigint;
ALTER TABLE test ALTER COLUMN id SET STORAGE PLAIN;
ALTER TABLE test ALTER COLUMN id SET NOT NULL;
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq'::regclass);
ALTER TABLE test ADD COLUMN data character varying(128);
ALTER TABLE test ALTER COLUMN data SET STORAGE EXTENDED;
ALTER TABLE test ALTER COLUMN data SET NOT NULL;

执行以下查询:

Performing the following query:

INSERT INTO "test" ("data") VALUES ('1')

我已经在表中插入了一些测试数据.

现在:

I''ve inserted some test data into the table.

Now:

SELECT "id" FROM "test" ORDER BY "id"

它给了我:

It gives me:

2
5
10
17
26
37
50

ID不会增加1.问题出在哪里?我错过了什么吗?

Ids aren''t incremented by 1. Where is the problem? Did I miss something?
Thanks!

推荐答案

看一下值(2、5、10等)，看来您正在运行增量"过程 2n 次，其中 n 是测试表中已经存在的记录数.您的ID之间的连续差异为3、5、7、9、11、13，在我看来，这非常像 2 * n + 1 模式.不要问我如何或为什么；我刚刚发现什么".我希望这可以为您或其他人提供从哪里开始挖掘的线索.祝你好运！
彼得

Looking at the values (2, 5, 10, etc), it seems that you have something that is running the ''increment'' process 2n times, where n is the number of records already in the test table. The successive differences between your id''s are 3, 5, 7, 9, 11, 13, which looks to me very much like a 2*n + 1 pattern. Don''t ask me how or why; I''ve just spotted "what". I hope this gives you or someone else a clue as to where to start digging. Good luck!
Peter

抱歉，伙计们.只是提出问题.我真的需要帮助我们正在从MySQL迁移到PostgreSQL

Sorry for bothering, guys. Just bringing up the question. I really need help. We are moving from MySQL to PostgreSQL

这篇关于PostgreSQL，顺序问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！