Issue: pwiz not generating foreign key fields for postgres in public schema
Author: TheCleric
State: closed
Created: 2018-01-23 17:54:22

I'm attempting to use pwiz to generate models based on my existing database tables and am having an issue where the generated models do not represent a foreign key that exists within the database.

I found #1021 but it seems like that users problems are related to the schema they were using. I am using the public schema. In the interest of providing info I will run through some of the debug steps that you instructed that user to provide.

I ran the following code to get what peewee thinks my foreign keys are:
```python
for table in db.get_tables():
    print db.get_foreign_keys(table)
```

And I got the following output:
```
[]
[]
```

I used psql to inspect my tables like so:
```
agnos=# \d+ "User"
                                                             Table "public.User"
   Column   |            Type             | Collation | Nullable |              Default               | Storage  | Stats target | Description 
------------+-----------------------------+-----------+----------+------------------------------------+----------+--------------+-------------
 id         | integer                     |           | not null | nextval('"User_id_seq"'::regclass) | plain    |              | 
 email      | text                        |           | not null |                                    | extended |              | 
 password   | text                        |           | not null |                                    | extended |              | 
 firstname  | text                        |           | not null |                                    | extended |              | 
 lastname   | text                        |           | not null |                                    | extended |              | 
 createdate | timestamp without time zone |           | not null |                                    | plain    |              | 
 createuser | text                        |           | not null |                                    | extended |              | 
 modifydate | timestamp without time zone |           |          |                                    | plain    |              | 
 modifyuser | text                        |           |          |                                    | extended |              | 
Indexes:
    "User_pkey" PRIMARY KEY, btree (id)
    "User_email_key" UNIQUE CONSTRAINT, btree (email)
    "user_email_password_idx" UNIQUE, btree (email, password)
Referenced by:
    TABLE ""Session"" CONSTRAINT "Session_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "User"(id)

agnos=# \d+ "Session"
                                                               Table "public.Session"
     Column     |            Type             | Collation | Nullable |                Default                | Storage  | Stats target | Description 
----------------+-----------------------------+-----------+----------+---------------------------------------+----------+--------------+-------------
 id             | integer                     |           | not null | nextval('"Session_id_seq"'::regclass) | plain    |              | 
 user_id        | integer                     |           | not null |                                       | plain    |              | 
 token          | text                        |           | not null |                                       | extended |              | 
 issuedate      | timestamp without time zone |           | not null |                                       | plain    |              | 
 validuntildate | timestamp without time zone |           | not null |                                       | plain    |              | 
Indexes:
    "Session_pkey" PRIMARY KEY, btree (id)
    "Session_token_key" UNIQUE CONSTRAINT, btree (token)
Foreign-key constraints:
    "Session_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "User"(id)
```

For reference here is what I get when I run pwiz against this database:
```python
from peewee import *

database = PostgresqlDatabase('agnos', **{'user': 'api_user'})

class UnknownField(object):
    def __init__(self, *_, **__): pass

class BaseModel(Model):
    class Meta:
        database = database

class Session(BaseModel):
    issuedate = DateTimeField()
    token = TextField(unique=True)
    user = IntegerField(db_column='user_id')
    validuntildate = DateTimeField()

    class Meta:
        db_table = 'Session'

class User(BaseModel):
    createdate = DateTimeField()
    createuser = TextField()
    email = TextField(unique=True)
    firstname = TextField()
    lastname = TextField()
    modifydate = DateTimeField(null=True)
    modifyuser = TextField(null=True)
    password = TextField()

    class Meta:
        db_table = 'User'
        indexes = (
            (('email', 'password'), True),
        )
```

As you can see the `user` field of the `Session` class is just being set to an `IntegerField` whereas I would expect it to be something similar to `ForeignKeyField(User)`. I'm also confused why it is creating the UnknownField at the top, but I can live with that.

Please let me know any other information I can provide to help diagnose this issue.

The `UnknownField` is part of the boilerplate -- it is included so that it is clear where a field was found that was not of a recognized type.

As far as why the foreign key is not being picked, I'm not sure. Under-the-hood, peewee uses the following query to get information from postgres about foreign keys:

```sql

SELECT
                kcu.column_name, ccu.table_name, ccu.column_name
            FROM information_schema.table_constraints AS tc
            JOIN information_schema.key_column_usage AS kcu
                ON (tc.constraint_name = kcu.constraint_name AND
                    tc.constraint_schema = kcu.constraint_schema)
            JOIN information_schema.constraint_column_usage AS ccu
                ON (ccu.constraint_name = tc.constraint_name AND
                    ccu.constraint_schema = tc.constraint_schema)
            WHERE
                tc.constraint_type = 'FOREIGN KEY' AND
                tc.table_name = '<the table name>' AND
                tc.table_schema ='public'
```

What happens when you run that query? You might try tweaking the WHERE clause (removing restrictions) to see if one of those expressions is causing the constraint not to be listed?

For the following query:

```sql
SELECT
                kcu.column_name, ccu.table_name, ccu.column_name
            FROM information_schema.table_constraints AS tc
            JOIN information_schema.key_column_usage AS kcu
                ON (tc.constraint_name = kcu.constraint_name AND
                    tc.constraint_schema = kcu.constraint_schema)
            JOIN information_schema.constraint_column_usage AS ccu
                ON (ccu.constraint_name = tc.constraint_name AND
                    ccu.constraint_schema = tc.constraint_schema)
            WHERE
                tc.constraint_type = 'FOREIGN KEY' AND
                tc.table_name = 'Session' AND
                tc.table_schema ='public'
```

I get the following result:
```
 column_name | table_name | column_name 
-------------+------------+-------------
 user_id     | User       | id
```

After digging into your query deeper I've discovered this is my issue. The table was created by one user, but the peewee code is accessing the postgres database via another user, and this other user somehow can't "see" the foreign key (even though they have full permissions to the database).  I have no idea how to fix this, but in the end this is not a peewee issue; it's a permissions issue. Feel free to close. Sorry for wasting your time. Thanks for pointing me in the right direction.

For anyone who comes after me. Here's what's happening (from the PostgreSQL documentation https://www.postgresql.org/docs/current/static/infoschema-constraint-column-usage.html):

My user can't see the foreign key created by the other user because when viewing the `constraint_column_usage` table:

> Only those columns are shown that are contained in a table owned by a currently enabled role. For a check constraint, this view identifies the columns that are used in the check expression.

I was able to get similar (though not exact) results via the following query which is not excluded from viewing other owners foreign keys:

```sql
SELECT
                kcu.column_name, pg_get_constraintdef(pgc.oid)
            FROM information_schema.table_constraints AS tc
            JOIN information_schema.key_column_usage AS kcu
                ON (tc.constraint_name = kcu.constraint_name AND
                    tc.constraint_schema = kcu.constraint_schema)
            JOIN 
            	 pg_catalog.pg_constraint AS pgc
                ON (pgc.conname = tc.constraint_name)
            JOIN pg_catalog.pg_namespace pgn
            	ON (pgn.oid = pgc.connamespace AND
            		pgn.nspname = tc.constraint_schema)
            WHERE
                tc.constraint_type = 'FOREIGN KEY' AND
                tc.table_name = 'Session' AND
                tc.table_schema ='public';
```

Results in:
```
 column_name |            pg_get_constraintdef             
-------------+---------------------------------------------
 user_id     | FOREIGN KEY (user_id) REFERENCES "User"(id)
```

Perhaps someone with better regex skills than I could massage this into an exact match of the output of the other query and we'd have a user-proof `get_foreign_keys()` function.

Sorry for the multiple postings. I've finally got something that has the same output as your existing query without the permissions issues:

```sql
SELECT 
    pgatt2.attname AS column_name, pgc.relname AS table_name, pgatt1.attname AS column_name
FROM
   (SELECT 
        unnest(pgcon.conkey) AS parent_id, unnest(pgcon.confkey) AS child_id, pgcon.confrelid, pgcon.conrelid
    FROM pg_catalog.pg_class pgc
    JOIN pg_catalog.pg_namespace pgn
    	ON (pgc.relnamespace = pgn.oid)
    JOIN pg_catalog.pg_constraint pgcon
    	ON (pgcon.conrelid = pgc.oid)
    WHERE
        pgc.relname = 'Session' AND
        pgn.nspname = 'public' AND
        pgcon.contype = 'f'
   ) AS meta
JOIN pg_catalog.pg_attribute pgatt1
	ON (pgatt1.attrelid = meta.confrelid AND
		pgatt1.attnum = meta.child_id)
JOIN pg_catalog.pg_class pgc
	ON (pgc.oid = meta.confrelid)
JOIN pg_catalog.pg_attribute pgatt2
	ON (pgatt2.attrelid = meta.conrelid AND
		pgatt2.attnum = meta.parent_id)
```

Modifying my local copy of peewee.py with this query gets me what I'm expecting:

```python
from peewee import *

database = PostgresqlDatabase('agnos', **{'user': 'api_user'})

class UnknownField(object):
    def __init__(self, *_, **__): pass

class BaseModel(Model):
    class Meta:
        database = database

class User(BaseModel):
    createdate = DateTimeField()
    createuser = TextField()
    email = TextField(unique=True)
    firstname = TextField()
    lastname = TextField()
    modifydate = DateTimeField(null=True)
    modifyuser = TextField(null=True)
    password = TextField()

    class Meta:
        db_table = 'User'
        indexes = (
            (('email', 'password'), True),
        )

class Session(BaseModel):
    issuedate = DateTimeField()
    token = TextField(unique=True)
    user = ForeignKeyField(db_column='user_id', rel_model=User, to_field='id')
    validuntildate = DateTimeField()

    class Meta:
        db_table = 'Session'
```

Would you be willing to accept a pull request if I submitted this as a change to `get_foreign_key()`?

Innnnnteresting! Thank you for digging so deeply into this issue. I recall a long time ago running into similar permissions deals relating to sequences and was baffled by it.

Let me look at and understand this query. Provided it works without regressions I'm fine with merging a change.

I've taken a look at the query, compared to the previous implementation I'm kind of leery of introducing the change. Presumably get_primary_keys(), get_columns() etc are subject to the same limitations as get_foreign_keys(). Ultimately this is a permissions issue inside the database, and presumably the query peewee uses currently is doing the appropriate thing? Might actually pass on accepting this change.

I think the issue is localized to the `information_schema.constraint_column_usage` table permissions, and therefore only affects `get_foreign_keys()` as the other functions do not attempt to select from that table. In my personal opinion I would say that the proper way to do this is to work around postgres limitation because who creates a FK shouldn't matter to who can see that it exists, and could lead to issues later for a developer who isn't paying attention.

At the same time I could see your position, and would have hesitance swapping out code that seems to have been working fine because some random developer found a weird edge case. I guess I can always patch it locally to cover my needs.

Think I'm going to pass on this patch, but will re-open if that changes in the future.

I'm running into similar issues. The fix proposed by @TheCleric works for foreign keys within the same schema, but fails for foreign keys pointing to other schemas. 

Does the pwiz script work for foreign key references across schemas?

Not to my knowledge, no.