From: Adrian Klaver <adrian.klaver@aklaver.com>
Subject: Re: Query about foreign key details for php framework
To: David Binney <donkeysoft@gmail.com>, pgsql-sql@postgresql.org
References: 
 <CAN123gmPUc2vutkE+H0FyPJSyf+xkHam2yuiGC2U8BTin6kaOA@mail.gmail.com>
 <56CFA136.4000508@aklaver.com>
 <CAN123gnvg5fC-SOBRwYT+eK6AOzfNU2+K4+AqjqJgPi9-Kh8Pw@mail.gmail.com>
 <56CFA4E1.8040204@aklaver.com>
 <CAN123gn7AL2mSpZitFb5bvF7VCbn0fVVv4qenyQj472P4AxdpQ@mail.gmail.com>
 <56CFC24A.6030508@aklaver.com>
 <CAN123g=5BbeEUz=q7w3_KdYZgirSxBL57V45sh4Aa5E3HCZ+Aw@mail.gmail.com>
 <56D06C02.5040107@aklaver.com>
 <CAN123gkwOujiAO3pwLqtouitTi0ai1J75iF1tNgfx6Hrrxh-=w@mail.gmail.com>
Message-ID: <56D0C4B8.7020200@aklaver.com>
Date: Fri, 26 Feb 2016 13:33:44 -0800
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAN123gkwOujiAO3pwLqtouitTi0ai1J75iF1tNgfx6Hrrxh-=w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 02/26/2016 10:47 AM, David Binney wrote:
> That is exactly the desired result, but in my db it is returning 2k rows
> with exactly the same query, even filtered to a specific table.

Note to self, read the entire doc page:

http://www.postgresql.org/docs/9.5/interactive/information-schema.html
"
Note: When querying the database for constraint information, it is 
possible for a standard-compliant query that expects to return one row 
to return several. This is because the SQL standard requires constraint 
names to be unique within a schema, but PostgreSQL does not enforce this 
restriction. PostgreSQL automatically-generated constraint names avoid 
duplicates in the same schema, but users can specify such duplicate names.

     This problem can appear when querying information schema views such 
as check_constraint_routine_usage, check_constraints, 
domain_constraints, and referential_constraints. Some other views have 
similar issues but contain the table name to help distinguish duplicate 
rows, e.g., constraint_column_usage, constraint_table_usage, 
table_constraints.
"


Best guess it is this line:

tc.constraint_name = rc.constraint_name

If you look at the output from my query you will see that is has two 
entries for name = con_fkey. There is actually only one such FK on that 
table, but another of the same name on another table. As written now it 
will find that constraint_name across all tables.

Rewriting see ^^^^^ in line:

production=# select
   rc.constraint_name AS name,
   tc.constraint_type AS type,
   kcu.column_name,
   rc.match_option AS match_type,
   rc.update_rule AS on_update,
   rc.delete_rule AS on_delete,
   kcu.table_name AS references_table,
   kcu.column_name AS references_field,
   kcu.ordinal_position
FROM
   (select distinct * from information_schema.referential_constraints) rc
   JOIN information_schema.key_column_usage kcu
   ON  kcu.constraint_name   = rc.constraint_name
   AND kcu.constraint_schema = rc.constraint_schema
   JOIN information_schema.table_constraints tc ON tc.constraint_name = 
rc.constraint_name
   AND tc.constraint_schema = rc.constraint_schema
   AND tc.constraint_name = rc.constraint_name
   AND tc.table_schema = rc.constraint_schema
   AND tc.table_name = kcu.table_name
   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
WHERE
   kcu.table_name = 'projection'
   AND rc.constraint_schema = 'public'
   AND tc.constraint_type = 'FOREIGN KEY'
ORDER BY
   rc.constraint_name,
   kcu.ordinal_position;

    name   |    type     | column_name | match_type | on_update | 
on_delete | references_table | references_field | ordinal_position
----------+-------------+-------------+------------+-----------+-----------+------------------+------------------+------------------
  con_fkey | FOREIGN KEY | c_id        | NONE       | CASCADE   | 
CASCADE   | projection       | c_id             |                1
  pno_fkey | FOREIGN KEY | p_item_no   | NONE       | CASCADE   | 
CASCADE   | projection       | p_item_no        |                1


Going back to your MySQL query I came up with this:

production=# SELECT
     distinct *
FROM
     information_schema.key_column_usage AS kcu
INNER JOIN
     information_schema.referential_constraints AS rc
ON
     (kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
     AND
     kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA)
--join
--    information_schema.tables
--ON
--    (tables.table_name = kcu.table_name AND tables.TABLE_SCHEMA = 
kcu.TABLE_SCHEMA)
WHERE
     kcu.TABLE_SCHEMA = 'public'
AND
     kcu.TABLE_NAME = 'projection';

-[ RECORD 1 ]-----------------+---------------
constraint_catalog            | production
constraint_schema             | public
constraint_name               | pno_fkey
table_catalog                 | production
table_schema                  | public
table_name                    | projection
column_name                   | p_item_no
ordinal_position              | 1
position_in_unique_constraint | 1
constraint_catalog            | production
constraint_schema             | public
constraint_name               | pno_fkey
unique_constraint_catalog     | production
unique_constraint_schema      | public
unique_constraint_name        | p_no_pkey
match_option                  | NONE
update_rule                   | CASCADE
delete_rule                   | CASCADE
-[ RECORD 2 ]-----------------+---------------
constraint_catalog            | production
constraint_schema             | public
constraint_name               | con_fkey
table_catalog                 | production
table_schema                  | public
table_name                    | projection
column_name                   | c_id
ordinal_position              | 1
position_in_unique_constraint | 1
constraint_catalog            | production
constraint_schema             | public
constraint_name               | con_fkey
unique_constraint_catalog     | production
unique_constraint_schema      | public
unique_constraint_name        | container_pkey
match_option                  | NONE
update_rule                   | CASCADE
delete_rule                   | CASCADE


>
> On Sat, 27 Feb 2016 at 01:16 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/25/2016 07:19 PM, David Binney wrote:
>      > Ah sorry adrian,
>      >
>      > I am a little in the dark as well since this is just a broken
>     piece of
>      > ORM i am attempting to fix, in the framework. So, maybe if you could
>      > help to reproduce that select list as a start that would be
>     great. But,
>      > I am suspecting they were trying to pull similar datasets from
>     mysql or
>      > postgres as an end goal.
>      >
>
>     Alright I ran the Postgres query you provided and it threw an error:
>
>     ERROR:  missing FROM-clause entry for table "cu"
>     LINE 26:   cu.ordinal_position;
>
>     in the ORDER BY clause. Changing cu.ordinal_position to
>     kcu.ordinal_position obtained a result when run for a table in one of my
>     databases:
>
>     production=# select
>         rc.constraint_name AS name,
>         tc.constraint_type AS type,
>         kcu.column_name,
>         rc.match_option AS match_type,
>         rc.update_rule AS on_update,
>         rc.delete_rule AS on_delete,
>         kcu.table_name AS references_table,
>         kcu.column_name AS references_field,
>         kcu.ordinal_position
>     FROM
>         (select distinct * from
>     information_schema.referential_constraints) rc
>         JOIN information_schema.key_column_usage kcu
>         ON  kcu.constraint_name   = rc.constraint_name
>         AND kcu.constraint_schema = rc.constraint_schema
>         JOIN information_schema.table_constraints tc ON tc.constraint_name =
>     rc.constraint_name
>         AND tc.constraint_schema = rc.constraint_schema
>         AND tc.constraint_name = rc.constraint_name
>         AND tc.table_schema = rc.constraint_schema
>     WHERE
>         kcu.table_name = 'projection'
>         AND rc.constraint_schema = 'public'
>         AND tc.constraint_type = 'FOREIGN KEY'
>     ORDER BY
>         rc.constraint_name,
>         kcu.ordinal_position;
>
>     -[ RECORD 1 ]----+------------
>     name             | con_fkey
>     type             | FOREIGN KEY
>     column_name      | c_id
>     match_type       | NONE
>     on_update        | CASCADE
>     on_delete        | CASCADE
>     references_table | projection
>     references_field | c_id
>     ordinal_position | 1
>     -[ RECORD 2 ]----+------------
>     name             | con_fkey
>     type             | FOREIGN KEY
>     column_name      | c_id
>     match_type       | NONE
>     on_update        | CASCADE
>     on_delete        | CASCADE
>     references_table | projection
>     references_field | c_id
>     ordinal_position | 1
>     -[ RECORD 3 ]----+------------
>     name             | pno_fkey
>     type             | FOREIGN KEY
>     column_name      | p_item_no
>     match_type       | NONE
>     on_update        | CASCADE
>     on_delete        | CASCADE
>     references_table | projection
>     references_field | p_item_no
>
>     If this is not the desired result, then we will need more information.
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> Cheers David Binney


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql