Opened 6 years ago

Closed 6 years ago

Last modified 2 years ago

#9302 closed Uncategorized (duplicate)

postgresql: currval of sequence is not yet defined in this session: fix

Reported by: bugs@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: eallik@… Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


In postgresql 8.x, SELECT CURRVAL must be called after the sequence is initialized by SELECT NEXTVAL. This breaks many things, like syncdb, on clean postgres database.

django/db/backends/postgresql/ should be updated to reflect this change.

Attachments (1)

patch (182 bytes) - added by bugs@… 6 years ago.
patch for postgresql

Download all attachments as: .zip

Change History (8)

Changed 6 years ago by bugs@…

patch for postgresql

comment:1 Changed 6 years ago by mtredinnick

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

How does this problem get triggered in the normal course of operations. A lot of us are using various postgreSQL 8.x versions for lots of situations (including regularly running the tests) and aren't seeing this problem, so it would be nice to understand how it is triggered.

Also, in future, please create a patch from the top of the source tree so that it's clear which file is being patched (without having to read the ticket report to see if you've mentioned it there. The patch should be self-contained).

comment:2 Changed 6 years ago by marcdm

  • Resolution set to invalid
  • Status changed from new to closed

This patch is actually invalid. The error happens when you try to select the value for the auto-incrementing id of the last item inserted. The probem usually arises when you try schema evolution by dropping some tables, and you don't always use DROP TABLE .... CASCADE;.

When creating a table, Django does something like (taken from ./ sql auth):

CREATE TABLE "auth_message" (
    "id" serial NOT NULL PRIMARY KEY,
    "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
    "message" text NOT NULL

By declaring that the "id" column is serial, PostgreSQL will implicitly create a sequence object named :


However, if you go into the database terminal, and issue a command to

DROP TABLE auth_message;

without using CASCADE, and without isssuing a DROP SEQUENCE command, then, when you recreate the table with the sql from before, Postgres will now create a sequence object named


And this is the root of the problem that this "bug" and patch addresses. The problem with this patch is that, it allows you to continue to get the last_value from the wrong sequence object. This means that, for one, foreign key fields in the django admin site, return wrong values when you try to create a foreign key object using the popup.

The real solution is, if you get this error from PostgreSQL, verify the sequence being used for a field by looking at the table structure using

 \d [tablename]

For example (simulated to illustrate a point. don't try to delete the auth_message table to re-enact this) :

\d auth_message 

returns :

                          Table "public.auth_message"
 Column  |  Type   |                         Modifiers                         
 id      | integer | not null default nextval('auth_message_id_seq1'::regclass)
 user_id | integer | not null
 message | text    | not null
    "auth_message_pkey" PRIMARY KEY, btree (id)
    "auth_message_user_id" btree (user_id)
Foreign-key constraints:
    "user_id_refs_id_650f49a6" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED

The id column gets its id from auth_message_id_seq1 But django will be trying to read the last id from auth_message_id_seq which now stands stagnant because no table uses it, and will not get nextval called on it when you add a row.

You can use the following commands to your discretion in solving it :

DROP SEQUENCE auth_message_id_seq;
ALTER SEQUENCE auth_message_id_seq1 RENAME TO auth_message_id_seq;
ALTER TABLE auth_message ALTER "id" SET DEFAULT nextval('auth_message_id_seq'::regclass);

And problem solved.

comment:3 Changed 6 years ago by RaceCondition

  • Resolution invalid deleted
  • Status changed from closed to reopened

I ran into the exact same problem. So Django is assuming the ID sequence of a model's table is appname_model_id_seq, but should Django actually make this assumption? What if I'm using a legacy schema? What if I just ran RENAME TABLE and didn't rename the ID sequence? Are these scenarios not legitimate?

Shouldn't Django just introspect the DB schema to know exactly which ID sequence to use for a model?

comment:4 Changed 6 years ago by RaceCondition

  • Cc eallik@… added
  • Version changed from 1.0 to SVN

comment:5 Changed 6 years ago by Alex

  • Resolution set to duplicate
  • Status changed from reopened to closed

Dupe of #1946

comment:6 Changed 3 years ago by anonymous

  • Easy pickings unset
  • Severity set to Normal
  • Type set to Uncategorized
  • UI/UX unset

Try to run select nextval('auth_message_id_seq'::text);
I am using PHP and running a script with that command works.

comment:7 Changed 2 years ago by anonymous

Try following

SELECT setval('"auth_message_id_seq"', coalesce(max("id"), 1), max("id") IS NOT null) FROM "auth_message";

Note: See TracTickets for help on using tickets.
Back to Top