Opened 12 years ago

Closed 11 years ago

Last modified 8 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@… 12 years ago.
patch for postgresql

Download all attachments as: .zip

Change History (8)

Changed 12 years ago by bugs@…

Attachment: patch added

patch for postgresql

comment:1 Changed 12 years ago by Malcolm Tredinnick

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 12 years ago by marcdm

Resolution: invalid
Status: newclosed

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 11 years ago by Erik Allik

Resolution: invalid
Status: closedreopened

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 11 years ago by Erik Allik

Cc: eallik@… added
Version: 1.0SVN

comment:5 Changed 11 years ago by Alex Gaynor

Resolution: duplicate
Status: reopenedclosed

Dupe of #1946

comment:6 Changed 9 years ago by anonymous

Easy pickings: unset
Severity: Normal
Type: 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 8 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