[Django] #9302: postgresql: currval of sequence is not yet defined in this session: fix

[Django]

#9302: postgresql: currval of sequence is not yet defined in this session: fix
------------------------------------------+---------------------------------
Reporter: bu...@erentil.net | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: 1.0
Keywords: | Stage: Unreviewed
Has_patch: 1 |
------------------------------------------+---------------------------------
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.

--
Ticket URL: <http://code.djangoproject.com/ticket/9302>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

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

---------------------------------------------------+------------------------

Reporter: bu...@erentil.net | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: 1.0

Resolution: | Keywords:
Stage: Unreviewed | Has_patch: 1
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Changes (by mtredinnick):

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

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

--
Ticket URL: <http://code.djangoproject.com/ticket/9302#comment:1>

[Django]

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

---------------------------------------------------+------------------------
Reporter: bu...@erentil.net | Owner: nobody
Status: closed | Milestone:

Component: Database layer (models, ORM) | Version: 1.0

Resolution: invalid | Keywords:
Stage: Unreviewed | Has_patch: 1

Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------

Changes (by marcdm):

* status: new => closed
* resolution: => invalid

Comment:

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.

--
Ticket URL: <http://code.djangoproject.com/ticket/9302#comment:2>

[Django]

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

---------------------------------------------------+------------------------
Reporter: bu...@erentil.net | Owner: nobody
Status: reopened | Milestone:

Component: Database layer (models, ORM) | Version: 1.0

Resolution: | Keywords:
Stage: Unreviewed | Has_patch: 1

Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------

Changes (by RaceCondition):

* status: closed => reopened
* resolution: invalid =>

Comment:

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?

--
Ticket URL: <http://code.djangoproject.com/ticket/9302#comment:3>

[Django]

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

---------------------------------------------------+------------------------
Reporter: bu...@erentil.net | Owner: nobody
Status: reopened | Milestone:
Component: Database layer (models, ORM) | Version: SVN
Resolution: | Keywords:
Stage: Unreviewed | Has_patch: 1

Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Changes (by RaceCondition):

* cc: eal...@gmail.com (added)
* version: 1.0 => SVN

--
Ticket URL: <http://code.djangoproject.com/ticket/9302#comment:4>

[Django]

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

---------------------------------------------------+------------------------
Reporter: bu...@erentil.net | Owner: nobody
Status: closed | Milestone:
Component: Database layer (models, ORM) | Version: SVN
Resolution: duplicate | Keywords:
Stage: Unreviewed | Has_patch: 1

Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------

Changes (by Alex):

* status: reopened => closed
* resolution: => duplicate

Comment:

Dupe of #1946

--
Ticket URL: <http://code.djangoproject.com/ticket/9302#comment:5>