From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Michael Paquier <michael(at)paquier(dot)xyz> |
Cc: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: psql display of foreign keys |
Date: | 2019-03-05 02:01:33 |
Message-ID: | 5a460c39-6416-5b05-907a-97b02c232c2a@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2019/03/05 4:41, Alvaro Herrera wrote:
> Here's the patch I'm really interested about :-)
Thanks for the updated patch. I applied it and rebased the
foreign-keys-referencing-partitioned-tables patch on top. Here's
something I think you may have missed:
-- partitioned primary key table
create table p (a int primary key) partition by list (a);
create table p1 partition of p for values in (1) partition by list (a);
create table p11 partition of p1 for values in (1);
-- regular primary key table
create table pk (a int primary key);
-- another partitioned table to define FK on
create table q (a int) partition by list (a);
create table q1 partition of q for values in (1) partition by list (a);
create table q11 partition of q1 for values in (1);
-- FK on q referencing p
alter table q add foreign key (a) references p;
-- seems OK
\d p
Partitioned table "public.p"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ not null │
Partition key: LIST (a)
Indexes:
"p_pkey" PRIMARY KEY, btree (a)
Referenced by:
TABLE "q" CONSTRAINT "q_a_fkey" FOREIGN KEY (a) REFERENCES p(a)
Number of partitions: 1 (Use \d+ to list them.)
\d p1
Partitioned table "public.p1"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ not null │
Partition of: p FOR VALUES IN (1)
Partition key: LIST (a)
Indexes:
"p1_pkey" PRIMARY KEY, btree (a)
Referenced by:
TABLE "q" CONSTRAINT "q_a_fkey" FOREIGN KEY (a) REFERENCES p(a)
Number of partitions: 1 (Use \d+ to list them.)
\d p11
Table "public.p11"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ not null │
Partition of: p1 FOR VALUES IN (1)
Indexes:
"p11_pkey" PRIMARY KEY, btree (a)
Referenced by:
TABLE "q" CONSTRAINT "q_a_fkey" FOREIGN KEY (a) REFERENCES p(a)
-- change the FK to reference regular table
alter table q drop constraint q_a_fkey ;
alter table q add foreign key (a) references pk;
-- not OK?
\d pk
Table "public.pk"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ not null │
Indexes:
"pk_pkey" PRIMARY KEY, btree (a)
Referenced by:
TABLE "q" CONSTRAINT "q_a_fkey" FOREIGN KEY (a) REFERENCES pk(a)
TABLE "q1" CONSTRAINT "q_a_fkey" FOREIGN KEY (a) REFERENCES pk(a)
TABLE "q11" CONSTRAINT "q_a_fkey" FOREIGN KEY (a) REFERENCES pk(a)
Shouldn't the above only list the constraint on q as follows?
Referenced by:
TABLE "q" CONSTRAINT "q_a_fkey" FOREIGN KEY (a) REFERENCES pk(a)
Maybe:
@@ -2488,7 +2488,8 @@ describeOneTableDetails(const char *schemaname,
"SELECT conname,
conrelid::pg_catalog.regclass,\n"
"
pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
"FROM pg_catalog.pg_constraint c\n"
- "WHERE c.confrelid = '%s' AND c.contype
= 'f' ORDER BY 1;",
+ "WHERE c.confrelid = '%s' AND c.contype
= 'f' AND conparentid = 0\n"
+ "ORDER BY conname;",
Thanks,
Amit
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2019-03-05 02:28:44 | Re: Inheriting table AMs for partitioned tables |
Previous Message | David Rowley | 2019-03-05 01:38:53 | Re: Ordered Partitioned Table Scans |