Undocumented limitation: FKs are problematic between partitioned and non-partitioned tables

[jseldess]

Jesse Seldess commented:

If you have a partitionable table (table a) with a FK reference to a non-partitionable table (table b), you will be able to partition table a’s primary index but not the secondary index auto-generated for the FK constraint.

Without being able to partition that secondary index, reads and writes touching that index won't be as effective because they could get routed far away and incur high latency. This probably means that we should recommend avoiding/dropping FK constraints on geo-partitioned tables and handling the relationship in the app when the relationship is from a partitioned to a non-partitioned table. If the relationship is between two similarly partitioned tables, all's well.

We need to expose this limitation in various places, e.g., on the table partitioning page, on the geo-partitioning topology pages, on the multi-region tutorial, etc. Likely as a warning in places.

root@127.0.0.1:63189/test> show create table orders;
  table_name |                                          create_statement
+------------+-----------------------------------------------------------------------------------------------------+
  orders     | CREATE TABLE orders (
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     city STRING NOT NULL,
             |     promo_code STRING NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     CONSTRAINT fk_promo_code_ref_promo_codes FOREIGN KEY (promo_code) REFERENCES promo_codes(code),
             |     INDEX orders_auto_index_fk_promo_code_ref_promo_codes (promo_code ASC),
             |     FAMILY "primary" (id, city, promo_code)
             | )
(1 row)
Time: 10.591ms

root@127.0.0.1:63189/test> show create table promo_codes;
  table_name  |                 create_statement
+-------------+---------------------------------------------------+
  promo_codes | CREATE TABLE promo_codes (
              |     id UUID NOT NULL DEFAULT gen_random_uuid(),
              |     code STRING NOT NULL,
              |     UNIQUE INDEX promo_codes_code_key (code ASC),
              |     FAMILY "primary" (id, code, rowid)
              | )
(1 row)
Time: 7.779ms

root@127.0.0.1:63189/test> insert into promo_codes (code) values ('foo'), ('bar');
INSERT 2

root@127.0.0.1:63189/test> insert into orders (city, promo_code) values ('new york', 'foo'), ('chicago', 'bar');
INSERT 2
Time: 5.635ms

root@127.0.0.1:63189/test> alter table orders partition by list (city) (partition ny values in ('new york'), partition chicago values in ('chicago'));
ALTER TABLE
Time: 51.582ms

root@127.0.0.1:63189/test> alter index orders_auto_index_fk_promo_code_ref_promo_codes partition by list (city) (partition ny values in ('new york'), partition chicago values in ('chicago'));
ERROR: declared partition columns (city) do not match first 1 columns in index being partitioned (promo_code)
SQLSTATE: 42601

root@127.0.0.1:63189/test>

cc @drewdeally, @solongordon, @danhhz, @awoods187

Jira Issue: DOC-413

[ericharmeling]

Assigning Ryan, as this has been marked Deploy Ops.

[exalate-issue-sync[bot]]

Nick Vigilante (nickvigilante) commented: Questions:

If table A contains a foreign key on column X pointing to table B’s column Y, should we recommend table B be set to GLOBAL? Would that overcome this limitation?

GLOBAL would satisfy this.