Re: \d doesn't show partitioned table foreign keys
On Tue, May 15, 2018 at 12:38 AM, Alvaro Herrera 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
Re: \d doesn't show partitioned table foreign keys
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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: \d doesn't show partitioned table foreign keys
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 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"
\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 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"