Mad SELECT with the admin view.
ggundy
Admin page for a table I have called "staged_order."
Here is the class:
*******************************
class StagedOrder(models.Model):
def __str__(self):
return 'FIXME'
staging_area = models.ForeignKey(StagingArea)
placed_order = models.ForeignKey(PlacedOrder)
in_time = models.DateTimeField()
done_time = models.DateTimeField()
out_time = models.DateTimeField()
changed = models.DateTimeField(auto_now_add=True, editable=False)
created = models.DateTimeField(auto_now=True, editable=False)
class Meta:
db_table = 'staged_order'
class Admin:
list_display = ('staging_area', 'placed_order', 'in_time',
'done_time', 'out_time', 'changed', 'created')
list_filter = ('in_time', 'done_time', 'out_time', 'changed',
'created')
search_fields = ('staging_area', 'placed_order')
fields = (
('Core information', {'fields': ('staging_area',
'placed_order', 'in_time', 'done_time', 'out_time')}),
('Record dates', {'fields': ('changed', 'created')}),
)
*******************************
Here is the error:
*******************************
ProgrammingError at /admin/core/stagedorder/
ERROR: target lists can have at most 1664 entries SELECT
Request Method: GET
Request URL: http://localhost:8000/admin/core/stagedorder/
Exception Type: ProgrammingError
Exception Value: ERROR: target lists can have at most 1664 entries
SELECT
<SNIP>
Big, HUGE, ugly SELECT statement.
</SNIP>
Exception Location:
/usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/backends/util.py
in execute, line 12
*******************************
If I remove 'staging_area' and 'placed_order' from the custom
"list_display" from the Admin class, I don't get the error.
There must be some kind of spanning or looping going on as the SELECT
is full of this type of thing: "AND "country176"."world_region_id" =
"world_region177"."id" AND "shipper169"."message_type_id" =
"message_type178"."id" AND."
I have a country, message_type, etc tables but they are a few
foreign-keys away and shouldn't be involved in this Admin page.
Any ideas what might be causing this? I've DROP DATABASE and CREATE
DATABASE and did a fresh sync all with no luck.
BTW... this in on Django version 0.96-pre
Thanks all.
Gabriel Gunderson
> There must be some kind of spanning or looping going on as the SELECT
> is full of this type of thing: "AND "country176"."world_region_id" =
> "world_region177"."id" AND "shipper169"."message_type_id" =
> "message_type178"."id" AND."
I thought I would update this with a paste-bin...
http://paste.e-scribe.com/1978/
Pretty ugly stuff. Anyone seen this before or know where I can start looking?
Thanks again,
Gabe
Karen Tracey
sucker for puzzles so tried to make some sense of the select. My
feeling is the problem isn't in the StagedOrder class that you posted
but rather one of the ones getting pulled in as a result of the
StagingArea ForeignKey.
The list of tables involved starts like this:
staging_area ->
location -> (2) region -> country -> world_region ->
region6 -> country7 -> world_region8 ->
order ->
customer ->
title ->
gender -> region13 -> country14 -> world_region15 ->
region16 -> country17 -> world_region18 ->
message_type -> etc. etc. etc.
So staging_area pulls in location and then location seems to have two
fields that could be region ForeignKeys. Region apparently points to a
country and country then to world_region. OK so far. Then (to handle
2nd region ForeignKey found in location?) the sixth table is added to
the select: region6, followed by country7 and world_region8. Shouldn't
these have been recognized as tables already in the select? Things
continue with order, customer, title, gender, oops here's another
region, and we're up to the 13th table in the select. So a bunch more
tables are added that are really already named as part of the select,
only this time they're given a number suffix that matches what table
number they are in the select statement. Eventually we get up to table
number 82, a customer table, which gives a loop. I guess it kept adding
"new" tables until it hit the limit mentioned in the exception.
Like I said I have no idea what's going on -- I'm just a casual user
and am on a pretty recent code base -- but maybe these observations
will trigger someone who knows a bit more to say "oh yeah, I know what
that problem is". Maybe search the list/problem database for issues
with multiple ForeignKey fields in a model pointing to the same table?
I have a vague feeling I might have seen some messages about stuff like
that fly by but I could easily be wrong about that.
Karen
Gabriel Gunderson
> Yah, that does look bizarre. I have no idea what is going on but am a
> sucker for puzzles so tried to make some sense of the select. My
> feeling is the problem isn't in the StagedOrder class that you posted
> but rather one of the ones getting pulled in as a result of the
> StagingArea ForeignKey.
I think you're on the right track. It's basically a matter of what
tables are used in foreign keys. A StagedOrder points to an
PlacedOrder that points to two Customers that each point to two
Regions that each point to a Country. If I prune that by limiting a
PlacedOrder to only have one FK pointing to a single Customer, then I
don't get the error. But what I *do* see is postmaster (PostgreSQL)
spike the CPU (100%) for about 3 minutes (or until I kill it) and then
it returns the admin page. That's not going to fly in production or
even pre-production :) Whatever the specific cause, shouldn't any
legitimate (and validating) model work with the relatively simple
SELECT that the Admins interface should be putting together? I mean,
if this can be fixed by changing my model, is that really a fix?
Also, I have to wonder, is this specific to to the Admin interface or
will I see it when working with the DB API?
> The list of tables involved starts like this:
>
> staging_area ->
> location -> (2) region -> country -> world_region ->
> region6 -> country7 -> world_region8 ->
> order ->
> customer ->
> title ->
> gender -> region13 -> country14 -> world_region15 ->
> region16 -> country17 -> world_region18 ->
> message_type -> etc. etc. etc.
>
> So staging_area pulls in location and then location seems to have two
> fields that could be region ForeignKeys. Region apparently points to a
> country and country then to world_region. OK so far. Then (to handle
> 2nd region ForeignKey found in location?) the sixth table is added to
> the select: region6, followed by country7 and world_region8. Shouldn't
> these have been recognized as tables already in the select? Things
> continue with order, customer, title, gender, oops here's another
> region, and we're up to the 13th table in the select. So a bunch more
> tables are added that are really already named as part of the select,
> only this time they're given a number suffix that matches what table
> number they are in the select statement. Eventually we get up to table
> number 82, a customer table, which gives a loop. I guess it kept adding
> "new" tables until it hit the limit mentioned in the exception.
But, why would it even go that far in the SELECT? This is for the
Admins interface of the StagedOrder. It only needs to display info
about two FKs, StagingArea and PlacedOrder and they both should be
easy to represent as they each have "def __str__(self): return
'FIXME'." I did that to simplify things by eliminating that part of
the problem. BTW, this only happens when you "change" *not* "add" via
the Admins interface.
> Like I said I have no idea what's going on -- I'm just a casual user
> and am on a pretty recent code base -- but maybe these observations
> will trigger someone who knows a bit more to say "oh yeah, I know what
> that problem is". Maybe search the list/problem database for issues
> with multiple ForeignKey fields in a model pointing to the same table?
> I have a vague feeling I might have seen some messages about stuff like
> that fly by but I could easily be wrong about that.
I'll have to do some more searching with different key words. Any
errors I found were PG specific, since that is the error that is
getting reported. In other words, Django thinks everything is just
peachy until PG says back to it. "Hey, you can't issue a SELECT
statement with 1500 entries!" I guess one *could* say that the
defects is in PostgreSQL :)
Thanks for your time and insight.
> Karen
--
Gabriel Gunderson
http://gundy.org