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

[bugs@…]

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/operations.py should be updated to reflect this change.

[bugs@…]

patch for postgresql operations.py

[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).

[marcdm]

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 ./manage.py 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 :

auth_message_id_seq

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

auth_message_id_seq1

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
Indexes:
    "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.

[Erik Allik]

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?

[Alex Gaynor]

Dupe of #1946

[anonymous]

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

[anonymous]

Try following

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