Re: psql display of foreign keys - Mailing list 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