[Django] #25476: bug in array handling hit in get_constraints() for psycopg2?
Django
-------------------------------+-------------------------------
Reporter: heasus | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
Severity: Normal | Keywords: psycopg2 fetchall
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+-------------------------------
Using inspectdb of Django1.8.4 with psycopg2 2.6.1 to import an existing
db, get_constraints() attempts to read table constraints. It exits here
parsing foreign keys with the traceback:
File "/home/www/db/lib/python3.4/site-
packages/django/db/backends/postgresql_psycopg2/introspection.py", line
171, in get_constraints
"foreign_key": tuple(used_cols[0].split(".", 1)) if kind.lower() ==
"foreign key" else None,
IndexError: list index out of range
The array of columns returned from the db is populated:
SELECT
kc.constraint_name,
kc.column_name,
c.constraint_type,
array(SELECT table_name::text || '.' || column_name::text
FROM information_schema.constraint_column_usage
WHERE constraint_name = kc.constraint_name)
FROM information_schema.key_column_usage AS kc
JOIN information_schema.table_constraints AS c ON
kc.table_schema = c.table_schema AND
kc.table_name = c.table_name AND
kc.constraint_name = c.constraint_name
WHERE
kc.table_schema = 'public' AND
kc.table_name = 'things'
ORDER BY kc.ordinal_position ASC;
constraint_name | column_name | constraint_type | array
------------------------+-------------+-----------------+-----------------------
things_pkey | trunk | PRIMARY KEY | {things.trunk,things.c
hild}
things_child_fkey | child | FOREIGN KEY | {children.idx}
things_trunk_fkey | trunk | FOREIGN KEY | {children.idx}
things_pkey | child | PRIMARY KEY | {things.trunk,things.c
hild}
but empty from cursor.fetchall(): for constraint, column, kind, used_cols
in cursor.fetchall(). A script using psycopg2 directly does not exhibit
this problem. I;ve also tried a current development checkout of django.
This is as far as I've traced this so far and it does not seem to be a
known issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: heasus | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: psycopg2 fetchall | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by heasus):
* needs_better_patch: => 0
* component: Uncategorized => Database layer (models, ORM)
* needs_tests: => 0
* needs_docs: => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:1>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by heasus):
* type: Uncategorized => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:2>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Description changed by claudep:
Old description:
New description:
ORDER BY kc.ordinal_position ASC;
}}}
{{{
constraint_name | column_name | constraint_type | array
------------------------+-------------+-----------------+-----------------------
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:3>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by claudep):
Would it be possible to have the schema of the involved tables?
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:4>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: psycopg2 fetchall | Triage Stage:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by claudep):
* status: new => closed
* resolution: => needsinfo
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:5>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by heasus):
Was this fixed? or did it go directly from 'need more info' directly to
closed? I can provide more information, but have been traveling.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:6>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
"needsinfo" is a "closed" state -- please reopen when you provide the
additional info. Thanks.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:7>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by heasus):
* status: closed => new
* resolution: needsinfo =>
Comment:
This schema reproduces the problem for me:
{{{CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE children (
idx bigint NOT NULL,
fullname character varying(64) NOT NULL
);
CREATE SEQUENCE children_idx_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE ONLY children ALTER COLUMN idx SET DEFAULT
nextval('children_idx_seq'::regclass);
ALTER TABLE ONLY children
ADD CONSTRAINT children_pkey PRIMARY KEY (idx);
CREATE TABLE things (
sibling bigint NOT NULL,
child bigint NOT NULL
);
ALTER TABLE ONLY things
ADD CONSTRAINT things_child_fkey FOREIGN KEY (child) REFERENCES
children(idx) ON DELETE CASCADE;
ALTER TABLE ONLY things
ADD CONSTRAINT things_sibling_fkey FOREIGN KEY (sibling) REFERENCES
children(idx) ON DELETE CASCADE;
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
# * Rearrange models' order
# * Make sure each model has one field with primary_key=True
# * Remove `managed = False` lines if you wish to allow Django to
create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or
field names.
#
# Also note: You'll have to insert the output of 'django-admin sqlcustom
[app_label]'
# into your database.
from __future__ import unicode_literals
from django.db import models
class Children(models.Model):
idx = models.BigIntegerField(primary_key=True)
fullname = models.CharField(max_length=64)
class Meta:
managed = False
db_table = 'children'
class Things(models.Model):
Traceback (most recent call last):
File "./manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "/home/www/beerdb_debug/lib/python3.4/site-
packages/django/core/management/__init__.py", line 338, in
execute_from_command_line
utility.execute()
File "/home/www/beerdb_debug/lib/python3.4/site-
packages/django/core/management/__init__.py", line 330, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/home/www/beerdb_debug/lib/python3.4/site-
packages/django/core/management/base.py", line 393, in run_from_argv
self.execute(*args, **cmd_options)
File "/home/www/beerdb_debug/lib/python3.4/site-
packages/django/core/management/base.py", line 444, in execute
output = self.handle(*args, **options)
File "/home/www/beerdb_debug/lib/python3.4/site-
packages/django/core/management/commands/inspectdb.py", line 25, in handle
for line in self.handle_inspection(options):
File "/home/www/beerdb_debug/lib/python3.4/site-
packages/django/core/management/commands/inspectdb.py", line 72, in
handle_inspection
constraints = connection.introspection.get_constraints(cursor,
table_name)
File "/home/www/beerdb_debug/lib/python3.4/site-
packages/django/db/backends/postgresql_psycopg2 /introspection.py", line
169, in get_constraints
"foreign_key": tuple(used_cols[0].split(".", 1)) if kind.lower() ==
"foreign key" else None,
IndexError: list index out of range
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:8>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by claudep):
Thanks for the instructions, but I'm still unable to reproduce. This
`IndexError` would imply that `used_cols` is empty, which is weird. Could
you try debugging this issue by setting a break point near the error and
examine the values of `constraint, column, kind, used_cols` just before
the crash?
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:9>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by heasus):
{{{
Are our environments similar?
% python -V
Python 3.4.3
% pip freeze
Django==1.8.4
django-postgresql==0.0.3
psycopg2==2.6.1
%psql things
things=# \encoding
UTF8
It is indeed empty in get_constraints and, as best I could follow it
through with the debugger, it is empty when it returns from psycopg. But,
running the sql manually or with the test script below produces the
expected output.
things=# SELECT
kc.constraint_name,
kc.column_name,
c.constraint_type,
array(SELECT table_name::text || '.' || column_name::text
FROM information_schema.constraint_column_usage
WHERE constraint_name = kc.constraint_name)
FROM information_schema.key_column_usage AS kc
JOIN information_schema.table_constraints AS c ON
kc.table_schema = c.table_schema AND
kc.table_name = c.table_name AND
kc.constraint_name = c.constraint_name
WHERE
kc.table_schema = 'public' AND
kc.table_name = 'things'
ORDER BY kc.ordinal_position ASC;
constraint_name | column_name | constraint_type | array
---------------------+-------------+-----------------+----------------
things_child_fkey | child | FOREIGN KEY | {children.idx}
things_sibling_fkey | sibling | FOREIGN KEY | {children.idx}
(2 rows)
import psycopg2
conn = psycopg2.connect("dbname=things")
curs = conn.cursor()
curs.execute("""SELECT
kc.constraint_name,
kc.column_name,
c.constraint_type,
array(SELECT table_name::text || '.' || column_name::text
FROM information_schema.constraint_column_usage
WHERE constraint_name = kc.constraint_name)
FROM information_schema.key_column_usage AS kc
JOIN information_schema.table_constraints AS c ON
kc.table_schema = c.table_schema AND
kc.table_name = c.table_name AND
kc.constraint_name = c.constraint_name
WHERE
kc.table_schema = 'public' AND
kc.table_name = 'things'
ORDER BY kc.ordinal_position ASC;
""")
print(curs.fetchone())
print(curs.fetchone())
print(curs.fetchone())
print(curs.fetchone())
% python arraytest.py
('things_child_fkey', 'child', 'FOREIGN KEY', ['children.idx'])
('things_sibling_fkey', 'sibling', 'FOREIGN KEY', ['children.idx'])
None
None
in pdb:
% ./manage.py inspectdb
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
# * Rearrange models' order
# * Make sure each model has one field with primary_key=True
# * Remove `managed = False` lines if you wish to allow Django to
create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or
field names.
#
# Also note: You'll have to insert the output of 'django-admin sqlcustom
[app_label]'
# into your database.
from __future__ import unicode_literals
from django.db import models
class Children(models.Model):
> /home/www/beerdb_debug/lib/python3.4/site-
packages/django/db/backends/postgresql_psycopg2/introspection.py(163)get_constraints()
-> for constraint, column, kind, used_cols in cursor.fetchall():
(Pdb)
(Pdb) n
> /home/www/beerdb_debug/lib/python3.4/site-
packages/django/db/backends/postgresql_psycopg2/introspection.py(165)get_constraints()
-> if constraint not in constraints:
(Pdb) p used_cols
[]
(Pdb)
I'm not proficient in python; I may be missing an obvious fix. I've also
tried the git HEAD, which appears to have its own issues with timezones
that I hacked around to test. I've also tried python 2.7 and on Debian.
I'm happy to try other variations of the environment.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:10>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
To rule out a bug in a third-party package, can you reproduce without
django-postgresql? What version of PostgreSQL?
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:11>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by heasus):
Without django-postgresql, the behavior is the same.
This Postgresql 9.4.4 (client and server).
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:12>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
I also couldn't reproduce with the provided schema.
* Python 3.4.3
* PostgreSQL 9.4.5
* psycopg2==2.6.1
One difference is that when I execute the query in the description, I only
get two rows:
"things_child_fkey";"child";"FOREIGN KEY";"{children.idx}"
"things_sibling_fkey";"sibling";"FOREIGN KEY";"{children.idx}"
(no results for primary keys). I haven't tried to understand the issue
further besides these steps.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:13>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by heasus):
Updating postgres to 9.4.5 did not change the behavior for me.
I reduced the schema to just the minimum to replicate the issue, and
indeed there are no default primary keys on table things but is one on
children (the object of the foreign keys). However, this does not matter;
the problem is in parsing the last field of the foreign keys as in the
previous comment in this ticket.
Hmmm; I'm not sure how to proceed to debug this. Same environment,
different results. The problem does appear to be within Django. Advice?
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:14>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
Could you try creating a failing test case for Django's test suite?
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:15>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: psycopg2 fetchall | Triage Stage:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => needsinfo
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:16>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by gagangupt16):
Facing similar issue while using inspectdb.
Environment:
Python: 3.4.3
Django: 1.9
PostgreSQL: 9.3.10
psycopg2: 2.6.1
{{{
Traceback (most recent call last):
File "manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "/usr/local/lib/python3.4/dist-
packages/django/core/management/__init__.py", line 350, in
execute_from_command_line
utility.execute()
File "/usr/local/lib/python3.4/dist-
packages/django/core/management/__init__.py", line 342, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/usr/local/lib/python3.4/dist-
packages/django/core/management/base.py", line 348, in run_from_argv
self.execute(*args, **cmd_options)
File "/usr/local/lib/python3.4/dist-
packages/django/core/management/base.py", line 399, in execute
output = self.handle(*args, **options)
File "/usr/local/lib/python3.4/dist-
packages/django/core/management/commands/inspectdb.py", line 25, in handle
for line in self.handle_inspection(options):
File "/usr/local/lib/python3.4/dist-
packages/django/core/management/commands/inspectdb.py", line 70, in
handle_inspection
constraints = connection.introspection.get_constraints(cursor,
table_name)
File "/usr/local/lib/python3.4/dist-
packages/django/db/backends/postgresql/introspection.py", line 171, in
get_constraints
"foreign_key": tuple(used_cols[0].split(".", 1)) if kind.lower() ==
"foreign key" else None,
IndexError: list index out of range
}}}
In this query,
{{{
cursor.execute("""
SELECT
kc.constraint_name,
kc.column_name,
c.constraint_type,
array(SELECT table_name::text || '.' || column_name::text
FROM information_schema.constraint_column_usage
WHERE constraint_name = kc.constraint_name)
FROM information_schema.key_column_usage AS kc
JOIN information_schema.table_constraints AS c ON
kc.table_schema = c.table_schema AND
kc.table_name = c.table_name AND
kc.constraint_name = c.constraint_name
WHERE
kc.table_schema = %s AND
kc.table_name = %s
ORDER BY kc.ordinal_position ASC
""", ["public", table_name])
}}}
The subquery
{{{
"SELECT table_name::text || '.' || column_name::text
FROM information_schema.constraint_column_usage
WHERE constraint_name = kc.constraint_name"
}}}
is returning empty array.
Hence, Index out of bound exception.
However on running the same query on postgresql console and replacing with
appropriate values. It was not returning array.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:17>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by gagangupt16):
* cc: gagangupt16@… (added)
* status: closed => new
* version: 1.8 => 1.9
* resolution: needsinfo =>
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:18>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by charettes):
Could you provide a list of your `INSTALLED_APPS`?
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:19>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by gagangupt16):
Replying to [comment:19 charettes]:
> Could you provide a list of your `INSTALLED_APPS`?
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
]
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:20>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by claudep):
We will probably need the dump file of a database causing this issue to be
able to debug it.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:21>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: psycopg2 fetchall | Triage Stage:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => needsinfo
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:22>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by xlotlu):
This is most likely caused to by an import by a different user, with
incomplete privileges (pg_dump -O maybe?). For example:
{{{
$ psql -qU user testdb
testdb=> select count(*) from information_schema.constraint_column_usage;
count
-------
0
(1 row)
}}}
as opposed to
{{{
$ psql -qU superuser testdb
testdb=# select count(*) from information_schema.constraint_column_usage;
count
-------
219
(1 row)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:23>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by TitanFighter):
I had the same error.
After investigation I found, that one table did not have some FK values to
another one (fields were NULL'ed). So, in order to fix this issue either
rows with NULL'ed FK's can be removed or NULL'ed fields can be populated.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:24>
Django
Reporter: heasus | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: psycopg2 fetchall | Triage Stage:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by heasus):
* resolution: needsinfo => invalid
Comment:
The problem was indeed permissions; the database user intentionally did
not have superuser privileges, but these queries, though apparently
denied, produce no error logs AFAICT.
Thanks for all the replies.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:25>
Django
-------------------------------------+-------------------------------------
Reporter: heasus | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* cc: marcos@… (added)
* version: 1.9 => master
* keywords: psycopg2 fetchall => psycopg2 fetchall inspectdb permissions
* has_patch: 0 => 1
* resolution: invalid =>
Comment:
The reason inspectdb wasn't working for you it's because the inspectdb
uses information_schema.constraint_column_usage, a view that,
[https://www.postgresql.org/docs/9.1/static/infoschema-constraint-column-
usage.html as explained on the PostgreSQL documentation]:
[...] identifies all columns in the current database that are used by
some constraint. '''Only those columns are shown that are contained in a
table owned by a currently enabled role'''. [...]
So, although Django was able to list all contraints and get their names by
using information_schema.key_column_usage and
information_schema.table_constraints (which have no such requirement about
owning the table - these let you view all data as long as you can modify
the said table), it wasn't able to get the foreign key the contraint
referenced to, and:
{{{
"foreign_key": tuple(used_cols[0].split(".", 1)) if kind.lower() ==
"foreign key" else None,
}}}
threw an exception because used_cols was an empty array.
The fix for this is easy: not using the constraint_column_usage table.
I've submitted a PR on GitHub that uses pg_catalog tables so it can be
used regardless of superuser status:
https://github.com/django/django/pull/7106
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:26>
Django
-------------------------------------+-------------------------------------
Reporter: heasus | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
inspectdb permissions |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* type: Bug => Cleanup/optimization
* stage: Unreviewed => Accepted
Comment:
I'm not an expert there, but tentatively accepting, although the tests are
passing with the proposed PR. That might just be a mistake rather than
something that can't be fixed though.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:27>
Django
-------------------------------------+-------------------------------------
Reporter: heasus | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: psycopg2 fetchall | Triage Stage: Accepted
inspectdb permissions |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by socram8888):
I've tweaked a bit the PR to support unique_together, too. Seems to be
working fine on every case now.
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:28>
Django
-------------------------------------+-------------------------------------
Reporter: heasus | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Keywords: psycopg2 fetchall | Triage Stage: Accepted
inspectdb permissions |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"d6b9aab37c41a772e5519e46b42b39958f99cadd" d6b9aab]:
{{{
#!CommitTicketReference repository=""
revision="d6b9aab37c41a772e5519e46b42b39958f99cadd"
Fixed #25476 -- Allowed PostgreSQL introspection to work regardless of
table owner.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25476#comment:29>