Re: [Django] #18082: get_indexes produces wrong resuls under oracle

[Django]

#18082: get_indexes produces wrong resuls under oracle
-+-
 Reporter:  akaariai |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  1.4
  (models, ORM)  |   Resolution:  fixed
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  oracle   |  checkin
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by akaariai):

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


Comment:

 Fixed in a18e43c5bb8cb7c82c84ca1b42d847a62ac9f077

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #18082: get_indexes produces wrong resuls under oracle

[Django]

#18082: get_indexes produces wrong resuls under oracle
-+-
 Reporter:  akaariai |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.4
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  oracle   |  checkin
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by akaariai):

 * stage:  Accepted => Ready for checkin


Comment:

 I have created pull request https://github.com/django/django/pull/23 which
 contains ready-for-checkin patch for this. I will commit this in a day or
 two unless something is found (or somebody does that before me).

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #18082: get_indexes produces wrong resuls under oracle

[Django]

#18082: get_indexes produces wrong resuls under oracle
-+-
 Reporter:  akaariai |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.4
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  oracle   |  Needs documentation:  0
Has patch:  0|  Patch needs improvement:  0
  Needs tests:  0|UI/UX:  0
Easy pickings:  0|
-+-
Changes (by akaariai):

 * cc: anssi.kaariainen@… (added)


Comment:

 I checked and PostgreSQL skips all multi-column indexes. So, I guess
 another join on user_ind_columns checking column_position = 2 does not
 exists would work. It would hopefully remove also the need for the ugly
 GROUP BY + MAX hack.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #18082: get_indexes produces wrong resuls under oracle

[Django]

#18082: get_indexes produces wrong resuls under oracle
-+-
 Reporter:  akaariai |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.4
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  oracle   |  Needs documentation:  0
Has patch:  0|  Patch needs improvement:  0
  Needs tests:  0|UI/UX:  0
Easy pickings:  0|
-+-
Changes (by ikelly):

 * stage:  Unreviewed => Accepted


Comment:

 The only comment I have is that it {{{column_position = 1}}} is not
 sufficient to determine whether the index is multi-column or not.  We
 would also need to check that there are no rows for the same index with
 {{{column_position > 1}}}.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #18082: get_indexes produces wrong resuls under oracle

[Django]

#18082: get_indexes produces wrong resuls under oracle
-+-
 Reporter:  akaariai |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.4
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:
 Keywords:  oracle   |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by akaariai):

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


Comment:

 Part of the problem comes from having multiple indexes on the same column
 (for example unique on firstname, lastname + a normal index on firstname).
 In that case it was ordering dependent if the column was seen as unique or
 not.

 The attached patch simplifies the SQL, removes non-indexed columns from
 the output and in addition handles the multiple indexes for the same
 column by using GROUP BY + MAX.

 I was wrong about the get_indexes calls taking a lot of time in the test
 suite. The first call takes around a second, but after that the query plan
 is cached and subsequent calls to get_indexes will be fast enough to not
 be a bottleneck.

 The introspection + inspectdb tests pass on Oracle 11g.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

[Django] #18082: get_indexes produces wrong resuls under oracle

[Django]

#18082: get_indexes produces wrong resuls under oracle
--+
 Reporter:  akaariai  |  Owner:  nobody
 Type:  Bug   | Status:  new
Component:  Database layer (models, ORM)  |Version:  1.4
 Severity:  Normal|   Keywords:  oracle
 Triage Stage:  Unreviewed|  Has patch:  0
Easy pickings:  0 |  UI/UX:  0
--+
 Currently oracle/introspection.py get_indexes uses this SQL:
 {{{
 SELECT LOWER(all_tab_cols.column_name) AS column_name,
CASE user_constraints.constraint_type
WHEN 'P' THEN 1 ELSE 0
END AS is_primary_key,
CASE user_indexes.uniqueness
WHEN 'UNIQUE' THEN 1 ELSE 0
END AS is_unique
 FROM   all_tab_cols, user_cons_columns, user_constraints,
 user_ind_columns, user_indexes
 WHERE  all_tab_cols.column_name = user_cons_columns.column_name (+)
   AND  all_tab_cols.table_name = user_cons_columns.table_name (+)
   AND  user_cons_columns.constraint_name =
 user_constraints.constraint_name (+)
   AND  user_constraints.constraint_type (+) = 'P'
   AND  user_ind_columns.column_name (+) = all_tab_cols.column_name
   AND  user_ind_columns.table_name (+) = all_tab_cols.table_name
   AND  user_indexes.uniqueness (+) = 'UNIQUE'
   AND  user_indexes.index_name (+) = user_ind_columns.index_name
   AND  all_tab_cols.table_name = UPPER(%s)
 }}}

 This SQL has two failings: first, it LEFT JOINs instead of INNER JOINs the
 user_ind_columns, so every column will show as indexed. In addition the
 results are ordering dependant, for example if we check AUTH_USER by that
 SQL, the result is:
 {{{
 id  1   1
 id  0   1
 username0   1
 is_staff0   0
 is_staff0   0
 last_name   0   0
 is_superuser0   0
 is_superuser0   0
 last_login  0   0
 date_joined 0   0
 first_name  0   0
 email   0   0
 is_active   0   0
 is_active   0   0
 password0   0
 }}}
 So, if ID is a primary key is purely luck-based.

 In addition the SQL is really slow, it takes around 0.9 seconds for single
 table on my laptop. As the test suite has nearly 1000 tables, inspectdb
 tests will take half an hour just executing this SQL.

 I think one could get the same results by using the three following
 queries:
 {{{
 select * from user_ind_columns where table_name = 'AUTH_GROUP';
 select * from user_constraints where constraint_type = 'P' and table_name
 = 'AUTH_GROUP';
 select * from user_indexes where uniqueness = 'UNIQUE' and table_name =
 'AUTH_GROUP';
 }}}
 and then doing the joins in Python. It seems this approach would also be
 at least an order of magnitude faster.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.