Postgres序列混乱/覆盖项目的问题

huangapple go评论64阅读模式
英文:

Postgres sequence getting messed up / overwriting items

问题

表格:

CREATE SEQUENCE vars_id_seq;
CREATE TABLE vars (
    id INT PRIMARY KEY NOT NULL DEFAULT nextval('vars_id_seq'),
    name VARCHAR(30) NOT NULL,
    value VARCHAR(30)
);
ALTER SEQUENCE vars_id_seq OWNED BY vars.id;

我插入了一些行,一切都很好。然而,今天在插入行时,我发现它做了以下两件事情(并且在不同的时间发生):

  • 当我运行INSERT命令时(没有指定ID,即应该创建一个新项),它开始覆盖现有的项。

  • 它报错说有重复的键,因为它尝试从7开始恢复序列,但是有一个ID为7的项和ID大于7的项。

所以我的问题是,从概念上讲,我的序列的当前值如何低于表中最高的ID?当然,我随着时间的推移从中删除了一些行,但我本来希望序列保持同步。

(我知道在PG中使用SERIAL / BIGSERIAL列类型可以实现自动递增,但由于某种原因,在我的数据库中没有显示为可用的类型。顺便说一句,我来自MySQL背景。)

谢谢。

====== 编辑

这是我的插入查询的样子(通过Node):

	INSERT INTO ${table}s (${Object.keys(params).map(col => `"${col}"`).join()})
	VALUES (${Array(Object.keys(params).length).fill().map((val, i) => '$'+(i+1)).join()})
	ON CONFLICT (id)
	DO UPDATE SET ${Object.entries(params).map((pair, i) => `"${pair[0]}" = $${i+1}`).join()}
	RETURNING id
英文:

Table:

CREATE SEQUENCE vars_id_seq;
CREATE TABLE vars (
    id INT PRIMARY KEY NOT NULL DEFAULT nextval('vars_id_seq'),
    name VARCHAR(30) NOT NULL,
    value VARCHAR(30)
);
ALTER SEQUENCE vars_id_seq OWNED BY vars.id;

I inserted some rows, and all was well. Today, however, when inserting rows, I found that it did one of two things (and both happend at different times):

  • It started overwriting existing items when I ran INSERT commands (without specifying an ID, i.e. it should have created a new item)

  • It error'd that there was a duplicate key, because it was trying to resume the sequence from 7, for some reason, even though there was an item with ID 7, and items with IDs above 7.

So my question is, conceptually, how could it be that my sequence's current value is lower than the highest ID in my table? Sure, I've deleted rows from it over time, but I would have expected the sequence to keep in sync.

(I know a later way of doing auto-inc in PG is via SERIAL / BIGSERIAL column types, but for some reason that's not showing as an available type in my DB. I'm from a MySQL background, incidentally.)

Thank you.

====== EDIT

This is what my insert query looks like (via Node):

	INSERT INTO ${table}s (${Object.keys(params).map(col => `"${col}"`).join()})
	VALUES (${Array(Object.keys(params).length).fill().map((val, i) => '$'+(i+1)).join()})
	ON CONFLICT (id)
	DO UPDATE SET ${Object.entries(params).map((pair, i) => `"${pair[0]}" = $${i+1}`).join()}
	RETURNING id

答案1

得分: 2

让数据库为您处理所有这些事情,并通过使用生成的列使手动处理id变得“不可能”:

创建表vars (
    id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(30) NOT NULL,
    value VARCHAR(30)
);
英文:

Let the database handle all this stuff for you and make it "impossible" to manually handle the id's by using a generated column:

CREATE TABLE vars (
    id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(30) NOT NULL,
    value VARCHAR(30)
);

答案2

得分: 1

所以我的问题是,从概念上讲,怎么可能我的序列的当前值比我的表中最高的ID还要低?当然,我随着时间的推移删除了一些行,但我本来希望序列能够保持同步。

当你删除行时,序列不会自动更新。如果你在带有序列的表中删除了一行,那些序列号就会消失。如果你插入数据时指定了自己的序列号,那么可能会导致不同步。

英文:

> So my question is, conceptually, how could it be that my sequence's current value is lower than the highest ID in my table? Sure, I've deleted rows from it over time, but I would have expected the sequence to keep in sync.

Sequences aren't going to magically update when you delete rows. If you delete a row in the table with a sequence those sequence numbers are gone. If you've inserted data specifying your own sequence number then you could get out of sync.

huangapple
  • 本文由 发表于 2023年8月9日 00:12:22
  • 转载请务必保留本文链接:https://go.coder-hub.com/76861397.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定