Code

Opened 6 years ago

Closed 5 years ago

Last modified 16 months 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

Description

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.

Attachments (1)

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

Download all attachments as: .zip

Change History (8)

Changed 6 years ago by bugs@…

patch for postgresql operations.py

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 5 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 ./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.

comment:3 Changed 5 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 5 years ago by RaceCondition

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

comment:5 Changed 5 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 16 months ago by anonymous

Try following

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.