agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: David Binney <donkeysoft@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Query about foreign key details for php framework
Date: Fri, 26 Feb 2016 13:33:44 -0800
Message-ID: <56D0C4B8.7020200@aklaver.com> (raw)
In-Reply-To: <CAN123gkwOujiAO3pwLqtouitTi0ai1J75iF1tNgfx6Hrrxh-=w@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (30+ messages) latest in thread
Message-ID: <56D0C4B8.7020200@aklaver.com>
Permalink: ../56D0C4B8.7020200@aklaver.com/
Also on: postgresql.org/message-id/56D0C4B8.7020200@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, donkeysoft@gmail.com
Subject: Re: Query about foreign key details for php framework
In-Reply-To: <56D0C4B8.7020200@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox