Postgresql转储是否创建以 - 或之后 - 最后一个键开始的序列？ [英] Does a Postgresql dump create sequences that start with - or after - the last key?

问题描述

我最近在Django项目后创建了一个数据库的SQL转储，并且在清理SQL之后，有一点可以还原DB和所有的数据。问题是序列都被弄脏了。我尝试添加一个新用户并生成Python错误 IntegrityError：重复键违反唯一约束。

自然，我想我的SQL转储没有重新启动序列。但是它确实如此：

  DROP SEQUENCEauth_user_id_seqCASCADE; 
 CREATE SEQUENCEauth_user_id_seqINCREMENT 1 START 446 MAXVALUE 9223372036854775807 MINVALUE 1 CACHE 1; 
 ALTER TABLEauth_user_id_seqOWNER TOdb_user; 
  

我发现重复尝试创建用户（或任何具有现有的表的新行数据和这样的序列）允许成功的对象/行创建。这解决了紧迫的问题。

但是考虑到该表中的最后一个用户ID为446 - 在上面的序列创建中是相同的起始值 - 看起来Postgresql只是试图开始创建行那个密钥。

SQL转储是否提供错误的开始键1？还是应该在给定的开始ID之后调用其他命令来启动序列？敏锐的好奇。

解决方案

转储很好，没问题。如果您的代码（或列的默认值）使用nextval（）从序列中获取下一个值，那么一切都将很好。检查你的代码，看看它是做什么，执行什么SQL。有了这些信息，你可以看到为什么会出现问题。

祝你好运！

I recently created a SQL dump of a database behind a Django project, and after cleaning the SQL up a little bit was able to restore the DB and all of the data. The problem was the sequences were all mucked up. I tried adding a new user and generated the Python error IntegrityError: duplicate key violates unique constraint.

Naturally I figured my SQL dump didn't restart the sequence. But it did:

DROP SEQUENCE "auth_user_id_seq" CASCADE;
CREATE SEQUENCE "auth_user_id_seq" INCREMENT 1 START 446 MAXVALUE 9223372036854775807 MINVALUE 1 CACHE 1;
ALTER TABLE "auth_user_id_seq" OWNER TO "db_user";

I figured out that a repeated attempt at creating a user (or any new row in any table with existing data and such a sequence) allowed for successful object/row creation. That solved the pressing problem.

But given that the last user ID in that table was 446 - the same start value in the sequence creation above - it looks like Postgresql was simply trying to start creating rows with that key.

Does the SQL dump provide the wrong start key by 1? Or should I invoke some other command to start sequences after the given start ID? Keenly curious.

解决方案

The dump is fine, no problem. If your code (or default value for the column) uses nextval() to get the next value from the sequence, everything will be fine as well. Check your code and see what it does, what SQL is executed. With this information you can see why things are going wrong.

Good luck!

这篇关于Postgresql转储是否创建以 - 或之后 - 最后一个键开始的序列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！