\d doesn't show partitioned table foreign keys
Hi.
I just noticed $subject, which attached seems to fix, although not sure if
that's the correct fix for the issue.
create table foo (a int primary key);
create table doo (a int primary key);
create table bar (a int references foo references doo) partition by list (a);
create table bar1 partition of bar for values in (1);
\d bar
Table "public.bar"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Partition key: LIST (a)
Number of partitions: 1 (Use \d+ to list them.)
But can see the key on the partition.
\d bar1
Table "public.bar1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Partition of: bar FOR VALUES IN (1)
Foreign-key constraints:
"bar_a_fkey" FOREIGN KEY (a) REFERENCES foo(a)
"bar_a_fkey1" FOREIGN KEY (a) REFERENCES doo(a)
After applying the patch:
\d bar
Table "public.bar"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Partition key: LIST (a)
Foreign-key constraints:
"bar_a_fkey" FOREIGN KEY (a) REFERENCES foo(a)
"bar_a_fkey1" FOREIGN KEY (a) REFERENCES doo(a)
Number of partitions: 1 (Use \d+ to list them.)
Will add this to open items.
Thanks,
Amit
Attachments:
describe-partitioned-table-fkey-v1.patchtext/plain; charset=UTF-8; name=describe-partitioned-table-fkey-v1.patchDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 410131e5c7..17bf5c8f6a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2312,7 +2312,8 @@ describeOneTableDetails(const char *schemaname,
}
/* print foreign-key constraints (there are none if no triggers) */
- if (tableinfo.hastriggers)
+ if (tableinfo.hastriggers ||
+ tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
printfPQExpBuffer(&buf,
"SELECT conname,\n"
On 2018/05/14 11:50, Amit Langote wrote:
Hi.
I just noticed $subject, which attached seems to fix, although not sure if
that's the correct fix for the issue.
I updated the comment above the changed code to explain things as I see them.
Attached updated patch.
Thanks,
Amit
Attachments:
describe-partitioned-table-fkey-v2.patchtext/plain; charset=UTF-8; name=describe-partitioned-table-fkey-v2.patchDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 410131e5c7..e72fdc5f72 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2311,8 +2311,13 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
- /* print foreign-key constraints (there are none if no triggers) */
- if (tableinfo.hastriggers)
+ /*
+ * Print foreign-key constraints (there are none if no triggers,
+ * except if the table is partitioned, in which case, any needed
+ * triggers are present in the table's partitions)
+ */
+ if (tableinfo.hastriggers ||
+ tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
printfPQExpBuffer(&buf,
"SELECT conname,\n"
On 2018-May-14, Amit Langote wrote:
Hi.
I just noticed $subject, which attached seems to fix, although not sure if
that's the correct fix for the issue.
The fix looks good to me. Will push shortly.
A related issue is that \d of the referenced table shows both the
partitioned table as well as all of its partitions, which seems too
repetitive. I think we should only list the partitioned table itself.
Thoughts?
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, May 15, 2018 at 12:38 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
On 2018-May-14, Amit Langote wrote:
Hi.
I just noticed $subject, which attached seems to fix, although not sure if
that's the correct fix for the issue.The fix looks good to me. Will push shortly.
Thank you.
A related issue is that \d of the referenced table shows both the
partitioned table as well as all of its partitions, which seems too
repetitive. I think we should only list the partitioned table itself.
Thoughts?
Yeah, I think showing only the partitioned table makes sense, assuming
that the triggers and constraint entries on the partitions are an
implementation detail. Right?
Thanks,
Amit